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

1、綜合實(shí)例定貨系統(tǒng)訂貨系統(tǒng)統(tǒng)對(duì)對(duì)象關(guān)系系的設(shè)計(jì)計(jì)與實(shí)現(xiàn)現(xiàn)一、系統(tǒng)統(tǒng)簡(jiǎn)介二、采用用關(guān)系方方法三、采用用對(duì)象表表四、采用用對(duì)象視視圖一、系統(tǒng)統(tǒng)簡(jiǎn)介系統(tǒng)涉及及的實(shí)體體和聯(lián)系系顧客表，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ì)

2、關(guān)系表表的數(shù)據(jù)據(jù)操縱基于關(guān)系系表的查查詢與統(tǒng)統(tǒng)計(jì)關(guān)系數(shù)據(jù)據(jù)庫(kù)的解解決方案案采用關(guān)系系方法將將顧客信信息，訂訂貨單，貨物條條目清單單組織成成表。將地址拆拆成為基基本元素素，如省省、市、街道、郵編等等。設(shè)置顧客客的一個(gè)個(gè)可能擁?yè)碛须娫捲挼淖畲蟠笙薅?，每一個(gè)個(gè)電話表表示為表表的一列列。關(guān)系方法法將訂貨貨條目從從訂貨單單中分離離出來(lái)，單獨(dú)存存放在一一張表中中，該表表中有指指向訂貨貨單表和和貨物表表的外鍵鍵。關(guān)系表的的定義采用關(guān)系系模型實(shí)實(shí)現(xiàn)對(duì)現(xiàn)現(xiàn)實(shí)世界界的抽象象。將實(shí)體定定義為關(guān)關(guān)系（表表）。實(shí)體間的的聯(lián)系通通過(guò)外來(lái)碼或關(guān)系表實(shí)現(xiàn)。表中的字字段必須須是不可可分割的的基本單單位。不允許表表中嵌表表。通過(guò)

