Oracle订货系统综合知识概述

1、综合实例定货系统12021/5/28订货系统对象关系的设计与实现n一、系统简介n二、采用关系方法n三、采用对象表n四、采用对象视图一、系统简介n系统涉及的实体和联系=顾客表，Customer_Info=货物表，Stock_Info=订货单，purchase_info=细目表 Items_infoCustomer_Infopurchase_infoStock_Infoitems_infomn1mmn11顾客信息n顾客表，Customer_Info编号 顾客名 地址 邮编 电话 1 电话 2 电话3 1 Jean Nance 2 Avocet Drive, Redwood Shores, CA 9

2、5054 415- 555- 1212 2 John Nike 323 College Drive, Edison, NJ 08820 609- 555- 1212 201- 555- 1212 货物信息n货物表，Stock_Info货货物物号号 价价格格 税税率率 1004 6750.00 2 1011 4500.23 2 1534 2234.00 2 1535 3456.23 2 定货信息n订货单，purchase_infon定货条目（细目表）items_info单号单号 顾客顾客 定货日期定货日期 发发货日期货日期 到货到货 街道街道 到货到货 城市城市 邮编邮编 2001 2 1-1-

3、96 20-5-98 1001 1 条目编号条目编号 单号单号 货物货物 数量数量 折扣折扣 01 1001 1534 12 0 02 1001 1535 10 10 10 2001 1004 1 0 11 2001 1011 2 1 二、采用关系方法n关系表的定义n对关系表的数据操纵n基于关系表的查询与统计关系数据库的解决方案n采用关系方法将顾客信息，订货单，货物条目清单组织成表。n将地址拆成为基本元素，如省、市、街道、邮编等。n设置顾客的一个可能拥有电话的最大限度，每一个电话表示为表的一列。n关系方法将订货条目从订货单中分离出来，单独存放在一张表中，该表中有指向订货单表和货物表的外键。关系

4、表的定义n采用关系模型实现对现实世界的抽象。n将实体定义为关系（表）。n实体间的联系通过外来码或关系表实现。n表中的字段必须是不可分割的基本单位。n不允许表中嵌表。n通过表的连接实现多表查询。CREATE TABLE customer_info (custno NUMBER PRIMARY KEY,custname VARCHAR2(200),street VARCHAR2(200),city VARCHAR2(200), state VARCHAR2(200),zip VARCHAR2(6),phone1 VARCHAR2(20),phone2 VARCHAR2(20),phone3 VAR

5、CHAR2(20);建立顾客表n9、 人的价值，在招收诱惑的一瞬间被决定。21.7.1521.7.15Thursday, July 15, 2021n10、低头要有勇气，抬头要有低气。21:24:1921:24:1921:247/15/2021 9:24:19 PMn11、人总是珍惜为得到。21.7.1521:24:1921:24Jul-2115-Jul-21n12、人乱于心，不宽余请。21:24:1921:24:1921:24Thursday, July 15, 2021n13、生气是拿别人做错的事来惩罚自己。21.7.1521.7.1521:24:1921:24:19July 15, 20

6、21n14、抱最大的希望，作最大的努力。2021年7月15日星期四下午9时24分19秒21:24:1921.7.15n15、一个人炫耀什么，说明他内心缺少什么。2021年7月下午9时24分21.7.1521:24July 15, 2021n16、业余生活要有意义，不要越轨。2021年7月15日星期四21时24分19秒21:24:1915 July 2021n17、一个人即使已登上顶峰，也仍要自强不息。下午9时24分19秒下午9时24分21:24:1921.7.15n9、 人的价值，在招收诱惑的一瞬间被决定。21.7.1521.7.15Thursday, July 15, 2021n10、低头要

7、有勇气，抬头要有低气。21:24:1921:24:1921:247/15/2021 9:24:19 PMn11、人总是珍惜为得到。21.7.1521:24:1921:24Jul-2115-Jul-21n12、人乱于心，不宽余请。21:24:1921:24:1921:24Thursday, July 15, 2021n13、生气是拿别人做错的事来惩罚自己。21.7.1521.7.1521:24:1921:24:19July 15, 2021n14、抱最大的希望，作最大的努力。2021年7月15日星期四下午9时24分19秒21:24:1921.7.15n15、一个人炫耀什么，说明他内心缺少什么。2

8、021年7月下午9时24分21.7.1521:24July 15, 2021n16、业余生活要有意义，不要越轨。2021年7月15日星期四21时24分19秒21:24:1915 July 2021n17、一个人即使已登上顶峰，也仍要自强不息。下午9时24分19秒下午9时24分21:24:1921.7.15建立货物表CREATE TABLE stock_info (stockno NUMBER PRIMARY KEY,cost NUMBER, tax_code NUMBER );CREATE TABLE purchase_info (ponoNUMBER PRIMARY KEY,custnoNU

9、MBER REFERENCES customer_info(custno),orderdateDATE,shiptodateDATE,shiptostreetVARCHAR2(200),shiptocityVARCHAR2(200), shiptostate VARCHAR2(200),shiptozipVARCHAR2(20) );建立定货表建立细目表CREATE TABLE items_info (lineitemno NUMBER,pono NUMBER REFERENCES purchase_info(pono),stockno NUMBER REFERENCES stock_info

10、(stockno),quantity NUMBER,discount NUMBER,PRIMARY KEY (pono, lineitemno) );向关系表插入数据n向顾客表插入数据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, 08820, 609

11、-555-1212, 201-555-1212, NULL) ;n向货物表插入数据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) ;向关系表插入数据n向定货单表插入数据n INSERT INTO purchase_info n VALUES (1001, 1

12、, SYSDATE, 10-5月-1997, NULL, NULL, NULL, n NULL) ;n INSERT INTO purchase_info n VALUES (2022, 2, SYSDATE, 20-5月-1997, 55 Madison Ave, Madison, WI, 53715) ;n向定货单条目插入数据nINSERT INTO items_info VALUES(01, 1001, 1534, 12, 0) ;nINSERT INTO items_info VALUES(02, 1001, 1535, 10, 10) ;nINSERT INTO items_info

