Oracle第14章PLSQL語言基礎(chǔ)

1、1第14章 PL/SQL語言基礎(chǔ)2本章內(nèi)容容PL/SQL概述PL/SQL基礎(chǔ)控制結(jié)構(gòu)構(gòu)游標(biāo)異常處理理3本章要求求掌握PL/SQL程序基本本結(jié)構(gòu)掌握PL/SQL程序控制制結(jié)構(gòu)掌握PL/SQL程序游標(biāo)標(biāo)應(yīng)用掌握PL/SQL程序異常常處理機機制414.1PL/SQL概述PL/SQL特點PL/SQL功能特性性PL/SQL執(zhí)行過程程與開發(fā)發(fā)工具514.1.1PL/SQL特點與SQL語言緊密密集成。減小網(wǎng)絡(luò)絡(luò)流量，提高應(yīng)應(yīng)用程序序的運行行性能。模塊化的的程序設(shè)設(shè)計功能能，提高高了系統(tǒng)統(tǒng)可靠性性。服務(wù)器端端程序設(shè)設(shè)計，可可移植性性好。614.1.2PL/SQL功能特性性語句塊結(jié)結(jié)構(gòu)異常處理理變量和類類型條件

2、語句句循環(huán)結(jié)構(gòu)構(gòu)游標(biāo)過程、函函數(shù)和觸觸發(fā)器包集合動態(tài)SQL批綁定對象特性性714.1.3PL/SQL執(zhí)行過程程與開發(fā)發(fā)工具PL/SQL塊SQL語句客戶端應(yīng)應(yīng)用程序序PL/SQL引擎數(shù)據(jù)庫服服務(wù)器過程化語語句執(zhí)行行器SQL執(zhí)行器塊中SQL語句PL/SQL執(zhí)行過程程8PL/SQL開發(fā)工具具SQL*PLUSProcedureBuilderOracleForm、OracleReportsPL/SQLDeveloper914.2PL/SQL基礎(chǔ)PL/SQL程序結(jié)構(gòu)構(gòu)詞法單元元數(shù)據(jù)類型型變量與常常量PL/SQL記錄編譯指示示PL/SQL中的SQL語句1014.2.1PL/SQL程序結(jié)構(gòu)構(gòu)PL/SQL塊的組

3、成成PL/SQL塊分類11（1）PL/SQL塊的組成成PL/SQL程序的基基本單元元是語句句塊，所所有的PL/SQL程序都是是由語句句塊構(gòu)成成的。一個完整整的PL/SQL語句塊由由3個部分組組成。12聲明部分分主要用于于聲明變變量、常常量、數(shù)數(shù)據(jù)類型型、游標(biāo)標(biāo)、異常常處理名名稱以及及本地（局部）子程序序定義等等。可執(zhí)行部部分執(zhí)行部分分是PL/SQL塊的功能能實現(xiàn)部部分。該該部分通通過變量量賦值、流程控控制、數(shù)數(shù)據(jù)查詢詢、數(shù)據(jù)據(jù)操縱、數(shù)據(jù)定定義、事事務(wù)控制制、游標(biāo)標(biāo)處理等等實現(xiàn)塊塊的功能能。異常處理理部分異常處理理部分用用于處理理該塊執(zhí)執(zhí)行過程程中產(chǎn)生生的異常常。13注意：執(zhí)行部分分是必須須的，

4、而而聲明部部分和異異常部分分是可選選的可以在一一個塊的的執(zhí)行部部分或異異常處理理部分嵌嵌套其他他的PL/SQL塊；所有的PL/SQL塊都是以以“END;”結(jié)束。14DECLAREv_ename VARCHAR2(10);BEGINSELECTenameINTOv_ename FROMempWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_ename);EXCEPTIONWHEN NO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(Thereisnot suchaemployee);END;15DECLAREv_salNUMBER(6,2);v

5、_deptnoNUMBER(2);BEGINBEGINSELECTdeptno INTOv_deptnoFROMempWHEREempno=7844;END;SELECTavg(sal)INTOv_salFROM empWHEREdeptno=v_deptno;DBMS_OUTPUT.PUT_LINE(v_sal);END;16注意若要在SQL*Plus環(huán)境中看看到DBMS_OUTPUT.PUT_LINE方法的輸輸出結(jié)果果，必須須將環(huán)境境變量SERVEROUTPUT設(shè)置為ON。SETSERVEROUTPUT ON17（2）PL/SQL塊分類匿名塊匿名塊是是指動態(tài)態(tài)生成，只能執(zhí)執(zhí)行一次次的塊，不

6、能由由其他應(yīng)應(yīng)用程序序調(diào)用。命名塊命名塊是是指一次次編譯可可多次執(zhí)執(zhí)行的PL/SQL程序，包包括函數(shù)數(shù)、存儲儲過程、包、觸觸發(fā)器等等。它們們編譯后后放在服服務(wù)器中中，由應(yīng)應(yīng)用程序序或系統(tǒng)統(tǒng)在特定定條件下下調(diào)用執(zhí)執(zhí)行。18命名塊示示例CREATEORREPLACEPROCEDUREshowavgsal(p_deptnoNUMBER)ASv_salNUMBER(6,2);BEGINSELECTavg(sal)INTOv_salFROM empWHEREdeptno=p_deptno;DBMS_OUTPUT.PUT_LINE(v_sal);ENDshowavgsal;1914.2.2詞法單元元字符集

