oracle_TP5.ppt

1、第五章,使用 PL/SQL,2,回顧,同義詞是現(xiàn)有數(shù)據(jù)庫對象的別名 序列用于生成唯一、連續(xù)的序號 視圖是基于一個(gè)或多個(gè)表的虛擬表 索引是與表相關(guān)的一個(gè)可選結(jié)構(gòu)，用于提高 SQL 語句執(zhí)行的性能 索引類型有標(biāo)準(zhǔn)索引、唯一索引、反向鍵索引、位圖索引和基于函數(shù)的索引 索引組織表基于主鍵訪問數(shù)據(jù),3,目標(biāo),理解 PL/SQL 功能和特點(diǎn) 了解數(shù)據(jù)類型及其用法 理解邏輯比較 理解控制結(jié)構(gòu),4,PL/SQL 簡介,PL/SQL 是過程語言(Procedural Language)與結(jié)構(gòu)化查詢語言(SQL)結(jié)合而成的編程語言 PL/SQL 是對 SQL 的擴(kuò)展 支持多種數(shù)據(jù)類型，如大對象和集合類型，可使用條

2、件和循環(huán)等控制結(jié)構(gòu) 可用于創(chuàng)建存儲過程、觸發(fā)器和程序包，給SQL語句的執(zhí)行添加程序邏輯 與 Oracle 服務(wù)器和 Oracle 工具緊密集成，具備可移植性、靈活性和安全性,5,PL/SQL 的優(yōu)點(diǎn) 2-1,支持 SQL，在 PL/SQL 中可以使用： 數(shù)據(jù)操縱命令 事務(wù)控制命令 游標(biāo)控制 SQL 函數(shù)和 SQL 運(yùn)算符 支持面向?qū)ο缶幊?(OOP) 可移植性，可運(yùn)行在任何操作系統(tǒng)和平臺上的Oralce 數(shù)據(jù)庫 更佳的性能，PL/SQL 經(jīng)過編譯執(zhí)行,用戶將整個(gè)語句塊發(fā)送給 Oracle,6,PL/SQL 的優(yōu)點(diǎn) 2-2,與 SQL 緊密集成，簡化數(shù)據(jù)處理。 支持所有 SQL 數(shù)據(jù)類型 支持

3、NULL 值 支持 %TYPE 和 %ROWTYPE 屬性類型,安全性，可以通過存儲過程限制用戶對數(shù)據(jù)的訪問,7,PL/SQL 的體系結(jié)構(gòu),PL/SQL 引擎駐留在 Oracle 服務(wù)器中 該引擎接受 PL/SQL 塊并對其進(jìn)行編譯執(zhí)行,將PL/SQL 塊發(fā)送給 Oracle 服務(wù)器,用戶,執(zhí)行過程語句,引擎將 SQL 語句發(fā)送給SQL 語句執(zhí)行器,執(zhí)行 SQL 語句,將結(jié)果發(fā)送給用戶,8,PL/SQL 塊簡介,PL/SQL 塊是構(gòu)成 PL/SQL 程序的基本單元 將邏輯上相關(guān)的聲明和語句組合在一起 PL/SQL 分為三個(gè)部分，聲明部分、可執(zhí)行部分和異常處理部分 DECLARE declara

4、tions BEGIN executable statements EXCEPTION handlers END;,DECLARE qty_on_hand NUMBER(5); BEGIN SELECT quantity INTO qty_on_hand 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_reco

5、rd VALUES (已購買芭比娃娃, SYSDATE); END IF; COMMIT; EXCEPTION /* 異常處理語句 */ WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(出錯(cuò):| SQLERRM); END;,聲明部分定義變量、游標(biāo)和自定義異常,包含 SQL 和 PL/SQL 語句的可執(zhí)行部分,指定出現(xiàn)錯(cuò)誤時(shí)需要執(zhí)行的操作,9,PL/SQL 塊,它包括一組語句 PL/SQL 程序中的基本單元 將相關(guān)的聲明和語句進(jìn)行邏輯分組 聲明對于塊是局部的 在塊完成之后，將超出范圍,10,PL/SQL塊的結(jié)構(gòu),一個(gè)程序塊是由三個(gè)部分或節(jié)構(gòu)成的 聲明部分 可執(zhí)行部分

