oracle數(shù)據(jù)庫(kù)筆記a11.plsql開(kāi)發(fā)

1、PL/SQL 開(kāi)發(fā)目的：掌握 PL/SQL 編程技術(shù)及其基本應(yīng)用內(nèi)容：PL/SQL 語(yǔ)言基礎(chǔ)、異常處理機(jī)制、器過(guò)程、函數(shù)、程序包、觸發(fā)重點(diǎn)：過(guò)程、函數(shù)、觸發(fā)器難點(diǎn)：異常處理一、PL/SQL 入門Procedure Language & Structured Query Language，PL/SQL 是 Oracle 對(duì) SQL 語(yǔ)句的擴(kuò)展，增加了編程語(yǔ)言的特點(diǎn)。數(shù)據(jù)操作和查詢語(yǔ)句被包含在 PL/SQL 代碼的過(guò)程性單元中，經(jīng)過(guò)邏輯判斷、循環(huán)等操作完成復(fù)雜的功能或者計(jì)算。PL/SQL 的優(yōu)點(diǎn)：（1）使一組語(yǔ)句功能形成模塊化程序開(kāi)發(fā)（2）使用過(guò)程性語(yǔ)言控制程序結(jié)構(gòu)（3）可以對(duì)程序中的錯(cuò)誤進(jìn)行處理

2、（4）具有較好的可移植性（5）集成在數(shù)據(jù)庫(kù)中（6）有助于提高程序性能PL/SQL 運(yùn)行示意1.1、PL/SQL 基本結(jié)構(gòu)PL/SQL 程序代碼采取模塊式結(jié)構(gòu)，通常由三部分組成。、執(zhí)行主體和異常處理PL/SQL 程序塊基本結(jié)構(gòu)：PL/SQL 程序塊舉例：-創(chuàng)建表exception_log 來(lái)保存異常處理信息create table exception_log(aris_time datedefault sysdate,msgvarchar2(200);-PL/SQL 程序塊舉例：declare-一個(gè)名為 dn 的變量，其數(shù)據(jù)類型與Scott 方案中的 emp 表deptno字段相同（number

3、(2)）dn scott.emp.deptno%type;begin-查詢 emp 表中dn 之中為 7566 的員工所屬的部門，并把之保存到變量select deptnoo dn from emp where empno=7566;-更新該部門的全體員工工資，每人加薪 33 元update emp set sal=sal+33 where deptno=dn;commit; -提交事務(wù)-異常處理部分exceptionwhen no_data_found then -出現(xiàn) no_data_found 異常inserto exception_log(msg) values(未查找到指定員工);c

4、ommit;then -出現(xiàn)其他異常when othersrollback;inserto exception_log(msg) values(修改工資操作出現(xiàn)異常);commit;end;執(zhí)行完程序塊后，與 7566 員工相同部門即 20 號(hào)部門的員工工資全部加了 33 元。注意：如果修改 empno=8000：select deptnoo dn from emp where empno=8000;這時(shí)候會(huì)出現(xiàn)no_data_found 異常，查找表 exception_log 可以見(jiàn)到異常信息：PL/SQL 注釋：PL/SQL 注釋分為單行和多行注釋兩種：?jiǎn)涡凶⑨屢?開(kāi)頭，注釋到本行行尾；

5、多行注釋以/*開(kāi)頭，以*/結(jié)束，中間可以換行，行數(shù)不限。1.2、字符集與數(shù)據(jù)類型PL/SQL 程序由純文本格式的字符序列組成，其所允許使用的字符集內(nèi)容如下：數(shù)字：09大寫及小寫英文字母：AZ，az非顯示字符：制表符、空格和回車符數(shù)學(xué)符號(hào)：+，-，*，/，*，=分隔符：()，?，!，;，:，#，%，$，&，等PL/SQL 語(yǔ)言中字符拼寫大小寫不敏感（字符串常量?jī)?nèi)容除外）。PL/SQL 數(shù)據(jù)類型劃分：基本數(shù)據(jù)類型數(shù)據(jù)只能保持單一的值。復(fù)合數(shù)據(jù)類型數(shù)據(jù)可以封裝多個(gè)不同類型屬性，類似于面象編程語(yǔ)言中的類。常用基本數(shù)據(jù)類型：1.3、變量與常量語(yǔ)法：其中：Identifer：的變量或常量名；Constan

