StudentGuide9

1、第九章 數(shù)據(jù)庫觸發(fā)器和內(nèi)置程序包理 論 部 分第九章 數(shù)據(jù)庫觸發(fā)器和內(nèi)置程序包本章目標(biāo)：完成本章內(nèi)容以后，您將能夠： 討論數(shù)據(jù)庫觸發(fā)器 討論內(nèi)置程序包簡介通過本章的學(xué)習(xí)，可以深入了解數(shù)據(jù)庫觸發(fā)器。本章還涉及觸發(fā)器的各組成部分以及觸發(fā)器的各種類型。還闡明了各種DBMS程序包的用途。9.1 數(shù)據(jù)庫觸發(fā)器數(shù)據(jù)庫觸發(fā)器是一個(gè)對關(guān)聯(lián)表發(fā)出insert、update或delete語句時(shí)觸發(fā)的存儲過程。數(shù)據(jù)庫觸發(fā)器可用于以下目的： 自動(dòng)生成數(shù)據(jù)。 強(qiáng)制復(fù)雜的完整性約束條件。（例如，使用sysdate進(jìn)行檢查，使用另一個(gè)表中的數(shù)據(jù)進(jìn)行檢查）。 自定義復(fù)雜的安全權(quán)限。 維護(hù)復(fù)制的表。 審計(jì)數(shù)據(jù)修改。創(chuàng)建觸發(fā)器的

2、語法創(chuàng)建觸發(fā)器的語法如下所示。Create or replace Trigger before/after insert/update/delete on referencing old as old / new as newfor each statement/ for each row when pl/sql_block其中，Trigger_name是觸發(fā)器名稱，table-name是表名。數(shù)據(jù)庫觸發(fā)器還可以包含聲明部分和異常處理部分。9.1.1 觸發(fā)器的組成部分?jǐn)?shù)據(jù)庫觸發(fā)器包括三個(gè)部分，即觸發(fā)器語句、觸發(fā)器主體和觸發(fā)器限制。 觸發(fā)器語句觸發(fā)器語句指定諸如UPDATE、DELETE和INS

3、ERT之類的DML語句，它觸發(fā)觸發(fā)器主體。它還指定觸發(fā)器的關(guān)聯(lián)表。 觸發(fā)器主體觸發(fā)器主體是在發(fā)出觸發(fā)語句時(shí)執(zhí)行的PL/SQL塊。 觸發(fā)器限制可以使用WHEN子句實(shí)現(xiàn)對觸發(fā)器的限制，如創(chuàng)建觸發(fā)器的語法所示。它們可以包括在行觸發(fā)器的定義中，在其中，對于受觸發(fā)器影響的每個(gè)行，都計(jì)算WHEN子句中的條件。注釋：子查詢不能包括在WHEN子句中。9.1.2 觸發(fā)器的類型觸發(fā)器有以下兩種類型： 應(yīng)用程序觸發(fā)器 數(shù)據(jù)庫觸發(fā)器應(yīng)用程序觸發(fā)器每當(dāng)應(yīng)用程序內(nèi)發(fā)生特定DML事件時(shí)，都將隱式觸發(fā)此觸發(fā)器。例如，Oracle Forms Developer是一個(gè)大量使用觸發(fā)器的應(yīng)用程序。數(shù)據(jù)庫觸發(fā)器每當(dāng)在表、視圖、方案或

4、數(shù)據(jù)庫上發(fā)生特定DML事件時(shí)，都將隱式觸發(fā)觸發(fā)器。數(shù)據(jù)庫觸發(fā)器可以在表和視圖上進(jìn)行定義。INSTEAD OF觸發(fā)器用于定義要在視圖上執(zhí)行的DML操作。在數(shù)據(jù)庫或方案上，數(shù)據(jù)庫觸發(fā)器也可以充當(dāng)系統(tǒng)觸發(fā)器。創(chuàng)建DML觸發(fā)器編寫觸發(fā)器的第一步是確定觸發(fā)器各組件的值。以下是DML觸發(fā)器組件： 觸發(fā)器定時(shí) 觸發(fā)器事件 表名 觸發(fā)器類型 WHEN子句 觸發(fā)器主體表9.1描述重要組件的用法：表9.1：DML觸發(fā)器組件組件描述可能的值有觸發(fā)器定時(shí)定義觸發(fā)器相對于觸發(fā)事件的觸發(fā)時(shí)間BEFOREAFTERINSTEAD OF觸發(fā)器事件定義表或視圖上導(dǎo)致觸發(fā)器觸發(fā)的DML操作INSERTUPDATEDELETE觸發(fā)

5、器類型定義觸發(fā)器主體應(yīng)該執(zhí)行的次數(shù)Statement（語句）Row（行）觸發(fā)器主體定義觸發(fā)器應(yīng)該執(zhí)行的操作完整的 PL/SQL 塊在隨后的主題中，我們將討論觸發(fā)器定時(shí)。該定時(shí)定義觸發(fā)器主體應(yīng)該執(zhí)行的時(shí)間，以及執(zhí)行級別是Statement級還是Row級。 Before/After For each row/Statement（后者為默認(rèn)值）BEFORE/AFTER選項(xiàng)BEFORE/AFTER選項(xiàng)指定對于觸發(fā)語句應(yīng)該觸發(fā)觸發(fā)器主體的時(shí)間。如果用戶包括BEFORE選項(xiàng)，則Oracle在執(zhí)行觸發(fā)語句之前觸發(fā)觸發(fā)器。反之，如果使用AFTER，則Oracle在執(zhí)行觸發(fā)語句之后觸發(fā)觸發(fā)器。For Each

