Oracle存儲(chǔ)過(guò)程開(kāi)發(fā)規(guī)范與技巧

1、存儲(chǔ)過(guò)程開(kāi)發(fā)規(guī)范與技巧開(kāi)發(fā)規(guī)范1.書(shū)寫(xiě)規(guī)范1）：程序頭書(shū)寫(xiě)規(guī)范程序頭開(kāi)始部分應(yīng)說(shuō)明程序整體的功能，存儲(chǔ)過(guò)程名稱(chēng)，編寫(xiě)人，編寫(xiě)日期，修改人，修改日期，版本號(hào)以及過(guò)程涉及的表和視圖。示例如下：-/*名稱(chēng)及實(shí)現(xiàn)功能: 版本: ( 版本號(hào)標(biāo)示：新建 V1.0.0 小的修改變?yōu)閂1.0.1 大的修改V1.1.0 重構(gòu)V2.0.0)Create by * Create Date 2006-06-29Update by * update Date 2006-06-30修改原因:Update by * update Date 2006-06-31修改原因:涉及的表或視圖：dump_init 輔助表(DM)：記

2、錄存儲(chǔ)過(guò)程中使用的物化視圖日志序號(hào)mlog$_acrcusmrsecindex 源表(ODS)：客戶(hù)第一索引物化視圖日志,使用同義詞ft_gld_customerdata 目標(biāo)表(DM)：客戶(hù)事實(shí)表*/CREATE OR REPLACE PROCEDURE *-2）：代碼書(shū)寫(xiě)規(guī)范1. 語(yǔ)句中出現(xiàn)的所有表名、字段名全部小寫(xiě)，系統(tǒng)保留字、內(nèi)置函數(shù)名、Sql保留字大寫(xiě)。 2. 連接符or、in、and、以及、<=、>=等前后加上一個(gè)空格。3. where子句書(shū)寫(xiě)時(shí)，每個(gè)條件占一行，語(yǔ)句令起一行時(shí)，以保留字或者連接符開(kāi)始，連接符右對(duì)齊。 4. 查詢(xún)的WHERE過(guò)濾，原則應(yīng)使過(guò)濾記

3、錄數(shù)最多的條件放在最前面。 5. 多表連接時(shí)，使用表的別名來(lái)引用列。6. 查找數(shù)據(jù)庫(kù)表或視圖時(shí)，只能取出確實(shí)需要的那些字段，不要使用*來(lái)代替所有列名。7. 功能相似的過(guò)程和函數(shù)，盡量寫(xiě)到同一個(gè)包中，加強(qiáng)管理。 示例如下：BEGIN -查詢(xún)員工及對(duì)應(yīng)的部門(mén)名稱(chēng) SELECT , FROM l_dept dept, l_employee emp WHERE emp.dept_id = dept.dept_id;END;3）注釋書(shū)寫(xiě)規(guī)范為了提高可讀性，應(yīng)該使用一定數(shù)量的注釋。注釋大約占總行數(shù)的1/5。1：注釋風(fēng)格：注釋單獨(dú)成行、放在語(yǔ)句前面。 2：應(yīng)對(duì)不

4、易理解的分支條件表達(dá)式加注釋?zhuān)?：對(duì)重要的計(jì)算應(yīng)說(shuō)明其功能；4：過(guò)長(zhǎng)的函數(shù)實(shí)現(xiàn)，應(yīng)將其語(yǔ)句按實(shí)現(xiàn)的功能分段加以概括性說(shuō)明；5：每條SQL語(yǔ)句均應(yīng)有注釋說(shuō)明6：對(duì)于程序的整體功能，應(yīng)在程序開(kāi)始部分說(shuō)明，可采用單行/多行注釋。（- 或 /* */ 方式）2.命名規(guī)范命名對(duì)象規(guī)則樣例存儲(chǔ)過(guò)程、包、方法1 業(yè)務(wù)相關(guān)以模塊代碼開(kāi)頭gld_assist_check_p2 如果區(qū)分全量和增量，在最后加標(biāo)識(shí)gld_load_to_etlgld_load_to_etl_full3 全局使用，以global開(kāi)頭global_procedure_check變量以 v 開(kāi)頭v_updatemode1游標(biāo)以 c 開(kāi)頭c

