Oracle8對(duì)象關(guān)系的設(shè)計(jì)與實(shí)現(xiàn)ppt課件

1、綜合實(shí)例定貨系統(tǒng).訂貨系統(tǒng)對(duì)象關(guān)系的設(shè)計(jì)與實(shí)現(xiàn)一、系統(tǒng)簡(jiǎn)介二、采用關(guān)系方法三、采用對(duì)象表四、采用對(duì)象視圖一、系統(tǒng)簡(jiǎn)介系統(tǒng)涉及的實(shí)體和聯(lián)絡(luò)顧客表，Customer_Info貨物表，Stock_Info訂貨單，purchase_info細(xì)目表 Items_infoCustomer_Infopurchase_infoStock_Infoitems_infomn1mmn11顧客信息顧客表，Customer_Info貨物信息貨物表，Stock_Info定貨信息訂貨單，purchase_info定貨條目細(xì)目表items_info二、采用關(guān)系方法關(guān)系表的定義對(duì)關(guān)系表的數(shù)據(jù)支配基于關(guān)系表的查詢與統(tǒng)計(jì)關(guān)系數(shù)據(jù)庫(kù)

2、的處理方案采用關(guān)系方法將顧客信息，訂貨單，貨物條目清單組織成表。將地址拆成為根本元素，如省、市、街道、等。設(shè)置顧客的一個(gè)能夠擁有的最大限制，每一個(gè)表示為表的一列。關(guān)系方法將訂貨條目從訂貨單中分別出來(lái)，單獨(dú)存放在一張表中，該表中有指向訂貨單表和貨物表的外鍵。關(guān)系表的定義采用關(guān)系模型實(shí)現(xiàn)對(duì)現(xiàn)實(shí)世界的籠統(tǒng)。將實(shí)體定義為關(guān)系表。實(shí)體間的聯(lián)絡(luò)經(jīng)過(guò)外來(lái)碼或關(guān)系表實(shí)現(xiàn)。表中的字段必需是不可分割的根本單位。不允許表中嵌表。經(jīng)過(guò)表的銜接實(shí)現(xiàn)多表查詢。CREATE TABLE customer_info (custno NUMBER PRIMARY KEY,custname VARCHAR2(200),stree

3、t VARCHAR2(200),city VARCHAR2(200), state VARCHAR2(200),zip VARCHAR2(6),phone1 VARCHAR2(20),phone2 VARCHAR2(20),phone3 VARCHAR2(20);建立顧客表建立貨物表CREATE TABLE stock_info (stocknoNUMBER PRIMARY KEY,cost NUMBER, tax_code NUMBER );CREATE TABLE purchase_info (ponoNUMBER PRIMARY KEY,custnoNUMBER REFERENCES c

4、ustomer_info(custno),orderdateDATE,shiptodateDATE,shiptostreetVARCHAR2(200),shiptocityVARCHAR2(200), shiptostate VARCHAR2(200),shiptozipVARCHAR2(20) );建立定貨表建立細(xì)目表CREATE TABLE items_info (lineitemno NUMBER,pono NUMBER REFERENCES purchase_info(pono),stockno NUMBER REFERENCES stock_info(stockno),quantit

5、y NUMBER,discount NUMBER,PRIMARY KEY (pono, lineitemno) );向關(guān)系表插入數(shù)據(jù)向顧客表插入數(shù)據(jù)INSERT INTO customer_info VALUES (1, Jean Nance, 2 Avocet Drive, Redwood Shores, CA, 95054, 415-555-1212, NULL, NULL) ;INSERT INTO customer_info VALUES (2, John Nike, 323 College Drive, Edison, NJ, 08820, 609-555-1212, 201-555

6、-1212, NULL) ;向貨物表插入數(shù)據(jù)INSERT INTO stock_info VALUES(1004, 6750.00, 2) ;INSERT INTO stock_info VALUES(1011, 4500.23, 2) ;INSERT INTO stock_info VALUES(1534, 2234.00, 2) ;INSERT INTO stock_info VALUES(1535, 3456.23, 2) ;向關(guān)系表插入數(shù)據(jù)向定貨單表插入數(shù)據(jù) INSERT INTO purchase_info VALUES (1001, 1, SYSDATE, 10-5月-1997,

7、NULL, NULL, NULL, NULL) ; INSERT INTO purchase_info VALUES (2001, 2, SYSDATE, 20-5月-1997, 55 Madison Ave, Madison, WI, 53715) ;向定貨單條目插入數(shù)據(jù)INSERT INTO items_info VALUES(01, 1001, 1534, 12, 0) ;INSERT INTO items_info VALUES(02, 1001, 1535, 10, 10) ;INSERT INTO items_info VALUES(10, 2001, 1004, 1, 0) ;I

8、NSERT INTO items_info VALUES(11, 2001, 1011, 2, 1) ;關(guān)系表的查詢查詢訂貨單1001的顧客信息和訂貨詳細(xì)信息多表銜接SELECT C.custno, C.custname, C.street, C.city, C.state, C.zip, C.phone1, C.phone2, C.phone3, P.pono, P.orderdate, L.stockno, L.lineitemno, L.quantity, L.discount FROM customer_info C, purchase_info P, items_info L WHE

9、RE C.custno = P.custno AND P.pono = L.pono AND P.pono = 1001;關(guān)系表的查詢SELECT P.pono,C.custname, P.orderdate, cursor(select stockno,lineitemno, quantity,discount from items_info where pono=p.pono) 細(xì)目表見(jiàn)下FROM customer_info C, purchase_info PWHERE P.custno = C.custno AND P.pono = 2001關(guān)系表的查詢 PONO CUSTNAME O

10、RDERDATE 細(xì)目表見(jiàn)下- - - - 2001 John Nike 14-10月-00 CURSOR ：4CURSOR STATEMENT ：4 STOCKNO LINEITEMNO QUANTITY DISCOUNT- - - - - 1004 10 1 0 1011 11 2 1數(shù)據(jù)支配-查詢數(shù)據(jù)統(tǒng)計(jì)每一個(gè)訂貨單的總價(jià)值SELECT L.pono, SUM(S.cost * L.quantity)FROM items_info L, stock_info SWHERE L.stockno = S.stockno GROUP BY L.pono ;查詢涉及貨物1004 的訂貨單及訂貨條

11、目信息SELECT P.pono, P.custno, L.stockno, L.lineitemno,L.quantity, L.discountFROM purchase_info P, items_info L WHERE P.pono = L.pono AND L.stockno = 1004;數(shù)據(jù)支配-維護(hù)數(shù)據(jù)修正數(shù)據(jù): 更改1001訂貨單中貨物號(hào)為1535的訂貨量UPDATE items_info SET quantity=20 WHERE pono=1001 AND stockno = 1535;刪除數(shù)據(jù): 刪除編號(hào)為1001的訂貨單DELETE FROM items_info

12、先刪細(xì)目表記錄WHERE pono = 1001;DELETE FROM purchase_info 再刪主表記錄WHERE pono = 1001;三、采用對(duì)象表 面向?qū)ο筇幚矸桨?定義類(lèi)型及類(lèi)型體 創(chuàng)建對(duì)象表 對(duì)象表的數(shù)據(jù)操作實(shí)現(xiàn)對(duì)象關(guān)系的方法O R 對(duì)象關(guān)系方法用戶自定義數(shù)據(jù)類(lèi)型使得復(fù)雜數(shù)據(jù)構(gòu)造進(jìn)入數(shù)據(jù)庫(kù)方式中。不將address拆散，也不將聯(lián)絡(luò)存為不相關(guān)的多列，在OR中可以定義特殊的類(lèi)型表示。不將訂貨條目以單獨(dú)表管理，OR中將他們作為相應(yīng)訂貨表的嵌套表。搜集類(lèi)型作為多值屬性在OR方法中，主要實(shí)體 顧客，貨物，訂貨單變?yōu)閷?duì)象。對(duì)象援用表示他們之間n:1的關(guān)系， 定義類(lèi)型定貨系統(tǒng)中的類(lèi)型：

