Oracle第14章PLSQL語言基礎(chǔ) 精品資料

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

2、對象特性714.1.3 PL/SQL執(zhí)行過程與開發(fā)工具PL/SQL塊SQL語句客戶端應用程序PL/SQL引擎數(shù)據(jù)庫服務器過程化語句執(zhí)行器SQL執(zhí)行器塊中SQL語句PL/SQL執(zhí)行過程 8PL/SQL開發(fā)工具SQL *PLUSProcedure BuilderOracle Form、Oracle ReportsPL/SQL Developer914.2 PL/SQL基礎(chǔ)PL/SQL程序結(jié)構(gòu) 詞法單元 數(shù)據(jù)類型變量與常量PL/SQL記錄 編譯指示PL/SQL中的SQL語句1014.2.1 PL/SQL程序結(jié)構(gòu)PL/SQL塊的組成PL/SQL塊分類 11（1）PL/SQL塊的組成PL/SQL程序的基

3、本單元是語句塊，所有的PL/SQL程序都是由語句塊構(gòu)成的 。一個完整的PL/SQL語句塊由3個部分組成。 12聲明部分主要用于聲明變量、常量、數(shù)據(jù)類型、游標、異常處理名稱以及本地（局部）子程序定義等。 可執(zhí)行部分執(zhí)行部分是PL/SQL塊的功能實現(xiàn)部分。該部分通過變量賦值、流程控制、數(shù)據(jù)查詢、數(shù)據(jù)操縱、數(shù)據(jù)定義、事務控制、游標處理等實現(xiàn)塊的功能。異常處理部分異常處理部分用于處理該塊執(zhí)行過程中產(chǎn)生的異常。 13注意：執(zhí)行部分是必須的，而聲明部分和異常部分是可選的可以在一個塊的執(zhí)行部分或異常處理部分嵌套其他的PL/SQL塊；所有的PL/SQL塊都是以“END;”結(jié)束。14DECLARE v_enam

4、e VARCHAR2(10);BEGIN SELECT ename INTO v_ename FROM emp WHERE empno=7844; DBMS_OUTPUT.PUT_LINE(v_ename);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(There is not such a employee);END;15DECLARE v_sal NUMBER(6,2); v_deptno NUMBER(2);BEGIN BEGIN SELECT deptno INTO v_deptno FROM emp WHERE empn

5、o=7844; END; SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=v_deptno; DBMS_OUTPUT.PUT_LINE(v_sal);END;16注意若要在SQL*Plus環(huán)境中看到DBMS_OUTPUT.PUT_LINE方法的輸出結(jié)果，必須將環(huán)境變量SERVEROUTPUT設(shè)置為ON。SET SERVEROUTPUT ON17（2）PL/SQL塊分類匿名塊匿名塊是指動態(tài)生成，只能執(zhí)行一次的塊，不能由其他應用程序調(diào)用。命名塊命名塊是指一次編譯可多次執(zhí)行的PL/SQL程序，包括函數(shù)、存儲過程、包、觸發(fā)器等。它們編譯后放在服務器中，

6、由應用程序或系統(tǒng)在特定條件下調(diào)用執(zhí)行。 18命名塊示例CREATE OR REPLACE PROCEDURE showavgsal (p_deptno NUMBER)AS v_sal NUMBER(6,2);BEGIN SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno; DBMS_OUTPUT.PUT_LINE(v_sal);END showavgsal;1914.2.2 詞法單元字符集標識符分隔符常量值注釋20（1）字符集PL/SQL的字符集包括：大小寫字母：AZ，az數(shù)字：09空白：制表符、空格和回車數(shù)字符號：+ - * /

7、 =標點符號： ! # $ % &* （）_ | ？ ； ：, . “ 注意PL/SQL字符集不區(qū)分大小寫。21（2）標識符標識符用于定義PL/SQL變量、常量、異常、游標名稱、游標變量、參數(shù)、子程序名稱和其他的程序單元名稱等。在PL/SQL程序中，標識符是以字母開頭的，后邊可以跟字母、數(shù)字、美元符號（$）、井號（#）或下劃線（_），其最大長度為30個字符，并且所有字符都是有效的。例如，X，v_empno，v_$等都是有效的標識符，而X+y，_temp則是非法的標識符。注意如果標識符區(qū)分大小寫、使用預留關(guān)鍵字或包含空格等特殊符號，則需要用“”括起來，稱為引證標識符。例如標識符“my book”