6、Row/Statement“create trigger”語法中的For each row選項(xiàng)指定觸發(fā)器在每行上觸發(fā)一次。默認(rèn)情況下，數(shù)據(jù)庫觸發(fā)器對每條語句觸發(fā)一次。使用以上選項(xiàng)的組合，可以為數(shù)據(jù)庫表指定12類觸發(fā)器。 BEFORE UPDATE for each row/statement AFTER UPDATE for each row/statement BEFORE DELETE for each row/statement AFTER DELETE for each row/statement BEFORE INSERT for each row/statement AFTER I

7、NSERT for each row/statement注釋：我們已經(jīng)了解DBMS_STANDARD，它提供與Oracle交互的語言工具。此程序包提供一個(gè)raise_application_error過程以發(fā)出用戶定義的錯(cuò)誤消息。語法如下。raise_application_error (error_number , 錯(cuò)誤消息);Error_number的范圍介于-20000到-20999之間，“錯(cuò)誤消息”可以是字符串。下列觸發(fā)器可防止未授權(quán)用戶更新“order_detail”表。僅當(dāng)用戶以管理員或DBA身份登錄時(shí)，該用戶才能更新此表。如果任何其他用戶試圖更新此表，則觸發(fā)器將被觸發(fā)并顯示消息“

8、不允許用戶更新該表”。示例1CREATE OR REPLACE TRIGGER ordersBEFORE UPDATE ON order_detailDECLAREvaruser VARCHAR2(25);BEGINIF updating THENSELECT username INTO varuser FROM user_users;IF varuser Administrator OR varuser DBA THEN RAISE_APPLICATION_ERROR(-20001,不允許用戶 |varuser| 更新該表);END IF;END IF;END;成功編譯后的輸出結(jié)果如下：觸發(fā)

9、器已創(chuàng)建。當(dāng)用戶JACK對此表發(fā)出update語句時(shí)，將出現(xiàn)以下錯(cuò)誤：SQL UPDATE order_detail SET qty_deld = 50;UPDATE order_detail SET qty_deld = 50 *ERROR位于第1行：ORA-20001:不允許用戶JACK更新該表ORA-06512:在JACK.ORDERS, line 7ORA-04088:觸發(fā)器JACK.ORDERS執(zhí)行過程中出錯(cuò)要增強(qiáng)數(shù)據(jù)庫的功能并防止從order_master表中意外刪除記錄，應(yīng)該顯示一條警告消息以提醒正在刪除記錄的人員。下列觸發(fā)器通過以下示例來說明此概念：示例2CREATE OR R

10、EPLACE TRIGGER orders_trigBEFORE DELETE ON order_masterBEGIN RAISE_APPLICATION_ERROR(-20001, 記錄已存在，不能刪除);END;/成功編譯后的程序輸出結(jié)果如下：觸發(fā)器已創(chuàng)建?？梢詮膐rder_master表中刪除行，來檢查上述觸發(fā)器的觸發(fā)。有兩個(gè)對等的變量，即:old和:new，它們保留數(shù)據(jù)庫中已更新的列的舊值和新值?？梢栽跀?shù)據(jù)庫觸發(fā)器中使用這些變量中的值以進(jìn)行操縱。以下示例可幫助您更好地理解這一點(diǎn)：示例3CREATE OR REPLACE TRIGGER newoldtrigBEFORE UPDATE

11、ON itemfile FOR EACH ROWBEGINIF :new.qty_hand UPDATE order_detail SET itemcode = i301;UPDATE order_detail SET itemcode = i301 *ERROR位于第1行:ORA-04091:表SCOTT.ORDER_DETAIL發(fā)生了變化，觸發(fā)器/函數(shù)不能讀ORA-06512:在SCOTT.UP_TRIG, line 2ORA-04088:觸發(fā)器SCOTT.UP_TRIG執(zhí)行過程中出錯(cuò)由于更新行時(shí)表處于變異狀態(tài)，觸發(fā)器觸發(fā)時(shí)Oracle將返回此錯(cuò)誤。如果從觸發(fā)器中刪除行“FOR EACH

12、ROW”，則觸發(fā)器將變?yōu)檎Z句觸發(fā)器，在觸發(fā)器觸發(fā)時(shí)表沒有處于變異狀態(tài)，觸發(fā)器就會輸出正確數(shù)據(jù)。9.1.4 INSTEAD OF觸發(fā)器Oracle引入了數(shù)據(jù)庫觸發(fā)器的概念，該觸發(fā)器在表上發(fā)生特定事件之前或之后觸發(fā)。這些觸發(fā)器可以在行級觸發(fā)，也可以在語句級觸發(fā)。對同一事件具有多個(gè)觸發(fā)器的能力提供了克服許多實(shí)際困難的功能。最明顯的是許多不同的應(yīng)用程序現(xiàn)在可以將自己的觸發(fā)器應(yīng)用于特定事件，而無需知道對方所需的邏輯。Oracle設(shè)計(jì)人員已經(jīng)使用觸發(fā)器取得了很大成效，尤其是在逆規(guī)范化的實(shí)現(xiàn)和復(fù)雜數(shù)據(jù)規(guī)則的強(qiáng)制方面。INSTEAD OF觸發(fā)器的工作原理 - 在Oracle中，新增了以下觸發(fā)器：INSTEAD