7、標(biāo)識符分隔符常量值注釋20（1）字符集集PL/SQL的字符集集包括：大小寫字字母：AZ，az數(shù)字：09空白：制制表符、空格和和回車數(shù)字符號號：+ -*/=標(biāo)點符號號： !# $%&*（）_ | ？；：, .“注意PL/SQL字符集不不區(qū)分大大小寫。21（2）標(biāo)識符符標(biāo)識符用用于定義義PL/SQL變量、常常量、異異常、游游標(biāo)名稱稱、游標(biāo)標(biāo)變量、參數(shù)、子程序序名稱和和其他的的程序單單元名稱稱等。在PL/SQL程序中，標(biāo)識符符是以字字母開頭頭的，后后邊可以以跟字母母、數(shù)字字、美元元符號（$）、井號號（#）或下劃劃線（_），其最最大長度度為30個字符，并且所所有字符符都是有有效的。例如，X，v_emp

8、no，v_$等都是有有效的標(biāo)標(biāo)識符，而X+y，_temp則是非法法的標(biāo)識識符。注意如果標(biāo)識識符區(qū)分分大小寫寫、使用用預(yù)留關(guān)關(guān)鍵字或或包含空空格等特特殊符號號，則需需要用“”括起來，稱為引引證標(biāo)識識符。例例如標(biāo)識識符“mybook”和“exception”。22（3）分隔符符+-*/=:=!=()/*/%;:.“.|=*-分隔符是是指有特特定含義義的單個個符號或或組合符符號23（4）常量值值字符型文文字以單引號號引起來來的字符符串，在在字符串串中的字字符區(qū)分分大小寫寫。如果果字符串串中本身身包含單單引號，則用兩兩個連續(xù)續(xù)的單引引號進(jìn)行行轉(zhuǎn)義。數(shù)字型文文字分為整數(shù)數(shù)與實數(shù)數(shù)兩類。其中，整數(shù)沒沒有小

9、數(shù)數(shù)點，如如123；而實數(shù)數(shù)有小數(shù)數(shù)點，如如123.45?？梢杂糜每茖W(xué)計計數(shù)法表表示數(shù)字字型文字字，如123.45可以表示示為1.2345E2。布爾型文文字預(yù)定義的的布爾型型變量的的取值，包括TRUE，F(xiàn)ALSE，NULL三個值。日期型文文字表示日期期值，其其格式隨隨日期類類型格式式不同而而不同。24（5）注釋單行注釋釋-多行注釋釋以“/*”開始，以以“*/”結(jié)束。DECLAREv_departmentCHAR(10);-variabletohold thedepartment nameBEGIN/*querythedepartmentname whichdepartment numberis

10、10ouputthedepartmentname intov_department*/SELECTdnameINTOv_departmentFROMdeptWHEREdeptno=10;END;2514.2.3數(shù)據(jù)類型型數(shù)字類型型字符類型型日期/區(qū)間類型型行標(biāo)識類類型布爾類型型原始類型型LOB類型引用類型型記錄類型型集合類型型%TYPE與%ROWTYPE26PL/SQL中常用的的基本數(shù)數(shù)據(jù)類型型分類數(shù)據(jù)類型數(shù)字類型NUMBER、BINARY_NUMBER PLS_NUMBER字符類型VARCHAR2、CHAR、LONG、NCHAR、NVARCHAR日期/區(qū)間類型 DATE、TIMESTAMP、

11、INTERVAL行標(biāo)識類型ROWID、UROWID布爾類型BOOLEAN（TRUE、FALSE、NULL）原始類型RAW、LONG RAWLOB類型CLOB、BLOB、NCLOB、BFILE引用類型 REF CURSOR，REF object_type。 記錄類型RECORD集合類型TABLE、VARRAY27數(shù)字類型型NUMBER類型以十十進(jìn)制形形式存儲儲整數(shù)和和浮點數(shù)數(shù)，語法法為NUMBER（p，s）。其中中，p為精度，即所有有有效數(shù)數(shù)字位數(shù)數(shù)；s為刻度范范圍，即即小數(shù)位位數(shù)。p的取值范范圍為138。BINARY_INTEGER類型用于于表示從從-2147483647+2147483647

12、之間的整整數(shù)，以以二進(jìn)制制形式存存儲。當(dāng)當(dāng)發(fā)生溢溢出時，將自動動轉(zhuǎn)換成成NUMBER類型。PLS_INTEGER類型表示示范圍與與BINARY_INTEGER相同，但但發(fā)生溢溢出時會會產(chǎn)生錯錯誤。28字符類型型PL/SQL中的字符符類型與與Oracle數(shù)據(jù)庫中中的字符符類型類類似，但但是允許許字符串串的長度度有所不不同。VARCHAR2，CHAR主要用于于存儲來來自本地地數(shù)據(jù)庫庫字符集集的字符符，而NCHAR，NVARCHAR2用于存儲儲來自國國家字符符集的字字符串。類 型PL/SQL中最大字節(jié)數(shù)Oracle中最大字節(jié)數(shù)VARCHAR2327674000NVARCHAR2327674000CH

13、AR327672000NCHAR327672000LONG327602GB29日期/區(qū)間類型型DATE：與數(shù)據(jù)據(jù)庫中的的DATE類型相同同，存儲儲日期和和時間信信息，包包括世紀(jì)紀(jì)、年、月、日日、小時時、分和和秒，不不包括秒秒的小數(shù)數(shù)部分。TIMESTAMP：與DATE類型相似似，但包包括秒的的小數(shù)部部分，有有以下3種形式。TIMESTAMP(p)：其中p為秒字段段的小數(shù)數(shù)部分精精度。TIMESTAMP(p)WITHTIMEZONE：返回當(dāng)當(dāng)前時區(qū)區(qū)的時間間戳。TIMESTAMP(p)WITHLOACLTIMEZONE：返回數(shù)數(shù)據(jù)庫時時區(qū)的時時間戳。30INTERVAL：用于存存儲兩個個時間戳戳