8、和“exception”。22（3）分隔符+-*/=:=!=()/*/%;:.“.|=*-分隔符是指有特定含義的單個符號或組合符號 23（4）常量值字符型文字以單引號引起來的字符串，在字符串中的字符區(qū)分大小寫。如果字符串中本身包含單引號，則用兩個連續(xù)的單引號進行轉(zhuǎn)義。數(shù)字型文字分為整數(shù)與實數(shù)兩類。其中，整數(shù)沒有小數(shù)點，如123；而實數(shù)有小數(shù)點，如123.45?？梢杂每茖W計數(shù)法表示數(shù)字型文字，如123.45可以表示為1.2345E2。布爾型文字預定義的布爾型變量的取值，包括TRUE，F(xiàn)ALSE，NULL三個值。日期型文字表示日期值，其格式隨日期類型格式不同而不同。24（5）注釋單行注釋-多行注釋

9、以 “/*”開始，以“*/”結(jié)束。DECLARE v_department CHAR(10); - variable to hold the department name BEGIN /* query the department name which department number is 10 ouput the department name into v_department*/ SELECT dname INTO v_department FROM dept WHERE deptno=10; END; 2514.2.3 數(shù)據(jù)類型數(shù)字類型字符類型日期/區(qū)間類型行標識類型布爾類型原始

10、類型LOB類型引用類型 記錄類型集合類型%TYPE與%ROWTYPE26PL/SQL中常用的基本數(shù)據(jù)類型分類數(shù)據(jù)類型數(shù)字類型NUMBER、BINARY_NUMBER PLS_NUMBER字符類型VARCHAR2、CHAR、LONG、NCHAR、NVARCHAR日期/區(qū)間類型 DATE、TIMESTAMP、INTERVAL行標識類型ROWID、UROWID布爾類型BOOLEAN（TRUE、FALSE、NULL）原始類型RAW、LONG RAWLOB類型CLOB、BLOB、NCLOB、BFILE引用類型 REF CURSOR，REF object_type。 記錄類型RECORD集合類型TABLE

11、、VARRAY27數(shù)字類型 NUMBER類型以十進制形式存儲整數(shù)和浮點數(shù)，語法為NUMBER（p，s）。其中，p為精度，即所有有效數(shù)字位數(shù)；s為刻度范圍，即小數(shù)位數(shù)。p的取值范圍為138。BINARY_INTEGER類型用于表示從-2147483647+2147483647之間的整數(shù)，以二進制形式存儲。當發(fā)生溢出時，將自動轉(zhuǎn)換成NUMBER類型。PLS_INTEGER類型表示范圍與BINARY_INTEGER相同，但發(fā)生溢出時會產(chǎn)生錯誤。28字符類型 PL/SQL中的字符類型與Oracle數(shù)據(jù)庫中的字符類型類似，但是允許字符串的長度有所不同。VARCHAR2，CHAR主要用于存儲來自本地數(shù)據(jù)庫

12、字符集的字符，而NCHAR，NVARCHAR2 用于存儲來自國家字符集的字符串。 類 型PL/SQL中最大字節(jié)數(shù)Oracle中最大字節(jié)數(shù)VARCHAR2327674000NVARCHAR2327674000CHAR327672000NCHAR327672000LONG327602GB29日期/區(qū)間類型 DATE：與數(shù)據(jù)庫中的DATE類型相同，存儲日期和時間信息，包括世紀、年、月、日、小時、分和秒，不包括秒的小數(shù)部分。TIMESTAMP：與DATE類型相似，但包括秒的小數(shù)部分，有以下3種形式。TIMESTAMP(p)：其中p為秒字段的小數(shù)部分精度。TIMESTAMP(p)WITH TIME ZO

13、NE：返回當前時區(qū)的時間戳。TIMESTAMP(p)WITH LOACL TIME ZONE：返回數(shù)據(jù)庫時區(qū)的時間戳。30INTERVAL：用于存儲兩個時間戳之間的時間間隔，有下面兩種形式。INTERVAL YEAR (p)TO MONTH：兩個時間戳相差的年數(shù)和月數(shù)。INTERVAL DAY(dp) TO SECOND(sp)：兩個時間戳相差的天數(shù)和秒數(shù)。31行標識類型ROWID表示行的物理地址UROWID既可以表示行的物理地址，也可以表示行的邏輯地址。布爾類型（BOOLEAN）只能在PL/SQL中使用，其取值為邏輯值，包括TRUE、FALSE、NULL。原始類型與Oracle數(shù)據(jù)庫中的原始