13、 OF觸發(fā)器。它們是在視圖上而不是在表上定義的觸發(fā)器。這樣的觸發(fā)器可以用于克服Oracle在任何視圖上設(shè)置的限制，即認(rèn)為視圖是不可更新的。在Oracle中，INSTEAD OF觸發(fā)器是在與視圖對應(yīng)的表的事件（INSERT、UPDATE和DELETE）上定義的。由于沒有規(guī)定在鎖定時(shí)間運(yùn)行的觸發(fā)器，因此鎖定必須是隱式的，或者應(yīng)用程序必須知道要鎖定哪些對象。盡管有少許不便，但是新的觸發(fā)器取消了對設(shè)計(jì)的主要限制。目前，設(shè)計(jì)人員能夠指定針對任何視圖定義執(zhí)行DML的邏輯，而且此邏輯只需實(shí)現(xiàn)一次。以下是對INSTEAD OF觸發(fā)器的一些限制： 它們只能在行級使用，而不能在語句級使用。 它們只能應(yīng)用于視圖，而

14、不能應(yīng)用于表。存在需要同時(shí)向兩個(gè)表中插入值的情況。這可以通過使用instead of觸發(fā)器來實(shí)現(xiàn)。在表order_master和order_detail上創(chuàng)建視圖以完成同時(shí)向兩個(gè)表中插入值的操作。為創(chuàng)建視圖和觸發(fā)器編寫的代碼如下所示：下例顯示用于將行插入到ord_view（它是兩個(gè)表order_master和order_detail上的聯(lián)接視圖）中的INSTEAD OF觸發(fā)器。按如下所示創(chuàng)建視圖，然后創(chuàng)建觸發(fā)器。示例5CREATE VIEW ord_view AS SELECT order_master.orderno,order_master.ostatus, order_detail.qt

15、y_deld, order_detail.qty_ord FROM order_master, order_detail WHERE order_master.orderno = order_detail.orderno/在成功編譯上述查詢后，輸出結(jié)果如下所示：視圖已建立。在創(chuàng)建視圖之后，可以按如下所示創(chuàng)建觸發(fā)器：CREATE OR REPLACE TRIGGER order_mast_insertINSTEAD OF INSERT ON ord_viewREFERENCING NEW AS n FOR EACH ROWDECLARECURSOR ecur IS SELECT * FROM o

16、rder_masterWHERE order_master.orderno = :n.orderno;CURSOR dcur IS SELECT * FROM order_detailWHERE order_detail.orderno = :n.orderno;a ecur%rowtype;b dcur%rowtype;BEGINOPEN ecur;OPEN dcur;FETCH ecur into a;FETCH dcur into b;IF dcur%notfound THENINSERT INTO order_master(orderno,ostatus)VALUES(:n.order

17、no, :n.ostatus);ELSEUPDATE order_master SET order_master.ostatus = :n.ostatusWHERE order_master.orderno = :n.orderno;END IF;IF ecur%notfound THENINSERT INTO order_detail(qty_ord,qty_deld,orderno)VALUES(:n.qty_ord, :n.qty_deld, :n.orderno);ELSEUPDATE order_detail SET order_detail.qty_ord = :n.qty_ord

18、,order_detail.qty_deld = :n.qty_deldWHERE order_detail.orderno = :n.orderno;END IF;CLOSE ecur;CLOSE dcur;END;/以上命令輸出結(jié)果如下，觸發(fā)器已創(chuàng)建。要檢查上述觸發(fā)器是否觸發(fā)正確，請?jiān)谝晥D上給出insert語句并檢查是否按所需要求更新或插入了行。嵌套表上的Instead of觸發(fā)器視圖中的嵌套列不能使用TABLE子句直接進(jìn)行修改，只能通過在嵌套表列上定義instead of觸發(fā)器來修改它。如果插入、更新或刪除了嵌套表元素，則嵌套表上的觸發(fā)器會觸發(fā)。嵌套表“dets”位于order_mast

