create table t_dept
(
dept_id number primary key,
dept_name varchar2(20)
);create table t_emp
(
e_id number primary key,
dept_id number references t_dept(dept_id),
e_name varchar2(20)
)create table t_user
(
u_id number primary key,
e_id number references t_emp(e_id),
u_name varchar2(20)
)insert into t_dept values(1,'开发部');
insert into t_dept values(2,'销售部');delete from t_emp where e_id=3;insert into t_emp values(101,1,'张三');
insert into t_emp values(102,2,'李四');
insert into t_emp values(103,2,'王五');insert into t_user values(1001,101,'张小三');
insert into t_user values (1002,102,'李小四');
insert into t_user values(1003,102,'李小小四');
insert into t_user values(1004,102,'李小小小四');
insert into t_user values (1005,103,'王小五');现在我要删除表t_dept,因为有外键关系,所以要把存在于t_dept中的emp下面所有的user的信息都备份,这个存储过程怎么写?
怎么写这个存储过程?
新建另外一个表,在每次执行存储过程的时候先清空该表,然后把t_User表中的数据插入到这个空表,然后你想干什么都可以了,不过你这个操作貌似有点...其实这里首先是涉及到t_emp表,然后才涉及到t_user表.
CREATE OR REPLACE PROCEDURE del_dept (p_dept_id IN NUMBER )
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE t_user_copy AS SELECT * FROM t_user a WHERE EXISTS
(SELECT 1 FROM t_emp b WHERE a.e_id =e_id AND dept_id='||p_dept_id||')';
DELETE FROM t_user a WHERE EXISTS (SELECT 1 FROM t_emp b WHERE a.e_id =e_id AND dept_id=p_dept_id);
DELETE FROM t_emp WHERE dept_id = p_dept_id;
DELETE FROM t_dept WHERE dept_id = p_dept_id;
COMMIT;
END;