13、地址 address_t 對(duì)象類(lèi)型多個(gè) phone_list_t 變長(zhǎng)數(shù)組類(lèi)型 顧客 customer_info_t 對(duì)象類(lèi)型 貨物 stock_info_t 對(duì)象類(lèi)型 貨物條目 line_item_t 對(duì)象類(lèi)型多個(gè)貨物條目 line_item_list_t 嵌套表類(lèi)型定貨單 purchase_info_t 對(duì)象類(lèi)型 定義類(lèi)型定義一個(gè)對(duì)象類(lèi)型表示地址信息CREATE TYPE address_t AS OBJECT ( street VARCHAR2(200), city VARCHAR2(200), state CHAR(2), zip VARCHAR2(20) ) ;定義一個(gè)數(shù)組類(lèi)型，表示

14、顧客的幾部CREATE TYPE phone_list_t AS VARRAY(10) OF VARCHAR2(20) ;定義類(lèi)型定義一個(gè)對(duì)象類(lèi)型表示定貨條目信息CREATE TYPE line_item_t AS OBJECT ( lineitemno NUMBER, STOCKREF REF stock_info_t, quantity NUMBER, discount NUMBER ) ;定義一個(gè)嵌套表類(lèi)型，表示定貨單中的貨物條目信息CREATE TYPE line_item_list_t AS TABLE OF line_item_t ;定義類(lèi)型定義一個(gè)對(duì)象類(lèi)型表示顧客CREATE T

