oracle存儲(chǔ)過(guò)程+觸發(fā)器

1、Oracle 存儲(chǔ)過(guò)程總結(jié)1、創(chuàng)建存儲(chǔ)過(guò)程createorreplaceproceduretest(var_name_1intype,var_name_2outtype)as-聲明變量(變量名變量類型)begin-存儲(chǔ)過(guò)程的執(zhí)行體endtest;打印出輸入的時(shí)間信息E.g:createorreplaceproceduretest(workDateinDate)isbegindbms_output.putline(Theinputdateis:|to_date(workDate,yyyy-mm-dd);endtest;2、變量賦值變量名:=值；E.g:createorreplaceprocedu

2、retest(workDateinDate)isxnumber(4,2);beginx:=1;endtest;3、判斷語(yǔ)句:if 比較式 thenbeginend;endif;E.gcreateorreplaceproceduretest(xinnumber)isbeginifx0thenbeginx:=0-x;end;endif;ifx=0thenbeginx:=1;end;endif;endtest;4、For 循環(huán)For.in.LOOP-執(zhí)行語(yǔ)句endLOOP;(1)循環(huán)遍歷游標(biāo)createorreplaceproceduretest()asCursorcursorisselectnam

3、efromstudent;namevarchar(20);beginfornameincursorLOOPbegindbms_output.putline(name);end;endLOOP;endtest;(2)循環(huán)遍歷數(shù)組createorreplaceproceduretest(varArrayinmyPackage.TestArray)as-(輸入?yún)?shù) varArray 是自定義的數(shù)組類型，定義方式見(jiàn)標(biāo)題 6)1number;begini:=1;-存儲(chǔ)過(guò)程數(shù)組是起始位置是從 1 開始的，與 java、GC+埸語(yǔ)言不同。因?yàn)樵?Oracle 中本是沒(méi)有數(shù)組的概念的，數(shù)組其實(shí)就是一張-表(T

4、able),每個(gè)數(shù)組元素就是表中的一個(gè)記錄，所以遍歷數(shù)組時(shí)就相當(dāng)于從表中的第一條記錄開始遍歷foriin1.varArray.countLOOPdbms_output.putline(TheNo.|i|recordinvarArrayis:|varArray(i)；endLOOP;endtest；5、While 循環(huán)while 條件語(yǔ)句 LOOPbeginend;endLOOP;E.gcreateorreplaceproceduretest(iinnumber)asbeginwhilei10LOOPbegini:=i+1;end;endLOOP;endtest;6、數(shù)組首先明確一個(gè)概念：Ora

5、cle 中本是沒(méi)有數(shù)組的概念的，數(shù)組其實(shí)就是一張表(Table),每個(gè)數(shù)組元素就是表中的一個(gè)記錄。使用數(shù)組時(shí)，用戶可以使用 Oracle 已經(jīng)定義好的數(shù)組類型，或可根據(jù)自己的需要定義數(shù)組類型。使用 Oracle 自帶的數(shù)組類型xarray;-使用時(shí)需要需要進(jìn)行初始化e.g:createorreplaceproceduretest(youtarray)isxarray;beginx:=newarray();y:=x;endtest;(2)自定義的數(shù)組類型(自定義數(shù)據(jù)類型時(shí)，建議通過(guò)創(chuàng)建 Package 的方式實(shí)現(xiàn)，以便于管理)E.g(自定義使用參見(jiàn)標(biāo)題 4.2)createorreplacepa

6、ckagemyPackageis-Publictypedeclarationstypeinfoisrecord(namevarchar(20),ynumber);typeTestArrayistableofinfoindexbybinary_integer;-止匕處聲明了一個(gè) TestArray 的類型數(shù)據(jù)，其實(shí)其為一張存儲(chǔ) Info 數(shù)據(jù)類型的 Table 而已，及TestArray 就是一張表，有兩個(gè)字段，一個(gè)是name 一個(gè)是 y。 需要注意的是此處使用了 Indexbybinary_integer 編制該 Table 的索引項(xiàng)，也可以不寫，直接寫成：typeTestArrayistab

7、leofinfo,如果不寫的話使用數(shù)組時(shí)就需要進(jìn)行初始化：varArraymyPackage.TestArray;varArray:=newmyPackage.TestArray();endTestArray;7.游標(biāo)的使用 Oracle 中 Cursor 是非常有用的，用于遍歷臨時(shí)表中的查詢結(jié)果。其相關(guān)方法和屬性也很多，現(xiàn)僅就常用的用法做一二介紹：(1)Cursor 型游標(biāo)(不能用于參數(shù)傳遞)createorreplaceproceduretest()iscusor_1Cursorisselectstd_namefromstudentwhere.;-Cursor的使用方式 1cursor_2

8、Cursor;beginselectclass_nameintocursor_2fromclasswhere.;-Cursor 的使用方式 2可使用 ForxincursorLOOP.endLOOP;來(lái)實(shí)現(xiàn)對(duì) Cursor 的遍歷endtest;(2)SYS_REFCURSOR 游標(biāo)，該游標(biāo)是 Oracle 以預(yù)先定義的游標(biāo)，可作出參數(shù)進(jìn)行傳遞createorreplaceproceduretest(rsCursoroutSYS_REFCURSOR)iscursorSYS_REFCURSOR;namevarhcar(20);beginOPENcursorFORselectnamefromstu

9、dentwhere.-SYS_REFCURSOR 只能通過(guò)OPENt 法來(lái)打開和賦值LOOPfetchcursorintoname-SYS_REFCURSOR 只能通過(guò) fetchinto 來(lái)打開和遍歷exitwhencursor%NOTFOUND;-SYS_REFCURSOR 中可使用三個(gè)狀態(tài)屬性：-%NOTFOUND(未找到記錄信息)%FOUN 坐到記錄信息)-%ROWCOUNT(然后當(dāng)前游標(biāo)所指向的行位置)dbms_output.putline(name);endLOOP;rsCursor:=cursor;endtest;下面寫一個(gè)簡(jiǎn)單的例子來(lái)對(duì)以上所說(shuō)的存儲(chǔ)過(guò)程的用法做一個(gè)應(yīng)用：現(xiàn)假設(shè)

