oracle存儲(chǔ)過(guò)程超詳細(xì)使用手冊(cè)

1、 HYPERLINK Oracle存儲(chǔ)過(guò)程總結(jié)1、創(chuàng)建存儲(chǔ)過(guò)程create or replace procedure test(var_name_1 in type,var_name_2 out type) as-聲明變量(變量名 變變量類型)begin-存儲(chǔ)過(guò)程的的執(zhí)行體end tesst;打印出輸入的時(shí)時(shí)間信息E.g:create or reeplacee procceduree testt(workkDate in Daate) iisbegindbms_ouutput.putliine(Thhe inpput daate iss:|too_datee(workkDate, yyyyy-

2、mm-ddd);end tesst;2、變量賦值變量名 := 值;E.g：create or reeplacee procceduree testt(workkDate in Daate) iisx numbeer(4,22);beginx := 11;end tesst;3、判斷語(yǔ)句:if 比較式 then beginn end; end if;E.gcreate or reeplacee procceduree testt(x inn numbber) iisbeginif xx 0 tthenbegginx := 0 - x;end;endd if;if x = 00 thennbegi

3、nnx: = 1;endd;endd if;end tesst;4、For 循循環(huán)For . in . LOOOP-執(zhí)行語(yǔ)句end LOOOP;(1)循環(huán)遍歷歷游標(biāo)create or reeplacee procceduree testt() assCursor cursoor is selecct namme froom stuudent; name vaarcharr(20);beginfor namme in cursoor LOOOPbegindbms_ooutputt.putlline(nname);end;end LOOOP;end tesst;(2)循環(huán)遍歷歷數(shù)組createe

4、or rreplacce proocedurre tesst(varrArrayy in mmyPackkage.TTestArrray) as-(輸入?yún)?shù)數(shù)varArrray 是是自定義的數(shù)數(shù)組類型，定定義方式見(jiàn)標(biāo)標(biāo)題6)i numbeer;begini := 1; -存儲(chǔ)過(guò)過(guò)程數(shù)組是起起始位置是從從1開(kāi)始的，與與java、CC、C+等等語(yǔ)言不同。因因?yàn)樵贠raacle中本本是沒(méi)有數(shù)組組的概念的，數(shù)數(shù)組其實(shí)就是是一張-表(Tabble),每每個(gè)數(shù)組元素素就是表中的的一個(gè)記錄，所所以遍歷數(shù)組組時(shí)就相當(dāng)于于從表中的第第一條記錄開(kāi)開(kāi)始遍歷for i iin 1.varArrray.ccount L