14、之間的的時間間間隔，有有下面兩兩種形式式。INTERVALYEAR (p)TO MONTH：兩個時時間戳相相差的年年數(shù)和月月數(shù)。INTERVALDAY(dp)TOSECOND(sp)：兩個時時間戳相相差的天天數(shù)和秒秒數(shù)。31行標(biāo)識類類型ROWID表示行的的物理地地址UROWID既可以表表示行的的物理地地址，也也可以表表示行的的邏輯地地址。布爾類型型（BOOLEAN）只能在PL/SQL中使用，其取值值為邏輯輯值，包包括TRUE、FALSE、NULL。原始類型型與Oracle數(shù)據(jù)庫中中的原始始類型相相似，但但子節(jié)數(shù)數(shù)不同。類 型PL/SQL中最大字節(jié)數(shù)Oracle中最大字節(jié)數(shù)RAW32767200

15、0LONG RAW327672G32LOB類型包括BLOB，CLOB，NCLOB和BFILE四種類型型。其中中BLOB存放二進(jìn)進(jìn)制數(shù)據(jù)據(jù)，CLOB，NCLOB存放文本本數(shù)據(jù)，而BFILE存放指向向操作系系統(tǒng)文件件的指針針。LOB類型變量量可以存存儲4 GB的數(shù)據(jù)量量。引用類型型引用類型型類似于于其他高高級語言言中的指指針類型型。在PL/SQL中，引用用類型包包括游標(biāo)標(biāo)的引用用類型和和對象的的引用類類型，即即REFCURSOR和REFobject_type。33記錄類型型記錄類型型是復(fù)合合類型，類似于于C語言中的的結(jié)構(gòu)體體，是一一個包含含若干個個成員分分量的復(fù)復(fù)合類型型。在使用記記錄類型型時，需

16、需要先在在聲明部部分定義義記錄類類型和記記錄類型型的變量量，然后后在執(zhí)行行部分引引用該記記錄類型型變量或或其成員員分量。集合類型型集合類型型是復(fù)合合類型，包括索索引表類類型、嵌嵌套表類類型和可可變數(shù)組組類型。集合類型型與記錄錄類型的的區(qū)別在在于，記記錄類型型中的成成員分量量可以是是不同類類型的，類似于于結(jié)構(gòu)體體，而集集合類型型中所有有的成員員分量必必須具有有相同的的數(shù)據(jù)類類型，類類似于數(shù)數(shù)組。34%TYPE與%ROWTYPE如果要定定義一個個類型與與某個變變量的數(shù)數(shù)據(jù)類型型或數(shù)據(jù)據(jù)庫表中中某個列列的數(shù)據(jù)據(jù)類型一一致（不不知道該該變量或或列的數(shù)數(shù)據(jù)類型型）的變變量，可可以利用用%TYPE來實現(xiàn)。

17、如果要定定義一個個與數(shù)據(jù)據(jù)庫中某某個表結(jié)結(jié)構(gòu)一致致的記錄錄類型的的變量，可以使使用%ROWTYPE來實現(xiàn)。注意變量的類類型隨參參照的變變量類型型、數(shù)據(jù)據(jù)庫表列列類型、表結(jié)構(gòu)構(gòu)的變化化而變化化；如果數(shù)據(jù)據(jù)庫表列列中有NOTNULL約束，則則%TYPE與%ROWTYPE返回的數(shù)數(shù)據(jù)類型型沒有此此限制。35DECLAREv_salemp.sal%TYPE;v_empemp%ROWTYPE;BEGINSELECTsal INTOv_salFROMempWHEREempno=7844;SELECT*INTO v_emp FROMemp WHERE empno=7900;DBMS_OUTPUT.PUT_L

18、INE(v_sal);DBMS_OUTPUT.PUT_LINE(v_emp.ename|v_emp.sal);END;3614.2.4變量與常常量變量與常常量的定定義變量的作作用域37變量聲明明（1）變量與與常量的的定義變量定義義的一般般格式variable_name CONSTANTdatatypeNOTNULLDEFAULT|:=expression;說明變量或常常量名稱稱是一個個PL/SQL標(biāo)識符，應(yīng)符合合標(biāo)識符符命名規(guī)規(guī)范；每行只能能定義一一個變量量；如果加上上關(guān)鍵字字CONSTANT，則表示示所定義義的是一一個常量量，必須須為它賦賦初值；如果定義義變量時時使用了了NOTNULL關(guān)鍵字

19、，則必須須為變量量賦初值值；如果變量量沒有賦賦初值，則默認(rèn)認(rèn)為NULL；使用DEFAULT或“:=”運算符為為變量初初始化。38DECLAREv1NUMBER(4);v2NUMBER(4)NOT NULL:=10;v3CONSTANTNUMBER(4)DEFAULT100;BEGINIFv1ISNULL THENDBMS_OUTPUT.PUT_LINE(V1ISNULL!);ENDIF;DBMS_OUTPUT.PUT_LINE(v2|v3);END;39（2）變量的的作用域域變量的作作用域是是指變量量的有效效作用范范圍，從從變量聲聲明開始始，直到到塊結(jié)束束。如果PL/SQL塊相互嵌嵌套，則則在

20、內(nèi)部部塊中聲聲明的變變量是局局部的，只能在在內(nèi)部塊塊中引用用，而在在外部塊塊中聲明明的變量量是全局局的，既既可以在在外部塊塊中引用用，也可可以在內(nèi)內(nèi)部塊中中引用。如果內(nèi)部部塊與外外部塊中中定義了了同名變變量，則則在內(nèi)部部塊中引引用外部部塊的全全局變量量時需要要使用外外部塊名名進(jìn)行標(biāo)標(biāo)識。40DECLAREv_enameCHAR(16);v_outerNUMBER(5);BEGINv_outer :=10;DECLAREv_ename CHAR(20);v_inner DATE;BEGINv_inner:=sysdate;v_ename:=INNERV_ENAME;OUTER.v_ename:=