10、存在兩張表，一張是學(xué)生成績(jī)表(studnet),字段為：stdId,math,article,language,music,sport,total,average,step 一張是學(xué)生課外成績(jī)表(out_school),字段為:stdId,parctice,comment通過(guò)存儲(chǔ)過(guò)程自動(dòng)計(jì)算出每位學(xué)生的總成績(jī)和平均成績(jī)，同時(shí)，如果學(xué)生在課外課程中獲得的評(píng)價(jià)為 A,就在總成績(jī)上加 20 分。createorreplaceprocedureautocomputer(stepinnumber)isrsCursorSYS_REFCURSOR;commentArraymyPackage.myArray;

11、mathnumber;articlenumber;languagenumber;musicnumber;sportnumber;totalnumber;averagenumber;stdIdvarchar(30);recordmyPackage.stdInfo;inumber;begini:=1;get_comment(commentArray);-調(diào)用名為 get_comment()的存儲(chǔ)過(guò)程獲取學(xué)生課外評(píng)分信息OPENrsCursorforselectstdId,math,article,language,music,sportfromstudenttwheret.step=step;LO

12、OPfetchrsCursorintostdId,math,article,language,music,sport;exitwhenrsCursor%NOTFOUND;total:=math+article+language+music+sport;mentArray.countLOOPrecord:=commentArray(i);ifstdId=record.stdIment='A'thenbegintotal:=total+20;gotonext;-使用 goto 跳出 for 循環(huán)end;endif;end;endif;endLOOP;averag

13、e:=total/5;updatestudenttsett.total=totalandt.average=averagewheret.stdId=stdId;endLOOP;end;endautocomputer;-取得學(xué)生評(píng)論信息的存儲(chǔ)過(guò)程createorreplaceprocedureget_comment(commentArrayoutmyPackage.myArray)isrsSYS_REFCURSORrecordmyPackage.stdInfo;stdIdvarchar(30);commentvarchar(1);inumber;beginopenrsforselectstdId

