订货系统对象关系的设计与实现课件

1、一个实例-订货系统第1页，共79页。订货系统-对象关系的设计与实现一、系统简介二、采用关系方法三、采用对象表四、采用对象视图第2页，共79页。一、系统简介1.1 建模方法1.2 系统涉及的实体和关系1.3 实体包含的信息第3页，共79页。首先采用关系模型，实体联系模型实现，称为关系方法然后使用UDTs将实体关系转换为模式对象，称为对象关系方法数据源取自对象表，而非关系模型中的关系表数据源使用关系表，而不是直接定义对象表，采用对象视图表示虚拟对象表1.1 建模方法第4页，共79页。1.2 系统涉及的实体和关系Customer_InfoPurchase_OrderStock_InfoLine_It

2、emsmn1mmn11顾客表，Customer_Info货物表，Stock_Info订货单，Purchase_Order第5页，共79页。顾客信息第6页，共79页。货物信息第7页，共79页。定货信息第8页，共79页。1.3 实体包含的信息顾客表:顾客联系信息货物表:货物标识，价格订货单:顾客，订货条目，发货日期，发货地址订货条目清单: 货物，数量，折扣有些实体属性很复杂：复合属性，如地址属性包括（省市，街道，邮编）一个顾客的电话可能有多部货物条目清单中应包含一个属性标识相应的订货单第9页，共79页。二、采用关系方法2.1 采用关系的解决方案2.2 关系表的定义方法2.3 对关系表的数据操纵第1