15、YPE customer_info_t AS OBJECT ( custno NUMBER, custname VARCHAR2(200), address address_t, phone_list phone_list_t, ORDER MEMBER FUNCTION cust_order(x IN customer_info_t) RETURN INTEGER, PRAGMA RESTRICT_REFERENCES ( cust_order, WNDS, WNPS, RNPS, RNDS) ) ;定義類(lèi)型定義一個(gè)對(duì)象類(lèi)型表示貨物信息CREATE TYPE stock_info_t AS

16、OBJECT ( stockno NUMBER, cost NUMBER, tax_code NUMBER ) ;定義類(lèi)型定義一個(gè)對(duì)象類(lèi)型表示定貨單CREATE TYPE purchase_info_t AS OBJECT ( pono NUMBER, custref REF customer_info_t, orderdate DATE, shipdate DATE, line_item_list line_item_list_t, shiptoaddr address_t, MAP MEMBER FUNCTION ret_value RETURN NUMBER, PRAGMA RESTRI

17、CT_REFERENCES ( ret_value, WNDS, WNPS, RNPS, RNDS), MEMBER FUNCTION total_value RETURN NUMBER, PRAGMA RESTRICT_REFERENCES (total_value, WNDS, WNPS) ) ;嵌套表和可變數(shù)組的比較對(duì)顧客中的來(lái)說(shuō)VARRAY的元素是有序的VARRAY要求預(yù)置元素?cái)?shù)目對(duì)定貨單中的貨物條目來(lái)說(shuō)嵌套表沒(méi)有上界的限制嵌套表可以直接做查詢可以對(duì)嵌套表做索引定義方法定義customer_info_t對(duì)象類(lèi)型的方法：CREATE OR REPLACE TYPE BODY custom

18、er_info_t AS ORDER MEMBER FUNCTION cust_order (x IN customer_info_t) RETURN INTEGER IS BEGIN RETURN custno - x.custno; END;END;顧客對(duì)象的比較是顧客號(hào)的比較定義方法定義purchase_info_t對(duì)象類(lèi)型的方法：CREATE OR REPLACE TYPE BODY purchase_info_t AS MAP MEMBER FUNCTION ret_value RETURN NUMBER IS BEGIN RETURN pono; END;定義方法 MEMBER F