14、,commentfromout_schooli:=1;LOOPfetchrsintostdId,comment;exitwhenrs%NOTFOUND;record.stdId:=stdId;ment:=comment;recommentArray(i):=record;i:=i+1;endLOOP;endget_comment;-定義數(shù)組類型 myArraycreateorreplacepackagemyPackageisbegintypestdInfoisrecord(stdIdvarchar(30),commentvarchar(1);typemyArrayistableofstdInf

15、oindexbybinary_integer;endmyPackage;項(xiàng)目中有涉及存儲(chǔ)過(guò)程對(duì)字符串的處理，所以就將在網(wǎng)上查找到的資料匯總，做一個(gè)信息拼接式的總結(jié)。以下信息均來(lái)自互聯(lián)網(wǎng)，貼出來(lái)一則自己保存以待以后使用，一則供大家分享。字符函數(shù)一一返回字符值這些函數(shù)全都接收的是字符族類型的參數(shù)(CHR除外)并且返回字符值.除了特別說(shuō)明的之外，這些函數(shù)大部分返回VARCHAR2型的數(shù)值.字符函數(shù)的返回類型所受的限制和基本數(shù)據(jù)庫(kù)類型所受的限制是相同的。字符型變量存儲(chǔ)的最大值：VARCHAR數(shù)值被PM制為2000字符(ORACLE8中為4000字符)CHAR數(shù)值被PM制為255字符(在ORACLE即是

16、2000)long類型為2GBClob類型為4GB1、CHR語(yǔ)法：chr(x)功能：返回在數(shù)據(jù)庫(kù)字符集中與X擁有等價(jià)數(shù)值的字符。CH濟(jì)口ASCII是一對(duì)反函數(shù)。經(jīng)過(guò)CHR專換后的字符再經(jīng)過(guò)ASCII轉(zhuǎn)換又得到了原來(lái)的字符。使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。2、CONCAT語(yǔ)法：CONCAT(string1,string2)功能：返回stringl,并且在后面連接string2。使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。3、INITCAP語(yǔ)法：INITCAP(string)功能：返回字符串的每個(gè)單詞的第一個(gè)字母大寫而單詞中的其他字母小寫的string。單詞是用.空格或給字母數(shù)字字符進(jìn)行分隔。不是字母的字

17、符不變動(dòng)。使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。4、LTRIM語(yǔ)法：LTRIMI(string1,string2)功能：返回刪除從左邊算起出現(xiàn)在string2中的字符的string1。String2被缺省設(shè)置為單個(gè)的空格。數(shù)據(jù)庫(kù)將掃描string1,從最左邊開始。當(dāng)遇到不在string2中的第一個(gè)字符，結(jié)果就被返回了。LTRIM的行為方式與RTRIMB相似。使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。5、NLS_INITCAP語(yǔ)法：NLS_INITCAP(string,nlsparams)功能：返回字符串每個(gè)單詞第一個(gè)字母大寫而單詞中的其他字母小寫的string,nlsparams指定了不同于該會(huì)話缺省值的

18、不同排序序列。如果不指定參數(shù)，則功能和INITCAP相同。Nlsparams可以使用的形式是：NLS_SORT=sort這里sort制訂了一個(gè)語(yǔ)言排序序列。使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。6、NLS_LOWER語(yǔ)法：NLS_LOWERstring,nlsparams)功能：返回字符串中的所有字母都是小寫形式的string。不是字母的字符不變。Nlsparams參數(shù)的形式與用途和NLS_INITCAP中的nlsparams參數(shù)是相同的。如果nlsparams沒(méi)有被包含，那么NLS_LOWER作的處理和LOWERI同。使用位置；過(guò)程性語(yǔ)句和SQL語(yǔ)句。7、NLS_UPPER語(yǔ)法：nls_uppe

19、r(string,nlsparams)功能：返回字符串中的所有字母都是大寫的形式的string。不是字母的字符不變。nlsparams參數(shù)的形式與用途和NLS_INITCAP中的相同。如果沒(méi)有設(shè)定參數(shù)，貝UNLS_UPPE功能和UPPE充目同。使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。8、REPLACE語(yǔ)法：REPLACEstring,search_str,replace_str)功能：把string中的所有的子字符串search_str用可選的replace_str替換，如果沒(méi)有指定replace_str,所有的string中的子字符串search_str都將被刪除。REPLAC思TRANSLAT所

20、提供的功能的一個(gè)子集。使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。9、RPAD語(yǔ)法：RPAD(string1,x,string2)功能：返回在X字符長(zhǎng)度的位置上插入一個(gè)string2中的字符的string1。如果string2的長(zhǎng)度要比X字符少，就按照需要進(jìn)行復(fù)制。如果string2多于X字符，則僅string1前面的X各字符被使用。如果沒(méi)有指定string2,那么使用空格進(jìn)行填充。X是使用顯示長(zhǎng)度可以比字符串的實(shí)際長(zhǎng)度要長(zhǎng)。RPAD勺行為方式與LPADf艮相似，除了它是在右邊而不是在左邊進(jìn)行填充。使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。10、RTRIM語(yǔ)法：RTRIM(string1,string2)功能：

21、返回刪除從右邊算起出現(xiàn)在string1中出現(xiàn)的字符string2.string2被缺省設(shè)置為單個(gè)的空格.數(shù)據(jù)庫(kù)將掃描string1,從右邊開始.當(dāng)遇到不在string2中的第一個(gè)字符，結(jié)果就被返回了RTRIM的行為方式與LTRIM很相似.使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。11、SOUNDEX語(yǔ)法：SOUNDEX(string)功能：返回string的聲音表示形式.這對(duì)于比較兩個(gè)拼寫不同但是發(fā)音類似的單詞而言很有幫助.使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。12、SUBSTR語(yǔ)法：SUBSTR(string,a,b)功能：返回從字母為值a開始b個(gè)字符長(zhǎng)的string的一個(gè)子字符串.如果a是0,那么它就

22、被認(rèn)為從第一個(gè)字符開始.如果是正數(shù)，返回字符是從左邊向右邊進(jìn)行計(jì)算的.如果b是負(fù)數(shù)，那么返回的字符是從string的末尾開始從右向左進(jìn)行計(jì)算的.如果b不存在，那么它將缺省的設(shè)置為整個(gè)字符串.如果b小于1,那么將返回NULL.如果a或b使用了浮點(diǎn)數(shù)，那么該數(shù)值將在處理進(jìn)行以前首先被卻為一個(gè)整數(shù).使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。13、TRANSLATE語(yǔ)法：TRANSLATE(string,from_str,to_str)功能：返回將所出現(xiàn)的from_str中的每個(gè)字符替換為to_str中的相應(yīng)字符以后的string.TRANSLATE是REPLAC所提供的功能的一個(gè)超集.如果from_str比

