Oracle 11g數(shù)據(jù)庫管理與開發(fā)基礎(chǔ)教程 教學(xué)課件 袁鵬飛 第12章

1、在線教務(wù)輔導(dǎo)網(wǎng)：在線教務(wù)輔導(dǎo)網(wǎng)： :/shangfuwang 教材其余課件及動畫素材請查閱在線教務(wù)輔導(dǎo)網(wǎng)教材其余課件及動畫素材請查閱在線教務(wù)輔導(dǎo)網(wǎng) QQ:349134187 或者直接輸入下面地址：或者直接輸入下面地址： :/shop106150152.taobao 第第1212章章 PL/SQL基礎(chǔ)基礎(chǔ) PL/SQLPL/SQL（Procedural Procedural Language extensions Language extensions to SQLto SQL）就是）就是OracleOracle對對 SQLSQL的過程化擴(kuò)展，是專的過程化擴(kuò)展，是專 門用于門用于OracleOr

2、acle產(chǎn)品的數(shù)產(chǎn)品的數(shù) 據(jù)庫編程語言。本章將據(jù)庫編程語言。本章將 介紹介紹PL/SQLPL/SQL程序設(shè)計語程序設(shè)計語 言的基本概念、言的基本概念、PL/SQLPL/SQL 程序設(shè)計和開發(fā)等內(nèi)容程序設(shè)計和開發(fā)等內(nèi)容 本章知識點本章知識點 l 視圖的基本概念和分類視圖的基本概念和分類 l 視圖的創(chuàng)建視圖的創(chuàng)建 l 視圖的修改和刪除視圖的修改和刪除 l 內(nèi)嵌視圖和對象視圖的應(yīng)用內(nèi)嵌視圖和對象視圖的應(yīng)用 12.1 PL/SQL基礎(chǔ)基礎(chǔ) pPL/SQLPL/SQL（Procedural Language extensions to Procedural Language extensions to

3、SQLSQL）語言是）語言是OracleOracle對對SQLSQL語言的過程化擴(kuò)展，是語言的過程化擴(kuò)展，是 專門用于專門用于OracleOracle產(chǎn)品的數(shù)據(jù)庫編程語言。產(chǎn)品的數(shù)據(jù)庫編程語言。 p在在PL/SQLPL/SQL語言中，既可以通過語言中，既可以通過SQLSQL語言實現(xiàn)對數(shù)據(jù)語言實現(xiàn)對數(shù)據(jù) 庫的操作，也可以通過過程化語言中的復(fù)雜結(jié)構(gòu)庫的操作，也可以通過過程化語言中的復(fù)雜結(jié)構(gòu) 完成復(fù)雜的業(yè)務(wù)邏輯。完成復(fù)雜的業(yè)務(wù)邏輯。 12.1.1 程序結(jié)構(gòu)程序結(jié)構(gòu) pPL/SQLPL/SQL程序的基本結(jié)構(gòu)是塊，所有程序的基本結(jié)構(gòu)是塊，所有PL/SQLPL/SQL程序都是程序都是 由塊組成的。由塊組成

4、的。 p一個完整的一個完整的PL/SQLPL/SQL塊由三個部分組成：聲明部分、塊由三個部分組成：聲明部分、 執(zhí)行部分和異常處理部分。執(zhí)行部分和異常處理部分。 p描述：描述： DECLARE /*聲明部分，聲明變量、數(shù)據(jù)類型、異常、局部子程序等聲明部分，聲明變量、數(shù)據(jù)類型、異常、局部子程序等*/ BEGIN /*執(zhí)行部分，實現(xiàn)塊的功能執(zhí)行部分，實現(xiàn)塊的功能*/ EXCEPTION /*異常處理部分，處理程序執(zhí)行過程中產(chǎn)生的異常異常處理部分，處理程序執(zhí)行過程中產(chǎn)生的異常*/ END;/*程序結(jié)束標(biāo)記程序結(jié)束標(biāo)記*/ p參數(shù)說明：參數(shù)說明： lDECLARE部分和EXCEPTION部分是可選的，B

5、EGINEND 是必須的； l所有PL/SQL程序塊都以“END;”結(jié)束，因此，需要使 用斜杠（/）結(jié)尾以結(jié)束編輯狀態(tài)并執(zhí)行程序塊。 p例如，定義一個例如，定義一個PL/SQLPL/SQL程序塊，以輸出圖書的作者程序塊，以輸出圖書的作者 名：名： BOOKS_PUBorcl_dbs DECLARE 2 v_authorfname VARCHAR2(20); 3 v_authorlname VARCHAR2(40); 4 BEGIN 5 SELECT author_fname, author_lname 6 INTO v_authorfname, v_authorlname 7 FROM aut

6、hors 8 WHERE author_id = 810001; 9 DBMS_OUTPUT.PUT_LINE(v_authorlname| 10 v_authorfname); 11 END; 12 / 歐陽平 PL/SQL 過程已成功完成。 12.1.2 數(shù)據(jù)類型數(shù)據(jù)類型 pPL/SQLPL/SQL數(shù)據(jù)類型與數(shù)據(jù)類型與SQLSQL數(shù)據(jù)類型有很多相同之處，數(shù)據(jù)類型有很多相同之處， 除此之外，除此之外，PL/SQLPL/SQL還有一些特定的數(shù)據(jù)類型，這還有一些特定的數(shù)據(jù)類型，這 些數(shù)據(jù)類型如表些數(shù)據(jù)類型如表12-112-1所示。所示。 數(shù)據(jù)類型描述 BOOLEAN 布爾型，這是PL/SQL程序

