【文檔資料】【精品課件】 精品課件-第16章 plsql程序設(shè)計(jì)

1、第16章 PL/SQL程序設(shè)計(jì)1本章內(nèi)容PL/SQL概述PL/SQL根底控制結(jié)構(gòu)游標(biāo)異常處理存儲子程序包觸發(fā)器集合 批綁定2本章要求掌握PL/SQL程序設(shè)計(jì)根底知識掌握存儲過程、函數(shù)、包、觸發(fā)器的應(yīng)用掌握集合、批綁定的應(yīng)用316.1 PL/SQL概述PL/SQL特點(diǎn)PL/SQL功能特性PL/SQL執(zhí)行過程與開發(fā)工具416.1.1 PL/SQL特點(diǎn)與SQL語言緊密集成。減小網(wǎng)絡(luò)流量，提高應(yīng)用程序的運(yùn)行性能。模塊化的程序設(shè)計(jì)功能，提高了系統(tǒng)可靠性。效勞器端程序設(shè)計(jì)，可移植性好。516.1.2 PL/SQL功能特性語句塊結(jié)構(gòu)異常處理變量和類型條件語句循環(huán)結(jié)構(gòu)游標(biāo)過程、函數(shù)和觸發(fā)器包集合動態(tài)SQL批綁

2、定對象特性616.1.3 PL/SQL執(zhí)行過程與開發(fā)工具PL/SQL塊SQL語句客戶端應(yīng)用程序PL/SQL引擎數(shù)據(jù)庫效勞器過程化語句執(zhí)行器SQL執(zhí)行器塊中SQL語句PL/SQL執(zhí)行過程 7PL/SQL開發(fā)工具SQL *PLUSProcedure BuilderOracle Form、Oracle ReportsPL/SQL Developer816.2 PL/SQL根底PL/SQL程序結(jié)構(gòu) 詞法單元 數(shù)據(jù)類型變量與常量PL/SQL記錄 編譯指示PL/SQL中的SQL語句916.2.1 PL/SQL程序結(jié)構(gòu)PL/SQL塊的組成PL/SQL塊分類 101PL/SQL塊的組成PL/SQL程序的根本單

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

4、CHAR2(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;14DECLARE v_sal NUMBER(6,2); v_deptno NUMBER(2);BEGIN BEGIN SELECT deptno INTO v_deptno FROM emp WHERE empno=784

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

6、系統(tǒng)在特定條件下調(diào)用執(zhí)行。 17命名塊例如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;1816.2.2 詞法單元字符集標(biāo)識符分隔符常量值注釋191字符集PL/SQL的字符集包括：大小寫字母：AZ，az數(shù)字：09空白：制表符、空格和回車數(shù)字符號：+ - * / =標(biāo)點(diǎn)符號：

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

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

9、_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; 2416.2.3 數(shù)據(jù)類型數(shù)字類型字符類型日期/區(qū)間類型行標(biāo)識類型布爾類型原始類型LOB類型引用類型 記錄類型集合類型%TYPE與

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

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

12、來自國家字符集的字符串。 類 型PL/SQL中最大字節(jié)數(shù)Oracle中最大字節(jié)數(shù)VARCHAR2327674000NVARCHAR2327674000CHAR327672000NCHAR327672000LONG327602GB28日期/區(qū)間類型 DATE：與數(shù)據(jù)庫中的DATE類型相同，存儲日期和時(shí)間信息，包括世紀(jì)、年、月、日、小時(shí)、分和秒，不包括秒的小數(shù)局部。TIMESTAMP：與DATE類型相似，但包括秒的小數(shù)局部，有以下3種形式。TIMESTAMP(p)：其中p為秒字段的小數(shù)局部精度。TIMESTAMP(p)WITH TIME ZONE：返回當(dāng)前時(shí)區(qū)的時(shí)間戳。TIMESTAMP(p)WI

13、TH LOACL TIME ZONE：返回?cái)?shù)據(jù)庫時(shí)區(qū)的時(shí)間戳。29INTERVAL：用于存儲兩個時(shí)間戳之間的時(shí)間間隔，有下面兩種形式。INTERVAL YEAR (p)TO MONTH：兩個時(shí)間戳相差的年數(shù)和月數(shù)。INTERVAL DAY(dp) TO SECOND(sp)：兩個時(shí)間戳相差的天數(shù)和秒數(shù)。30行標(biāo)識類型ROWID表示行的物理地址UROWID既可以表示行的物理地址，也可以表示行的邏輯地址。布爾類型BOOLEAN只能在PL/SQL中使用，其取值為邏輯值，包括TRUE、FALSE、NULL。原始類型與Oracle數(shù)據(jù)庫中的原始類型相似，但子節(jié)數(shù)不同。 類 型PL/SQL中最大字節(jié)數(shù)Or