5、OOPdbms_ouutput.putliine(Thhe No. | i |reccord iin varrArrayy is: |varrArrayy(i);end LOOOP;end tesst;5、Whilee 循環(huán)while 條條件語(yǔ)句 LLOOPbeginend;end LOOOP;E.gcreate or reeplacee procceduree testt(i inn numbber) aasbeginwhile ii 100 LOOPPbegini:= i + 1;end;end LOOOP;end teest;6、數(shù)組首先明確一個(gè)概概念：Oraacle中本本是沒(méi)有數(shù)組組的

6、概念的，數(shù)數(shù)組其實(shí)就是是一張表(TTable),每個(gè)數(shù)組組元素就是表表中的一個(gè)記記錄。使用數(shù)組時(shí)，用用戶可以使用用Oraclle已經(jīng)定義義好的數(shù)組類類型，或可根根據(jù)自己的需需要定義數(shù)組組類型。(1)使用Orracle自自帶的數(shù)組類類型x arrayy; -使使用時(shí)需要需需要進(jìn)行初始始化e.g:create or reeplacee procceduree testt(y ouut arrray) iisx arraay;beginx := neew arrray();y := x;end tesst;(2)自定義的的數(shù)組類型 (自定義數(shù)據(jù)類類型時(shí)，建議議通過(guò)創(chuàng)建PPackagge的方式實(shí)實(shí)現(xiàn)，

7、以便于于管理)E.g (自定定義使用參見(jiàn)見(jiàn)標(biāo)題4.22) creeate oor repplace packaage myyPackaage iss - Puublic type declaaratioons type info is reecord( name varchhar(200), y nnumberr); type TestAArray is taable oof inffo inddex byy binaary_inntegerr; -此處聲明明了一個(gè)TeestArrray的類型型數(shù)據(jù)，其實(shí)實(shí)其為一張存存儲(chǔ)Infoo數(shù)據(jù)類型的的Tablee而已，及TTestArrray 就就是一

8、張表，有有兩個(gè)字段，一一個(gè)是name，一個(gè)個(gè)是y。需要要注意的是此此處使用了IIndex by biinary_integger 編制制該Tablle的索引項(xiàng)項(xiàng)，也可以不不寫(xiě)，直接寫(xiě)寫(xiě)成：typpe TesstArraay istable oof inffo，如果不不寫(xiě)的話使用用數(shù)組時(shí)就需需要進(jìn)行初始始化：varrArrayy myPaackagee.TesttArrayy; varrArrayy := nnew myyPackaage.TeestArrray();end TesstArraay;7.游標(biāo)的使用用 Oracle中中Cursoor是非常有有用的，用于于遍歷臨時(shí)表表中的查詢結(jié)結(jié)果。

9、其相關(guān)關(guān)方法和屬性性也很多，現(xiàn)現(xiàn)僅就常用的的用法做一二二介紹：(1)Curssor型游標(biāo)標(biāo)(不能用于于參數(shù)傳遞)create or reeplacee procceduree testt() isscusor_11 Curssor iss seleect sttd_namme froom stuudent wheree .; -Cursoor的使用方方式1 cursoor_2 CCursorr;beginselect classs_namee intoo curssor_2 from classs wherre .; -Cursoor的使用方方式2可使用For x in cursoor LOO

10、OP . endd LOOPP; 來(lái)實(shí)現(xiàn)現(xiàn)對(duì)Curssor的遍歷歷end tesst;(2)SYS_REFCUURSOR型型游標(biāo)，該游游標(biāo)是Oraacle以預(yù)預(yù)先定義的游游標(biāo)，可作出出參數(shù)進(jìn)行傳傳遞create or reeplacee procceduree testt(rsCuursor out SSYS_REEFCURSSOR) iiscursor SYS_RREFCURRSOR; name varhccar(200);beginOPEN cuursor FOR sselectt namee fromm studdent wwhere . -SYS_REFCUURSOR只只能通過(guò)OPPE

11、N方法來(lái)來(lái)打開(kāi)和賦值值LOOPfetch cursoor intto namme-SYS_REFCUURSOR只只能通過(guò)feetch iinto來(lái)打打開(kāi)和遍歷 exit when cursoor%NOTTFOUNDD; -SYSS_REFCCURSORR中可使用三三個(gè)狀態(tài)屬性性： -%NOTFFOUND(未找到記錄錄信息) %FOUNDD(找到記錄錄信息) -%ROOWCOUNNT(然后當(dāng)當(dāng)前游標(biāo)所指指向的行位置置)dbms_ooutputt.putlline(nname);end LOOOP;rsCursoor := cursoor;end tesst;下面寫(xiě)一個(gè)簡(jiǎn)單單的例子來(lái)對(duì)對(duì)以上所說(shuō)的

12、的存儲(chǔ)過(guò)程的的用法做一個(gè)個(gè)應(yīng)用：現(xiàn)假設(shè)存在兩張張表，一張是是學(xué)生成績(jī)表表(studdnet)，字字段為：sttdId,mmath,aarticlle,lannguagee,musiic,spoort,tootal,aaveragge,steep 一張是學(xué)生生課外成績(jī)表表(out_schoool),字段段為:stddId,paarcticce,commment通過(guò)存儲(chǔ)過(guò)程自自動(dòng)計(jì)算出每每位學(xué)生的總總成績(jī)和平均均成績(jī)，同時(shí)時(shí)，如果學(xué)生生在課外課程程中獲得的評(píng)評(píng)價(jià)為A，就就在總成績(jī)上上加20分。create or reeplacee procceduree autoocompuuter(sstep

13、iin nummber) isrsCursoor SYSS_REFCCURSORR;commenttArrayy myPaackagee.myArrray;math nuumber;articlee numbber;languagge nummber;music nnumberr;sport nnumberr;total nnumberr;averagee numbber;stdId vvarchaar(30);record myPacckage.stdInnfo;i numbeer;begini := 1;get_commment(commeentArrray); -調(diào)用名名為get_com

14、meent()的的存儲(chǔ)過(guò)程獲獲取學(xué)生課外外評(píng)分信息OPEN rssCursoor forr seleect sttdId,mmath,aarticlle,lannguagee,musiic,spoort frrom sttudentt t whhere tt.stepp = sttep;LOOPfetch rrsCurssor innto sttdId,mmath,aarticlle,lannguagee,musiic,spoort; eexit wwhen rrsCurssor%NOOTFOUNND;total := matth + aarticlle + llanguaage + musi

15、cc + spport;for i iin meentArrray.coount LLOOPrecordd := ccommenntArraay(i);if stdIId = rrecordd.stdIId theenbeginif reccord.ccommennt = 'A&apoos; thhenbeginntotal := tootal + 20;go tto nexxt; -使用go to跳出ffor循環(huán) end;end if;end;end if;end LOOOP; aveerage := tootal / 5;updatee studdent tt set t.tot

16、tal=tootal aand t.averaage = averaage whhere tt.stdIId = sstdId;end LOOOP;end;end auttocompputer;-取得學(xué)生評(píng)評(píng)論信息的存存儲(chǔ)過(guò)程create or reeplacee procceduree get_commeent(coommenttArrayy out myPacckage.myArrray) iisrs SYS_REFCUURSOR；record myPacckage.stdInnfo;stdId vvarchaar(30);commentt varcchar(11);i numbeer;b

17、eginopen rss for selecct stddId,coommentt fromm out_schoooli := 1;LOOPfetch rs innto sttdId,ccommennt; exxit whhen rss%NOTFFOUND;record.stdIdd := sstdId;mment := commment;recommeentArrray(i) := rrecordd;i:=i + 1;end LOOOP;end gett_commment;-定義數(shù)組類類型myArrraycreate or reeplacee packkage mmyPackkage iis

18、 beggintype sttdInfoo is rrecordd(stdIId varrchar(30),ccommennt varrchar(1);type myyArrayy is ttable of sttdInfoo indeex by binarry_intteger;end myPPackagge;項(xiàng)目中有涉及存存儲(chǔ)過(guò)程對(duì)字字符串的處理理，所以就將將在網(wǎng)上查找找到的資料匯匯總，做一個(gè)個(gè)信息拼接式式的總結(jié)。以下信息均來(lái)自自互聯(lián)網(wǎng)，貼貼出來(lái)一則自自己保存以待待以后使用，一一則供大家分分享。字符函數(shù)返返回字符值這些函數(shù)全都接接收的是字符符族類型的參參數(shù)(CHRR除外)并且且返回字符值值

19、.除了特別說(shuō)明的的之外,這些些函數(shù)大部分分返回VARRCHAR22類型的數(shù)值值.字符函數(shù)的返回回類型所受的的限制和基本本數(shù)據(jù)庫(kù)類型型所受的限制制是相同的。字符型變量存儲(chǔ)儲(chǔ)的最大值：VARCHAAR2數(shù)值被被限制為20000字符(ORACLLE 8中為為4000字字符)CHAR數(shù)值值被限制為2255字符(在ORACCLE8中是是2000)long類型型為2GBClob類型型為4GB1、CHR語(yǔ)法: chhr(x)功能：返回在數(shù)數(shù)據(jù)庫(kù)字符集集中與X擁有有等價(jià)數(shù)值的的字符。CHHR和ASCCII是一對(duì)對(duì)反函數(shù)。經(jīng)經(jīng)過(guò)CHR轉(zhuǎn)轉(zhuǎn)換后的字符符再經(jīng)過(guò)ASSCII轉(zhuǎn)換換又得到了原原來(lái)的字符。使用位置：過(guò)程程

20、性語(yǔ)句和SSQL語(yǔ)句。2、CONCAAT語(yǔ)法： CONNCAT（sstringg1,strring2）功能：返回sttring11，并且在后后面連接sttring22。使用位置：過(guò)程程性語(yǔ)句和SSQL語(yǔ)句。3、INITCCAP語(yǔ)法：INITTCAP（sstringg）功能：返回字符符串的每個(gè)單單詞的第一個(gè)個(gè)字母大寫(xiě)而而單詞中的其其他字母小寫(xiě)寫(xiě)的striing。單詞詞是用.空格格或給字母數(shù)數(shù)字字符進(jìn)行行分隔。不是是字母的字符不變動(dòng)。使用位置：過(guò)程程性語(yǔ)句和SSQL語(yǔ)句。4、LTRIMM語(yǔ)法：LTRIIM（strring1,strinng2）功能：返回刪除除從左邊算起起出現(xiàn)在sttring22中的

21、字符的的strinng1。Sttring22被缺省設(shè)置置為單個(gè)的空空格。數(shù)據(jù)庫(kù)庫(kù)將掃描sttring11，從最左邊邊開(kāi)始。當(dāng)遇到不在strring2中中的第一個(gè)字字符，結(jié)果就就被返回了。LLTRIM的的行為方式與與RTRIMM很相似。使用位置：過(guò)程程性語(yǔ)句和SSQL語(yǔ)句。5、NLS_IINITCAAP語(yǔ)法：NLS_INITCCAP（sttring,nlspparamss）功能：返回字符符串每個(gè)單詞詞第一個(gè)字母母大寫(xiě)而單詞詞中的其他字字母小寫(xiě)的sstringg，nlspparamss指定了不同于該該會(huì)話缺省值值的不同排序序序列。如果果不指定參數(shù)數(shù)，則功能和和INITCCAP相同。NNlsparr

22、ams可以以使用的形式式是：NLS_SOORT=soort這里sort制制訂了一個(gè)語(yǔ)語(yǔ)言排序序列列。使用位置：過(guò)程程性語(yǔ)句和SSQL語(yǔ)句。6、NLS_LLOWER語(yǔ)法：NLS_LOWERR（striing,nnlsparrams）功能：返回字符符串中的所有有字母都是小小寫(xiě)形式的sstringg。不是字母母的字符不變變。 NNlsparrams參數(shù)數(shù)的形式與用用途和NLSS_INITTCAP中的的nlspaarams參參數(shù)是相同的的。如果nllsparaams沒(méi)有被被包含，那么么NLS_LLOWER所所作的處理和和LOWER相同同。使用位置；過(guò)程程性語(yǔ)句和SSQL語(yǔ)句。7、NLS_UUPPER語(yǔ)

23、法：nls_upperr（striing,nnlsparrams）功能：返回字符符串中的所有有字母都是大大寫(xiě)的形式的的strinng。不是字字母的字符不不變。nlssparamms參數(shù)的形形式與用途和和NLS_IINITCAAP中的相同同。如果沒(méi)有設(shè)定參數(shù)，則則NLS_UUPPER功功能和UPPPER相同。使用位置：過(guò)程程性語(yǔ)句和SSQL語(yǔ)句。8、REPLAACE語(yǔ)法：REPLLACE（sstringg，searrch_sttr,reeplacee_str）功能：把strring中的的所有的子字字符串seaarch_sstr用可選選的repllace_sstr替換，如如果沒(méi)有指定定replaa

24、ce_sttr，所有的的strinng中的子字字符串search_str都將將被刪除。RREPLACCE是TRAANSLATTE所提供的的功能的一個(gè)個(gè)子集。使用位置：過(guò)程程性語(yǔ)句和SSQL語(yǔ)句。9、RPAD語(yǔ)法：RPADD（striing1,xx,strring2）功能：返回在XX字符長(zhǎng)度的的位置上插入入一個(gè)strring2中中的字符的sstringg1。如果sstringg2的長(zhǎng)度要要比X字符少少，就按照需需要進(jìn)行復(fù)制制。如果sttring22多于X字符，則則僅striing1前面面的X各字符符被使用。如如果沒(méi)有指定定strinng2，那么么使用空格進(jìn)進(jìn)行填充。XX是使用顯示示長(zhǎng)度可以比比字

25、符串的實(shí)實(shí)際長(zhǎng)度要長(zhǎng)。RPADD的行為方式式與LPADD很相似，除除了它是在右右邊而不是在在左邊進(jìn)行填填充。使用位置：過(guò)程程性語(yǔ)句和SSQL語(yǔ)句。10、RTRIIM語(yǔ)法: RTTRIM（sstringg1,sstringg2）功能: 返回回刪除從右邊邊算起出現(xiàn)在在strinng1中出現(xiàn)現(xiàn)的字符sttring22. strring2被被缺省設(shè)置為為單個(gè)的空格格.數(shù)據(jù)庫(kù)將將掃描strring1,從右邊開(kāi)始始.當(dāng)遇到不在striing2中的的第一個(gè)字符符,結(jié)果就被被返回了RTTRIM的行行為方式與LLTRIM很很相似.使用位置：過(guò)程程性語(yǔ)句和SSQL語(yǔ)句。11、SOUNNDEX語(yǔ)法: SOOUNDE

26、XX（striing）功能: 返回回strinng的聲音表表示形式.這這對(duì)于比較兩兩個(gè)拼寫(xiě)不同同但是發(fā)音類類似的單詞而而言很有幫助助.使用位置：過(guò)程程性語(yǔ)句和SSQL語(yǔ)句。12、SUBSSTR語(yǔ)法: SUUBSTR（sstringg,a,bb）功能: 返回回從字母為值值a開(kāi)始b個(gè)個(gè)字符長(zhǎng)的sstringg的一個(gè)子字字符串.如果果a是0,那那么它就被認(rèn)認(rèn)為從第一個(gè)個(gè)字符開(kāi)始.如果是正數(shù)數(shù),返回字符符是從左邊向右邊進(jìn)行計(jì)計(jì)算的.如果果b是負(fù)數(shù),那么返回的的字符是從sstringg的末尾開(kāi)始始從右向左進(jìn)進(jìn)行計(jì)算的.如果b不存存在,那么它它將缺省的設(shè)設(shè)置為整個(gè)字字符串.如果b小于于1,那么將將返回NU

27、LLL.如果aa或b使用了了浮點(diǎn)數(shù),那那么該數(shù)值將將在處理進(jìn)行行以前首先被被卻為一個(gè)整整數(shù).使用位置：過(guò)程程性語(yǔ)句和SSQL語(yǔ)句。13、TRANNSLATEE 語(yǔ)法: TRANNSLATEE(striing,frrom_sttr,to_str) 功能: 返回將所所出現(xiàn)的frrom_sttr中的每個(gè)個(gè)字符替換為為to_sttr中的相應(yīng)應(yīng)字符以后的的strinng. TRRANSLAATE是REEPLACEE所提供的功功能的一個(gè)超超集.如果from_str比tto_strr長(zhǎng),那么在在from_str中而而不在to_str中而而外的字符將將從striing中被刪刪除,因?yàn)樗鼈儧](méi)有相應(yīng)應(yīng)的替換字符

