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(stocknoNUMBERPRIMARYKEY,costNUMBER,tax_codeNUMBER);CREATETABLEpurchase_info(ponoNUMBERPRIMARYKEY,custno NUMBERREFERENCEScustomer_info(custno),orderdate DATE,shiptodate DATE,shiptostreetVARCHAR2(200),shiptocityVARCHAR2(200),shiptostateVARCHAR2(200),shiptozip VARCHAR2(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(`25IStreet',`Me