6、t：標(biāo)記常量；Daype：變量或常量的數(shù)據(jù)類型；Not null：變量或常量取值不能為空值；:=或 default：為當(dāng)前例子：的變量或常量指定初始值。set serveroutput on;-開(kāi)啟 DBMS 系統(tǒng)輸出功能，在命令窗口中執(zhí)行輸入結(jié)果為：在 PL/SQL 中變量或常量時(shí)，還允許通過(guò)方式指定其數(shù)據(jù)類型。%TYPE 用于指定當(dāng)前所的變量或常量的數(shù)據(jù)類型與指定方案的指定表的類型相同，其中方案名部分不是必須的，默認(rèn)為當(dāng)前用戶的方案名。例子：輸出結(jié)果如下：、PL/SQL 中執(zhí)行 SQL 指令、執(zhí)行 SELECT 指令PL/SQL 中可以使用 select 指令從數(shù)據(jù)庫(kù)中檢索數(shù)據(jù)，但必須添加

7、 子句將查詢結(jié)果保存到指定的變量中，除此之外可使用完整的 SELECT法。語(yǔ)法格式如下：O語(yǔ)例子：執(zhí)行結(jié)果：注意：PL/SQL 指令必須且只能返回一行查詢結(jié)果，否則會(huì)導(dǎo)致相應(yīng)的程序運(yùn)行異常：TOO_MANY_ROWS（查詢返回多行結(jié)果）和 NO_DATA_FOUND（查詢返回零行結(jié)果）。如下：1.4.2、執(zhí)行 DML 及事務(wù)控制指令在 PL/SQL 程序中執(zhí)行 DML 和事務(wù)控制指令格式與 SQL 中完全相同，其細(xì)微差別在于前者還可以使用其程序的變量或常量。例子：輸入結(jié)果：執(zhí)行事務(wù)控制指令：指令格式與 SQL 中完全相同，且使用其程序的變量或常量。例如：執(zhí)行結(jié)果如下，輸入 select * f

8、rom test2 可以查看到事務(wù)先返回到保存點(diǎn) b，以 1005 這條然后再提交沒(méi)有被提交：1.4.3、執(zhí)行 DDL 及 DCL 指令在 PL/SQL 代碼中不允許直接執(zhí)行 DDL 和 DCLEXECUTE IMMEDIATE 語(yǔ)句對(duì)其進(jìn)行調(diào)用。例子：指令，但可以通過(guò)二、PL/SQL 程序流程控制2.1、分支結(jié)構(gòu)根據(jù)約定條件的成立與否有選擇地執(zhí)行或跳過(guò)相應(yīng)的程序代碼，PL/SQL 使用常規(guī)的 IF 語(yǔ)句實(shí)現(xiàn)分支結(jié)構(gòu)。語(yǔ)法格式：其中，可選部分 elseif.then 子句可以出現(xiàn) 0 至多次，else 子句可以出現(xiàn)0-1 次。使用if 子句可以實(shí)現(xiàn)單路分支、雙路分支和多路分支結(jié)構(gòu)。例子：輸出結(jié)

9、果：注意：PL/SQL 中處理空值 NULL 的語(yǔ)則與 SQL 相同-任何包含空值的數(shù)學(xué)表達(dá)式，其值為 NULL；在字符表達(dá)式中出現(xiàn)的空值，會(huì)被作為空串（長(zhǎng)度為零的字符串處理）；使用IS NULL 操作符判斷空值。例如：、循環(huán)結(jié)構(gòu)用于在約定條件下重復(fù)執(zhí)行特定的代碼，PL/SQL 支持三種循環(huán)類型：簡(jiǎn)單循環(huán)、FOR 循環(huán)、WHILE 循環(huán)。、簡(jiǎn)單循環(huán)語(yǔ)法：其中，關(guān)鍵字 LOOP.END LOOP 之間的內(nèi)容為循環(huán)體；EXIT 指令用于退出循環(huán)；WHEN用于終止循環(huán)的條件，如果默認(rèn) WHEN 字句，則應(yīng)將EXIT 指令置于 IF 分支語(yǔ)句中。舉例：上面的程序塊等價(jià)于：2.2.2、FOR 循環(huán)語(yǔ)法：

10、Index 是循環(huán)計(jì)數(shù)器變量，相當(dāng)于其他高級(jí)語(yǔ)言中的整形循環(huán)迭代變量；PL/SQL 中 for 循環(huán)的計(jì)數(shù)器不需要例子：計(jì)算 1 到 100 的和，但只能在循環(huán)體內(nèi)。for(i=0;i=100;i+) N=i;Total=total+i;輸出結(jié)果為：2.2.3、WHILE 循環(huán)語(yǔ)法：condition 為循環(huán)條件，當(dāng)條件成立的時(shí)候執(zhí)行循環(huán)體中的代碼。例子：用 while 循環(huán)計(jì)算 1 到 100 的和While(i100) i=i+i; total=total+i;輸出結(jié)果為：2.3.4、使用控制循環(huán)PL/SQL 支持循環(huán)嵌套，且可使用例子：進(jìn)行循環(huán)控制。輸出結(jié)果：2.3、跳轉(zhuǎn)語(yǔ)句PL/SQL