19、UNCTION total_value RETURN NUMBER IS i INTEGER;stock stock_info_t; - (stockno , cost , tax_code)line_item line_item_t; -( lineitemno STOCKREF quantity discount) total NUMBER := 0; BEGIN FOR i IN 1.SELF.line_item_list.COUNT LOOP line_item := SELF.line_item_list(i); SELECT DEREF(line_item.stockref) IN

20、TO stock FROM DUAL ; total := total + line_item.quantity * stock.cost ; END LOOP; RETURN total; END; END; 創(chuàng)建對(duì)象表普通來(lái)講，可以按下面規(guī)那么了解“對(duì)象和“表之間的關(guān)系：類(lèi)，即客觀世界的實(shí)體，對(duì)應(yīng)于表對(duì)象屬性對(duì)應(yīng)于表的列對(duì)象對(duì)應(yīng)于表的記錄每一個(gè)表是一個(gè)隱式的類(lèi)，它的對(duì)象記錄都有一樣的屬性列創(chuàng)建對(duì)象表定義對(duì)象表customer_tabCREATE TABLE customer_tab OF customer_info_t (custno PRIMARY KEY);對(duì)象類(lèi)型包含四個(gè)屬性：cus

21、tno NUMBERcustname VARCHAR2(200)address address_tphone_list phone_list_t創(chuàng)建對(duì)象表對(duì)象類(lèi)型作為創(chuàng)建對(duì)象表的模板約束用于表上，不能定義在類(lèi)型上如PRIMARY KEY的約束定義表可以包含對(duì)象列，由于變長(zhǎng)數(shù)組phone_list_t包含的數(shù)量少于10 x20，所以O(shè)RACLE將其作為一個(gè)存儲(chǔ)單元，當(dāng)超越4000byte時(shí)，將以BLOB類(lèi)型存于表外不為對(duì)象類(lèi)型分配存儲(chǔ)空間，僅為表分配記錄空間 創(chuàng)建對(duì)象表對(duì)象表stock_tab的定義：CREATE TABLE stock_tab OF stock_info_t (stockno

22、PRIMARY KEY) ;STOCK_INFO_T對(duì)象類(lèi)型包含三個(gè)屬性： stockno NUMBER, cost NUMBER, tax_code NUMBER創(chuàng)建對(duì)象表對(duì)象表purchase_tab的定義：CREATE TABLE purchase_tab OF purchase_info_t ( PRIMARY KEY (pono), SCOPE FOR (custref) IS customer_tab ) NESTED TABLE line_item_list STORE AS po_line_tab ;purchase_info_T對(duì)象類(lèi)型的屬性包括： pono NUMBER，

23、custref REF customer_info_t， orderdate DATE ， shipdate DATE， line_item_list line_item_list_t ， shiptoaddr address_t創(chuàng)建對(duì)象表 REF操作符：假設(shè)未作限制，REF允許援用恣意類(lèi)型為customer_info_t表的行對(duì)象。SCOPE 約束只是限制purchase_tab對(duì)象表的custref列的取值范圍。 嵌套表：非常適宜于表示表間的主從關(guān)系，從而可以防止DML中的關(guān)系聯(lián)接JOIN。一個(gè)嵌套表的一切記錄存儲(chǔ)在一個(gè)獨(dú)立的存儲(chǔ)表中，在該存儲(chǔ)表中有一個(gè)隱含的列NESTED_TABLE_I

24、D與相應(yīng)的父表記錄相對(duì)應(yīng)，在嵌套表中的記錄與父表中的記錄根據(jù)NESTED_TABLE_ID對(duì)應(yīng)。嵌套表類(lèi)型的各個(gè)屬性對(duì)應(yīng)于存儲(chǔ)表的各列。維護(hù)嵌套表修正嵌套表的存儲(chǔ)表 ALTER TABLE po_line_tab ADD (SCOPE FOR (stockref) IS stock_tab); ALTER TABLE po_line_tab STORAGE (NEXT 5K PCTINCREASE 5 MINEXTENTS 1 MAXEXTENTS 20);插入stock_tab數(shù)據(jù)INSERT INTO stock_tab VALUES(1004, 6750.00, 2);INSERT IN