14、類型相似，但子節(jié)數(shù)不同。 類 型PL/SQL中最大字節(jié)數(shù)Oracle中最大字節(jié)數(shù)RAW327672000LONG RAW327672G32LOB類型包括BLOB，CLOB，NCLOB和BFILE四種類型。其中BLOB存放二進制數(shù)據(jù)，CLOB，NCLOB存放文本數(shù)據(jù)，而BFILE存放指向操作系統(tǒng)文件的指針。LOB類型變量可以存儲4 GB的數(shù)據(jù)量。引用類型引用類型類似于其他高級語言中的指針類型。在PL/SQL中，引用類型包括游標的引用類型和對象的引用類型，即REF CURSOR和REF object_type。33記錄類型記錄類型是復合類型，類似于C語言中的結(jié)構(gòu)體，是一個包含若干個成員分量的復合類

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

16、隨參照的變量類型、數(shù)據(jù)庫表列類型、表結(jié)構(gòu)的變化而變化； 如果數(shù)據(jù)庫表列中有NOT NULL約束，則%TYPE與%ROWTYPE返回的數(shù)據(jù)類型沒有此限制。 35DECLARE v_sal emp.sal%TYPE; v_emp emp%ROWTYPE;BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=7844; SELECT * INTO v_emp FROM emp WHERE empno=7900; DBMS_OUTPUT.PUT_LINE(v_sal); DBMS_OUTPUT.PUT_LINE(v_emp.ename|v_emp.sal);

17、END; 3614.2.4 變量與常量變量與常量的定義變量的作用域37變量聲明（1）變量與常量的定義變量定義的一般格式variable_name CONSTANT datatype NOT NULL DEFAULT|:=expression;說明變量或常量名稱是一個PL/SQL標識符，應符合標識符命名規(guī)范；每行只能定義一個變量；如果加上關(guān)鍵字CONSTANT，則表示所定義的是一個常量，必須為它賦初值；如果定義變量時使用了NOT NULL關(guān)鍵字，則必須為變量賦初值；如果變量沒有賦初值，則默認為NULL；使用DEFAULT或“:=”運算符為變量初始化。38DECLARE v1 NUMBER(4);

18、 v2 NUMBER(4) NOT NULL :=10; v3 CONSTANT NUMBER(4) DEFAULT 100;BEGIN IF v1 IS NULL THEN DBMS_OUTPUT.PUT_LINE(V1 IS NULL! ); END IF; DBMS_OUTPUT.PUT_LINE(v2| |v3);END;39（2）變量的作用域變量的作用域是指變量的有效作用范圍，從變量聲明開始，直到塊結(jié)束。如果PL/SQL塊相互嵌套，則在內(nèi)部塊中聲明的變量是局部的，只能在內(nèi)部塊中引用，而在外部塊中聲明的變量是全局的，既可以在外部塊中引用，也可以在內(nèi)部塊中引用。如果內(nèi)部塊與外部塊中定義了

19、同名變量，則在內(nèi)部塊中引用外部塊的全局變量時需要使用外部塊名進行標識。 40DECLARE v_ename CHAR(16); v_outer NUMBER(5);BEGIN v_outer :=10; DECLARE v_ename CHAR(20); v_inner DATE; BEGIN v_inner:=sysdate; v_ename:=INNER V_ENAME; OUTER.v_ename:=OUTER V_ENAME; END; DBMS_OUTPUT.PUT_LINE(v_ename);END; 4114.2.5 PL/SQL記錄 用戶定義記錄類型及變量 利用%ROWTYPE

20、獲取記錄類型定義變量 記錄類型變量的應用 在SELECT語句中使用記錄類型變量 在INSERT語句中使用記錄類型變量 在UPDATE語句中使用記錄類型變量 在DELETE語句中使用記錄類型變量 42（1）用戶定義記錄類型及變量 定義記錄類型的語法為TYPE record_type IS RECORD(field1 datatype1 NOT NULLDEFAULT|:=expr1,field2 datatype2 NOT NULL DEFAULT|:=expr2,fieldn datatypen NOT NULL DEFAULT|:=exprn);注意：相同記錄類型的變量可以相互賦值；不同記錄