23、to_str長(zhǎng)，那么在from_str中而不在to_str中而外的字符將從string中被刪除，因?yàn)樗鼈儧](méi)有相應(yīng)的替換字符.to_str不能為空.Oracle把空字符串認(rèn)為是NULL,并且如果TRANSLATE的任何參數(shù)為NULL,那么結(jié)果也是NULL.使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。14、UPPER語(yǔ)法：UPPER(string)功能：返回大寫的string.不是字母的字符不變.如果string是CHA嚶?lián)愋偷?，那么結(jié)果也是CHA戲型的.如果string是VARCHAR2型的，那么結(jié)果也是VARCHAR2型的.使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。字符函數(shù)一一返回?cái)?shù)字這些函數(shù)接受字符參數(shù)回?cái)?shù)

24、字結(jié)果.參數(shù)可以是CHA臧者是VARCHAR2型的.盡管實(shí)際下許多結(jié)果都是整數(shù)值，但是返回結(jié)果都是簡(jiǎn)單的NUMBER類型的，沒(méi)有定義任何的精度或刻度范圍.16、ASCII語(yǔ)法：ASCII(string)功能：數(shù)據(jù)庫(kù)字符集返回string的第一個(gè)字節(jié)的十進(jìn)制表示.請(qǐng)注意該函數(shù)仍然稱作為ASCII.盡管許多字符集不是7位ASCII.CHR和ASCII是互為相反的函數(shù).CHR得到給定字符編碼的響應(yīng)字符.ASCII得到給定字符的字符編碼.使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。17、INSTR語(yǔ)法：INSTR(stringl,string2a,b)功能：得到在string1中包含string2的位置.str

25、ing1時(shí)從左邊開始檢查的，開始的位置為a,如果a是一個(gè)負(fù)數(shù)，那么string1是從右邊開始進(jìn)行掃描的.第b次出現(xiàn)的位置將被返回.a和b都缺省設(shè)置為1,這將會(huì)返回在string1中第一次出現(xiàn)string2的位置.如果string2在a和b的規(guī)定下沒(méi)有找到，那么返回0.位置的計(jì)算是相對(duì)于string1的開始位置的，不管a和b的取值是多少.使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。18、INSTRB語(yǔ)法：INSTRB(string1,string2a,b)功能：和INSTR相同，只是操作的對(duì)參數(shù)字符使用的位置的是字節(jié)使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。19、LENGTH語(yǔ)法：LENGTH(string)功能

26、：返回string的字節(jié)單位的長(zhǎng)度.CHAR數(shù)值是填充空格類型的，如果string由數(shù)據(jù)類型CHAR它的結(jié)尾的空格都被計(jì)算到字符串長(zhǎng)度中間如果string是NULL,返回結(jié)果是NULL,而不是0.使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。20、LENGTHB語(yǔ)法：LENGTHB(string)功能：返回以字節(jié)為單位的string的長(zhǎng)度.對(duì)于單字節(jié)字符集LENGTHB口LENGTH1一樣的.使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。21、NLSSORT語(yǔ)法：NLSSORT(string,nlsparams)功能：得到用于排序string的字符串字節(jié).所有的數(shù)值都被轉(zhuǎn)換為字節(jié)字符串，這樣在不同數(shù)據(jù)庫(kù)之間就保持了一

27、致性.Nlsparams的作用和NLS_INITCAP中的相同.如果忽略參數(shù)，會(huì)話使用缺省排序.使用位置：過(guò)程性語(yǔ)句和SQL語(yǔ)句。oracle 存儲(chǔ)過(guò)程的基本語(yǔ)法1.基本結(jié)構(gòu)CREATEORREPLACEPROCEDURE 存儲(chǔ)過(guò)程名字（參數(shù) 1INNUMBER,參數(shù) 2INNUMBER）IS變量 1INTEGER:=0;變量 2DATE;BEGINEND 存儲(chǔ)過(guò)程名字2.SELECTINTOSTATEMENT將 select 查詢的結(jié)果存入到變量中，可以同時(shí)將多個(gè)列存儲(chǔ)多個(gè)變量中，必須有一條記錄，否則拋出異常（如果沒(méi)有記錄拋出 NO_DATA_FOUND）例子：BEGINSELECTcol1

28、,col2into 變量 1,變量 2FROMtypestructwherexxx;EXCEPTIONWHENNO_DATA_FOUNDTHENxxxx;END;.1.1 F 判斷IFV_TEST=1THENBEGINdosomethingEND;ENDIF;1、.while 循環(huán)WHILEV_TEST=1LOOPBEGINXXXXEND;ENDLOOP;2、.變量賦值V_TEST:=123;3、.用 forin 使用 cursorISCURSORcurISSELECT*FROMxxx;BEGINFORcur_resultincurLOOPBEGINV_SUM:=cur_result.歹 U