25、TO stock_tab VALUES(1011, 4500.23, 2);INSERT INTO stock_tab VALUES(1534, 2234.00, 2);INSERT INTO stock_tab VALUES(1535, 3456.23, 2);向?qū)ο蟊聿迦霐?shù)據(jù)插入customer_tab數(shù)據(jù)INSERT INTO customer_tab VALUES ( 1, Jean Nance, address_t(2 Avocet Drive, Redwood Shores, CA, 95054), phone_list_t(415-555-1212) ;INSERT INTO c

26、ustomer_tab VALUES (2, John Nike, address_t(323 College Drive, Edison, NJ, 08820), phone_list_t(609-555-1212,201-555-1212) ) ;向?qū)ο蟊聿迦霐?shù)據(jù)向?qū)ο蟊聿迦霐?shù)據(jù)插入purchase_tab數(shù)據(jù)INSERT INTO purchase_tab SELECT 1001, REF(C), SYSDATE,10-MAY-1997, line_item_list_t(), NULL FROM customer_tab C WHERE C.custno = 1 ;上面的語(yǔ)句用以下屬性

27、創(chuàng)建了一個(gè)purchase_info_T對(duì)象： pono=1001 custref= 對(duì)于顧客1的援用orderdate=SYSDATE shipdate=10-MAY-1997 line_item_list= 一個(gè)空的貨物列表 shiptoaddr= NULL向嵌套表插入數(shù)據(jù)INSERT INTO THE ( SELECT P.line_item_list FROM purchase_tab P WHERE P.pono = 1001 ) SELECT 01, REF(S), 12, 0 FROM stock_tab S WHERE S.stockno = 1534;INSERT INTO

28、THE ( SELECT P.line_item_list FROM purchase_tab P WHERE P.pono = 1001 ) SELECT 02, REF(S), 10, 10 FROM stock_tab S WHERE S.stockno = 1535; 向?qū)ο蟊聿迦霐?shù)據(jù)INSERT INTO purchase_tab SELECT 2001, REF(C), SYSDATE , 20-MAY- 1997, line_item_list_t(), address_t(55 Madisonve,Madison,WI,53715) FROM customer_tab C WH

29、ERE C.custno = 2;向嵌套表插入數(shù)據(jù)INSERT INTO THE ( SELECT P.line_item_list FROM purchase_tab P WHERE P.pono = 2001 ) SELECT 10, REF(S), 1, 0 FROM stock_tab S WHERE S.stockno = 1004;INSERT INTO THE ( SELECT P.line_item_list FROM purchase_tab P WHERE P.pono = 2001 ) VALUES( line_item_t(11, NULL, 2, 1) ) ;修正嵌套

30、表修正定單表中單號(hào)為2001記錄的嵌套表，將條目號(hào)為11的貨物指針指向1011號(hào)貨物UPDATE THE (SELECT P.line_item_list FROM purchase_tab P WHERE P.pono = 2001 ) plistSET plist.stockref = ( SELECT REF(S) FROM stock_tab S WHERE S.stockno = 1011 )WHERE plist.lineitemno = 11 ;運(yùn)用排序方法排序方法的援用 SELECT p.pono FROM purchase_tab p ORDER BY VALUE(p);按p

31、urchase_tab 對(duì)象大小比較，隱含調(diào)用：MAP MEMBER FUNCTION ret_value RETURN NUMBER IS BEGIN RETURN pono; END;相當(dāng)于 order by pono 數(shù)據(jù)支配-查詢數(shù)據(jù) 查詢訂貨單1001的顧客信息和訂貨詳細(xì)信息SELECT DEREF(p.custref), p.shiptoaddr, p.pono, p.orderdate, line_item_listFROM purchase_tab p WHERE p.pono = 1001 ; 每一個(gè)訂貨單的總價(jià)值SELECT p.pono, p.total_value()