21、類型的變量，即使成員完全相同也不能相互賦值；記錄類型只能應用于定義該記錄類型的PL/SQL塊中，即記錄類型是局部的。43利用記錄類型以及記錄類型變量，保存員工信息。 DECLARE TYPE t_emp IS RECORD( empno NUMBER(4), ename CHAR(10), sal NUMBER(6,2); v_emp t_emp;BEGIN SELECT empno,ename,sal INTO v_emp FROM emp WHERE empno=7844; DBMS_OUTPUT.PUT_LINE(v_emp.ename| |v_emp.sal);END;44（2）利用%

22、ROWTYPE獲取記錄類型定義變量DECLARE v_emp1 emp%ROWTYPE; v_emp2 emp%ROWTYPE; CURSOR c_emp IS SELECT empno,ename FROM emp WHERE deptno=10; v_emp10 c_emp%ROWTYPE; BEGIN SELECT * INTO v_emp1 FROM emp WHERE empno=7844; OPEN c_emp; LOOP FETCH c_emp INTO v_emp10; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp10

23、.empno| | v_emp10.ename); END LOOP; CLOSE c_emp;END; 45（3）記錄類型變量的應用在SELECT語句中使用記錄類型變量 在SELECT INTO 語句中使用記錄類型變量DECLARE v_emp emp%ROWTYPE; BEGIN SELECT * INTO v_emp FROM emp WHERE empno=7844; DBMS_OUTPUT.PUT_LINE(v_emp.empno| | v_emp.ename| |v_emp.sal); END;注意記錄類型變量中分量的個數(shù)、順序、類型應該與查詢列表中列的個數(shù)、順序、類型完全匹配。

24、46在SELECT語句中使用記錄類型變量在SELECT INTO 語句中使用記錄類型變量成員DECLARE v_emp emp%ROWTYPE; BEGIN SELECT empno,ename,sal INTO v_emp.empno, v_emp.ename,v_emp.sal FROM emp WHERE empno=7844; DBMS_OUTPUT.PUT_LINE(v_emp.empno| v_emp.ename|v_emp.sal); END;47在INSERT語句中使用記錄類型變量在VALUES子句中使用記錄類型變量 DECLARE v_dept dept%ROWTYPE;BE

25、GIN v_dept.deptno:=50; v_dept.loc:=BEIJING; V_dept.dname:=COMPUTER; INSERT INTO DEPT VALUES v_dept;END;注意記錄類型變量中分量的個數(shù)、順序、類型應該與表中列的個數(shù)、順序、類型完全匹配。 48在INSERT語句中使用記錄類型變量在VALUES子句中使用記錄類型變量成員 DECLARE v_emp emp%ROWTYPE;BEGIN SELECT * INTO v_emp FROM emp WHERE empno=7844; INSERT INTO emp(empno,ename,mgr,sal)

26、 VALUES(1234,TOM,v_emp.mgr,v_emp. sal);END; 49在UPDATE語句中使用記錄類型變量在SET子句中使用記錄類型變量（使用ROW關(guān)鍵字） DECLARE v_dept dept%ROWTYPE;BEGIN v_dept.deptno:=50; v_dept.loc:=TIANJIN; V_dept.dname:=COMPUTER; UPDATE dept SET ROW=v_dept WHERE deptno=50;END;注意記錄類型變量中分量的個數(shù)、順序、類型應該與表中列的個數(shù)、順序、類型完全匹配。 50在UPDATE語句中使用記錄類型變量在SET

27、子句中使用記錄類型變量成員DECLARE v_emp emp%ROWTYPE; BEGIN SELECT * INTO v_emp FROM emp WHERE empno=7844; UPDATE emp SET sal=v_emp.sal, comm=v_m WHERE empno=7369; END;51在DELETE語句中使用記錄類型變量DECLARE v_emp emp%ROWTYPE; BEGIN SELECT * INTO v_emp FROM emp WHERE empno=7844; DELETE FROM emp WHERE deptno=v_emp.deptno; END

28、; 5214.2.6 編譯指示編譯指示是對編譯程序發(fā)出的特殊指令，也稱為偽指令，不會改變程序含義。它只是向編譯程序傳遞信息，類似于嵌入在SQL中的注釋。在PL/SQL中使用PRAGMA關(guān)鍵字通知編譯程序，PL/SQL語句的剩余部分是一個編譯指示或命令。編譯指示在編譯時被處理，而不會在運行時被執(zhí)行，類似于C語言中的#define。53PL/SQL提供以下4種編譯指示EXCEPTION_INIT：告訴編譯程序?qū)⒁粋€特定的錯誤號與程序中所聲明的異常標識符關(guān)聯(lián)起來。RESTRICT_REFERENCES：告訴編譯程序打包程序的純度，即對函數(shù)中可以使用的SQL語句和包變量進行限制。SERIALLY_RE

