五章使用PLSQLppt課件

1、第五章第五章運用 PL/SQL回想 同義詞是現(xiàn)有數(shù)據(jù)庫對象的別名 序列用于生成獨一、延續(xù)的序號 視圖是基于一個或多個表的虛擬表 索引是與表相關(guān)的一個可選構(gòu)造，用于提高 SQL 語句執(zhí)行的性能 索引類型有規(guī)范索引、獨一索引、反向鍵索引、位圖索引和基于函數(shù)的索引 索引組織表基于主鍵訪問數(shù)據(jù)目的 了解 PL/SQL 功能和特點 了解數(shù)據(jù)類型及其用法(重點) 了解邏輯比較 - 了解控制構(gòu)造(重點) 掌握錯誤處置(重點)專業(yè)英語 LOB大對象(Large OBject )PL/SQL 簡介 PL/SQL 是過程言語(Procedural Language)與構(gòu)造化查詢言語(SQL)結(jié)合而成的編程言語 P

2、L/SQL 是對 SQL 的擴展 支持多種數(shù)據(jù)類型，如大對象和集合類型，可運用條件和循環(huán)等控制構(gòu)造 可用于創(chuàng)建存儲過程、觸發(fā)器和程序包，給SQL語句的執(zhí)行添加程序邏輯 與 Oracle 效力器和 Oracle 工具嚴密集成，具備可移植性、靈敏性和平安性PL/SQL 的優(yōu)點 2-1 支持 SQL，在 PL/SQL 中可以運用： 數(shù)據(jù)支配命令 事務(wù)控制命令 游標控制 SQL 函數(shù)和 SQL 運算符 支持面向?qū)ο缶幊?(OOP) 可移植性，可運轉(zhuǎn)在任何操作系統(tǒng)和平臺上的Oralce 數(shù)據(jù)庫 更佳的性能，PL/SQL 經(jīng)過編譯執(zhí)行用戶將整個語句塊發(fā)送給 OracleOracleProcedureBeg

3、in ProcedureCall SQL Command EndPL/SQL 的優(yōu)點 2-2 PL/SQLSQL 與 SQL 嚴密集成，簡化數(shù)據(jù)處置。 支持一切 SQL 數(shù)據(jù)類型 支持 NULL 值 支持 %TYPE 和 %ROWTYPE 屬性類型 平安性，可以經(jīng)過存儲過程限制用戶對數(shù)據(jù)的訪問PL/SQL 的體系構(gòu)造 PL/SQL 引擎駐留在 Oracle 效力器中 該引擎接受 PL/SQL 塊并對其進展編譯執(zhí)行將PL/SQL 塊發(fā)送給 Oracle 效力器用戶用戶執(zhí)行過程語句引擎將 SQL 語句發(fā)送給SQL 語句執(zhí)行器Oracle 效力器效力器PL/SQL引擎引擎SQL 語句語句執(zhí)行器執(zhí)行器

4、過程語句過程語句執(zhí)行器執(zhí)行器執(zhí)行 SQL 語句將結(jié)果發(fā)送給用戶PL/SQL 塊簡介 PL/SQL 塊是構(gòu)成 PL/SQL 程序的根本單元 將邏輯上相關(guān)的聲明和語句組合在一同 PL/SQL 分為三個部分，聲明部分、可執(zhí)行部分和異常處置部分 DECLARE declarations BEGIN executable statements EXCEPTION handlers END;DECLARE qty_on_hand NUMBER(5);BEGIN SELECT quantity INTO qty_on_hand FROM Products WHERE product = 芭比娃娃 FOR U

5、PDATE 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(出錯:| SQLERRM); END;聲明部分定義變量、游標和自定義異常包含 SQL 和 PL/SQL 語句的可執(zhí)行部分指定

6、出現(xiàn)錯誤時需求執(zhí)行的操作變量和常量 1-2 PL/SQL 塊中可以運用變量和常量 在聲明部分聲明，運用前必需先聲明 聲明時必需指定數(shù)據(jù)類型，每行聲明一個標識符 在可執(zhí)行部分的 SQL 語句和過程語句中運用 聲明變量和常量的語法： 給變量賦值有兩種方法： 運用賦值語句 := 運用 SELECT INTO 語句變量和常量 2-2DECLARE icode VARCHAR2(6); p_catg VARCHAR2(20); p_rate NUMBER; c_rate CONSTANT NUMBER := 0.10;BEGIN . icode := i205; SELECT p_category, i

