Oracle常见问题Q&A(第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]cursorc1isselect*fromtablenamewherenameisnullforupdate[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)returnvarchar2isl_strvarchar2(255)defaultNULL;l_numnumberdefaultp_dec;l_hexvarchar2(16)default'0123456789ABCDEF';beginif(p_decisnullorp_baseisnull)thenreturnnull;endif;if(trunc(p_dec)p_decORp_dec0)thenraisePROGRAM_ERROR;endif;loopl_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)returnnumberisl_numnumberdefault0;l_hexvarchar2(16)default'0123456789ABCDEF';beginif(p_strisnullorp_from_baseisnull)thenreturnnull;endif;foriin1..length(p_str)loopl_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)returnvarchar2isbeginreturnto_base(p_dec,16);endto_hex;/createorreplacefunctionto_bin(p_decinnumber)returnvarchar2isbeginreturnto_base(p_dec,2);endto_bin;/createorreplacefunctionto_oct(p_decinnumber)returnvarchar2isbeginreturnto_base(p_dec,8);endto_oct;/[Q]能不能介绍SYS_CONTEXT的详细用法[A]利用以下的查询，你就明白了selectSYS_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_datafromdual[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())whererownumN;select*from(select*fromtablenameorderbydbms_random.value)whererownumN;注：dbms_random包需要手工安装，位于$ORACLE_HOME/rdbms/admin/dbmsrand.sqldbms_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)fromtablet2wheret1.id=t2.idandt1.name=t2.name)或者selectcount(*),t.col_a,t.col_bfromtabletgroupbycol_a,col_bhavingcount(*)1如果想删除重复记录，可以把第一个语句的select替换为delete[Q]怎么样设置自治事务[A]8i以上版本，不影响主事务pragmaautonomous_transaction;……commit|rollback;[Q]怎么样在过程中暂停指定时间[A]DBMS_LOCK包的sleep过程如：dbms_lock.sleep(5);表示暂停5秒。[Q]怎么样快速计算事务的时间与日志量[A]可以采用类似如下的脚本DECLAREstart_timeNUMBER;end_timeNUMBER;start_redo_sizeNUMBER;end_redo_sizeNUMBER;BEGINstart_time:=dbms_utility.get_time;SELECTVALUEINTOstart_redo_sizeFROMv$mystatm,v$statnamesWHEREm.STATISTIC#=s.STATISTIC#ANDs.NAME='redosize';--transactionstartINSERTINTOt1SELECT*FROMAll_Objects;--otherdmlstatementCOMMIT;end_time:=dbms_utility.get_time;SELECTVALUEINTOend_redo_sizeFROMv$mystatm,v$statnamesWHEREm