11、 支持使用 GOTO 語(yǔ)句實(shí)現(xiàn)跳轉(zhuǎn)操作。語(yǔ)法：舉例：注意：goto 語(yǔ)句降低了程序代碼的可讀性和可使用了。性，現(xiàn)在已經(jīng)不提倡三、異常處理程序代碼運(yùn)行中所出現(xiàn)錯(cuò)誤稱為異常。常見(jiàn)異常類型：數(shù)據(jù)類型轉(zhuǎn)換錯(cuò)誤、算數(shù)除法的除零錯(cuò)誤、向表中時(shí)出現(xiàn)主鍵字重復(fù)。PL/SQL 異常處理機(jī)制：針對(duì)各種常見(jiàn)的異常狀況，預(yù)先定義的多種異常類型，指令執(zhí)行過(guò)程中如果出現(xiàn)異常，則系統(tǒng)會(huì)偵測(cè)到并自動(dòng)生成相應(yīng)類型的異常信息，然后查找并執(zhí)行相應(yīng)的處理代碼，如找不到則終止程序運(yùn)行并顯示必要的提示信息。3.1、系統(tǒng)預(yù)定義異常PL/SQL 中使用 EXCEPTINO 子句處理異常。例子：輸出結(jié)果：其中，SQLCODE 和 SQLERR

12、M 為 PL/SQL 系統(tǒng)函數(shù)，用于獲取異常代碼及異常描述信息。在之前的表中可以看到有異常的列和異常名稱。對(duì)于未命名的系統(tǒng)預(yù)定義異常，可以在 when others then 字句中根據(jù)異常代碼進(jìn)行區(qū)別處理；當(dāng)然對(duì)于已命名異常也可以用異常代碼處理，但是一般并不這樣用。例子：上面代碼分別運(yùn)行：inserto dept values(500,Account,Beijng);delete dept where deptno=20;-刪除子表 emp 中子表 dept 中的主前不能刪除主inserto dept values(null,Account,Beijing);-主鍵字段不能使用空值會(huì)出現(xiàn)下面

13、三個(gè)輸出：3.2、用戶自定義異常除使用 Oracle 系統(tǒng)預(yù)定義異常之外，用戶也可以根據(jù)具體的業(yè)務(wù)邏輯規(guī)則定義自己的異常名稱，當(dāng)業(yè)務(wù)邏輯規(guī)則時(shí)即可人為觸發(fā)該自定義異常，進(jìn)而轉(zhuǎn)入異常處理流程、執(zhí)行預(yù)先準(zhǔn)備好的處理代碼。用戶自定義異常名稱必須在 PL/SQL區(qū)定義：人為觸發(fā)異常使用 raise 指令或 raise_application_error()函數(shù)：例子 1：輸出結(jié)果：可以用 raise_application_error 人為觸發(fā)用戶自定義異常，但這種方式只能指定異常及其描述信息，而無(wú)法指定異常名稱，例如：四、過(guò)程之前接觸的 PL/SQL 程序塊均為塊，因?yàn)闆](méi)有名稱標(biāo)識(shí)，只能使用一次且不

14、會(huì)被在數(shù)據(jù)庫(kù)中。如果希望實(shí)現(xiàn)代碼重用，則應(yīng)對(duì) PL/SQL 程序塊進(jìn)行命名，并將之編譯后保存在數(shù)據(jù)庫(kù)服務(wù)器端，將來(lái)在客戶端程序中調(diào)用時(shí)只需指定其名稱即可，且因每次調(diào)用時(shí)不需要重新編譯而可以顯著提高運(yùn)行效率。命名后的PL/SQL 程序塊，相當(dāng)于其他高級(jí)編程語(yǔ)言中的函數(shù)或子過(guò)程，具體又可分為過(guò)程、函數(shù)、包和觸發(fā)器四種。4.1、創(chuàng)建過(guò)程過(guò)程（PROCEDURE）用于在數(shù)據(jù)庫(kù)中完成特定的操作或者任務(wù)，PL/SQL 中使用 DDL 指令 create procedure 創(chuàng)建或覆蓋定義創(chuàng)建語(yǔ)法：過(guò)程。Procedure_name：過(guò)程名稱，第一行相當(dāng)于其他語(yǔ)言的函數(shù)頭定義；IS 和 AS 之后的內(nèi)容相當(dāng)