14、acle中最大字節(jié)數(shù)RAW327672000LONG RAW327672G31LOB類型包括BLOB，CLOB，NCLOB和BFILE四種類型。其中BLOB存放二進(jìn)制數(shù)據(jù)，CLOB，NCLOB存放文本數(shù)據(jù)，而BFILE存放指向操作系統(tǒng)文件的指針。LOB類型變量可以存儲4 GB的數(shù)據(jù)量。引用類型引用類型類似于其他高級語言中的指針類型。在PL/SQL中，引用類型包括游標(biāo)的引用類型和對象的引用類型，即REF CURSOR和REF object_type。32記錄類型記錄類型是復(fù)合類型，類似于C語言中的結(jié)構(gòu)體，是一個包含假設(shè)干個成員分量的復(fù)合類型。在使用記錄類型時(shí)，需要先在聲明局部定義記錄類型和記錄類

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

16、據(jù)庫表列中有NOT NULL約束，那么%TYPE與%ROWTYPE返回的數(shù)據(jù)類型沒有此限制。 34DECLARE 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);END; 3516.2.4 變量與常量變量與常量的定義變量的

17、作用域36變量聲明1變量與常量的定義變量定義的一般格式variable_name CONSTANT datatype NOT NULL DEFAULT|:=expression;說明變量或常量名稱是一個PL/SQL標(biāo)識符，應(yīng)符合標(biāo)識符命名標(biāo)準(zhǔn)；每行只能定義一個變量；如果加上關(guān)鍵字CONSTANT，那么表示所定義的是一個常量，必須為它賦初值；如果定義變量時(shí)使用了NOT NULL關(guān)鍵字，那么必須為變量賦初值；如果變量沒有賦初值，那么默認(rèn)為NULL；使用DEFAULT或“:=運(yùn)算符為變量初始化。37DECLARE v1 NUMBER(4); v2 NUMBER(4) NOT NULL :=10; v

18、3 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;382變量的作用域變量的作用域是指變量的有效作用范圍，從變量聲明開始，直到塊結(jié)束。如果PL/SQL塊相互嵌套，那么在內(nèi)部塊中聲明的變量是局部的，只能在內(nèi)部塊中引用，而在外部塊中聲明的變量是全局的，既可以在外部塊中引用，也可以在內(nèi)部塊中引用。如果內(nèi)部塊與外部塊中定義了同名變量，那么在內(nèi)部塊中引用外部塊的全局變量時(shí)需要使用外部塊名

19、進(jìn)行標(biāo)識。 39DECLARE 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; 4016.2.5 PL/SQL記錄 用戶定義記錄類型及變量 利用%ROWTYPE獲取記錄類型定義變量 記錄類型變量的應(yīng)用 在SELECT語句

20、中使用記錄類型變量 在INSERT語句中使用記錄類型變量 在UPDATE語句中使用記錄類型變量 在DELETE語句中使用記錄類型變量 411用戶定義記錄類型及變量 定義記錄類型的語法為TYPE record_type IS RECORD(field1 datatype1 NOT NULLDEFAULT|:=expr1,field2 datatype2 NOT NULL DEFAULT|:=expr2,fieldn datatypen NOT NULL DEFAULT|:=exprn);注意：相同記錄類型的變量可以相互賦值；不同記錄類型的變量，即使成員完全相同也不能相互賦值；記錄類型只能應(yīng)用于定

21、義該記錄類型的PL/SQL塊中，即記錄類型是局部的。42利用記錄類型以及記錄類型變量，保存員工信息。 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;432利用%ROWTYPE獲取記錄類型定義變量DECLARE v_emp1 em

22、p%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.empno| | v_emp10.ename); END LOOP

23、; CLOSE c_emp;END; 443記錄類型變量的應(yīng)用在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ù)、順序、類型應(yīng)該與查詢列表中列的個數(shù)、順序、類型完全匹配。 45在SELECT語句中使用記錄類型變量在SELECT INTO 語句中