6、 異常處理部分,11,PL/SQL 結(jié)構(gòu)（續(xù)）,聲明部分 定義變量的可選部分 可執(zhí)行部分 構(gòu)成可執(zhí)行語句的必要部分 異常處理部分 構(gòu)成錯(cuò)誤處理代碼的可選部分,12,PL/SQL 結(jié)構(gòu)（續(xù)）,可以將語句在 PL/SQL 中分組 命名組稱為子程序 未命名組是匿名程序塊 程序塊可以包括稱為嵌套程序塊的多個(gè)塊 只允許在可執(zhí)行程序和異常處理部分進(jìn)行嵌套 最多允許 200 級嵌套,13,PL/SQL 結(jié)構(gòu)（續(xù)）,可以用任何編輯器編寫 PL/SQL程序，并將其保存為具有 .sql 擴(kuò)展名的文件 也可以使用 SQL*Plus中的“ED”命令創(chuàng)建 PL/SQL 程序文件 使用 “ ”命令執(zhí)行 PL/SQL 程序

7、文件,14,變量和常量 1-2,PL/SQL 塊中可以使用變量和常量 在聲明部分聲明，使用前必須先聲明 變量名不區(qū)分大小寫 多個(gè)變量不能在一個(gè)語句中聲明 聲明時(shí)必須指定數(shù)據(jù)類型，每行聲明一個(gè)標(biāo)識符 在可執(zhí)行部分的 SQL 語句和過程語句中使用 聲明變量和常量的語法：,identifier CONSTANT datatype NOT NULL := | DEFAULT expr;,給變量賦值有兩種方法： 使用賦值語句 := 使用 SELECT INTO 語句,15,變量和常量 2-2,DECLARE icode VARCHAR2(6); p_catg VARCHAR2(20); p_rate N

8、UMBER; 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;,16,使用 SELECT INTO 進(jìn)行賦值,還可以按如下方法使用 SELECT INTO 對變量賦值 SELECT INTO FROM WHERE ; 示例 SELECT first_fare INTO oldfare FROM fare WHERE route_cod

9、e = SAN-LOU;,17,使用 SELECT INTO 進(jìn)行賦值（續(xù)）,SELECT 語句執(zhí)行后，將出現(xiàn)下列情況之一 只檢索了一行 檢索了多行 不檢索任何行 僅當(dāng)它檢索一行時(shí)，SELECT 才成功操作 其他兩種情況將導(dǎo)致錯(cuò)誤并產(chǎn)生異常處理程序,18,數(shù)據(jù)類型,PL/SQL 支持的內(nèi)置數(shù)據(jù)類型,數(shù)據(jù)類型,LOB類型,標(biāo)量類型,屬性類型,%ROWTYPE,數(shù)字,字符,布爾型,日期時(shí)間,BFILE,BLOB,CLOB,NCLOB,%TYPE,提供某個(gè)變量或數(shù)據(jù)庫表列的數(shù)據(jù)類型,提供表示表中一行的記錄類型,存儲非結(jié)構(gòu)化數(shù)據(jù)塊,19,數(shù)字?jǐn)?shù)據(jù)類型,指定數(shù)值的存儲格式,數(shù)字?jǐn)?shù)據(jù)類型,NUMBER,B

10、INARY_INTEGER,PLS_INTEGER,NATURAL,NATURALLN,POSITIVE,POSITIVEN,DECIMAL,FLOAT,INTEGER,REAL,SIGNTYPE,存儲有符號整數(shù)，所需存儲空間少于NUMBER類型值,存儲整數(shù)、實(shí)數(shù)和浮點(diǎn)數(shù),存儲有符號整數(shù)，可使算術(shù)計(jì)算快速而有效,20,字符數(shù)據(jù)類型,字符數(shù)據(jù)類型包括： CHAR VARCHAR2 LONG RAW LONG RAW PL/SQL 的數(shù)據(jù)類型與 SQL數(shù)據(jù)類型的比較,21,數(shù)據(jù)類型：字符型（賦值）,Char數(shù)據(jù)： 當(dāng)賦值字符串長度小于聲明長度時(shí)，將在字符串的尾部填充空格 示例：Last_name

11、CHAR(10):=CHEN; Last_name 的值為CHEN （尾部有6個(gè)空格） 當(dāng)賦值字符串長度大于聲明長度時(shí)，將出錯(cuò) 示例：acronym CHAR(4); acronym := SPCA 將出錯(cuò) Varchar2數(shù)據(jù)： 當(dāng)賦值字符串長度小于定義長度時(shí)，將保持字符串原值，不進(jìn)行任何處理 示例：Last_name VARCHAR2(10):=CHEN ; Last_name 的值為CHEN 當(dāng)賦值字符串長度大于定義長度時(shí)，將出錯(cuò) 示例：acronym VARCHAR2 (4); acronym := SPCA 將出錯(cuò),22,日期時(shí)間和布爾數(shù)據(jù)類型,日期時(shí)間類型 存儲日期和時(shí)間數(shù)據(jù) 常用

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