32、FROM purchase_tab p ;數(shù)據(jù)支配-查詢數(shù)據(jù) 查詢定貨單及涉及貨物1004定貨條目的信息。 SELECT po.pono, po.custref.custno, CURSOR ( SELECT * FROM TABLE (po.line_item_list) L WHERE L.stockref.stockno = 1004 )FROM purchase_tab po ; 數(shù)據(jù)支配-刪除數(shù)據(jù)在下面的刪除例子中，Oracle自動(dòng)刪除一切屬于訂貨單的貨物條目，而在原關(guān)系模型中必需求思索到兩張表的刪除問(wèn)題。刪除訂貨單1001：DELETEFROM purchase_tabWHERE

33、pono = 1001 ;四、采用對(duì)象視圖對(duì)象視圖處理方案基于關(guān)系表 定義對(duì)象視圖對(duì)象視圖的查詢運(yùn)用觸發(fā)器更新對(duì)象視圖對(duì)象視圖處理方案對(duì)象視圖是虛擬對(duì)象表，數(shù)據(jù)源取自表和視圖。采用對(duì)象表實(shí)現(xiàn)系統(tǒng)設(shè)計(jì)普通采用如下步驟建立實(shí)體和關(guān)系經(jīng)過(guò)創(chuàng)建和填充關(guān)系表實(shí)現(xiàn)實(shí)體關(guān)系構(gòu)造采用UDT表示對(duì)象關(guān)系方式，模型化一個(gè)實(shí)體關(guān)系構(gòu)造采用O-R方式創(chuàng)建和填充對(duì)象表實(shí)現(xiàn)實(shí)體關(guān)系構(gòu)造采用對(duì)象視圖方式要運(yùn)用一樣的初始步驟，但最后一步有所不同，它不是經(jīng)過(guò)創(chuàng)建和填充對(duì)象表的方式，而是運(yùn)用對(duì)象視圖來(lái)表示虛擬對(duì)象表，數(shù)據(jù)取自普通的關(guān)系表定義對(duì)象視圖三個(gè)對(duì)象視圖：Customer_view, Stock_view, Purchas

34、e_view。創(chuàng)建對(duì)象視圖的語(yǔ)句有四個(gè)部分：視圖的名字視圖所基于的對(duì)象類(lèi)型的名字基于主碼創(chuàng)建對(duì)象標(biāo)識(shí)OID)一個(gè)選擇語(yǔ)句根據(jù)對(duì)應(yīng)的對(duì)象類(lèi)型向虛擬對(duì)象表中填充數(shù)據(jù)后面例子借用前面的幾個(gè)類(lèi)型定義 定義對(duì)象視圖Customer_view對(duì)象視圖：CREATE OR REPLACE VIEW customer_view OF customer_info_t WITH OBJECT OID(custno) AS SELECT C.custno, C.custname, address_t(C.street, C.city, C.state, C.zip), phone_list_t (C.phone1,

35、 C.phone2, C.phone3) FROM customer_info C ;定義對(duì)象視圖Stock_view對(duì)象視圖CREATE OR REPLACE VIEW stock_view OF stock_info_t WITH OBJECT OID(stockno) ASSELECT * FROM stock_info ;定義對(duì)象視圖Purchase_view對(duì)象視圖CREATE OR REPLACE VIEW purchase_view OF purchase_info_t WITH OBJECT OID (pono) AS SELECT P.pono, , P.orderdate,