13、 VALUES(10, 2022, 1004, 1, 0) ;nINSERT INTO items_info VALUES(11, 2022, 1011, 2, 1) ;关系表的查询查询订货单1001的顾客信息和订货详细信息（多表连接）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_info C

14、, purchase_info P, items_info L WHERE C.custno = P.custno AND P.pono = L.pono AND P.pono = 1001;关系表的查询SELECT P.pono,C.custname, P.orderdate, cursor(select stockno,lineitemno, quantity,discount from items_info where pono=p.pono) 细目表见下FROM customer_info C, purchase_info PWHERE P.custno = C.custno AND

15、P.pono = 2022关系表的查询 PONO CUSTNAME ORDERDATE 细目表见下- - - - 2022 John Nike 14-10月-00 CURSOR ：4CURSOR STATEMENT ：4 STOCKNO LINEITEMNO QUANTITY DISCOUNT- - - - - 1004 10 1 0 1011 11 2 1数据操纵-查询数据n统计每一个订货单的总价值SELECT L.pono, SUM(S.cost * L.quantity)FROM items_info L, stock_info SWHERE L.stockno = S.stockno

16、GROUP BY L.pono ;n查询涉及货物1004 的订货单及订货条目信息SELECT P.pono, P.custno, L.stockno, L.lineitemno,L.quantity, L.discountFROM purchase_info P, items_info L WHERE P.pono = L.pono AND L.stockno = 1004;数据操纵-维护数据n修改数据:= 更改1001订货单中货物号为1535的订货量UPDATE items_info SET quantity=20 WHERE pono=1001 AND stockno = 1535;n删除

17、数据:= 删除编号为1001的订货单DELETE FROM items_info （先删细目表记录）WHERE pono = 1001;DELETE FROM purchase_info （再删主表记录）WHERE pono = 1001;三、采用对象表q 面向对象解决方案q 定义类型及类型体q 创建对象表q 对象表的数据操作实现对象关系的方法nO R 对象关系方法n用户自定义数据类型使得复杂数据结构进入数据库模式中。n不将address拆散，也不将联系电话存为不相关的多列，在OR中可以定义特殊的类型表示。n不将订货条目以单独表管理，OR中将他们作为相应订货表的嵌套表。n收集类型作为多值属性n

