Oracle对象关系的设计与实现

综合实例定货系统订货系统—

对象关系的设计与实现一、系统简介二、采用关系方法三、采用对象表四、采用对象视图一、系统简介系统涉及的实体和联系顾客表，Customer_Info货物表，Stock_Info订货单，purchase_info细目表Items_infoCustomer_Infopurchase_infoStock_Infoitems_infomn1mmn11顾客信息顾客表，Customer_Info货物信息货物表，Stock_Info定货信息订货单，purchase_info定货条目（细目表）items_info二、采用关系方法关系表的定义对关系表的数据操纵基于关系表的查询与统计关系数据库的解决方案采用关系方法将顾客信息，订货单，货物条目清单组织成表。将地址拆成为基本元素，如省、市、街道、邮编等。设置顾客的一个可能拥有电话的最大限度，每一个电话表示为表的一列。关系方法将订货条目从订货单中分离出来，单独存放在一张表中，该表中有指向订货单表和货物表的外键。关系表的定义采用关系模型实现对现实世界的抽象。将实体定义为关系（表）。实体间的联系通过外来码或关系表实现。表中的字段必须是不可分割的基本单位。不允许表中嵌表。通过表的连接实现多表查询。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));向关系表表插入数数据向顾客表表插入数数据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);向货物表表插入数数据INSERTINTOstock_infoVALUES(1004,6750.00,2);INSERTINTOstock_infoVALUES(1011,4500.23,2);INSERTINTOstock_infoVALUES(1534,2234.00,2);INSERTINTOstock_infoVALUES(1535,3456.23,2);向关系表表插入数数据向定货单单表插入入数据INSERTINTOpurchase_infoVALUES(1001,1,SYSDATE,‘10-5月-1997',NULL,NULL,NULL,NULL);INSERTINTOpurchase_infoVALUES(2001,2,SYSDATE,‘‘20-5月-1997','55MadisonAve','Madison','WI','53715');向定货单单条目插插入数据据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数据操操纵--查查询数数据统计每每一个个订货货单的的总价价值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;数据操操纵--维维护数数据修改数数据:更改改1001订订货货单单中中货货物物号号为为1535的的订订货货量量UPDATEitems_infoSETquantity=20WHEREpono=1001ANDstockno=1535;删除除数数据据:删除除编编号号为为1001的的订订货货单单DELETEFROMitems_info（（先先删删细细目目表表记记录录））WHEREpono=1001;DELETEFROMpurchase_info（（再再删删主主表表记记录录））WHEREpono=1001;三、、采采用用对对象象表表面向向对对象象解解决决方方案案定义义类类型型及及类类型型体体创建建对对象象表表对象象表表的的数数据据操操作作实现现对对象象关关系系的的方方法法O－－R对对象象关关系系方方法法用户户自自定定义义数数据据类类型型使使得得复复杂杂数数据据结结构构进进入入数数据据库库模模式式中中。。不将将address拆拆散散，，也也不不将将联联系系电电话话存存为为不不相相关关的的多多列列，，在在O－－R中中可可以以定定义义特特殊殊的的类类型型表表示示。。不将将订订货货条条目目以以单单独独表表管管理理，，O－－R中中将将他他们们作作为为相相应应订订货货表表的的嵌嵌套套表表。。收集集类类型型作作为为多多值值属属性性在O－－R方方法法中中，，主主要要实实体体顾客，货物，订货单变为对象象。对象引用用表示他他们之间间n:1的关系系，定义类型型定货系统统中的类类型：地址address_t对对象类类型多个电话话phone_list_t变长数组组类型顾客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));定义一个个数组类类型，表表示顾客客的几部部电话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));嵌套表和和可变数数组的比比较对顾客中中的电话话来说VARRAY的的元素是是有序的的VARRAY要要求预置置元素数数目对定货单单中的货货物条目目来说嵌套表没没有上界界的限制制嵌套表可可以直接接做查询询可以对嵌嵌套表做做索引定义方法法定义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;创建对象象表一般来讲讲，可以以按下面面规则理理解“对对象”和和“表””之间的的关系：：类，即客客观世界界的实体体，对应应于表对象属性性对应于于表的列列对象对应应于表的的记录每一个表表是一个个隐式的的类，它它的对象象（记录录）都有有相同的的属性（（列）创建对象表定义对象表customer_tabCREATETABLEcustomer_tabOFcustomer_info_t(custnoPRIMARYKEY);对象类型包含含四个属性：：custnoNUMBERcustnameVARCHAR2(200)addressaddress_tphone_listphone_list_t创建对象表对象类型作为为创建对象表表的模板约束用于表上上，不能定义义在类型上（（如PRIMARYKEY的约束束定义）表可以包含对对象列，由于于变长数组phone_list_t包含的数数量少于10x20，所所以ORACLE将其作作为一个存储储单元，当超超过4000byte时时，将以BLOB类型存存于表外不为对象类型型分配存储空空间，仅为表表分配记录空空间创建对象表对象表stock_tab的定义：：CREATETABLEstock_tabOFstock_info_t(stocknoPRIMARYKEY);STOCK_INFO_T对象类型型包含三个属属性：stocknoNUMBER,costNUMBER,tax_codeNUMBER创建对象表对象表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创建对象表表REF操作作符：如果未作限限制，REF允许引引用任意类类型为customer_info_t表的行行对象。SCOPE约束只只是限制purchase_tab对对象表的custref列的的取值范围围。嵌套表：十分适合于于表示表间间的主从关关系，从而而可以避免免DML中中的关系联联接（JOIN）。。一个嵌套表表的所有记记录存储在在一个独立立的存储表表中，在该该存储表中中有一个隐隐含的列NESTED_TABLE_ID与相相应的父表表记录相对对应，在嵌嵌套表中的的记录与父父表中的记记录根据NESTED_TABLE_ID对应应。嵌套表类型型的各个属属性对应于于存储表的的各列。维护嵌套表表修改嵌套表表的存储表表ALTERTABLEpo_line_tabADD(SCOPEFOR(stockref)ISstock_tab);ALTERTABLEpo_line_tabSTORAGE(NEXT5KPCTINCREASE5MINEXTENTS1MAXEXTENTS20);插入stock_tab数据据INSERTINTOstock_tabVALUES(1004,6750.00,2);INSERTINTOstock_tabVALUES(1011,4500.23,2);INSERTINTOstock_tabVALUES(1534,2234.00,2);INSERTINTOstock_tabVALUES(1535,3456.23,2);向对象表插插入数据插入customer_tab数据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'));向对象表插插入数据向对象表插插入数据插入purchase_tab数据INSERTINTOpurchase_tabSELECT1001,REF(C),SYSDATE,'10-MAY-1997',line_item_list_t(),NULLFROMcustomer_tabCWHEREC.custno=1;上面的语句句用下列属属性创建了了一个purchase_info_T对象：：pono=1001custref=对对于顾客客1的引用用orderdate=SYSDATEshipdate=10-MAY-1997line_item_list=一一个空的货货物列表shiptoaddr=NULL向嵌套表插插入数据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;向对象表插插入数据INSERTINTOpurchase_tabSELECT2001,REF(C),SYSDATE,'20-MAY-1997',line_item_list_t(),address_t(`55Madisonve','Madison','WI','53715')FROMcustomer_tabCWHEREC.custno=2;向嵌套表插插入数据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数据操纵--查询数数据查询订货单单1001的顾客信信息和订货货详细信息息SELECTDEREF(p.custref),p.shiptoaddr,p.pono,p.orderdate,line_item_listFROMpurchase_tabpWHEREp.pono=1001;每一个订货货单的总价价值SELECTp.pono,p.total_value()FROMpurchase_tabp;数据操纵--查询数数据查询定货单单及涉及货货物1004定货条条目的信息息。SELECTpo.pono,po.custref.custno,CURSOR(SELECT*FROMTABLE(po.line_item_list)LWHEREL.stockref.stockno=1004)FROMpurchase_tabpo;数据操纵--删除数数据在下面的删删除例子中中，Oracle自自动删除所所有属于订订货单的货货物条目，，而在原关关系模型中中必须要考考虑到两张张表的删除除问题。删除订货单单1001：DELETE FROMpurchase_tabWHEREpono=1001;四、采用对对象视图对象视图解解决方案（（基于关系系表）定义对象视视图对象视图的的查询使用触发器器更新对象象视图对象视图解解决方案对象视图是是虚拟对象象表，数据据源取自表表和视图。。采用对象表表实现系统统设计一般般采用如下下步骤建立实体和和关系通过创建和和填充关系系表实现实实体关系结结构采用UDT表示对象象关系模式式，模型化化一个实体体关系结构构采用O-R模式创建建和填充对对象表实现现实体关系系结构采用对象视视图方式要要使用相同同的初始步步骤，但最最后一步有有所不同，，它不是通通过创建和和填充对象象表的方式式，而是使使用对象视视图来表示示虚拟对象象表，数据据取自一般般的关系表表定义对象视视图三个对象视视图：Customer_view,Stock_view,Purchase_view。。创建建对对象象视视图图的的语语句句有有四四个个部部分分：：视图图的的名名字字视图图所所基基于于的的对对象象类类型型的的名名字字基于于主主码码创创建建对对象象标标识识OID)一个个选选择择语语句句根根据据对对应应的的对对象象类类型型向向虚虚拟拟对对象象表表中中填填充充数数据据后面面例例子子借借用用前前面面的的几几个个类类型型定定义义定义义对对象象视视图图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使用对象象视图--查查询数数据查询订货货单1001的的顾客信信息和订订货详细细信息SELECTDEREF(p.custref),p.shiptoaddr,p.pono,p.orderdate,line_item_listFROMpurchase_viewpWHEREp.pono=1001;统计每一一个订货货单的总总价值SELECTp.pono,p.total_value()FROMpurchase_viewp;使用对象象视图--查查询数数据查询定货货单及涉涉及货物物1004定货货条目的的信息。。SELECTpo.pono,po.custref.custno,CURSOR(SELECT*FROMTABLE(po.line_item_list)LWHEREL.stockref.stockno=1004)FROMpurchase_viewpo;使用触发发器更新新对象视视图Oracle提提供INSTEADOF触触发器可可作为更更新复杂杂对象视视图的方方法。每当想要要改变对对象视图图中行对对象的属属性值时时，Oracle执行行对象视视图的INSTEADOF触发器器在触发器器中，Oracle使使用关键键字:OLD和和:NEW存取取行对象象的当前前值和新新值。使用触发发器更新新对象视视图Stock_view的INSTEADOF触触发器CREATEORREPLACETRIGGERstockview_insert_trINSTEADOFINSERTONstock_viewFOREACHROWBEGININSERTINTOstock_infoVALUES(:NEW.stockno,:NEW.cost,:NEW.tax_code);END;使用触发发器更新新对象视视图Customer_view的INSTEADOF触触发器CREATEORREPLACETRIGGERcustview_insert_trINSTEADOFINSERTONcustomer_viewFOREACHROWDECLAREphonesphone_list_t;tphone1customer_info.phone1%TYPE:=NULL;tphone2customer_info.phone2%TYPE:=NULL;tphone3customer_info.phone3%TYPE:=NULL;使用触发发器更新新对象视视图BEGINphones:=:NEW.phone_list;IFphones.COUNT>2THENtphone3:=phones(3);ENDIF;IFphones.COUNT>1THENtphone2:=phones(2);ENDIF;IFphones.COUNT>0THENtphone1:=phones(1);ENDIF;使用触发发器更新新对象视视图INSERTINTOcustomer_infoVALUES(:NEW.custno,:NEW.custname,:NEW.address.street,:NEW.address.city,:NEW.address.state,:NEW.address.zip,tphone1,tphone2,tphone3);END;向对象视视图插入入数据下列语句句激活了了customer_view触发发器：INSERTINTOcustomer_viewVALUES(13,`EllanWhite',address_t(`25IS