36、P.shiptodate , , address_t (P.shiptostreet, P.shiptocity, P.shiptostate, P.shiptozip) FROM purchase_info P ;客戶對(duì)象援用嵌套表列構(gòu)造視圖的對(duì)象援用列MAKE_REF (customer_view, P.custno),MAKE_REF(對(duì)象表/視圖，定位記錄的主鍵值前往指向?qū)ο蟊?視圖的一個(gè)對(duì)象記錄的REF客戶對(duì)象援用構(gòu)造視圖的嵌套表列CAST ( MULTISET( SELECT line_item_t ( L.lineitemno, MAKE_REF(stock_view, L.st

37、ockno), L.quantity , L.discount ) FROM items_info L WHERE L.pono= P.pono ) AS line_item_list_t ),嵌套表列 定義對(duì)象視圖purchase_info_t對(duì)象類(lèi)型有如下屬性： pono NUMBER custref REF customer_info_t orderdate DATE shipdate DATE line_item_list line_item_list_t shiptoaddr address_t運(yùn)用對(duì)象視圖 - 查詢數(shù)據(jù)查詢訂貨單1001的顧客信息和訂貨詳細(xì)信息SELECT DERE

38、F(p.custref), p.shiptoaddr, p.pono, p.orderdate, line_item_list FROM purchase_view p WHERE p.pono = 1001 ;統(tǒng)計(jì)每一個(gè)訂貨單的總價(jià)值SELECT p.pono, p.total_value()FROM purchase_view p ;運(yùn)用對(duì)象視圖 - 查詢數(shù)據(jù)查詢定貨單及涉及貨物1004定貨條目的信息。SELECT po.pono, po.custref.custno, CURSOR ( SELECT * FROM TABLE (po.line_item_list) L WHERE L.s

39、tockref.stockno = 1004 )FROM purchase_view po ;運(yùn)用觸發(fā)器更新對(duì)象視圖Oracle提供INSTEAD OF觸發(fā)器可作為更新復(fù)雜對(duì)象視圖的方法。每當(dāng)想要改動(dòng)對(duì)象視圖中行對(duì)象的屬性值時(shí)，Oracle執(zhí)行對(duì)象視圖的INSTEAD OF觸發(fā)器在觸發(fā)器中，Oracle運(yùn)用關(guān)鍵字:OLD和:NEW存取行對(duì)象的當(dāng)前值和新值。運(yùn)用觸發(fā)器更新對(duì)象視圖Stock_view的INSTEAD OF觸發(fā)器CREATE OR REPLACE TRIGGER stockview_insert_tr INSTEAD OF INSERT ON stock_viewFOR EACH

40、ROW BEGIN INSERT INTO stock_info VALUES ( :NEW.stockno, :NEW.cost, :NEW.tax_code ); END ; 運(yùn)用觸發(fā)器更新對(duì)象視圖Customer_view的INSTEAD OF觸發(fā)器CREATE OR REPLACE TRIGGER custview_insert_tr INSTEAD OF INSERT ON customer_viewFOR EACH ROW DECLARE phones phone_list_t; tphone1 customer_info.phone1%TYPE := NULL; tphone2

41、customer_info.phone2%TYPE := NULL; tphone3 customer_info.phone3%TYPE := NULL;運(yùn)用觸發(fā)器更新對(duì)象視圖BEGIN phones := :NEW.phone_list; IF phones.COUNT 2 THEN tphone3 := phones(3); END IF; IF phones.COUNT 1 THEN tphone2 := phones(2); END IF; IF phones.COUNT 0 THEN tphone1 := phones(1); END IF;運(yùn)用觸發(fā)器更新對(duì)象視圖 INSERT IN

42、TO customer_info VALUES ( :NEW.custno, :NEW.custname, :NEW.address.street, :NEW.address.city, :NEW.address.state, :NEW.address.zip, tphone1, tphone2, tphone3);END ;向?qū)ο笠晥D插入數(shù)據(jù)以下語(yǔ)句激活了customer_view觸發(fā)器：INSERT INTO customer_view VALUES ( 13, Ellan White, address_t(25 I Street, Memphis, TN, 05456), phone_list_t(615-555-1212) );運(yùn)用觸發(fā)器更新對(duì)象視圖Purchase_view的INSTEAD OF觸發(fā)器CREATE OR REPLACE TRIGGER poview_insert_tr INSTEAD OF INSERT ON pur