Oracle訂貨系統(tǒng)綜合知識(shí)概述

綜合實(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)系顧客表，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ù)的解決方案采用關(guān)系方法將顧客信息，訂貨單，貨物條目清單組織成表。將地址拆成為基本元素，如省、市、街道、郵編等。設(shè)置顧客的一個(gè)可能擁有電話的最大限度，每一個(gè)電話表示為表的一列。關(guān)系方法將訂貨條目從訂貨單中分離出來(lái)，單獨(dú)存放在一張表中，該表中有指向訂貨單表和貨物表的外鍵。關(guān)系表的定義采用關(guān)系模型實(shí)現(xiàn)對(duì)現(xiàn)實(shí)世界的抽象。將實(shí)體定義為關(guān)系（表）。實(shí)體間的聯(lián)系通過(guò)外來(lái)碼或關(guān)系表實(shí)現(xiàn)。表中的字段必須是不可分割的基本單位。不允許表中嵌表。通過(guò)表的連接實(shí)現(xiàn)多表查詢。CREATETABLEcustomer_info( custno NUMBERPRIMARYKEY, custname VARCHAR2(200), street VARCHAR2(200), city VARCHAR2(200),stateVARCHAR2(200), zip VARCHAR2(6), phone1 VARCHAR2(20), phone2 VARCHAR2(20), phone3 VARCHAR2(20));建立顧客表建立貨物表表CREATETABLEstock_info(stocknoNUMBERPRIMARYKEY,costNUMBER,tax_codeNUMBER);CREATETABLEpurchase_info(ponoNUMBERPRIMARYKEY,custno NUMBERREFERENCEScustomer_info(custno),orderdate DATE,shiptodate DATE,shiptostreetVARCHAR2(200),shiptocityVARCHAR2(200),shiptostateVARCHAR2(200),shiptozip VARCHAR2(20));建立定貨表表建立細(xì)目表表CREATETABLEitems_info(lineitemnoNUMBER,ponoNUMBERREFERENCESpurchase_info(pono),stocknoNUMBERREFERENCESstock_info(stockno),quantityNUMBER,discountNUMBER,PRIMARYKEY(pono,lineitemno));向關(guān)系表插插入數(shù)據(jù)向顧客表插插入數(shù)據(jù)INSERTINTOcustomer_infoVALUES(1,'JeanNance','2AvocetDrive',‘RedwoodShores','CA','95054','415-555-1212',NULL,NULL);INSERTINTOcustomer_infoVALUES(2,'JohnNike','323CollegeDrive','Edison','NJ','08820','609-555-1212','201-555-1212',NULL);向貨物表插插入數(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)關(guān)系系表表插插入入數(shù)數(shù)據(jù)據(jù)向定定貨貨單單表表插插入入數(shù)數(shù)據(jù)據(jù)INSERTINTOpurchase_infoVALUES(1001,1,SYSDATE,‘‘10-5月-1997',NULL,NULL,NULL,NULL);INSERTINTOpurchase_infoVALUES(2001,2,SYSDATE,‘‘20-5月-1997','55MadisonAve','Madison','WI','53715');向定貨單條目目插入數(shù)據(jù)INSERTINTOitems_infoVALUES(01,1001,1534,12,0);INSERTINTOitems_infoVALUES(02,1001,1535,10,10);INSERTINTOitems_infoVALUES(10,2001,1004,1,0);INSERTINTOitems_infoVALUES(11,2001,1011,2,1);關(guān)系表的查詢?cè)儾樵冇嗀泦?001的顧顧客信息和訂訂貨詳細(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_infoLWHEREC.custno=P.custnoANDP.pono=L.ponoANDP.pono=1001;關(guān)系表的查詢?cè)僑ELECTP.pono,C.custname,P.orderdate,cursor(selectstockno,lineitemno,quantity,discountfromitems_infowherepono=p.pono)細(xì)細(xì)目表見(jiàn)下FROMcustomer_infoC,purchase_infoPWHEREP.custno=C.custnoANDP.pono=2001關(guān)系表的查詢?cè)働ONOCUSTNAMEORDERDATE細(xì)細(xì)目表見(jiàn)見(jiàn)下-------------------------------------------------------------------2001JohnNike14-10月月-00CURSOR……：4CURSORSTATEMENT：4STOCKNOLINEITEMNOQUANTITYDISCOUNT------------------------------------------------------------------1004101010111121數(shù)據(jù)操縱--查詢數(shù)據(jù)統(tǒng)計(jì)每一個(gè)訂訂貨單的總價(jià)價(jià)值SELECTL.pono,SUM(S.cost*L.quantity)FROMitems_infoL,stock_infoSWHEREL.stockno=S.stocknoGROUPBYL.pono;查詢涉及貨物物1004的的訂貨單及及訂貨條目信信息SELECTP.pono,P.custno,L.stockno,L.lineitemno,L.quantity,L.discountFROMpurchase_infoP,items_infoLWHEREP.pono=L.ponoANDL.stockno=1004;數(shù)據(jù)操縱--維護(hù)數(shù)據(jù)修改數(shù)據(jù):更改1001訂貨單中貨貨物號(hào)為1535的訂貨貨量UPDATEitems_infoSETquantity=20WHEREpono=1001ANDstockno=1535;刪除數(shù)據(jù):刪除編號(hào)為1001的訂訂貨單DELETEFROMitems_info（（先刪細(xì)目表表記錄）WHEREpono=1001;DELETEFROMpurchase_info（（再刪主表表記錄）WHEREpono=1001;三、采用對(duì)象象表面向?qū)ο蠼鉀Q決方案定義類型及類類型體創(chuàng)建對(duì)象表對(duì)象表的數(shù)據(jù)據(jù)操作實(shí)現(xiàn)對(duì)象關(guān)系系的方法O－R對(duì)對(duì)象關(guān)系方方法用戶自定義數(shù)數(shù)據(jù)類型使得得復(fù)雜數(shù)據(jù)結(jié)結(jié)構(gòu)進(jìn)入數(shù)據(jù)據(jù)庫(kù)模式中。。不將address拆散散，也不將聯(lián)聯(lián)系電話存為為不相關(guān)的多多列，在O－－R中可以定定義特殊的類類型表示。不將訂貨條目目以單獨(dú)表管管理，O－R中將他們作作為相應(yīng)訂貨貨表的嵌套表表。收集集類類型型作作為為多多值值屬屬性性在O－－R方方法法中中，，主主要要實(shí)實(shí)體體顧客客，貨物物，訂貨貨單單變?yōu)闉閷?duì)對(duì)象象。。對(duì)象象引引用用表表示示他他們們之之間間n:1的的關(guān)關(guān)系系，，定義義類類型型定貨貨系系統(tǒng)統(tǒng)中中的的類類型型：：地址址address_t對(duì)對(duì)象象類類型型多個(gè)個(gè)電電話話phone_list_t變長(zhǎng)長(zhǎng)數(shù)數(shù)組組類類型型顧客客customer_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è)對(duì)對(duì)象類類型表表示地地址信信息CREATETYPEaddress_tASOBJECT(streetVARCHAR2(200),cityVARCHAR2(200),stateCHAR(2),zipVARCHAR2(20));定義一一個(gè)數(shù)數(shù)組類類型，，表示示顧客客的幾幾部電電話CREATETYPEphone_list_tASVARRAY(10)OFVARCHAR2(20);定義類類型定義一一個(gè)對(duì)對(duì)象類類型表表示定定貨條條目信信息CREATETYPEline_item_tASOBJECT(lineitemnoNUMBER,STOCKREFREFstock_info_t,quantityNUMBER,discountNUMBER);定義一一個(gè)嵌嵌套表表類型型，表表示定定貨單單中的的貨物物條目目信息息CREATETYPEline_item_list_tASTABLEOFline_item_t;定義類類型定義一一個(gè)對(duì)對(duì)象類類型表表示顧顧客CREATETYPEcustomer_info_tASOBJECT(custnoNUMBER,custnameVARCHAR2(200),addressaddress_t,phone_listphone_list_t,ORDERMEMBERFUNCTIONcust_order(xINcustomer_info_t)RETURNINTEGER,PRAGMARESTRICT_REFERENCES(cust_order,WNDS,WNPS,RNPS,RNDS));定義類類型定義一一個(gè)對(duì)對(duì)象類類型表表示貨貨物信信息CREATETYPEstock_info_tASOBJECT(stocknoNUMBER,costNUMBER,tax_codeNUMBER);定義類型定義一個(gè)對(duì)對(duì)象類型表表示定貨單單CREATETYPEpurchase_info_tASOBJECT(ponoNUMBER,custrefREFcustomer_info_t,orderdateDATE,shipdateDATE,line_item_listline_item_list_t,shiptoaddraddress_t,MAPMEMBERFUNCTIONret_valueRETURNNUMBER,PRAGMARESTRICT_REFERENCES(ret_value,WNDS,WNPS,RNPS,RNDS),MEMBERFUNCTIONtotal_valueRETURNNUMBER,PRAGMARESTRICT_REFERENCES(total_value,WNDS,WNPS));嵌套表和可可變數(shù)組的的比較對(duì)顧客中的的電話來(lái)說(shuō)說(shuō)VARRAY的元素素是有序的的VARRAY要求預(yù)預(yù)置元素?cái)?shù)數(shù)目對(duì)定貨單中中的貨物條條目來(lái)說(shuō)嵌套表沒(méi)有有上界的限限制嵌套表可以以直接做查查詢可以對(duì)嵌套套表做索引引定義方法定義customer_info_t對(duì)象類型型的方法：：CREATEORREPLACETYPEBODYcustomer_info_tASORDERMEMBERFUNCTIONcust_order(xINcustomer_info_t)RETURNINTEGERISBEGINRETURNcustno-x.custno;END;END;顧客對(duì)象的的比較是顧顧客號(hào)的比比較定義方法定義purchase_info_t對(duì)象類型型的方法：：CREATEORREPLACETYPEBODYpurchase_info_tASMAPMEMBERFUNCTIONret_valueRETURNNUMBERISBEGINRETURNpono;END;定義方法MEMBERFUNCTIONtotal_valueRETURNNUMBERISiINTEGER;stockstock_info_t;--(stockno,cost,tax_code)line_itemline_item_t;--(lineitemnoSTOCKREFquantitydiscount)totalNUMBER:=0;BEGINFORiIN1..SELF.line_item_list.COUNTLOOPline_item:=SELF.line_item_list(i);SELECTDEREF(line_item.stockref)INTOstockFROMDUAL;total:=total+line_item.quantity*stock.cost;ENDLOOP;RETURNtotal;END;END;創(chuàng)建對(duì)象表一般來(lái)講，可可以按下面規(guī)規(guī)則理解“對(duì)對(duì)象”和“表表”之間的關(guān)關(guān)系：類，即客觀世世界的實(shí)體，，對(duì)應(yīng)于表對(duì)象屬性對(duì)應(yīng)應(yīng)于表的列對(duì)象對(duì)應(yīng)于表表的記錄每一個(gè)表是一一個(gè)隱式的類類，它的對(duì)象象（記錄）都都有相同的屬屬性（列）創(chuàng)建對(duì)象表定義對(duì)象表customer_tabCREATETABLEcustomer_tabOFcustomer_info_t(custnoPRIMARYKEY);對(duì)象類型包含含四個(gè)屬性：：custnoNUMBERcustnameVARCHAR2(200)addressaddress_tphone_listphone_list_t創(chuàng)建建對(duì)對(duì)象象表表對(duì)象象類類型型作作為為創(chuàng)創(chuàng)建建對(duì)對(duì)象象表表的的模模板板約束束用用于于表表上上，，不不能能定定義義在在類類型型上上（（如如PRIMARYKEY的的約約束束定定義義））表可可以以包包含含對(duì)對(duì)象象列列，，由由于于變變長(zhǎng)長(zhǎng)數(shù)數(shù)組組phone_list_t包包含含的的數(shù)數(shù)量量少少于于10x20，，所所以以O(shè)RACLE將將其其作作為為一一個(gè)個(gè)存存儲(chǔ)儲(chǔ)單單元元，，當(dāng)當(dāng)超超過(guò)過(guò)4000byte時(shí)時(shí)，，將將以以BLOB類類型型存存于于表表外外不為為對(duì)對(duì)象象類類型型分分配配存存儲(chǔ)儲(chǔ)空空間間，，僅僅為為表表分分配配記記錄錄空空間間創(chuàng)建建對(duì)對(duì)象象表表對(duì)象象表表stock_tab的的定定義義：：CREATETABLEstock_tabOFstock_info_t(stocknoPRIMARYKEY);STOCK_INFO_T對(duì)對(duì)象象類類型型包包含含三三個(gè)個(gè)屬屬性性：：stocknoNUMBER,costNUMBER,tax_codeNUMBER創(chuàng)建建對(duì)對(duì)象象表表對(duì)象象表表purchase_tab的的定定義義：：CREATETABLEpurchase_tabOFpurchase_info_t(PRIMARYKEY(pono),SCOPEFOR(custref)IScustomer_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允許引引用任意意類型為為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_tabADD(SCOPEFOR(stockref)ISstock_tab);ALTERTABLEpo_line_tabSTORAGE(NEXT5KPCTINCREASE5MINEXTENTS1MAXEXTENTS20);插入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.23,2);向?qū)ο蟊聿宀迦霐?shù)據(jù)插入customer_tab數(shù)據(jù)INSERTINTOcustomer_tabVALUES(1,`JeanNance',address_t(`2AvocetDrive',`RedwoodShores',`CA',`95054'),phone_list_t(`415-555-1212'));INSERTINTOcustomer_tabVALUES(2,`JohnNike',address_t(`323CollegeDrive',`Edison',`NJ',`08820'),phone_list_t(`609-555-1212',`201-555-1212'));向?qū)ο蟊聿宀迦霐?shù)據(jù)向?qū)ο蟊聿宀迦霐?shù)據(jù)插入purchase_tab數(shù)據(jù)INSERTINTOpurchase_tabSELECT1001,REF(C),SYSDATE,'10-MAY-1997',line_item_list_t(),NULLFROMcustomer_tabCWHEREC.custno=1;上面的語(yǔ)句句用下列屬屬性創(chuàng)建了了一個(gè)purchase_info_T對(duì)象：：pono=1001custref=對(duì)對(duì)于顧客客1的引用用orderdate=SYSDATEshipdate=10-MAY-1997line_item_list=一一個(gè)空的貨貨物列表shiptoaddr=NULL向嵌套表插插入數(shù)據(jù)INSERTINTOTHE(SELECTP.line_item_listFROMpurchase_tabPWHEREP.pono=1001)SELECT01,REF(S),12,0FROMstock_tabSWHERES.stockno=1534;INSERTINTOTHE(SELECTP.line_item_listFROMpurchase_tabPWHEREP.pono=1001)SELECT02,REF(S),10,10FROMstock_tabSWHERES.stockno=1535;向?qū)ο蟊聿宀迦霐?shù)據(jù)INSERTINTOpurchase_tabSELECT2001,REF(C),SYSDATE,'20-MAY-1997',line_item_list_t(),address_t(`55Madisonve','Madison','WI','53715')FROMcustomer_tabCWHEREC.custno=2;向嵌套表插插入數(shù)據(jù)INSERTINTOTHE(SELECTP.line_item_listFROMpurchase_tabPWHEREP.pono=2001)SELECT10,REF(S),1,0FROMstock_tabSWHERES.stockno=1004;INSERTINTOTHE(SELECTP.line_item_listFROMpurchase_tabPWHEREP.pono=2001)VALUES(line_item_t(11,NULL,2,1));修改改嵌嵌套套表表修改改定定單單表表中中單單號(hào)號(hào)為為2001記記錄錄的的嵌嵌套套表表，，將將條條目目號(hào)號(hào)為為11的的貨貨物物指指針針指指向向1011號(hào)號(hào)貨貨物物UPDATETHE(SELECTP.line_item_listFROMpurchase_tabPWHEREP.pono=2001)plistSETplist.stockref=(SELECTREF(S)FROMstock_tabSWHERES.stockno=1011)WHEREplist.lineitemno=11;使用用排排序序方方法法排序序方方法法的引引用用SELECTp.ponoFROMpurchase_tabpORDERBYVALUE(p);按purchase_tab對(duì)對(duì)象象大大小小比比較較，，隱隱含含調(diào)調(diào)用用：：MAPMEMBERFUNCTIONret_valueRETURNNUMBERISBEGINRETURNpono;END;相當(dāng)當(dāng)于于orderbypono數(shù)據(jù)操縱--查詢數(shù)據(jù)查詢訂貨單1001的顧顧客信息和訂訂貨詳細(xì)信息息SELECTDEREF(p.custref),p.shiptoaddr,p.pono,p.orderdate,line_item_listFROMpurchase_tabpWHEREp.pono=1001;每一個(gè)訂貨單單的總價(jià)值SELECTp.pono,p.total_value()FROMpurchase_tabp;數(shù)據(jù)操縱--查詢數(shù)據(jù)查詢定貨單及及涉及貨物1004定貨貨條目的信息息。SELECTpo.pono,po.custref.custno,CURSOR(SELECT*FROMTABLE(po.line_item_list)LWHEREL.stockref.stockno=1004)FROMpurchase_tabpo;數(shù)據(jù)操縱--刪除數(shù)據(jù)在下面的刪除除例子中，Oracle自動(dòng)刪除所所有屬于訂貨貨單的貨物條條目，而在原原關(guān)系模型中中必須要考慮慮到兩張表的的刪除問(wèn)題。。刪除訂貨單1001：DELETE FROMpurchase_tabWHEREpono=1001;四、采用對(duì)象象視圖對(duì)象視圖解決決方案（基于于關(guān)系表）定義對(duì)象視圖圖對(duì)象視圖的查查詢使用觸發(fā)器更更新對(duì)象視圖圖對(duì)象視圖解決決方案對(duì)象視圖是虛虛擬對(duì)象表，，數(shù)據(jù)源取自自表和視圖。。采用對(duì)象表實(shí)實(shí)現(xiàn)系統(tǒng)設(shè)計(jì)計(jì)一般采用如如下步驟建立實(shí)實(shí)體和和關(guān)系系通過(guò)創(chuàng)創(chuàng)建和和填充充關(guān)系系表實(shí)實(shí)現(xiàn)實(shí)實(shí)體關(guān)關(guān)系結(jié)結(jié)構(gòu)采用UDT表示示對(duì)象象關(guān)系系模式式，模模型化化一個(gè)個(gè)實(shí)體體關(guān)系系結(jié)構(gòu)構(gòu)采用O-R模式式創(chuàng)建建和填填充對(duì)對(duì)象表表實(shí)現(xiàn)現(xiàn)實(shí)體體關(guān)系系結(jié)構(gòu)構(gòu)采用對(duì)對(duì)象視視圖方方式要要使用用相同同的初初始步步驟，，但最最后一一步有有所不不同，，它不不是通通過(guò)創(chuàng)創(chuàng)建和和填充充對(duì)象象表的的方式式，而而是使使用對(duì)對(duì)象視視圖來(lái)來(lái)表示示虛擬擬對(duì)象象表，，數(shù)據(jù)據(jù)取自自一般般的關(guān)關(guān)系表表定義對(duì)象視視圖三個(gè)對(duì)象視圖圖：Customer_view,Stock_view,Purchase_view。。創(chuàng)建對(duì)象視圖圖的語(yǔ)句有四四個(gè)部分：視圖的名字視圖所基于的的對(duì)象類型的的名字基于主碼創(chuàng)建建對(duì)象標(biāo)識(shí)OID)一個(gè)選擇語(yǔ)句句根據(jù)對(duì)應(yīng)的的對(duì)象類型向向虛擬對(duì)象表表中填充數(shù)據(jù)據(jù)后面例子借用用前面的幾個(gè)個(gè)類型定義定義對(duì)象視圖圖Customer_view對(duì)象視視圖：CREATEORREPLACEVIEWcustomer_viewOFcustomer_info_tWITHOBJECTOID(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ì)對(duì)象視圖CREATEORREPLACEVIEWstock_viewOFstock_info_tWITHOBJECTOID(stockno)ASSELECT*FROMstock_info;定義對(duì)象視圖圖Purchase_view對(duì)象視視圖CREATEORREPLACEVIEWpurchase_viewOFpurchase_info_tWITHOBJECTOID(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ì)象表/視圖，定定位記錄的的主鍵值））返回指向?qū)?duì)象表/視視圖的一個(gè)個(gè)對(duì)象（記記錄）的REF客戶對(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ì)象象類型有如如下屬性：：ponoNUMBERcustrefREFcustomer_info_torderdateDATEshipdateDATEline_item_listline_item_list_tshiptoaddraddress_t使用對(duì)象視視圖--查詢數(shù)數(shù)據(jù)查詢訂貨單單1001的顧客信信息和訂貨貨詳細(xì)信息息SELECTDEREF(p.custref),p.shiptoaddr,p.pono,p.orderdate,line_item_listFROMpurchase_viewpWHEREp.pono=1001;統(tǒng)計(jì)每一個(gè)個(gè)訂貨單的的總價(jià)值SELECTp.pono,p.total_value()FROMpurchase_viewp;使用對(duì)象視視圖--查詢數(shù)數(shù)據(jù)查詢定貨單單及涉及貨貨物1004定貨條條目的信息息。SELECTpo.pono,po.custref.custno,CURSOR(SELECT*FROMTABLE(po.line_item_list)LWHEREL.stockref.stockno=1004)FROMpurchase_viewpo;使用觸發(fā)器器更新對(duì)象象視圖Oracle提供INSTEADOF觸發(fā)器器可作為更更新復(fù)雜對(duì)對(duì)象視圖的的方法。每當(dāng)想要改改變對(duì)象視視圖中行對(duì)對(duì)象的屬性性值時(shí)，Oracle執(zhí)行對(duì)對(duì)象視圖的的INSTEADOF觸發(fā)發(fā)器在觸發(fā)器器中，Oracle使使用關(guān)鍵鍵字:OLD和和:NEW存取取行對(duì)象象的當(dāng)前前值和新新值。使用觸發(fā)發(fā)器更新新對(duì)象視視圖Stock_view的INSTEADOF觸觸發(fā)器CREATEORREPLACETRIGGERstockview_insert_trINSTEADOFINSERTONstock_viewFOREACHROWBEGININSERTINTOstock_infoVALUES(:NEW.stockno,:NEW.cost,:NEW.tax_code);END;使用觸發(fā)發(fā)器更新新對(duì)象視視圖Customer_view的INSTEADOF觸觸發(fā)器CREATEORREPLACETRIGGERcustview_insert_trINSTEADOFINSERTONcustomer_viewFOREACHROWDECLAREphonesphone_list_t;tphone1customer_info.phone1%TYPE:=NULL;tphone2customer_info.phone2%TYPE:=NULL;tphone3customer_info.phone3%TYPE:=NULL;使用觸發(fā)發(fā)器更新新對(duì)象視視圖BEGINphones:=:NEW.phone_list;IFphones.COUNT>2THENtphone3:=phones(3);ENDIF;IFphones.COUNT>1THENtphone2:=phones(2);ENDIF;IFphones.COUNT>0THENtphone1:=phones(1);ENDIF;使用觸發(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ā)器：INSERTINTOcustomer_viewVALUES(13,`EllanWhite',address_t(`25IStreet',`Me