oracle存儲過程+觸發(fā)器

1、Oracle 存儲過程總結(jié)1、創(chuàng)建存儲過程createorreplaceproceduretest(var_name_1intype,var_name_2outtype)as-聲明變量(變量名變量類型)begin-存儲過程的執(zhí)行體endtest;打印出輸入的時間信息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、判斷語句: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í)行語句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ù)組類型，定義方式見標(biāo)題 6)1number;begini:=1;-存儲過程數(shù)組是起始位置是從 1 開始的，與 java、GC+埸語言不同。因?yàn)樵?Oracle 中本是沒有數(shù)組的概念的，數(shù)組其實(shí)就是一張-表(T

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

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

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

7、leofinfo,如果不寫的話使用數(shù)組時就需要進(jìn)行初始化：varArraymyPackage.TestArray;varArray:=newmyPackage.TestArray();endTestArray;7.游標(biāo)的使用 Oracle 中 Cursor 是非常有用的，用于遍歷臨時表中的查詢結(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;來實(shí)現(xiàn)對 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 只能通過OPENt 法來打開和賦值LOOPfetchcursorintoname-SYS_REFCURSOR 只能通過 fetchinto 來打開和遍歷exitwhencursor%NOTFOUND;-SYS_REFCURSOR 中可使用三個狀態(tài)屬性：-%NOTFOUND(未找到記錄信息)%FOUN 坐到記錄信息)-%ROWCOUNT(然后當(dāng)前游標(biāo)所指向的行位置)dbms_output.putline(name);endLOOP;rsCursor:=cursor;endtest;下面寫一個簡單的例子來對以上所說的存儲過程的用法做一個應(yīng)用：現(xiàn)假設(shè)

10、存在兩張表，一張是學(xué)生成績表(studnet),字段為：stdId,math,article,language,music,sport,total,average,step 一張是學(xué)生課外成績表(out_school),字段為:stdId,parctice,comment通過存儲過程自動計算出每位學(xué)生的總成績和平均成績，同時，如果學(xué)生在課外課程中獲得的評價為 A,就在總成績上加 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()的存儲過程獲取學(xué)生課外評分信息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é)生評論信息的存儲過程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)目中有涉及存儲過程對字符串的處理，所以就將在網(wǎng)上查找到的資料匯總，做一個信息拼接式的總結(jié)。以下信息均來自互聯(lián)網(wǎng)，貼出來一則自己保存以待以后使用，一則供大家分享。字符函數(shù)一一返回字符值這些函數(shù)全都接收的是字符族類型的參數(shù)(CHR除外)并且返回字符值.除了特別說明的之外，這些函數(shù)大部分返回VARCHAR2型的數(shù)值.字符函數(shù)的返回類型所受的限制和基本數(shù)據(jù)庫類型所受的限制是相同的。字符型變量存儲的最大值：VARCHAR數(shù)值被PM制為2000字符(ORACLE8中為4000字符)CHAR數(shù)值被PM制為255字符(在ORACLE即是

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

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

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

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

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

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

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

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

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

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

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

27、致性.Nlsparams的作用和NLS_INITCAP中的相同.如果忽略參數(shù)，會話使用缺省排序.使用位置：過程性語句和SQL語句。oracle 存儲過程的基本語法1.基本結(jié)構(gòu)CREATEORREPLACEPROCEDURE 存儲過程名字（參數(shù) 1INNUMBER,參數(shù) 2INNUMBER）IS變量 1INTEGER:=0;變量 2DATE;BEGINEND 存儲過程名字2.SELECTINTOSTATEMENT將 select 查詢的結(jié)果存入到變量中，可以同時將多個列存儲多個變量中，必須有一條記錄，否則拋出異常（如果沒有記錄拋出 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ù)庫后建立一個 TestWINDOW在窗口輸入調(diào)用 SP 的代碼，F(xiàn)9 開始 debug,CTRL+N 單步調(diào)試關(guān)于 or

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

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

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

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

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

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

36、ger;begina:=0;whilea300loopdbms_output.put_line(a);a:=a+1;endloop;end;/setserveroutputon;beginmydel(2);end;-1.存儲過程之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(俄國)的國家名稱SELECTnameFROMbbcWHEREpopulation(SELECTpopulationFROMbbcWHEREname=Russia)b.給出India(印度),Iran(伊朗)所在地區(qū)的所有國家的所有信息SELECT*FROMbbcWHEREregionIN(SELECTregionFROMbbcWHEREnameIN(India,Iran)c.給出人均GD剛過UnitedKingdom(英國)的歐洲國家.SELECTnameFROMbbcWHERE

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

46、ING);當(dāng)你在一個WHERETO中寫EXISTS時，又等于向最優(yōu)化傳達(dá)了這樣一條信息，即你想讓外部查詢先運(yùn)行，使用每一個值來從內(nèi)部查詢(假定：EXISTS=由外而內(nèi))中得到一個值。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;過濾分組：順序?yàn)橄确纸M，再過濾，最后進(jìn)行統(tǒng)計(實(shí)際值).selectstudentid,count(*)ashighpassesfromstudentexamwheremark70groupbystud

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

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

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

51、果沒有記錄拋出 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ù)庫后建立一個 TestWINDOW在窗口輸入調(diào)用 SP 的代碼，F(xiàn)9

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

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

55、末修改表：createtriggerauth_securebeforeinsertorupdateordelete/對整表更新前觸發(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ā)語句3、觸發(fā)器限制4、觸發(fā)操作觸發(fā)器名稱

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

59、t_id 列進(jìn)行 update 的時候觸發(fā)器限制when(new_value.department_id80)限制不是必須的此例表示如果列 department_id 不等于 80 的時候，觸發(fā)器就會執(zhí)行其中的 new_value 是代表更新的后的值觸發(fā)操作是觸發(fā)器的主體begin:new_mission_pct:=0;end;主體很簡單，就是將更新后的 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、語句級觸發(fā)器.(語句級觸發(fā)器對每個 DML 語句執(zhí)彳 f 一次)是在表上或者某些情況下的視圖上執(zhí)行的特定語句或者語句組上的觸發(fā)器能夠和 INSERT、UPDATE、DELETE 或者組合上進(jìn)行關(guān)聯(lián)但是無論使用什么樣的組合，各個語句觸發(fā)器都只會針對指定語句激活一次比如，無論 update