5、_tablist內(nèi)存表以 m 開(kāi)頭m_table1臨時(shí)表以 t 開(kāi)頭t_tmpTable存儲(chǔ)過(guò)程技術(shù)1存儲(chǔ)過(guò)程樣例CREATE OR REPLACE PROCEDURE example ( v_input IN NUMBER,-輸入?yún)?shù) v_output OUT NUMBER -輸出參數(shù))ISPRAGMA AUTONOMOUS_TRANSACTION; CURSOR c1-定義一個(gè)游標(biāo)，在begin之前 IS SELECT b.tablename mlogtable, MAX (remarks)KEEP (DENSE_RANK LAST ORDER BY starttime) remarks

6、FROM proc_log a, table_proc b WHERE TO_CHAR (starttime, 'yyyy-mm-dd') <= -轉(zhuǎn)換時(shí)間并做比較 TO_CHAR ( SYSDATE - TO_DSINTERVAL ( TO_CHAR (intervaldays) | ' 00:00:00'), 'yyyy-mm-dd' ) AND a.remarks LIKE 'SUCCEEDED:%' AND cedurename = cedurename GROUP BY b.tablename)

7、; -定義結(jié)束c1_rec c1%ROWTYPE; -定義接受游標(biāo)數(shù)據(jù)行的ROWTYPE v_mlogtable VARCHAR (30); v_postperiod CHAR (2); v_acctbalbeginseq NUMBER; v_systime DATE;BEGIN v_input := 0;-變量賦值 v_systime := SYSDATE; OPEN c1;-打開(kāi)游標(biāo) LOOP-循環(huán) FETCH c1 INTO c1_rec;-從當(dāng)前游標(biāo)行賦值c1_rec EXIT WHEN c1%NOTFOUND;-游標(biāo)沒(méi)有數(shù)據(jù)退出 v_mlogtable := c1_rec.mlogt

8、able;-從行取出具體數(shù)據(jù)賦給變量 CASE TRIM (LOWER (v_mlogtable)-CASE起始 WHEN 'String1'-當(dāng)條件一 THEN-做條件一工作 BEGIN v_remarks := REPLACE (v_remarks, 'AA'); END; WHEN 'String2'-當(dāng)條件二 THEN BEGIN END; ELSE-其他條件 NULL; END CASE;-CASE結(jié)束 IF (LOWER (SUBSTR (v_mlogtable, 1, 5) <> 'mlog$') THE

9、N SELECT log_table INTO v_mlogtable FROM user_snapshot_logs WHERE LOWER (MASTER) = LOWER (v_mlogtable); END IF; EXECUTE IMMEDIATE 'delete from ' | v_mlogtable | ' where sequence$ <= ' | TO_CHAR (v_lognum); EXIT WHEN 1>2;-循環(huán)跳出條件 END LOOP;-循環(huán)結(jié)束 CLOSE c1;-關(guān)閉游標(biāo) EXCEPTION WHEN OTHER

10、S THEN ROLLBACK; global_procedure_check.check_end ('checkdataerror01', v_systime, 1, SQLCODE | ' ' | SQLERRM ); RAISE; RETURN; END;END example;2基本知識(shí)1) 基本結(jié)構(gòu)-CREATE OR REPLACE PROCEDURE example（parameters）-過(guò)程聲明區(qū)IS-v_1 NUMBER;-過(guò)程中變量聲明區(qū)-BEGIN v_1 := 0;-過(guò)程內(nèi)容區(qū)END example;-2) 基本類(lèi)型CHAR固定長(zhǎng)度字符

11、類(lèi)型VARCHAR2可變長(zhǎng)字符類(lèi)型VARCHAR可變長(zhǎng)字符類(lèi)型(不建議使用)NUMBER一切數(shù)值類(lèi)型DATE一切日期類(lèi)型3) 參數(shù)三種：IN 輸入?yún)?shù)，OUT輸出參數(shù)，IN OUT 輸入輸出參數(shù)。4) 變量的聲明在變量聲明區(qū)聲明變量的名稱(chēng)和類(lèi)型例：v_postperiod CHAR (2);可賦初值v_postperiod CHAR (2):=01;（這里叫變量聲明區(qū)可能并不恰當(dāng)，因?yàn)橛螛?biāo)、自定義類(lèi)型等，一切需要事先聲明的都應(yīng)在這里聲明。）5) 變量的賦值使用：=為變量賦值1直接使用基本類(lèi)型賦值例：v_number := 1;2.使用函數(shù)賦值例：v_date := sysdate;3使用SQL