19、er表中。要限制除嵌套表列之外的所有列，請?jiān)趏rder_master表上定義對象視圖。用戶希望通過對象視圖對嵌套表列執(zhí)行DML操作。要?jiǎng)?chuàng)建對象視圖，請執(zhí)行下列步驟：基于itemcode、qty_ord、qty_deld列創(chuàng)建名為ord_ty的類型，如下所示：示例6SQL CREATE OR REPLACE TYPE ord_ty AS OBJECT( itemcode VARCHAR2(5), qty_ord NUMBER(5),qty_deld NUMBER(5); /上述命令的輸出結(jié)果為：類型已創(chuàng)建?；趏rd_ty類型創(chuàng)建嵌套表，如下所示：示例7SQL CREATE TYPE ord_n

20、t AS TABLE OF ord_ty; / 上述命令的輸出結(jié)果為：類型已創(chuàng)建。以上創(chuàng)建的嵌套表類型可以用于創(chuàng)建包含dets嵌套列的order_master表。創(chuàng)建order_master表的語法如下所示；示例8DROP TABLE order_master;CREATE TABLE order_master (orderno VARCHAR2(5),odate DATE,vencode VARCHAR2 (5),dets ord_nt)NESTED TABLE dets STORE AS ord_nt_tab;上述命令的輸出結(jié)果為：表已創(chuàng)建。在上述語法中指定的ord_nt_tab表用于存儲

21、“dets”列的值?，F(xiàn)在，我們將看到向order_master表中插入一些值。此操作按如下方式執(zhí)行：示例9INSERT INTO order_master VALUES (o100,to_date(18-07-99, DD-MM-YY),v001,ord_nt( ord_ty(i100,10,5),ord_ty(i101,50,25),ord_ty(i102,5,5) );要在order_master表上創(chuàng)建對象視圖，必須基于order_master表創(chuàng)建對象類型。創(chuàng)建對象類型的語法如下所示：示例10CREATE TYPE order_type AS OBJECT(orderno VARCHA

22、R2(5),odate DATE, vencode VARCHAR2(5),dets ord_nt);/現(xiàn)在可以通過從order_master表中選擇所有列，基于order_type對象類型創(chuàng)建對象視圖，如下所示：示例11CREATE VIEW order_view OF order_type WITH OBJECT OID(orderno)AS SELECT * FROM order_master;上述命令的輸出結(jié)果為：視圖已建立?，F(xiàn)在，如果通過order_view向“dets”嵌套列中插入一些值，則Oracle將給出錯(cuò)誤“不能在嵌套表視圖列中執(zhí)行DML”，如下所示：示例12INSERT I

23、NTO TABLE(SELECT p.dets FROM order_view p WHEREp.orderno = o100)VALUES (i103,30,25);INSERT INTO TABLE(SELECT p.dets FROM order_view p WHERE*ERROR位于第1行:ORA-25015:不能在嵌套表視圖列中執(zhí)行DML為了通過order_view對嵌套表列執(zhí)行DML操作，Oracle提供了名為“INSTEAD OF觸發(fā)器”的功能?，F(xiàn)在，基于對象視圖創(chuàng)建instead of觸發(fā)器，如以下腳本所示：示例13CREATE OR REPLACE TRIGGER orde

24、r_trig INSTEAD OF INSERT ONNESTED TABLE dets OF order_view FOR EACH ROWBEGININSERT INTO TABLE(SELECT p.dets FROM order_master p WHEREp.orderno = :parent.orderno)VALUES (:new.itemcode,:new.qty_ord,:new.qty_deld);END; /上述腳本的輸出結(jié)果為：觸發(fā)器已創(chuàng)建。在此示例中，觸發(fā)器order_trig基于“dets”嵌套表。:new和:old屬性只能用于訪問當(dāng)前行的值，而不能用于訪問包含嵌套

25、表的父行的值。可以在instead of觸發(fā)器中使用:parent限定詞訪問父行的值。:parent限定詞只能在嵌套表觸發(fā)器內(nèi)使用，但不能在常規(guī)觸發(fā)器中使用?，F(xiàn)在，通過order_view向嵌套表列中插入一些值。語法如下：示例14INSERT INTO TABLE(SELECT p.dets FROM order_view p WHEREp.orderno = o100)VALUES (i103,30,25);以上給出的insert語句執(zhí)行instead of觸發(fā)器order_trig，該觸發(fā)器執(zhí)行在觸發(fā)器主體中給出的代碼，而不是執(zhí)行上述給出的insert語句。注釋：PARENT限定詞用于訪問

26、包含嵌套表列的父行的值。此限定詞只能在這些嵌套表觸發(fā)器內(nèi)使用。啟用和禁用觸發(fā)器觸發(fā)器可以處于啟用狀態(tài)，也可以處于禁用狀態(tài)。如果發(fā)出觸發(fā)語句，則處于啟用狀態(tài)的觸發(fā)器執(zhí)行觸發(fā)器主體。默認(rèn)情況下，觸發(fā)器處于啟用狀態(tài)。即使發(fā)出觸發(fā)語句，處于禁用狀態(tài)的觸發(fā)器也不執(zhí)行觸發(fā)器主體?？梢允褂孟铝姓Z法禁用觸發(fā)器：alter trigger disable;其中，Trigger_name是觸發(fā)器名。下列示例禁用一個(gè)名為order_mast_insert的觸發(fā)器。已經(jīng)在表order_master上創(chuàng)建了太多的觸發(fā)器。為了禁用向此表中插入值之前觸發(fā)的觸發(fā)器，請使用如下所示的命令：示例15SQL ALTER TRIGG

27、ER order_mast_insert DISABLE;可以發(fā)出以下語法禁用與此表關(guān)聯(lián)的所有觸發(fā)器：alter table disable all triggers;其中，table_name是表名。注釋：要啟用已禁用的觸發(fā)器，可以使用下列語法：alter trigger enable;其中，trigger name是觸發(fā)器名?？梢酝ㄟ^發(fā)出下列語法啟用為特定表定義的所有觸發(fā)器。alter table enable all triggers;其中，table_name是表名。刪除觸發(fā)器drop trigger命令用于從數(shù)據(jù)庫中刪除觸發(fā)器。語法如下：drop trigger ;其中，trigge

28、r_name是觸發(fā)器名。9.1.5 查看有關(guān)觸發(fā)器的信息下列數(shù)據(jù)字典視圖用于檢索有關(guān)觸發(fā)器的信息： USER_TRIGGERS ALL_TRIGGERS DBA_TRIGGERS新列BASE_OBJECT_TYPE指定觸發(fā)器是基于數(shù)據(jù)庫、方案、表還是視圖。如果基本對象不是表或視圖，則舊列TABLE_NAME為空。列ACTION_TYPE指定觸發(fā)器是調(diào)用類型觸發(fā)器還是PL/SQL觸發(fā)器。列TRIGGER_TYPE包括另外兩個(gè)值：BEFORE EVENT和AFTER EVENT，它們僅適用于系統(tǒng)事件。列TRIGGERING_EVENT包括所有系統(tǒng)事件和DML事件。9.2 內(nèi)置程序包Oracle提供

29、了許多內(nèi)置程序包。這些程序包使用戶可以訪問SQL功能（在PL/SQL中，這些功能通常是受到限制的）。它還可以用于擴(kuò)展數(shù)據(jù)庫的功能。在開發(fā)應(yīng)用程序時(shí)，可以利用這些程序包。數(shù)據(jù)庫用戶SYS擁有提供的所有程序包。它們是公共同義詞，任何用戶都可以訪問它們。除SYS之外，其他用戶調(diào)用程序包內(nèi)的過程和函數(shù)均需要具有程序包的EXECUTE權(quán)限。Oracle提供的程序包的部分列表如表9.2所示：表9.2：內(nèi)置程序包程序包名稱描述DBMS_STANDARD提供語言工具DBMS_ALERT支持?jǐn)?shù)據(jù)庫事件的異步通知DBMS_APPLICATION_INFO允許為跟蹤目的而注冊應(yīng)用程序DBMS_AQ和DBMS_AQA

30、DM管理Oracle Advanced Queuing選件DBMS_DEFER、DBMS_DEFER_SYS和DBMS_DEFER_QUERY允許構(gòu)建和管理延遲的遠(yuǎn)程過程調(diào)用DBMS_DDL某些DDL命令的PL/SQL等效項(xiàng)CALENDAR提供日歷維護(hù)功能DBMS_DESCRIBE描述存儲子程序DBMS_LOB操縱Oracle LOBDBMS_JOB允許對 PL/SQL 過程進(jìn)行調(diào)度DBMS_LOCK用戶定義的鎖DBMS_OUTPUT在SQL *Plus或服務(wù)器管理器中提供屏幕輸出DBMS_PIPE會話間異步通信DBMS_REFRESH 和 DBMS_SNAPSHOT允許管理快照DBMS_RE

31、PCAT、DBMS_REPCAT_AUTH和DBMS_REPCAT_ADMIN允許管理Oracle的對稱復(fù)制工具DBMS_ROWID允許從ROWID獲得信息DBMS_SESSIONAlter Session的PL/SQL等效項(xiàng)DBMS_SHARED_POOL控制共享池DBMS_SQL動(dòng)態(tài)PL/SQL和SQLDBMS_TRANSACTION事務(wù)處理管理命令DBMS_UTILITY其他實(shí)用過程UTL_FILE提供文件I/O上述程序包中的一些程序包將在以下進(jìn)行討論。9.2.1 DBMS_STANDARDDBMS_STANDARD程序包提供幫助應(yīng)用程序與Oracle進(jìn)行交互的語言工具。例如，一個(gè)名為r

32、aise_application_error的過程允許發(fā)出用戶定義的錯(cuò)誤消息。這就確保將錯(cuò)誤報(bào)告給應(yīng)用程序，并避免返回未處理的異常。9.2.2 DBMS_OUTPUTDBMS_OUTPUT程序包允許顯示PL/SQL塊和子程序的輸出結(jié)果，這樣更便于測試和調(diào)試它們。put_line過程將信息輸出到SGA中的緩沖區(qū)。通過調(diào)用過程get_line或通過設(shè)置SERVEROUTPUT ON，可以顯示信息。如果此程序包被禁用，則忽略對此程序包的調(diào)用。這樣，僅當(dāng)客戶機(jī)能夠處理信息時(shí)，這些例程才是活動(dòng)的。默認(rèn)緩沖區(qū)大小是2000字節(jié)。最小值是2000，最大值是1,000,000。 Enable這是第一個(gè)過程，它

33、只有一個(gè)輸入?yún)?shù)，即緩沖區(qū)大小(buffer_size)。enable過程用來啟用對put、put_line、new_line、get_line和get_lines的調(diào)用。緩沖區(qū)大小的輸入值用于設(shè)置默認(rèn)緩存的信息量。調(diào)用enable將清除任何已廢棄會話中緩存的數(shù)據(jù)。允許多次調(diào)用enable。SVRMGR或SQL *PLUS不需要enable過程。 DisableDisable沒有輸入或輸出。disable用于禁用對put、put_line、new_line、get_line和get_lines的所有調(diào)用。調(diào)用disable過程還可清除緩沖區(qū)中的任何剩余信息。此過程僅用于禁用PL/SQL腳本內(nèi)的