7、temrate * c_rate INTO p_catg, p_rate FROM itemfile WHERE itemcode = icode; .END;數(shù)據(jù)類型 PL/SQL 支持的內(nèi)置數(shù)據(jù)類型數(shù)據(jù)類型數(shù)據(jù)類型LOB類型標量類型屬性類型%ROWTYPE數(shù)字字符布爾型日期時間BFILEBLOBCLOBNCLOB%TYPE提供某個變量或數(shù)據(jù)庫表列的數(shù)據(jù)類型提供表示表中一行的記錄類型 存儲非構(gòu)造化數(shù)據(jù)塊數(shù)字數(shù)據(jù)類型 指定數(shù)值的存儲格式數(shù)字數(shù)據(jù)類型數(shù)字數(shù)據(jù)類型NUMBERBINARY_INTEGERPLS_INTEGERNATURALNATURALLNPOSITIVEPOSITIVENDECI

8、MALFLOATINTEGERREALSIGNTYPE存儲有符號整數(shù)，所需存儲空間少于NUMBER類型值存儲整數(shù)、實數(shù)和浮點數(shù)存儲有符號整數(shù)，可使算術(shù)計算快速而有效字符數(shù)據(jù)類型 字符數(shù)據(jù)類型包括： CHAR VARCHAR2 LONG RAW LONG RAW PL/SQL 的數(shù)據(jù)類型與 SQL數(shù)據(jù)類型的比較數(shù)據(jù)類型SQL類型PL/SQL類型CHAR1.20001.32767LONG1.2GB1.32760LONG RAW1.2GB1.32760RAW1.20001.32767VARCHAR21.40001.32767日期時間和布爾數(shù)據(jù)類型 日期時間類型 存儲日期和時間數(shù)據(jù) 常用的兩種日期時間

9、類型 DATE TIMESTAMP 布爾數(shù)據(jù)類型 此類別只需一種類型，即BOOLEAN類型 用于存儲邏輯值(TRUE、FALSE和NULL) 不能向數(shù)據(jù)庫中插入BOOLEAN數(shù)據(jù) 不能將列值保管到BOOLEAN變量中 只能對BOOLEAN變量執(zhí)行邏輯操作LOB 數(shù)據(jù)類型 2-1 用于存儲大文本、圖像、視頻剪輯和聲音剪輯等非構(gòu)造化數(shù)據(jù)。 LOB 數(shù)據(jù)類型可存儲最大 4GB的數(shù)據(jù)。 LOB 類型包括： BLOB 將大型二進制對象存儲在數(shù)據(jù)庫中 CLOB 將大型字符數(shù)據(jù)存儲在數(shù)據(jù)庫中 NCLOB 存儲大型UNICODE字符數(shù)據(jù) BFILE 將大型二進制對象存儲在操作系統(tǒng)文件中LOB 數(shù)據(jù)類型 2-2

10、 LOB 類型的數(shù)據(jù)庫列僅存儲定位符，該定位符指向大型對象的存儲位置 DBMS_LOB程序包用于支配 LOB 數(shù)據(jù)SET SERVEROUTPUT ONDECLARE clob_var CLOB; amount INTEGER; offset INTEGER; output_var VARCHAR2(100);BEGIN SELECT chapter_text INTO clob_var FROM my_book_text WHERE chapter_id=5; amount := 24; - 要讀取的字符數(shù) offset := 1; - 起始位置 DBMS_LOB.READ(clob_var

11、,amount,offset,output_var); DBMS_OUTPUT.PUT_LINE(output_var);END;/從表中選擇 CLOB 定位符到 clob_var變量中從CLOB數(shù)據(jù)中讀取24個字符存儲到 output_var 變量中顯示讀到的信息屬性類型 用于援用數(shù)據(jù)庫列的數(shù)據(jù)類型，以及表示表中一行的記錄類型 屬性類型有兩種： %TYPE - 援用變量和數(shù)據(jù)庫列的數(shù)據(jù)類型 %ROWTYPE - 提供表示表中一行的記錄類型 運用屬性類型的優(yōu)點： 不需求知道被援用的表列的詳細類型 假設(shè)被援用對象的數(shù)據(jù)類型發(fā)生改動，PL/SQL 變量的數(shù)據(jù)類型也隨之改動icode itemfil

12、e.itemcode%TYPE;emp_rec scott.emp%ROWTYPE; 布爾表達式的結(jié)果為TRUE、FALSE或NULL，通常由邏輯運算符AND、OR和NOT銜接 布爾表達式有三種類型： 數(shù)字布爾型 字符布爾型 日期布爾型邏輯比較 邏輯比較用于比較變量和常量的值，這些表達式稱為布爾表達式 布爾表達式由關(guān)系運算符與變量或常量組成關(guān)系運算符關(guān)系運算符說明說明=比較兩個變量是否相等，如果值相當(dāng)，則返回比較兩個變量是否相等，如果值相當(dāng)，則返回 True, !=比較兩個變量，如果不相等，則返回比較兩個變量，如果不相等，則返回 True比較兩個變量，檢查值比較兩個變量，檢查值 1 是否大于是