18、在OR方法中，主要实体 顾客，货物，订货单变为对象。n对象引用表示他们之间n:1的关系， 定义类型n定货系统中的类型：=地址 address_t 对象类型=多个电话 phone_list_t 变长数组类型 =顾客 customer_info_t 对象类型 =货物 stock_info_t 对象类型 =货物条目 line_item_t 对象类型=多个货物条目 line_item_list_t 嵌套表类型=定货单 purchase_info_t 对象类型 定义类型n定义一个对象类型表示地址信息CREATE TYPE address_t AS OBJECT ( street VARCHAR2(200

19、), city VARCHAR2(200), state CHAR(2), zip VARCHAR2(20) ) ;n定义一个数组类型，表示顾客的几部电话CREATE TYPE phone_list_t AS VARRAY(10) OF VARCHAR2(20) ;定义类型n定义一个对象类型表示定货条目信息CREATE TYPE line_item_t AS OBJECT ( lineitemno NUMBER, STOCKREF REF stock_info_t, quantity NUMBER, discount NUMBER ) ;n定义一个嵌套表类型，表示定货单中的货物条目信息CREA

20、TE TYPE line_item_list_t AS TABLE OF line_item_t ;定义类型n定义一个对象类型表示顾客CREATE TYPE customer_info_t AS OBJECT ( custno NUMBER, custname VARCHAR2(200), address address_t, phone_list phone_list_t, ORDER MEMBER FUNCTION cust_order(x IN customer_info_t) RETURN INTEGER, PRAGMA RESTRICT_REFERENCES ( cust_order

21、, WNDS, WNPS, RNPS, RNDS) ) ;定义类型n定义一个对象类型表示货物信息CREATE TYPE stock_info_t AS OBJECT ( stockno NUMBER, cost NUMBER, tax_code NUMBER ) ;定义类型n定义一个对象类型表示定货单CREATE TYPE purchase_info_t AS OBJECT ( pono NUMBER, custref REF customer_info_t, orderdate DATE, shipdate DATE, line_item_list line_item_list_t, shi

22、ptoaddr address_t, 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 (total_value, WNDS, WNPS) ) ;嵌套表和可变数组的比较n对顾客中的电话来说=VARRAY的元素是有序的=VARRAY要求预置元素数目n对定货单中的货物条目来说=嵌套表没有上界的

23、限制=嵌套表可以直接做查询=可以对嵌套表做索引定义方法n定义customer_info_t对象类型的方法：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;顾客对象的比较是顾客号的比较定义方法n定义purchase_info_t对象类型的方法：CREATE OR REPLACE TYPE BODY purchase_info_

24、t AS MAP MEMBER FUNCTION ret_value RETURN NUMBER IS BEGIN RETURN pono; END;定义方法 MEMBER FUNCTION total_value RETURN NUMBER IS i INTEGER;stock stock_info_t; - (stockno , cost , tax_code)line_item line_item_t; -( lineitemno STOCKREF quantity discount) total NUMBER := 0; BEGIN LOOP line_item := SELF.lin

25、e_item_list(i); SELECT DEREF(line_item.stockref) INTO stock FROM DUAL ; total := total + line_item.quantity * stock.cost ; END LOOP; RETURN total; END; END; 创建对象表n一般来讲，可以按下面规则理解“对象”和“表”之间的关系：=类，即客观世界的实体，对应于表=对象属性对应于表的列=对象对应于表的记录n每一个表是一个隐式的类，它的对象（记录）都有相同的属性（列）创建对象表n定义对象表customer_tabCREATE TABLE custo

26、mer_tab OF customer_info_t (custno PRIMARY KEY);n对象类型包含四个属性：= custno NUMBER= custname VARCHAR2(200)= address address_t= phone_list phone_list_t创建对象表n对象类型作为创建对象表的模板n约束用于表上，不能定义在类型上（如PRIMARY KEY的约束定义）n表可以包含对象列，由于变长数组phone_list_t包含的数量少于10 x20，所以ORACLE将其作为一个存储单元，当超过4000byte时，将以BLOB类型存于表外n不为对象类型分配存储空间，仅为

27、表分配记录空间 创建对象表n对象表stock_tab的定义：CREATE TABLE stock_tab OF stock_info_t (stockno PRIMARY KEY) ;nSTOCK_INFO_T对象类型包含三个属性： = stockno NUMBER,= cost NUMBER,= tax_code NUMBER创建对象表n对象表purchase_tab的定义：CREATE TABLE purchase_tab OF purchase_info_t ( PRIMARY KEY (pono), SCOPE FOR (custref) IS customer_tab ) NESTE