15、一函數(shù)體；“OR REPLACE”可以省略，省略后只是創(chuàng)建新的過(guò)程而無(wú)法覆蓋現(xiàn)有的過(guò)程，過(guò)程的形式參數(shù)格式如下：例子 1:上述是一個(gè)名為 change_salary 的過(guò)程，類似于一個(gè)函數(shù)，調(diào)用時(shí)和工資數(shù)額，然后修改 emp 的相應(yīng)記通過(guò)參數(shù)接收從外界傳來(lái)的雇員錄。注意：定義無(wú)參的過(guò)程時(shí)，其過(guò)程的小括號(hào)必須省略；如果在過(guò)程中系統(tǒng)提示出現(xiàn)編譯錯(cuò)誤，可使用 show errors;指令查看詳細(xì)的錯(cuò)誤提示信息。執(zhí)行的語(yǔ)句，可以在 PL/SQL 左下角看到創(chuàng)建好的過(guò)程：4.2、調(diào)用過(guò)程過(guò)程創(chuàng)建之后即可對(duì)其進(jìn)行調(diào)用，可以使用 execute過(guò)程。語(yǔ)法如下：指令直接調(diào)用舉例：在命令窗口下執(zhí)行如下語(yǔ)句:也可

16、以在 PL/SQL 語(yǔ)句塊中調(diào)用過(guò)程，語(yǔ)法格式為：例子：在一個(gè)過(guò)程中調(diào)用另一個(gè)過(guò)程create or replace procedure test_procedure(a in number,b in number) isBEGINchange_salary(7369, 2500);END;4.3、參數(shù)類型及默認(rèn)值與高級(jí)編程語(yǔ)言中函數(shù)的形參類似，PL/SQ過(guò)程借助于過(guò)程參數(shù)實(shí)現(xiàn)其與調(diào)用環(huán)境之間的數(shù)據(jù)傳遞，并可以實(shí)現(xiàn)靈活的數(shù)據(jù)傳輸功能。二者的差別在于，高級(jí)編程語(yǔ)言中的函數(shù)的形參都是單向傳遞數(shù)據(jù)的（函數(shù)僅用于接收從外界調(diào)用環(huán)境傳入的數(shù)據(jù)，再通過(guò)函數(shù)返回值將處理結(jié)果返回/輸出到調(diào)用環(huán)境），而 PL/

