級聯(lián)查詢
在ORACLE 數(shù)據(jù)庫中有一種方法可以實(shí)現(xiàn)級聯(lián)查詢

select *    //要查詢的字段
from table    //具有子接點(diǎn)ID與父接點(diǎn)ID的表 
start with selfid=id  //給定一個(gè)startid(字段名為子接點(diǎn)ID，及開始的ID號)
connect by prior selfid=parentid  //聯(lián)接條件為子接點(diǎn)等于父接點(diǎn)，不能反

這個(gè)SQL主要用于菜單的級聯(lián)查詢，給一個(gè)父接點(diǎn)可以查出所有的子接點(diǎn)。及子接點(diǎn)的子接點(diǎn)，一查到底，很實(shí)用。不過呢這個(gè)程序只能在oracle里面用，我目前還不知道在其它數(shù)據(jù)庫里是怎么調(diào)用的。等我找到了，再貼出來與大家分享。
這個(gè)程序，估計(jì)好多人看不明白，其實(shí)放了這么久我也一時(shí)沒看明白，重新測了一下，補(bǔ)充說明一下，不然我下次又看不懂了。
以一個(gè)windows系統(tǒng)的菜單為例。我那一個(gè)這樣的表menu。
說明：
mid:菜單的ID號
mname：菜單名稱
mpid：菜單的
quickey：快捷鍵
validate:權(quán)限表（存放userid,或者角色id）

如果我想知道在“文件”菜單下有那些子菜單的話。我就可以這樣用這個(gè)SQL程序：

select * from menu
start with mid=1  
connect by prior mid=mpid;

這樣就可以把 “文件”里的子菜單全部列出來了。當(dāng)然實(shí)際應(yīng)用不會這么簡單，如附加其實(shí)條件，尤其是權(quán)限管理，這時(shí)根據(jù)你的系統(tǒng)要求，是對個(gè)個(gè)驗(yàn)證，還是對角色驗(yàn)證，把這些人的ID放在validate這個(gè)字段里，組成一個(gè)字符串，N個(gè)ID用逗號隔開，（注意，在往數(shù)據(jù)庫保存時(shí)要注意對字符串處理一下，截取掉最后一個(gè)逗號這樣可以節(jié)省很多麻煩）

select * from menu
where validate in(……)
and mid in(
 select mid from menu //這里不能用*號了。
 start with mid=1  
 connect by prior mid=mpid;
)

最后再補(bǔ)充一點(diǎn)關(guān)于隨機(jī)查詢的代碼

select * from user order by sys_guid()

級聯(lián)刪除

Oracle在外鍵的刪除上有NO ACTION(類似RESTRICT)、CASCADE和SET NULL三種行為。
下面以學(xué)生-班級為例說明不同情況下的外鍵刪除，學(xué)生屬于班級，班級的主鍵是學(xué)生的外鍵。
-- 班級表  

CRATE TABLE TB_CLASS 
( 
 ID NUMBER NOT NULL, --班級主鍵 
 NAME VARCHAR2(50), --班級名稱 
 CONSTRAINT PK_TB_CLASS PRIMARY KEY (ID) 
); 

 
-- 學(xué)生表  

CREATE TABLE TB_STUDENT 
( 
 ID  NUMBER NOT NULL, --學(xué)生主鍵 
 NAME  VARCHAR2(50),  --學(xué)生姓名 
 CLASS_ID NUMBER,   --學(xué)生所屬班級，外鍵 
 
 --主鍵約束 
 CONSTRAINT PK_TB_STUDENT PRIMARY KEY (ID), 
 
 --外鍵約束 
 --設(shè)置級聯(lián)刪除為NO ACTION 
 CONSTRAINT FK_TB_STUDENT_CLASS_ID FOREIGN KEY (CLASS_ID) REFERENCES TB_CLASS (ID) 
); 

 
-- 添加班級數(shù)據(jù)  

INSERT INTO TB_CLASS (ID, NAME) VALUES (1, '一班'); 
INSERT INTO TB_CLASS (ID, NAME) VALUES (2, '二班'); 
INSERT INTO TB_CLASS (ID, NAME) VALUES (3, '三班'); 

 
-- 添加學(xué)生數(shù)據(jù) 

INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (1, '小明', 1); 
INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (2, '小剛', 1); 
INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (3, '小王', 1); 
INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (4, '二明', 2); 
INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (5, '二剛', 2); 
INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (6, '二王', 2); 
INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (7, '大明', 3); 
INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (8, '大剛', 3); 
INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (9, '大王', 3); 

初始班級數(shù)據(jù)

初始學(xué)生數(shù)據(jù)

NO ACTION：

NO ACTION指當(dāng)刪除主表中被引用列的數(shù)據(jù)時(shí)，如果子表的引用列中包含該值，則禁止該操作執(zhí)行。
現(xiàn)在學(xué)生外鍵級聯(lián)刪除是NO ACTION，執(zhí)行刪除班級操作。

