級聯(lián)查詢
在ORACLE 數(shù)據(jù)庫中有一種方法可以實(shí)現(xiàn)級聯(lián)查詢
select * //要查詢的字段
from table //具有子接點(diǎn)ID與父接點(diǎn)ID的表
start with selfid=id //給定一個startid(字段名為子接點(diǎn)ID，及開始的ID號)
connect by prior selfid=parentid //聯(lián)接條件為子接點(diǎn)等于父接點(diǎn)，不能反
這個SQL主要用于菜單的級聯(lián)查詢，給一個父接點(diǎn)可以查出所有的子接點(diǎn)。及子接點(diǎn)的子接點(diǎn)，一查到底，很實(shí)用。不過呢這個程序只能在oracle里面用，我目前還不知道在其它數(shù)據(jù)庫里是怎么調(diào)用的。等我找到了，再貼出來與大家分享。
這個程序，估計(jì)好多人看不明白，其實(shí)放了這么久我也一時(shí)沒看明白，重新測了一下，補(bǔ)充說明一下，不然我下次又看不懂了。
以一個windows系統(tǒng)的菜單為例。我那一個這樣的表menu。
說明：
mid:菜單的ID號
mname：菜單名稱
mpid：菜單的
quickey：快捷鍵
validate:權(quán)限表（存放userid,或者角色id）
如果我想知道在“文件”菜單下有那些子菜單的話。我就可以這樣用這個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)要求，是對個個驗(yàn)證，還是對角色驗(yàn)證，把這些人的ID放在validate這個字段里，組成一個字符串，N個ID用逗號隔開，（注意，在往數(shù)據(jù)庫保存時(shí)要注意對字符串處理一下，截取掉最后一個逗號這樣可以節(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會提示違反完整性約束，如圖所示。
如果想要刪除三班，必須先刪除三班的學(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有個前提就是外鍵引用列必須可以設(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外鍵
復(fù)制代碼 代碼如下:
ALTER TABLE TB_STUDENT ADD CONSTRAINT FK_TB_STUDENT_CLASS_ID FOREIGN KEY (CLASS_ID) REFERENCES TB_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)更新就是使用這個特性來實(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í)，表的外鍵引用同一個表的主鍵。這時(shí)候無法用觸發(fā)器實(shí)現(xiàn)級聯(lián)更新。
您可能感興趣的文章:- oracle數(shù)據(jù)庫的刪除方法詳解
- Oracle刪除數(shù)據(jù)報(bào)ORA 02292錯誤的巧妙解決方法
- oracle 數(shù)據(jù)按主鍵刪除慢問題的解決方法
- Oracle刪除重復(fù)的數(shù)據(jù)，Oracle數(shù)據(jù)去重復(fù)
- Oracle誤刪除表數(shù)據(jù)后的數(shù)據(jù)恢復(fù)詳解
- 徹底刪除Oracle數(shù)據(jù)庫的方法
- oracle查詢重復(fù)數(shù)據(jù)和刪除重復(fù)記錄示例分享
- oracle數(shù)據(jù)庫添加或刪除一列的sql語句
- oracle 批量刪除表數(shù)據(jù)的幾種方法