28、符. to_str不能能為空.Oraclee把空字符串串認(rèn)為是NUULL,并且且如果TRAANSLATTE中的任何何參數(shù)為NUULL,那么么結(jié)果也是NNULL.使用位置：過(guò)程程性語(yǔ)句和SSQL語(yǔ)句。14、UPPEER語(yǔ)法: UPPPER（sttring）功能: 返回大大寫(xiě)的strring.不不是字母的字字符不變.如如果striing是CHHAR數(shù)據(jù)類類型的,那么么結(jié)果也是CCHAR類型型的.如果sstringg是VARCCHAR2類類型的,那么么結(jié)果也是VARRCHAR22類型的.使用位置: 過(guò)過(guò)程性語(yǔ)句和和SQL語(yǔ)句句。字符函數(shù)返返回?cái)?shù)字這些函數(shù)接受字字符參數(shù)回?cái)?shù)數(shù)字結(jié)果.參參數(shù)可以是CCH

29、AR或者者是VARCCHAR2類類型的.盡管管實(shí)際下許多多結(jié)果都是整整數(shù)值,但是是返回結(jié)果都都是簡(jiǎn)單的NNUMBERR類型的,沒(méi)有定定義任何的精精度或刻度范范圍.16、ASCIII語(yǔ)法: ASSCII（sstringg）功能: 數(shù)據(jù)庫(kù)庫(kù)字符集返回回strinng的第一個(gè)個(gè)字節(jié)的十進(jìn)進(jìn)制表示.請(qǐng)請(qǐng)注意該函數(shù)數(shù)仍然稱作為為ASCIII.盡管許多多字符集不是是7位ASCCII.CHHR和ASCCII是互為為相反的函數(shù).CCHR得到給給定字符編碼碼的響應(yīng)字符符. ASCCII得到給給定字符的字字符編碼.使用位置: 過(guò)過(guò)程性語(yǔ)句和和SQL語(yǔ)句句。17、INSTTR語(yǔ)法: INNSTR（sstringg1