21、OUTERV_ENAME;END;DBMS_OUTPUT.PUT_LINE(v_ename);END;4114.2.5PL/SQL記錄用戶定義義記錄類類型及變變量利用%ROWTYPE獲取記錄錄類型定定義變量量記錄類型型變量的的應(yīng)用在SELECT語句中使使用記錄錄類型變變量在INSERT語句中使使用記錄錄類型變變量在UPDATE語句中使使用記錄錄類型變變量在DELETE語句中使使用記錄錄類型變變量42（1）用戶定定義記錄錄類型及及變量定義記錄錄類型的的語法為為TYPE record_type IS RECORD(field1datatype1 NOTNULLDEFAULT|:=expr1,fie

22、ld2datatype2 NOTNULL DEFAULT|:=expr2,fieldndatatypen NOTNULL DEFAULT|:=exprn);注意：相同記錄錄類型的的變量可可以相互互賦值；不同記錄錄類型的的變量，即使成成員完全全相同也也不能相相互賦值值；記錄類型型只能應(yīng)應(yīng)用于定定義該記記錄類型型的PL/SQL塊中，即即記錄類類型是局局部的。43利用記錄錄類型以以及記錄錄類型變變量，保保存員工工信息。DECLARETYPE t_emp IS RECORD(empnoNUMBER(4),enameCHAR(10),salNUMBER(6,2);v_empt_emp;BEGINSELE

23、CTempno,ename,sal INTOv_empFROM empWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_emp.ename|v_emp.sal);END;44（2）利用%ROWTYPE獲取記錄錄類型定定義變變量DECLAREv_emp1emp%ROWTYPE;v_emp2emp%ROWTYPE;CURSORc_empISSELECT empno,ename FROMempWHEREdeptno=10;v_emp10 c_emp%ROWTYPE;BEGINSELECT*INTO v_emp1FROM empWHEREempno=7844;OPEN c_

24、emp;LOOPFETCHc_empINTO v_emp10;EXIT WHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp10.empno|v_emp10.ename);ENDLOOP;CLOSEc_emp;END;45（3）記錄類類型變量量的應(yīng)用用在SELECT語句中使使用記錄錄類型變變量在SELECTINTO語句中使使用記錄錄類型變變量DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTO v_emp FROMempWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.

25、ename|v_emp.sal);END;注意記錄類型型變量中中分量的的個數(shù)、順序、類型應(yīng)應(yīng)該與查查詢列表表中列的的個數(shù)、順序、類型完完全匹配配。46在SELECT語句中使使用記錄錄類型變變量在SELECTINTO語句中使使用記錄錄類型變變量成員員DECLAREv_empemp%ROWTYPE;BEGINSELECTempno,ename,sal INTOv_emp.empno,v_emp.ename,v_emp.salFROMempWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.ename|v_emp.sal);END;47在IN

26、SERT語句中使使用記錄錄類型變變量在VALUES子句中使使用記錄錄類型變變量DECLAREv_deptdept%ROWTYPE;BEGINv_dept.deptno:=50;v_dept.loc:=BEIJING;V_dept.dname:=COMPUTER;INSERTINTODEPT VALUESv_dept;END;注意記錄類型型變量中中分量的的個數(shù)、順序、類型應(yīng)應(yīng)該與表表中列的的個數(shù)、順序、類型完完全匹配配。48在INSERT語句中使使用記錄錄類型變變量在VALUES子句中使使用記錄錄類型變變量成員員DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTO v

27、_emp FROMempWHEREempno=7844;INSERTINTOemp(empno,ename,mgr,sal)VALUES(1234,TOM,v_emp.mgr,v_emp. sal);END;49在UPDATE語句中使使用記錄錄類型變變量在SET子句中使使用記錄錄類型變變量（使使用ROW關(guān)鍵字）DECLAREv_deptdept%ROWTYPE;BEGINv_dept.deptno:=50;v_dept.loc:=TIANJIN;V_dept.dname:=COMPUTER;UPDATEdeptSETROW=v_deptWHEREdeptno=50;END;注意記錄類型型變量中

28、中分量的的個數(shù)、順序、類型應(yīng)應(yīng)該與表表中列的的個數(shù)、順序、類型完完全匹配配。50在UPDATE語句中使使用記錄錄類型變變量在SET子句中使使用記錄錄類型變變量成員員DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTO v_emp FROMempWHEREempno=7844;UPDATEemp SETsal=v_emp.sal,comm=v_mWHEREempno=7369;END;51在DELETE語句中使使用記錄錄類型變變量DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTO v_emp FROMempWHEREempno=7844;

29、DELETEFROMempWHEREdeptno=v_emp.deptno;END;5214.2.6編譯指示示編譯指示示是對編編譯程序序發(fā)出的的特殊指指令，也也稱為偽偽指令，不會改改變程序序含義。它只是是向編譯譯程序傳傳遞信息息，類似似于嵌入入在SQL中的注釋釋。在PL/SQL中使用PRAGMA關(guān)鍵字通通知編譯譯程序，PL/SQL語句的剩剩余部分分是一個個編譯指指示或命命令。編編譯指示示在編譯譯時被處處理，而而不會在在運行時時被執(zhí)行行，類似似于C語言中的的#define。53PL/SQL提供以下下4種編譯指指示EXCEPTION_INIT：告訴編編譯程序序?qū)⒁粋€個特定的的錯誤號號與程序序中所聲

