Oracle存儲過程開發(fā)規(guī)范與技巧

1、存儲過程開開發(fā)規(guī)范與與技巧開發(fā)規(guī)范1.書寫規(guī)規(guī)范1）：程序序頭書寫規(guī)規(guī)范程序頭開始始部分應(yīng)說明程序序整體的功功能，存儲儲過程名稱稱，編寫人人，編寫日日期，修改改人，修改改日期，版版本號以及過程程涉及的表表和視圖。示例如下下：-/*名稱及實現(xiàn)現(xiàn)功能: 版本: ( 版本號號標(biāo)示：新新建 V11.0.00 小的的修改變?yōu)闉閂1.00.1 大大的修改VV1.1.0 重構(gòu)構(gòu)V2.00.0)Creatte by * Creaate Datee 20066-06-29Updatte by * uppdatee Datte 20066-06-30修改原因:Updatte by * uppdatee Datte

2、 20066-06-31修改原因:涉及的表或或視圖：dump_initt 輔助表(DDM)：記記錄存儲過過程中使用用的物化視視圖日志序序號mlog$_acrrcusmmrseccindeex 源表(ODDS)：客客戶第一索索引物化視視圖日志,使用同義義詞ft_glld_cuustommerdaata 目標(biāo)表(DDM)：客客戶事實表表*/CREATTE OR REPLLACE PROCCEDURRE *-2）：代碼碼書寫規(guī)范范語句中出現(xiàn)現(xiàn)的所有表表名、字段段名全部小小寫，系統(tǒng)統(tǒng)保留字、內(nèi)置函數(shù)數(shù)名、Sqql保留字字大寫。 連接符orr、in、and、以及、=等前后后加上一個個空格。wheree子

3、句書寫寫時，每個個條件占一一行，語句句令起一行行時，以保保留字或者者連接符開開始，連接接符右對齊齊。查詢的WHHERE過過濾，原則則應(yīng)使過濾濾記錄數(shù)最最多的條件件放在最前前面。 多表連接時時，使用表表的別名來來引用列。查找數(shù)據(jù)庫庫表或視圖圖時，只能能取出確實實需要的那那些字段，不不要使用*來代替所所有列名。功能相似的的過程和函函數(shù)，盡量量寫到同一一個包中，加加強(qiáng)管理。 示示例如下：BEGINN -查詢詢員工及對對應(yīng)的部門門名稱 SELLECT e, e FFROM l_deept ddept, l_emmployyee eemp WHHERE emp.dep

4、tt_id = deppt.deptt_id;END;3）注釋書書寫規(guī)范為了提高可可讀性，應(yīng)應(yīng)該使用一一定數(shù)量的的注釋。注注釋大約占占總行數(shù)的的1/5。1：注釋風(fēng)風(fēng)格：注釋釋單獨(dú)成行行、放在語語句前面。2：應(yīng)對不不易理解的的分支條件件表達(dá)式加加注釋；3：對重要要的計算應(yīng)應(yīng)說明其功功能；4：過長的的函數(shù)實現(xiàn)現(xiàn)，應(yīng)將其其語句按實實現(xiàn)的功能能分段加以以概括性說說明；5：每條SSQL語句句均應(yīng)有注注釋說明6：對于程程序的整體體功能，應(yīng)應(yīng)在程序開開始部分說說明，可采采用單行/多行注釋釋。（- 或 /* */ 方式）2.命名規(guī)規(guī)范命名對象規(guī)則樣例存儲過程、包、方法法1 業(yè)務(wù)相相關(guān)以模塊塊代碼開頭頭gld_

5、aassisst_chheck_p2 如果區(qū)區(qū)分全量和和增量，在在最后加標(biāo)標(biāo)識gld_lload_to_eetlgld_lload_to_eetl_ffull3 全局使使用，以gglobaal開頭globaal_prroceddure_checck變量以 v 開開頭v_upddatemmode11游標(biāo)以 c 開開頭c_tabblistt內(nèi)存表以 m 開開頭m_tabble1臨時表以 t 開開頭t_tmppTablle存儲過程技技術(shù)1存儲過過程樣例CREATTE OR REPLLACE PROCCEDURRE exammple ( v_inpuut IN NUMBBER,-輸入入?yún)?shù) v_out

6、pput OUT NUMBBER -輸輸出參數(shù))ISPRAGMMA AUUTONOOMOUSS_TRAANSACCTIONN; CUURSORR c1-定義義一個游標(biāo)標(biāo)，在beegin之之前 ISS SELLECT b.tabllenamme mloggtablle, MAAX (remarrks)KKEEP (DENNSE_RRANK LASTT ORDEER BY sttartttime) remaarks FROOM procc_logg a, tablle_prroc bb WHEERE TTO_CHHAR (starrttimme, yyyyy-mmm-dd) = -轉(zhuǎn)換換時間并做做

7、比較 TO_CCHAR ( SYYSDATTE - TO_DDSINTTERVAAL ( TO_CHHAR (inteervalldayss) | 000:00:00), yyyyy-mmm-dd ) ANND a.remaarks LIKEE SUCCCEEDDED:% ANND ccedurrenamme = ccedurrenamme GROOUP BY b.tabllenamme); -定義義結(jié)束c1_reec c1%ROWTTYPE; -定義義接受游標(biāo)標(biāo)數(shù)據(jù)行的ROWTTYPE v_mloggtablle VARCCHAR (30); v_posttperiiod