24、使用記錄類型變量成員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;46在INSERT語句中使用記錄類型變量在VALUES子句中使用記錄類型變量 DECLARE v_dept dept%ROWTYPE;BEGIN v_dept.deptno:=50; v_dept.loc:=B

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

26、l);END; 48在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ù)、順序、類型應(yīng)該與表中列的個數(shù)、順序、類型完全匹配。 49在UPDATE語句中使用記錄類型變量在SET子句中使用記錄類型變量成員DECLARE v_emp emp%ROWTYPE

27、; BEGIN SELECT * INTO v_emp FROM emp WHERE empno=7844; UPDATE emp SET sal=v_emp.sal, comm=v_emp m WHERE empno=7369; END;50在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; 5116.2.5 編譯指示編譯指示是對編譯程序發(fā)出的特殊指令，也

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

29、級數(shù)據(jù)。AUTONOMOUS_TRANSACTION：告訴編譯程序，該程序塊為自治事務(wù)，即該事務(wù)的提交和回滾是獨(dú)立進(jìn)行的。5316.2.6 PL/SQL中SQL語句由于PL/SQL執(zhí)行采用早期綁定，即在編譯階段對變量進(jìn)行綁定，識別程序中標(biāo)識符的位置，檢查用戶權(quán)限、數(shù)據(jù)庫對象等信息，因此在PL/SQL中只允許出現(xiàn): SELECT DML(UPDATE、DELETE、INSERT)事務(wù)控制語句COMMIT、ROLLBACK、SAVEPOINT注意DDL語句不可以直接使用54通常，利用SQL語句對數(shù)據(jù)庫進(jìn)行操作時(shí)，各種相關(guān)量都在代碼中以常量的形式指定，而在PL/SQL中可以通過變量動態(tài)指定各種相關(guān)量

30、的值，從而實(shí)現(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; 55SELECT語句在PL/SQL程序中，使用SELECTINTO語句查詢一個記錄的信息。其語法為：SELECT select_list_item INTO variable_list|record_variable FROM tableWHERE condition; 56根據(jù)員工名或員工號查詢員工信息，程序?yàn)椋篋ECLARE v_emp emp%ROWTYPE; v

31、_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; 57注意：SELECTINTO語句只能查詢一個記錄的信息，如果沒有查詢到任何數(shù)據(jù)，會產(chǎn)生NO_DATA