34、內(nèi)部輸入和輸出，SVRMGR或SQL *PLUS不需要它。 PutPut有一個(gè)輸入?yún)?shù)，即“a”，此參數(shù)被重載以接受varchar2、number和date值。Put用于在緩沖區(qū)中存儲一條信息。當(dāng)用get_line檢索number和date項(xiàng)時(shí)，to_char將使用默認(rèn)格式設(shè)置這些項(xiàng)的格式。 Put_linePut_line有一個(gè)輸入?yún)?shù)，即“a”，此參數(shù)被重載以接受varchar2、number和date值。如果這些值是要混合使用，則必須顯式使用to_char函數(shù)。put_line過程用于將一條信息存儲在緩沖區(qū)中，后接一個(gè)行結(jié)束標(biāo)記。當(dāng)用get_line檢索number和date項(xiàng)時(shí)，to_

35、char將使用默認(rèn)格式設(shè)置這些項(xiàng)的格式。 New_lineNew_line沒有參數(shù)。它用于向緩沖區(qū)中添加換行符。換行符充當(dāng)行結(jié)束標(biāo)記。Get_line返回由換行符分隔的行。對put_line或new_line的每次調(diào)用都將產(chǎn)生一行，該行可由get_line返回。 Get_lineGet_line有兩個(gè)輸出參數(shù)，即line和status。get_line過程用于返回已經(jīng)緩存的單個(gè)行。行將按調(diào)用put_line或new_line產(chǎn)生的行結(jié)束標(biāo)記或換行符進(jìn)行分隔。通過將所有項(xiàng)置于新行、將所有項(xiàng)均轉(zhuǎn)換為varchar2并將它們連接為單個(gè)行來構(gòu)造該行。如果客戶機(jī)在下一個(gè)put、put_line或new_