7、塊中才能使用的數(shù)據(jù)類型，在數(shù)據(jù)庫 列上不能使用這種數(shù)據(jù)類型。BOOLEAN變量的取值為TRUE、 FALSE或NULL BINARY_INTEGER 帶符號整數(shù)，取值范圍為-231231。這種類型的數(shù)據(jù)是以2的補(bǔ) 碼二進(jìn)制格式存儲的，當(dāng)不需要在數(shù)據(jù)庫中存儲整個數(shù)值，但是 要在算術(shù)運算中使用這個數(shù)值時，建議使用這種數(shù)據(jù)類型 PLS_INTEGER 帶符號整數(shù)，取值范圍為-231231。它與BINARY_INTEGER類 似，都比NUMBER類型表示的范圍小，占用更少的內(nèi)存。當(dāng)使用 PLS_INTEGER值時，如果算法發(fā)生溢出，會觸發(fā)異常 SIMPLE_INTEGER 這 是 O r a c l

8、e D a t a b a s e 1 1 g 新 增 的 數(shù) 據(jù) 類 型 ， 它 是 BINARY_INTEGER的子類型，其取值范圍與BINARY_INTEGER 相同，但不能存儲NULL值。當(dāng)使用SIMPLE_INTEGER值時，如 果算法發(fā)生溢出，不會觸發(fā)異常，只會簡單地截斷結(jié)果 STRING 與VARCHAR2相同，主要用于存儲本地數(shù)據(jù)庫字符集的字符， 可以使用該類型與ANSI/ISO類型兼容 RECORD 記錄類型，類似于C語言中的結(jié)構(gòu)體，是一個包含一組其他類 型的復(fù)合類型。在PL/SQL中使用RECORD類型時，要先在聲明部 分定義RECORD結(jié)構(gòu)以及設(shè)置該類型的變量，然后在執(zhí)行

9、部分引 用該記錄變量或成員分量 REF CURSOR 游標(biāo)引用類型，類似于其他高級程序設(shè)計語言中的指針。REF CURSOR是指向一個行集的指針。利用引用類型變量可以使應(yīng)用 程序共享相同的存儲空間，提高程序的運行效率 %TYPE、 %ROWTYPE 嚴(yán)格來說，%TYPE和%ROWTYPE并不屬于一種數(shù)據(jù)類型，在 PL/SQL中聲明變量時使用它們來取其他變量或列的類型，其中， %TYPE類型用于隱式地將變量的數(shù)據(jù)類型聲明為與其他變量或數(shù) 據(jù)庫表中對應(yīng)列類型相同的數(shù)據(jù)類型；在聲明記錄類型變量時使 用%ROWTYPE把該變量聲明為與另一記錄變量或表的結(jié)構(gòu)相同 12.1.3 聲明變量與常量聲明變量與常

10、量 p定義變量或常量的語法為：定義變量或常量的語法為： variable_name CONSTANT datatype NOT NULL := | DEFAULT value; p參數(shù)說明：參數(shù)說明： lvariable_name：指出聲明的變量或常量名，它必須符 合Oracle標(biāo)識符命名規(guī)范； lCONSTANT：說明聲明的是常量，此時必須賦初值； lNOT NULL：要求聲明的變量非空，這時必須為變量賦初 值； l:=、DEFAULT：為變量或常量進(jìn)行初始化。 p例如，例如， BOOKS_PUBorcl_dbs DECLARE 2 v_booknum CONSTANT VARCHAR2(6

11、):= DB1001; 3 v_bookname VARCHAR2(60); 4 v_bookcategory CHAR(10); 5 v_author VARCHAR2(50); 6 v_publish VARCHAR2(50); 7 v_price books.bookprice%TYPE; 8 BEGIN . 該例中，聲明部分定義了一個常量v_booknum，并為該常量賦 初值；另外定義了五個變量，聲明最后一個變量時用到%TYPE。 l聲明變量時使用%TYPE指定數(shù)據(jù)類型，不僅可以確保所聲明 的變量與其他變量或列的數(shù)據(jù)類型完全相同，而且還可以保 證在其他變量或列的數(shù)據(jù)類型修改時，該變量的

12、數(shù)據(jù)類型能 夠隨之更改，從而減少PL/SQL代碼的維護(hù)工作量。 12.1.4 變量的賦值變量的賦值 p變量聲明時直接初始化。變量聲明時直接初始化。 如：如： BOOKS_PUBorcl_dbs DECLARE 2 v_booknum VARCHAR2(6) := DB1001; 3 v_bookcategory CHAR(10) DEFAULT 圖像處理圖像處理; . p在執(zhí)行部分用賦值操作符在執(zhí)行部分用賦值操作符:=:=為單個變量賦值。如為單個變量賦值。如： BOOKS_PUBorcl_dbs DECLARE 2 v_bookcategory CHAR(10); 3 BEGIN 4 v_bo

13、okcategory := 圖像處理圖像處理; . p在執(zhí)行部分，用在執(zhí)行部分，用SELECTSELECT、FETCHFETCH語句同時為多個語句同時為多個 變量賦值，這種方法又稱作集體賦值。如：變量賦值，這種方法又稱作集體賦值。如： BOOKS_PUBorcl_dbs DECLARE 2 v_booknum CONSTANT VARCHAR2(6):= DB1001; 3 v_bookname VARCHAR2(60); 4 v_price books.bookprice%TYPE; 5 BEGIN 6 SELECT bookname, bookprice INTO v_bookname,

14、7 v_price 8 FROM books 9 WHERE booknum = v_booknum; . 12.1.5 PL/SQL中的中的SQL語句語句 pPL/SQLPL/SQL在設(shè)計時采用了早期綁定變量的方式，也在設(shè)計時采用了早期綁定變量的方式，也 就是所有的數(shù)據(jù)庫對象在運行前都已經(jīng)被編譯器就是所有的數(shù)據(jù)庫對象在運行前都已經(jīng)被編譯器 所確定，因此，在所確定，因此，在PL/SQLPL/SQL中可以直接使用的中可以直接使用的SQLSQL語語 句只有句只有DMLDML（除了（除了EXPLAIN PLAINEXPLAIN PLAIN）和事務(wù)控制語）和事務(wù)控制語 句。句。 p使用使用SELECT