17、SQL過(guò)程的參數(shù)卻分為 in、out 和 in out 共三種模式參數(shù)以實(shí)現(xiàn)靈活的數(shù)據(jù)傳輸功能。PL/SQL 參數(shù)類型：IN 模式、OUT 模式、IN OUT 模式。4.3.1、IN 參數(shù)IN 模式用于在過(guò)程調(diào)用時(shí)接收外界傳進(jìn)來(lái)的數(shù)據(jù)值，相當(dāng)于傳統(tǒng)函數(shù)的形參，使用最為廣泛，也是過(guò)程的默認(rèn)參數(shù)模式（定義時(shí) IN 關(guān)鍵字可以省略）。過(guò)程定義中形參數(shù)據(jù)類型不允許指定其字段長(zhǎng)度及數(shù)值精度。IN 模式參數(shù)被做為已賦值的常量對(duì)待，因而不可以在過(guò)程體中再對(duì)其進(jìn)行賦值操作。例子：-首先創(chuàng)建樣本表mytable1drop table mytable1;createtable mytable1(name varc

18、har2(10),age number(3);inserto mytable1 values(1,30);inserto mytable1 values(2,25);-創(chuàng)建過(guò)程mp1,有兩個(gè)參數(shù)newAge 和ncreateor replace procedure mp1(newAgein number,n invarchar)isbeginupdate mytable1 set age=newAgewheretrim(name)=n;commit;end;/此時(shí)mytable1 中的數(shù)據(jù)是：調(diào)用過(guò)程：現(xiàn)在 mytable1 中的數(shù)據(jù)是：4.3.2、OUT 模式OUT 模式參數(shù)的作用與 IN

19、模式參數(shù)相反，即用于將過(guò)程處理結(jié)果返回給調(diào)用環(huán)境。OUT 參數(shù)相當(dāng)于未賦值的變量，因要在過(guò)程執(zhí)行結(jié)束時(shí)將其值返回給調(diào)用者，故必須在過(guò)程中為其賦值。調(diào)用過(guò)程時(shí)與 OUT 模式參數(shù)相對(duì)應(yīng)的實(shí)參必須是變量，否則，使用常量或表達(dá)式的話，將因無(wú)法保存返回值而出錯(cuò)。例子：-創(chuàng)建一個(gè)過(guò)程mp3，其中v_name 是 in 參數(shù)，v_age 是 out 參數(shù)create or replaceproceduremp3(v_nameinvarchar,v_ageoutnumber)asbeginv_age:=777;end;/-測(cè)試declarev1 number;beginmp3(Tom,v1);-調(diào)用過(guò)程mp

20、3inserto mytable1values(Tom,v1+100);end;/上面的例子中，在調(diào)用過(guò)程mp3(Tom,v1);的時(shí)候，實(shí)參Tom的值被傳遞給mp3 過(guò)程的IN 模式參數(shù)v_name，在過(guò)程執(zhí)行完畢時(shí)，其out 模式參數(shù)v_age的值（777）被反向傳遞給相應(yīng)的調(diào)用環(huán)境（塊）的實(shí)參v1。執(zhí)行完后，表mytable1 的數(shù)據(jù)如下所示：4.3.3、IN OUT 模式IN OUT 模式參數(shù)是 IN 與 OUT 兩者的結(jié)合在過(guò)程調(diào)用之初接收調(diào)用環(huán)境傳遞進(jìn)來(lái)的參數(shù)值，在過(guò)程執(zhí)行期間該參數(shù)可作為普通局部變量使用（其值可以被修改），在過(guò)程執(zhí)行完畢之后再將其返回給調(diào)用環(huán)境。例子：-創(chuàng)建使用i

21、n out 模式參數(shù)的過(guò)程create or replace proceduremp5(v_nameinvarchar,v_ageinoutnumber)asbeginv_age:=v_age+888;end;-調(diào)用過(guò)程declarev1 number:=0;beginmp5(zhangsan,v1);inserto mytable1 values(zhangsan,v1);end;在過(guò)程 mp5 中，v_age 被設(shè)置為 in out 參數(shù)，那么調(diào)用mp5(zhangsan,v1);之初，實(shí)參zhangsan被傳遞給 mp5 的 IN 模式 v_age，在執(zhí)行完了之后，其out 模式參數(shù) v

22、_age 被反向傳遞給相應(yīng)的調(diào)用環(huán)境。這樣 v_age 就相當(dāng)于起到了一個(gè) IN 和OUT 的作用。所有調(diào)用過(guò)程完后，查詢表mytable1：4.3.4、參數(shù)默認(rèn)值對(duì)于 IN 模式參數(shù)，可以在例子：時(shí)為其指定參數(shù)默認(rèn)值。先創(chuàng)建一個(gè)過(guò)程：v_new_sal 被設(shè)置了默認(rèn)值 8888create or replace procedure mp6(v_empno number,v_new_salnumber:=8888)isv_nameemp.ename%TYPE;beginselectenameo v_name from emp where empno=v_empno;updateemp set

23、sal=v_new_sal where empno=v_empno;dbms_output.put_line(v_name|工資修改為|v_new_sal);commit;end;然后調(diào)用過(guò)程：execute mp6(7369,2500):因?yàn)檫@時(shí)候傳入的是兩個(gè)參數(shù)，那么第二個(gè)參數(shù)v_new_sal 為 2500如果調(diào)用過(guò)程：execute mp6(7369):此時(shí)因?yàn)榈诙€(gè)參數(shù)沒(méi)有值，所有默認(rèn)v_new_sal 為 8888所以，調(diào)用過(guò)程中如果給出的實(shí)參數(shù)目，運(yùn)行環(huán)境將按照從左到右的順序依次進(jìn)行參數(shù)值的傳遞，多余出來(lái)的形參（必須為 IN 模式，否則運(yùn)行出錯(cuò)）則使用其默認(rèn)值，因而在過(guò)程參數(shù)時(shí)即

24、應(yīng)考慮好其排列順序，建議排列順序如下：無(wú)默認(rèn)值的 IN 參數(shù)、OUT 默認(rèn)參數(shù)、IN OUT 模式參數(shù)，最后才是有默認(rèn)值的 IN 模式參數(shù)。4.4、刪除語(yǔ)法格式：過(guò)程例如：drop procedure change_salary;五、函數(shù)5.1、創(chuàng)建函數(shù)函數(shù)用來(lái)執(zhí)行復(fù)雜的計(jì)算，并返回計(jì)算的結(jié)果。 與其他高級(jí)編程語(yǔ)言中函數(shù)的不同之處在于：PL/SQL 函數(shù)必須有返回值。函數(shù)與過(guò)程的區(qū)別在于：PL/SQL 函數(shù)只能使用 IN 模式參數(shù)在調(diào)用時(shí)接收外界傳進(jìn)來(lái)的數(shù)據(jù)；而過(guò)程可以使用 IN、OUT 及 IN OUT 3 種模式實(shí)現(xiàn)雙向的數(shù)據(jù)傳遞；PL/SQL 函數(shù)必須有返回值，且只能有一個(gè)，以返回計(jì)算結(jié)