13、否大于 值值 2=比較兩個變量，檢查變量比較兩個變量，檢查變量 1 是否大于等于變量是否大于等于變量 2控制構(gòu)造PL/SQL 支持的流程控制構(gòu)造：條件控制IF 語句CASE 語句循環(huán)控制LOOP 循環(huán)WHILE 循環(huán)FOR 循環(huán)順序控制GOTO 語句NULL 語句條件控制 2-1 IF 語句根據(jù)條件執(zhí)行一系列語句，有三種方式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIFDECLARE icode VARCHAR2(4); irate NUMBER;BEGIN icode := i203; SELECT itemrate INTO irate FROM itemfil

14、e WHERE itemcode = icode; IF irate 200 THEN UPDATE itemfile SET itemrate = itemrate - 200 WHERE itemcode = icode; ELSE UPDATE itemfile SET itemrate = itemrate - 50 WHERE itemcode = icode; END IF; DBMS_OUTPUT.PUT_LINE(itemrate=| irate);END;條件控制 2-2BEGIN CASE &grade WHEN A THEN DBMS_OUTPUT.PUT_LIN

15、E(優(yōu)良優(yōu)良); WHEN B THEN DBMS_OUTPUT.PUT_LINE (優(yōu)秀優(yōu)秀); WHEN C THEN DBMS_OUTPUT.PUT_LINE (良好良好); WHEN D THEN DBMS_OUTPUT.PUT_LINE (普通普通); WHEN F THEN DBMS_OUTPUT.PUT_LINE (較差較差); ELSE DBMS_OUTPUT.PUT_LINE (沒有此成果沒有此成果); END CASE;END; CASE 語句用于根據(jù)單個變量或表達式與多個值進展比較 執(zhí)行 CASE 語句前，先計算選擇器的值循環(huán)控制 循環(huán)控制用于反復(fù)執(zhí)行一系列語句 循環(huán)控制

16、語句包括： LOOP、EXIT 和 EXIT WHEN 循環(huán)控制的三種類型： LOOP - 無條件循環(huán) WHILE - 根據(jù)條件循環(huán) FOR - 循環(huán)固定的次數(shù)LOOP sequence_of_statementsEND LOOP;WHILE condition LOOP sequence_of_statementsEND LOOP;FOR counter IN REVERSE value1.value2LOOP sequence_of_statementsEND LOOP;順序控制 順序控制用于按順序執(zhí)行語句 順序控制語句包括： GOTO 語句 - 無條件地轉(zhuǎn)到標簽指定的語句 NULL 語句

17、 - 什么也不做的空語句DECLARE qtyhand itemfile.qty_hand%type; relevel itemfile.re_level%type;BEGIN SELECT qty_hand,re_level INTO qtyhand,relevel FROM itemfile WHERE itemcode = i201; IF qtyhand relevel THEN GOTO updation; ELSE GOTO quit; END IF; UPDATE itemfile SET qty_hand = qty_hand + re_level WHERE itemcode

18、 = i201; NULL;END;動態(tài) SQL 動態(tài) SQL 是指在PL/SQL程序執(zhí)行時生成的 SQL 語句 編譯程序?qū)討B(tài) SQL 不做處置，而是在程序運轉(zhuǎn)時動態(tài)構(gòu)造語句、對語句進展語法分析并執(zhí)行 DDL 語句命令和會話控制語句不能在 PL/SQL 中直接運用，但是可以經(jīng)過動態(tài) SQL 來執(zhí)行 執(zhí)行動態(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

19、) := 7566; emp_rec emp%ROWTYPE;BEGIN EXECUTE IMMEDIATE CREATE TABLE bonus1 (id NUMBER, amt NUMBER); sql_stmt := SELECT * FROM emp WHERE empno = :id; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;END;錯誤處置 2-1 在運轉(zhuǎn)程序時出現(xiàn)的錯誤叫做異常 發(fā)生異常后，語句將停頓執(zhí)行，控制權(quán)轉(zhuǎn)移到 PL/SQL 塊的異常處置部分 異常有兩種類型： 預(yù)定義異常 - 當(dāng) PL/SQL 程序違反 O

20、racle 規(guī)那么或超越系統(tǒng)限制時隱式引發(fā) 用戶定義異常 - 用戶可以在 PL/SQL 塊的聲明部分定義異常，自定義的異常經(jīng)過 RAISE 語句顯式引發(fā)錯誤處置 2-2 處置預(yù)定義異常 處置用戶定義異常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 := &Category; IF category NOT IN (附件,頂蓋,備件) THEN RAISE invalidCATEGORY; ELSE DBMS_OUTPUT.PUT_LINE(您輸入的類別是| category); END IF;EXCEPTION WHEN invalidCATEGORY THEN DBMS_OUTPUT.PUT_LINE(無法識