Oracle之常用FAQ–SQL

第一部分、SQL&PL/SQL[Q]怎麼樣查詢特殊字元，如萬用字元%與_
[A]select * from table where name like 'A\_%' escape '\'[Q]如何插入單引號到資料庫表中
[A]可以用ASCII碼處理，其它特殊字元如&也一樣，如insert into t values('i'||chr(39)||'m');  -- chr(39)代表字元'或者用兩個單引號表示一個or insert into t values('I''m');  -- 兩個''可以表示一個'[Q]怎樣設定事務一致性
[A]set transaction [isolation level] read committed;  預設語句級一致性set transaction [isolation level] serializable;read only;   事務級一致性[Q]怎麼樣利用遊標更新資料
[A]cursor c1 isselect * from tablenamewhere name is null for update [of column]……update tablename set column = ……where current of c1;[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以下的進位之間的轉換參考如下指令碼create or replace function to_base( p_dec in number, p_base in number )return varchar2isl_str varchar2(255) default NULL;l_num number default p_dec;l_hex varchar2(16) default '0123456789ABCDEF';beginif ( p_dec is null or p_base is null ) thenreturn null;end if;if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) thenraise PROGRAM_ERROR;end if;loopl_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;l_num := trunc( l_num/p_base );exit when ( l_num = 0 );end loop;return l_str;end to_base;/create or replace function to_dec( p_str in varchar2,p_from_base in number default 16 ) return numberisl_num number default 0;l_hex varchar2(16) default '0123456789ABCDEF';beginif ( p_str is null or p_from_base is null ) thenreturn null;end if;for i in 1 .. length(p_str) loopl_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;end loop;return l_num;end to_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,SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_datafrom dual[Q]怎麼獲得今天是星期幾，還關於其它日期函數用法
[A]可以用to_char來解決，如select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;在擷取之前可以設定日期語言，如ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';還可以在函數中指定select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;其它更多用法，可以參考to_char與to_date函數如獲得完整的時間格式select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from  dual;隨便介紹幾個其它函數的用法：本月的天數SELECT to_char(last_day(SYSDATE),'dd') days FROM dual今年的天數select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual下個星期一的日期SELECT Next_day(SYSDATE,'monday') FROM dual[Q]隨機抽取前N條記錄的問題
[A]8i以上版本select * from (select * from tablename order by sys_guid()) where rownum < N;select * from (select * from tablename order by dbms_random.value) where rownum< N;註：dbms_random包需要手工安裝，位於$ORACLE_HOME/rdbms/admin/dbmsrand.sqldbms_random.value(100,200)可以產生100到200範圍的隨機數[Q]抽取從N行到M行的記錄，如從20行到30行的記錄
[A]select * from (select rownum id,t.* from table where ……and rownum <= 30) where id > 20;[Q]怎麼樣抽取重複記錄
[A]select * from table t1 where where t1.rowed !=(select max(rowed) from table t2where t1.id=t2.id and t1.name=t2.name)或者select count(*), t.col_a,t.col_b from table tgroup by col_a,col_bhaving count(*)>1如果想重複資料刪除記錄，可以把第一個語句的select替換為delete[Q]怎麼樣設定自治事務
[A]8i以上版本，不影響主事務pragma autonomous_transaction;……commit|rollback;[Q]怎麼樣在過程中暫停指定時間
[A]DBMS_LOCK包的sleep過程如：dbms_lock.sleep(5);表示暫停5秒。[Q]怎麼樣快速計算事務的時間與日誌量
[A]可以採用類似如下的指令碼DECLAREstart_time NUMBER;end_time NUMBER;start_redo_size NUMBER;end_redo_size NUMBER;BEGINstart_time := dbms_utility.get_time;SELECT VALUE INTO start_redo_size FROM v$mystat m,v$statname sWHERE m.STATISTIC#=s.STATISTIC#AND s.NAME='redo size';--transaction startINSERT INTO t1SELECT * FROM All_Objects;--other dml statementCOMMIT;end_time := dbms_utility.get_time;SELECT VALUE INTO end_redo_size FROM v$mystat m,v$statname sWHERE m.STATISTIC#=s.STATISTIC#AND s.NAME='redo size';dbms_output.put_line('Escape Time:'||to_char(end_time-start_time)||' centiseconds');dbms_output.put_line('Redo Size:'||to_char(end_redo_size-start_redo_size)||' bytes');END;[Q]怎樣建立暫存資料表
[A]8i以上版本create global temporary tablename(column list)on commit preserve rows;  --提交保留資料 會話暫存資料表on commit delete rows;    --提交刪除資料 事務暫存資料表暫存資料表是相對於會話的，別的會話看不到該會話的資料。[Q]怎麼樣在PL/SQL中執行DDL語句
[A]1、8i以下版本dbms_sql包2、8i以上版本還可以用execute immediate sql;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）wrap iname=a.sqlPL/SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001Copyright (c) Oracle Corporation 1993, 2000.  All Rights Reserved.Processing a.sql to a.plb提示a.sql轉換為a.plb，這就是加密了的指令碼，執行a.plb即可產生加密了的預存程序[Q]怎麼樣在ORACLE中定時運行預存程序
[A]可以利用dbms_job包來定時運行作業，如執行預存程序，一個簡單的例子，提交一個作業：VARIABLE jobno number;BEGINDBMS_JOB.SUBMIT(:jobno, 'ur_procedure;',SYSDATE,'SYSDATE + 1');commit;END;之後，就可以用以下語句查詢已經提交的作業select * from user_jobs;[Q]怎麼樣從資料庫中獲得毫秒
[A]9i以上版本，有一個timestamp類型獲得毫秒，如SQL>select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ssxff') time1,to_char(current_timestamp) time2 from dual;TIME1                         TIME2----------------------------- ----------------------------------------------------------------2003-10-24 10:48:45.656000    24-OCT-03 10.48.45.656000 AM +08:00可以看到，毫秒在to_char中對應的是FF。8i以上版本可以建立一個如下的java函數SQL>create or replace and compilejava sourcenamed "MyTimestamp"asimport java.lang.String;import java.sql.Timestamp;public class MyTimestamp{public static String getTimestamp(){return(new Timestamp(System.currentTimeMillis())).toString();}};SQL>java created.註：注意java的文法，注意大小寫SQL>create or replace function my_timestamp return varchar2as language javaname 'MyTimestamp.getTimestamp() return java.lang.String';/SQL>function created.SQL>select my_timestamp,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') ORACLE_TIME from dual;MY_TIMESTAMP             ORACLE_TIME------------------------ -------------------2003-03-17 19:15:59.688  2003-03-17 19:15:59如果只想獲得1/100秒(hsecs)，還可以利用dbms_utility.get_time[Q]如果存在就更新，不存在就插入可以用一個語句實現嗎
[A]9i已經支援了，是Merge，但是只支援select子查詢，如果是單條資料記錄，可以寫作select …… from dual的子查詢。文法為：MERGE INTO tableUSING data_sourceON (condition)WHEN MATCHED THEN update_clauseWHEN NOT MATCHED THEN insert_clause;如MERGE INTO course cUSING (SELECT course_name, period,course_hoursFROM course_updates) cuON (c.course_name = cu.course_nameAND c.period = cu.period)WHEN MATCHED THENUPDATESET c.course_hours = cu.course_hoursWHEN NOT MATCHED THENINSERT (c.course_name, c.period,c.course_hours)VALUES (cu.course_name, cu.period,cu.course_hours);  [Q]怎麼實現左聯，右聯與外聯
[A]在9i以前可以這麼寫:左聯：select a.id,a.name,b.address from a,bwhere a.id=b.id(+)右聯:select a.id,a.name,b.address from a,bwhere a.id(+)=b.id外聯SELECT a.id,a.name,b.addressFROM a,bWHERE a.id = b.id(+)UNIONSELECT b.id,'' name,b.addressFROM bWHERE NOT EXISTS (SELECT * FROM aWHERE a.id = b.id);在9i以上，已經開始支援SQL99標準，所以，以上語句可以寫成：預設內部連接：select a.id,a.name,b.address,c.subjectfrom (a inner join b on a.id=b.id)inner join c on b.name = c.namewhere other_clause左聯select a.id,a.name,b.addressfrom a left outer join b on a.id=b.idwhere other_clause右聯select a.id,a.name,b.addressfrom a right outer join b on a.id=b.idwhere other_clause外聯select a.id,a.name,b.addressfrom a full outer join b on a.id=b.idwhere other_clauseorselect a.id,a.name,b.addressfrom a full outer join b using (id)where other_clause[Q]怎麼實現一條記錄根據條件多表插入
[A]9i以上可以通過Insert all陳述式完成，僅僅是一個語句，如：INSERT ALLWHEN (id=1) THENINTO table_1 (id, name)values(id,name)WHEN (id=2) THENINTO table_2 (id, name)values(id,name)ELSEINTO table_other (id, name)values(id, name)SELECT id,nameFROM a;如果沒有條件的話，則完成每個表的插入，如INSERT ALLINTO table_1 (id, name)values(id,name)INTO table_2 (id, name)values(id,name)INTO table_other (id, name)values(id, name)SELECT id,nameFROM a;[Q]如何?行列轉換
[A]1、固定列數的行列轉換如student  subject grade---------------------------student1  語文 80student1  數學 70student1  英語 60student2  語文 90student2  數學 80student2 英語 100……轉換為語文  數學  英語student1   80    70     60student2   90    80     100……語句如下：select student,sum(decode(subject,'語文', grade,null)) "語文",sum(decode(subject,'數學', grade,null)) "數學",sum(decode(subject,'英語', grade,null)) "英語"from tablegroup by student2、不定列行列轉換如c1     c2--------------1      我1      是1      誰2      知2      道3      不……轉換為1   我是誰2   知道3   不這一類型的轉換必須藉助於PL/SQL來完成，這裡給一個例子CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)RETURN VARCHAR2ISCol_c2 VARCHAR2(4000);BEGINFOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOPCol_c2 := Col_c2||cur.c2;END LOOP;Col_c2 := rtrim(Col_c2,1);RETURN Col_c2;END;/SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可[Q]怎麼樣實現分組取前N條記錄
[A]8i以上版本，利用分析函數如擷取每個部門薪水前三名的員工或每個班成績前三名的學生。Select * from(select depno,ename,sal,row_number() over (partition by depnoorder by sal desc) rnfrom emp)where rn<=3[Q]怎麼樣把相鄰記錄合并到一條記錄
[A]8i以上版本，分析函數lag與lead可以提取後一條或前一天記錄到本記錄。Select deptno,ename,hiredate,lag(hiredate,1,null) over(partition by deptno order by hiredate,ename) last_hirefrom emporder by depno,hiredate[Q]如何取得一列中第N大的值？
[A]select * from(select t.*,dense_rank() over (order by t2 desc) rank from t)where rank = &N;[Q]怎麼樣把查詢內容輸出到文本
[A]用spool如如sqlplus –s  " / as sysdba" <<EOFset heading offset feedback offspool temp.txt　 select * from tab;dbms_output.put_line(‘test’);spool offexitEOF[Q] 如何在SQL*PLUS環境中執行OS命令？
[A] 比如進入了SQLPLUS，啟動了資料庫，忽然想起監聽還沒有啟動，此時不用退出SQLPLUS，也不用另外起一個命令列視窗，直接輸入：SQL> host lsntctl start或者unix/linux平台下SQL>!<OS command>windows平台下SQL>$<OS command>總結：HOST <OS command>可以直接執行OS命令。備忘：cd命令無法正確執行。[Q]怎麼設定預存程序的調用者許可權
[A]普通預存程序都是所有者許可權，如果想設定調用者許可權，請參考如下語句create or replaceprocedure ……()AUTHID CURRENT_USERAsbegin……end;[Q]怎麼快速獲得使用者下每個表或表分區的記錄數
[A]可以分析該使用者，然後查詢user_tables字典，或者採用如下指令碼即可SET SERVEROUTPUT ON SIZE 20000DECLAREmiCount INTEGER;BEGINFOR c_tab IN (SELECT table_name FROM user_tables) LOOPEXECUTE IMMEDIATE 'select count(*) from "' || c_tab.table_name || '"' into miCount;dbms_output.put_line(rpad(c_tab.table_name,30,'.') || lpad(miCount,10,'.'));--if it is partition tableSELECT COUNT(*) INTO miCount FROM User_Part_Tables WHERE table_name = c_tab.table_name;IF miCount >0 THENFOR c_part IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = c_tab.table_name) LOOPEXECUTE IMMEDIATE 'select count(*) from ' || c_tab.table_name || ' partition (' || c_part.partition_name || ')'INTO miCount;dbms_output.put_line('     '||rpad(c_part.partition_name,30,'.') || lpad(miCount, 10,'.'));END LOOP;END IF;END LOOP;END;[Q]怎麼在Oracle中發郵件
[A]可以利用utl_smtp包發郵件，以下是一個發送簡單郵件的例子程式
/****************************************************************************parameter: Rcpter in varchar2 接收者郵箱Mail_Content in Varchar2 郵件內容desc: ·發送郵件到指定郵箱·只能指定一個郵箱，如果需要發送到多個郵箱，需要另外的輔助程式****************************************************************************/CREATE OR REPLACE PROCEDURE sp_send_mail( rcpter IN VARCHAR2,mail_content IN VARCHAR2)ISconn utl_smtp.connection;--write titlePROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) ASBEGINutl_smtp.write_data(conn, NAME||': '|| HEADER||utl_tcp.CRLF);END;BEGIN--opne connectconn := utl_smtp.open_connection('smtp.com');utl_smtp.helo(conn, 'oracle');utl_smtp.mail(conn, 'oracle info');utl_smtp.rcpt(conn, Rcpter);utl_smtp.open_data(conn);--write titlesend_header('From',    'Oracle Database');send_header('To',      '"Recipient" <'||rcpter||'>');send_header('Subject', 'DB Info');--write mail contentutl_smtp.write_data(conn, utl_tcp.crlf || mail_content);--close connectutl_smtp.close_data(conn);utl_smtp.quit(conn);EXCEPTIONWHEN utl_smtp.transient_error OR utl_smtp.permanent_error THENBEGINutl_smtp.quit(conn);EXCEPTIONWHEN OTHERS THENNULL;END;WHEN OTHERS THENNULL;END sp_send_mail;[Q]怎麼樣在Oracle中寫作業系統檔案，如寫日誌
[A]可以利用utl_file包，但是，在此之前，要注意設定好Utl_file_dir初始化參數/**************************************************************************parameter:textContext in varchar2  日誌內容desc: ·寫日誌,把內容記到伺服器指定目錄下·必須配置Utl_file_dir初始化參數，並保證日誌路徑與Utl_file_dir路徑一致或者是其中一個****************************************************************************/CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2)ISfile_handle      utl_file.file_type;Write_content    VARCHAR2(1024);Write_file_name  VARCHAR2(50);BEGIN--open filewrite_file_name := 'db_alert.log';file_handle    := utl_file.fopen('/u01/logs',write_file_name,'a');write_content  := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||text_context;--write fileIF utl_file.is_open(file_handle) THENutl_file.put_line(file_handle,write_content);END IF;--close fileutl_file.fclose(file_handle);EXCEPTIONWHEN OTHERS THENBEGINIF utl_file.is_open(file_handle) THENutl_file.fclose(file_handle);END IF;EXCEPTIONWHEN OTHERS THENNULL;END;END sp_Write_log;