3、0页，共79页。2.1采用关系的解决方案采用关系方法将顾客信息，订货单，货物条目清单组织成表将地址拆成为基本元素，如省市，街道，邮编设置一个可能拥有电话的最大限度，每一个电话表示为表的一列关系方法将订货条目从订货单中分离出来单独存放在一张表中，该表中有指向货物表和订货单表的外键第11页，共79页。2.2 表的定义采用关系模型实现对现实世界的反映将实体定义为关系（表）实体间的联系通过外键或关系（表）来实现表中的字段必须是不可分割的基本单位不允许表中嵌表多表联合实现查询第12页，共79页。CREATE TABLE customer_info (custnoNUMBER PRIMARY KEY,cu

4、stnameVARCHAR2(200),streetVARCHAR2(200),cityVARCHAR2(200), state VARCHAR2(2),zipVARCHAR2(6),phone1VARCHAR2(20),phone2VARCHAR2(20),phone3VARCHAR2(20);建立顾客表第13页，共79页。建立货物表CREATE TABLE stock_info (stocknoNUMBER PRIMARY KEY,price NUMBER tax_code NUMBER );第14页，共79页。CREATE TABLE purchase_order (ponoNUMBER

5、 PRIMARY KEY,custnoNUMBER REFERENCES customer_info,orderdateDATE,shiptodateDATE,shiptostreetVARCHAR2(200),shiptocityVARCHAR2(200), shiptostate VARCHAR2(2),shiptozipVARCHAR2(20) );建立定货表第15页，共79页。建立定货表的详细条目CREATE TABLE line_items (lineitemno NUMBER,pono NUMBER REFERENCES purchase_order,stockno NUMBER

6、REFERENCES stock_info,quantity NUMBER,discount NUMBERPRIMARY KEY (pono, lineitemno) );第16页，共79页。向顾客表插入数据INSERT INTO customer_info VALUES (1, Jean Nance, 2 Avocet Drive, Redwood Shores, CA, 95054, 415-555-1212, NULL, NULL) ;INSERT INTO customer_info VALUES (2, John Nike, 323 College Drive, Edison, NJ

7、, 08820, 609-555-1212, 201-555-1212, NULL) ;向货物表插入数据INSERT INTO stock_info VALUES(1004, 6750.00, 2) ;INSERT INTO stock_info VALUES(1011, 4500.23, 2) ;INSERT INTO stock_info VALUES(1534, 2234.00, 2) ;INSERT INTO stock_info VALUES(1535, 3456.23, 2) ;2.3 数据操纵-构造数据第17页，共79页。2.3 数据操纵-构造数据向定货单表插入数据 INSERT

8、 INTO purchase_order VALUES (1001, 1, SYSDATE, 10-MAY-1997, NULL, NULL, NULL, NULL) ;INSERT INTO purchase_order VALUES (2001, 2, SYSDATE, 20-MAY-1997, 55 Madison Ave, Madison, WI, 53715) ;向定货单条目插入数据INSERT INTO line_items VALUES(01, 1001, 1534, 12, 0) ;INSERT INTO line_items VALUES(02, 1001, 1535, 10

9、, 10) ;INSERT INTO line_items VALUES(10, 2001, 1004, 1, 0) ;INSERT INTO line_items VALUES(11, 2001, 1011, 2, 1) ;第18页，共79页。SELECT C.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.discount FROM customer_i

10、nfo C, purchase_order P, line_items L WHERE C.custno = P.custno AND P.pono = L.pono AND P.pono = 1001;2.3 数据操纵-查询数据查询订货单1001的顾客信息和订货详细信息第19页，共79页。每一个订货单的总价值SELECT P.pono, SUM(S.cost * L.quantity) FROM purchase_order P, line_items L, stock_info SWHERE P.pono = L.pono AND L.stockno = S.stockno GROUP B

11、Y P.pono;2.3 数据操纵-查询数据涉及货物1004 的订货单及订货条目信息SELECT P.pono, P.custno, L.stockno, L.lineitemno, L.quantity, L.discount FROM purchase_order P, line_items L WHERE P.pono = L.pono AND L.stockno = 1004;第20页，共79页。更新数据: 更改订货单1001，货物号为1535的订货量UPDATE line_items SET quantity=20 WHERE pono=1001 AND stockno = 1535

12、;删除数据: 删除订货单1001DELETE FROM line_items WHERE pono = 1001;DELETE FROM purchase_order WHERE pono = 1001;2.3 数据操纵-维护数据第21页，共79页。三、采用对象表3.1 对象-关系解决方案3.2 定义类型3.3 定义方法3.4 创建对象表3.5 数据操纵第22页，共79页。3.1 对象-关系解决方案3GL可以实现数据封装，复杂的用户定义类型（封装了方法）SQL只能提供基本类型，无法封装对数据的相关操作3GL语言很难实现数据的一致性和安全性将复杂数据类型映射为SQL APP logic DATA

13、 logic第23页，共79页。实现对象关系的方法O-R方法UDT使得复杂数据结构进入数据库模式中不将address拆散，也不将联系电话存为不相关的多列，在OR中可以定义特殊的类型表示不将订货条目以单独表管理，OR中将他们作为相应订货表的嵌套表在OR方法中，主要实体：顾客，货物，订货单变为对象对象引用表示他们之间n:1的关系，收集类型作为多值属性第24页，共79页。有两种方法实现OR策略：创建对象表并装入数据使用对象视图表示已存在关系数据的虚拟对象表采用对象表的O-R方法采用对象视图的O-R方法实现对象关系的方法第25页，共79页。查看属于用户的所有类型 用户字典 user_types查看某类

14、型的详细定义desc 类型名3.2 定义类型第26页，共79页。3.2 定义类型定货系统中的类型地址 address_t ：对象类型多个电话 phone_list_t：变长数组顾客 customer_info_t：对象类型货物 stock_info_t：对象类型货物条目 line_item_t ：对象类型多个货物条目 line_item_list_t：嵌套表定货单 purchase_order_t ：对象类型第27页，共79页。定义一个对象类型表示地址信息CREATE TYPE address_t AS OBJECT ( street VARCHAR2(200), city VARCHAR2(

15、200), state CHAR(2), zip VARCHAR2(20) ) ;3.2 定义类型第28页，共79页。定义一个数组类型(collection type)表示电话CREATE TYPE phone_list_t AS VARRAY(10) OF VARCHAR2(20) ;3.2 定义类型第29页，共79页。定义一个对象类型表示顾客CREATE TYPE customer_info_t AS OBJECT ( custno NUMBER, custname VARCHAR2(200), address address_t, phone_list phone_list_t, ORD

16、ER MEMBER FUNCTION cust_order(x IN customer_info_t) RETURN INTEGER, PRAGMA RESTRICT_REFERENCES ( cust_order, WNDS, WNPS, RNPS, RNDS) ) ;3.2 定义类型第30页，共79页。定义一个对象类型表示货物信息CREATE TYPE stock_info_t AS OBJECT ( stockno NUMBER, cost NUMBER, tax_code NUMBER ) ;3.2 定义类型第31页，共79页。定义一个对象类型表示货物条目信息CREATE TYPE l

17、ine_item_t AS OBJECT ( lineitemno NUMBER, STOCKREF REF stock_info_t, quantity NUMBER, discount NUMBER ) ;3.2 定义类型第32页，共79页。定义一个嵌套表类型表示定货单中的所有货物条目信息 CREATE TYPE line_item_list_t AS TABLE OF line_item_t ;3.2 定义类型第33页，共79页。定义一个对象类型表示定货单CREATE TYPE purchase_order_t AS OBJECT ( pono NUMBER, custref REF c

18、ustomer_info_t, orderdate DATE, shipdate DATE, line_item_list line_item_list_t, shiptoaddr address_t, 3.2 定义类型第34页，共79页。MAP MEMBER FUNCTION ret_value RETURN NUMBER, PRAGMA RESTRICT_REFERENCES ( ret_value, WNDS, WNPS, RNPS, RNDS),MEMBER FUNCTION total_value RETURN NUMBER, PRAGMA RESTRICT_REFERENCES (

19、total_value, WNDS, WNPS) ) ;一个订货单（purchase_order_t）中包含一个订货条目（line_item_t）的清单（line_item_list），每一个订货条目包含一个对于货物（stock_info_t）的引用（stockref）3.2 定义类型第35页，共79页。嵌套表和VARRAY的比较 (collection type)对顾客中的电话来说VARRAY的元素是有序的VARRAY要求预置元素数目对定货单中的货物条目来说嵌套表没有上界的限制嵌套表可以直接做查询可以对嵌套表做索引3.2 定义类型第36页，共79页。定义customer_info_t对象类型

20、的方法：CREATE OR REPLACE TYPE BODY customer_info_t AS ORDER MEMBER FUNCTION cust_order (x IN customer_info_t) RETURN INTEGER IS BEGIN RETURN custno - x.custno; END;END;3.3 定义方法第37页，共79页。3.3 定义方法purchase_order_t对象类型的方法：CREATE OR REPLACE TYPE BODY purchase_order_t ASBEGIN MAP MEMBER FUNCTION ret_value RE

21、TURN NUMBER IS BEGIN RETURN pono; END; 第38页，共79页。MEMBER FUNCTION total_value RETURN NUMBER IS i INTEGER;stock stock_info_t; line_item line_item_t;total NUMBER := 0; BEGIN FOR i IN 1.SELF.line_item_list.COUNT LOOP line_item := SELF.line_item_list(i); SELECT DEREF(line_item.stockref) INTO stock FROM D

22、UAL ; total := total + line_item.quantity * stock.price ; END LOOP; RETURN total; END; END;3.3 定义方法第39页，共79页。 SELF作为每一个函数的隐式参数，代表对象自身 COUNT给定嵌套表或数组中元素个数 DEREF操作符以一个引用为参数，返回一个行对象显示错误信息 show errors;3.3 定义方法第40页，共79页。表是一个事物的表示，有一些特征和行为也称为类（Class）或抽象数据类型（ADT）Oracle不使用这些术语特征定义为对象属性，如员工对象的姓名、年龄、性别行为定义为对象的

23、方法，如员工工资的支付对象是对象类型的一个实例对象相当于关系表中的行和记录3.4 创建对象表第41页，共79页。一般来讲，可以按下面规则理解“对象”和“表”之间的关系：类，即客观世界的实体，对应于表对象属性对应于表的列对象对应于表的记录每一个表是一个隐式的类，它的对象（记录）都有相同的属性（列）3.4 创建对象表第42页，共79页。定义对象表customer_info_tCREATE TABLE customer_tab OF customer_info_t (custno PRIMARY KEY);对象类型包含三个属性：custno NUMBERcustname VARCHAR2(200)a

24、ddress address_tphone_list phone_list_t3.4 创建对象表第43页，共79页。抽象数据类型作为对象表的模板约束用于表上，不能定义在类型上（如PRIMARY KEY的约束定义）表可以包含对象列，由于变长数组phone_list_t包含的数量少于10 x20，所以ORACLE将其作为一个存储单元，当超过4000byte时，将以BLOB类型存于表外不为对象类型分配存储空间，仅为表分配记录空间3.4 创建对象表第44页，共79页。对象表stock_tab的定义：CREATE TABLE stock_tab OF stock_info_t (stockno PRIM

25、ARY KEY) ;STOCK_INFO_T对象类型包含三个属性： stockno NUMBER, cost NUMBER, tax_code NUMBER3.4 创建对象表第45页，共79页。对象表purchase_tab的定义：CREATE TABLE purchase_tab OF purchase_order_t ( PRIMARY KEY (pono), SCOPE FOR (custref) IS customer_tab ) NESTED TABLE line_item_list STORE AS po_line_tab ;PURCHASE_ORDER_T对象类型的属性包括： p

26、ono NUMBER， custref REF customer_info_t， orderdate DATE ， shipdate DATE， line_item_list line_item_list_t ， shiptoaddr address_t3.4 创建对象表第46页，共79页。 REF操作符：如果未作限制，REF允许引用任意类型为customer_info_t表的行对象。SCOPE 约束只是限制purchase_tab对象表的custref列的取值范围。 嵌套表：十分适合于表示表间的主从关系，从而可以避免DML中的关系联接（JOIN）。一个嵌套表的所有记录存储在一个独立的存储表中

27、，在该存储表中有一个隐含的列NESTED_TABLE_ID与相应的父表记录相对应，在嵌套表中的记录与父表中的记录根据NESTED_TABLE_ID对应。嵌套表类型的各个属性对应于存储表的各列。3.4 创建对象表第47页，共79页。修改对象表 ALTER TABLE po_line_tab ADD (SCOPE FOR (stockref) IS stock_tab); ALTER TABLE po_line_tab STORAGE (NEXT 5K PCTINCREASE 5 MINEXTENTS 1 MAXEXTENTS 20);3.4 创建对象表第48页，共79页。插入stock_tab数

28、据INSERT INTO stock_tab VALUES(1004, 6750.00, 2);INSERT INTO stock_tab VALUES(1011, 4500.23, 2);INSERT INTO stock_tab VALUES(1534, 2234.00, 2);INSERT INTO stock_tab VALUES(1535, 3456.23, 2);3.5 数据操纵-插入数据第49页，共79页。插入customer_tab数据INSERT INTO customer_view VALUES ( 1, Jean Nance, address_t(2 Avocet Dri

29、ve, Redwood Shores, CA, 95054), phone_list_t(415-555-1212) ;INSERT INTO customer_tab VALUES (2, John Nike, address_t(323 College Drive, Edison, NJ, 08820), phone_list_t(609-555-1212,201-555-1212) ) ;3.5 数据操纵-插入数据第50页，共79页。插入purchase_tab数据INSERT INTO purchase_tab SELECT 1001, REF(C), SYSDATE,10-MAY-1

30、997, line_item_list_t(), NULL FROM customer_tab C WHERE C.custno = 1 ;上面的语句用下列属性创建了一个PURCHASE_ORDER_T对象： pono=1001 custref= 对于顾客1的引用orderdate=SYSDATE shipdate=10-MAY-1997 line_item_list= 一个空的货物列表 shiptoaddr= NULL3.5 数据操纵-插入数据第51页，共79页。INSERT INTO THE ( SELECT P.line_item_list FROM purchase_tab P WHE

31、RE P.pono = 1001 ) SELECT 01, REF(S), 12, 0 FROM stock_tab S WHERE S.stockno = 1534;3.5 数据操纵-插入数据INSERT INTO THE ( SELECT P.line_item_list FROM purchase_tab P WHERE P.pono = 1001 ) SELECT 02, REF(S), 10, 10 FROM stock_tab S WHERE S.stockno = 1535;向嵌套表插入数据：关键字THE第52页，共79页。INSERT INTO purchase_tab SEL

32、ECT 2001, REF(C), SYSDATE,20-MAY-1997, line_item_list_t(), address_t(55 Madison Ave,Madison,WI,53715) FROM customer_tab C WHERE C.custno = 2;3.5 数据操纵-插入数据第53页，共79页。INSERT INTO THE ( SELECT P.line_item_list FROM purchase_tab P WHERE P.pono = 2001 ) SELECT 10, REF(S), 1, 0 FROM stock_tab S WHERE S.sto

33、ckno = 1004;INSERT INTO THE ( SELECT P.line_item_list FROM purchase_tab P WHERE P.pono = 2001 ) VALUES( line_item_t(11, NULL, 2, 1) ) ;3.5 数据操纵-插入数据第54页，共79页。UPDATE THE (SELECT P.line_item_list FROM purchase_tab P WHERE P.pono = 2001 ) plistSET plist.stockref = (SELECT REF(S) FROM stock_tab S WHERE

34、S.stockno = 1011 )WHERE plist.lineitemno = 11 ;3.5 数据操纵-更新数据第55页，共79页。比较方法的引用SELECT p.pono FROM purchase_tab p ORDER BY VALUE(p);3.5 数据操纵-查询数据第56页，共79页。 查询订货单1001的顾客信息和订货详细信息SELECT DEREF(p.custref), p.shiptoaddr, p.pono, p.orderdate, line_item_listFROM purchase_tab p WHERE p.pono = 1001 ; 每一个订货单的总价值

35、SELECT p.pono, p.total_value() FROM purchase_tab p ;3.5 数据操纵-查询数据第57页，共79页。 涉及货物1004 的订货单及订货条目信息SELECT po.pono, po.custref.custno, CURSOR ( SELECT * FROM TABLE (po.line_item_list) L WHERE L.stockref.stockno = 1004 )FROM purchase_tab po ; 3.5 数据操纵-查询数据第58页，共79页。在下面的删除例子中，Oracle自动删除所有属于订货单的货物条目，而在关系模型

36、中必须需要单独的两步来完成。删除订货单1001：DELETEFROM purchase_order WHERE pono = 1001 ;3.5 数据操纵-删除数据第59页，共79页。四、采用对象视图4.1 对象视图解决方案4.2 定义对象视图4.3 使用触发器更新对 象视图4.4 数据操纵第60页，共79页。4.1 对象视图解决方案对象视图是虚拟对象表，数据源取自表和视图采用对象表设计方法实现系统设计一般采用如下步骤建立实体和关系通过创建和填充关系表实现实体关系结构采用UDT表示对象关系模式，模型化一个实体关系结构采用O-R模式创建和填充对象表实现实体关系结构采用对象视图方式要使用相同的初始

37、步骤，但最后一步有所不同，它不是通过创建和填充对象表的方式，而是使用对象视图来表示虚拟对象表，数据取自一般的关系表第61页，共79页。4.2 定义对象视图三个对象视图：Customer_view, Stock_view, Purchase_view。创建对象视图的语句有四个部分：视图的名字视图所基于的对象类型的名字基于主码创建对象标识一个选择语句根据对应的对象类型向虚拟对象表中填充数据第62页，共79页。Customer_view对象视图的实现：CREATE OR REPLACE VIEW customer_view OF customer_info_t WITH OBJECT OID(cus

38、tno) AS SELECT C.custno, C.custname, address_t(C.street, C.city, C.state, C.zip), phone_list_t (C.phone1, C.phone2, C.phone3) FROM customer_info C ;4.2 定义对象视图第63页，共79页。Stock_view对象视图的实现CREATE OR REPLACE VIEW stock_view OF stock_info_t WITH OBJECT OID(stockno) ASSELECT * FROM stock_info ;4.2 定义对象视图第6

39、4页，共79页。Purchase_view对象视图的实现：CREATE OR REPLACE VIEW purchase_view OF purchase_order_t WITH OBJECT OID (pono) AS SELECT P.pono, , P.orderdate, P.shiptodate, , address_t (P.shiptostreet, P.shiptocity, P.shiptostate, P.shiptozip) FROM purchase_order P ;4.2 定义对象视图第65页，共79页。CAST ( MULTISET (SELECT line_i

40、tem_t (L.lineitemno, MAKE_REF(stock_view, L.stockno), L.quantity, L.discount ) FROM line_items L WHERE L.pono= P.pono )AS line_item_list_t )MAKE_REF (customer_view, P.custno),4.2 定义对象视图第66页，共79页。purchase_order_t对象类型有如下属性： pono NUMBER custref REF customer_info_t orderdate DATE shipdate DATE line_item

41、_list line_item_list_t shiptoaddr address_t4.2 定义对象视图第67页，共79页。4.3 使用触发器更新对象视图Oracle提供INSTEAD OF触发器作为更新复杂对象视图的方式INSTEAD OF触发器对于更新前面定义的对象视图是必须的每当想要改变对象视图中行对象的属性值时，Oracle执行对象视图的INSTEAD OF触发器在触发器中，Oracle使用关键字:OLD和:NEW存取行对象的当前值和新值。第68页，共79页。Stock_view的INSTEAD OF触发器CREATE OR REPLACE TRIGGER stockview_ins

42、ert_tr INSTEAD OF INSERT ON stock_viewBEGIN INSERT INTO stock_info VALUES ( :NEW.stockno, :NEW.cost, :NEW.tax_code );END ; insert into stock_view values (1534, 1000,10);insert into stock_info values (1534, 1000,10);4.3 使用触发器更新对象视图第69页，共79页。Customer_view的INSTEAD OF触发器CREATE OR REPLACE TRIGGER custvie

43、w_insert_tr INSTEAD OF INSERT ON customer_viewDECLARE phones phone_list_t; tphone1 customer_info.phone1%TYPE := NULL; tphone2 customer_info.phone2%TYPE := NULL; tphone3 customer_info.phone3%TYPE := NULL;BEGIN phones := :NEW.phone_list; 4.3 使用触发器更新对象视图第70页，共79页。 IF phones.COUNT 2 THEN tphone3 := phon

44、es(3); END IF;IF phones.COUNT 1 THEN tphone2 := phones(2); END IF; IF phones.COUNT 0 THEN tphone1 := phones(1); END IF;4.3 使用触发器更新对象视图第71页，共79页。 INSERT INTO customer_info VALUES ( :NEW.custno, :NEW.custname, :NEW.address.street, :NEW.address.city, :NEW.address.state, :NEW.address.zip,/ phones(1), ph

45、ones(2),phones(3) tphone1, tphone2, tphone3);END ;INSERT INTO customer_view VALUES ( 1, Jean Nance, address_t(2 Avocet Drive, Redwood Shores, CA, 95054), phone_list_t(415-555-1212) ;4.3 使用触发器更新对象视图第72页，共79页。Purchase_view的INSTEAD OF触发器CREATE OR REPLACE TRIGGERpoview_insert_tr INSTEAD OF INSERT ON purchase_viewDECLARE line_itms line_item_list_t ; i INTEGER ;custvar customer_info_t ; stockvar stock_info_t ;stockvartemp REF stock_info_t ;BEGIN line_itms := :NEW.line_item_