15、SELECT語句語句 l在PL/SQL中，可以使用SELECT INTO語句把單行查 詢結(jié)果返回到變量中。如果查詢語句返回多行數(shù)據(jù)， 則會產(chǎn)生TOO_MANY_ROWS異常。 l使用的SELECT INTO語句的語法格式為： SELECT select_list INTO variable_list | record_variable FROM table_name WHERE condition; p例如，下面代碼使用例如，下面代碼使用SELECTSELECTINTOINTO語句根據(jù)圖語句根據(jù)圖 書編號查詢圖書信息：書編號查詢圖書信息： BOOKS_PUBorcl_dbs DECLARE 2

16、 v_bookname books.bookname%TYPE; 3 v_author books.author%TYPE; 4 BEGIN 5 SELECT bookname, author INTO v_bookname, 6 v_author 7 FROM books 8 WHERE booknum=DB1007; 9 DBMS_OUTPUT.PUT_LINE(v_bookname | | v_author); 10 END; 11 / l注意：INTO子句后的變量用于接收查詢的結(jié)果，其變量的 個數(shù)、順序和類型應(yīng)該與查詢的目標(biāo)數(shù)據(jù)相匹配。 p使用使用DMLDML語句語句 lPL/SQL中

17、調(diào)用DML語句時，可以直接使用PL/SQL塊中聲 明的變量。 l例如， BOOKS_PUBorcl_dbs DECLARE 2 v_booknum VARCHAR2(6) := DB1005; 3 BEGIN 4 INSERT INTO books(bookid, booknum, bookname) 5 VALUES(books_seq.nextval, v_booknum, 6 數(shù)據(jù)庫原理基礎(chǔ)教程數(shù)據(jù)庫原理基礎(chǔ)教程); 7 UPDATE books SET bookprice = 55.2 8 WHERE booknum = v_booknum; 9 DELETE FROM books 1

18、0 WHERE booknum = v_booknum; 11 END; 12 / p事務(wù)控制事務(wù)控制 lOracle數(shù)據(jù)庫的事務(wù)控制語句包括COMMIT、ROLLBACK 和SAVEPOINT。 l執(zhí)行第一條DML語句時，一個新的事務(wù)就開始了，成功 執(zhí)行COMMIT（提交）或ROLLBACK（回滾）語句后事務(wù) 結(jié)束。 lROLLBACK語句有兩種形式： ROLLBACK; ROLLBACK TO SAVEPOINT savepoint_name; p在在PL/SQLPL/SQL程序塊中，可以在一系列程序塊中，可以在一系列DMLDML語句之后語句之后 使用事務(wù)控制語句來控制事務(wù)的執(zhí)行。例如，使

19、用事務(wù)控制語句來控制事務(wù)的執(zhí)行。例如， BOOKS_PUBorcl_dbs DECLARE 2 v_booknum VARCHAR2(6) := DB1005; 3 BEGIN 4 INSERT INTO books(bookid, booknum, bookname) 5 VALUES(books_seq.nextval, v_booknum, 6 數(shù)據(jù)庫原理基礎(chǔ)教程數(shù)據(jù)庫原理基礎(chǔ)教程); 7 SAVEPOINT sp1; 8 DELETE FROM books 9 WHERE booknum = v_booknum; 10 ROLLBACK TO SAVEPOINT sp1; 11 UPD

20、ATE books SET bookprice = 55.2 12 WHERE booknum = v_booknum; 13 COMMIT; 14 END; 15 / 12.2 PL/SQL控制結(jié)構(gòu)控制結(jié)構(gòu) pPL/SQLPL/SQL中中引入了能進(jìn)行邏輯控制的語句。這些語引入了能進(jìn)行邏輯控制的語句。這些語 句包括句包括： l條件控制 l循環(huán)控制 l跳轉(zhuǎn) l返回語句 12.2.1 條件結(jié)構(gòu)條件結(jié)構(gòu) pPL/SQLPL/SQL語言中的條件控制語句有語言中的條件控制語句有IFIF語句、語句、CASECASE語句語句 等，用它們可以實現(xiàn)條件選擇。等，用它們可以實現(xiàn)條件選擇。 pIFIF語句語句 l

21、IF條件包括IF、ELSIF、ELSE、THEN和END IF等關(guān)鍵字。語 法為： IF condition1 THEN statements1; ELSIF condition2 THEN statements2; . ELSE statements3; END IF; l注意：IF語句中的ELSIF子句和ELSE子句均為選項，并 且可以用多個ELSIF子句實現(xiàn)多條件分支判斷。在IF、 ELSIF和ELSE子句中可以嵌入其他IF條件語句。 l例如，統(tǒng)計orders表中圖書的銷售量，以判斷圖書的 銷售情況。 BOOKS_PUBorcl_dbs DECLARE 2 v_num INTEGER;

22、3 v_booknum VARCHAR2(6) := DB1004; 4 v_str VARCHAR2(60); 5 BEGIN 6 SELECT SUM(qty) INTO v_num 7 FROM orders 8 WHERE book_id = v_booknum; 9 IF v_num = 100000 THEN 10 v_str := v_booknum | 的銷量為： | v_num | 冊，暢銷的書！; 11 ELSIF v_num = 50000 THEN 12 v_str := v_booknum |的銷量為：| v_num | 冊，比較暢銷的書！; 13 ELSIF v_n

23、um = 5000 THEN 14 v_str := v_booknum | 的銷量為：| v_num |冊，銷售一般的書！; 15 ELSE 16 v_str := v_booknum | 的銷量為： | v_num | 冊，滯銷的書！; 17 END IF; 18 DBMS_OUTPUT.PUT_LINE(v_str); 19 END; 20 / pCASECASE語句語句 lPL/SQL中在的CASE語句有兩種形式： 簡單CASE語句：只進(jìn)行等值比較，用表達(dá)式確定返回值； 搜索CASE語句：可以進(jìn)行多種條件的比較，用條件確定返 回值。 l事實上，PL/SQL中的CASE語句可以實現(xiàn)IF語