3、表的的連接實(shí)實(shí)現(xiàn)多表表查詢。CREATETABLEcustomer_info(custnoNUMBERPRIMARYKEY,custnameVARCHAR2(200),streetVARCHAR2(200),cityVARCHAR2(200),stateVARCHAR2(200),zipVARCHAR2(6),phone1VARCHAR2(20),phone2VARCHAR2(20),phone3VARCHAR2(20);建立顧客客表建立貨物物表CREATETABLEstock_info(stocknoNUMBERPRIMARYKEY,costNUMBER,tax_codeNUMBER );C

4、REATETABLEpurchase_info(ponoNUMBERPRIMARY KEY,custnoNUMBER REFERENCEScustomer_info(custno),orderdateDATE,shiptodateDATE,shiptostreetVARCHAR2(200),shiptocityVARCHAR2(200),shiptostateVARCHAR2(200),shiptozipVARCHAR2(20);建立定貨貨表建立細(xì)目目表CREATETABLEitems_info(lineitemnoNUMBER,ponoNUMBERREFERENCESpurchase_inf

5、o(pono),stocknoNUMBERREFERENCESstock_info(stockno),quantityNUMBER,discountNUMBER,PRIMARY KEY(pono,lineitemno);向關(guān)系表表插入數(shù)數(shù)據(jù)向顧客表表插入數(shù)數(shù)據(jù)INSERTINTOcustomer_infoVALUES(1, Jean Nance,2Avocet Drive,RedwoodShores,CA,95054,415-555-1212,NULL,NULL);INSERTINTOcustomer_infoVALUES(2, John Nike,323 CollegeDrive,Ediso

6、n,NJ,08820,609-555-1212,201-555-1212, NULL) ;向貨物表表插入數(shù)數(shù)據(jù)INSERTINTOstock_infoVALUES(1004, 6750.00, 2) ;INSERTINTOstock_infoVALUES(1011, 4500.23, 2) ;INSERTINTOstock_infoVALUES(1534, 2234.00, 2) ;INSERTINTOstock_infoVALUES(1535, 3456.23, 2) ;向關(guān)系表表插入數(shù)數(shù)據(jù)向定貨單單表插入入數(shù)據(jù)INSERTINTOpurchase_infoVALUES(1001, 1, S

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

8、s_infoVALUES(11,2001,1011,2,1) ;關(guān)系表的的查詢查詢訂貨貨單1001的顧客信信息和訂訂貨詳細(xì)細(xì)信息（多表連連接）SELECTC.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.discountFROMcustomer_infoC,purchase_infoP,items_info LWHEREC.custno =P.custnoANDP.pono =L

9、.ponoANDP.pono=1001;關(guān)系表的的查詢SELECTP.pono,C.custname,P.orderdate,cursor(selectstockno,lineitemno,quantity,discountfromitems_infowherepono=p.pono)細(xì)細(xì)目目表見(jiàn)下下FROMcustomer_info C, purchase_infoPWHEREP.custno=C.custnoANDP.pono=2001關(guān)系表的的查詢PONO CUSTNAMEORDERDATE細(xì)細(xì)目表見(jiàn)見(jiàn)下-2001 JohnNike14-10月-00CURSOR ：4CURSORSTAT

10、EMENT ：4STOCKNOLINEITEMNOQUANTITYDISCOUNT-1004101010111121數(shù)據(jù)操縱縱-查查詢數(shù)據(jù)據(jù)統(tǒng)計(jì)每一一個(gè)訂貨貨單的總總價(jià)值SELECTL.pono, SUM(S.cost*L.quantity)FROMitems_info L, stock_infoSWHEREL.stockno =S.stocknoGROUPBYL.pono;查詢涉及及貨物1004 的訂訂貨單及及訂貨條條目信息息SELECTP.pono,P.custno,L.stockno,L.lineitemno,L.quantity,L.discountFROMpurchase_infoP

11、,items_info LWHEREP.pono=L.ponoAND L.stockno= 1004;數(shù)據(jù)操縱縱-維維護(hù)數(shù)據(jù)據(jù)修改數(shù)據(jù)據(jù):更改1001訂訂貨單中中貨物號(hào)號(hào)為1535的的訂貨量量UPDATEitems_infoSETquantity=20WHEREpono=1001AND stockno=1535;刪除數(shù)據(jù)據(jù):刪除編號(hào)號(hào)為1001的的訂貨單單DELETEFROMitems_info（先刪刪細(xì)目表表記錄）WHEREpono =1001;DELETEFROMpurchase_info （再再刪主表表記錄）WHEREpono =1001;三、采用用對(duì)象表表面向?qū)ο笙蠼鉀Q方方案定義類型型

12、及類型型體創(chuàng)建對(duì)象象表對(duì)象表的的數(shù)據(jù)操操作實(shí)現(xiàn)對(duì)象象關(guān)系的的方法O R對(duì)對(duì)象關(guān)系系方法用戶自定定義數(shù)據(jù)據(jù)類型使使得復(fù)雜雜數(shù)據(jù)結(jié)結(jié)構(gòu)進(jìn)入入數(shù)據(jù)庫(kù)庫(kù)模式中中。不將address拆拆散，也也不將聯(lián)聯(lián)系電話話存為不不相關(guān)的的多列，在OR中可可以定義義特殊的的類型表表示。不將訂貨貨條目以以單獨(dú)表表管理，OR中將他他們作為為相應(yīng)訂訂貨表的的嵌套表表。收集類型型作為多多值屬性性在OR方法中中，主要要實(shí)體顧客，貨物，訂貨單變?yōu)閷?duì)象象。對(duì)象引用用表示他他們之間間n:1的關(guān)系系，定義類型型定貨系統(tǒng)統(tǒng)中的類類型：地址address_t對(duì)對(duì)象類類型多個(gè)電話話 phone_list_t變長(zhǎng)數(shù)組組類型顧客customer

13、_info_t對(duì)對(duì)象類型型貨物stock_info_t對(duì)對(duì)象類類型貨物條目目line_item_t對(duì)對(duì)象象類型多個(gè)貨物物條目line_item_list_t嵌嵌套表表類型定貨單purchase_info_t對(duì)對(duì)象象類型定義類型型定義一個(gè)個(gè)對(duì)象類類型表示示地址信信息CREATETYPEaddress_tASOBJECT (streetVARCHAR2(200),cityVARCHAR2(200),stateCHAR(2),zipVARCHAR2(20) ;定義一個(gè)個(gè)數(shù)組類類型，表表示顧客客的幾部部電話CREATETYPEphone_list_tASVARRAY(10)OFVARCHAR2(20)

14、 ;定義類型型定義一個(gè)個(gè)對(duì)象類類型表示示定貨條條目信息息CREATETYPEline_item_tASOBJECT(lineitemno NUMBER,STOCKREFREFstock_info_t,quantityNUMBER,discountNUMBER);定義一個(gè)個(gè)嵌套表表類型，表示定定貨單中中的貨物物條目信信息CREATETYPEline_item_list_tASTABLEOFline_item_t;定義類型型定義一個(gè)個(gè)對(duì)象類類型表示示顧客CREATETYPEcustomer_info_tASOBJECT (custnoNUMBER,custnameVARCHAR2(200),add

15、ressaddress_t,phone_list phone_list_t,ORDERMEMBERFUNCTIONcust_order(xINcustomer_info_t)RETURNINTEGER,PRAGMARESTRICT_REFERENCES(cust_order,WNDS,WNPS,RNPS,RNDS) ;定義類型型定義一個(gè)個(gè)對(duì)象類類型表示示貨物信信息CREATETYPEstock_info_tASOBJECT (stocknoNUMBER,costNUMBER,tax_codeNUMBER) ;定義類型型定義一個(gè)個(gè)對(duì)象類類型表示示定貨單單CREATETYPEpurchase_in

16、fo_tASOBJECT (ponoNUMBER,custrefREFcustomer_info_t,orderdateDATE,shipdateDATE,line_item_listline_item_list_t,shiptoaddraddress_t,MAPMEMBER FUNCTION ret_valueRETURNNUMBER,PRAGMARESTRICT_REFERENCES(ret_value, WNDS, WNPS, RNPS, RNDS),MEMBERFUNCTIONtotal_valueRETURNNUMBER,PRAGMARESTRICT_REFERENCES(total

17、_value,WNDS,WNPS) ;嵌套表和和可變數(shù)數(shù)組的比比較對(duì)顧客中中的電話話來(lái)說(shuō)VARRAY的的元素是是有序的的VARRAY要要求預(yù)置置元素?cái)?shù)數(shù)目對(duì)定貨單單中的貨貨物條目目來(lái)說(shuō)嵌套表沒(méi)沒(méi)有上界界的限制制嵌套表可可以直接接做查詢?cè)兛梢詫?duì)嵌嵌套表做做索引定義方法法定義customer_info_t對(duì)象象類型的的方法：CREATEORREPLACETYPE BODYcustomer_info_tASORDERMEMBERFUNCTIONcust_order (x IN customer_info_t)RETURNINTEGERISBEGINRETURNcustno -x.custno;END