30、聲明的異異常標(biāo)識識符關(guān)聯(lián)聯(lián)起來。RESTRICT_REFERENCES：告訴編編譯程序序打包程程序的純純度，即即對函數(shù)數(shù)中可以以使用的的SQL語句和包包變量進(jìn)進(jìn)行限制制。SERIALLY_REUSEABLE：告訴PL/SQL運行引擎擎時，在在數(shù)據(jù)引引用之間間不要保保持包級級數(shù)據(jù)。AUTONOMOUS_TRANSACTION：告訴編編譯程序序，該程程序塊為為自治事事務(wù)，即即該事務(wù)務(wù)的提交交和回滾滾是獨立立進(jìn)行的的。5414.2.7PL/SQL中SQL語句由于PL/SQL執(zhí)行采用用早期綁綁定，即即在編譯譯階段對對變量進(jìn)進(jìn)行綁定定，識別別程序中中標(biāo)識符符的位置置，檢查查用戶權(quán)權(quán)限、數(shù)數(shù)據(jù)庫對對象等信

31、信息，因因此在PL/SQL中只允許許出現(xiàn):SELECTDML(UPDATE、DELETE、INSERT)事務(wù)控制制語句（COMMIT、ROLLBACK、SAVEPOINT）注意DDL語句不可可以直接接使用55通常，利利用SQL語句對數(shù)數(shù)據(jù)庫進(jìn)進(jìn)行操作作時，各各種相關(guān)關(guān)量都在在代碼中中以常量量的形式式指定，而在PL/SQL中可以通通過變量量動態(tài)指指定各種種相關(guān)量量的值，從而實實現(xiàn)對數(shù)數(shù)據(jù)庫的的動態(tài)操操作。DECLAREv_empno NUMBER(4);BEGINv_empno:=&x;UPDATEemp SETsal=sal+100WHEREempno=v_empno;END;56SELECT

32、語句在PL/SQL程序中，使用SELECTINTO語句查詢詢一個記記錄的信信息。其語法為為：SELECTselect_list_itemINTOvariable_list|record_variableFROM tableWHEREcondition;57根據(jù)員工工名或員員工號查查詢員工工信息，程序為為：DECLAREv_empemp%ROWTYPE;v_ename emp.ename%type;v_salemp.sal%type;BEGINSELECT*INTO v_emp FROMempWHEREename=SMITH;DBMS_OUTPUT.PUT_LINE(v_emp.empno|v_

33、emp.sal);SELECTename,sal INTOv_ename,v_salFROM empWHEREempno=7900;DBMS_OUTPUT.PUT_LINE(v_ename| |v_sal);END;58注意：SELECTINTO語句只能能查詢一一個記錄錄的信息息，如果果沒有查查詢到任任何數(shù)據(jù)據(jù)，會產(chǎn)產(chǎn)生NO_DATA_FOUND異常；如如果查詢詢到多個個記錄，則會產(chǎn)產(chǎn)生TOO_MANY_ROWS異常。INTO句子后的的變量用用于接收收查詢的的結(jié)果，變量的的個數(shù)、順序應(yīng)應(yīng)該與查查詢的目目標(biāo)數(shù)據(jù)據(jù)相匹配配，也可可以是記記錄類型型的變量量。59用SELECTINTO語句查詢詢10號