--刪除三班 

DELETE FROM TB_CLASS WHERE ID=3; 

Oracle會提示違反完整性約束，如圖所示。

如果想要?jiǎng)h除三班，必須先刪除三班的學(xué)生。
--刪除三班學(xué)生 

DELETE FROM TB_STUDENT WHERE CLASS_ID=3; 

--刪除三班 

DELETE FROM TB_CLASS WHERE ID=3; 

SET NULL：

SET NULL指當(dāng)刪除主表中被引用列的數(shù)據(jù)時(shí)，將子表中相應(yīng)引用列的值設(shè)置為NULL值。SET NULL有個(gè)前提就是外鍵引用列必須可以設(shè)置為NULL。
把學(xué)生表(TB_STUDENT)的外鍵刪除行為改為SET NULL。ORACLE似乎沒有MODIFY CONSTRAINT操作，只能先刪除外鍵，然后創(chuàng)建新的。

--刪除學(xué)生表(TB_STUDENT)表的外鍵 

ALTER TABLE TB_STUDENT DROP CONSTRAINT FK_TB_STUDENT_CLASS_ID; 

--刪除添加ON DELETE SET NULL外鍵  

DELETE FROM TB_CLASS WHERE ID=1; 

由于外鍵的ON DELETE是SET NULL，所以當(dāng)刪除一班時(shí)，一班學(xué)生的CLASS_ID被設(shè)置為NULL，如圖所示。

CASCADE

CASCADE指當(dāng)刪除主表中被引用列的數(shù)據(jù)時(shí)，級聯(lián)刪除子表中相應(yīng)的數(shù)據(jù)行。
把學(xué)生表(TB_STUDENT)的外鍵刪除行為改為CASCADE。

--刪除TB_STUDENT表上的NO ACTION外鍵 

ALTER TABLE TB_STUDENT DROP CONSTRAINT FK_TB_STUDENT_CLASS_ID; 

--刪除添加ON DELETE CASCADE外鍵  

ALTER TABLE TB_STUDENT ADD CONSTRAINT FK_TB_STUDENT_CLASS_ID FOREIGN KEY (CLASS_ID) REFERENCES TB_CLASS (ID) ON DELETE CASCADE; 

 
--刪除二班 

DELETE FROM TB_CLASS WHERE ID=2; 

由于外鍵的ON DELETE是CASCADE，所以當(dāng)刪除二班時(shí)，二班下的學(xué)生也會被刪除。

級聯(lián)更新

Oracle本身并不支持外鍵的級聯(lián)更新，不過可以按照如下方法達(dá)到級聯(lián)更新的效果。
首先要先了解Oracle延遲約束和非延遲約束。非延遲約束就是在修改記錄的時(shí)候會立刻進(jìn)行約束條件的查看，是否因?yàn)檫`反了某些約束條件而不能執(zhí)行修改。延遲約束不會在剛進(jìn)行修改的時(shí)候進(jìn)行約束查看，只有提交的時(shí)候才會檢查。Oracle的級聯(lián)更新就是使用這個(gè)特性來實(shí)現(xiàn)的。
Oracle的外鍵默認(rèn)是非延遲約束，修改學(xué)生的外鍵為延遲約束。

--刪除學(xué)生表(TB_STUDENT)上的已有外鍵 

ALTER TABLE TB_STUDENT DROP CONSTRAINT FK_TB_STUDENT_CLASS_ID; 

--添加延遲約束外鍵 

ALTER TABLE TB_STUDENT ADD CONSTRAINT FK_TB_STUDENT_CLASS_ID FOREIGN KEY (CLASS_ID) REFERENCES TB_CLASS (ID) ON DELETE CASCADE DEFERRABLE; 

設(shè)置觸發(fā)器，當(dāng)班級表(TB_CLASS)的主鍵改變了，就更新學(xué)生表(TB_STUDENT)的外鍵(CLASS_ID)。

CREATE OR REPLACE TRIGGER TGR_TB_CLASS_UPDATE 
AFTER UPDATE OF ID ON TB_CLASS 
FOR EACH ROW 
BEGIN 
 IF :OLD.ID<>:NEW.ID THEN 
 UPDATE TB_STUDENT SET CLASS_ID=:NEW.ID WHERE CLASS_ID=:OLD.ID; 
 END IF; 
END; 

注意：
Oracle外鍵級聯(lián)更新方法可以用于外鍵和外鍵引用的主鍵在不同表上。不過會經(jīng)常遇到以下情況，就是在數(shù)據(jù)庫中保存具有層級關(guān)系的數(shù)據(jù)時(shí)，表的外鍵引用同一個(gè)表的主鍵。這時(shí)候無法用觸發(fā)器實(shí)現(xiàn)級聯(lián)更新。

最新評論