28、D TABLE line_item_list STORE AS po_line_tab ;npurchase_info_T对象类型的属性包括： = pono NUMBER， = custref REF customer_info_t，= orderdate DATE ， = shipdate DATE，= line_item_list line_item_list_t ， = shiptoaddr address_t创建对象表n REF操作符：=如果未作限制，REF允许引用任意类型为customer_info_t表的行对象。=SCOPE 约束只是限制purchase_tab对象表的custre

29、f列的取值范围。n 嵌套表：=十分适合于表示表间的主从关系，从而可以避免DML中的关系联接（JOIN）。=一个嵌套表的所有记录存储在一个独立的存储表中，在该存储表中有一个隐含的列NESTED_TABLE_ID与相应的父表记录相对应，在嵌套表中的记录与父表中的记录根据NESTED_TABLE_ID对应。=嵌套表类型的各个属性对应于存储表的各列。维护嵌套表n修改嵌套表的存储表 ALTER TABLE po_line_tab ADD (SCOPE FOR (stockref) IS stock_tab); ALTER TABLE po_line_tab STORAGE (NEXT 5K PCTINC

30、REASE 5 MINEXTENTS 1 MAXEXTENTS 20);n插入stock_tab数据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);向对象表插入数据n插入customer_tab数据INSERT INTO customer_tab VALUES ( 1,

31、Jean Nance, address_t(2 Avocet Drive, 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) ) ;向对象表插入数据向对象表插入数据n插入purchase_tab数据INSERT INTO purchase_tab SELECT 10

32、01, REF(C), SYSDATE,10-MAY-1997, line_item_list_t(), NULL FROM customer_tab C WHERE C.custno = 1 ;n上面的语句用下列属性创建了一个purchase_info_T对象： =pono=1001 =custref= 对于顾客1的引用=orderdate=SYSDATE =shipdate=10-MAY-1997= line_item_list= 一个空的货物列表 = shiptoaddr= NULL向嵌套表插入数据INSERT INTO THE ( SELECT P.line_item_list FRO

33、M purchase_tab P WHERE P.pono = 1001 ) SELECT 01, REF(S), 12, 0 FROM stock_tab S WHERE S.stockno = 1534;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; 向对象表插入数据INSERT INTO purchase_tab SELECT 2022,

34、 REF(C), SYSDATE , 20-MAY- 1997, line_item_list_t(), address_t(55 Madisonve,Madison,WI,53715) FROM customer_tab C WHERE C.custno = 2;向嵌套表插入数据INSERT INTO THE ( SELECT P.line_item_list FROM purchase_tab P WHERE P.pono = 2022 ) SELECT 10, REF(S), 1, 0 FROM stock_tab S WHERE S.stockno = 1004;INSERT INTO

35、 THE ( SELECT P.line_item_list FROM purchase_tab P WHERE P.pono = 2022 ) VALUES( line_item_t(11, NULL, 2, 1) ) ;修改嵌套表n修改定单表中单号为2022记录的嵌套表，将条目号为11的货物指针指向1011号货物nUPDATE THE (SELECT P.line_item_listn FROM purchase_tab Pn WHERE P.pono = 2022 ) plistnSET plist.stockref = ( SELECT REF(S)n FROM stock_tab S

36、n WHERE S.stockno = 1011 )nWHERE plist.lineitemno = 11 ;使用排序方法n排序方法的引用 SELECT p.pono FROM purchase_tab p ORDER BY VALUE(p);n按purchase_tab 对象大小比较，隐含调用：MAP MEMBER FUNCTION ret_value RETURN NUMBER IS BEGIN RETURN pono; END;n相当于 order by pono 数据操纵-查询数据n 查询订货单1001的顾客信息和订货详细信息SELECT DEREF(p.custref), p.sh

