oracle存儲過程超詳細(xì)使用手冊

1、 HYPERLINK Oracle存儲過程總結(jié)1、創(chuàng)建存儲過程create or replace procedure test(var_name_1 in type,var_name_2 out type) as-聲明變量(變量名 變變量類型)begin-存儲過程的的執(zhí)行體end tesst;打印出輸入的時時間信息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、判斷語句: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í)行語句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ù)組類型，定定義方式見標(biāo)標(biāo)題6)i numbeer;begini := 1; -存儲過過程數(shù)組是起起始位置是從從1開始的，與與java、CC、C+等等語言不同。因因?yàn)樵贠raacle中本本是沒有數(shù)組組的概念的，數(shù)數(shù)組其實(shí)就是是一張-表(Tabble),每每個數(shù)組元素素就是表中的的一個記錄，所所以遍歷數(shù)組組時就相當(dāng)于于從表中的第第一條記錄開開始遍歷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 條條件語句 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ù)組首先明確一個概概念：Oraacle中本本是沒有數(shù)組組的

6、概念的，數(shù)數(shù)組其實(shí)就是是一張表(TTable),每個數(shù)組組元素就是表表中的一個記記錄。使用數(shù)組時，用用戶可以使用用Oraclle已經(jīng)定義義好的數(shù)組類類型，或可根根據(jù)自己的需需要定義數(shù)組組類型。(1)使用Orracle自自帶的數(shù)組類類型x arrayy; -使使用時需要需需要進(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ù)類類型時，建議議通過創(chuàng)建PPackagge的方式實(shí)實(shí)現(xiàn)，

7、以便于于管理)E.g (自定定義使用參見見標(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; -此處聲明明了一個TeestArrray的類型型數(shù)據(jù)，其實(shí)實(shí)其為一張存存儲Infoo數(shù)據(jù)類型的的Tablee而已，及TTestArrray 就就是一

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

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

12、的存儲過程的的用法做一個個應(yīng)用：現(xiàn)假設(shè)存在兩張張表，一張是是學(xué)生成績表表(studdnet)，字字段為：sttdId,mmath,aarticlle,lannguagee,musiic,spoort,tootal,aaveragge,steep 一張是學(xué)生生課外成績表表(out_schoool),字段段為:stddId,paarcticce,commment通過存儲過程自自動計(jì)算出每每位學(xué)生的總總成績和平均均成績，同時時，如果學(xué)生生在課外課程程中獲得的評評價為A，就就在總成績上上加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()的的存儲過程獲獲取學(xué)生課外外評分信息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é)生評評論信息的存存儲過程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)目中有涉及存存儲過程對字字符串的處理理，所以就將將在網(wǎng)上查找找到的資料匯匯總，做一個個信息拼接式式的總結(jié)。以下信息均來自自互聯(lián)網(wǎng)，貼貼出來一則自自己保存以待待以后使用，一一則供大家分分享。字符函數(shù)返返回字符值這些函數(shù)全都接接收的是字符符族類型的參參數(shù)(CHRR除外)并且且返回字符值值

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

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

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

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

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

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

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

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

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

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

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

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

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

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

33、EDUREE 存儲過程程名字( 參數(shù)11 IN NNUMBERR, 參數(shù)22 IN NNUMBERR) IS變量1 INTTEGER :=0;變量2 DATTE;BEGINEND 存儲過過程名字2.SELECCT INTTO STAATEMENNT 將seleect查詢的的結(jié)果存入到到變量中，可可以同時將多多個列存儲多多個變量中，必必須有一條 記錄，否則則拋出異常(如果沒有記記錄拋出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ù)庫庫后建立一個個Test WINDOOW 在窗口輸入入調(diào)用SP的的代碼,F99開始debbug,CTTRL+N單單步調(diào)試關(guān)于oraclle存儲過程程的若干問題題備忘1.在oraccle中，數(shù)數(shù)據(jù)表別名不不能加as，如如：selecta.apppnamefromaappinffoa;- 正確selecta.apppnamefromaappinffoasa;- 錯誤也許，是怕和和oraclle中的存儲儲過程中的關(guān)關(guān)鍵字as沖沖突的問題吧吧2.在存儲過程程中，sellect某一一字段時，后后面必須緊跟跟into，如如果seleect整個記記錄，利用游游標(biāo)的話就另另當(dāng)別論了。selec

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

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

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

40、如如下語句：selectsum(vvcountt)inttofcoountffromAAwherrebidd=xxxxxxx;如果A表中不存存在bid=xxxxxxx的記記錄，則fccount=null(即使fcoount定義義時設(shè)置了默默認(rèn)值，如：fcounnt nummber(88):=0依依然無效，ffcountt還是會變成成null)，這樣以后后使用fcoount時就就可能有問題題，所以在這這里最好先判判斷一下：iffcouuntissnulltthenfcoount:=0;endif;這樣就一切okk了。6.Hiberrnate調(diào)調(diào)用oraccle存儲過過程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存儲過程語語法總結(jié)及練練習(xí)-1.存儲過過程之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.存儲過過程之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.存儲過過程之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.同表子查詢詢作為條件a. 給出人口口多于Russsia(俄俄國)的國家家名稱 SEELECT name FROM bbcWHERE ppopulaation(SELECTT popuulatioon FROOM bbccWHERE nname=Russiia) b.給出Inndia(印度), Irann(伊朗)所在地區(qū)的的所有國家的的所有信息 SELECCT * FFROM bbbcWHERE rregionn IN(S

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

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

56、定：EEXISTSS由外而內(nèi)內(nèi)）中得到一一個值。B.異表子查詢詢作為條件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;過濾分組:順序?yàn)橄确纸M,再過濾,最最后進(jìn)行統(tǒng)計(jì)計(jì)(實(shí)際值).select studeentid,countt(*) aas h

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

59、ntexaam grooup byy studdentidd haviing avvg(marrk)700 or aavg(maark)70;(先先分組,再過過濾,再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子句句中與一個比比較運(yùn)算符一一起指定,而而不能與orrder bby 一起配配合便用,因因?yàn)閞ownnum 維護(hù)護(hù)的是原始行行號.如果需需要用grooup byyordeer by 就用子句查查詢作表使用用的方法:select studeentid,averaagemarrk froom(sellect sstudenntid,aavg(maark) aas aveeragemmarkfrrom sttudenttexamggroup by sttudenttid orrder bby aveeragemmark ddesc)wwhe