ORACLE之常用FAQ V1.0

　　第一部分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
From:http://tw.wingwit.com/Article/program/Oracle/201311/16737.html