32、_FOUND異常；如果查詢到多個記錄，那么會產(chǎn)生TOO_MANY_ROWS異常。INTO句子后的變量用于接收查詢的結(jié)果，變量的個數(shù)、順序應(yīng)該與查詢的目標(biāo)數(shù)據(jù)相匹配，也可以是記錄類型的變量。58用SELECTINTO語句查詢10號部門所有員工信息。DECLARE v_emp emp%ROWTYPE; BEGIN SELECT * INTO v_emp FROM emp WHERE deptno=10; END; /*ERROR 位于第 1 行:ORA-01422: 實(shí)際返回的行數(shù)超出請求的行數(shù)ORA-06512: 在line 459DML語句PL/SQL中DML語句對標(biāo)準(zhǔn)SQL語句中的DML語句

33、進(jìn)行了擴(kuò)展，允許使用變量。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; 60WHERE標(biāo)識符的區(qū)分系統(tǒng)首先查看WHERE子句中的標(biāo)識符是否與表中的列名相同，如果相同，那么該標(biāo)識符被解釋為列名；如果沒有同名列，系統(tǒng)檢查該標(biāo)識符是不是PL/SQ

34、L語句塊的變量。字符串比較填充比較：通過在短字符串后添加空格，使兩個字符串到達(dá)相同長度，然后根據(jù)每個字符的ASCII碼進(jìn)行比較。非填充比較：根據(jù)每個字符的ASCII碼進(jìn)行比較，最先結(jié)束的字符串為小。61那么何時(shí)采用填充比較，何時(shí)采用非填充比較呢？PL/SQL中規(guī)定，對定長的字符串CHAR類型的字符串和字符串常量采用填充比較；如果比較的字符串中有一個是變長字符串VARCHAR2類型的字符串，那么采用非填充比較。 62例如，emp表中ename列類型為VARCHAR210，執(zhí)行下面的代碼。DECLARE v_ename CHAR(10):=TURNER;-v_ename VARCHAR2(20);

35、 -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 63產(chǎn)生錯誤的原因是VARCHAR210類型與CHAR10類型比較時(shí)采用非填充比較，因此無法查詢到員工名為“TURNER的員工?？梢詫_ename變量類型修改為VARCHAR210類型，也可以直接采用

36、emp.ename%TYPE方式定義。因此，為了保證程序的正確執(zhí)行，一定要使PL/SQL語句塊中的變量與要比較的數(shù)據(jù)庫列擁有相同的數(shù)據(jù)類型，可以使用%TYPE或%ROWTYPE來定義變量。64RETURNING如果要查詢當(dāng)前DML語句操作的記錄的信息，可以在DML語句末尾使用RETURNING語句返回該記錄的信息。RETURNING語句的根本語法：RETURNING select_list_item INTO variable_list|record_variable; 65DECLARE v_sal emp.sal%TYPE;BEGIN UPDATE emp SET sal=sal+100

37、WHERE empno=7844 RETURNING sal INTO v_sal; DBMS_OUTPUT.PUT_LINE(v_sal);END;6616.3 控制結(jié)構(gòu) 選擇結(jié)構(gòu)循環(huán)結(jié)構(gòu)跳轉(zhuǎn)結(jié)構(gòu)67選擇結(jié)構(gòu)IF語句CASE語句681IF語句語法IF condition1 THEN statements1;ELSIF condition2 THEN statements2;ELSE else_statements;END IF; 注意條件是一個布爾型變量或表達(dá)式，取值只能是TRUE，F(xiàn)ALSE，NULL。69例如，輸入一個員工號，修改該員工的工資，如果該員工為10號部門，工資增加100；假設(shè)

38、為20號部門，工資增加160；假設(shè)為30號部門，工資增加200；否那么增加300。 70DECLARE 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_deptno=20 THEN v_increment:=160; ELSIF v_deptno=30 TH

39、EN v_increment:=200; ELSE v_increment:=300; END IF; UPDATE emp SET sal=sal+v_increment WHERE empno=v_empno;END;71由于PL/SQL中的邏輯運(yùn)算結(jié)果有TRUE，F(xiàn)ALSE和NULL三種，因此在進(jìn)行選擇條件判斷時(shí)，要考慮條件為NULL的情況。例如，下面兩個程序，如果不考慮條件為NULL的情況，那么運(yùn)行結(jié)果是一致的，但是假設(shè)考慮條件為NULL的情況，那么結(jié)果就不同了。 7273為了防止條件為NULL時(shí)出現(xiàn)歧義，應(yīng)該在程序中進(jìn)行條件是否為NULL的檢查。 742CASE語句根本語法CASE

40、WHEN condition1 THEN statements1; WHEN condition2 THEN statements2; WHEN conditionn THEN statementsn; ELSE else_statements;END CASE;注意 在CASE語句中，當(dāng)?shù)谝粋€WHEN條件為真時(shí)，執(zhí)行其后的操作，操作完后結(jié)束CASE語句。其他的WHEN條件不再判斷，其后的操作也不執(zhí)行。 75根據(jù)輸入的員工號，修改該員工工資。如果該員工工資低于1000，那么工資增加200；如果工資在10002000之間，那么增加150；如果工資在20003000之間，那么增加100；否那么增加

41、50。 76DECLAREv_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;END; 822WHILE循環(huán)根本語法WHILE condition LOOP sequence_o

42、f_statement;END LOOP; 83利用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; 843FOR循環(huán)根本語法FOR loop_counter IN REVERSE low_bound.high_boundLOOP sequence_of_sta

43、tement;END LOOP;注意：循環(huán)變量不需要顯式定義，系統(tǒng)隱含地將它聲明為BINARY_INTEGER變量；系統(tǒng)默認(rèn)時(shí)，循環(huán)變量從下界往上界遞增計(jì)數(shù)，如果使用REVERSE關(guān)鍵字，那么表示循環(huán)變量從上界向下界遞減計(jì)數(shù)；循環(huán)變量只能在循環(huán)體中使用，不能在循環(huán)體外使用。85利用FOR循環(huán)向temp_table表中插入50條記錄。BEGIN FOR v_counter IN 1.50 LOOP INSERT INTO temp_table VALUES (v_counter, Loop Index); END LOOP;END;86跳轉(zhuǎn)結(jié)構(gòu)語法格式：?標(biāo)號? GOTO 標(biāo)號；說明：塊內(nèi)可以跳

44、轉(zhuǎn)，內(nèi)層塊可以跳到外層塊，但外層塊不能跳到內(nèi)層。IF語句不能跳入。不能從循環(huán)體外跳入循環(huán)體內(nèi)。不能從子程序外部跳到子程序中。由于goto語句的缺點(diǎn)，建議盡量少用甚至不用goto語句。 87DECLARE 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(sal) FROM emp WHERE deptno=10); ELSIF v_table = dept TH

45、EN 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; 130 LOOP IF v_table = emp THEN FETCH v_cursor INTO v_emp; EXIT WHEN v_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp.empno| | v_emp.ename| | v_emp.sal| | v_e

46、mp.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; 13116.5 異常處理異常概述異常處理過程 異常的傳播13216.5.1 異常概述Oracle錯誤處理機(jī)制異常的類型1331 Oracle錯誤處理機(jī)制Oracle中對運(yùn)行時(shí)錯誤的處理采用了異常處理機(jī)制。 一個錯誤對應(yīng)一個異常，當(dāng)錯誤產(chǎn)生時(shí)拋出相應(yīng)的異常，并被異常處理器捕獲，程序

47、控制權(quán)傳遞給異常處理器，由異常處理器來處理運(yùn)行時(shí)錯誤。 1342異常的類型預(yù)定義的Oracle異常 Oracle錯誤非預(yù)定義的Oracle異常 Oracle錯誤用戶定義的異常用戶定義錯誤135預(yù)定義的Oracle異常當(dāng)Oracle錯誤產(chǎn)生時(shí)，與錯誤對應(yīng)的預(yù)定義異常被自動拋出，通過捕獲該異常可以對錯誤進(jìn)行處理。常用預(yù)定義異常包括：136異常情況名錯誤代碼描述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_M

48、ANY_ROWSORA-01422一個SELECT INTO語句匹配多個數(shù)據(jù)行INVALID_NUMBERORA-01722轉(zhuǎn)換成數(shù)字失敗 （X） VALUE_ERRORORA-06502截?cái)?、算法或轉(zhuǎn)換錯誤，通常出現(xiàn)在賦值錯誤 ZERO_DIVIDEORA-01476除數(shù)為0 ROWTYPE_MISMATCHORA-06504主機(jī)游標(biāo)變量與PL/SQL游標(biāo)變量類型不匹配137異常情況名錯誤代碼描述DUP_VAL_ON_INDEXORA-00001違反唯一性約束或主鍵約束SYS_INVALID_ROWIDORA-01410轉(zhuǎn)換成ROWID失敗TIMEOUT_ON_RESOURCEORA-000

49、51在等待資源中出現(xiàn)超時(shí)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)部錯誤138異常情況名錯誤代碼描述ACCESS_INTO_NULLORA-06530給空對象屬性賦值COLLECTION_IS_NULLORA-06531對某NULL PL/SQL表或可變數(shù)組試圖應(yīng)用集合方法，而不是EXISTS SELF_IS_NULLORA-3

50、0625調(diào)用空對象實(shí)例的方法SUBSCRIPT_BEYOND_COUNTORA-06533對嵌套表或數(shù)組索引引用時(shí)超出集合中元素的數(shù)量SUBSCRIPT_OUTSIDE_LIMITORA-06532對嵌套表或可變數(shù)組索引的引用超出聲明的范圍139非預(yù)定義異常有一些Oracle錯誤沒有預(yù)定義異常與其關(guān)聯(lián)，需要在語句塊的聲明局部聲明一個異常名稱，然后通過編譯指示PRAGMA EXCEPTION_INIT將該異常名稱與一個Oracle錯誤相關(guān)聯(lián)。此后，當(dāng)執(zhí)行過程出現(xiàn)該錯誤時(shí)將自動拋出該異常。140聲明一個異常名稱e_integrity EXCEPTION;將異常與一個Oracle錯誤號相綁定PRAG

51、MA EXCEPTION-INIT(e_integrity.-2291)例如DECLARE e_deptno_fk EXCEPTION; PRAGMA EXCEPTION_INIT(e_deptno_fk,-2292);BEGINEXCEPTIONEND;141用戶自定義的異常用戶定義錯誤是指，有些操作并不會產(chǎn)生Oracle錯誤，但是從業(yè)務(wù)規(guī)那么角度考慮，認(rèn)為是一種錯誤。用戶自定義異常必須在聲明局部進(jìn)行聲明。當(dāng)異常發(fā)生時(shí)，系統(tǒng)不能自動觸發(fā)，需要用戶使用RAISE語句。在異常處理局部捕捉并處理異常。14216.5.2 異常處理過程異常的定義異常的拋出異常的捕獲與處理OTHERS異常處理器143異

52、常處理分3個步驟進(jìn)行：在聲明局部為錯誤定義異常，包括非預(yù)定義異常和用戶定義異常。在執(zhí)行過程中當(dāng)錯誤產(chǎn)生時(shí)拋出與錯誤對應(yīng)的異常。在異常處理局部通過異常處理器捕獲異常，并進(jìn)行異常處理。1441異常的定義Oracle中的3種異常，其中預(yù)定義異常由系統(tǒng)定義，而其他兩種異常那么需要用戶定義。定義異常方法e_exception EXCEPTION;如果是非預(yù)定義的異常，需要將異常與一個Oracle錯誤相關(guān)聯(lián)，其語法為：PRAGMA EXCEPTION_INIT(e_exception, -#);注意Oracle內(nèi)部錯誤號用一個負(fù)的5位數(shù)表示，如-02292。其中 -20999-20000為用戶定義錯誤的保

53、存號。1452異常的拋出由于系統(tǒng)可以自動識別Oracle內(nèi)部錯誤，因此當(dāng)錯誤產(chǎn)生時(shí)系統(tǒng)會自動拋出與之對應(yīng)的預(yù)定義異?；蚍穷A(yù)定義異常。但是，系統(tǒng)無法識別用戶定義錯誤，因此當(dāng)用戶定義錯誤產(chǎn)生時(shí)，需要用戶手動拋出與之對應(yīng)的異常。用戶定義異常的拋出語法為RAISE user_define_exception； 1463異常的捕獲與處理異常處理器的根本形式為EXCEPTIONWHEN exception1OR excetpion2THEN sequence_of_statements1;WHEN exception3OR exception4THEN sequence_of_statements2;WH

54、EN OTHERS THEN sequence_of_statementsn;END;注意：一個異常處理器可以捕獲多個異常，只需在WHEN子句中用 OR連接即可；一個異常只能被一個異常處理器捕獲，并進(jìn)行處理。 147查詢名為SMITH的員工工資，如果該員工不存在，那么輸出“There is not such an employee!；如果存在多個同名的員工，那么輸出其員工號和工資。DECLARE v_sal emp.sal%type;BEGIN SELECT sal INTO v_sal FROM emp WHERE ename=SMITH; DBMS_OUTPUT.PUT_LINE(v_sa

55、l);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;148刪除dept表中部門號為10的部門信息，如果不能刪除那么輸出“There are subrecords in emp table!。D

56、ECLARE 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;149修改7844員工的工資，保證修改后工資不超過6000。DECLARE e_highlimit EXCEPTION; v_sal emp.sal%TYPE;BEGIN UPDATE emp

57、 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; 1504OTHERS異常處理器OTHERS異常處理器是一個特殊的異常處理器，可以捕獲所有的異常。通常，OTHERS異常處理器總是作為異常處理局部的最后一個異常處理器，負(fù)責(zé)處理那些沒有被其他異常處理器捕獲的

58、異常。 151DECLARE 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 OTHE

59、RS THEN DBMS_OUTPUT.PUT_LINE(There is some wrong in selecting!);END; 152可以通過兩個函數(shù)來獲取錯誤相關(guān)信息。SQLCODE：返回當(dāng)前錯誤代碼。如果是用戶定義錯誤返回值為1；如果是ORA-1403：NO DATA FOUND錯誤，返回值為100其他Oracle內(nèi)部錯誤返回相應(yīng)的錯誤號。 SQLERRM：返回當(dāng)前錯誤的消息文本。如果是Oracle內(nèi)部錯誤，返回系統(tǒng)內(nèi)部的錯誤描述；如果是用戶定義錯誤，那么返回信息文本為“User-defined Exception。 153DECLARE v_sal emp.sal%TYPE;

60、e_highlimit EXCEPTION; v_code NUMBER(6); v_text VARCHAR2(200);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