1、第五章,使用 PL/SQL,2,目標(biāo),理解 PL/SQL 功能和特點(diǎn) 了解數(shù)據(jù)類型及其用法 理解邏輯比較 理解控制結(jié)構(gòu) 掌握錯(cuò)誤處理,3,PL/SQL 簡(jiǎn)介,PL/SQL 是過程語言(Procedural Language)與結(jié)構(gòu)化查詢語言(SQL)結(jié)合而成的編程語言 PL/SQL 是對(duì) SQL 的擴(kuò)展 支持多種數(shù)據(jù)類型，如大對(duì)象和集合類型，可使用條件和循環(huán)等控制結(jié)構(gòu) 可用于創(chuàng)建存儲(chǔ)過程、觸發(fā)器和程序包，給SQL語句的執(zhí)行添加程序邏輯 與 Oracle 服務(wù)器和 Oracle 工具緊密集成，具備可移植性、靈活性和安全性,4,PL/SQL 的優(yōu)點(diǎn) 2-1,支持 SQL，在 PL/SQL 中可以使

2、用： 數(shù)據(jù)操縱命令 事務(wù)控制命令 游標(biāo)控制 SQL 函數(shù)和 SQL 運(yùn)算符 支持面向?qū)ο缶幊?(OOP) 可移植性，可運(yùn)行在任何操作系統(tǒng)和平臺(tái)上的Oralce 數(shù)據(jù)庫 更佳的性能，PL/SQL 經(jīng)過編譯執(zhí)行,用戶將整個(gè)語句塊發(fā)送給 Oracle,5,PL/SQL 的優(yōu)點(diǎn) 2-2,與 SQL 緊密集成，簡(jiǎn)化數(shù)據(jù)處理。 支持所有 SQL 數(shù)據(jù)類型 支持 NULL 值 支持 %TYPE 和 %ROWTYPE 屬性類型,安全性，可以通過存儲(chǔ)過程限制用戶對(duì)數(shù)據(jù)的訪問,6,PL/SQL 的體系結(jié)構(gòu),PL/SQL 引擎駐留在 Oracle 服務(wù)器中 該引擎接受 PL/SQL 塊并對(duì)其進(jìn)行編譯執(zhí)行,將PL/S

3、QL 塊發(fā)送給 Oracle 服務(wù)器,用戶,執(zhí)行過程語句,引擎將 SQL 語句發(fā)送給SQL 語句執(zhí)行器,執(zhí)行 SQL 語句,將結(jié)果發(fā)送給用戶,7,PL/SQL 塊簡(jiǎn)介,PL/SQL 塊是構(gòu)成 PL/SQL 程序的基本單元 將邏輯上相關(guān)的聲明和語句組合在一起 PL/SQL 分為三個(gè)部分，聲明部分、可執(zhí)行部分和異常處理部分 DECLARE declarations BEGIN executable statements EXCEPTION handlers END;,DECLARE qty_on_hand NUMBER(5); BEGIN SELECT quantity INTO qty_on_h

4、and FROM Products WHERE product = 芭比娃娃 FOR UPDATE OF quantity; IF qty_on_hand 0 THEN UPDATE Products SET quantity = quantity + 1 WHERE product = 芭比娃娃; INSERT INTO purchase_record VALUES (已購買芭比娃娃, SYSDATE); END IF; COMMIT; EXCEPTION /* 異常處理語句 */ WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(出錯(cuò):| SQLERRM); EN

5、D;,聲明部分定義變量、游標(biāo)和自定義異常,包含 SQL 和 PL/SQL 語句的可執(zhí)行部分,指定出現(xiàn)錯(cuò)誤時(shí)需要執(zhí)行的操作,8,變量和常量 1-2,PL/SQL 塊中可以使用變量和常量 在聲明部分聲明，使用前必須先聲明 聲明時(shí)必須指定數(shù)據(jù)類型，每行聲明一個(gè)標(biāo)識(shí)符 在可執(zhí)行部分的 SQL 語句和過程語句中使用 聲明變量和常量的語法：,identifier CONSTANT datatype NOT NULL := | DEFAULT expr;,給變量賦值有兩種方法： 使用賦值語句 := 使用 SELECT INTO 語句,9,變量和常量 2-2,DECLARE icode VARCHAR2(6)