25、果給調(diào)用環(huán)境；而過(guò)程不允許有返回值，但可以使用 OUT 或 IN OUT 模式參數(shù)返回多個(gè)數(shù)值給調(diào)用環(huán)境；（3）函數(shù)不允許像過(guò)程一樣被獨(dú)立調(diào)用，而只能作為表達(dá)式的一部分來(lái)使用（其執(zhí)行后的返回值將被作為一個(gè)運(yùn)算數(shù)進(jìn)行表達(dá)式計(jì)算）；（4）PL/SQL 函數(shù)返回值類型必須是 Oracle 數(shù)據(jù)庫(kù)支持的數(shù)據(jù)類型，即不允許使用 PL/SQL 特有的數(shù)據(jù)類型；因?yàn)楹瘮?shù)已經(jīng)定義，不僅可以在PL/SQL 代碼被調(diào)用，還允許使用標(biāo)準(zhǔn) SQL 指令進(jìn)行調(diào)用，而過(guò)程則與之相反；PL/SQL 函數(shù)中，必須包括一個(gè)帶有數(shù)據(jù)類型的 return 子句，以指定其返回?cái)?shù)據(jù)的類型，且在其函數(shù)體（PL/SQL 程序塊）中至少應(yīng)包

26、括一個(gè)有效的 return 語(yǔ)句。PL/SQL 函數(shù)語(yǔ)法格式如下：例子：-創(chuàng)建函數(shù),函數(shù)名為tax,形參是v_value，類型是number，返回類型是number 類型create or replace function tax(v_value in number) return numberisbeginif v_value1000 thenreturn(v_value*0.1);elsereturn(v_value*0.5);end if;end tax; -結(jié)束函數(shù)創(chuàng)建執(zhí)行上述函數(shù)創(chuàng)建后，可以見(jiàn)到Function 中多了 TAX 函數(shù)：5.2、調(diào)用函數(shù)-在SQL 指令中直接調(diào)用函數(shù)se

27、lect empno,ename,tax(sal)應(yīng)納稅額 from emp;-在函數(shù)中調(diào)用函數(shù)create or replace function tax2(v_value in number)returnnumberisbeginreturn tax(v_value)+100;end;-在過(guò)程中調(diào)用函數(shù)CREATE OR REPLACEPROCEDURE mp6(v_empno NUMBER)ISv_sal emp.sal%TYPE;BEGINSELECT salO v_sal FROM emp WHERE empno =v_empno;UPDATE emp SET sal = sal -

28、 tax(v_sal) WHERE empno = v_empno;COMMIT;END;5.3、刪除函數(shù)使用 DDL 指令drop function 刪除函數(shù)，其語(yǔ)法格式：例如：DROP FUNCTION tax;六、包PL/SQL 程序包（Package ）用于將多個(gè)功能或用途相近的程序單元組合到一起，組成一個(gè)邏輯上的集合。如果需要，還可以在包內(nèi)若干個(gè)供包中的程序單元共用的全局變量。當(dāng)包中的任何程序單元被調(diào)用時(shí)，運(yùn)行環(huán)境都將加載整個(gè)程序包，以對(duì)包中其他程序單元的速度。6.1、創(chuàng)建包PL/SQL 程序包分為包頭和包體兩部分：包頭（Package Specification）也稱包描述、包規(guī)范

29、，用于對(duì)包中所有組成元素進(jìn)行簡(jiǎn)單。包體（Package Body）與包頭相對(duì)應(yīng)，包括相關(guān)過(guò)程及函數(shù)的具體定義信息。創(chuàng)建包頭的語(yǔ)法格式如下：-創(chuàng)建包頭create or replace package p1istax_rate1number:=0.1;tax_rate2number:=0.5;procedurechange_salary(v_empno in number,v_new_sal in number);function tax(v_value in number) return number;end p1;上述代碼定義了一個(gè)名為 p1 的包頭部，其中包含兩個(gè)全局變量(tax_rate

30、1、tax_rate2)的定義，以及一個(gè)明頭部信息。過(guò)程（change_salary）和一個(gè)函數(shù)(tax)的聲創(chuàng)建好了可以在 PL/SQL Developer 的左下角 Packages 看到創(chuàng)建好的信息：包體部分與包頭相對(duì)應(yīng)，包括相關(guān)過(guò)程及函數(shù)的具體定義信息，創(chuàng)建包體語(yǔ)法格式如下：舉例：-創(chuàng)建包體create or replace package body p1is-過(guò)程的具體定義信息procedure change_salary(v_empno in number,v_new_sal in number)isv_nameemp.ename%TYPE;beginselectenameo v_

31、name from emp where empno=v_empno;updateemp set sal=v_new_sal where empno=v_empno;dbms_output.put_line(v_name|工資修改完畢);commit;end change_salary;-函數(shù)的具體定義信息function tax(v_value in number) return numberisbeginif v_value1000 thenreturn (v_value*tax_rate1);elsereturn (v_value*tax_rate2);end if;end tax;end