29、USEABLE：告訴PL/SQL運行引擎時，在數(shù)據(jù)引用之間不要保持包級數(shù)據(jù)。AUTONOMOUS_TRANSACTION：告訴編譯程序，該程序塊為自治事務，即該事務的提交和回滾是獨立進行的。5414.2.7 PL/SQL中SQL語句由于PL/SQL執(zhí)行采用早期綁定，即在編譯階段對變量進行綁定，識別程序中標識符的位置，檢查用戶權(quán)限、數(shù)據(jù)庫對象等信息，因此在PL/SQL中只允許出現(xiàn): SELECT DML(UPDATE、DELETE、INSERT)事務控制語句（COMMIT、ROLLBACK、SAVEPOINT）注意DDL語句不可以直接使用55通常，利用SQL語句對數(shù)據(jù)庫進行操作時，各種相關(guān)量都在

30、代碼中以常量的形式指定，而在PL/SQL中可以通過變量動態(tài)指定各種相關(guān)量的值，從而實現(xiàn)對數(shù)據(jù)庫的動態(tài)操作。DECLARE v_empno NUMBER(4);BEGIN v_empno:=&x; UPDATE emp SET sal=sal+100 WHERE empno=v_empno;END; 56SELECT語句在PL/SQL程序中，使用SELECTINTO語句查詢一個記錄的信息。其語法為：SELECT select_list_item INTO variable_list|record_variable FROM tableWHERE condition; 57根據(jù)員工名或員工號查詢員

31、工信息，程序為：DECLARE v_emp emp%ROWTYPE; v_ename emp.ename%type; v_sal emp.sal%type;BEGIN SELECT * INTO v_emp FROM emp WHERE ename=SMITH; DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.sal); SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE empno=7900; DBMS_OUTPUT.PUT_LINE(v_ename| |v_sal);END; 58注意：SELECTINTO