8、 CHARR (2); v_accttbalbbeginnseq NUMBBER; v_systtime DATEE;BEGINN v_inpuut := 0;-變量量賦值 v_systtime := SYYSDATTE; OPPEN cc1;-打開開游標(biāo) LOOOP-循環(huán)環(huán) FETTCH cc1 INTOO c1_rec;-從當(dāng)當(dāng)前游標(biāo)行行賦值c11_recc EXIIT WHENN c1%NNOTFOOUND;-游標(biāo)標(biāo)沒有數(shù)據(jù)據(jù)退出 v_mmlogttablee := c11_recc.mloggtablle;-從行行取出具體體數(shù)據(jù)賦給給變量 CASSE TRRIM (LOWEER (v_

9、mllogtaable)-CAASE起始始 WHENN Striing1-當(dāng)條條件一 THENN-做條條件一工作作 BBEGINN v_remaarks := REPLLACE (v_reemarkks, AA); EEND; WHENN Striing2-當(dāng)條條件二 THENN BBEGINN EEND; ELSEE-其他他條件 NNULL; ENDD CASEE;-CAASE結(jié)束束 IF (LOWEER (SUBSSTR (v_mllogtaable, 1, 5) mloog$) THEEN SELEECT llog_ttablee INNTO vv_mloogtabble FRROM u

10、user_snappshott_loggs WHEERE LLOWERR (MAASTERR) = LOWWER (v_mllogtaable); ENDD IF; EXEECUTEE IMMEEDIATTE deeletee froom | v_mloggtablle | wherre seequennce$ 2;-循環(huán)環(huán)跳出條件件 ENND LOOPP;-循環(huán)環(huán)結(jié)束 CLLOSE c1;-關(guān)閉閉游標(biāo) EXXCEPTTION WHEEN OTHEERS THEEN ROLLLBACKK; globbal_pproceeduree_cheeck.checck_ennd (cheeckdaata

11、errror001, v_systtime, 1, SQQLCODDE | | SQLEERRM ); RAISSE; RETUURN; ENND;END eexampple;2基本知知識基本結(jié)構(gòu)-CREATTE OR REPLLACE PROCCEDURRE exammple（pparammeterrs）-過程程聲明區(qū)IS-v_1 NNUMBEER;-過程程中變量聲聲明區(qū)-BEGINN v_1 := 0;-過程內(nèi)內(nèi)容區(qū)END eexampple;-基本類型CHAR固定長度度字符類型型VARCHHAR2可變長字字符類型VARCHHAR可變長字字符類型(不建議使使用)NUMBEER一切數(shù)值值類型

12、DATE一切日期期類型參數(shù)三種：INN 輸入?yún)?shù)，OUUT輸出參參數(shù)，INN OUTT 輸入輸輸出參數(shù)。變量的聲明明在變量聲明明區(qū)聲明變變量的名稱稱和類型例：v_ppostpperiood CCHAR (2);可賦初值v_posstperriod CHAAR (2):=01;（這里叫變變量聲明區(qū)區(qū)可能并不不恰當(dāng)，因因為游標(biāo)、自定義類類型等，一一切需要事事先聲明的的都應(yīng)在這這里聲明。）變量的賦值值使用：=為變量賦賦值1直接使使用基本類類型賦值例：v_nnumbeer := 1;2.使用函函數(shù)賦值例：v_ddate := ssysdaate;3使用SSQL語句句為變量賦賦值1通過ssql直接接賦

13、值 SELLECT COUNNT (*) IINTO v_tmmpnummber FFROM etl_ods_mastterdaata_ttabliist;2通過構(gòu)構(gòu)造SQLL賦值： v_tmmpsqll := SSELECCT loog_taable FROOM usser_ssnapsshot_logss | v_dbliink | WHERRE UPPPER (MASSTER) = UUPPERR ( | v_singgletaab | ); EXECCUTE IMMEEDIATTE v_tmpssql INTTO v_tmpvvarchhar;循環(huán)無限或簡單單循環(huán)LOOPEXIT WH

14、ENN (退退出循環(huán)條條件);END LLOOP;whilee循環(huán)WHILEE connditiionLOOP exxecuttablee_staatemeents;END LLOOP;for循環(huán)環(huán)基于數(shù)字的的for循循環(huán)：FOR ffor_iindexx IN low_valuue . higgh_vaalueLOOP exxecuttablee_staatemeents;END LLOOP; 基于游標(biāo)的的for循循環(huán)：FOR rrecorrd_inndex IN mmy_cuursorrLOOP exxecuttablee_staatemeents;END LLOOP;調(diào)用其他過過程或方法

15、法1如果單單獨(dú)定義，直直接使用例：v_rretvaal0 := ff_dummp_innit (v_uppdateemodee, v_syystimme, mloog$_gglddoocheaader, v_prrocnaame, v_doocheaaderbbeginnseq, v_doocheaadereendseeq );2如果定定義在包下下，使用包包名+過程程名例： glloball_prooceduure_ccheckk.cheeck_rrun (v_prrocnaame)；3固定用用法和函數(shù)數(shù)標(biāo)識作用用法或類型型固定用法：SYSDAATE當(dāng)前系統(tǒng)時時間DATESQLCOODE異常代

16、碼VARCHHAR2SQLERRRM異常描述VARCHHAR2NO_DAATA_FFOUNDD未找到數(shù)據(jù)據(jù)異常與 wheen 搭配配OTHERRS其他所有異異常與 wheen 搭配配RAISEE拋出當(dāng)前異異常RAISEE;DENSEE_RANNK非選取字段段排序MIN(BB) KEEEP (DENSSE_RAANK FIRSST ORDDER BBY A)MAX(BB) KEEEP (DENSEE_RANNK LASTT ORDEER BYY A)PRAGMMA AUUTONOOMOUSS_TRAANSACCTIONNBULK COLLLECT INTOOSQL%RROWCOOUNT使用自治事