36、line之前未能檢索所有行，則將丟棄未返回的行。輸出參數(shù)具有以下定義：l Line - 它將容納長達(dá)255個(gè)字節(jié)的行。l Status - 在成功完成調(diào)用時(shí)，它將為返回0。1表示沒有行可以返回。 Get_linesGet_lines有一個(gè)輸入?yún)?shù)。輸出結(jié)果實(shí)際上是對應(yīng)于varchar2的標(biāo)量數(shù)組的PL/SQL類型。單個(gè)輸入numlines也兼做輸出，因此不論過程要獲取的行數(shù)（輸入），它均輸出緩沖區(qū)中的行數(shù)。行將按調(diào)用put_line或new_line產(chǎn)生的行結(jié)束標(biāo)記或換行符進(jìn)行分隔。通過將所有項(xiàng)置于新行、將所有項(xiàng)均轉(zhuǎn)換為varchar2并將它們連接為單個(gè)行來構(gòu)造行。執(zhí)行g(shù)et_lines后，客

37、戶機(jī)應(yīng)該繼續(xù)檢索所有行，因?yàn)橄乱粋€(gè)put、put_line或new_line將首先清除緩沖區(qū)中任何剩余數(shù)據(jù)。如果客戶機(jī)在獲取信息時(shí)被中斷，則出現(xiàn)這種情況，將沒有任何有用的信息留下。get_lines過程具有以下輸入?yún)?shù)：l Numlines - 這是調(diào)用程序準(zhǔn)備接受的最大行數(shù)。Get_lines過程返回的行數(shù)不會超過此行數(shù)。get_lines過程具有兩個(gè)輸出，其中的一個(gè)實(shí)際上是標(biāo)量數(shù)組：l Lines - 此數(shù)組將容納返回的行，每行可長達(dá)255個(gè)字節(jié)。此數(shù)組的下標(biāo)從0開始，然后順序增加。對于3GL主機(jī)程序，數(shù)組的下標(biāo)從該語言約定的值開始。l Numlines - 這將是實(shí)際返回的行數(shù)。如果它小

38、于傳入的值，則沒有更多的行。9.2.3 DBMS_LOBDBMS_LOB程序包是Oracle中新增的。此程序包添加用于處理大型對象的實(shí)用過程和函數(shù)。在Oracle中，LOB分為以下幾種類型：BLOB（二進(jìn)制大對象）、CLOB（字符大對象）和BFILE（外部存儲的二進(jìn)制文件 - 只讀）。除了BFILE類型外，Oracle SQL支持LOB的定義、創(chuàng)建、刪除和完全更新。大部分LOB操作由此程序包提供。適用于此程序包中函數(shù)和過程的規(guī)格說明的一組規(guī)則如下： 對BLOB和BFILE執(zhí)行操作的例程的LENGTH和OFFSET參數(shù)是用字節(jié)數(shù)指定的。 對CLOB執(zhí)行操作的例程的LENGTH和OFFSET參數(shù)是

39、用字符數(shù)指定的。 如果在為參數(shù)指定值時(shí)不遵循以下限制，則函數(shù)/過程將引發(fā)INVALID_ARGVAL異常。l 只允許使用相對于LOB數(shù)據(jù)起始處的正的、絕對偏移量。不允許使用相對于LOB結(jié)尾處的負(fù)偏移量。l 對于代表大小和位置量的參數(shù)（如AMOUNT、OFFSET、NEWLEN、NTH等），只允許使用正的非零值。l 在任何DBMS_LOB過程或函數(shù)中，OFFSET、AMOUNT、NEWLEN、NTH的值不得超過lobmaxsize的值。l 對于由定寬多字節(jié)字符組成的CLOB，這些參數(shù)的最大值不得超過字符數(shù)。 PL/SQL語言規(guī)范規(guī)定在DBMS_LOB程序中使用的RAW和VARCHAR2參數(shù)的上限

40、是32767字節(jié)。 如果在更新例程的調(diào)用中，AMOUNT + OFFSET的值超過4GB（對于BLOB和BFILE）或超過最大字符數(shù)+1（對于CLOB），則將引發(fā)訪問異常。在這些輸入條件下，讀取例程（如READ、COMPARE、INSTR、SUBSTR）將一直到讀到LOB/文件的結(jié)尾處。 參數(shù)為NULL值或無效輸入值的函數(shù)將返回NULL。目標(biāo)LOB參數(shù)為NULL值的過程將引發(fā)異常。 以樣式為參數(shù)的操作（如COMPARE、INSTR和SUBSTR）不支持PATTERN參數(shù)或字符串中的正則表達(dá)式和特殊匹配字符。 “LOB結(jié)尾處”條件是由READ過程使用NO_DATA_FOUND異常指示的。如果用戶