32、語句只能查詢一個記錄的信息，如果沒有查詢到任何數(shù)據(jù)，會產(chǎn)生NO_DATA_FOUND異常；如果查詢到多個記錄，則會產(chǎn)生TOO_MANY_ROWS異常。INTO句子后的變量用于接收查詢的結(jié)果，變量的個數(shù)、順序應該與查詢的目標數(shù)據(jù)相匹配，也可以是記錄類型的變量。59用SELECTINTO語句查詢10號部門所有員工信息。DECLARE v_emp emp%ROWTYPE; BEGIN SELECT * INTO v_emp FROM emp WHERE deptno=10; END; /*ERROR 位于第 1 行:ORA-01422: 實際返回的行數(shù)超出請求的行數(shù)ORA-06512: 在line

33、460DML語句PL/SQL中DML語句對標準SQL語句中的DML語句進行了擴展，允許使用變量。DECLARE v_empno emp.empno%TYPE :=7500;BEGIN INSERT INTO emp(empno,ename,sal,deptno) VALUES(v_empno,JOAN,2300,20); UPDATE emp SET sal=sal+100 WHERE empno=v_empno; DELETE FROM emp WHERE empno=v_empno;END; 61WHERE標識符的區(qū)分系統(tǒng)首先查看WHERE子句中的標識符是否與表中的列名相同，如果相同，則該

34、標識符被解釋為列名；如果沒有同名列，系統(tǒng)檢查該標識符是不是PL/SQL語句塊的變量。字符串比較填充比較：通過在短字符串后添加空格，使兩個字符串達到相同長度，然后根據(jù)每個字符的ASCII碼進行比較。非填充比較：根據(jù)每個字符的ASCII碼進行比較，最先結(jié)束的字符串為小。62那么何時采用填充比較，何時采用非填充比較呢？PL/SQL中規(guī)定，對定長的字符串（CHAR類型的字符串和字符串常量）采用填充比較；如果比較的字符串中有一個是變長字符串（VARCHAR2類型的字符串），則采用非填充比較。 63例如，已知emp表中ename列類型為VARCHAR2（10），執(zhí)行下面的代碼。DECLARE v_enam

35、e CHAR(10):=TURNER;-v_ename VARCHAR2(20); -v_ename emp.ename%TYPE:=TURNER; v_sal emp.sal%TYPE;BEGIN SELECT sal INTO v_sal FROM emp WHERE ename=v_ename; dbms_output.put_line(v_sal);END; /DECLARE*第 1 行出現(xiàn)錯誤:ORA-01403: 未找到數(shù)據(jù)ORA-06512: 在 line 6 64產(chǎn)生錯誤的原因是VARCHAR2（10）類型與CHAR（10）類型比較時采用非填充比較，因此無法查詢到員工名為“TU

36、RNER”的員工?？梢詫_ename變量類型修改為VARCHAR2（10）類型，也可以直接采用emp.ename%TYPE方式定義。因此，為了保證程序的正確執(zhí)行，一定要使PL/SQL語句塊中的變量與要比較的數(shù)據(jù)庫列擁有相同的數(shù)據(jù)類型，可以使用%TYPE或%ROWTYPE來定義變量。65RETURNING如果要查詢當前DML語句操作的記錄的信息，可以在DML語句末尾使用RETURNING語句返回該記錄的信息。RETURNING語句的基本語法：RETURNING select_list_item INTO variable_list|record_variable; 66DECLARE v_sa

37、l emp.sal%TYPE;BEGIN UPDATE emp SET sal=sal+100 WHERE empno=7844 RETURNING sal INTO v_sal; DBMS_OUTPUT.PUT_LINE(v_sal);END;6714.3 控制結(jié)構(gòu) 選擇結(jié)構(gòu)循環(huán)結(jié)構(gòu)跳轉(zhuǎn)結(jié)構(gòu)6814.3.1選擇結(jié)構(gòu)IF語句CASE語句69（1）IF語句語法IF condition1 THEN statements1;ELSIF condition2 THEN statements2;ELSE else_statements;END IF; 注意條件是一個布爾型變量或表達式，取值只能是TRUE

38、，F(xiàn)ALSE，NULL。70例如，輸入一個員工號，修改該員工的工資，如果該員工為10號部門，工資增加100；若為20號部門，工資增加160；若為30號部門，工資增加200；否則增加300。 71DECLARE v_deptno emp.deptno%type; v_increment NUMBER(4); v_empno emp.empno%type;BEGIN v_empno:=&x; SELECT deptno INTO v_deptno FROM emp WHERE empno=v_empno; IF v_deptno=10 THEN v_increment:=100; ELSIF v_

39、deptno=20 THEN v_increment:=160; ELSIF v_deptno=30 THEN v_increment:=200; ELSE v_increment:=300; END IF; UPDATE emp SET sal=sal+v_increment WHERE empno=v_empno;END;72由于PL/SQL中的邏輯運算結(jié)果有TRUE，F(xiàn)ALSE和NULL三種，因此在進行選擇條件判斷時，要考慮條件為NULL的情況。例如，下面兩個程序，如果不考慮條件為NULL的情況，則運行結(jié)果是一致的，但是若考慮條件為NULL的情況，則結(jié)果就不同了。 7374為了避免條件為

40、NULL時出現(xiàn)歧義，應該在程序中進行條件是否為NULL的檢查。 75（2）CASE語句基本語法CASE WHEN condition1 THEN statements1; WHEN condition2 THEN statements2; WHEN conditionn THEN statementsn; ELSE else_statements;END CASE;注意 在CASE語句中，當?shù)谝粋€WHEN條件為真時，執(zhí)行其后的操作，操作完后結(jié)束CASE語句。其他的WHEN條件不再判斷，其后的操作也不執(zhí)行。 76根據(jù)輸入的員工號，修改該員工工資。如果該員工工資低于1000，則工資增加200；如果

41、工資在10002000之間，則增加150；如果工資在20003000之間，則增加100；否則增加50。 77DECLAREv_sal emp.sal%type;v_increment NUMBER(4);v_empno emp.empno%type;BEGINv_empno:=&x;SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;CASE WHEN v_sal1000 THEN v_increment:=200; WHEN v_sal2000 THEN v_increment:=150; WHEN v_sal 50; END LOOP;EN

42、D; 83（2）WHILE循環(huán)基本語法WHILE condition LOOP sequence_of_statement;END LOOP; 84利用WHILE循環(huán)向temp_table表中插入50條記錄。DECLARE v_counter BINARY_INTEGER :=1;BEGIN WHILE v_counter = 50 LOOP INSERT INTO temp_table VALUES (v_counter, Loop index); v_counter := v_counter + 1; END LOOP;END; 85（3）FOR循環(huán)基本語法FOR loop_counter

43、 IN REVERSE low_bound.high_boundLOOP sequence_of_statement;END LOOP;注意：循環(huán)變量不需要顯式定義，系統(tǒng)隱含地將它聲明為BINARY_INTEGER變量；系統(tǒng)默認時，循環(huán)變量從下界往上界遞增計數(shù)，如果使用REVERSE關(guān)鍵字，則表示循環(huán)變量從上界向下界遞減計數(shù)；循環(huán)變量只能在循環(huán)體中使用，不能在循環(huán)體外使用。86利用FOR循環(huán)向temp_table表中插入50條記錄。BEGIN FOR v_counter IN 1.50 LOOP INSERT INTO temp_table VALUES (v_counter, Loop In

44、dex); END LOOP;END;8714.3.3跳轉(zhuǎn)結(jié)構(gòu)語法格式：標號 GOTO 標號；說明：塊內(nèi)可以跳轉(zhuǎn)，內(nèi)層塊可以跳到外層塊，但外層塊不能跳到內(nèi)層。IF語句不能跳入。不能從循環(huán)體外跳入循環(huán)體內(nèi)。不能從子程序外部跳到子程序中。由于goto語句的缺點，建議盡量少用甚至不用goto語句。 88DECLARE v_counter BINARY_INTEGER :=1;BEGIN INSERT INTO temp_table VALUES (v_counter, Loop index); v_counter := v_Counter + 1; IF v_counter( SELECT AVG(

45、sal) FROM emp WHERE deptno=10); ELSIF v_table = dept THEN OPEN v_cursor FOR SELECT deptno,count(*) num FROM emp GROUP BY deptno; ELSE RAISE_APPLICATION_ERROR(-20000,Input must be emp or dept); END IF; 131 LOOP IF v_table = emp THEN FETCH v_cursor INTO v_emp; EXIT WHEN v_cursor%NOTFOUND; DBMS_OUTPUT.

46、PUT_LINE(v_emp.empno| | v_emp.ename| | v_emp.sal| | v_emp.deptno); ELSE FETCH v_cursor INTO v_deptno,v_num; EXIT WHEN v_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_deptno| |v_num); END IF; END LOOP; CLOSE v_cursor;END; 13214.5 異常處理異常概述異常處理過程 異常的傳播13314.5.1 異常概述Oracle錯誤處理機制異常的類型134（1） Oracle錯誤處理機制Oracle中

47、對運行時錯誤的處理采用了異常處理機制。 一個錯誤對應一個異常，當錯誤產(chǎn)生時拋出相應的異常，并被異常處理器捕獲，程序控制權(quán)傳遞給異常處理器，由異常處理器來處理運行時錯誤。 135（2）異常的類型預定義的Oracle異常（ Oracle錯誤）非預定義的Oracle異常（ Oracle錯誤）用戶定義的異常（用戶定義錯誤）136預定義的Oracle異常當Oracle錯誤產(chǎn)生時，與錯誤對應的預定義異常被自動拋出，通過捕獲該異?？梢詫﹀e誤進行處理。常用預定義異常包括：137異常情況名錯誤代碼描述CURSOR_ALREADY_OPEN ORA-06511 嘗試打開已經(jīng)打開的游標 INVALID_CURSOR

48、ORA-01001不合法的游標操作（如要打開已經(jīng)關(guān)閉的游標） NO_DATA_FOUNDORA-01403沒有發(fā)現(xiàn)數(shù)據(jù) TOO_MANY_ROWSORA-01422一個SELECT INTO語句匹配多個數(shù)據(jù)行INVALID_NUMBERORA-01722轉(zhuǎn)換成數(shù)字失敗 （X） VALUE_ERRORORA-06502截斷、算法或轉(zhuǎn)換錯誤，通常出現(xiàn)在賦值錯誤 ZERO_DIVIDEORA-01476除數(shù)為0 ROWTYPE_MISMATCHORA-06504主機游標變量與PL/SQL游標變量類型不匹配138異常情況名錯誤代碼描述DUP_VAL_ON_INDEXORA-00001違反唯一性約束或主

49、鍵約束SYS_INVALID_ROWIDORA-01410轉(zhuǎn)換成ROWID失敗TIMEOUT_ON_RESOURCEORA-00051在等待資源中出現(xiàn)超時LOGIN_DENIEDORA-01017無效用戶名/密碼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_NUL

50、LORA-06531對某NULL PL/SQL表或可變數(shù)組試圖應用集合方法，而不是EXISTS SELF_IS_NULLORA-30625調(diào)用空對象實例的方法SUBSCRIPT_BEYOND_COUNTORA-06533對嵌套表或數(shù)組索引引用時超出集合中元素的數(shù)量SUBSCRIPT_OUTSIDE_LIMITORA-06532對嵌套表或可變數(shù)組索引的引用超出聲明的范圍140非預定義異常有一些Oracle錯誤沒有預定義異常與其關(guān)聯(lián)，需要在語句塊的聲明部分聲明一個異常名稱，然后通過編譯指示PRAGMA EXCEPTION_INIT將該異常名稱與一個Oracle錯誤相關(guān)聯(lián)。此后，當執(zhí)行過程出現(xiàn)該錯誤

51、時將自動拋出該異常。141聲明一個異常名稱e_integrity EXCEPTION;將異常與一個Oracle錯誤號相綁定PRAGMA EXCEPTION-INIT(e_integrity.-2291)示例DECLARE e_deptno_fk EXCEPTION; PRAGMA EXCEPTION_INIT(e_deptno_fk,-2292);BEGINEXCEPTIONEND;142用戶自定義的異常用戶定義錯誤是指，有些操作并不會產(chǎn)生Oracle錯誤，但是從業(yè)務規(guī)則角度考慮，認為是一種錯誤。用戶自定義異常必須在聲明部分進行聲明。當異常發(fā)生時，系統(tǒng)不能自動觸發(fā)，需要用戶使用RAISE語句。

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

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

54、atements1;WHEN exception3OR exception4THEN sequence_of_statements2;WHEN OTHERS THEN sequence_of_statementsn;END;注意：一個異常處理器可以捕獲多個異常，只需在WHEN子句中用 OR連接即可；一個異常只能被一個異常處理器捕獲，并進行處理。 148查詢名為SMITH的員工工資，如果該員工不存在，則輸出“There is not such an employee!”；如果存在多個同名的員工，則輸出其員工號和工資。DECLARE v_sal emp.sal%type;BEGIN SELECT

55、sal INTO v_sal FROM emp WHERE ename=SMITH; DBMS_OUTPUT.PUT_LINE(v_sal);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(There is not such an emplyee!); WHEN TOO_MANY_ROWS THEN FOR v_emp IN (SELECT * FROM emp WHERE ename=SMITH) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.sal); END LOOP;END;14

56、9刪除dept表中部門號為10的部門信息，如果不能刪除則輸出“There are subrecords in emp table!”。DECLARE e_deptno_fk EXCEPTION; PRAGMA EXCEPTION_INIT(e_deptno_fk,-2292);BEGIN DELETE FROM dept WHERE deptno=10;EXCEPTION WHEN e_deptno_fk THEN DBMS_OUTPUT.PUT_LINE( There are subrecords in emp table!);END;150修改7844員工的工資，保證修改后工資不超過600

57、0。DECLARE e_highlimit EXCEPTION; v_sal emp.sal%TYPE;BEGIN UPDATE emp SET sal=sal+100 WHERE empno=7844 RETURNING sal INTO v_sal; IF v_sal6000 THEN RAISE e_highlimit; END IF;EXCEPTION WHEN e_highlimit THEN DBMS_OUTPUT.PUT_LINE(The salary is too large!); ROLLBACK;END; 151（4）OTHERS異常處理器OTHERS異常處理器是一個特殊的

58、異常處理器，可以捕獲所有的異常。通常，OTHERS異常處理器總是作為異常處理部分的最后一個異常處理器，負責處理那些沒有被其他異常處理器捕獲的異常。 152DECLARE v_sal emp.sal%TYPE; e_highlimit EXCEPTION;BEGIN SELECT sal INTO v_sal FROM emp WHERE ename=JOAN; UPDATE emp SET sal=sal+100 WHERE empno=7900; IF v_sal6000 THEN RAISE e_highlimit; END IF;EXCEPTION WHEN e_highlimit THEN DBMS_OUTPUT.PUT_LINE(The salary is too large!); ROLLBACK; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(There is some wrong in selecting!);END; 153可以通過兩個函數(shù)來獲取錯誤相關(guān)信息。SQLCODE：返回當前錯誤代碼。如果是用戶定義錯誤返回值