24、句的所有功 能，并且其代碼結(jié)構(gòu)具有更好的閱讀性，因此，在多條 件分支判斷時，建議盡量使用CASE語句代替IF語句。 p簡單簡單CASECASE語句語句 l語法為： CASE search_expression WHEN expression1 THEN result1; WHEN expression2 THEN result2; . ELSE default_result; END CASE; l參數(shù)說明： search_expression：為待求值的表達(dá)式； expression1、expression2等：是要與 search_expression進(jìn)行比較的表達(dá)式，如果相等，則返 回相

25、應(yīng)WHEN子句后的result；如果search_expression與 所有WHEN子句中expression的值都不相等，則返回 default_result。 p例如，用簡單例如，用簡單CASECASE語句判斷顯示不同銷售情況的語句判斷顯示不同銷售情況的 書所對應(yīng)的銷售量書所對應(yīng)的銷售量。 BOOKS_PUBorcl_dbs DECLARE 2 v_salstatus VARCHAR2(10) := 3 v_str VARCHAR2(60); 4 BEGIN 5 v_str := v_salstatus | 書的售出冊數(shù)大于等于書的售出冊數(shù)大于等于; 6 CASE v_salstatus

26、 7 WHEN 暢銷暢銷 THEN v_str := v_str | 100000; 8 WHEN 比較暢銷比較暢銷 THEN v_str := v_str | 50000; 9 WHEN 銷售一般銷售一般 THEN v_str := v_str | 5000; 10 WHEN 滯銷滯銷 THEN v_str := v_salstatus | 11 書的售出冊數(shù)小于書的售出冊數(shù)小于5000; 12 ELSE v_str := 此狀態(tài)不對應(yīng)任何售出數(shù)量等級此狀態(tài)不對應(yīng)任何售出數(shù)量等級; 13 END CASE; 14 DBMS_OUTPUT.PUT_LINE(v_str); 15 END; 16

27、 / p搜索搜索CASECASE語句語句 l語法為： CASE WHEN condition1 THEN result1; WHEN condition2 THEN result2; . ELSE default_result; END CASE; l 在搜索型CASE語句中，CASE關(guān)鍵字后沒有表達(dá)式。此時， CASE語句對每一個WHEN子句中的條件進(jìn)行判斷，當(dāng)條件為 真時，執(zhí)行其后的語句；如果所有條件都不為真，則執(zhí)行 ELSE子句后的語句。 p例如，下面代碼實現(xiàn)與上面例如，下面代碼實現(xiàn)與上面IFIF語句例子相同的功能，語句例子相同的功能， 但這里用搜索但這里用搜索CASECASE語句實現(xiàn)：

28、語句實現(xiàn)： BOOKS_PUBorcl_dbs DECLARE 2 v_num INTEGER; 3 v_booknum VARCHAR2(6) := DB1004; 4 v_str VARCHAR2(60); 5 BEGIN 6 SELECT SUM(qty) INTO v_num 7 FROM orders 8 WHERE book_id = v_booknum; 9 CASE 10 WHEN v_num = 100000 THEN 11 v_str := v_booknum | 的銷量為： | v_num | 12 冊，暢銷！; 13 WHEN v_num = 50000 THEN 14

29、 v_str := v_booknum | 的銷量為： | v_num | 15 冊，比較暢銷！; 16 WHEN v_num = 5000 THEN 17 v_str := v_booknum | 的銷量為： | v_num | 18 冊，銷售一般！; 19 ELSE 20 v_str := v_booknum | 的銷量為： | v_num | 21 冊，滯銷！; 22 END CASE; 23 DBMS_OUTPUT.PUT_LINE(v_str); 24 END; 25 / 12.2.2 循環(huán)結(jié)構(gòu)循環(huán)結(jié)構(gòu) pPL/SQLPL/SQL中提供了三種循環(huán)結(jié)構(gòu)：簡單中提供了三種循環(huán)結(jié)構(gòu)：簡單L

30、OOPLOOP循環(huán)、循環(huán)、 WHILEWHILE循環(huán)和循環(huán)和FORFOR循環(huán)。循環(huán)。 p簡單簡單LOOPLOOP循環(huán)循環(huán) l此循環(huán)結(jié)構(gòu)是PL/SQL中最簡單的循環(huán)語句，語法為： LOOP statements; EXIT WHEN condition; END LOOP; l這種循環(huán)自身沒有循環(huán)條件判斷，所以循環(huán)體中一定 要包含EXIT語句，否則程序?qū)⑦M(jìn)入死循環(huán)。如果EXIT 語句使用了WHEN子句，則實現(xiàn)有條件的退出；否則， 就是無條件的退出。 l 例如，下面代碼用基本LOOP循環(huán)計算10以內(nèi)正整數(shù)的平方 和。 BOOKS_PUBorcl_dbs DECLARE 2 v_i INTEGER

31、:= 1; 3 v_sum INTEGER := 0; 4 BEGIN 5 LOOP 6 v_sum := v_sum + v_i * v_i; 7 v_i := v_i + 1; 8 EXIT WHEN v_i 10; 9 END LOOP; 10 DBMS_OUTPUT.PUT_LINE(10以內(nèi)的正整數(shù)平方和等于以內(nèi)的正整數(shù)平方和等于 | 11 v_sum); 12 END; 13 / pWHILEWHILE循環(huán)循環(huán) lWHILE循環(huán)在基本LOOP循環(huán)的基礎(chǔ)上添加了循環(huán)條件， 即先判斷循環(huán)條件是否滿足，只有滿足WHILE循環(huán)條件 才進(jìn)入循環(huán)體進(jìn)行操作。 lWHILE循環(huán)的語法格式為： W

