Oracle常见问题QA第2版.docx
《Oracle常见问题QA第2版.docx》由会员分享，可在线阅读，更多相关《Oracle常见问题QA第2版.docx（122页珍藏版）》请在冰豆网上搜索。
Oracle常见问题QA第2版
前言
本文档由piner发表。
您可以自由地将此文档复制发行，但请保留此声明页。
请勿将此文档用于任何商业用途。
第一部分、SQL&PL/SQL
[Q]怎么样查询特殊字符，如通配符%与_
[A]select*fromtablewherenamelike'A\_%'escape'\'
[Q]如何插入单引号到数据库表中
[A]可以用ASCII码处理，其它特殊字符如&也一样，如
insertintotvalues（'i'||chr（39）||'m'）;--chr（39）代表字符'
或者用两个单引号表示一个
orinsertintotvalues（'I''m'）;--两个''可以表示一个'
[Q]怎样设置事务一致性
[A]settransaction[isolationlevel]readcommitted;默认语句级一致性
settransaction[isolationlevel]serializable;
readonly;事务级一致性
[Q]怎么样利用光标更新数据
[A]cursorc1is
select*fromtablename
wherenameisnullforupdate[ofcolumn]
……
updatetablenamesetcolumn=……
wherecurrentofc1;
[Q]怎样自定义异常
[A]pragma_exception_init（exception_name,error_number）;
如果立即抛出异常
raise_application_error（error_number,error_msg,true|false）;
其中number从-20000到-20999，错误信息最大2048B
异常变量
SQLCODE错误代码
SQLERRM错误信息
[Q]十进制与十六进制的转换
[A]8i以上版本：
to_char（100,'XX'）
to_number（'4D','XX'）
8i以下的进制之间的转换参考如下脚本
createorreplacefunctionto_base（p_decinnumber,p_baseinnumber）
returnvarchar2
is
l_strvarchar2（255）defaultNULL;
l_numnumberdefaultp_dec;
l_hexvarchar2（16）default'0123456789ABCDEF';
begin
if（p_decisnullorp_baseisnull）then
returnnull;
endif;
if（trunc（p_dec）<>p_decORp_dec<0）then
raisePROGRAM_ERROR;
endif;
loop
l_str:
=substr（l_hex,mod（l_num,p_base）+1,1）||l_str;
l_num:
=trunc（l_num/p_base）;
exitwhen（l_num=0）;
endloop;
returnl_str;
endto_base;
/
createorreplacefunctionto_dec
（p_strinvarchar2,
p_from_baseinnumberdefault16）returnnumber
is
l_numnumberdefault0;
l_hexvarchar2（16）default'0123456789ABCDEF';
begin
if（p_strisnullorp_from_baseisnull）then
returnnull;
endif;
foriin1..length（p_str）loop
l_num:
=l_num*p_from_base+instr（l_hex,upper（substr（p_str,i,1）））-1;
endloop;
returnl_num;
endto_dec;
/
createorreplacefunctionto_hex（p_decinnumber）returnvarchar2
is
begin
returnto_base（p_dec,16）;
endto_hex;
/
createorreplacefunctionto_bin（p_decinnumber）returnvarchar2
is
begin
returnto_base（p_dec,2）;
endto_bin;
/
createorreplacefunctionto_oct（p_decinnumber）returnvarchar2
is
begin
returnto_base（p_dec,8）;
endto_oct;
/
[Q]能不能介绍SYS_CONTEXT的详细用法
[A]利用以下的查询，你就明白了
select
SYS_CONTEXT（'USERENV','TERMINAL'）terminal,
SYS_CONTEXT（'USERENV','LANGUAGE'）language,
SYS_CONTEXT（'USERENV','SESSIONID'）sessionid,
SYS_CONTEXT（'USERENV','INSTANCE'）instance,
SYS_CONTEXT（'USERENV','ENTRYID'）entryid,
SYS_CONTEXT（'USERENV','ISDBA'）isdba,
SYS_CONTEXT（'USERENV','NLS_TERRITORY'）nls_territory,
SYS_CONTEXT（'USERENV','NLS_CURRENCY'）nls_currency,
SYS_CONTEXT（'USERENV','NLS_CALENDAR'）nls_calendar,
SYS_CONTEXT（'USERENV','NLS_DATE_FORMAT'）nls_date_format,
SYS_CONTEXT（'USERENV','NLS_DATE_LANGUAGE'）nls_date_language,
SYS_CONTEXT（'USERENV','NLS_SORT'）nls_sort,
SYS_CONTEXT（'USERENV','CURRENT_USER'）current_user,
SYS_CONTEXT（'USERENV','CURRENT_USERID'）current_userid,
SYS_CONTEXT（'USERENV','SESSION_USER'）session_user,
SYS_CONTEXT（'USERENV','SESSION_USERID'）session_userid,
SYS_CONTEXT（'USERENV','PROXY_USER'）proxy_user,
SYS_CONTEXT（'USERENV','PROXY_USERID'）proxy_userid,
SYS_CONTEXT（'USERENV','DB_DOMAIN'）db_domain,
SYS_CONTEXT（'USERENV','DB_NAME'）db_name,
SYS_CONTEXT（'USERENV','HOST'）host,
SYS_CONTEXT（'USERENV','OS_USER'）os_user,
SYS_CONTEXT（'USERENV','EXTERNAL_NAME'）external_name,
SYS_CONTEXT（'USERENV','IP_ADDRESS'）ip_address,
SYS_CONTEXT（'USERENV','NETWORK_PROTOCOL'）network_protocol,
SYS_CONTEXT（'USERENV','BG_JOB_ID'）bg_job_id,
SYS_CONTEXT（'USERENV','FG_JOB_ID'）fg_job_id,
SYS_CONTEXT（'USERENV','AUTHENTICATION_TYPE'）authentication_type,
SYS_CONTEXT（'USERENV','AUTHENTICATION_DATA'）authentication_data
fromdual
[Q]怎么获得今天是星期几，还关于其它日期函数用法
[A]可以用to_char来解决，如
selectto_char（to_date（'2002-08-26','yyyy-mm-dd'）,'day'）fromdual;
在获取之前可以设置日期语言，如
ALTERSESSIONSETNLS_DATE_LANGUAGE='AMERICAN';
还可以在函数中指定
selectto_char（to_date（'2002-08-26','yyyy-mm-dd'）,'day','NLS_DATE_LANGUAGE=American'）fromdual;
其它更多用法，可以参考to_char与to_date函数
如获得完整的时间格式
selectto_char（sysdate,'yyyy-mm-ddhh24:
mi:
ss'）fromdual;
随便介绍几个其它函数的用法：
本月的天数
SELECTto_char（last_day（SYSDATE）,'dd'）daysFROMdual
今年的天数
selectadd_months（trunc（sysdate,'year'）,12）-trunc（sysdate,'year'）fromdual
下个星期一的日期
SELECTNext_day（SYSDATE,'monday'）FROMdual
[Q]随机抽取前N条记录的问题
[A]8i以上版本
select*from（select*fromtablenameorderbysys_guid（））whererownumselect*from（select*fromtablenameorderbydbms_random.value）whererownum注：
dbms_random包需要手工安装，位于$ORACLE_HOME/rdbms/admin/dbmsrand.sql
dbms_random.value（100,200）可以产生100到200范围的随机数
[Q]抽取从N行到M行的记录，如从20行到30行的记录
[A]select*from（selectrownumid,t.*fromtable）whereidbetweenNandM;
[Q]怎么样抽取重复记录
[A]select*fromtablet1wherewheret1.rowed!
=
（selectmax（rowed）fromtablet2
wheret1.id=t2.idandt1.name=t2.name）
或者
selectcount（*）,t.col_a,t.col_bfromtablet
groupbycol_a,col_b
havingcount（*）>1
如果想删除重复记录，可以把第一个语句的select替换为delete
[Q]怎么样设置自治事务
[A]8i以上版本，不影响主事务
pragmaautonomous_transaction;
……
commit|rollback;
[Q]怎么样在过程中暂停指定时间
[A]DBMS_LOCK包的sleep过程
如：
dbms_lock.sleep（5）;表示暂停5秒。
[Q]怎么样快速计算事务的时间与日志量
[A]可以采用类似如下的脚本
DECLARE
start_timeNUMBER;
end_timeNUMBER;
start_redo_sizeNUMBER;
end_redo_sizeNUMBER;
BEGIN
start_time:
=dbms_utility.get_time;
SELECTVALUEINTOstart_redo_sizeFROMv$mystatm,v$statnames
WHEREm.STATISTIC#=s.STATISTIC#
ANDs.NAME='redosize';
--transactionstart
INSERTINTOt1
SELECT*FROMAll_Objects;
--otherdmlstatement
COMMIT;
end_time:
=dbms_utility.get_time;
SELECTVALUEINTOend_redo_sizeFROMv$mystatm,v$statnames
WHEREm.STATISTIC#=s.STATISTIC#
ANDs.NAME='redosize';
dbms_output.put_line（'EscapeTime:
'||to_char（end_time-start_time）||'centiseconds'）;
dbms_output.put_line（'RedoSize:
'||to_char（end_redo_size-start_redo_size）||'bytes'）;
END;
[Q]怎样创建临时表
[A]8i以上版本
createglobaltemporarytablename（columnlist）
oncommitpreserverows;--提交保留数据会话临时表
oncommitdeleterows;--提交删除数据事务临时表
临时表是相对于会话的，别的会话看不到该会话的数据。
[Q]怎么样在PL/SQL中执行DDL语句
[A]1、8i以下版本dbms_sql包
2、8i以上版本还可以用
executeimmediatesql;
dbms_utility.exec_ddl_statement（'sql'）;
[Q]怎么样获取IP地址
[A]服务器（817以上）：
utl_inaddr.get_host_address
客户端：
sys_context（'userenv','ip_address'）
[Q]怎么样加密存储过程
[A]用wrap命令，如（假定你的存储过程保存为a.sql）
wrapiname=a.sql
PL/SQLWrapper:
Release8.1.7.0.0-ProductiononTueNov2722:
26:
482001
Copyright（c）OracleCorporation1993,2000.AllRightsReserved.
Processinga.sqltoa.plb
提示a.sql转换为a.plb，这就是加密了的脚本，执行a.plb即可生成加密了的存储过程
[Q]怎么样在ORACLE中定时运行存储过程
[A]可以利用dbms_job包来定时运行作业，如执行存储过程，一个简单的例子，提交一个作业：
VARIABLEjobnonumber;
BEGIN
DBMS_JOB.SUBMIT（:
jobno,'ur_procedure;',SYSDATE,'SYSDATE+1'）;
commit;
END;
之后，就可以用以下语句查询已经提交的作业
select*fromuser_jobs;
[Q]怎么样从数据库中获得毫秒
[A]9i以上版本，有一个timestamp类型获得毫秒，如
SQL>selectto_char（systimestamp,'yyyy-mm-ddhh24:
mi:
ssxff'）time1,
to_char（current_timestamp）time2fromdual;
TIME1TIME2
---------------------------------------------------------------------------------------------
2003-10-2410:
48:
45.65600024-OCT-0310.48.45.656000AM+08:
00
可以看到，毫秒在to_char中对应的是FF。
8i以上版本可以创建一个如下的java函数
SQL>createorreplaceandcompile
javasource
named"MyTimestamp"
as
importjava.lang.String;
importjava.sql.Timestamp;
publicclassMyTimestamp
{
publicstaticStringgetTimestamp（）
{
return（newTimestamp（System.currentTimeMillis（）））.toString（）;
}
};
SQL>javacreated.
注：
注意java的语法，注意大小写
SQL>createorreplacefunctionmy_timestampreturnvarchar2
aslanguagejava
name'MyTimestamp.getTimestamp（）returnjava.lang.String';
/
SQL>functioncreated.
SQL>selectmy_timestamp,to_char（sysdate,'yyyy-mm-ddhh24:
mi:
ss'）ORACLE_TIMEfromdual;
MY_TIMESTAMPORACLE_TIME
-------------------------------------------
2003-03-1719:
15:
59.6882003-03-1719:
15:
59
如果只想获得1/100秒（hsecs），还可以利用dbms_utility.get_time
[Q]如果存在就更新，不存在就插入可以用一个语句实现吗
[A]9i已经支持了，是Merge，但是只支持select子查询，
如果是单条数据记录，可以写作select……fromdual的子查询。
语法为：
MERGEINTOtable
USINGdata_source
ON（condition）
WHENMATCHEDTHENupdate_clause
WHENNOTMATCHEDTHENinsert_clause;
如
MERGEINTOcoursec
USING（SELECTcourse_name,period,
course_hours
FROMcourse_updates）cu
ON（c.course_name=cu.course_name
ANDc.period=cu.period）
WHENMATCHEDTHEN
UPDATE
SETc.course_hours=cu.course_hours
WHENNOTMATCHEDTHEN
INSERT（c.course_name,c.period,
c.course_hours）
VALUES（cu.course_name,cu.period,
cu.course_hours）;
[Q]怎么实现左联，右联与外联
[A]在9i以前可以这么写:
左联：
selecta.id,a.name,b.addressfroma,b
wherea.id=b.id（+）
右联:
selecta.id,a.name,b.addressfroma,b
wherea.id（+）=b.id
外联
SELECTa.id,a.name,b.address
FROMa,b
WHEREa.id=b.id（+）
UNION
SELECTb.id,''name,b.address
FROMb
WHERENOTEXISTS（
SELECT*FROMa
WHEREa.id=b.id）;
在9i以上，已经开始支持SQL99标准，所以，以上语句可以写成：
默认内部联结：
selecta.id,a.name,b.address,c.subject
from（ainnerjoinbona.id=b.id）
innerjoinconb.name=c.name
whereother_clause
左联
selecta.id,a.name,b.address
fromaleftouterjoinbona.id=b.id
whereother_clause
右联
selecta.id,a.name,b.address
fromarightouterjoinbona.id=b.id
whereother_clause
外联
selecta.id,a.name,b.address
fromafullouterjoinbona.id=b.id
whereother_clause
or
selecta.id,a.name,b.address
fromafullouterjoinbusing（id）
whereother_clause
[Q]怎么实现一条记录根据条件多表插入
[A]9i以上可以通过Insertall语句完成，仅仅是一个语句，如：
INSERTALL
WHEN（id=1）THEN
INTOtable_1（id,name）
values（id,name）
WHEN（id=2）THEN
INTOtable_2（id,name）
values（id,name）
ELSE
INTOtable_other（id,name）
values（id,name）
SELECTid,name
FROMa;
如果没有条件的话，则完成每个表的插入，如
INSERTALL
INTOtable_1（id,name）
values（id,name）
INTOtable_2（id,name）
values（id,name）
INTOtable_other（id,name）
values（id,name）
SELECTid,name
FROMa;
[Q]如何实现行列转换
[A]1、固定列数的行列转换
如
studentsubjectgrade
-------------------
升级会员 