32、 p1;執(zhí)行上面創(chuàng)建包體部分代碼，創(chuàng)建成功后查看到 Package bodies 增加了 P1：注意：在包體定義過(guò)程或函數(shù)時(shí)，不允許使用 createor replace關(guān)鍵字。為增加代碼的可讀性，可以在過(guò)程、函數(shù)或包體定義結(jié)束的 END 后加上相應(yīng)的程序單元名稱，例如“END change_salary;”一分清代碼層次。6.2、調(diào)用包在包外部調(diào)用其中所定義的過(guò)程及函數(shù)時(shí)需要在其前面加上.以做標(biāo)記，除此之外與調(diào)用普通過(guò)程和函數(shù)方法相同。-調(diào)用包 p1 中的change_salary 過(guò)程execute p1.change_salary(7369,5000);-調(diào)用包 p1 中的 tax 函數(shù)

33、select p1.tax(2000) from dual;注意：一直在使用的“dbms_output.put_line()”語(yǔ)句其實(shí)就是調(diào)用系統(tǒng)預(yù)定義程序包dbms_output 中的 put_line 函數(shù)。6.3、刪除包語(yǔ)法格式：舉例：DROP PACKAGE p1;七、觸發(fā)器觸發(fā)器（Triger）是與過(guò)程和函數(shù)類似的另一種保存在數(shù)據(jù)庫(kù)服務(wù)器端的 PL/SQL 程序單元，但其運(yùn)行機(jī)制上與函數(shù)存在明顯差別觸發(fā)器不能由用戶或應(yīng)用程序顯示調(diào)用，而是與特定的數(shù)據(jù)表、視圖操作或數(shù)據(jù)庫(kù)事件聯(lián)系到一起，當(dāng)進(jìn)行特定操作或發(fā)生特定事件時(shí)系統(tǒng)將自動(dòng)觸發(fā)并執(zhí)行相關(guān)觸發(fā)器程序代碼，這就類似于高級(jí)編程語(yǔ)言中的事件

34、處理器。觸發(fā)器的作用：安全性保護(hù)產(chǎn)生對(duì)數(shù)據(jù)值修改的審計(jì)提供更靈活的完整性校驗(yàn)規(guī)則（4）提供表數(shù)據(jù)的同步以及事件日志觸發(fā)器的事件可以是對(duì)數(shù)據(jù)庫(kù)表的DML 操作（insert、update 或delete）或視圖操作，也可以是某種系統(tǒng)事件或用戶事件，例如數(shù)據(jù)庫(kù)的啟動(dòng)和管理或 DDL 操作。按照其觸發(fā)事件的性質(zhì)進(jìn)行分類：DML 觸發(fā)器系統(tǒng)事件觸發(fā)器用戶事件觸發(fā)器DML 觸發(fā)器又可以細(xì)分為語(yǔ)句級(jí)觸發(fā)器、行級(jí)觸發(fā)器、instead of 觸發(fā)器三種。7.1、語(yǔ)句級(jí)觸發(fā)器DML 觸發(fā)器的操作對(duì)象為表或視圖，由 DML 語(yǔ)句觸發(fā)，按照 DML 操作類型又可分為insert、update 和delete 三種