13、24,LOB 數(shù)據(jù)類型 2-2,LOB 類型的數(shù)據(jù)庫列僅存儲定位符，該定位符指向大型對象的存儲位置 DBMS_LOB程序包用于操縱 LOB 數(shù)據(jù),SET SERVEROUTPUT ON DECLARE 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

14、_LOB.READ(clob_var,amount,offset,output_var); DBMS_OUTPUT.PUT_LINE(output_var); END; /,從表中選擇 CLOB 定位符 到 clob_var變量中,從CLOB數(shù)據(jù)中讀取24個(gè)字符存儲到 output_var 變量中,顯示讀到的信息,25,%TYPE 屬性,可以提供變量、常量或列的數(shù)據(jù)類型 示例 Oldfare fare.first_fare%TYPE; newfare oldfare%TYPE; 當(dāng)聲明一個(gè)引用數(shù)據(jù)庫中的列的變量時(shí)非常有用 不必明確知道列的實(shí)際數(shù)據(jù)類型 如果列定義發(fā)生變化，則變量的數(shù)據(jù)類型在運(yùn)行

15、時(shí)也將隨之更改,26,%ROWTYPE屬性,當(dāng)記錄變量具有與表或視圖中的行或從游標(biāo)獲取的行相同的結(jié)構(gòu)時(shí)有用 記錄中的字段具有與表/視圖中的列相同的名稱和數(shù)據(jù)類型 示例 emp_rec emp%ROWTYPE; 可以使用下列屬性引用特定字段 emp_rec.emp_num;,27,布爾表達(dá)式的結(jié)果為TRUE、FALSE或NULL，通常由邏輯運(yùn)算符AND、OR和NOT連接 布爾表達(dá)式有三種類型： 數(shù)字布爾型 字符布爾型 日期布爾型,邏輯比較,邏輯比較用于比較變量和常量的值，這些表達(dá)式稱為布爾表達(dá)式 布爾表達(dá)式由關(guān)系運(yùn)算符與變量或常量組成,28,字符型（比較規(guī)則）,如果運(yùn)算符兩邊都是CHAR類型，使

16、用填充空格規(guī)則，使兩邊長度都相等再比較 last_name1 CHAR(5) := BELLO; last_name2 CHAR(10) := BELLO ; 則Last_name1 = last_name2 如果運(yùn)算符兩邊都是VARCHAR2類型，使用不填充空格規(guī)則 last_name1 VARCHAR2(5) := BELLO; last_name2 VARCHAR2(10) := BELLO ; 則Last_name1 last_name2,29,字符型（比較規(guī)則）,如果一個(gè)是CHAR類型，另一個(gè)是VARCHAR2類型，使用不填充空格規(guī)則 last_name1 VARCHAR2(5) :

17、= BELLO; last_name2 CHAR(10) := BELLO; 則Last_name1 last_name2 所有的字符串常量的類型都是CHAR類型,30,控制結(jié)構(gòu),PL/SQL 支持的流程控制結(jié)構(gòu)： 條件控制 IF 語句 CASE 語句 循環(huán)控制 LOOP 循環(huán) WHILE 循環(huán) FOR 循環(huán) 順序控制 GOTO 語句 NULL 語句,31,條件控制 2-1,IF 語句根據(jù)條件執(zhí)行一系列語句，有三種形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF,DECLARE icode VARCHAR2(4); irate NUMBER; BEGIN icod

18、e := i203; SELECT itemrate INTO irate FROM itemfile 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;,32,條件控制 2

19、-2,BEGIN CASE ,CASE 語句用于根據(jù)單個(gè)變量或表達(dá)式與多個(gè)值進(jìn)行比較 執(zhí)行 CASE 語句前，先計(jì)算選擇器的值,33,循環(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ù),LOOP sequence_of_statements END LOOP;,WHILE condition LOOP sequence_of_statements END LOOP;,FOR counter IN REVERSE value1.value

20、2 LOOP sequence_of_statements END LOOP;,34,順序控制,順序控制用于按順序執(zhí)行語句 順序控制語句包括： GOTO 語句 - 無條件地轉(zhuǎn)到標(biāo)簽指定的語句 NULL 語句 - 什么也不做的空語句,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 UPDATE itemfile SET qty_hand = qty_hand + re_level WHERE itemco