級聯查詢
在ORACLE 數據庫中有一種方法可以實現級聯查詢
select * //要查詢的字段from table //具有子接點ID與父接點ID的表 start with selfid=id //給定一個startid(字段名為子接點ID，及開始的ID號)connect by prior selfid=parentid //聯接條件為子接點等于父接點，不能反
這個SQL主要用于菜單的級聯查詢，給一個父接點可以查出所有的子接點。及子接點的子接點，一查到底，很實用。不過呢這個程序只能在oracle里面用，我目前還不知道在其它數據庫里是怎么調用的。等我找到了，再貼出來與大家分享。
這個程序，估計好多人看不明白，其實放了這么久我也一時沒看明白，重新測了一下，補充說明一下，不然我下次又看不懂了。
以一個windows系統的菜單為例。我那一個這樣的表menu。
說明：
mid:菜單的ID號
mname：菜單名稱
mpid：菜單的
quickey：快捷鍵
validate:權限表（存放userid,或者角色id）
如果我想知道在“文件”菜單下有那些子菜單的話。我就可以這樣用這個SQL程序：
select * from menustart with mid=1 connect by prior mid=mpid;
這樣就可以把 “文件”里的子菜單全部列出來了。當然實際應用不會這么簡單，如附加其實條件，尤其是權限管理，這時根據你的系統要求，是對個個驗證，還是對角色驗證，把這些人的ID放在validate這個字段里，組成一個字符串，N個ID用逗號隔開，（注意，在往數據庫保存時要注意對字符串處理一下，截取掉最后一個逗號這樣可以節省很多麻煩）
select * from menuwhere validate in(……)and mid in( select mid from menu //這里不能用*號了。 start with mid=1 connect by prior mid=mpid;)
最后再補充一點關于隨機查詢的代碼
select * from user order by sys_guid()
級聯刪除
Oracle在外鍵的刪除上有NO ACTION(類似RESTRICT)、CASCADE和SET NULL三種行為。
下面以學生-班級為例說明不同情況下的外鍵刪除，學生屬于班級，班級的主鍵是學生的外鍵。
-- 班級表
CRATE TABLE TB_CLASS ( ID NUMBER NOT NULL, --班級主鍵 NAME VARCHAR2(50), --班級名稱 CONSTRAINT PK_TB_CLASS PRIMARY KEY (ID) );
-- 學生表
CREATE TABLE TB_STUDENT ( ID NUMBER NOT NULL, --學生主鍵 NAME VARCHAR2(50), --學生姓名 CLASS_ID NUMBER, --學生所屬班級，外鍵 --主鍵約束 CONSTRAINT PK_TB_STUDENT PRIMARY KEY (ID), --外鍵約束 --設置級聯刪除為NO ACTION CONSTRAINT FK_TB_STUDENT_CLASS_ID FOREIGN KEY (CLASS_ID) REFERENCES TB_CLASS (ID) );
-- 添加班級數據
INSERT INTO TB_CLASS (ID, NAME) VALUES (1, '一班'); INSERT INTO TB_CLASS (ID, NAME) VALUES (2, '二班'); INSERT INTO TB_CLASS (ID, NAME) VALUES (3, '三班');
-- 添加學生數據
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);
初始班級數據
初始學生數據
NO ACTION：
NO ACTION指當刪除主表中被引用列的數據時，如果子表的引用列中包含該值，則禁止該操作執行。
現在學生外鍵級聯刪除是NO ACTION，執行刪除班級操作。
--刪除三班
DELETE FROM TB_CLASS WHERE ID=3;
Oracle會提示違反完整性約束，如圖所示。
如果想要刪除三班，必須先刪除三班的學生。
--刪除三班學生
DELETE FROM TB_STUDENT WHERE CLASS_ID=3;
--刪除三班
DELETE FROM TB_CLASS WHERE ID=3;
SET NULL：
SET NULL指當刪除主表中被引用列的數據時，將子表中相應引用列的值設置為NULL值。SET NULL有個前提就是外鍵引用列必須可以設置為NULL。
把學生表(TB_STUDENT)的外鍵刪除行為改為SET NULL。ORACLE似乎沒有MODIFY CONSTRAINT操作，只能先刪除外鍵，然后創建新的。
--刪除學生表(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，所以當刪除一班時，一班學生的CLASS_ID被設置為NULL，如圖所示。
CASCADE
CASCADE指當刪除主表中被引用列的數據時，級聯刪除子表中相應的數據行。
把學生表(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，所以當刪除二班時，二班下的學生也會被刪除。
級聯更新
Oracle本身并不支持外鍵的級聯更新，不過可以按照如下方法達到級聯更新的效果。
首先要先了解Oracle延遲約束和非延遲約束。非延遲約束就是在修改記錄的時候會立刻進行約束條件的查看，是否因為違反了某些約束條件而不能執行修改。延遲約束不會在剛進行修改的時候進行約束查看，只有提交的時候才會檢查。Oracle的級聯更新就是使用這個特性來實現的。
Oracle的外鍵默認是非延遲約束，修改學生的外鍵為延遲約束。
--刪除學生表(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;
設置觸發器，當班級表(TB_CLASS)的主鍵改變了，就更新學生表(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外鍵級聯更新方法可以用于外鍵和外鍵引用的主鍵在不同表上。不過會經常遇到以下情況，就是在數據庫中保存具有層級關系的數據時，表的外鍵引用同一個表的主鍵。這時候無法用觸發器實現級聯更新。
新聞熱點
疑難解答