35、，按照具體觸發(fā)時(shí)機(jī)可分為before（DML 語(yǔ)句執(zhí)行之前觸發(fā)），afte（r代 DML 語(yǔ)句的執(zhí)行)3 種。語(yǔ)句級(jí)觸發(fā)器在被一條 DMLDML 語(yǔ)句執(zhí)行之后觸發(fā)）和 instead of(替語(yǔ)句觸發(fā)后只執(zhí)行一次，而無(wú)論該 DML操作會(huì)涉及多少行。語(yǔ)法如下：Tigger_name：觸發(fā)器名；Timing：觸發(fā)的時(shí)機(jī)；Event：具體觸發(fā)的時(shí)間（DML 操作）類型；When 子句：確定觸發(fā)后是否執(zhí)行觸發(fā)器的執(zhí)行部分。例子 1：(注意：創(chuàng)建的語(yǔ)句最好在 SQL PLus 上面執(zhí)行，因?yàn)槌霈F(xiàn)一些編譯錯(cuò)誤，SQLPlus 中是不提示的，而在測(cè)試時(shí)候可能會(huì)發(fā)生觸發(fā)器無(wú)效且未通過(guò)重新驗(yàn)證。如果在 SQL

36、PLus 中運(yùn)行提示有編譯錯(cuò)誤，可以執(zhí)行 show error;查看錯(cuò)誤的位置)-創(chuàng)建語(yǔ)句級(jí)觸發(fā)器create or replace trigger secure_deptbefore insert on dept -每當(dāng)在 dept 表上執(zhí)行insert 操作之前觸發(fā)觸發(fā)器secure_deptbegin-如果時(shí)間是周末或工作日的下班時(shí)間，則拋出異常if(to_char(sysdate,day) in (六,日) or(to_char(sysdate,hh24:mi) not betn 08:00 and 18:00)then-自定義的一個(gè)異常raise_application_error(

37、-20001,你只能在工作時(shí)間對(duì)dept 表執(zhí)行操作);dbms_output.put_line(呵呵);end if;end;創(chuàng)建好觸發(fā)器后，可以看到PL/SQL Developer 左下角的 Triggers：上述觸發(fā)器創(chuàng)建之后，如果試圖在工作以外時(shí)間對(duì) dept 表執(zhí)行 insert 操作則會(huì)出錯(cuò)：inserto dept values(60,Account,);例子 2：create or replace trigger secure_dept-每當(dāng)在 dept 表上執(zhí)行insert,update 或delete 操作之前觸發(fā)觸發(fā)器secure_deptbefore insert or

38、 update or delete on deptdeclareuser_name varchar2(20);begin-如果時(shí)間是周末或工作日的下班時(shí)間，則拋出異常if(to_char(sysdate,day) in (五,六,日) or(to_char(sysdate,hh24:mi) not betn 08:00 and 18:00)thenif deleting then -如果對(duì)表 dept 執(zhí)行刪除操作raise_application_error(-20002,你只能在工作時(shí)間對(duì) dept 表執(zhí)行數(shù)據(jù)刪除操作);elsif inserting then -如果對(duì)表 dept 執(zhí)行

39、操作raise_application_error(-20003,你只能在工作時(shí)間對(duì) dept 表執(zhí)行數(shù)據(jù)操作);elsif updating(dname) then -如果對(duì)表 dept 中的 dname 字段執(zhí)行更新操作select usero user_name from dual; -從 dual 表中找出當(dāng)前用戶賦值給user_nameif(lower(user_name)scott) then -如果當(dāng)前用戶不是 scottraise_application_error(-20004,你無(wú)權(quán)在下班時(shí)間內(nèi)修改部門名稱信息);end if;elseraise_application_e

40、rror(-20005,只允許在工作時(shí)間執(zhí)行數(shù)據(jù)修改操作);end if;end if;end;/如下測(cè)試：1、以 sys 登錄 orcl，然后hr 操作可以在任何方案中更新表：2、在 scott 方案下更新表 dept，因?yàn)楫?dāng)前登錄用戶是 sys，所以會(huì)報(bào)一下異常：3、如果以scott 用戶登錄，那么就會(huì)修改成功：7.2、行級(jí)觸發(fā)器針對(duì)某一 DML 語(yǔ)句操作所影響到的多行數(shù)據(jù)的每一行都單獨(dú)執(zhí)行一次。對(duì)于觸發(fā)時(shí)機(jī)為 BEFORE 的行級(jí)觸發(fā)器，還可以在觸發(fā)器執(zhí)行代碼中相關(guān)的行字。語(yǔ)法格式：For each row 的意義是：在一次操作表的語(yǔ)句中，每操作成功一行就會(huì)觸發(fā)一次；不寫的話，表示是表級(jí)

41、觸發(fā)器，則無(wú)論操作多少行，都只觸發(fā)一次；:NEW 和:OLD 使用方法和意義，new 只出現(xiàn)在 insert 和 update 時(shí)，old只出現(xiàn)在update 和 delete 時(shí)。在 insert 時(shí) new 表示新的行數(shù)據(jù)，update時(shí) new 表示要替換的新數(shù)據(jù)、old 表示要被更改的原來(lái)的數(shù)據(jù)行，delete 時(shí)old 表示要被刪除的數(shù)據(jù)。例子：-創(chuàng)建行級(jí)觸發(fā)器create or replace trigger restrict_sal-每當(dāng)在 emp 表上進(jìn)行新或涉及 sal 字段更新操作時(shí)，會(huì)調(diào)用觸發(fā)器restrict_salbefore insert or update of

42、sal on empfor each rowbegin-如果雇員的職位不是經(jīng)理或者分析員，并且工資超過(guò) 5000，那么出錯(cuò)，拋出異常if not(:new.job in(MANAGER,YST) and :new.sal3000thenraise_application_error(-20202,員工不能賺到這么多薪水);end if;end;/測(cè)試：7.3、INSTEAD OF 觸發(fā)器ead of 觸發(fā)器主要用于視圖操作，其作用是替代觸發(fā)它的 DML 語(yǔ)句主要用于視圖操作。在表或視圖中定義 instead of 觸發(fā)器之后，針對(duì)該表或視圖的 DML 操作將不會(huì)被執(zhí)行而是轉(zhuǎn)向執(zhí)行instead of 觸發(fā)器中的代碼。復(fù)雜視圖中的數(shù)據(jù)通常來(lái)自多個(gè)基表或符合表達(dá)式，故無(wú)法對(duì)其進(jìn)行插入、更新及刪除等 DML 操作，但