30、, sttring22a,b）功能: 得到到在striing1中包包含striing2的位位置. sttring11時(shí)從左邊開(kāi)開(kāi)始檢查的,開(kāi)始的位置置為a,如果果a是一個(gè)負(fù)負(fù)數(shù),那么sstringg1是從右邊邊開(kāi)始進(jìn)行掃描的.第b次次出現(xiàn)的位置置將被返回. a和b都都缺省設(shè)置為為1,這將會(huì)會(huì)返回在sttring11中第一次出出現(xiàn)striing2的位位置.如果sstringg2在a和bb的規(guī)定下沒(méi)沒(méi)有找到,那么返回回0.位置的的計(jì)算是相對(duì)對(duì)于striing1的開(kāi)開(kāi)始位置的,不管a和bb的取值是多多少.使用位置: 過(guò)過(guò)程性語(yǔ)句和和SQL語(yǔ)句句。18、INSTTRB語(yǔ)法: INNSTRB（sstri

31、ngg1, sttring22a,bb）功能: 和IINSTR相相同,只是操操作的對(duì)參數(shù)數(shù)字符使用的的位置的是字字節(jié).使用位置: 過(guò)過(guò)程性語(yǔ)句和和SQL語(yǔ)句句。19、LENGGTH語(yǔ)法: LEENGTH（sstringg）功能: 返回回strinng的字節(jié)單單位的長(zhǎng)度.CHAR數(shù)數(shù)值是填充空空格類型的,如果strring由數(shù)數(shù)據(jù)類型CHHAR,它的的結(jié)尾的空格格都被計(jì)算到到字符串長(zhǎng)度度中間.如果strinng是NULLL,返回結(jié)結(jié)果是NULLL,而不是是0.使用位置: 過(guò)過(guò)程性語(yǔ)句和和SQL語(yǔ)句句。20、LENGGTHB語(yǔ)法: LEENGTHBB（striing）功能: 返回回以字節(jié)為單單位的