41、嘗試讀取LOB/文件的結(jié)尾處以后的內(nèi)容，將會引發(fā)此異常。最后一次讀取時(shí)的READ緩沖區(qū)將包含0個(gè)字節(jié)。 為了獲得一致的LOB更新，用戶應(yīng)負(fù)責(zé)在對修改LOB數(shù)據(jù)的任何過程進(jìn)行調(diào)用之前鎖定包含目標(biāo)LOB的行。 對于BFILE，如果文件尚未使用FILEOPEN打開，則程序COMPARE、INSTR、READ、SUBSTR將引發(fā)異常。DBMS_LOB程序包的安全性規(guī)定權(quán)限是與此程序包中的過程/函數(shù)的調(diào)用程序關(guān)聯(lián)的，如下所述： 如果調(diào)用程序是匿名PL/SQL塊，則使用當(dāng)前用戶的權(quán)限運(yùn)行過程/函數(shù)。 如果調(diào)用程序是存儲過程，則使用存儲過程的所有者權(quán)限運(yùn)行過程/函數(shù)。程序包建立以下常量以供它所控制的過程和函

42、數(shù)使用： File_readonly - 這是值為0的二進(jìn)制整數(shù)。 Lobmaxsize - 這是值為4294967295的整數(shù)。程序包使用下列異常：Invalid_argval - 其值為21560，并顯示消息“參數(shù)%s為空、無效或超出范圍”，導(dǎo)致這類錯(cuò)誤是因?yàn)椋簠?shù)應(yīng)為非空有效值，但傳遞的參數(shù)值為空、無效或超出范圍。程序包還可以生成其他異常，其中包括access_error、noexist_directory、nopriv_directory、invalid_directory、invalid_operation、unopened_file和open_toomany。Append過程App

43、end接受以下兩個(gè)輸入?yún)?shù)：dest_lob（BLOB或CLOB）和src_lob（BLOB或CLOB）。此過程用于將在src_lob參數(shù)中指定的BLOB或CLOB追加到在dest_lob參數(shù)中指定的BLOB或CLOB。此過程產(chǎn)生下列異常：VALUE_ERROR（如果源或目標(biāo)LOB為空）。Compare函數(shù)Compare接受五個(gè)輸入?yún)?shù)，即lob_1、lob_2、amount、offset_1和offset_2。如果比較成功，則此函數(shù)返回對應(yīng)于0的一個(gè)整數(shù)值；如果失敗，則返回非零的整數(shù)值。此函數(shù)維護(hù)純度，所以它可以在其父程序包之外的程序包中使用。輸入?yún)?shù)定義如下： lob_1 - 這是第一比較

44、目標(biāo)的LOB定位器。 lob_2 - 這是第二比較目標(biāo)的LOB定位器。 Amount - 這是要比較的字節(jié)數(shù)或字符數(shù)。 Offset_1 - 這是第一比較目標(biāo)LOB相對于比較起始處的偏移量（以字節(jié)或字符為單位）。 Offset_2 - 這是第二比較目標(biāo)LOB相對于比較起始處的偏移量（以字節(jié)或字符為單位）。執(zhí)行BFILE操作可產(chǎn)生下列異常： UNOPENED_FILE - 如果文件沒有打開。 NOEXIST_DIRECTORY - 如果目錄不存在。 NOPRIV_DIRECTORY - 如果沒有授予用戶訪問目錄的權(quán)限。 INVALID_DIRECTORY - 如果在打開文件之后目錄已經(jīng)無效。 I

45、NVALID_OPERATION - 如果文件不存在，或者如果沒有授予文件的訪問權(quán)限。Copy過程Copy包含五個(gè)輸入?yún)?shù)，即dest_lob、scr_lob、amount、dest_offset和scr_offset。此過程用于從指定的偏移值開始將源BLOB或CLOB復(fù)制到目標(biāo)CLOB或BLOB。 Dest_lob - 這是復(fù)制目標(biāo)的LOB定位器。 Src_lob - 這是復(fù)制源的LOB定位器。 Amount - 這是要復(fù)制的字節(jié)數(shù)或字符數(shù)。 Dest_offset - 這是目標(biāo)LOB相對于復(fù)制起始處的偏移量（以字節(jié)或字符為單位）。 Scr_offset - 這是源LOB相對于復(fù)制起始處的偏

46、移量（以字節(jié)或字符為單位）。此過程產(chǎn)生下列異常： VALUE_ERROR - 如果任何輸入?yún)?shù)為NULL或無效。 INVALID_ARGVAL - 如果src_offset或dest_offset LOBMAXSIZE，amount LOBMAXSIZE。Erase過程Erase包含三個(gè)輸入?yún)?shù)：lob_loc、amount和offset。此過程用于刪除lob_loc指定的BLOB或CLOB中從指定偏移量(offset)處開始的指定量(amount)的部分。此過程產(chǎn)生下列異常： VALUE_ERROR - 如果任何輸入?yún)?shù)為NULL。 INVALID_ARGVAL - 如果amountLOBM