6、; p_catg VARCHAR2(20); p_rate NUMBER; c_rate CONSTANT NUMBER := 0.10; BEGIN . icode := i205; SELECT p_category, itemrate * c_rate INTO p_catg, p_rate FROM itemfile WHERE itemcode = icode; . END;,10,復(fù)合數(shù)據(jù)類型- TABLE 由兩個(gè)組件組成： 數(shù)據(jù)類型為BINARY_INTEGER的主鍵 數(shù)據(jù)類型為標(biāo)量的列(即為一個(gè)簡(jiǎn)單類型的列) Table類型沒有長度限制，可以動(dòng)態(tài)增長,結(jié)構(gòu),主鍵列 . 1Jon

7、es 2Smith 3Maduro . BINARY_INTEGER標(biāo)量,. TYPE name_table_type IS TABLE OF VARCHAR2(16) INDEX BY BINARY_INTEGER; v1_namename_table_type; v2_namename_table_type; .,11,屬性類型,用于引用數(shù)據(jù)庫列的數(shù)據(jù)類型，以及表示表中一行的記錄類型 屬性類型有兩種： %TYPE - 引用變量和數(shù)據(jù)庫列的數(shù)據(jù)類型 %ROWTYPE - 提供表示表中一行的記錄類型 使用屬性類型的優(yōu)點(diǎn)： 不需要知道被引用的表列的具體類型 如果被引用對(duì)象的數(shù)據(jù)類型發(fā)生改變，PL

8、/SQL 變量的數(shù)據(jù)類型也隨之改變,icode itemfile.itemcode%TYPE; emp_rec scott.emp%ROWTYPE;,12,布爾表達(dá)式的結(jié)果為TRUE、FALSE或NULL，通常由邏輯運(yùn)算符AND、OR和NOT連接 布爾表達(dá)式有三種類型： 數(shù)字布爾型 字符布爾型 日期布爾型,邏輯比較,邏輯比較用于比較變量和常量的值，這些表達(dá)式稱為布爾表達(dá)式 布爾表達(dá)式由關(guān)系運(yùn)算符與變量或常量組成,13,控制結(jié)構(gòu),PL/SQL 支持的流程控制結(jié)構(gòu)： 條件控制 IF 語句 CASE 語句 循環(huán)控制 LOOP 循環(huán) WHILE 循環(huán) FOR 循環(huán) 順序控制 GOTO 語句 NULL

9、語句,14,條件控制 2-1,IF 語句根據(jù)條件執(zhí)行一系列語句，有三種形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF,DECLARE icode VARCHAR2(4); irate NUMBER; BEGIN icode := i203; SELECT itemrate INTO irate FROM itemfile WHERE itemcode = icode; IF irate 200 THEN UPDATE itemfile SET itemrate = itemrate - 200 WHERE itemcode = icode; ELSE UPDAT

10、E itemfile SET itemrate = itemrate - 50 WHERE itemcode = icode; END IF; DBMS_OUTPUT.PUT_LINE(itemrate=| irate); END;,15,條件控制 2-2,BEGIN CASE ,CASE 語句用于根據(jù)單個(gè)變量或表達(dá)式與多個(gè)值進(jìn)行比較 執(zhí)行 CASE 語句前，先計(jì)算選擇器的值,16,循環(huán)控制,循環(huán)控制用于重復(fù)執(zhí)行一系列語句 循環(huán)控制語句包括： LOOP、EXIT 和 EXIT WHEN 循環(huán)控制的三種類型： LOOP - 無條件循環(huán) WHILE - 根據(jù)條件循環(huán) FOR - 循環(huán)固定的次數(shù),L

11、OOP sequence_of_statements END LOOP;,WHILE condition LOOP sequence_of_statements END LOOP;,FOR counter IN REVERSE value1.value2 LOOP sequence_of_statements END LOOP;,17,順序控制,順序控制用于按順序執(zhí)行語句 順序控制語句包括： GOTO 語句 - 無條件地轉(zhuǎn)到標(biāo)簽指定的語句 NULL 語句 - 什么也不做的空語句,DECLARE qtyhand itemfile.qty_hand%type; relevel itemfile.r