32、strring的長(zhǎng)長(zhǎng)度.對(duì)于單單字節(jié)字符集集LENGTTHB和LEENGTH是是一樣的.使用位置: 過(guò)過(guò)程性語(yǔ)句和和SQL語(yǔ)句句。21、NLSSSORT語(yǔ)法: NLSSSORT（sstringg,nlssparamms）功能: 得到用用于排序sttring的的字符串字節(jié)節(jié).所有的數(shù)數(shù)值都被轉(zhuǎn)換換為字節(jié)字符符串,這樣在在不同數(shù)據(jù)庫(kù)庫(kù)之間就保持持了一致性. Nlspparamss的作用和NLS_INIITCAP中中的相同.如如果忽略參數(shù)數(shù),會(huì)話使用用缺省排序.使用位置: 過(guò)過(guò)程性語(yǔ)句和和SQL語(yǔ)句句。oracle 存儲(chǔ)過(guò)程的的基本語(yǔ)法1.基本結(jié)構(gòu) CREATE OR REEPLACEE PROCC

33、EDUREE 存儲(chǔ)過(guò)程程名字( 參數(shù)11 IN NNUMBERR, 參數(shù)22 IN NNUMBERR) IS變量1 INTTEGER :=0;變量2 DATTE;BEGINEND 存儲(chǔ)過(guò)過(guò)程名字2.SELECCT INTTO STAATEMENNT 將seleect查詢的的結(jié)果存入到到變量中，可可以同時(shí)將多多個(gè)列存儲(chǔ)多多個(gè)變量中，必必須有一條 記錄，否則則拋出異常(如果沒(méi)有記記錄拋出NOO_DATAA_FOUNND) 例子： BEGINN SELECCT coll1,coll2 intto 變量11,變量2 FROM typesstructt wherre xxxx; EXCEPPTION W