18、;END;顧客對(duì)象象的比較較是顧客客號(hào)的比比較定義方法法定義purchase_info_t對(duì)象象類型的的方法：CREATEORREPLACETYPE BODYpurchase_info_tASMAPMEMBER FUNCTION ret_valueRETURNNUMBERISBEGINRETURNpono;END;定義方法法MEMBERFUNCTIONtotal_valueRETURN NUMBERISiINTEGER;stockstock_info_t;-(stockno, cost,tax_code)line_itemline_item_t;-(lineitemnoSTOCKREFquan

19、titydiscount)totalNUMBER:=0;BEGINFORiIN1.SELF.line_item_list.COUNTLOOPline_item:=SELF.line_item_list(i);SELECTDEREF(line_item.stockref) INTOstockFROM DUAL;total:=total+ line_item.quantity* stock.cost;ENDLOOP;RETURNtotal;END;END;創(chuàng)建對(duì)象象表一般來(lái)講講，可以以按下面面規(guī)則理理解“對(duì)對(duì)象”和和“表”之間的的關(guān)系：類，即客客觀世界界的實(shí)體體，對(duì)應(yīng)應(yīng)于表對(duì)象屬性性對(duì)應(yīng)于于表的列

20、列對(duì)象對(duì)應(yīng)應(yīng)于表的的記錄每一個(gè)表表是一個(gè)個(gè)隱式的的類，它它的對(duì)象象（記錄錄）都有有相同的的屬性（列）創(chuàng)建對(duì)象象表定義對(duì)象象表customer_tabCREATETABLEcustomer_tab OFcustomer_info_t(custno PRIMARYKEY);對(duì)象類型型包含四四個(gè)屬性性：custnoNUMBERcustnameVARCHAR2(200)addressaddress_tphone_listphone_list_t創(chuàng)建對(duì)象象表對(duì)象類型型作為創(chuàng)創(chuàng)建對(duì)象象表的模模板約束用于于表上，不能定定義在類類型上（如PRIMARYKEY的約束束定義）表可以包包含對(duì)象象列，由由于變長(zhǎng)長(zhǎng)數(shù)組