29、名 1+cur_result.歹 U 名 2END;ENDLOOP;END;4、.帶參數(shù)的 cursorCURSORC_USER(C_IDNUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_ID;OPENC_USER(變量值);LOOPFETCHC_USERINTOV_NAME;EXITFETCHC_USER%NOTFOUND;dosomethingENDLOOP;CLOSEC_USER;5、.用 pl/sqldeveloperdebug連接數(shù)據(jù)庫(kù)后建立一個(gè) TestWINDOW在窗口輸入調(diào)用 SP 的代碼，F(xiàn)9 開始 debug,CTRL+N 單步調(diào)試關(guān)于 or

30、acle 存儲(chǔ)過(guò)程的若干問(wèn)題備忘.在 oracle 中，數(shù)據(jù)表別名不能加 as,如：selecta.appnamefromappinfoa;-正確selecta.appnamefromappinfoasa;-錯(cuò)誤也許，是怕和 oracle 中的存儲(chǔ)過(guò)程中的關(guān)鍵字 as 沖突的問(wèn)題吧.在存儲(chǔ)過(guò)程中，select 某一字段時(shí)，后面必須緊跟 into,如果 select 整個(gè)記錄，利用游標(biāo)的話就另當(dāng)別論了。selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;-有 into,正確編譯一 sel

31、ectaf.keynodefromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;-沒(méi)有 into,編譯報(bào)錯(cuò)，提示：Compilation一Error:PLS-00428:anINTOclauseisexpectedinthisSELECTstatement.在利用 o.語(yǔ)法時(shí)，必須先確保數(shù)據(jù)庫(kù)中有該條記錄，否則會(huì)報(bào)出nodatafound異常。可以在該語(yǔ)法之前，先利用 selectcount(*)from 查看數(shù)據(jù)庫(kù)中是否存在該記錄，如果存在，再利用 o.在存儲(chǔ)過(guò)程中，別名不能和字段名稱相同

32、，否則雖然編譯可以通過(guò)，但在運(yùn)行階段會(huì)報(bào)錯(cuò)selectkeynodeintoknfromAPPFOUNDATIONwhereappid=aidandfoundationid=fid;-正確運(yùn)行selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=appidandaf.foundationid=foundationid;-運(yùn)行階段報(bào)錯(cuò)，提示ORA-01422:exactfetchreturnsmorethanrequestednumberofrows.在存儲(chǔ)過(guò)程中，關(guān)于出現(xiàn) null 的問(wèn)題假設(shè)有一個(gè)表 A,定義如下：createtable

33、A(idvarchar2(50)primarykeynotnull,_vcountnumber(8)notnull,bidvarchar2(50)notnull 一外鍵);如果在存儲(chǔ)過(guò)程中，使用如下語(yǔ)句：selectsum(vcount)intofcountfromAwherebid=xxxxxx;如果 A 表中不存在 bid=xxxxxx的記錄，則 fcount=null(即使 fcount 定義時(shí)設(shè)置了默認(rèn)值，如：fcountnumber(8):=0 依然無(wú)效，fcount 還是會(huì)變成 null),這樣以后使用 fcount 時(shí)就可能有問(wèn)題，所以在這里最好先判斷一下：iffcountisn

34、ullthenfcount:=0;endif;這樣就一切 ok 了。.Hibernate 調(diào)用 oracle 存儲(chǔ)過(guò)程this.pnumberManager.getHibernateTemplate().execute(newHibernateCallback()publicObjectdoInHibernate(Sessionsession)throwsHibernateException,SQLExceptionCallableStatementcs=session.connection。.prepareCall(callmodifyapppnumber_remain(?);cs.setS

35、tring(1,foundationid);cs.execute();returnnull;);oracle 存儲(chǔ)過(guò)程語(yǔ)法總結(jié)及練習(xí)-1.存儲(chǔ)過(guò)程之ifclear;createorreplaceproceduremydel(in_aininteger)asbeginifin_a100thendbms_output.put_line(elsifin_a301;endloop;end;/setserveroutputon;beginmydel(2);end;/-1.存儲(chǔ)過(guò)程之100P2clear;createorreplaceproceduremydel(in_aininteger)asainte

36、ger;begina:=0;whilea300loopdbms_output.put_line(a);a:=a+1;endloop;end;/setserveroutputon;beginmydel(2);end;-1.存儲(chǔ)過(guò)程之100P3clear;createorreplaceproceduremydel(in_aininteger)asainteger;beginforain0.300loopdbms_output.put_line(a);endloop;end;/setserveroutputon;beginmydel(2);end;/clear;selectename,cc:=(ca

37、sewhencomm=nullthensal*12;else(sal+comm)*12;endcasefromemporderbysalpersal;clear;createorreplaceproceduregetstudentcomments(i_studentidinint,o_commentsoutvarchar)asexams_satint;avg_markint;tmp_commentsvarchar(100);beginselectcount(examid)intoexams_satfromstudentexamwherestudentid=i_studentid;ifexams

38、_sat=0thentmp_comments:=n/a-thisstudentdidnotattendtheexam!;elseselectavg(mark)intoavg_markfromstudentexamwherestudentid=i_studentid;casewhenavg_mark50thentmp_comments:=verybad;whenavg_mark60thentmp_comments:=bad;whenavg_mark70thentmp_comments:=good;endcase;endif;o_comments:=tmp_comments;end;/setser

39、veroutputon;ments%type;begingetstudentcomments(8,pp);dbms_output.put_line(pp);end;/deletefromempwhereempno6000;clear;createorreplaceprocedureinsertdata(in_numininteger)asmyNumintdefault0;emp_noemp.empno%type:=1000;beginwhilemyNumin_numloopinsertintoempvalues(emp_no,hui|myNum,coder,7555,current_date,

40、8000,6258,30);emp_no:=emp_no+1;myNum:=myNum+1;endloop;end;/setserveroutputon;begininsertdata(10);end;/select*fromemp;clear;selectstudentname,averageMark,casewhenaverageMark60then不及格whenaverageMark70then考得好whenaverageMark(selectsalfromempwhereename=SMITH);3selecta.ename,(selectenamefromempbwhereb.emp

41、no=a.mgr)asbossnamefromempa;4selecta.enamefromempawherea.hiredate1500;8selectenamefromempwheredeptno=(selectdeptnofromdeptwheredname=SALES);9selectenamefromempwheresal(selectavg(sal)fromemp);10selectenamefromempwherejob=(selectjobfromempwhereename=SCOTT);11selecta.ename,a.salfromempawherea.salin(sel

42、ectb.salfromempbwhereb.deptno=30)anda.deptno30;12selectename,salfromempwheresal(selectmax(sal)fromempwheredeptno=30);13select(selectb.dnamefromdeptbwherea.deptno=b.deptno)asdeptname,count(deptno)asdeptcount,avg(sal)asdeptavgsalfromempagroupbydeptno;14selecta.ename,(selectb.dnamefromdeptbwhereb.deptn

43、o=a.deptno)asdeptname,salfromempa;15selecta.deptno,a.dname,a.loc,(selectcount(deptno)fromempbwhereb.deptno=a.deptnogroupbyb.deptno)asdeptcountfromdepta;16selectjob,avg(sal)fromempgroupbyjob;17selectdeptno,min(sal)fromempwherejob=MANAGERgroupbydeptno;18selectename,(sal+nvl(comm,0)*12assalpersalfromem

44、porderbysalpersal;ORACLE 旬查詢，分組等A.同表子查詢作為條件a.給出人口多于Russia(俄國(guó))的國(guó)家名稱SELECTnameFROMbbcWHEREpopulation(SELECTpopulationFROMbbcWHEREname=Russia)b.給出India(印度),Iran(伊朗)所在地區(qū)的所有國(guó)家的所有信息SELECT*FROMbbcWHEREregionIN(SELECTregionFROMbbcWHEREnameIN(India,Iran)c.給出人均GD剛過(guò)UnitedKingdom(英國(guó))的歐洲國(guó)家.SELECTnameFROMbbcWHERE

45、region=EuropeANDgdp/population(SELECTgdp/populationFROMbbcWHEREname=UnitedKingdom)d.這個(gè)查詢實(shí)際上等同于以下這個(gè)：selecte1.enamefromempe1,(selectempnofromempwhereename=KING)e2wheree1.mgr=e2.empno;你可以用EXISTS寫同樣的查詢，你只要把外部查詢一欄移到一個(gè)像下面這樣的子查詢環(huán)境中就可以了：selectenamefromempewhereexists(select0fromempwheree.mgr=empnoandename=K

46、ING);當(dāng)你在一個(gè)WHERETO中寫EXISTS時(shí)，又等于向最優(yōu)化傳達(dá)了這樣一條信息，即你想讓外部查詢先運(yùn)行，使用每一個(gè)值來(lái)從內(nèi)部查詢(假定：EXISTS=由外而內(nèi))中得到一個(gè)值。B.異表子查詢作為條件a.select*fromstudentExamwherestudentid=(selectstudentidfromstudentwherename=吳麗麗);b.select*fromstudentexamwherestudentidin(selectstudentidfromstudent)orderbystudentid;c.select*fromstudentwherestudent

47、idin(selectstudentidfromstudentexamwheremark80);3.selectstudentexam.mark,studentexam.studentidasseid,student.studentid,fromstudentexam,studentwherestudent.studentid=studentexam.studentid;過(guò)濾分組：順序?yàn)橄确纸M，再過(guò)濾，最后進(jìn)行統(tǒng)計(jì)(實(shí)際值).selectstudentid,count(*)ashighpassesfromstudentexamwheremark70groupbystud

48、entid;假使我們不想通過(guò)數(shù)據(jù)表中的實(shí)際值，而是通過(guò)聚合函數(shù)的結(jié)果來(lái)過(guò)過(guò)濾查詢的結(jié)果.selectstudentid,avg(mark)asaveragemarkfromstudentexamwhereavg(mark)70groupbystudentid;(此句錯(cuò)誤，where句子是不能用聚合函數(shù)作條件的)此時(shí)要用having.selectstudentid,avg(mark)fromstudentexamgroupbystudentidhavingavg(mark)70oravg(mark)70;(先分組，再過(guò)濾，再having聚合，最后再統(tǒng)計(jì)).selectstudentid,avg(

49、mark)asaveragemarkfromstudentexamwhereexamidin(5,8,11)groupbystudentidhavingavg(mark)70;返回限定行數(shù)查詢：selectnamefromstudentwhererownum=10;oracle中使用rownum關(guān)鍵字指定，但該關(guān)鍵字必須在where子句中與一個(gè)比較運(yùn)算符一起指定，而不能與orderby一起配合便用，因?yàn)閞ownum維護(hù)的是原始行號(hào).如果需要用groupbyorderby就用子句查詢作表使用的方法：selectstudentid,averagemarkfrom(selectstudentid,a

50、vg(mark)asaveragemarkfromstudentexamgroupbystudentidorderbyaveragemarkdesc)whererownum=10;oracle 存儲(chǔ)過(guò)程語(yǔ)法:Oracle 存儲(chǔ)過(guò)程入門學(xué)習(xí)基本語(yǔ)法.基本結(jié)構(gòu)createORREPLACEPROCEDURE 存儲(chǔ)過(guò)程名字(參數(shù) 1INNUMBER,參數(shù) 2INNUMBER)IS變量 1INTEGER:=0;變量 2DATE;BEGINEND 存儲(chǔ)過(guò)程名字.selectINTOSTATEMENT將 select 查詢的結(jié)果存入到變量中，可以同時(shí)將多個(gè)列存儲(chǔ)多個(gè)變量中，必須有一條記錄，否則拋出異常(如

51、果沒(méi)有記錄拋出 NO_DATA_FOUND)例子：BEGINselectcol1,col2into 變量 1,變量 2FROMtypestructwherexxx;EXCEPTIONWHENNO_DATA_FOUNDTHENxxxx;END;.3.IF 判斷 IFV_TEST=1THENBEGINdosomethingEND;ENDIF;.while 循環(huán)WHILEV_TEST=1LOOPBEGINXXXXEND;ENDLOOP;.變量賦值V_TEST:=123;.用 forin 使用 cursor.ISCURSORcurISselect*FROMxxx;BEGINFORcur_resulti

52、ncurLOOPBEGINV_SUM:=cur_result.列名 1+cur_result.列名 2END;ENDLOOP;END;.帶參數(shù)的 cursorCURSORC_USER（C_IDNUMBER）ISselectNAMEFROMUSERwhereTYPEID=C_ID;OPENC_USER（變量值）;LOOPFETCHC_USERINTOV_NAME;EXITFETCHC_USER%NOTFOUND;dosomethingENDLOOP;CLOSEC_USER;.用 pl/sqldeveloperdebug連接數(shù)據(jù)庫(kù)后建立一個(gè) TestWINDOW在窗口輸入調(diào)用 SP 的代碼，F(xiàn)9

53、開始 debug,CTRL+N 單步調(diào)試oracle 語(yǔ)法:Oracle 觸發(fā)器語(yǔ)法及實(shí)例基礎(chǔ)知識(shí)（一）一 Oracle 觸發(fā)器語(yǔ)法觸發(fā)器是特定事件出現(xiàn)的時(shí)候，自動(dòng)執(zhí)行的代碼塊類似于存儲(chǔ)過(guò)程，觸發(fā)器和存儲(chǔ)過(guò)程的區(qū)別在于:存儲(chǔ)過(guò)程是由用戶或應(yīng)用程序顯式調(diào)用的，而觸發(fā)器是不能被直接調(diào)用的功能：1、允許/限制對(duì)表的修改2、自動(dòng)生成派生列，比如自增字段3、強(qiáng)制數(shù)據(jù)一致性4、提供審計(jì)和日志記錄5、防止無(wú)效的事務(wù)處理6、啟用復(fù)雜的業(yè)務(wù)邏輯觸發(fā)器觸發(fā)時(shí)間有兩種：after 和 before1、觸發(fā)器的語(yǔ)法：CREATEORREPLACETIGGER 觸發(fā)器名觸發(fā)時(shí)間觸發(fā)事件ON 表名FOREACHROWBE

54、GINpl/sql 語(yǔ)句END其中：觸發(fā)器名：觸發(fā)器對(duì)象的名稱由于觸發(fā)器是數(shù)據(jù)庫(kù)自動(dòng)執(zhí)行的，因此該名稱只是一個(gè)名稱，沒(méi)有實(shí)質(zhì)的用途觸發(fā)時(shí)間：指明觸發(fā)器何時(shí)執(zhí)行，該值可?。篵efore-表示在數(shù)據(jù)庫(kù)動(dòng)作的前觸發(fā)器執(zhí)行；after-表示在數(shù)據(jù)庫(kù)動(dòng)作的后出發(fā)器執(zhí)行觸發(fā)事件：指明哪些數(shù)據(jù)庫(kù)動(dòng)作會(huì)觸發(fā)此觸發(fā)器：insert:數(shù)據(jù)庫(kù)插入會(huì)觸發(fā)此觸發(fā)器；update:數(shù)據(jù)庫(kù)修改會(huì)觸發(fā)此觸發(fā)器delete:數(shù)據(jù)庫(kù)刪除會(huì)觸發(fā)此觸發(fā)器表名:數(shù)據(jù)庫(kù)觸發(fā)器所在的表foreachrow:對(duì)表的每一行觸發(fā)器執(zhí)行一次如果沒(méi)有這一選項(xiàng)，則只對(duì)整個(gè)表執(zhí)行一次2、舉例：下面的觸發(fā)器在更新表 auths 的前觸發(fā)，目的是不允許在周

55、末修改表：createtriggerauth_securebeforeinsertorupdateordelete/對(duì)整表更新前觸發(fā)onauthsbeginif(to_char(sysdate,DY)=SUNRAISE_APPLICATION_ERROR(-20600,不能在周末修改表 auths);endif;end例子:CREATEORREPLACETRIGGERCRM.T_SUB_USERINFO_AUR_NAMEAFTERUPDATEOFSTAFF_NAMEONCRM.T_SUB_USERINFOREFERENCINGOLDASOLDNEWASNEWFOREACHROWdeclareb

56、eginif:NEW.STAFF_NAME!=:OLD.STAFF_NAMEthenbegin客戶投訴updateT_COMPLAINT_MANAGEsetSERVE_NAME=:NEW.STAFF_NAMEwhereSERVE_SEED=:OLD.SEED;客戶關(guān)懷updateT_CUSTOMER_CAREsetEXECUTOR_NAME=:NEW.STAFF_NAMEwhereEXECUTOR_SEED=:OLD.SEED;客戶服務(wù)updateT_CUSTOMER_SERVICEsetEXECUTOR_NAME=:NEW.STAFF_NAMEwhereEXECUTOR_SEED=:OLD.

57、SEED;end;endif;endT_sub_userinfo_aur_name;/2Oracle 觸發(fā)器詳解開始：createtriggerbiufer_employees_department_idbeforeinsertorupdateofdepartment_idonemployeesreferencingoldasold_valuenewasnew_valueforeachrowwhen(new_value.department_id80)begin:new_mission_pct:=0;end;/1、觸發(fā)器的組成部分：1、觸發(fā)器名稱2、觸發(fā)語(yǔ)句3、觸發(fā)器限制4、觸發(fā)操作觸發(fā)器名稱

58、createtriggerbiufer_employees_department_id命名習(xí)慣:employees 表名department_id 歹 U 名觸發(fā)語(yǔ)句比如：表或視圖上的 DML 語(yǔ)句DDL 語(yǔ)句數(shù)據(jù)庫(kù)關(guān)閉或啟動(dòng),startupshutdown 等等beforeinsertorupdateofdepartment_idonemployeesreferencingoldasold_valuenewasnew_valueforeachrow介紹說(shuō)明：是否規(guī)定了 department_id,對(duì) employees 表進(jìn)行 insert 的時(shí)候employees 表的 departmen

59、t_id 列進(jìn)行 update 的時(shí)候觸發(fā)器限制when(new_value.department_id80)限制不是必須的此例表示如果列 department_id 不等于 80 的時(shí)候，觸發(fā)器就會(huì)執(zhí)行其中的 new_value 是代表更新的后的值觸發(fā)操作是觸發(fā)器的主體begin:new_mission_pct:=0;end;主體很簡(jiǎn)單，就是將更新后的 commission_pct 列置為 0觸發(fā)：insertintoemployees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,c

60、ommission_pct)values(12345,Cherf,Donny,sysdate,12,60,10000,.25);selectcommission_pctfromemployeeswhereemployee_id=12345;2、觸發(fā)器的類型有：觸發(fā)器類型：觸發(fā)器發(fā)器STEADOF 觸發(fā)件觸發(fā)器件觸發(fā)器2.1、語(yǔ)句級(jí)觸發(fā)器.(語(yǔ)句級(jí)觸發(fā)器對(duì)每個(gè) DML 語(yǔ)句執(zhí)彳 f 一次)是在表上或者某些情況下的視圖上執(zhí)行的特定語(yǔ)句或者語(yǔ)句組上的觸發(fā)器能夠和 INSERT、UPDATE、DELETE 或者組合上進(jìn)行關(guān)聯(lián)但是無(wú)論使用什么樣的組合，各個(gè)語(yǔ)句觸發(fā)器都只會(huì)針對(duì)指定語(yǔ)句激活一次比如，無(wú)論 update