12、語(yǔ)句為變量賦值1通過(guò)sql直接賦值 SELECT COUNT (*) INTO v_tmpnumber FROM etl_ods_masterdata_tablist;2通過(guò)構(gòu)造SQL賦值： v_tmpsql := 'SELECT log_table FROM user_snapshot_logs' | v_dblink | ' WHERE UPPER (MASTER) = UPPER (''' | v_singletab | ''')' EXECUTE IMMEDIATE v_tmpsql INTO v_tmpv

13、archar;6) 循環(huán)1. 無(wú)限或簡(jiǎn)單循環(huán)LOOPEXIT WHEN (退出循環(huán)條件);END LOOP;2. while循環(huán)WHILE conditionLOOP executable_statements;END LOOP;3. for循環(huán)基于數(shù)字的for循環(huán)：FOR for_index IN low_value . high_valueLOOP executable_statements;END LOOP; 基于游標(biāo)的for循環(huán)：FOR record_index IN my_cursorLOOP executable_statements;END LOOP;7) 調(diào)用其他過(guò)程或方法1如

14、果單獨(dú)定義，直接使用例：v_retval0 := f_dump_init (v_updatemode, v_systime, 'mlog$_glddocheader', v_procname, v_docheaderbeginseq, v_docheaderendseq );2如果定義在包下，使用包名+過(guò)程名例： global_procedure_check.check_run (v_procname)；3固定用法和函數(shù)標(biāo)識(shí)作用用法或類(lèi)型固定用法：SYSDATE當(dāng)前系統(tǒng)時(shí)間DATESQLCODE異常代碼VARCHAR2SQLERRM異常描述VARCHAR2NO_DATA_FOU

15、ND未找到數(shù)據(jù)異常與 when 搭配OTHERS其他所有異常與 when 搭配RAISE拋出當(dāng)前異常RAISE;DENSE_RANK非選取字段排序MIN(B) KEEP (DENSE_RANK FIRST ORDER BY A)MAX(B) KEEP (DENSE_RANK LAST ORDER BY A)PRAGMA AUTONOMOUS_TRANSACTIONBULK COLLECT INTOSQL%ROWCOUNT使用自治事務(wù)，可以使該過(guò)程被調(diào)用時(shí)單獨(dú)提交Begin之前使用 PRAGMA AUTONOMOUS_TRANSACTION;將前面執(zhí)行結(jié)果大批放入后面的集合中BULK COLLE

16、CT INTO columntab;前一個(gè)DML語(yǔ)句執(zhí)行影響行數(shù)作為NUMBER型使用v_number:= SQL%ROWCOUNTDBMS_OUTPUT.put_line（）輸出信息函數(shù)TO_CHAR轉(zhuǎn)換NCHAR、NVARCHAR2、CLOB、NCLOBTO_CHAR（A）轉(zhuǎn)換DATE型為指定格式TO_CHAR (time, 'yyyy-mm-dd')轉(zhuǎn)換NUMBER型為指定格式TO_CHAR (564.70, '$999.9')TO_DATE轉(zhuǎn)換字符串為指定日期to_date('1900-01-01','YYYY-MM-DD'

17、;)INSTR(string,substring(,postion)(, occurrence)返回目標(biāo)字符串中子字符串的位置。（起始位置和出現(xiàn)次數(shù)為可選）INSTR ('bug- archie', 'archie')INSTR ('haracter?archie', 'a', 1, 2)LENGTH獲得指定字符串長(zhǎng)度LENGTH('CANDIDE')LOWER將指定字符串轉(zhuǎn)換成小寫(xiě)LOWER ('LETTERS')UPPER將指定字符串轉(zhuǎn)換成大寫(xiě)UPPER ('letters')L

18、PAD(str1,n,str2)將str1用str2左補(bǔ)齊至n位LPAD ('55', 10, '0')RPAD(str1,n,str2)將str1用str2右補(bǔ)齊至n位RPAD ('55', 10, '0')LTRIM去掉指定字符串左側(cè)的指定字符或字符集合，默認(rèn)為空格LTRIM (' Way')LTRIM ('123123Way','123')RTRIM去掉指定字符串右側(cè)的指定字符或字符集合，默認(rèn)為空格RTRIM ('Way xyXxyxy','xy'

19、;)POWER（m,n）計(jì)算m的n次方POWER(2,3)Extract (year from date)取出date的年4ROWTYPE的使用可以使用%type 和% rowtype屬性實(shí)現(xiàn)使用其他變量、數(shù)據(jù)庫(kù)列或表的數(shù)據(jù)類(lèi)型的引用。%type屬性提供了所需要的變量的類(lèi)型及長(zhǎng)度。% rowtype屬性允許人們定義一個(gè)記錄變量，它的成員變量擁有表中每一列正確的類(lèi)型及長(zhǎng)度,使用點(diǎn)符號(hào)引用記錄中的每個(gè)成員變量。這種動(dòng)態(tài)賦值方法是非常有用的，比如變量引用的列的數(shù)據(jù)類(lèi)型和大小改變了，如果使用了%TYPE,那么用戶(hù)就不必修改代碼，否則就必須修改代碼。 CREATE TABLE EMPLOYEE ( EM

20、P_ID NUMBER NOT NULL, EMP_NAME CHAR (20), CREATE_DATE DATE）DECLARE v_studentrecord employee%ROWTYPE; n employee.create_date%TYPE;BEGIN SELECT * INTO v_studentrecord FROM employee WHERE emp_id = 1; n := v_studentrecord.create_date; DBMS_OUTPUT.put_line (n);END;5內(nèi)存表的使用內(nèi)存表主要作為數(shù)組用。1）：一個(gè)字段：PROCEDURE t1IS

21、 TYPE t_c IS TABLE OF testa.a1%TYPE INDEX BY BINARY_INTEGER; aa t_c;BEGIN aa (0) := 'aaa' DBMS_OUTPUT.put_line (aa (0);END;2）：定義多個(gè)字段：PROCEDURE t1IS TYPE t_r IS RECORD ( t1 VARCHAR (10), t2 VARCHAR (10) ); TYPE t_t IS TABLE OF t_r INDEX BY BINARY_INTEGER; aa t_t;BEGIN aa (0).t1 := 'aaa

22、9; aa (0).t2 := 'bbb' DBMS_OUTPUT.put_line (aa (0).t1); DBMS_OUTPUT.put_line (aa (0).t2);END;6游標(biāo)的使用游標(biāo)是用來(lái)處理使用SELECT語(yǔ)句從數(shù)據(jù)庫(kù)中檢索到的多行記錄的工具。借助于游標(biāo)的功能，數(shù)據(jù)庫(kù)應(yīng)用程序可以對(duì)一組記錄逐個(gè)進(jìn)行處理，每次處理一行。DECLARE n NUMBER; CURSOR c IS SELECT * FROM employee;BEGIN FOR v_c IN c LOOP n := v_c.emp_id; DBMS_OUTPUT.put_line (n); EN

23、D LOOP;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('error');END;7跟蹤調(diào)試根蹤調(diào)試主要是檢查程序運(yùn)行的情況，可以在需要檢查程序是否執(zhí)行正確作為輸出的依據(jù)：DBMS_OUTPUT.PUT_LINE(G_USERID(-2);執(zhí)行時(shí)設(shè)置：set serveroutput on8臨時(shí)表臨時(shí)表用于保存事務(wù)或者會(huì)話(huà)的中間結(jié)果，臨時(shí)表中保存的數(shù)據(jù)只有對(duì)當(dāng)時(shí)的會(huì)話(huà)是可見(jiàn)的，任何會(huì)話(huà)都不能看見(jiàn)其他會(huì)話(huà)的數(shù)據(jù)。即使COMMIT之后也是不可見(jiàn)的。對(duì)于臨時(shí)表并行不是問(wèn)題，即使鎖定也不能阻止其他程序的訪(fǎng)問(wèn)。每個(gè)數(shù)據(jù)庫(kù)創(chuàng)建臨時(shí)表一

24、次，（ORACLE 的DDL語(yǔ)句是一種消耗較大的動(dòng)作）并不用每個(gè)程序創(chuàng)建一次，并且臨時(shí)表總保持為空。下面這個(gè)例子可以說(shuō)明臨時(shí)表的運(yùn)行過(guò)程：CREATE GLOBAL TEMPORARY TABLE REPDB.L_EMP_DEPT_TEMP(EMP_IDVARCHAR(5),EMP_NAMEVARCHAR(20),DEPT_IDVARCHAR(5),DEPT_NAMEVARCHAR(20) 1 DECLARE 2 D L_EMP_DEPT_TEMP%ROWTYPE; 3 CURSOR C IS 4 SELECT E.EMP_ID AA ,E.EMP_NAME BB ,D.DEPT_ID CC

25、,D.NAME DD 5 FROM L_EMPLOYEE E,L_DEPT D 6 WHERE E.DEP_ID=D.DEPT_ID; 7 BEGIN 8 FOR V_C IN C LOOP 9 INSERT INTO L_EMP_DEPT_TEMP 10 VALUES (V_C.AA,V_C.BB,V_C.CC,V_C.DD); 11 END LOOP; 12* END ;SQL> /PL/SQL 過(guò)程已成功完成。SQL> SELECT COUNT(*) 2 FROM L_EMP_DEPT_TEMP 3 / COUNT(*)- 3SQL> COMMIT 2 /提交完成。SQ

26、L> SELECT COUNT(*) 2 FROM L_EMP_DEPT_TEMP 3 / COUNT(*)- 09異常處理例外是一個(gè)非致命事件，它立即中斷程序的正常執(zhí)行并引起一個(gè)非條件轉(zhuǎn)移，跳轉(zhuǎn)到當(dāng)前程序塊的例外處理部分。一些例外，像NO_DATE_FOUND或TO_MANY_ROWS，屬于預(yù)定義例外用于處理常見(jiàn)的oracle錯(cuò)誤，可以被認(rèn)為是正常的處理部分。部分ERROR這樣的例外表明一個(gè)程序錯(cuò)誤或一些意料之外的事件。如下所示：1）：正常處理的部分 1 DECLARE 2 N CHAR; 3 BEGIN 4 SELECT EMP_NAME 5 INTO N 6 FROM EMPLOY

27、EE; 7 DBMS_OUTPUT.PUT_LINE('N'); 8* END;SQL> /DECLARE*第 1 行出現(xiàn)錯(cuò)誤:ORA-01422: 實(shí)際返回的行數(shù)超出請(qǐng)求的行數(shù)ORA-06512: 在 line 4 1 DECLARE 2 N CHAR; 3 BEGIN 4 SELECT EMP_NAME 5 INTO N 6 FROM EMPLOYEE; 7 DBMS_OUTPUT.PUT_LINE(N); 8 EXCEPTION WHEN TOO_MANY_ROWS THEN 9 DBMS_OUTPUT.PUT_LINE('TOO MANY ROWS RET

28、URN'); 10* END;PL/SQL 過(guò)程已成功完成。 輸出結(jié)果為：TOO MANY ROWS RETURN2）：非正常處理的部分，自定義異常SQL> insert into l_employee 2 values ('4','dd','3',sysdate,'2000') 3 /insert into l_employee*第 1 行出現(xiàn)錯(cuò)誤:ORA-02291: 違反完整約束條件 (REPDB.FK_EMP_DEPT) - 未找到父項(xiàng)關(guān)鍵字處理方法：自定義異常 1 declare 2 e exception

29、 ; 3 pragma exception_init(e,-2291); 4 begin 5 insert into l_employee 6 values ('6','dd','3',sysdate,'2000') ; 7 exception when e then 8 DBMS_OUTPUT.PUT_LINE('違反完整約束條件 (REPDB.FK_EMP_DEPT)'); 9* end ;SQL> /PL/SQL 過(guò)程已成功完成。 輸出結(jié)果為： 違反完整約束條件 (REPDB.FK_EMP_DEPT)

30、10嵌套程序塊的內(nèi)部可以有另一個(gè)程序塊這種情況稱(chēng)為嵌套。嵌套要注意的是變量，定義在最外部程序塊中的變量可以在所有子塊中使用，如果在子塊中定義了與外部程序塊變量相同的變量名，在執(zhí)行子塊時(shí)將使用子塊中定義的變量。子塊中定義的變量不能被父塊引用。如果字塊需要單獨(dú)提交，應(yīng)使用自治事務(wù)。11標(biāo)簽用戶(hù)可以使用標(biāo)簽使程序獲得更好的可讀性。程序塊或循環(huán)都可以被標(biāo)記。標(biāo)簽的形式是<>。要求使用標(biāo)簽。12記錄轉(zhuǎn)儲(chǔ)開(kāi)始時(shí)間和結(jié)束時(shí)間1）在建立中間表后，用腳本或手工在數(shù)據(jù)表中建立一條記錄，以后每次都更新。對(duì)每個(gè)轉(zhuǎn)儲(chǔ)只記錄一條記錄，不保存歷史記錄。2）在開(kāi)始轉(zhuǎn)儲(chǔ)時(shí)，讀取上次轉(zhuǎn)儲(chǔ)結(jié)束時(shí)間，只轉(zhuǎn)儲(chǔ)從上次轉(zhuǎn)儲(chǔ)以來(lái)

31、的新增或修改的記錄。3）在轉(zhuǎn)儲(chǔ)的存儲(chǔ)過(guò)程中記錄開(kāi)始時(shí)間和結(jié)束時(shí)間，是否成功。如果失敗，記錄失敗原因。4）可以用SQL語(yǔ)句查找失敗的轉(zhuǎn)儲(chǔ)，可以查找轉(zhuǎn)儲(chǔ)時(shí)間過(guò)長(zhǎng)的轉(zhuǎn)儲(chǔ)。表名：轉(zhuǎn)儲(chǔ)記錄（TRANSLOG）字段：程序包名存儲(chǔ)過(guò)程名中間表名開(kāi)始時(shí)間結(jié)束時(shí)間成功標(biāo)識(shí)失敗原因13授權(quán)grant select on cs_new.ACPStorkFlAssAnaTab to repdbnewrevoke select on cs_new.BILInvoiceUseEntityData from repdbnew注意：不能為當(dāng)前用戶(hù)授權(quán)14建立同義詞CREATE SYNONYM ACPStorkFlAssAna

32、Tab FOR jcerp.ACPStorkFlAssAnaTabdrop SYNONYM BILInvoiceUseEntityData注：建立同義詞后用戶(hù)可以用select訪(fǎng)問(wèn)，但不能建立視圖。15為表字段加注釋COMMENT ON COLUMN HAN_2.BBBB IS 'B字段'16觸發(fā)器create or replace trigger qytest1_trigger3 BEFORE INSERT ON mlog$_qytest1FOR EACH ROWbegin:new.snaptime$ := SYSDATE;end;17自定義類(lèi)型的賦值自定義類(lèi)型：CREATE

33、 OR REPLACE TYPE repdbnew.INPARAM AS VARRAY(50) OF VARCHAR2(25); 自定義類(lèi)型的賦值： declare indataname inparam; begin indataname := inparam(2); indataname:= inparam('isCalCount','NO_TX'); end; 18OBJECT TYPES簡(jiǎn)單來(lái)說(shuō)，Oracle Object Types 就是 Oracle 以 TYPE 的方式來(lái)實(shí)現(xiàn)物件(Objects)的方法，宣告/定義的方法，類(lèi)似於 Package。Ob

34、ject Type 的宣告/定義中包含了它的 Attributes/Properties 與 Methods，也就是 Member Functions/Procedures。本篇來(lái)介紹利用 Oracle Object Types 來(lái)做中介暫存的實(shí)作。建立 Oracle Object:定義這個(gè) Object 的內(nèi)容，可以把它想像為所希望的 Row Columns 的定義。view plaincopy to clipboardprint?CREATE TYPE type_obj AS OBJECT ( col1 INTEGER, col2 VARCHAR2(60) ); / CREATE TYPE

35、 type_obj AS OBJECT ( col1 INTEGER, col2 VARCHAR2(60);/建立 Object Collection:建立一個(gè) Table Type，這個(gè) Table 裝的資料列內(nèi)容(欄位)就是之前所建立的 Object。view plaincopy to clipboardprint?CREATE OR REPLACE TYPE type_tab IS TABLE OF type_obj; / CREATE OR REPLACE TYPE type_tab IS TABLE OF type_obj;/在 PL/SQL 中的應(yīng)用:定義好前面兩個(gè) Type，就可

36、以拿它來(lái)在程式中實(shí)作了。view plaincopy to clipboardprint?DECLARE - 宣告與初始化 obj_type_tab type_tab := type_tab(); BEGIN - 多筆給值 obj_type_tab := type_tab ( type_obj ( 37, 'col2_val1'), type_obj ( 3, 'col2_val2'), type_obj (254, 'col2_val3'); - 逐筆給值 obj_type_tab.EXTEND; obj_type_tab(obj_type_t

37、ab.LAST) := type_obj( 12, 'col_2_val'); /* 要逐筆給或一次給多筆就純粹看使用時(shí)機(jī)與方便性. 舉例來(lái)說(shuō), 已經(jīng)有確切可知的數(shù)筆資料時(shí), 就可以用多筆給的方式來(lái)定義使用. 但是, 如果資料來(lái)源是取自其他媒介, 比方說(shuō) Cursor, 可能就需要跑 Loop 去逐筆取值與給值了. */ /* 再來(lái), 可以把已經(jīng)存放好資料的 obj_type_tab, 像下面這種應(yīng)用方式去取回資料. 當(dāng)然, 舉一反三, 它可以用這樣子的 SELECT 方式轉(zhuǎn)化成 Reference Cursor 及其他相關(guān)應(yīng)用. */ FOR rec IN (SELECT c

38、ol1, col2 FROM TABLE(CAST(obj_type_tab AS TYPE_TAB) LOOP DBMS_OUTPUT.put_line(' rec.col1 = ' | rec.col1 | ' rec.col2 = ' | rec.col2); END LOOP; /* 輸出結(jié)果: rec.col1 = 37; rec.col2 = col2_val1 rec.col1 = 3; rec.col2 = col2_val2 rec.col1 = 254; rec.col2 = col2_val3 rec.col1 = 12; rec.col2

39、 = col_2_val */ END; 19Tabletype的使用定義：resultset OUT tabletype使用：OPEN resultset FOR SELECT DISTINCT cname, b.paramneeded FROM temp_tablenames a, table_proc b WHERE UPPER (a.tablename) = b.tablename;查詢(xún)：DECLARE TABNAMES TABNAMES_OBJ; RESULTSET GETTABLENAMES.TABLETYPE; w_id VARCHAR2 (30); h_id VARC

40、HAR2 (30);BEGIN TABNAMES := (TABNAMES_OBJ (tabname_list_typ ('REP_FT_CO_CPCB_V'); GETTABLENAMES.GETTABLESOFVIEW (TABNAMES, RESULTSET); LOOP FETCH RESULTSET INTO w_id, h_id; EXIT WHEN RESULTSET%NOTFOUND; DBMS_OUTPUT.put_line (w_id | ',' | h_id); END LOOP; COMMIT;END;20創(chuàng)建DB-Linkcreate

41、database link "erpcar"-db link 名稱(chēng)，與連接實(shí)例名相同connect to jferp -連接使用用戶(hù)identified by "qmnerp" -用戶(hù)密碼using 'ERPCAR' -連接實(shí)例21SQL優(yōu)化1) SELECT子句中避免使用 *當(dāng)你想在SELECT子句中列出所有的COLUMN時(shí),使用動(dòng)態(tài)SQL列引用 *' 是一個(gè)方便的方法.不幸的是,這是一個(gè)非常低效的方法. 實(shí)際 上,ORACLE在解析的過(guò)程中, 會(huì)將'*' 依次轉(zhuǎn)換成所有的列名, 這個(gè)工 作是通過(guò)查詢(xún)數(shù)據(jù)字典完成

42、的, 這意味著將耗費(fèi)更多的時(shí)間.2) 盡量多使用COMMIT只要有可能,在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,需求也會(huì)因?yàn)镃OMMIT所釋放的資源而減少:COMMIT所釋放的資源:a. 回滾段上用于恢復(fù)數(shù)據(jù)的信息.b. 被程序語(yǔ)句獲得的鎖c. redo log buffer 中的空間d. ORACLE為管理上述3種資源中的內(nèi)部花費(fèi)3) 減少對(duì)表的查詢(xún)次數(shù)在含有子查詢(xún)的SQL語(yǔ)句中,要特別注意減少對(duì)表的查詢(xún).1)例如:低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NAME = ( SELECT TAB_NAMEFROM TAB_COLUMNSWH

43、ERE VERSION = 604)AND DB_VER= ( SELECT DB_VERFROM TAB_C0LUMNSWHERE VERSION = 604)高效SELECT TAB_NAMEFROM TABLESWHERE (TAB_NAME,DB_VER)= ( SELECT TAB_NAME,DB_VER)FROM TAB_COLUMNSWHERE VERSION = 604)2)Update 多個(gè)Column 例子:低效:UPDATE EMPSET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),SAL_RANGE = (S

44、ELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;高效:UPDATE EMPSET (EMP_CAT, SAL_RANGE)= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;4) 用EXISTS替代IN在許多基于基礎(chǔ)表的查詢(xún)中,為了滿(mǎn)足一個(gè)條件,往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢(xún)的效率.低效:SELECT *FROM EMP (基礎(chǔ)表)WHE

45、RE EMPNO > 0AND DEPTNO IN (SELECT DEPTNOFROM DEPTWHERE LOC = MELB')高效:SELECT *FROM EMP (基礎(chǔ)表)WHERE EMPNO > 0AND EXISTS (SELECT X'FROM DEPTWHERE DEPT.DEPTNO = EMP.DEPTNOAND LOC = MELB')用IN來(lái)替換OR下面的查詢(xún)可以被更有效率的語(yǔ)句替換:低效:SELECT.FROM LOCATIONWHERE LOC_ID = 10OR LOC_ID = 20OR LOC_ID = 30高效SEL

46、ECTFROM LOCATIONWHERE LOC_IN IN (10,20,30);5) 用Where子句替換HAVING子句：避免使用HAVING子句， HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過(guò)濾。這個(gè)處理需要排序，總計(jì)等操作。 如果能通過(guò)WHERE子句限制記錄的數(shù)目，那就能減少這方面的開(kāi)銷(xiāo)。例如:低效:SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION != SYDNEY' AND REGION != PERTH'高效SELECT REGION，AVG(LOG_SIZ

47、E) FROM LOCATION WHERE REGION REGION != SYDNEY'AND REGION != PERTH' GROUP BY REGIONHAVING 中的條件一般用于對(duì)一些集合函數(shù)的比較，如COUNT() 等等。除此而外，一般的條件應(yīng)該寫(xiě)在WHERE子句中。其他相關(guān)技術(shù)1. 作業(yè)調(diào)度A. 說(shuō)明1）定時(shí)執(zhí)行數(shù)據(jù)轉(zhuǎn)儲(chǔ)：用ORACLE的作業(yè)調(diào)度的功能。2）定義作業(yè)：通過(guò)WEB方式的ORACLE管理界面定義作業(yè)。作業(yè)名所有者命令類(lèi)型：PL/SQL塊PL/SQL：包名。過(guò)程名如果包不屬于定義的所有者，需要指定包所在用戶(hù)名。beginSYLTEST.TESTS

48、CHE;end;調(diào)度：按每天的方式執(zhí)行，也可以按每月的方式執(zhí)行3）包的執(zhí)行順序如果多個(gè)包有依賴(lài)關(guān)系，可以用存儲(chǔ)過(guò)程定義執(zhí)行順序（在定時(shí)器中不能定義）。4）對(duì)作業(yè)的監(jiān)控在ORACLE的管理界面中有日志B. 使用TOAD實(shí)現(xiàn)作業(yè)調(diào)度：方式一：直接定義Jobs（作業(yè)）1選擇創(chuàng)建一個(gè)作業(yè)，輸入名稱(chēng)2輸入首次執(zhí)行時(shí)間(First Excution)3輸入執(zhí)行頻率(Subsequent Excution)如：每天早上六點(diǎn)執(zhí)行：TRUNC(SYSDATE+1)+6/24注意：當(dāng)作業(yè)成功完成時(shí)，系統(tǒng)才會(huì)計(jì)算下一次執(zhí)行時(shí)間（SYSDATE+1）4選擇單選框“Parse”（默認(rèn)）在定義作業(yè)的時(shí)候解析存儲(chǔ)過(guò)程或選擇

49、“No Parse”在執(zhí)行作業(yè)的時(shí)候解析存儲(chǔ)過(guò)程5最后在“What to excute”欄中輸入作業(yè)的具體內(nèi)容或點(diǎn)擊該區(qū)域的右上角按鈕選擇一個(gè)存儲(chǔ)過(guò)程。優(yōu)點(diǎn)：適用不頻繁改動(dòng)或不改動(dòng)作業(yè)本身即作業(yè)執(zhí)行頻率的情況。缺點(diǎn)：作業(yè)定義與作業(yè)執(zhí)行頻率在一起定義不利于管理。方式二：定義Sched.Jobs（作業(yè)調(diào)度）1 新建一個(gè)Program（相當(dāng)于作業(yè)內(nèi)容）或Schedule（相當(dāng)于作業(yè)運(yùn)行時(shí)間和頻率）（此過(guò)程可選）2 新建一個(gè)Sched.Jobs，輸入名稱(chēng)3 在Progam Info的Tab頁(yè)選擇存儲(chǔ)過(guò)程（Specify Program Info）或預(yù)定義的Program（Use Predefined Program）4 在Schedule Info 的Tab頁(yè)輸入開(kāi)始、結(jié)束時(shí)間和運(yùn)行頻率（Spesify Schedule Info）（如下例：）或預(yù)定義的Schedule（Use Predefined Schedule）。例：Start Date:2008/01/09 02:00:00.000000 +08:00End Date:2009/01/09 08:00:00.000000 +08:00Repeat Interval:FREQ=DAILY;INTERVAL=1優(yōu)點(diǎn)：1 作業(yè)和作業(yè)執(zhí)行頻率可以分開(kāi)