21、phone_list_t包包含的數(shù)數(shù)量少于于10 x20，所以O(shè)RACLE將將其作為為一個(gè)存存儲(chǔ)單元元，當(dāng)超超過(guò)4000byte時(shí)，將將以BLOB類類型存于于表外不為對(duì)象象類型分分配存儲(chǔ)儲(chǔ)空間，僅為表表分配記記錄空間間創(chuàng)建對(duì)象象表對(duì)象表stock_tab的的定義：CREATETABLEstock_tab OF stock_info_t(stocknoPRIMARY KEY);STOCK_INFO_T對(duì)對(duì)象類型型包含三三個(gè)屬性性：stocknoNUMBER,costNUMBER,tax_codeNUMBER創(chuàng)建對(duì)象象表對(duì)象表purchase_tab的的定義：CREATETABLEpurchase

22、_tab OF purchase_info_t(PRIMARY KEY(pono),SCOPEFOR(custref) IS customer_tab)NESTEDTABLEline_item_listSTOREASpo_line_tab;purchase_info_T對(duì)對(duì)象類型型的屬性性包括：ponoNUMBER，custrefREFcustomer_info_t，orderdateDATE ，shipdateDATE，line_item_listline_item_list_t ，shiptoaddraddress_t創(chuàng)建對(duì)象象表REF操操作符：如果未作作限制，REF允許引引用任意意類型為

23、為customer_info_t表的行行對(duì)象。SCOPE約約束只是是限制purchase_tab對(duì)對(duì)象表的的custref列的的取值范范圍。嵌套表：十分適合合于表示示表間的的主從關(guān)關(guān)系，從從而可以以避免DML中中的關(guān)系系聯(lián)接（JOIN）。一個(gè)嵌套套表的所所有記錄錄存儲(chǔ)在在一個(gè)獨(dú)獨(dú)立的存存儲(chǔ)表中中，在該該存儲(chǔ)表表中有一一個(gè)隱含含的列NESTED_TABLE_ID與與相應(yīng)的的父表記記錄相對(duì)對(duì)應(yīng)，在在嵌套表表中的記記錄與父父表中的的記錄根根據(jù)NESTED_TABLE_ID對(duì)應(yīng)應(yīng)。嵌套表類類型的各各個(gè)屬性性對(duì)應(yīng)于于存儲(chǔ)表表的各列列。維護(hù)嵌套套表修改嵌套套表的存存儲(chǔ)表ALTERTABLEpo_line_