34、HEN NO_DAATA_FOOUND TTHEN xxxxx; END; .3.IF 判斷斷 IF V_TEST=1 THEEN BEGGIN do soomethiing ENDD; END IIF;4.whilee 循環(huán) WHILEE V_TEEST=1 LOOP BEGINNXXXX END; END LLOOP;5.變量賦值 V_TESST := 123;6.用for in 使用用cursoor . IS CURSOOR curr IS SSELECTT * FRROM xxxx; BEGINNFOR cuur_ressult iin curr LOOPPBEGINNV_SUUM :

35、=ccur_reesult.列名1+ccur_reesult.列名2END;END LOOOP; END;7.帶參數(shù)的ccursorr CURSOOR C_UUSER(CC_ID NNUMBERR) IS SELECCT NAMME FROOM USEER WHEERE TYYPEID=C_ID; OPEN C_USEER(變量值值); LOOPFETCH C_USEER INTTO V_NNAME;EXIT FFETCH C_USEER%NOTTFOUNDD; do sometthing END LLOOP; CLOSEE C_USSER;8.用pl/ssql deevelopper dee

36、bug 連接數(shù)據(jù)庫(kù)庫(kù)后建立一個(gè)個(gè)Test WINDOOW 在窗口輸入入調(diào)用SP的的代碼,F99開(kāi)始debbug,CTTRL+N單單步調(diào)試關(guān)于oraclle存儲(chǔ)過(guò)程程的若干問(wèn)題題備忘1.在oraccle中，數(shù)數(shù)據(jù)表別名不不能加as，如如：selecta.apppnamefromaappinffoa;- 正確selecta.apppnamefromaappinffoasa;- 錯(cuò)誤也許，是怕和和oraclle中的存儲(chǔ)儲(chǔ)過(guò)程中的關(guān)關(guān)鍵字as沖沖突的問(wèn)題吧吧2.在存儲(chǔ)過(guò)程程中，sellect某一一字段時(shí)，后后面必須緊跟跟into，如如果seleect整個(gè)記記錄，利用游游標(biāo)的話就另另當(dāng)別論了。selec

37、ctaf.keynoodeinntoknnfrommAPPFFOUNDAATIONafwhhereaaf.apppid=aidanndaf.founddationnid=fid;-有intto，正確編編譯selecctaf.keynoodefrromAPPPFOUNNDATIOONafwhereeaf.aappid=aidanndaf.founddationnid=fid;-沒(méi)有innto，編譯譯報(bào)錯(cuò)，提示示：ComppilatiionErrorr:PLSS-004288:anINTOcclauseeisexppecteddinthiisSELLECTsstatemment3.在利用to.語(yǔ)語(yǔ)

38、法時(shí)，必須須先確保數(shù)據(jù)據(jù)庫(kù)中有該條條記錄，否則則會(huì)報(bào)出nno datta fouund異常常。 可以在該該語(yǔ)法之前，先先利用sellect ccount(*) frrom 查看看數(shù)據(jù)庫(kù)中是是否存在該記記錄，如果存存在，再利用用selecct.iinto.4.在存儲(chǔ)過(guò)程程中，別名不不能和字段名名稱相同，否否則雖然編譯譯可以通過(guò)，但但在運(yùn)行階段段會(huì)報(bào)錯(cuò)selecttkeynnodeiintokknfroomAPPPFOUNDDATIONNwherreapppid=aidanndfouundatiionid=fid;-正確運(yùn)行行selectaf.keeynodeeintoknnfrommAPPFFO

39、UNDAATIONafwhhereaf.apppid=aappidandaf.fooundattionidd=founndatioonid;-運(yùn)行行階段報(bào)錯(cuò)，提提示ORA-014422:exxactffetchreturrnsmoorethhanreequesttednumbberofrowws5.在存儲(chǔ)過(guò)程程中，關(guān)于出出現(xiàn)nulll的問(wèn)題假設(shè)有一個(gè)表AA，定義如下下：createtableeA(idvarcchar2(50)primmarykeynotnull,vcountnumbeer(8)notnull,bidvarrchar22(50)notnull-外鍵鍵);如果在存儲(chǔ)過(guò)程程中，使用

40、如如下語(yǔ)句：selectsum(vvcountt)inttofcoountffromAAwherrebidd=xxxxxxx;如果A表中不存存在bid=xxxxxxx的記記錄，則fccount=null(即使fcoount定義義時(shí)設(shè)置了默默認(rèn)值，如：fcounnt nummber(88):=0依依然無(wú)效，ffcountt還是會(huì)變成成null)，這樣以后后使用fcoount時(shí)就就可能有問(wèn)題題，所以在這這里最好先判判斷一下：iffcouuntissnulltthenfcoount:=0;endif;這樣就一切okk了。6.Hiberrnate調(diào)調(diào)用oraccle存儲(chǔ)過(guò)過(guò)程thiss.pnummbe