47、AXSIZE，OFFSETLOBMAXSIZE。FilecloseBFILE過程fileclose只有一個(gè)輸入?yún)?shù)，即file_loc(BFILE)。此過程用于關(guān)閉指定的BFILE。此過程產(chǎn)生下列異常： VALUE_ERROR - 如果file_loc的輸入值為NULL。 UNOPENED_FILE - 如果文件尚未打開。 NOEXIST_DIRECTORY - 如果目錄不存在。 NOPRIV_DIRECTORY - 如果沒有授予訪問目錄的權(quán)限。 INVALID_DIRECTORY - 如果在打開文件之后目錄已經(jīng)無效。 INVALID_OPERATION - 如果文件不存在，或者沒有授予文件的

48、訪問權(quán)限。FilecloseallBFILE過程沒有輸入。此過程用于關(guān)閉所有打開的BFILE。此過程產(chǎn)生的異常類似如下： UNOPENED_FILE - 如果文件尚未打開。FileexistsBFILE函數(shù)fileexists只有一個(gè)輸入?yún)?shù)，即file_loc(BFILE)。此過程用于確定file_loc值指向的文件是否存在。此函數(shù)返回一個(gè)整數(shù)值，該值可能等于以下任一值，這取決于文件檢查的結(jié)果： 1 - 如果物理文件存在。 0 - 如果物理文件不存在。 NULL - 如果file_loc為空，或者file_loc沒有必需的目錄和OS權(quán)限，或者由于操作系統(tǒng)錯(cuò)誤而無法讀取file_loc。此函數(shù)

49、聲明純度，所以它可以在其父程序包之外的其他程序包過程和函數(shù)中使用。此函數(shù)引發(fā)下列異常： NOEXIST_DIRECTORY - 如果目錄不存在。 NOPRIV_DIRECTORY - 如果尚未授予用戶訪問目錄的權(quán)限。 INVALID_DIRECTORY - 如果在打開文件之后目錄已經(jīng)無效。FilegetnameBFILE過程filegetname包含一個(gè)輸入?yún)?shù)(file_loc)和兩個(gè)輸出參數(shù)（dir_alias和filename）。此過程用于接受文件定位器值并返回文件目錄別名和文件名值。此函數(shù)指示分配給定位器的目錄別名和文件名。它不指示物理文件或目錄的存在。dir_alias緩沖區(qū)的最大值

50、是30，整個(gè)路徑名的最大值是2000。此過程引發(fā)下列異常： VALUE_ERROR - 如果任何輸入?yún)?shù)為NULL或無效。 INVALID_ARGVAL - 如果dir_alias或filename是空值。FileisopenBFILE函數(shù)fileisopen只有一個(gè)輸入?yún)?shù)（即file_loc），它返回一個(gè)具有以下可能值的整數(shù)值： 1 - 文件已打開。 0 - 文件未打開。此函數(shù)引發(fā)下列異常： NOEXIST_DIRECTORY - 如果目錄不存在。 NOPRIV_DIRECTORY - 如果尚未授予用戶訪問目錄的權(quán)限。 INVALID_DIRECTORY - 如果在打開文件之后目錄已經(jīng)無效

51、。 INVALID_OPERATION - 如果文件不存在，或者沒有授予訪問權(quán)限。FileopenBFILE過程fileopen接受兩個(gè)輸入?yún)?shù)，即file_loc(BFILE)和open_mode(BINARY_INTEGER)。此過程用于打開BFILE類型文件以進(jìn)行讀取。目前，打開BFILE類型文件只能進(jìn)行讀取。此過程引發(fā)下列異常： VALUE_ERROR - 如果file_loc或open_mode為NULL。 INVALID_ARGVAL - 如果open_mode不等于FILE_READONLY。 OPEN_TOOMANY - 如果會話中的打開文件數(shù)超過了SESSION_MAX_OP

52、EN_FILES參數(shù)值。 NOEXIST_DIRECTORY - 如果目錄不存在。 INVALID_DIRECTORY - 如果在打開文件之后目錄已經(jīng)無效。 INVALID_OPERATION - 如果文件不存在。Getlength此函數(shù)接受一個(gè)重載的輸入?yún)?shù)，即lob_loc。此函數(shù)返回指定的BLOB、CLOB或BFILE的長度，并使用適合于該類型的單位。此函數(shù)維護(hù)純度，所以它可以在其父程序包之外使用。此函數(shù)返回一個(gè)整數(shù)值，此值對應(yīng)于： LOB的長度（以字節(jié)或字符為單位）是一個(gè)整數(shù)。 如果輸入LOB為空，則返回NULL。 對于BFILE，在下列情況下返回NULL：lob_loc為NULL；lob_loc不是有效的打開文件；lob_loc沒有必需的目錄和OS權(quán)限；由于OS讀取錯(cuò)誤而無法讀取lob_loc。此函數(shù)不引發(fā)異常。Instr函數(shù)instr包含四個(gè)輸入?yún)?shù)，即lob_loc、pattern、offset和nth。可以調(diào)用instr函數(shù)以便返回LOB中樣式的第n次出現(xiàn)的匹配位置（從指定的偏移量開始）。對于CLOB，varchar2緩沖區(qū)和LOB值必須來自同一字符集。對于BFILE，要使instr操作成功，該文件必須已經(jīng)使用成功的FILEISOPEN()操作打開。此函數(shù)聲明純度，因此它可以在父程序包之外的程序包中使用。此函數(shù)的輸入?yún)?shù)如下： Lob_l