24、tabADD(SCOPE FOR(stockref) IS stock_tab);ALTERTABLEpo_line_tabSTORAGE (NEXT 5K PCTINCREASE 5MINEXTENTS 1MAXEXTENTS20);插入stock_tab數(shù)據(jù)據(jù)INSERTINTOstock_tabVALUES(1004, 6750.00, 2);INSERTINTOstock_tabVALUES(1011, 4500.23, 2);INSERTINTOstock_tabVALUES(1534, 2234.00, 2);INSERTINTOstock_tabVALUES(1535, 3456

25、.23, 2);向?qū)ο蟊肀聿迦霐?shù)數(shù)據(jù)插入customer_tab數(shù)據(jù)據(jù)INSERTINTOcustomer_tabVALUES(1,JeanNance,address_t(2Avocet Drive,RedwoodShores,CA,95054),phone_list_t(415-555-1212);INSERTINTOcustomer_tabVALUES(2, John Nike,address_t(323College Drive,Edison, NJ, 08820),phone_list_t(609-555-1212,201-555-1212) );向?qū)ο蟊肀聿迦霐?shù)數(shù)據(jù)向?qū)ο蟊肀聿迦霐?shù)數(shù)

26、據(jù)插入purchase_tab數(shù)據(jù)據(jù)INSERTINTOpurchase_tabSELECT1001, REF(C),SYSDATE,10-MAY-1997,line_item_list_t(), NULLFROMcustomer_tab CWHEREC.custno= 1;上面的語(yǔ)語(yǔ)句用下下列屬性性創(chuàng)建了了一個(gè)purchase_info_T對(duì)對(duì)象：pono=1001custref=對(duì)對(duì)于顧客客1的引引用orderdate=SYSDATEshipdate=10-MAY-1997line_item_list=一一個(gè)空空的貨物物列表shiptoaddr=NULL向嵌套表表插入數(shù)數(shù)據(jù)INSERTIN

27、TOTHE(SELECTP.line_item_listFROMpurchase_tab PWHEREP.pono =1001)SELECT01,REF(S), 12,0FROMstock_tab SWHERES.stockno =1534;INSERTINTOTHE(SELECTP.line_item_listFROMpurchase_tab PWHEREP.pono =1001)SELECT02,REF(S), 10,10FROMstock_tab SWHERES.stockno =1535;向?qū)ο蟊肀聿迦霐?shù)數(shù)據(jù)INSERTINTOpurchase_tabSELECT2001, REF(C

28、),SYSDATE,20-MAY-1997,line_item_list_t(),address_t(55 Madisonve,Madison,WI,53715)FROMcustomer_tab CWHEREC.custno=2;向嵌套表表插入數(shù)數(shù)據(jù)INSERTINTOTHE(SELECTP.line_item_listFROMpurchase_tab PWHEREP.pono =2001)SELECT10,REF(S), 1, 0FROMstock_tab SWHERES.stockno =1004;INSERTINTOTHE(SELECTP.line_item_listFROMpurcha

29、se_tab PWHEREP.pono =2001)VALUES( line_item_t(11,NULL,2,1) ;修改嵌套套表修改定單單表中單單號(hào)為2001記錄的的嵌套表表，將條條目號(hào)為為11的的貨物指指針指向向1011號(hào)貨貨物UPDATETHE (SELECTP.line_item_listFROMpurchase_tab PWHEREP.pono =2001)plistSETplist.stockref=( SELECTREF(S)FROMstock_tabSWHERES.stockno=1011 )WHEREplist.lineitemno =11;使用排序序方法排序方法法的引用S

30、ELECTp.ponoFROMpurchase_tab pORDERBYVALUE(p);按purchase_tab 對(duì)象象大小比比較，隱隱含調(diào)用用：MAPMEMBER FUNCTION ret_valueRETURNNUMBERISBEGINRETURNpono;END;相當(dāng)于orderbypono數(shù)據(jù)操縱縱-查查詢數(shù)據(jù)據(jù)查詢訂貨貨單1001的的顧客信信息和訂訂貨詳細(xì)細(xì)信息SELECTDEREF(p.custref), p.shiptoaddr,p.pono,p.orderdate,line_item_listFROMpurchase_tab pWHEREp.pono =1001;每一個(gè)訂訂

31、貨單的的總價(jià)值值SELECTp.pono, p.total_value()FROMpurchase_tab p;數(shù)據(jù)操縱縱-查查詢數(shù)據(jù)據(jù)查詢定貨貨單及涉涉及貨物物1004定貨貨條目的的信息。SELECTpo.pono,po.custref.custno,CURSOR(SELECT*FROMTABLE(po.line_item_list)LWHEREL.stockref.stockno =1004)FROMpurchase_tabpo;數(shù)據(jù)操縱縱-刪刪除數(shù)據(jù)據(jù)在下面的的刪除例例子中，Oracle自動(dòng)刪刪除所有有屬于訂訂貨單的的貨物條條目，而而在原關(guān)關(guān)系模型型中必須須要考慮慮到兩張張表的刪刪除問(wèn)題

32、題。刪除訂貨貨單1001：DELETEFROMpurchase_tabWHEREpono= 1001;四、采用用對(duì)象視視圖對(duì)象視圖圖解決方方案（基基于關(guān)系系表）定義對(duì)象象視圖對(duì)象視圖圖的查詢?cè)兪褂糜|發(fā)發(fā)器更新新對(duì)象視視圖對(duì)象視圖圖解決方方案對(duì)象視圖圖是虛擬擬對(duì)象表表，數(shù)據(jù)據(jù)源取自自表和視視圖。采用對(duì)象象表實(shí)現(xiàn)現(xiàn)系統(tǒng)設(shè)設(shè)計(jì)一般般采用如如下步驟驟建立實(shí)體體和關(guān)系系通過(guò)創(chuàng)建建和填充充關(guān)系表表實(shí)現(xiàn)實(shí)實(shí)體關(guān)系系結(jié)構(gòu)采用UDT表示示對(duì)象關(guān)關(guān)系模式式，模型型化一個(gè)個(gè)實(shí)體關(guān)關(guān)系結(jié)構(gòu)構(gòu)采用O-R模式式創(chuàng)建和和填充對(duì)對(duì)象表實(shí)實(shí)現(xiàn)實(shí)體體關(guān)系結(jié)結(jié)構(gòu)采用對(duì)象象視圖方方式要使使用相同同的初始始步驟，但最后后一步有有所不同

