历史上最强的sqlFAQforOracleORACLE之常用FAQV1.0，已经停止更新，准备出chm版本为便于大家阅读．此帖置顶．请不要在后面跟上一些"好"，"顶"之类的帖子,如果真的要感谢Piner，请多多提交FAQ．谢谢合作--byFenng-----------------------------------------------------------------------------------------------------------------第一部分、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_dec<0)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;/[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,S...