32、HILE condition LOOP statements; END LOOP; l例如，下面代碼用WHILELOOP實現(xiàn)上例的操作。 BOOKS_PUBorcl_dbs DECLARE 2 v_i INTEGER := 1; 3 v_sum INTEGER := 0; 4 BEGIN 5 WHILE v_i DECLARE 2 v_sum INTEGER := 0; 3 BEGIN 4 FOR v_i IN 1.10 LOOP 5 v_sum := v_sum + v_i * v_i; 6 END LOOP; 7 DBMS_OUTPUT.PUT_LINE(10以內(nèi)的正整數(shù)平方和等于以內(nèi)的正

33、整數(shù)平方和等于 | 8 v_sum); 9 END; 10 / p使用使用FORFOR循環(huán)時應(yīng)該注意以下幾點：循環(huán)時應(yīng)該注意以下幾點： lFOR循環(huán)中，循環(huán)變量為PL/SQL隱含聲明的局部變量， 它只在循環(huán)語句內(nèi)有效，循環(huán)結(jié)束后即被釋放。所以， 用戶不必在PL/SQL塊的聲明區(qū)內(nèi)聲明循環(huán)變量。如果 用戶所聲明的變量與循環(huán)變量同名，則在FOR循環(huán)內(nèi)， 循環(huán)變量為局部變量，用戶變量為全局變量，這時局 部變量將隱藏全局變量。循環(huán)體內(nèi)語句需要參照用戶 變量時，必須使用變量的作用域名稱進(jìn)行限定； lFOR循環(huán)隱含聲明的循環(huán)變量為整數(shù)型，所以，循環(huán)語 句中的下界表達(dá)式和上界表達(dá)式必須為整數(shù)型表達(dá)式； l

34、FOR循環(huán)的執(zhí)行流程為：進(jìn)入FOR循環(huán)時，首先計算下 界表達(dá)式和上界表達(dá)式之值，并且在整個循環(huán)期間， 這兩個表達(dá)式的值只計算這一次； l對于正向FOR循環(huán)，把計算出來的下界表達(dá)式的值賦值 給循環(huán)變量，然后開始第一次循環(huán)條件測試，如果循環(huán) 變量值小于等于上界表達(dá)式之值，即循環(huán)條件為TRUE時， 執(zhí)行循環(huán)語句。執(zhí)行一次循環(huán)后，循環(huán)變量的值自動加 1，進(jìn)入下一次循環(huán)測試。在進(jìn)入下一次循環(huán)測試時， 不再計算下界表達(dá)式和上界表達(dá)式的值。之后如此循環(huán) 下去，直至循環(huán)條件為FALSE時才結(jié)束循環(huán)； l對于逆向FOR循環(huán)，計算下界表達(dá)式和上界表達(dá)式之值 后，把上界表達(dá)式的值賦值給循環(huán)變量，然后開始第一 次循環(huán)

35、條件測試，如果循環(huán)變量值大于等于下界表達(dá)式 之值，即循環(huán)條件為TRUE時，執(zhí)行循環(huán)語句，之后，循 環(huán)變量的值自動減1，進(jìn)入下一次循環(huán)條件測試，如此 循環(huán)下去，直至循環(huán)條件為FALSE時才結(jié)束循環(huán)。 12.2.3 GOTO語句和語句和NULL語句語句 p GOTOGOTO語句語句 l PL/SQL中的GOTO語句實現(xiàn)程序流程的強(qiáng)制跳轉(zhuǎn)。 l 例如，下面語句在基本LOOP循環(huán)內(nèi)使用GOTO語句結(jié)束循環(huán)： BOOKS_PUBorcl_dbs DECLARE 2 v_i INTEGER := 1; 3 v_sum INTEGER := 0; 4 BEGIN 5 LOOP 6 v_sum := v_su

36、m + v_i * v_i; 7 v_i := v_i + 1; 8 IF v_i 10 THEN 9 GOTO endofloop; 10 END IF; 11 END LOOP; 12 13 DBMS_OUTPUT.PUT_LINE(10以內(nèi)的正整數(shù)平方和等于以內(nèi)的正整數(shù)平方和等于 | v_sum); 14 END; 15 / l注意： GOTO語句不能轉(zhuǎn)移到任何非執(zhí)行語句前面； GOTO語句不能跳轉(zhuǎn)到條件語句、循環(huán)語句和子塊內(nèi)，也不 能從條件語句的一個子句（如IF子句）調(diào)轉(zhuǎn)到另外一個子 句（如ELSE子句）內(nèi)； 子程序內(nèi)的GOTO語句不能轉(zhuǎn)移到子程序外； 不能跳轉(zhuǎn)到異常錯誤處理程序執(zhí)行部

37、分的PL/SQL塊內(nèi)。 pNULLNULL語句語句 lNULL語句（空語句）只是一個占位符，它不實現(xiàn)任何具 體操作。 12.3 集合與記錄集合與記錄 pPL/SQLPL/SQL提供了聲明復(fù)合數(shù)據(jù)類型的功能。提供了聲明復(fù)合數(shù)據(jù)類型的功能。PL/SQLPL/SQL 的復(fù)合數(shù)據(jù)類型有兩種的復(fù)合數(shù)據(jù)類型有兩種： l記錄類型，是多個不同類型分量的集合，類似于高級語 言中的結(jié)構(gòu)體，對應(yīng)表中的一行數(shù)據(jù)或若干個字段的集 合。 l集合類型，是多個相同類型分量的集合，類似于高級語 言中的數(shù)組，對應(yīng)表中某一列的多個值的集合。 p集合類型又分為聯(lián)合數(shù)組（集合類型又分為聯(lián)合數(shù)組（Associative ArrayAss