33、同，它不不是通過(guò)過(guò)創(chuàng)建和和填充對(duì)對(duì)象表的的方式，而是使使用對(duì)象象視圖來(lái)來(lái)表示虛虛擬對(duì)象象表，數(shù)數(shù)據(jù)取自自一般的的關(guān)系表表定義對(duì)象象視圖三個(gè)對(duì)象象視圖：Customer_view,Stock_view,Purchase_view。創(chuàng)建對(duì)象象視圖的的語(yǔ)句有有四個(gè)部部分：視圖的名名字視圖所基基于的對(duì)對(duì)象類型型的名字字基于主碼碼創(chuàng)建對(duì)對(duì)象標(biāo)識(shí)識(shí)OID)一個(gè)選擇擇語(yǔ)句根根據(jù)對(duì)應(yīng)應(yīng)的對(duì)象象類型向向虛擬對(duì)對(duì)象表中中填充數(shù)數(shù)據(jù)后面例子子借用前前面的幾幾個(gè)類型型定義定義對(duì)象象視圖Customer_view對(duì)象視視圖：CREATEORREPLACEVIEW customer_viewOFcustomer_info

34、_tWITH OBJECTOID(custno)ASSELECTC.custno,C.custname,address_t(C.street, C.city,C.state,C.zip),phone_list_t(C.phone1,C.phone2,C.phone3)FROMcustomer_infoC ;定義對(duì)象象視圖Stock_view對(duì)象視視圖CREATEORREPLACEVIEW stock_viewOFstock_info_tWITH OBJECTOID(stockno)ASSELECT*FROMstock_info;定義對(duì)象象視圖Purchase_view對(duì)象視視圖CREATEOR

35、REPLACEVIEW purchase_viewOFpurchase_info_tWITH OBJECTOID(pono) ASSELECTP.pono,P.orderdate,P.shiptodate,address_t(P.shiptostreet,P.shiptocity,P.shiptostate,P.shiptozip)FROMpurchase_infoP ;客戶對(duì)象象引用嵌套表列列構(gòu)造視圖圖的對(duì)象象引用列列MAKE_REF(customer_view,P.custno),MAKE_REF(對(duì)對(duì)象表/視圖，定位記記錄的主主鍵值）返回指向向?qū)ο蟊肀?視圖圖的一個(gè)個(gè)對(duì)象（記錄）的REF