17、事務(wù)，可以以使該過程程被調(diào)用時時單獨(dú)提交交Beginn之前使用用 PRAAGMA AUTOONOMOOUS_TTRANSSACTIION;將前面執(zhí)行行結(jié)果大批批放入后面面的集合中中BULK COLLLECT INTOO collumnttab;前一個DMML語句執(zhí)執(zhí)行影響行行數(shù)作為NUMMBER型型使用v_nummber:= SQL%ROWCCOUNTTDBMS_OUTPPUT.pput_lline（）輸出信息函數(shù)TO_CHHAR轉(zhuǎn)換NCHHAR、NVARRCHARR2、CLOBB、NCLOOBTO_CHHAR（AA）轉(zhuǎn)換DATTE型為指指定格式TO_CHHAR (timee, yyyyy-m

18、mm-dd)轉(zhuǎn)換NUMMBER型型為指定格格式TO_CHHAR (564.70, $9999.9)TO_DAATE轉(zhuǎn)換字符串串為指定日日期to_daate(19000-01-01,YYYYY-MMM-DD)INSTRR(striing,ssubsttringg(,poostioon)(, occuurrennce)返回目標(biāo)字字符串中子子字符串的的位置。（起起始位置和和出現(xiàn)次數(shù)數(shù)為可選）INSTRR (bugg- arrchiee, arcchie)INSTRR (harracteer?arrchiee, a, 1, 2)LENGTTH獲得指定字字符串長度度LENGTTH(CANNDIDEE)L

19、OWERR將指定字符符串轉(zhuǎn)換成成小寫LOWERR (LETTTERSS)UPPERR將指定字符符串轉(zhuǎn)換成成大寫UPPERR (lettters)LPAD(str11,n,sstr2)將str11用strr2左補(bǔ)齊至n位LPAD (55, 10, 0)RPAD(str11,n,sstr2)將str11用strr2右補(bǔ)齊至nn位RPAD (55, 10, 0)LTRIMM去掉指定字字符串左側(cè)側(cè)的指定字字符或字符符集合，默默認(rèn)為空格格LTRIMM ( Wayy)LTRIMM (1233123WWay,1233)RTRIMM去掉指定字字符串右側(cè)側(cè)的指定字字符或字符符集合，默默認(rèn)為空格格RTRIMM (

20、Wayy xyXXxyxyy,xy)POWERR（m,nn）計算m的nn次方POWERR(2,3)Extraact (yearr froom daate)取出datte的年4ROWWTYPEE的使用可以使用%typee 和% rowwtypee屬性實現(xiàn)現(xiàn)使用其他他變量、數(shù)數(shù)據(jù)庫列或或表的數(shù)據(jù)據(jù)類型的引引用。%ttype屬屬性提供了了所需要的的變量的類類型及長度度。% rowwtypee屬性允許許人們定義義一個記錄錄變量，它它的成員變變量擁有表表中每一列列正確的類類型及長度度,使用點(diǎn)點(diǎn)符號引用用記錄中的的每個成員員變量。這這種動態(tài)賦賦值方法是是非常有用用的，比如如變量引用用的列的數(shù)數(shù)據(jù)類型和和大

21、小改變變了，如果果使用了%TYPEE,那么用用戶就不必必修改代碼碼，否則就就必須修改改代碼。 CREATTE TABLLE EMPLLOYEEE ( EMPP_ID NUUMBERR NOT NULLL, EMPP_NAMME CHHAR (20), CREEATE_DATEE DAATE）DECLAARE v_studdentrrecorrd emplloyeee%ROWWTYPEE; n emplloyeee.creaate_ddate%TYPEE;BEGINN SEELECTT * INTOO v_sstudeentreecordd FROMM emplloyeee WWHEREE em

22、pp_id = 1; n := v_studdentrrecorrd.creaate_ddate; DBBMS_OOUTPUUT.put_linee (n);END;5內(nèi)存表表的使用內(nèi)存表主要要作為數(shù)組組用。1）：一個個字段：PROCEEDUREE t1IS TYYPE tt_c IIS TABLLE OF testta.a1%TYYPE INDDEX BY BINAARY_IINTEGGER; aaa tt_c;BEGINN aaa (0) := aaaa; DBBMS_OOUTPUUT.put_linee (aa (0);END;2）：定義義多個字段段：PROCEEDUREE t1IS T

23、YYPE tt_r IIS RECOORD ( t1 VAARCHAAR (10), t2 VAARCHAAR (10) ); TYYPE tt_t IIS TABLLE OF t_r INDDEX BY BINAARY_IINTEGGER; aaa tt_t;BEGINN aaa (0).t1 := aaa; aaa (0).t2 := bbbb; DBBMS_OOUTPUUT.put_linee (aa (0).t1); DBBMS_OOUTPUUT.put_linee (aa (0).t2);END;6游標(biāo)的的使用游標(biāo)是用來來處理使用用SELEECT語句句從數(shù)據(jù)庫庫中檢索到到的多行記記錄

24、的工具具。借助于于游標(biāo)的功功能，數(shù)據(jù)據(jù)庫應(yīng)用程程序可以對對一組記錄錄逐個進(jìn)行行處理，每每次處理一一行。DECLAARE n NUUMBERR; CUURSORR c ISS SELLECT * FFROM emplloyeee;BEGINN FOOR v_c INN c LOOOP n := v_c.emp_id; DBMMS_OUUTPUTT.put_linee (n); ENND LOOPP;EXCEPPTIONN WHHEN OTHEERS THHEN DBMMS_OUUTPUTT.put_linee (errror);END;7跟蹤調(diào)調(diào)試根蹤調(diào)試主主要是檢查查程序運(yùn)行行的情況，可可以在

25、需要要檢查程序序是否執(zhí)行行正確作為為輸出的依依據(jù)：DBBMS_OOUTPUUT.PUUT_LIINE(GG_USEERID(-2);執(zhí)行時設(shè)置置：sett serrverooutpuut onn8臨時表表臨時表用于于保存事務(wù)務(wù)或者會話話的中間結(jié)結(jié)果，臨時時表中保存存的數(shù)據(jù)只只有對當(dāng)時時的會話是是可見的，任任何會話都都不能看見見其他會話話的數(shù)據(jù)。即使COOMMITT之后也是是不可見的的。對于臨臨時表并行行不是問題題，即使鎖鎖定也不能能阻止其他他程序的訪訪問。每個個數(shù)據(jù)庫創(chuàng)創(chuàng)建臨時表表一次，（OORACLLE 的DDDL語句句是一種消消耗較大的的動作）并并不用每個個程序創(chuàng)建建一次，并并且臨時表表總

26、保持為為空。下面這個例例子可以說說明臨時表表的運(yùn)行過過程：CREATTE GLOBBAL TEMPPORARRY TABLLE REEPDB.L_EMMP_DEEPT_TTEMP(EMP_IDVARCCHAR(5),EMP_NAMEEVARCCHAR(20),DEPTT_IDVARCCHAR(5),DEPTT_NAMMEVARCCHAR(20) 1 DECCLAREE 2 D LL_EMPP_DEPPT_TEEMP%RROWTYYPE; 3 CURRSOR C ISS 4 SELLECT E.EMMP_IDD AA ,E.EEMP_NNAME BB ,D.DEEPT_IID CCC ,D.NA

27、MEE DD 5 FROOM L_EMPLLOYEEE E,LL_DEPPT D 6 WHEERE EE.DEPP_ID=D.DEEPT_IID; 7 BEGGIN 8 FORR V_C INN C LLOOP 9 INSSERT INTOO L_EMP_DEPTT_TEMMP 10 VALLUES (V_CC.AA,V_C.BB,VV_C.CCC,V_C.DDD); 11 ENDD LOOOP; 12* ENDD ;SQL /PL/SQQL 過程程已成功完完成。SQL SELEECT CCOUNTT(*) 2 FROMM L_EEMP_DDEPT_TEMPP 3 / COUUNT(*)- 3

28、SQL COMMMIT 2 /提交完成。SQL SELEECT CCOUNTT(*) 2 FROMM L_EEMP_DDEPT_TEMPP 3 / COUUNT(*)- 09異常處處理例外是一個個非致命事事件，它立立即中斷程程序的正常常執(zhí)行并引引起一個非非條件轉(zhuǎn)移移，跳轉(zhuǎn)到到當(dāng)前程序塊的的例外處理理部分。一一些例外，像像NO_DDATE_FOUNND或TOO_MANNY_ROOWS，屬屬于預(yù)定義義例外用于于處理常見見的oraacle錯誤誤，可以被被認(rèn)為是正正常的處理理部分。部部分ERRROR這樣樣的例外表表明一個程程序錯誤或或一些意料料之外的事事件。如下下所示：1）：正常常處理的部部分 1 D

29、ECLLARE 2 N CHHAR; 3 BEGIIN 4 SELEECT EEMP_NNAME 5 INTOO N 6 FROMM EMPPLOYEEE; 7 DBMSS_OUTTPUT.PUT_LINEE(N); 8* END;SQL /DECLAARE*第 1 行行出現(xiàn)錯誤誤:ORA-0014222: 實際際返回的行行數(shù)超出請請求的行數(shù)數(shù)ORA-0065122: 在 linee 4 1 DECLLARE 2 N CHHAR; 3 BEGIIN 4 SELEECT EEMP_NNAME 5 INTOO N 6 FROMM EMPPLOYEEE; 7 DBMSS_OUTTPUT.PUT_LI

30、NEE(N); 8 EXCEEPTIOON WHHEN TTOO_MMANY_ROWSS THEEN 9 DBMSS_OUTTPUT.PUT_LINEE(TOOO MAANY RROWS RETUURN); 10* END;PL/SQQL 過程程已成功完完成。 輸輸出結(jié)果為為：TOOO MANNY ROOWS RRETURRN2）：非正正常處理的的部分，自自定義異常常SQL inseert iinto l_emmployyee 2 valuues (4,dd,3,syssdatee,20000) 3 /inserrt innto ll_empployeee*第 1 行行出現(xiàn)錯誤誤:ORA-00

31、22911: 違反反完整約束束條件 (REPDDB.FKK_EMPP_DEPPT) - 未找到到父項關(guān)鍵鍵字處理方法：自定義異異常 1 decllare 2 e exxcepttion ; 3 praggma eexcepptionn_iniit(e,-22991); 4 begiin 5 inseert iinto l_emmployyee 6 valuues (6,dd,3,syssdatee,20000) ; 7 exceeptioon whhen ee theen 8 DBMSS_OUTTPUT.PUT_LINEE(違反反完整約束束條件 (REPDDB.FKK_EMPP_DEPPT);

32、 9* end ;SQL /PL/SQQL 過程程已成功完完成。 輸輸出結(jié)果為為： 違反反完整約束束條件 (REPDDB.FKK_EMPP_DEPPT) 10嵌套套程序塊的內(nèi)內(nèi)部可以有有另一個程程序塊這種種情況稱為為嵌套。嵌嵌套要注意意的是變量量，定義在在最外部程程序塊中的的變量可以以在所有子子塊中使用用，如果在在子塊中定定義了與外外部程序塊塊變量相同同的變量名名，在執(zhí)行行子塊時將將使用子塊塊中定義的的變量。子子塊中定義義的變量不不能被父塊塊引用。如果字塊需需要單獨(dú)提提交，應(yīng)使使用自治事事務(wù)。11標(biāo)簽簽用戶可以使使用標(biāo)簽使使程序獲得得更好的可可讀性。程程序塊或循循環(huán)都可以以被標(biāo)記。標(biāo)簽的形形式

33、是。要求使用標(biāo)標(biāo)簽。12記錄錄轉(zhuǎn)儲開始始時間和結(jié)結(jié)束時間1）在建立立中間表后后，用腳本本或手工在在數(shù)據(jù)表中中建立一條條記錄，以以后每次都都更新。對每個轉(zhuǎn)轉(zhuǎn)儲只記錄錄一條記錄錄，不保存存歷史記錄錄。2）在開始始轉(zhuǎn)儲時，讀讀取上次轉(zhuǎn)轉(zhuǎn)儲結(jié)束時時間，只轉(zhuǎn)轉(zhuǎn)儲從上次次轉(zhuǎn)儲以來來的新增或或修改的記記錄。3）在轉(zhuǎn)儲儲的存儲過過程中記錄錄開始時間間和結(jié)束時時間，是否否成功。如如果失敗，記記錄失敗原原因。4）可以以用SQLL語句查找找失敗的轉(zhuǎn)轉(zhuǎn)儲，可以以查找轉(zhuǎn)儲儲時間過長長的轉(zhuǎn)儲。表名：轉(zhuǎn)儲儲記錄（TTRANSSLOG）字段：程序包名名存儲過程程名中間表表名開始時時間結(jié)束時時間成功標(biāo)標(biāo)識失敗原原因13授權(quán)權(quán)g

34、rantt seleect on css_neww.ACPSStorkkFlAsssAnaaTab to reepdbnnewrevokke seleect on css_neww.BILIInvoiiceUsseEnttityDData fromm reppdbneew注意：不能能為當(dāng)前用用戶授權(quán)14建立立同義詞CREATTE SYNOONYM ACPSStorkkFlAsssAnaaTab FOR jjcerpp.ACPSStorkkFlAsssAnaaTabdrop SYNOONYM BILIInvoiiceUsseEnttityDData注：建立同同義詞后用用戶可以用用seleect訪

35、問問，但不能能建立視圖圖。15為表表字段加注注釋COMMEENT ON COLUUMN HAN_2.BBBBB IS B字段;16觸發(fā)發(fā)器creatte or repllace triggger qyteest1_triggger33 BEFFORE INSEERT ON mlogg$_qyytestt1FOR EEACH ROWbeginn:new.snapptimee$ := SYYSDATTE;end;17自定定義類型的的賦值自定義類型型：CREATTE OR REPLLACE TYYPE reppdbneew.INPAARAM ASS VAARRAYY(50) OFF VAARCHAA

36、R2(25); 自定義類型型的賦值： declaare inddatanname innparaam; beggin inddatanname := iinparram(2); inddatanname:= inpaaram(isCCalCoount,NO_TX); endd; 18OBBJECTT TYPPES簡單來說，OOraclle Obbjectt Typees 就是是 Oraacle 以 TYYPE 的的方式來實實現(xiàn)物件(Objeects)的方法，宣宣告/定義的方方法，類似似於 Paackagge。Objeect Typee 的宣告告/定義中包包含了它的的 Atttribuutes/

37、Proppertiies 與與 Metthodss，也就是是 Memmber Funcctionns/Prrocedduress。本篇來介紹紹利用 OOraclle Obbjectt Typees 來做做中介暫存存的實作。建立 Orraclee Objject:定義這個 Objeect 的的內(nèi)容，可可以把它想想像為所希希望的 RRow Coluumns 的定義。view plaiincoppy too cliipboaardprrint?CREATTE TYPEE typpe_obbj ASS OBJEECT ( coll1 IINTEGGER, coll2 VVARCHHAR2(60) );

38、 / CREATTE TYPEE typpe_obbj ASS OBJEECT ( cool1 INTEEGER, cool2 VARCCHAR22(60);/建立 Obbjectt Colllecttion:建立一個 Tablle Typee，這個 Tablle 裝的的資料列內(nèi)內(nèi)容(欄位)就是之前前所建立的的 Objject。view plaiincoppy too cliipboaardprrint?CREATTE OR REPLLACE TYPE typee_tabb IS TABLLE OF tyype_oobj; / CREATTE OR REPLLACE TYPEE typpe_t

39、aab ISS TABLLE OF tyype_oobj;/在 PL/SQL 中的應(yīng)用用:定義好前面面兩個 TType，就就可以拿它它來在程式式中實作了了。view plaiincoppy too cliipboaardprrint?DECLAARE - 宣告與初初始化 objj_typpe_taab typee_tabb := tyype_ttab(); BEGINN - 多筆給值值 objj_typpe_taab := typpe_taab ( ttype_obj ( 37, coll2_vaal1), ttype_obj ( 3, coll2_vaal2), ttype_obj (254

40、, coll2_vaal3); - 逐筆給值值 objj_typpe_taab.EXXTENDD; objj_typpe_taab(obj_typee_tabb.LASST) := tyype_oobj( 12, coll_2_vval); /* 要逐筆給或或一次給多多筆就純粹粹看使用時時機(jī)與方便便性. 舉舉例來說, 已經(jīng)有有確切可知知的數(shù)筆資資料時, 就可以用用多筆給的的方式來定定義使用. 但是, 如果資資料來源是是取自其他他媒介, 比方說 Curssor, 可能就需需要跑 LLoop 去逐筆取取值與給值值了. */ /* 再來, 可可以把已經(jīng)經(jīng)存放好資資料的 oobj_ttype_tab,

41、 像下面面這種應(yīng)用用方式去取取回資料. 當(dāng)然, 舉舉一反三, 它可以以用這樣子子的 SEELECTT 方式轉(zhuǎn)轉(zhuǎn)化成 RReferrencee Currsor 及其他相相關(guān)應(yīng)用. */ FORR recc IN (SELLECT col11, coll2 FRROM TABLLE(CAAST(oobj_ttype_tab AS TYYPE_TTAB) LOOOP DDBMS_OUTPPUT.pput_lline( rrec.ccol1 = | rrec.col1 | ; rrec.ccol2 = | rrec.col22); ENDD LOOPP; /* 輸出結(jié)果: recc.coll1 = 3

42、7; recc.coll2 = col22_vall1 recc.coll1 = 3; recc.coll2 = col22_vall2 recc.coll1 = 254; recc.coll2 = col22_vall3 recc.coll1 = 12; recc.coll2 = col_2_vaal */ END; 19Taablettype的的使用定義：reesulttset OUUT ttableetypee使用：OPPEN rresulltsett FORR SELEECT DISTTINCTT ccnamee, b.paraamneeeded FFROM tempp_ta

43、bblenaames a, tablle_prroc bb WHHERE UPPEER (a.taablenname) = b.tabllenamme;查詢：DEECLARRE TAABNAMMES TAABNAMMES_OOBJ; REESULTTSET GEETTABBLENAAMES.TABLLETYPPE; w_id VAARCHAAR2 (30); h_id VAARCHAAR2 (30);BEGINN TAABNAMMES := (TABNNAMESS_OBJJ (tabnname_listt_typp (REPP_FT_CO_CCPCB_V); GEETTABBLENAAMES.

44、GETTTABLEESOFVVIEW (TABNNAMESS, RESSULTSSET); LOOOP FETTCH RRESULLTSETT INTTO w_idd, h_iid; EXIIT WHENN RESSULTSSET%NNOTFOOUND; DBMMS_OUUTPUTT.putt_linne (w_idd | , | h_id); ENND LOOPP; COOMMITT;END;20創(chuàng)建建DB-LLinkcreatte dataabasee linkk erppcar-dbb linnk 名稱稱，與連接接實例名相相同conneect to jfferp -連接接使用用戶戶ide

45、nttifieed by qmnnerp -用戶密密碼usingg ERPPCAR -連接實實例21SQQL優(yōu)化 SELEECT子句句中避免使使用 *當(dāng)你想在SSELECCT子句中中列出所有有的COLLUMN時時,使用動動態(tài)SQLL列引用 * 是是一個方便便的方法.不幸的是是,這是一一個非常低低效的方法法. 實際際 上,OORACLLE在解析析的過程中中, 會將將* 依次轉(zhuǎn)換換成所有的的列名, 這個工 作是通過過查詢數(shù)據(jù)據(jù)字典完成成的, 這這意味著將將耗費(fèi)更多多的時間.盡量多使用用COMMMIT只要有可能能,在程序序中盡量多多使用COOMMITT, 這樣樣程序的性性能得到提提高,需求求也會因為

46、為COMMMIT所釋釋放的資源源而減少:COMMIIT所釋放放的資源:a. 回滾滾段上用于于恢復(fù)數(shù)據(jù)據(jù)的信息.b. 被程程序語句獲獲得的鎖c. reedo llog bbuffeer 中的的空間d. ORRACLEE為管理上上述3種資資源中的內(nèi)內(nèi)部花費(fèi)減少對表的的查詢次數(shù)數(shù)在含有子查查詢的SQQL語句中中,要特別別注意減少少對表的查查詢.1)例如:低效SELECCT TAAB_NAAMEFROM TABLLESWHEREE TABB_NAMME = ( SEELECTT TABB_NAMMEFROM TAB_COLUUMNSWHEREE VERRSIONN = 6604)AND DDB_VEE

47、R= ( SELLECT DB_VVERFROM TAB_C0LUUMNSWHEREE VERRSIONN = 6604)高效SELECCT TAAB_NAAMEFROM TABLLESWHEREE (TAAB_NAAME,DDB_VEER)= ( SSELECCT TAAB_NAAME,DDB_VEER)FROM TAB_COLUUMNSWHEREE VERRSIONN = 6604)2)Upddate 多個Coolumnn 例子:低效:UPDATTE EMMPSET EEMP_CCAT = (SEELECTT MAXX(CATTEGORRY) FFROM EMP_CATEEGORIIES)

48、,SAL_RRANGEE = (SELEECT MMAX(SSAL_RRANGEE) FRROM EEMP_CCATEGGORIEES)WHEREE EMPP_DEPPT = 00200;高效:UPDATTE EMMPSET (EMP_CAT, SALL_RANNGE)= (SEELECTT MAXX(CATTEGORRY) , MAXX(SALL_RANNGE)FROM EMP_CATEEGORIIES)WHEREE EMPP_DEPPT = 00200;用EXISSTS替代代IN在許多基于于基礎(chǔ)表的的查詢中,為了滿足足一個條件件,往往需需要對另一一個表進(jìn)行行聯(lián)接.在在這種情況況下, 使使用

49、EXIISTS(或NOTT EXIISTS)通常將提提高查詢的的效率.低效:SELECCT *FROM EMP (基礎(chǔ)表表)WHEREE EMPPNO 0AND DDEPTNNO INN (SEELECTT DEPPTNOFROM DEPTTWHEREE LOCC = MELBB)高效:SELECCT *FROM EMP (基礎(chǔ)表表)WHEREE EMPPNO 0AND EEXISTTS (SSELECCT XXFROM DEPTTWHEREE DEPPT.DEEPTNOO = EEMP.DDEPTNNOAND LLOC = MEELB)用IN來替替換OR下面的查詢詢可以被更更有效率的的語句替

50、換換:低效:SELECCT.FROM LOCAATIONNWHEREE LOCC_ID = 100OR LOOC_IDD = 220OR LOOC_IDD = 330高效SELECCTFROM LOCAATIONNWHEREE LOCC_IN IN (10,220,300);用Wherre子句替替換HAVVING子子句：避免使用HHAVINNG子句， HAVIING 只只會在檢索索出所有記記錄之后才才對結(jié)果集集進(jìn)行過濾濾。這個處處理需要排排序，總計計等操作。 如果能能通過WHHERE子子句限制記記錄的數(shù)目目，那就能能減少這方方面的開銷銷。例如:低效:SELECCT REEGIONN，AVGG(

51、LOGG_SIZZE) FFROM LOCAATIONN GROOUP BBY REEGIONN HAVVING REGIION != SYDNNEY AND REGIION != PERTTH高效SELECCT REEGIONN，AVGG(LOGG_SIZZE) FFROM LOCAATIONN WHEERE RREGIOON REEGIONN != SYDNNEYAND RREGIOON != PEERTH GROOUP BBY REEGIONNHAVINNG 中的的條件一般般用于對一一些集合函函數(shù)的比較較，如COOUNT() 等等等。除此而而外，一般般的條件應(yīng)應(yīng)該寫在WWHEREE子句中

52、。其他相關(guān)技技術(shù)作業(yè)調(diào)度說明1）定時執(zhí)執(zhí)行數(shù)據(jù)轉(zhuǎn)轉(zhuǎn)儲：用OORACLLE的作業(yè)業(yè)調(diào)度的功功能。2）定義作作業(yè)：通過過WEB方方式的ORRACLEE管理界面面定義作業(yè)業(yè)。作業(yè)名名所有者者命令類類型：PLL/SQLL塊PL/SQL：包名。過過程名如果包不屬屬于定義的的所有者，需需要指定包包所在用戶戶名。beginnSYLTTEST.TESTTSCHEE;endd;調(diào)度：按每天的的方式執(zhí)行行，也可以以按每月的的方式執(zhí)行行3）包的執(zhí)執(zhí)行順序如果多個包包有依賴關(guān)關(guān)系，可以以用存儲過過程定義執(zhí)執(zhí)行順序（在在定時器中中不能定義義）。4）對作業(yè)業(yè)的監(jiān)控在ORRACLEE的管理界界面中有日日志使用TOAAD實現(xiàn)

53、作作業(yè)調(diào)度：方式一：直直接定義JJobs（作作業(yè)）1選擇創(chuàng)創(chuàng)建一個作作業(yè)，輸入入名稱2輸入首首次執(zhí)行時時間(Fiirst Excuutionn)3輸入執(zhí)執(zhí)行頻率(Subssequeent EExcuttion)如：每天早早上六點(diǎn)執(zhí)執(zhí)行：TRRUNC(SYSDDATE+1)+66/24注意：當(dāng)作作業(yè)成功完完成時，系系統(tǒng)才會計計算下一次次執(zhí)行時間間（SYSSDATEE+1）4選擇單單選框“Parsse”（默認(rèn)）在定義義作業(yè)的時時候解析存存儲過程或選擇“No PParsee”在執(zhí)行行作業(yè)的時時候解析存存儲過程5最后在在“Whatt to excuute”欄中輸入入作業(yè)的具具體內(nèi)容或或點(diǎn)擊該區(qū)域域的右

54、上角角按鈕選擇一一個存儲過過程。優(yōu)點(diǎn)：適用用不頻繁改改動或不改改動作業(yè)本本身即作業(yè)業(yè)執(zhí)行頻率率的情況。缺點(diǎn)：作業(yè)業(yè)定義與作作業(yè)執(zhí)行頻頻率在一起起定義不利利于管理。方式二：定定義Schhed.Jobss（作業(yè)調(diào)調(diào)度）新建一個PProgrram（相相當(dāng)于作業(yè)業(yè)內(nèi)容）或或Scheedulee（相當(dāng)于于作業(yè)運(yùn)行行時間和頻頻率）（此此過程可選選）新建一個SSchedd.Jobbs，輸入入名稱在Proggam IInfo的的Tab頁頁選擇存儲儲過程（SSpeciify PProgrram IInfo）或預(yù)定義的Program（Use Predefined Program）在Scheedulee Inffo

55、 的TTab頁輸輸入開始、結(jié)束時間間和運(yùn)行頻頻率（Sppesiffy Sccheduule IInfo）（如下例：）或預(yù)定義的Schedule（Use Predefined Schedule）。例：Staart DDate:20088/01/09 002:000:00.0000000 +08:000End Datee:20099/01/09 008:000:00.0000000 +08:000Repeeat IInterrval:FREQQ=DAIILY;IINTERRVAL=1優(yōu)點(diǎn)：1 作業(yè)和作作業(yè)執(zhí)行頻頻率可以分分開定義，并并且組合出出許多作業(yè)業(yè)調(diào)度 2 更多作業(yè)業(yè)屬性可以以被定義缺點(diǎn)：相

56、對對于方式一一，稍顯繁繁瑣。物化視圖物化視圖具具有視圖的的特性，但但是又不同同于視圖?？梢曰谟赟ELEECT語句句創(chuàng)建物化化視圖，但但是物化視視圖可以物物理的保存存和存儲數(shù)數(shù)據(jù)。Orraclee Dattabasse 100G使對表表的匯總計計算轉(zhuǎn)向?qū)ξ锘晥D圖的查詢.這里給出出一個創(chuàng)建建物化視圖圖的例子：CREATTE MAATERIIALIZZED VVIEW REPPDB. REFREESH FORCCE STARTT WITHH to_datee(30-6 -20006 111:38:30 ,dd-Mon-yyyyy HH:MI:SSS AMM) NEXT sysddate + 1

57、/864000 ASSELECCT emmp.emp_id,emp.emp_namee, deppt.NAMEE FROOM l_deept ddept, l_emmployyee eemp WHERRE emmp.dep_id = deppt.deptt_id物化視圖日日志物化視圖日日志是根據(jù)據(jù)目標(biāo)表的的目標(biāo)字段段建立的一一張表，它它記錄了目目標(biāo)表的目目標(biāo)字段發(fā)發(fā)生增、刪刪、改時的的過程，使使我們可以以得到這些些過程數(shù)據(jù)據(jù)，為增量量更新提供供數(shù)據(jù)來源源。物化視圖日日志的名稱稱為MLOOG$_后后面跟基表表的名稱，如如果表名的的長度超過過20位，則則只取前220位，當(dāng)當(dāng)截短后出出現(xiàn)名稱重重復(fù)時

58、，OOraclle會自動動在物化視視圖日志名名稱后面加加上數(shù)字作作為序號物化視圖日日志中包含含目標(biāo)表的的目標(biāo)列，除除此之外還還包括以下下常用列：列名稱說明SNAPTTIME$表示刷新時時間DMLTYYPE$表示DMLL操作類型型，I表示示INSEERT，DD表示DEELETEE，U表示示UPDAATEOLD_NNEW$表示這個值值是新值還還是舊值。N（EWW）表示新新值，O（LLD）表示示舊值，UU表示UPPDATEE操作時，如如果所觀察察的值沒有有變化，則則記錄舊值值的行被標(biāo)標(biāo)記為USEQUEENCE$（可選選）給每個操作作一個SEEQUENNCE號，從從而保證刷刷新時按照照順序進(jìn)行行刷新索

59、引設(shè)計與與維護(hù)數(shù)據(jù)庫會建建立一個物物理索引對對象，而每每次運(yùn)行查查詢的時候候都訪問同同一個索引引。1）：對于于小表來說說使用索引引對于性能能不會有任任何提高。2)：當(dāng)你你的索引列列中有極多多的不同的的數(shù)據(jù)和空空值時索引引會使性能能有極大的的提高（每每個表中的的ID）。當(dāng)查詢要返返回的數(shù)據(jù)據(jù)很少時索索引可以優(yōu)優(yōu)化查詢（ 比較好的的情況是少少于全部數(shù)數(shù)據(jù)的25%） 如果你要要返回的數(shù)數(shù)據(jù)很多時時索引會加加大系統(tǒng)開開銷。3)：索引引可以提高高數(shù)據(jù)的返返回速度，但但是它使得得數(shù)據(jù)的更更新操作變變慢在對記記錄和索引引進(jìn)行大量量的更新操操作更新時時，應(yīng)先刪刪除索引，當(dāng)當(dāng)執(zhí)行完更更新操作后后只需要恢恢復(fù)索引

60、即即可。4)：索引引會占用數(shù)數(shù)據(jù)庫的空空間設(shè)計數(shù)數(shù)據(jù)庫時，要要考慮索引引所占用的的空間（索索引和表一一般應(yīng)該放放在不同的的表空間）。5)：不要要創(chuàng)建對經(jīng)經(jīng)常需要更更新或修改改的字段創(chuàng)創(chuàng)建索引（每每次更新數(shù)數(shù)據(jù)都要更更新索引，增增大系統(tǒng)開開銷）。6)：經(jīng)常常在WHEERE子句句中出現(xiàn)的的字段需要要加索引，這這樣可以避避免全表掃掃描，提高高數(shù)據(jù)的訪訪問速度。分區(qū)設(shè)計 在物理上把把一個表或或索引分割割成更小、更易于管管理的區(qū)塊塊。就應(yīng)用程程序訪問數(shù)數(shù)據(jù)庫而言言，只有一一個表或一一個索引，在物理上上組成這個個表，或者者索引可能能有很多分分區(qū)，每個個分區(qū)都是是一個獨(dú)立立的對象，他他可以由自自己操作，或或