41、rMaanagerr.getHHibernnateTeemplatte().eexecutte(nnewHiibernaateCalllbackk()puublicObjecctdoIInHibeernatee(Sesssionssessioon)throowsHiibernaateExcceptioon,SQQLExceeptionnCalllableeStateementcs=sessiion.connnectioon().preppareCaall(callmodiffyappppnumbeer_remmain(?);cs.setSttring(1,fooundattionidd);cs

42、.execuute();retturnnull;);oracle存儲(chǔ)過(guò)程語(yǔ)語(yǔ)法總結(jié)及練練習(xí)-1.存儲(chǔ)過(guò)過(guò)程之ifclear;create or reeplacee procceduree mydeel(in_a inn inteeger)asbeginif in_aa100 thendbms_ouutput.put_lline(小于1000.);elsif iin_a301;end looop;end;/set serrverouutput on;beginmydel(22);end;/-1.存儲(chǔ)過(guò)過(guò)程之looop2clear;create or reeplacee procceduree m

43、ydeel(in_a inn inteeger)asa integger;begina:=0;while aa300 loopdbms_ouutput.put_lline(aa);a:=a+1;end looop;end;/set serrverouutput on;beginmydel(22);end;-1.存儲(chǔ)過(guò)過(guò)程之looop3clear;create or reeplacee procceduree mydeel(in_a inn inteeger)asa integger;beginfor a iin 0.300loopdbms_ouutput.put_lline(aa);end l

44、ooop;end;/set serrverouutput on;beginmydel(22);end;/clear;select enamee,cc:=(caseewhen coomm=nuull thhen saal*12;else (ssal+coomm)*112;end casse froom empp ordeer by salpeersal;-clear;create or reeplacee procceduree getsstudenntcommments(i_studeentid in innt,o_ccommennts ouut varrchar)asexams_ssat i

45、nnt;avg_marrk intt;tmp_commmentss varcchar(1100);beginselect countt(exammid) iinto eexams_sat ffrom sstudenntexammwhere sstudenntid=ii_studdentidd;if examms_satt=0 thhentmp_commmentss:=n/a-thiis stuudent did nnot atttend the eexam!;elseselect avg(mmark) into avg_mmark ffrom sstudenntexammwhere sstud

46、enntid=ii_studdentidd;casewhen avvg_marrk50 then tmp_ccommennts:=very bad;when avvg_marrk60 then tmp_ccommennts:=bad;when avvg_marrk70 then tmp_ccommennts:=good;end casse;end if;o_commeents:=tmp_ccommennts;end;/set serrverouutput on;declareepp studdentexxam.coommentts%typpe;begingetstuddentcoommentt

47、s(8,ppp);dbms_ouutput.put_lline(ppp);end;/-delete from emp wwhere empnoo60000;clear;create or reeplacee procceduree inseertdatta(in_num in inntegerr)asmyNum iint deefaultt 0;emp_no emp.eempno%type:=10000;beginwhile mmyNumin_nuum looopinsert into emp vvaluess(emp_no,hhui|myNumm,codder,77555,ccurrennt

48、_datte,80000,62558,30);emp_no:=emp_no+1;myNum:=myNumm+1;end looop;end;/set serrverouutput on;begininsertddata(110);end;/select * froom empp;-clear;select studeentnamme,aveerageMMark,ccasewhen avverageeMark60 thhen 不不及格when avverageeMark70 thhen 考考得好when avverageeMark(select sal ffrom eemp whhere een

49、ame=SMITTH);-3-select a.enaame,(select enamee fromm emp b wheere b.empnoo=a.mggr) ass bosssname from emp aa;-4-select a.enaame frrom emmp a wwhere a.hirredatee1500;-8-select enamee fromm emp wheree depttno=(select deptnno froom deppt wheere dnname=SALESS);-9-select enamee fromm emp wheree sal(select

50、 avg(ssal) ffrom eemp);-10-select enamee fromm emp wheree job=(select job ffrom eemp whhere eename=SCOTTT);-11-select a.enaame,a.sal ffrom eemp a wheree a.saal in (select b.sall fromm emp b wheere b.deptnno=30) and a.depptno30;-12-select enamee,sal from emp wwhere sal(select max(ssal) ffrom eemp whh

51、ere ddeptnoo=30);-13-select (selectt b.dnname ffrom ddept bb wherre a.ddeptnoo=b.deeptno) as ddeptnaame ,count(ddeptnoo) as deptccount,avg(sall) as deptaavgsall from emmp a ggroup by deeptno; -14-select a.enamee,(selectt b.dnname ffrom ddept bb wherre b.ddeptnoo=a.deeptno) as ddeptnaame,sal from emm

52、p a; -15-selecta.deptnno,a.dnamee,a.loc,(selectt counnt(depptno) from emp bb wherre b.ddeptnoo=a.deeptno groupp by bb.depttno) aas depptcounntfrom deept a;-16-select job,aavg(saal) frrom emmp grooup byy job;-17-select deptnno,minn(sal) fromm emp wheree job=MANAAGER groupp by ddeptnoo;-18-select enam

53、ee,(sall+nvl(comm,0)*112 as salpeersal from emp oorder by saalperssal;ORACLE子子句查詢,分分組等A.同表子查詢?cè)冏鳛闂l件a. 給出人口口多于Russsia(俄俄國(guó))的國(guó)家家名稱 SEELECT name FROM bbcWHERE ppopulaation(SELECTT popuulatioon FROOM bbccWHERE nname=Russiia) b.給出Inndia(印度), Irann(伊朗)所在地區(qū)的的所有國(guó)家的的所有信息 SELECCT * FFROM bbbcWHERE rregionn IN(S

54、ELECTT regiion FRROM bbbcWHERE nname IIN (IIndia,Iraan) c.給出人均GGDP超過(guò)Uniteed Kinngdom(英國(guó))的的歐洲國(guó)家. SELEECT naame FRROM bbbcWHERE rregionn=Eurrope AND ggdp/poopulattion (SELECTT gdp/popullationn FROMM bbcWHERE nname=Uniteed Kinngdom) d.這個(gè)查詢實(shí)實(shí)際上等同于于以下這個(gè):select e1.enname ffrom eemp e11,(sellect eempno fro

55、m emp wwhere enamee = KKING) e2 wwhere e1.mggr = ee2.emppno; 你可以用EXIISTS寫(xiě)同同樣的查詢，你你只要把外部部查詢一欄移移到一個(gè)像下下面這樣的子子查詢環(huán)境中中就可以了：select enamee fromm emp ewhere eexistss (sellect 00 fromm emp wheree e.mggr = eempno and eename = KIING); 當(dāng)你在一個(gè)WHHERE子句句中寫(xiě)EXIISTS時(shí)，又又等于向最優(yōu)優(yōu)化傳達(dá)了這這樣一條信息息，即你想讓讓外部查詢先先運(yùn)行，使用用每一個(gè)值來(lái)來(lái)從內(nèi)部查詢?cè)儯?/p>

56、定：EEXISTSS由外而內(nèi)內(nèi)）中得到一一個(gè)值。B.異表子查詢?cè)冏鳛闂l件a.selecct * ffrom sstudenntExamm wherre stuudentiid=( sselectt studdentidd fromm studdent wwhere name=吳麗麗);b.selecct * ffrom sstudenntexamm wherre stuudentiid in (select studeentid from studeent) oorder by sttudenttid;c.selecct * ffrom sstudennt wheere sttudenttid

57、 inn (sellect sstudenntid ffrom sstudenntexamm wherre marrk80); 3.selecct stuudenteexam.mmark,sstudenntexamm.studdentidd as sseid, studeent.sttudenttid,e fromm studdentexxam,sttudentt wherre stuudent.studeentid=studeentexaam.stuudentiid;過(guò)濾分組:順序?yàn)橄确纸M,再過(guò)濾,最最后進(jìn)行統(tǒng)計(jì)計(jì)(實(shí)際值).select studeentid,countt(*) aas h

