第一部分SQL&PL/SQL
[Q]怎么样查询特殊字符如通配符%与_
[A]select * from table where name like A\_% escape \
[Q]如何插入单引号到数据库表中
[A]可以用ASCII码处理其它特殊字符如&也一样如
insert into t values(i||chr()||m); chr()代表字符
或者用两个单引号表示一个
or insert into t values(Im); 两个可以表示一个
[Q]怎样设置事务一致性
[A]set transaction [isolation level] read committed; 默认语句级一致性
set transaction [isolation level] serializable;
read only; 事务级一致性
[Q]怎么样利用游标更新数据
[A]cursor c is
select * from tablename
where name is null for update [of column]
……
update tablename set column = ……
where current of c;
[Q]怎样自定义异常
[A] pragma_exception_init(exception_nameerror_number);
如果立即抛出异常
raise_application_error(error_numbererror_msgtrue|false);
其中number从到错误信息最大B
异常变量
SQLCODE 错误代码
SQLERRM 错误信息
[Q]十进制与十六进制的转换
[A]i以上版本
to_char(XX)
to_number(DXX)
i以下的进制之间的转换参考如下脚本
create or replace function to_base( p_dec in number p_base in number )
return varchar
is
l_str varchar() default NULL;
l_num number default p_dec;
l_hex varchar() default ABCDEF;
begin
if ( p_dec is null or p_base is null ) then
return null;
end if;
if ( trunc(p_dec) <> p_dec OR p_dec < ) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr( l_hex mod(l_nump_base)+ ) || l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = );
end loop;
return l_str;
end to_base;
/
create or replace function to_dec
( p_str in varchar
p_from_base in number default ) return number
is
l_num number default ;
l_hex varchar() default ABCDEF;
begin
if ( p_str is null or p_from_base is null ) then
return null;
end if;
for i in length(p_str) loop
l_num := l_num * p_from_base + instr(l_hexupper(substr(p_stri)));
end loop;
return l_num;
end to_dec;
/
[Q]能不能介绍SYS_CONTEXT的详细用法
[A]利用以下的查询你就明白了
select
SYS_CONTEXT(USERENVTERMINAL) terminal
SYS_CONTEXT(USERENVLANGUAGE) language
SYS_CONTEXT(USERENVSESSIONID) sessionid
SYS_CONTEXT(USERENVINSTANCE) instance
SYS_CONTEXT(USERENVENTRYID) entryid
SYS_CONTEXT(USERENVISDBA) isdba
SYS_CONTEXT(USERENVNLS_TERRITORY) nls_territory
SYS_CONTEXT(USERENVNLS_CURRENCY) nls_currency
SYS_CONTEXT(USERENVNLS_CALENDAR) nls_calendar
SYS_CONTEXT(USERENVNLS_DATE_FORMAT) nls_date_format
SYS_CONTEXT(USERENVNLS_DATE_LANGUAGE) nls_date_language
SYS_CONTEXT(USERENVNLS_SORT) nls_sort
SYS_CONTEXT(USERENVCURRENT_USER) current_user
SYS_CONTEXT(USERENVCURRENT_USERID) current_userid
SYS_CONTEXT(USERENVSESSION_USER) session_user
SYS_CONTEXT(USERENVSESSION_USERID) session_userid
SYS_CONTEXT(USERENVPROXY_USER) proxy_user
SYS_CONTEXT(USERENVPROXY_USERID) proxy_userid
SYS_CONTEXT(USERENVDB_DOMAIN) db_domain
SYS_CONTEXT(USERENVDB_NAME) db_name
SYS_CONTEXT(USERENVHOST) host
SYS_CONTEXT(USERENVOS_USER) os_user
SYS_CONTEXT(USERENVEXTERNAL_NAME) external_name
SYS_CONTEXT(USERENVIP_ADDRESS) ip_address
SYS_CONTEXT(USERENVNETWORK_PROTOCOL) network_protocol
SYS_CONTEXT(USERENVBG_JOB_ID) bg_job_id
SYS_CONTEXT(USERENVFG_JOB_ID) fg_job_id
SYS_CONTEXT(USERENVAUTHENTICATION_TYPE) authentication_type
SYS_CONTEXT(USERENVAUTHENTICATION_DATA) authentication_data
from dual
[Q]怎么获得今天是星期几还关于其它日期函数用法
[A]可以用to_char来解决如
select to_char(to_date(yyyymmdd)day) from dual;
在获取之前可以设置日期语言如
ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;
还可以在函数中指定
select to_char(to_date(yyyymmdd)dayNLS_DATE_LANGUAGE = American) from dual;
其它更多用法可以参考to_char与to_date函数
如获得完整的时间格式
select to_char(sysdateyyyymmdd hh:mi:ss) from dual;
随便介绍几个其它函数的用法
本月的天数
SELECT to_char(last_day(SYSDATE)dd) days FROM dual
今年的天数
select add_months(trunc(sysdateyear) ) trunc(sysdateyear) from dual
下个星期一的日期
SELECT Next_day(SYSDATEmonday) FROM dual
[Q]随机抽取前N条记录的问题
[A]i以上版本
select * from (select * from tablename order by sys_guid()) where rownum < N;
select * from (select * from tablename order by dbms_randomvalue) where rownum< N;
注dbms_random包需要手工安装位于$ORACLE_HOME/rdbms/admin/dbmsrandsql
dbms_randomvalue()可以产生到范围的随机数
[Q]抽取从N行到M行的记录如从行到行的记录
[A]select * from (select rownum idt* from table where ……
and rownum <= ) where id > ;
[Q]怎么样抽取重复记录
[A]select * from table t where where trowed !=
(select max(rowed) from table t
where tid=tid and tname=tname)
或者
select count(*) l_al_b from table t
group by col_acol_b
having count(*)>
如果想删除重复记录可以把第一个语句的select替换为delete
[Q]怎么样设置自治事务
[A]i以上版本不影响主事务
pragma autonomous_transaction;
……
commit|rollback;
[Q]怎么样在过程中暂停指定时间
[A]DBMS_LOCK包的sleep过程
如dbms_locksleep();表示暂停秒
[Q]怎么样快速计算事务的时间与日志量
[A]可以采用类似如下的脚本
DECLARE
start_time NUMBER;
end_time NUMBER;
start_redo_size NUMBER;
end_redo_size NUMBER;
BEGIN
start_time := dbms_utilityget_time;
SELECT VALUE INTO start_redo_size FROM v$mystat mv$statname s
WHERE mSTATISTIC#=sSTATISTIC#
AND sNAME=redo size;
transaction start
INSERT INTO t
SELECT * FROM All_Objects;
other dml statement
COMMIT;
end_time := dbms_utilityget_time;
SELECT VALUE INTO end_redo_size FROM v$mystat mv$statname s
WHERE mSTATISTIC#=sSTATISTIC#
AND sNAME=redo size;
dbms_outputput_line(Escape Time:||to_char(end_timestart_time)|| centiseconds);
dbms_outputput_line(Redo Size:||to_char(end_redo_sizestart_redo_size)|| bytes);
END;
[Q]怎样创建临时表
[A]i以上版本
create global temporary tablename(column list)
on commit preserve rows; 提交保留数据 会话临时表
on commit delete rows; 提交删除数据 事务临时表
临时表是相对于会话的别的会话看不到该会话的数据
[Q]怎么样在PL/SQL中执行DDL语句
[A]i以下版本dbms_sql包
i以上版本还可以用
execute immediate sql;
dbms_utilityexec_ddl_statement(sql);
[Q]怎么样获取IP地址
[A]服务器(以上)utl_inaddrget_host_address
客户端sys_context(userenvip_address)
[Q]怎么样加密存储过程
[A]用wrap命令如（假定你的存储过程保存为asql）
wrap iname=asql
PL/SQL Wrapper: Release Production on Tu