36、客戶對(duì)象象引用構(gòu)造視圖圖的嵌套套表列CAST (MULTISET(SELECTline_item_t (L.lineitemno,MAKE_REF(stock_view,L.stockno),L.quantity,L.discount)FROMitems_infoLWHEREL.pono=P.pono)ASline_item_list_t),嵌套表列列定義對(duì)象象視圖purchase_info_t對(duì)對(duì)象類型型有如下下屬性：ponoNUMBERcustrefREFcustomer_info_torderdateDATEshipdateDATEline_item_listline_item_list

37、_tshiptoaddraddress_t使用對(duì)象象視圖-查查詢數(shù)數(shù)據(jù)查詢訂貨貨單1001的的顧客信信息和訂訂貨詳細(xì)細(xì)信息SELECTDEREF(p.custref),p.shiptoaddr,p.pono,p.orderdate,line_item_listFROM purchase_viewpWHEREp.pono =1001;統(tǒng)計(jì)每一一個(gè)訂貨貨單的總總價(jià)值SELECTp.pono,p.total_value()FROM purchase_viewp;使用對(duì)象象視圖-查查詢數(shù)數(shù)據(jù)查詢定貨貨單及涉涉及貨物物1004定貨貨條目的的信息。SELECTpo.pono,po.custref.cust

38、no,CURSOR(SELECT*FROMTABLE(po.line_item_list) LWHEREL.stockref.stockno=1004)FROM purchase_viewpo;使用觸發(fā)發(fā)器更新新對(duì)象視視圖Oracle提提供INSTEADOF觸觸發(fā)器可可作為更更新復(fù)雜雜對(duì)象視視圖的方方法。每當(dāng)想要要改變對(duì)對(duì)象視圖圖中行對(duì)對(duì)象的屬屬性值時(shí)時(shí)，Oracle執(zhí)行行對(duì)象視視圖的INSTEAD OF觸發(fā)器器在觸發(fā)器器中，Oracle使使用關(guān)鍵鍵字:OLD和和:NEW存取取行對(duì)象象的當(dāng)前前值和新新值。使用觸發(fā)發(fā)器更新新對(duì)象視視圖Stock_view的INSTEADOF觸觸發(fā)器CREATEO

39、RREPLACETRIGGERstockview_insert_trINSTEAD OF INSERTONstock_viewFOREACHROWBEGININSERTINTOstock_infoVALUES( :NEW.stockno,:NEW.cost,:NEW.tax_code );END; 使用觸發(fā)發(fā)器更新新對(duì)象視視圖Customer_view的INSTEADOF觸觸發(fā)器CREATEORREPLACETRIGGER custview_insert_trINSTEAD OF INSERTONcustomer_viewFOREACHROWDECLAREphonesphone_list_t;

40、tphone1 customer_info.phone1%TYPE:=NULL;tphone2 customer_info.phone2%TYPE:=NULL;tphone3 customer_info.phone3%TYPE:=NULL;使用觸發(fā)發(fā)器更新新對(duì)象視視圖BEGINphones:=:NEW.phone_list;IFphones.COUNT2 THENtphone3 := phones(3);ENDIF;IFphones.COUNT1 THENtphone2 := phones(2);ENDIF;IFphones.COUNT0 THENtphone1 := phones(1);EN

41、DIF;使用觸發(fā)發(fā)器更新新對(duì)象視視圖INSERTINTOcustomer_infoVALUES(: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ā)發(fā)器：INSERTINTOcustomer_viewVALUES(13,EllanWhite,address_t(25 IStreet,Memphis,TN,05456),phone_list_t(615-555-1212) );使用觸發(fā)發(fā)器更新新對(duì)象視視圖Purchase_view的INSTEADOF觸觸發(fā)器CREATEOR