34、部門所所有員工工信息。DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTO v_emp FROMemp WHERE deptno=10;END; /*ERROR位于第1行:ORA-01422:實際返回回的行數(shù)數(shù)超出請請求的行行數(shù)ORA-06512:在line 460DML語句PL/SQL中DML語句對標(biāo)標(biāo)準(zhǔn)SQL語句中的的DML語句進(jìn)行行了擴展展，允許許使用變變量。DECLAREv_empno emp.empno%TYPE :=7500;BEGININSERTINTOemp(empno,ename,sal,deptno)VALUES(v_empno,JOAN,230

35、0,20);UPDATEemp SETsal=sal+100WHEREempno=v_empno;DELETEFROMempWHEREempno=v_empno;END;61WHERE標(biāo)識符的的區(qū)分系統(tǒng)首先先查看WHERE子句中的的標(biāo)識符符是否與與表中的的列名相相同，如如果相同同，則該該標(biāo)識符符被解釋釋為列名名；如果果沒有同同名列，系統(tǒng)檢檢查該標(biāo)標(biāo)識符是是不是PL/SQL語句塊的的變量。字符串比比較填充比較較：通過過在短字字符串后后添加空空格，使使兩個字字符串達(dá)達(dá)到相同同長度，然后根根據(jù)每個個字符的的ASCII碼進(jìn)行比比較。非填充比比較：根根據(jù)每個個字符的的ASCII碼進(jìn)行比比較，最最先結(jié)束束

36、的字符符串為小小。62那么何時時采用填填充比較較，何時時采用非非填充比比較呢？PL/SQL中規(guī)定，對定長長的字符符串（CHAR類型的字字符串和和字符串串常量）采用填填充比較較；如果果比較的的字符串串中有一一個是變變長字符符串（VARCHAR2類型的字字符串），則采采用非填填充比較較。63例如，已已知emp表中ename列類型為為VARCHAR2（10），執(zhí)行行下面的的代碼。DECLAREv_ename CHAR(10):=TURNER;-v_enameVARCHAR2(20);-v_enameemp.ename%TYPE:=TURNER;v_salemp.sal%TYPE;BEGINSELEC

37、Tsal INTOv_salFROMempWHEREename=v_ename;dbms_output.put_line(v_sal);END;/DECLARE*第1行出現(xiàn)錯錯誤:ORA-01403:未找到數(shù)數(shù)據(jù)ORA-06512:在line 664產(chǎn)生錯誤誤的原因因是VARCHAR2（10）類型與與CHAR（10）類型比比較時采采用非填填充比較較，因此此無法查查詢到員員工名為為“TURNER”的員工。可以將將v_ename變量類型型修改為為VARCHAR2（10）類型，也可以以直接采采用emp.ename%TYPE方式定義義。因此，為為了保證證程序的的正確執(zhí)執(zhí)行，一一定要使使PL/SQL語句

38、塊中中的變量量與要比比較的數(shù)數(shù)據(jù)庫列列擁有相相同的數(shù)數(shù)據(jù)類型型，可以以使用%TYPE或%ROWTYPE來定義變變量。65RETURNING如果要查查詢當(dāng)前前DML語句操作作的記錄錄的信息息，可以以在DML語句末尾尾使用RETURNING語句返回回該記錄錄的信息息。RETURNING語句的基基本語法法：RETURNINGselect_list_itemINTO variable_list|record_variable;66DECLAREv_salemp.sal%TYPE;BEGINUPDATEemp SETsal=sal+100WHEREempno=7844RETURNINGsal INTOv

39、_sal;DBMS_OUTPUT.PUT_LINE(v_sal);END;6714.3控制結(jié)構(gòu)構(gòu)選擇結(jié)構(gòu)構(gòu)循環(huán)結(jié)構(gòu)構(gòu)跳轉(zhuǎn)結(jié)構(gòu)構(gòu)6814.3.1選擇結(jié)構(gòu)構(gòu)IF語句CASE語句69（1）IF語句語法IFcondition1 THENstatements1;ELSIFcondition2THEN statements2;ELSEelse_statements;ENDIF;注意條件是一一個布爾爾型變量量或表達(dá)達(dá)式，取取值只能能是TRUE，F(xiàn)ALSE，NULL。70例如，輸輸入一個個員工號號，修改改該員工工的工資資，如果果該員工工為10號部門，工資增增加100；若為20號部門，工資增增加160；若為30

40、號部門，工資增增加200；否則增增加300。71DECLAREv_deptnoemp.deptno%type;v_incrementNUMBER(4);v_empnoemp.empno%type;BEGINv_empno:=&x;SELECTdeptno INTOv_deptnoFROMempWHEREempno=v_empno;IFv_deptno=10THEN v_increment:=100;ELSIFv_deptno=20THEN v_increment:=160;ELSIFv_deptno=30THEN v_increment:=200;ELSEv_increment:=300;EN

41、DIF;UPDATEemp SETsal=sal+v_incrementWHEREempno=v_empno;END;72由于PL/SQL中的邏輯輯運算結(jié)結(jié)果有TRUE，F(xiàn)ALSE和NULL三種，因因此在進(jìn)進(jìn)行選擇擇條件判判斷時，要考慮慮條件為為NULL的情況。例如，下面兩兩個程序序，如果果不考慮慮條件為為NULL的情況，則運行行結(jié)果是是一致的的，但是是若考慮慮條件為為NULL的情況，則結(jié)果果就不同同了。7374為了避免免條件為為NULL時出現(xiàn)歧歧義，應(yīng)應(yīng)該在程程序中進(jìn)進(jìn)行條件件是否為為NULL的檢查。75（2）CASE語句基本語法法CASEWHEN condition1THENstateme

42、nts1;WHEN condition2THENstatements2;WHEN conditionnTHENstatementsn;ELSEelse_statements;ENDCASE;注意 在CASE語句中，當(dāng)?shù)谝灰粋€WHEN條件為真真時，執(zhí)執(zhí)行其后后的操作作，操作作完后結(jié)結(jié)束CASE語句。其其他的WHEN條件不再再判斷，其后的的操作也也不執(zhí)行行。76根據(jù)輸入入的員工工號，修修改該員員工工資資。如果果該員工工工資低低于1000，則工資資增加200；如果工工資在10002000之間，則則增加150；如果工工資在20003000之間，則則增加100；否則增增加50。77DECLAREv_sa

43、lemp.sal%type;v_incrementNUMBER(4);v_empnoemp.empno%type;BEGINv_empno:=&x;SELECTsal INTOv_salFROMempWHEREempno=v_empno;CASEWHEN v_sal1000THENv_increment:=200;WHEN v_sal2000THENv_increment:=150;WHEN v_sal50;ENDLOOP;END;83（2）WHILE循環(huán)基本語法法WHILEconditionLOOPsequence_of_statement;ENDLOOP;84利用WHILE循環(huán)向temp_

44、table表中插入入50條記錄。DECLAREv_counterBINARY_INTEGER:=1;BEGINWHILEv_counter=50LOOPINSERTINTOtemp_table VALUES(v_counter,Loopindex);v_counter:=v_counter +1;ENDLOOP;END;85（3）FOR循環(huán)基本語法法FORloop_counter IN REVERSElow_bound.high_boundLOOPsequence_of_statement;ENDLOOP;注意：循環(huán)變量量不需要要顯式定定義，系系統(tǒng)隱含含地將它它聲明為為BINARY_INTEG

45、ER變量；系統(tǒng)默認(rèn)認(rèn)時，循循環(huán)變量量從下界界往上界界遞增計計數(shù)，如如果使用用REVERSE關(guān)鍵字，則表示示循環(huán)變變量從上上界向下下界遞減減計數(shù)；循環(huán)變量量只能在在循環(huán)體體中使用用，不能能在循環(huán)環(huán)體外使使用。86利用FOR循環(huán)向temp_table表中插入入50條記錄。BEGINFORv_counter IN 1.50 LOOPINSERTINTOtemp_table VALUES(v_counter,LoopIndex);ENDLOOP;END;8714.3.3跳轉(zhuǎn)結(jié)構(gòu)構(gòu)語法格式式：標(biāo)號 GOTO標(biāo)號；說明：塊內(nèi)可以以跳轉(zhuǎn)，內(nèi)層塊塊可以跳跳到外層層塊，但但外層塊塊不能跳跳到內(nèi)層層。IF語句不能

46、能跳入。不能從從循環(huán)體體外跳入入循環(huán)體體內(nèi)。不不能從子子程序外外部跳到到子程序序中。由于goto語句的缺缺點，建建議盡量量少用甚甚至不用用goto語句。88DECLAREv_counterBINARY_INTEGER:=1;BEGININSERTINTOtemp_table VALUES(v_counter,Loopindex);v_counter:=v_Counter +1;IFv_counter(SELECTAVG(sal)FROMempWHEREdeptno=10);ELSIFv_table =dept THENOPEN v_cursor FORSELECTdeptno,count(*)

47、 numFROM empGROUPBYdeptno;ELSERAISE_APPLICATION_ERROR(-20000,Input mustbeempordept);ENDIF;131LOOPIFv_table =empTHENFETCHv_cursorINTO v_emp;EXIT WHENv_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.ename|v_emp.sal|v_emp.deptno);ELSEFETCHv_cursorINTO v_deptno,v_num;EXIT WHENv_cursor%NOTFOUND;D

48、BMS_OUTPUT.PUT_LINE(v_deptno|v_num);ENDIF;ENDLOOP;CLOSEv_cursor;END;13214.5異常處理理異常概述述異常處理理過程異常的傳傳播13314.5.1異常概述述Oracle錯誤處理理機制異常的類類型134（1）Oracle錯誤處理理機制Oracle中對運行行時錯誤誤的處理理采用了了異常處處理機制制。一個錯誤誤對應(yīng)一一個異常常，當(dāng)錯錯誤產(chǎn)生生時拋出出相應(yīng)的的異常，并被異異常處理理器捕獲獲，程序序控制權(quán)權(quán)傳遞給給異常處處理器，由異常常處理器器來處理理運行時時錯誤。135（2）異常的的類型預(yù)定義的的Oracle異常（Oracle錯誤）非

49、預(yù)定義義的Oracle異常（Oracle錯誤）用戶定義義的異常常（用戶戶定義錯錯誤）136預(yù)定義的的Oracle異常當(dāng)Oracle錯誤產(chǎn)生生時，與與錯誤對對應(yīng)的預(yù)預(yù)定義異異常被自自動拋出出，通過過捕獲該該異常可可以對錯錯誤進(jìn)行行處理。常用預(yù)定定義異常常包括：137異常情況名錯誤代碼描述CURSOR_ALREADY_OPEN ORA-06511 嘗試打開已經(jīng)打開的游標(biāo) INVALID_CURSORORA-01001不合法的游標(biāo)操作（如要打開已經(jīng)關(guān)閉的游標(biāo)） NO_DATA_FOUNDORA-01403沒有發(fā)現(xiàn)數(shù)據(jù) TOO_MANY_ROWSORA-01422一個SELECT INTO語句匹配多個

50、數(shù)據(jù)行INVALID_NUMBERORA-01722轉(zhuǎn)換成數(shù)字失敗 （X） VALUE_ERRORORA-06502截斷、算法或轉(zhuǎn)換錯誤，通常出現(xiàn)在賦值錯誤 ZERO_DIVIDEORA-01476除數(shù)為0 ROWTYPE_MISMATCHORA-06504主機游標(biāo)變量與PL/SQL游標(biāo)變量類型不匹配138異常情況名錯誤代碼描述DUP_VAL_ON_INDEXORA-00001違反唯一性約束或主鍵約束SYS_INVALID_ROWIDORA-01410轉(zhuǎn)換成ROWID失敗TIMEOUT_ON_RESOURCEORA-00051在等待資源中出現(xiàn)超時LOGIN_DENIEDORA-01017無效用

51、戶名/密碼CASE_NOT_FOUNDORA-06592沒有匹配的WHEN子句NOT_LOGGED_ONORA-01012沒有與數(shù)據(jù)庫建立連接STORAGE_ERRORORA-06500PL/SQL內(nèi)部錯誤PROGRAM_ERRORORA-06501PL/SQL內(nèi)部錯誤139異常情況名錯誤代碼描述ACCESS_INTO_NULLORA-06530給空對象屬性賦值COLLECTION_IS_NULLORA-06531對某NULL PL/SQL表或可變數(shù)組試圖應(yīng)用集合方法，而不是EXISTS SELF_IS_NULLORA-30625調(diào)用空對象實例的方法SUBSCRIPT_BEYOND_COUNT

52、ORA-06533對嵌套表或數(shù)組索引引用時超出集合中元素的數(shù)量SUBSCRIPT_OUTSIDE_LIMITORA-06532對嵌套表或可變數(shù)組索引的引用超出聲明的范圍140非預(yù)定義義異常有一些Oracle錯誤沒有有預(yù)定義義異常與與其關(guān)聯(lián)聯(lián)，需要要在語句句塊的聲聲明部分分聲明一一個異常常名稱，然后通通過編譯譯指示PRAGMAEXCEPTION_INIT將該異常常名稱與與一個Oracle錯誤相關(guān)關(guān)聯(lián)。此此后，當(dāng)當(dāng)執(zhí)行過過程出現(xiàn)現(xiàn)該錯誤誤時將自自動拋出出該異常常。141聲明一個個異常名名稱e_integrityEXCEPTION;將異常與與一個Oracle錯誤號相相綁定PRAGMAEXCEPTIO

53、N-INIT(e_integrity.-2291)示例DECLAREe_deptno_fkEXCEPTION;PRAGMAEXCEPTION_INIT(e_deptno_fk,-2292);BEGINEXCEPTIONEND;142用戶自定定義的異異常用戶定義義錯誤是是指，有有些操作作并不會會產(chǎn)生Oracle錯誤，但但是從業(yè)業(yè)務(wù)規(guī)則則角度考考慮，認(rèn)認(rèn)為是一一種錯誤誤。用戶自定定義異常常必須在在聲明部部分進(jìn)行行聲明。當(dāng)異常發(fā)發(fā)生時，系統(tǒng)不不能自動動觸發(fā)，需要用用戶使用用RAISE語句。在異常處處理部分分捕捉并并處理異異常。14314.5.2異常處理理過程異常的定定義異常的拋拋出異常的捕捕獲與處處

54、理OTHERS異常處理理器144異常處理理分3個步驟進(jìn)進(jìn)行：在聲明部部分為錯錯誤定義義異常，包括非非預(yù)定義義異常和和用戶定定義異常常。在執(zhí)行過過程中當(dāng)當(dāng)錯誤產(chǎn)產(chǎn)生時拋拋出與錯錯誤對應(yīng)應(yīng)的異常常。在異常處處理部分分通過異異常處理理器捕獲獲異常，并進(jìn)行行異常處處理。145（1）異常的的定義Oracle中的3種異常，其中預(yù)預(yù)定義異異常由系系統(tǒng)定義義，而其其他兩種種異常則則需要用用戶定義義。定義異常常方法e_exceptionEXCEPTION;如果是非非預(yù)定義義的異常常，需要要將異常常與一個個Oracle錯誤相關(guān)關(guān)聯(lián)，其其語法為為：PRAGMAEXCEPTION_INIT(e_exception,

55、-#);注意Oracle內(nèi)部錯誤誤號用一一個負(fù)的的5位數(shù)表示示，如-02292。其中-20999-20000為用戶定定義錯誤誤的保留留號。146（2）異常的的拋出由于系統(tǒng)統(tǒng)可以自自動識別別Oracle內(nèi)部錯誤誤，因此此當(dāng)錯誤誤產(chǎn)生時時系統(tǒng)會會自動拋拋出與之之對應(yīng)的的預(yù)定義義異?；蚧蚍穷A(yù)定定義異常常。但是是，系統(tǒng)統(tǒng)無法識識別用戶戶定義錯錯誤，因因此當(dāng)用用戶定義義錯誤產(chǎn)產(chǎn)生時，需要用用戶手動動拋出與與之對應(yīng)應(yīng)的異常常。用戶定義義異常的的拋出語語法為RAISEuser_define_exception；147（3）異常的的捕獲與與處理異常處理理器的基基本形式式為EXCEPTIONWHEN excep

56、tion1ORexcetpion2THENsequence_of_statements1;WHEN exception3ORexception4THENsequence_of_statements2;WHEN OTHERSTHENsequence_of_statementsn;END;注意：一個異常常處理器器可以捕捕獲多個個異常，只需在在WHEN子句中用用OR連接即可可；一個異常常只能被被一個異異常處理理器捕獲獲，并進(jìn)進(jìn)行處理理。148查詢名為為SMITH的員工工工資，如如果該員員工不存存在，則則輸出“Thereisnotsuchanemployee!”；如果存存在多個個同名的的員工，則輸出出

57、其員工工號和工工資。DECLAREv_salemp.sal%type;BEGINSELECTsal INTOv_salFROMempWHEREename=SMITH;DBMS_OUTPUT.PUT_LINE(v_sal);EXCEPTIONWHEN NO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(Thereisnot suchanemplyee!);WHEN TOO_MANY_ROWSTHENFORv_empIN(SELECT* FROMemp WHERE ename=SMITH)LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.

58、sal);ENDLOOP;END;149刪除dept表中部門門號為10的部門信信息，如如果不能能刪除則則輸出“Therearesubrecordsinemptable!”。DECLAREe_deptno_fkEXCEPTION;PRAGMAEXCEPTION_INIT(e_deptno_fk,-2292);BEGINDELETEFROMdept WHERE deptno=10;EXCEPTIONWHEN e_deptno_fk THENDBMS_OUTPUT.PUT_LINE( There aresubrecords in emptable!);END;150修改7844員工的工工資，保保證修

59、改改后工資資不超過過6000。DECLAREe_highlimitEXCEPTION;v_salemp.sal%TYPE;BEGINUPDATEemp SETsal=sal+100WHEREempno=7844 RETURNINGsalINTOv_sal;IFv_sal6000 THENRAISEe_highlimit;ENDIF;EXCEPTIONWHEN e_highlimit THENDBMS_OUTPUT.PUT_LINE(The salaryistoolarge!);ROLLBACK;END;151（4）OTHERS異常處理理器OTHERS異常處理理器是一一個特殊殊的異常常處理器器，

60、可以以捕獲所所有的異異常。通常，OTHERS異常處理理器總是是作為異異常處理理部分的的最后一一個異常常處理器器，負(fù)責(zé)責(zé)處理那那些沒有有被其他他異常處處理器捕捕獲的異異常。152DECLAREv_salemp.sal%TYPE;e_highlimitEXCEPTION;BEGINSELECTsal INTOv_salFROMempWHEREename=JOAN;UPDATEemp SETsal=sal+100WHEREempno=7900;IFv_sal6000 THENRAISEe_highlimit;ENDIF;EXCEPTIONWHEN e_highlimit THENDBMS_OUTPU