38、ociative Array， 又稱作索引表，又稱作索引表，Index-By TableIndex-By Table）、嵌套表）、嵌套表 （Nested TableNested Table）和變長數(shù)組（）和變長數(shù)組（VarrayVarray）。）。 12.3.1 聯(lián)合數(shù)組聯(lián)合數(shù)組 p聯(lián)合數(shù)組類似于聯(lián)合數(shù)組類似于JavaJava中的哈希表，它是一個鍵值中的哈希表，它是一個鍵值 對集合。對集合。 p聯(lián)合數(shù)組中元素具有相同的類型，不同于高級語聯(lián)合數(shù)組中元素具有相同的類型，不同于高級語 言中的數(shù)組，聯(lián)合數(shù)組中的元素是稀疏分布且沒言中的數(shù)組，聯(lián)合數(shù)組中的元素是稀疏分布且沒 有固定上下限。有固定上下限。

39、p要創(chuàng)建一個聯(lián)合數(shù)組，先要定義一個聯(lián)合數(shù)組類要創(chuàng)建一個聯(lián)合數(shù)組，先要定義一個聯(lián)合數(shù)組類 型，然后再定義該類型的變量。型，然后再定義該類型的變量。 p定義聯(lián)合數(shù)組類型的語法如下：定義聯(lián)合數(shù)組類型的語法如下： TYPE assoc_array IS TABLE OF elem_type INDEX BY BINAR_INTEGER | PLS_INTEGER | VARCHAR2(n); p參數(shù)說明：參數(shù)說明： lassoc_array為聯(lián)合數(shù)組類型名； lelem_type為聯(lián)合數(shù)組中元素的類型，可以是基本類型、 用戶定義類型、通過%TYPE或%ROWTYPE獲取的類型； lINDEX BY指定

40、索引值的類型。 p定義了聯(lián)合數(shù)組類型后，就可以利用該類型定義定義了聯(lián)合數(shù)組類型后，就可以利用該類型定義 變量。變量。 p例如，例如，定義一個聯(lián)合數(shù)組類型，其中元素類型與定義一個聯(lián)合數(shù)組類型，其中元素類型與 booksbooks表的表的booknamebookname列類型一致，然后定義了該類列類型一致，然后定義了該類 型的變量：型的變量： TYPE bookname_array IS TABLE OF books.bookname%TYPE INDEX BY BINARY_INTEGER; v_bookname bookname_array; p聯(lián)合數(shù)組操作聯(lián)合數(shù)組操作 l元素賦值或引用 對于

41、聯(lián)合數(shù)組中的元素，通常利用索引值來引用或賦值。 其語法如下： assoc_array_name(index) 例如，下面代碼為聯(lián)合數(shù)組中的元素賦值，并引用輸出其 中的部分元素值： BOOKS_PUBorcl_dbs DECLARE 2 TYPE bookname_array IS TABLE OF 3 books_pub.books.bookname%TYPE 4 INDEX BY BINARY_INTEGER; 5 v_bookname1 bookname_array; 6 v_bookname2 bookname_array; 7 BEGIN 8 v_bookname1(-10) := 數(shù)