58、igghpassses frrom sttudenttexamwwhere mark70grooup byy studdentidd;假使我們不想通通過(guò)數(shù)據(jù)表中中的實(shí)際值,而是通過(guò)聚聚合函數(shù)的結(jié)結(jié)果來(lái)過(guò)過(guò)濾濾查詢的結(jié)果果.select studeentid,avg(mmark) as avverageemarkffrom sstudenntexammwheree avg(mark)70ggroup by sttudenttid;(此此句錯(cuò)誤,wwhere 句子是不能能用聚合函數(shù)數(shù)作條件的)此時(shí)要用hhavingg.select studeentid,avg(mmark) from studee

59、ntexaam grooup byy studdentidd haviing avvg(marrk)700 or aavg(maark)70;(先先分組,再過(guò)過(guò)濾,再haaving聚聚合,最后再再統(tǒng)計(jì)).select studeentid ,avg(mark) as aaveraggemarkkfrom studeentexaamwherre exaamid iin(5,88,11)ggroup by sttudenttidhavving aavg(maark)70;返回限定行數(shù)查查詢:select name from studeent whhere rrownumm=10;oracle 中使

60、用roownum關(guān)關(guān)鍵字指定,但該關(guān)鍵字字必須在whhere子句句中與一個(gè)比比較運(yùn)算符一一起指定,而而不能與orrder bby 一起配配合便用,因因?yàn)閞ownnum 維護(hù)護(hù)的是原始行行號(hào).如果需需要用grooup byyordeer by 就用子句查查詢作表使用用的方法:select studeentid,averaagemarrk froom(sellect sstudenntid,aavg(maark) aas aveeragemmarkfrrom sttudenttexamggroup by sttudenttid orrder bby aveeragemmark ddesc)wwhe