Oracle数据库中的级联查询、级联删除、级联更新操作教程

select *    //要查询的字段
from table    //具有子接点ID与父接点ID的表 
start with selfid=id  //给定一个startid(字段名为子接点ID，及开始的ID号)
connect by prior selfid=parentid  //联接条件为子接点等于父接点，不能反

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

select * from menu
where 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()

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); 

DELETE FROM TB_CLASS WHERE ID=3; 

DELETE FROM TB_STUDENT WHERE CLASS_ID=3; 

DELETE FROM TB_CLASS WHERE ID=3; 

ALTER TABLE TB_STUDENT DROP CONSTRAINT FK_TB_STUDENT_CLASS_ID; 

DELETE FROM TB_CLASS WHERE ID=1; 

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; 

DELETE FROM TB_CLASS WHERE ID=2; 

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; 

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;