42、據(jù)結(jié)構(gòu)（C語言版）; 9 v_bookname1(1) := 數(shù)據(jù)庫原理; 10 v_bookname1(10) := Oracle 10g入門與提高; 11 FOR i IN 20.25 LOOP 12 v_bookname1(i) := 高等數(shù)學(xué) | TO_CHAR(i); 13 END LOOP; 14 v_bookname2 := v_bookname1; 15 DBMS_OUTPUT.PUT_LINE(v_bookname1(-10)： | 16 v_bookname1(-10); 17 DBMS_OUTPUT.PUT_LINE(v_bookname1(1)： | 18 v_book

43、name1(1); 19 DBMS_OUTPUT.PUT_LINE(v_bookname2(20)： | 20 v_bookname2(20); 21 END; 22 / p使用聯(lián)合數(shù)組時請注意：使用聯(lián)合數(shù)組時請注意： l聯(lián)合數(shù)組類型是PL/SQL特有的，即只能在PL/SQL中使用， 不能在SQL中使用； l當(dāng)為不存在的元素賦值時，系統(tǒng)會自動創(chuàng)建該元素； l當(dāng)引用不存在的元素時，會導(dǎo)致Oracle預(yù)定義異常 NO_DATA_FOUND。 l添加、刪除元素 添加元素的方法就是為一個不存在的元素賦值。 刪除聯(lián)合數(shù)組中的元素則需要調(diào)用集合方法delete。 delete方法三種調(diào)用形式： DELET

44、E：刪除集合內(nèi)的所有元素； DELETE(n)：刪除集合內(nèi)的第n個元素； DELETE(m, n)：刪除集合內(nèi)范圍在m到n之間的所有元素。 例如，刪除索引值為20和索引值從2224之間的元素： v_bookname1.DELETE(20); v_bookname1.DELETE(22,24); p集合方法集合方法 PL/SQL提供一些集合方法（如表12-2所示），在PL/SQL程 序塊中可以直接調(diào)用這些方法，實現(xiàn)相應(yīng)的集合操作。 方法功能 COUNT返回集合中元素的個數(shù)；對于嵌套表，則返回非空元素的個數(shù) DELETE刪除集合中所有的元素 DELETE(n)刪除集合中第n個元素 DELETE(n

45、, m)刪除集合中從第n個到第m個之間的元素 EXISTS(n) 判斷集合中第n個元素是否存在，存在則返回TRUE；否則返回 FALSE EXTEND在集合末尾添加一個元素，值為NULL EXTEND(n)在集合末尾添加n個元素，值均為NULL EXTEND(n, m)在集合末尾添加n個元素，它們的值均為第m個元素的值 FIRST 返回集合中第一個元素的索引值，也就是鍵值。如果集合為空，則返 回空值；對于嵌套表，其返回嵌套表中非空元素的最小索引值 LAST 返回集合中最后一個元素的索引值。如果集合為空，則返回空值；對 于嵌套表，其返回嵌套表中非空元素的最大索引值 LIMIT 返回集合中允許的元

46、素最大個數(shù)。對于嵌套表，如果沒有聲明大小， 則返回空 NEXT(n) 返回集合中比n大的下一個有效元素的索引值。如果n后面沒有元素， 則返回空 PRIOR(n) 返回集合中比n小的上一個有效元素索引值。如果n前面沒有元素，則 返回空 TRIM刪除集合末尾的一個元素 TRIM(n)刪除集合末尾的n個元素 p例如，例如，將圖書編號（作為聯(lián)合數(shù)組元素的鍵）和將圖書編號（作為聯(lián)合數(shù)組元素的鍵）和 圖書名稱（作為聯(lián)合數(shù)組元素的值）保存到一個圖書名稱（作為聯(lián)合數(shù)組元素的值）保存到一個 聯(lián)合數(shù)組中，然后調(diào)用集合方法輸出聯(lián)合數(shù)組中聯(lián)合數(shù)組中，然后調(diào)用集合方法輸出聯(lián)合數(shù)組中 元素的個數(shù)、最小索引值、最大索引值，

47、以及刪元素的個數(shù)、最小索引值、最大索引值，以及刪 除部分元素后剩余元素的個數(shù)。除部分元素后剩余元素的個數(shù)。 BOOKS_PUBorcl_dbs DECLARE 2 TYPE books_array IS TABLE OF VARCHAR2(60) 3 INDEX BY VARCHAR2(6); 4 v_books books_array; 5 v_idx VARCHAR2(6); 6 BEGIN 7 -初始化聯(lián)合數(shù)組元素 8 v_books(DM0001) := 數(shù)據(jù)結(jié)構(gòu)（C語言版）; 9 v_books(CM0002) := 數(shù)據(jù)庫原理; 10 v_books(AA0010) := Orac

48、le 10g入門與提高; 11 -取第一個元素的索引鍵 12 v_idx := v_books.FIRST; 13 -調(diào)用集合方法COUNT讀取集合中的元素個數(shù) 14 FOR i IN 1.v_books.COUNT LOOP 15 -顯示各個元素值 16 DBMS_OUTPUT.PUT_LINE(v_idx | : | 17 v_books(v_idx); 18 -用NEXT方法取當(dāng)前元素的下一個各個元素的索引鍵 19 v_idx := v_books.NEXT(v_idx); 20 END LOOP; 21 -刪除集合中的所有元素 22 v_books.DELETE; 23 DBMS_OU

49、TPUT.PUT_LINE( v_books集合內(nèi)的元素數(shù): | 24 v_books.COUNT); 25 END; 26 / 12.3.2 嵌套表嵌套表 p嵌套表是嵌套在另一個表中的表，可用于存儲元嵌套表是嵌套在另一個表中的表，可用于存儲元 素的無序集合。素的無序集合。 p嵌套表元素的索引值必須是有序的，從嵌套表元素的索引值必須是有序的，從1 1開始，沒開始，沒 有固定的上限。有固定的上限。 p嵌套表需要使用構(gòu)造函數(shù)進(jìn)行初始化，且嵌套表嵌套表需要使用構(gòu)造函數(shù)進(jìn)行初始化，且嵌套表 可以定義在可以定義在OracleOracle數(shù)據(jù)庫中。數(shù)據(jù)庫中。 p嵌套表的定義嵌套表的定義： TYPE nes

50、ted_tab IS TABLE OF elem_type NOT NULL; l參數(shù)說明： nested_tab為嵌套表類型名； elem_type為嵌套表中元素的類型，可以是基本類型、用 戶定義類型、通過%TYPE或%ROWTYPE獲取的類型。 p嵌套表的操作嵌套表的操作 l嵌套表必須進(jìn)行初始化才能被引用或賦值，否則會引發(fā) COLLECTION_IS_NULL異常。 l使用構(gòu)造函數(shù)初始化嵌套表，其中，參數(shù)個數(shù)不確定， 但類型要與嵌套表定義時指定的參數(shù)類型一致。 l嵌套表的初始化 例如，定義一個嵌套表類型，存儲圖書名稱，然后進(jìn) 行初始化操作。 BOOKS_PUBorcl_dbs DECLAR

51、E 2 TYPE bookname_nested IS TABLE OF 3 books.bookname%TYPE; 4 -只聲明一個嵌套表變量，未初始化 5 v_ntab1 bookname_nested; 6 -聲明一個嵌套表變量，并初始化 7 v_ntab2 bookname_nested := 8 bookname_nested(); 9 -聲明一個嵌套表變量，初始化時為三個元素賦值 10 v_ntab3 bookname_nested := 11 bookname_nested(高等數(shù)學(xué),數(shù)據(jù)結(jié)構(gòu), 12 數(shù)據(jù)庫原理); 13 BEGIN 14 IF v_ntab1 IS NULL

52、 THEN 15 DBMS_OUTPUT.PUT_LINE(v_ntab1為沒有初始化的嵌 套表！); 16 ELSE 17 -調(diào)用集合的COUNT方法查詢集合內(nèi)的元素數(shù)量 18 DBMS_OUTPUT.PUT_LINE(v_ntab1中的元素數(shù)： | v_ntab1.COUNT); 19 END IF; 20 IF v_ntab2 IS NULL THEN 21 DBMS_OUTPUT.PUT_LINE(v_ntab2為沒有初始化的嵌 套表！); 22 ELSIF v_ntab2.LAST IS NULL THEN 23 DBMS_OUTPUT.PUT_LINE(v_ntab2中的元素數(shù)：

53、| v_ntab2.COUNT); 24 END IF; 25 IF v_ntab3 IS NULL THEN 26 DBMS_OUTPUT.PUT_LINE(v_ntab3為沒有初始化的嵌 套表！); 27 ELSE 28 DBMS_OUTPUT.PUT_LINE(v_ntab3中的元素數(shù)： | v_ntab3.COUNT); 29 -依次讀取、顯示聯(lián)合表內(nèi)的各個元素 30 FOR I IN v_ntab3.FIRST.v_ntab3.LAST LOOP 31 DBMS_OUTPUT.PUT_LINE(v_ntab3( | i | )= | v_ntab3(i); 32 END LOOP;

54、33 END IF; 34 END; 35 / l嵌套表元素的添加和刪除 與聯(lián)合數(shù)組不同，嵌套表中元素的添加是通過調(diào)用EXTEND 方法實現(xiàn)。 嵌套表元素的刪除也使用DELETE方法，還可以使用TRIM方 法。 例如， BOOKS_PUBorcl_dbs DECLARE 2 TYPE bookname_nested IS TABLE OF 3 books.bookname%TYPE; 4 v_ntab3 bookname_nested := 5 bookname_nested(高等數(shù)學(xué),數(shù)據(jù)結(jié)構(gòu), 6 數(shù)據(jù)庫原理); 7 ind BINARY_INTEGER := 1; 8 BEGIN 9 -

55、擴(kuò)展一個元素，之后為該元素賦值 10 v_ntab3.EXTEND; 11 v_ntab3(v_ntab3.LAST) := 軟件工程; 12 -擴(kuò)展兩個元素，同時把嵌套表內(nèi)第三個元素的值復(fù)制給新擴(kuò)展的兩個元素 13 v_ntab3.EXTEND(2,3); 14 -各個元素稠密存儲，因此可以逐個引用嵌套表內(nèi)的元素 15 FOR i IN 1.v_ntab3.COUNT LOOP 16 DBMS_OUTPUT.PUT_LINE(v_ntab3(i); 17 END LOOP; 18 -刪除嵌套表最后的兩個元素 19 v_ntab3.TRIM(2); 20 DBMS_OUTPUT.PUT_LIN

56、E(TRIM后v_ntab3中的元素數(shù)： | 21 v_ntab3.COUNT); 20 -刪除嵌套表內(nèi)的1到3號元素 21 v_ntab3.DELETE(1,3); 22 DBMS_OUTPUT.PUT_LINE(DELETE后v_ntab3中的元素數(shù)： | 23 v_ntab3.COUNT); 24 v_ntab3(2) := 編譯原理; 25 DBMS_OUTPUT.PUT_LINE(賦值后，v_ntab3中的元素數(shù)： | 26 v_ntab3.COUNT); 27 -刪除后重新添加元素，導(dǎo)致嵌套表內(nèi)的元素稀疏存儲，因此只能用下面方法遍歷元素 28 ind := v_ntab3.FIRS

57、T; 29 FOR i IN 1.v_ntab3.COUNT LOOP 30 DBMS_OUTPUT.PUT_LINE(v_ntab3(ind); 31 ind := v_ntab3.NEXT(ind); 32 END LOOP; 33 END; 34 / 注意： TRIM方法刪除元素后，立即釋放其內(nèi)存，不再為這些元素 保留占位符，所以不能再為這些元素賦值。 調(diào)用不帶參數(shù)的DELETE方法刪除集合內(nèi)的所有元素后，元 素的存儲空間被立即釋放，因此不能再為元素賦值。 調(diào)用帶參數(shù)的DELETE語句刪除元素（即使是刪除所有元素） 后，PL/SQL為這些元素保留占位符，沒有釋放它們的存儲 空間，因此，仍

58、然可以重新為這些元素賦值來添加元素。 12.3.3 變長數(shù)組變長數(shù)組 p變長數(shù)組用于存儲有序的元素集合，它與嵌套表變長數(shù)組用于存儲有序的元素集合，它與嵌套表 很相似，但是，變長數(shù)組的索引值有固定的上限，很相似，但是，變長數(shù)組的索引值有固定的上限， 且不能刪除變長數(shù)組的中間元素。且不能刪除變長數(shù)組的中間元素。 p變長數(shù)組的定義變長數(shù)組的定義 l語法格式為： TYPE varray_name IS VARRAY | VARYING ARRAY(maxsize) OF elem_type NOT NULL; l參數(shù)說明： varray_name為變長數(shù)組類型名； maxsize為變長數(shù)組中元素的最大

59、數(shù)量； elem_type為變長數(shù)組元素的類型。 l例如，下面的語句首先定義一個變長數(shù)組類型，它最 多可包含5個元素，其元素類型與books表的bookname 列類型一致，然后用該類型聲明變量： TYPE bookname_varray IS VARRAY(5) OF books.bookname%TYPE; v_bookname bookname_varray; p變長數(shù)組的操作變長數(shù)組的操作 l變長數(shù)組的初始化 對變長數(shù)組變量進(jìn)行初始化就是調(diào)用與類型同名的構(gòu)造 函數(shù)，其中參數(shù)的類型要與類型定義的一致，而參數(shù) 的個數(shù)要少于或等于類型定義中指定的元素上限。 l例如，下面例子利用變長數(shù)組類型變

60、量存儲一 個部門中員工的名字： BOOKS_PUBorcl_dbs DECLARE 2 TYPE varray_name IS VARRAY(10) OF VARCHAR2(20); 3 v_name varray_name := varray_name(洪敏, 英子, 4 小強(qiáng)); 5 BEGIN 6 DBMS_OUTPUT.PUT_LINE(部門員工總數(shù)為： | 7 v_name.COUNT); 8 DBMS_OUTPUT.PUT_LINE(員工名字分別為：); 9 FOR i IN 1.v_name.COUNT LOOP 10 DBMS_OUTPUT.PUT_LINE(v_name(i)