Oracle對象關系的設計與實現(xiàn)

綜合實例定貨系統(tǒng)訂貨系統(tǒng)—

對象關系的設計與實現(xiàn)一、系統(tǒng)簡介二、采用關系方法三、采用對象表四、采用對象視圖一、系統(tǒng)簡介系統(tǒng)涉及的實體和聯(lián)系顧客表，Customer_Info貨物表，Stock_Info訂貨單，purchase_info細目表Items_infoCustomer_Infopurchase_infoStock_Infoitems_infomn1mmn11顧客信息顧客表，Customer_Info貨物信息貨物表，Stock_Info定貨信息訂貨單，purchase_info定貨條目（細目表）items_info二、采用關系方法關系表的定義對關系表的數(shù)據(jù)操縱基于關系表的查詢與統(tǒng)計關系數(shù)據(jù)庫的解決方案采用關系方法將顧客信息，訂貨單，貨物條目清單組織成表。將地址拆成為基本元素，如省、市、街道、郵編等。設置顧客的一個可能擁有電話的最大限度，每一個電話表示為表的一列。關系方法將訂貨條目從訂貨單中分離出來，單獨存放在一張表中，該表中有指向訂貨單表和貨物表的外鍵。關系表的定義采用關系模型實現(xiàn)對現(xiàn)實世界的抽象。將實體定義為關系（表）。實體間的聯(lián)系通過外來碼或關系表實現(xiàn)。表中的字段必須是不可分割的基本單位。不允許表中嵌表。通過表的連接實現(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(stockno NUMBERPRIMARYKEY,costNUMBER,tax_codeNUMBER);CREATETABLEpurchase_info(ponoNUMBERPRIMARYKEY,custno NUMBERREFERENCEScustomer_info(custno),orderdateDATE,shiptodateDATE,shiptostreet VARCHAR2(200),shiptocityVARCHAR2(200),shiptostateVARCHAR2(200),shiptozipVARCHAR2(20));建立定貨表建立細目表CREATETABLEitems_info(lineitemnoNUMBER,ponoNUMBERREFERENCESpurchase_info(pono),stocknoNUMBERREFERENCESstock_info(stockno),quantityNUMBER,discountNUMBER,PRIMARYKEY(pono,lineitemno));向關系表表插入數(shù)數(shù)據(jù)向顧客表表插入數(shù)數(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ù)數(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);向關系表表插入數(shù)數(shù)據(jù)向定貨單單表插入入數(shù)據(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ù)據(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);關系表的的查詢查詢訂貨貨單1001的的顧客信信息和訂訂貨詳細細信息（多表連連接）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;關系表的的查詢SELECTP.pono,C.custname,P.orderdate,cursor(selectstockno,lineitemno,quantity,discountfromitems_infowherepono=p.pono)細細目目表見下下FROMcustomer_infoC,purchase_infoPWHEREP.custno=C.custnoANDP.pono=2001關系表的的查詢PONOCUSTNAMEORDERDATE細細目表見見下-------------------------------------------------------------------2001JohnNike14-10月-00CURSOR……：：4CURSORSTATEMENT：：4STOCKNOLINEITEMNOQUANTITYDISCOUNT------------------------------------------------------------------1004101010111121數(shù)據(jù)操操縱--查查詢數(shù)數(shù)據(jù)統(tǒng)計每每一個個訂貨貨單的的總價價值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ù)操操縱--維維護數(shù)數(shù)據(jù)修改數(shù)數(shù)據(jù):更改改1001訂訂貨貨單單中中貨貨物物號號為為1535的的訂訂貨貨量量UPDATEitems_infoSETquantity=20WHEREpono=1001ANDstockno=1535;刪除除數(shù)數(shù)據(jù)據(jù):刪除除編編號號為為1001的的訂訂貨貨單單DELETEFROMitems_info（（先先刪刪細細目目表表記記錄錄））WHEREpono=1001;DELETEFROMpurchase_info（（再再刪刪主主表表記記錄錄））WHEREpono=1001;三、、采采用用對對象象表表面向向對對象象解解決決方方案案定義義類類型型及及類類型型體體創(chuàng)建建對對象象表表對象象表表的的數(shù)數(shù)據(jù)據(jù)操操作作實現(xiàn)現(xiàn)對對象象關關系系的的方方法法O－－R對對象象關關系系方方法法用戶戶自自定定義義數(shù)數(shù)據(jù)據(jù)類類型型使使得得復復雜雜數(shù)數(shù)據(jù)據(jù)結結構構進進入入數(shù)數(shù)據(jù)據(jù)庫庫模模式式中中。。不將將address拆拆散散，，也也不不將將聯(lián)聯(lián)系系電電話話存存為為不不相相關關的的多多列列，，在在O－－R中中可可以以定定義義特特殊殊的的類類型型表表示示。。不將將訂訂貨貨條條目目以以單單獨獨表表管管理理，，O－－R中中將將他他們們作作為為相相應應訂訂貨貨表表的的嵌嵌套套表表。。收集集類類型型作作為為多多值值屬屬性性在O－－R方方法法中中，，主主要要實實體體顧客，貨物，訂貨單變?yōu)閷ο笙?。對象引用用表示他他們之間間n:1的關系系，定義類型型定貨系統(tǒng)統(tǒng)中的類類型：地址address_t對對象類類型多個電話話phone_list_t變長數(shù)組組類型顧客customer_info_t對對象類型型貨物stock_info_t對對象類類型貨物條目目line_item_t對對象象類型多個貨物物條目line_item_list_t嵌嵌套表表類型定貨單purchase_info_t對對象象類型定義類型型定義一個個對象類類型表示示地址信信息CREATETYPEaddress_tASOBJECT(streetVARCHAR2(200),cityVARCHAR2(200),stateCHAR(2),zipVARCHAR2(20));定義一個個數(shù)組類類型，表表示顧客客的幾部部電話CREATETYPEphone_list_tASVARRAY(10)OFVARCHAR2(20);定義類型型定義一個個對象類類型表示示定貨條條目信息息CREATETYPEline_item_tASOBJECT(lineitemnoNUMBER,STOCKREFREFstock_info_t,quantityNUMBER,discountNUMBER);定義一個個嵌套表表類型，，表示定定貨單中中的貨物物條目信信息CREATETYPEline_item_list_tASTABLEOFline_item_t;定義類型型定義一個個對象類類型表示示顧客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));定義類型型定義一個個對象類類型表示示貨物信信息CREATETYPEstock_info_tASOBJECT(stocknoNUMBER,costNUMBER,tax_codeNUMBER);定義類型型定義一個個對象類類型表示示定貨單單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ù)數(shù)組的比比較對顧客中中的電話話來說VARRAY的的元素是是有序的的VARRAY要要求預置置元素數(shù)數(shù)目對定貨單單中的貨貨物條目目來說嵌套表沒沒有上界界的限制制嵌套表可可以直接接做查詢詢可以對嵌嵌套表做做索引定義方法法定義customer_info_t對象象類型的的方法：：CREATEORREPLACETYPEBODYcustomer_info_tASORDERMEMBERFUNCTIONcust_order(xINcustomer_info_t)RETURNINTEGERISBEGINRETURNcustno-x.custno;END;END;顧客對象象的比較較是顧客客號的比比較定義方法法定義purchase_info_t對象象類型的的方法：：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)建對象象表一般來講講，可以以按下面面規(guī)則理理解“對對象”和和“表””之間的的關系：：類，即客客觀世界界的實體體，對應應于表對象屬性性對應于于表的列列對象對應應于表的的記錄每一個表表是一個個隱式的的類，它它的對象象（記錄錄）都有有相同的的屬性（（列）創(chuàng)建對象表定義對象表customer_tabCREATETABLEcustomer_tabOFcustomer_info_t(custnoPRIMARYKEY);對象類型包含含四個屬性：：custnoNUMBERcustnameVARCHAR2(200)addressaddress_tphone_listphone_list_t創(chuàng)建對象表對象類型作為為創(chuàng)建對象表表的模板約束用于表上上，不能定義義在類型上（（如PRIMARYKEY的約束束定義）表可以包含對對象列，由于于變長數(shù)組phone_list_t包含的數(shù)數(shù)量少于10x20，所所以ORACLE將其作作為一個存儲儲單元，當超超過4000byte時時，將以BLOB類型存存于表外不為對象類型型分配存儲空空間，僅為表表分配記錄空空間創(chuàng)建對象表對象表stock_tab的定義：：CREATETABLEstock_tabOFstock_info_t(stocknoPRIMARYKEY);STOCK_INFO_T對象類型型包含三個屬屬性：stocknoNUMBER,costNUMBER,tax_codeNUMBER創(chuàng)建對象表對象表purchase_tab的的定義：CREATETABLEpurchase_tabOFpurchase_info_t(PRIMARYKEY(pono),SCOPEFOR(custref)IScustomer_tab)NESTEDTABLEline_item_listSTOREASpo_line_tab;purchase_info_T對象象類型的屬屬性包括：：ponoNUMBER，，custrefREFcustomer_info_t，orderdateDATE，，shipdateDATE，line_item_listline_item_list_t，shiptoaddraddress_t創(chuàng)建對象表表REF操作作符：如果未作限限制，REF允許引引用任意類類型為customer_info_t表的行行對象。SCOPE約束只只是限制purchase_tab對對象表的custref列的的取值范圍圍。嵌套表：十分適合于于表示表間間的主從關關系，從而而可以避免免DML中中的關系聯(lián)聯(lián)接（JOIN）。。一個嵌套表表的所有記記錄存儲在在一個獨立立的存儲表表中，在該該存儲表中中有一個隱隱含的列NESTED_TABLE_ID與相相應的父表表記錄相對對應，在嵌嵌套表中的的記錄與父父表中的記記錄根據(jù)NESTED_TABLE_ID對應應。嵌套表類型型的各個屬屬性對應于于存儲表的的各列。維護嵌套表表修改嵌套表表的存儲表表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);向對象表插插入數(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'));向對象表插插入數(shù)據(jù)向對象表插插入數(shù)據(jù)插入purchase_tab數(shù)據(jù)INSERTINTOpurchase_tabSELECT1001,REF(C),SYSDATE,'10-MAY-1997',line_item_list_t(),NULLFROMcustomer_tabCWHEREC.custno=1;上面的語句句用下列屬屬性創(chuàng)建了了一個purchase_info_T對象：：pono=1001custref=對對于顧客客1的引用用orderdate=SYSDATEshipdate=10-MAY-1997line_item_list=一一個空的貨貨物列表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;向對象表插插入數(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));修改嵌套表表修改定單表表中單號為為2001記記錄的嵌套套表，將條條目號為11的貨物物指針指向向1011號貨物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對象大大小比較，，隱含調用用：MAPMEMBERFUNCTIONret_valueRETURNNUMBERISBEGINRETURNpono;END;相當于orderbypono數(shù)據(jù)操縱--查詢數(shù)數(shù)據(jù)查詢訂貨單單1001的顧客信信息和訂貨貨詳細信息息SELECTDEREF(p.custref),p.shiptoaddr,p.pono,p.orderdate,line_item_listFROMpurchase_tabpWHEREp.pono=1001;每一個訂貨貨單的總價價值SELECTp.pono,p.total_value()FROMpurchase_tabp;數(shù)據(jù)操縱--查詢數(shù)數(shù)據(jù)查詢定貨單單及涉及貨貨物1004定貨條條目的信息息。SELECTpo.pono,po.custref.custno,CURSOR(SELECT*FROMTABLE(po.line_item_list)LWHEREL.stockref.stockno=1004)FROMpurchase_tabpo;數(shù)據(jù)操縱--刪除數(shù)數(shù)據(jù)在下面的刪刪除例子中中，Oracle自自動刪除所所有屬于訂訂貨單的貨貨物條目，，而在原關關系模型中中必須要考考慮到兩張張表的刪除除問題。刪除訂貨單單1001：DELETE FROMpurchase_tabWHEREpono=1001;四、采用對對象視圖對象視圖解解決方案（（基于關系系表）定義對象視視圖對象視圖的的查詢使用觸發(fā)器器更新對象象視圖對象視圖解解決方案對象視圖是是虛擬對象象表，數(shù)據(jù)據(jù)源取自表表和視圖。。采用對象表表實現(xiàn)系統(tǒng)統(tǒng)設計一般般采用如下下步驟建立實體和和關系通過創(chuàng)建和和填充關系系表實現(xiàn)實實體關系結結構采用UDT表示對象象關系模式式，模型化化一個實體體關系結構構采用O-R模式創(chuàng)建建和填充對對象表實現(xiàn)現(xiàn)實體關系系結構采用對象視視圖方式要要使用相同同的初始步步驟，但最最后一步有有所不同，，它不是通通過創(chuàng)建和和填充對象象表的方式式，而是使使用對象視視圖來表示示虛擬對象象表，數(shù)據(jù)據(jù)取自一般般的關系表表定義對象視視圖三個對象視視圖：Customer_view,Stock_view,Purchase_view。。創(chuàng)建建對對象象視視圖圖的的語語句句有有四四個個部部分分：：視圖圖的的名名字字視圖圖所所基基于于的的對對象象類類型型的的名名字字基于于主主碼碼創(chuàng)創(chuàng)建建對對象象標標識識OID)一個個選選擇擇語語句句根根據(jù)據(jù)對對應應的的對對象象類類型型向向虛虛擬擬對對象象表表中中填填充充數(shù)數(shù)據(jù)據(jù)后面面例例子子借借用用前前面面的的幾幾個個類類型型定定義義定義義對對象象視視圖圖Customer_view對對象象視視圖圖：：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;定義義對對象象視視圖圖Stock_view對對象象視視圖圖CREATEORREPLACEVIEWstock_viewOFstock_info_tWITHOBJECTOID(stockno)ASSELECT*FROMstock_info;定義義對對象象視視圖圖Purchase_view對對象象視視圖圖CREATEORREPLACEVIEWpurchase_viewOFpurchase_info_tWITHOBJECTOID(pono)ASSELECTP.pono,,P.orderdate,P.shiptodate,,address_t(P.shiptostreet,P.shiptocity,P.shiptostate,P.shiptozip)FROMpurchase_infoP;客戶戶對對象象引引用用嵌套套表表列列構造造視視圖圖的的對對象象引引用用列列MAKE_REF(customer_view,P.custno),MAKE_REF(對對象象表表/視視圖圖，，定定位位記記錄錄的的主主鍵鍵值值））返回回指指向向對對象象表表/視視圖圖的的一一個個對對象象（（記記錄錄））的的REF客戶戶對對象象引引用用構造造視視圖圖的的嵌嵌套套表表列列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),嵌套套表表列列定義義對對象象視視圖圖purchase_info_t對對象象類類型型有有如如下下屬屬性性：：ponoNUMBERcustrefREFcustomer_info_torderdateDATEshipdateDATEline_item_listline_item_list_tshiptoaddraddress_t使用對象象視圖--查查詢數(shù)數(shù)據(jù)查詢訂貨貨單1001的的顧客信信息和訂訂貨詳細細信息SELECTDEREF(p.custref),p.shiptoaddr,p.pono,p.orderdate,line_item_listFROMpurchase_viewpWHEREp.pono=1001;統(tǒng)計每一一個訂貨貨單的總總價值SELECTp.pono,p.total_value()FROMpurchase_viewp;使用對象象視圖--查查詢數(shù)數(shù)據(jù)查詢定貨貨單及涉涉及貨物物1004定貨貨條目的的信息。。SELECTpo.pono,po.custref.custno,CURSOR(SELECT*FROMTABLE(po.line_item_list)LWHEREL.stockref.stockno=1004)FROMpurchase_viewpo;使用觸發(fā)發(fā)器更新新對象視視圖Oracle提提供INSTEADOF觸觸發(fā)器可可作為更更新復雜雜對象視視圖的方方法。每當想要要改變對對象視圖圖中行對對象的屬屬性值時時，Oracle執(zhí)行行對象視視圖的INSTEADOF觸發(fā)器器在觸發(fā)器器中，Oracle使使用關鍵鍵字:OLD和和:NEW存取取行對象象的當前前值和新新值。使用觸發(fā)發(fā)器更新新對象視視圖Stock_view的INSTEADOF觸觸發(fā)器CREATEORREPLACETRIGGERstockview_insert_trINSTEADOFINSERTONstock_viewFOREACHROWBEGININSERTINTOstock_infoVALUES(:NEW.stockno,:NEW.cost,:NEW.tax_code);END;使用觸發(fā)發(fā)器更新新對象視視圖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ā)器更新新對象視視圖BEGINphones:=:NEW.phone_list;IFphones.COUNT>2THENtphone3:=phones(3);ENDIF;IFphones.COUNT>1THENtphone2:=phones(2);ENDIF;IFphones.COUNT>0THENtphone1:=phones(1);ENDIF;使用觸發(fā)發(fā)器更新新對象視視圖INSERTINTOcustomer_infoVALUES(:NEW.custno,:NEW.custname,:NEW.address.street,:NEW.address.city,:NEW.address.state,:NEW.address.zip,tphone1,tphone2,tphone3);END;向對象視視圖插入入數(shù)據(jù)下列語句句激活了了customer_view觸發(fā)發(fā)器：INSERTINTOcustomer_viewVALUES(13,`EllanWhite',address_t(`25IS