12、e_level%type; BEGIN SELECT qty_hand,re_level INTO qtyhand,relevel FROM itemfile WHERE itemcode = i201; IF qtyhand UPDATE itemfile SET qty_hand = qty_hand + re_level WHERE itemcode = i201; NULL; END;,18,動(dòng)態(tài) SQL,動(dòng)態(tài) SQL 是指在PL/SQL程序執(zhí)行時(shí)生成的 SQL 語句 編譯程序?qū)?dòng)態(tài) SQL 不做處理，而是在程序運(yùn)行時(shí)動(dòng)態(tài)構(gòu)造語句、對(duì)語句進(jìn)行語法分析并執(zhí)行 DDL 語句命令和會(huì)話控制語

13、句不能在 PL/SQL 中直接使用，但是可以通過動(dòng)態(tài) SQL 來執(zhí)行 執(zhí)行動(dòng)態(tài) SQL 的語法： EXECUTE IMMEDIATE dynamic_sql_string INTO define_variable_list USING bind_argument_list;,DECLARE sql_stmt VARCHAR2(200); emp_id NUMBER(4) := 7566; emp_rec emp%ROWTYPE; BEGIN EXECUTE IMMEDIATE CREATE TABLE bonus1 (id NUMBER, amt NUMBER); sql_stmt := SE

14、LECT * FROM emp WHERE empno = :id; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; END;,19,錯(cuò)誤處理 2-1,在運(yùn)行程序時(shí)出現(xiàn)的錯(cuò)誤叫做異常 發(fā)生異常后，語句將停止執(zhí)行，控制權(quán)轉(zhuǎn)移到 PL/SQL 塊的異常處理部分 異常有兩種類型： 預(yù)定義異常 - 當(dāng) PL/SQL 程序違反 Oracle 規(guī)則或超越系統(tǒng)限制時(shí)隱式引發(fā) 用戶定義異常 - 用戶可以在 PL/SQL 塊的聲明部分定義異常，自定義的異常通過 RAISE 語句顯式引發(fā),20,預(yù)定義異常,21,錯(cuò)誤處理 2-2,處理預(yù)定義異常 處理用戶

15、定義異常,DECLARE ordernum VARCHAR2(5); BEGIN SELECT orderno INTO ordernum FROM order_master; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE (返回多行); END;,DECLARE invalidCATEGORY EXCEPTION; category VARCHAR2(10); BEGIN category := ,22,RAISE_APPLICATION_ERROR 過程 用于創(chuàng)建用戶定義的錯(cuò)誤信息 可以在可執(zhí)行部分和異常處理部分使用 錯(cuò)誤編號(hào)

16、必須介于 20000 和 20999 之間 錯(cuò)誤消息的長度可長達(dá) 2048 個(gè)字節(jié) 引發(fā)應(yīng)用程序錯(cuò)誤的語法： RAISE_APPLICATION_ERROR(error_number, error_message);,引發(fā)應(yīng)用程序錯(cuò)誤,DECLARE rate itemfile.itemrate%TYPE; rate_exception EXCEPTION; BEGIN SELECT NVL(itemrate,0) INTO rate FROM itemfile WHERE itemcode = i207; IF rate = 0 THEN RAISE rate_exception; ELSE DBMS_OUTPUT.PUT_LINE(項(xiàng)費(fèi)率為： | rate); END IF; EXCEPTION WHEN rate_exception THEN RAISE_APPLICATION_ERROR(-20001, 未指定項(xiàng)費(fèi)率); END;,23,SQLCODE和SQLERRM 預(yù)定義函數(shù)SQLCODE和SQLERRM確定引發(fā)OTHERS處理器處理異常的到底是哪個(gè)錯(cuò)誤 SQLCODE - 返回當(dāng)前錯(cuò)誤代碼,對(duì)于用戶自定義異常，返回的是1 SQLER