37、iptoaddr, p.pono, p.orderdate, line_item_listFROM purchase_tab p WHERE p.pono = 1001 ;n 每一个订货单的总价值SELECT p.pono, p.total_value() FROM purchase_tab p ;数据操纵-查询数据n 查询定货单及涉及货物1004定货条目的信息。 CURSOR ( SELECT * FROM TABLE (po.line_item_list) LFROM purchase_tab po ; 数据操纵-删除数据n在下面的删除例子中，Oracle自动删除所有属于订货单的货物条目，

38、而在原关系模型中必须要考虑到两张表的删除问题。n删除订货单1001：DELETEFROM purchase_tabWHERE pono = 1001 ;四、采用对象视图n对象视图解决方案（基于关系表）n 定义对象视图n对象视图的查询n使用触发器更新对象视图对象视图解决方案n对象视图是虚拟对象表，数据源取自表和视图。n采用对象表实现系统设计一般采用如下步骤=建立实体和关系=通过创建和填充关系表实现实体关系结构=采用UDT表示对象关系模式，模型化一个实体关系结构=采用O-R模式创建和填充对象表实现实体关系结构n采用对象视图方式要使用相同的初始步骤，但最后一步有所不同，它不是通过创建和填充对象表的方

39、式，而是使用对象视图来表示虚拟对象表，数据取自一般的关系表定义对象视图n三个对象视图：=Customer_view, Stock_view, Purchase_view。n创建对象视图的语句有四个部分：=视图的名字=视图所基于的对象类型的名字=基于主码创建对象标识OID)=一个选择语句根据对应的对象类型向虚拟对象表中填充数据n后面例子借用前面的几个类型定义 定义对象视图nCustomer_view对象视图：CREATE OR REPLACE VIEW customer_view OF customer_info_t WITH OBJECT OID(custno) AS SELECT C.cus

40、tno, 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 ;定义对象视图nStock_view对象视图CREATE OR REPLACE VIEW stock_view OF stock_info_t WITH OBJECT OID(stockno) ASSELECT * FROM stock_info ;定义对象视图nPurchase_view对象视图CREATE OR REPLACE VIEW p

41、urchase_view OF purchase_info_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_info P ;客户对象引用嵌套表列构造视图的对象引用列MAKE_REF (customer_view, P.custno),nMAKE_REF(对象表/视图，定位记录的主键值）n返回指向对象表/视图的一个对象（记录）的REF

42、客户对象引用构造视图的嵌套表列CAST ( MULTISET( SELECT line_item_t ( L.lineitemno, MAKE_REF(stock_view, L.stockno), L.quantity , L.discount ) FROM items_info L WHERE L.pono= P.pono ) AS line_item_list_t ),嵌套表列 定义对象视图npurchase_info_t对象类型有如下属性：= pono NUMBER= custref REF customer_info_t= orderdate DATE= shipdate DATE=

43、 line_item_list line_item_list_t= shiptoaddr address_t使用对象视图 - 查询数据n查询订货单1001的顾客信息和订货详细信息SELECT DEREF(p.custref), p.shiptoaddr, p.pono, p.orderdate, line_item_list FROM purchase_view p WHERE p.pono = 1001 ;n统计每一个订货单的总价值SELECT p.pono, p.total_value()FROM purchase_view p ;使用对象视图 - 查询数据n查询定货单及涉及货物1004定

44、货条目的信息。 CURSOR ( SELECT * FROM TABLE (po.line_item_list) LFROM purchase_view po ;使用触发器更新对象视图nOracle提供INSTEAD OF触发器可作为更新复杂对象视图的方法。n每当想要改变对象视图中行对象的属性值时，Oracle执行对象视图的INSTEAD OF触发器n在触发器中，Oracle使用关键字:OLD和:NEW存取行对象的当前值和新值。使用触发器更新对象视图nStock_view的INSTEAD OF触发器CREATE OR REPLACE TRIGGER stockview_insert_tr IN

45、STEAD OF INSERT ON stock_viewFOR EACH ROW BEGIN INSERT INTO stock_info VALUES ( :NEW.stockno, :NEW.cost, :NEW.tax_code ); END ;n 使用触发器更新对象视图nCustomer_view的INSTEAD OF触发器CREATE OR REPLACE TRIGGER custview_insert_tr INSTEAD OF INSERT ON customer_viewFOR EACH ROW DECLARE phones phone_list_t; tphone1 cus

46、tomer_info.phone1%TYPE := NULL; tphone2 customer_info.phone2%TYPE := NULL; tphone3 customer_info.phone3%TYPE := NULL;使用触发器更新对象视图BEGIN phones := :NEW.phone_list; IF phones.COUNT 2 THEN tphone3 := phones(3); END IF; IF phones.COUNT 1 THEN tphone2 := phones(2); END IF; IF phones.COUNT 0 THEN tphone1 := phones(1); END IF;使用触发器更新对象视图 INSERT INTO customer_info VALUES ( :NEW.custno, :NEW.custname, tphone1, tp