[每日一题] OCP1z0-047 :2013-08-28 DELETE(二)
al;
DBMS_METADATA.GET_DDL('TABLE','ORDER_ITEMS')
--------------------------------------------------------------------------------
CREATE TABLE "OE"."ORDER_ITEMS"
( "ORDER_ID" NUMBER(12,0),
"LINE_ITEM_ID" NUMBER(3,0) NOT NULL ENABLE,
"PRODUCT_ID" NUMBER(6,0) NOT NULL ENABLE,
"UNIT_PRICE" NUMBER(8,2),
"QUANTITY" NUMBER(8,0),
CONSTRAINT "ORDER_ITEMS_PK" PRIMARY KEY ("ORDER_ID", "LINE_ITEM_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE" ENABLE,
CONSTRAINT "ORDER_ITEMS_ORDER_ID_FK" FOREIGN KEY ("ORDER_ID")
REFERENCES "OE"."ORDERS" ("ORDER_ID") ON DELETE CASCADE ENABLE NOVALIDATE,
CONSTRAINT "ORDER_ITEMS_PRODUCT_ID_FK" FOREIGN KEY ("PRODUCT_ID")
REFERENCES "OE"."PRODUCT_INFORMATION" ("PRODUCT_ID") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE"
[html]
删除了子表上的记录：
[html]
oe@OCM> select count(*) from orders; －－主表
COUNT(*)
----------
105
oe@OCM> select count(*) from order_items; －－子表
COUNT(*)
----------
665
oe@OCM> delete from(select * from order_items i,orders o
2 WHERE i.order_id=o.order_id AND order_status in(0,1));
110 rows deleted.
oe@OCM> select count(*) from orders; －－记录没变
COUNT(*)
----------
105
oe@OCM> select count(*) from order_items; －－记录少了110条
COUNT(*)
----------
555
有些人D答案百思不得期解，模拟这个操作时一直报错（ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table）原因就在于自己去建了两个表order和order_items时没有在这两个表上建约束主外键，并且加上主外键可以级联删除（CONSTRAINT "ORDER_ITEMS_ORDER_ID_FK" FOREIGN KEY ("ORDER_ID") REFERENCES "OE"."ORDERS" ("ORDER_ID") ON DELETE CASCADE ENABLE NOVALIDATE）。
这个我就不再测试了，太简单了。