用存储过程删除外键关联表的数据

来源：互联网发布：mac装什么虚拟机好编辑：程序博客网时间：2024/05/04 05:43

最近生产环境上遇到一需求，要求清理有主外键关联的两个大表的部分历史数据。由于在建立外键的时候没有加on delete cascade;所以不能级联删除。于是就想到了用存储过程，来批量删除历史数据。具体实现方法如下：

1.创建测试表

create table customers(customer_id number,                       customer_name varchar2(50),                       customer_location varchar2(50),                       customer_date date default sysdate,                       constraint PK_CUSTOMERS primary key (customer_id)                       );      --创建父表客户表create table orders(order_id number,                    customer_id number,                    order_type varchar2(20),                    order_date date default sysdate,                    constraint PK_ORDERS primary key (order_id)                    );      --创建子表订单表alter table orders add constraint fk_orders_customers foreign key (customer_id) references customers (customer_id);   --添加外键约束

2.插入数据

SQL> select * from customers;CUSTOMER_ID     CUSTOMER_NAME              CUSTOMER_LOCATION             CUSTOMER_DATE-----------     -------------------------- ----------------------------- ------------          1     张三                        武汉                          2010-1-1          2     李四                        深圳                          2010-1-1          3     王五                        广州                          2010-1-1          4     赵六                        北京                          2010-1-1          5     李刚                        上海                          2010-1-1          6     张婷婷                      杭州                          2010-1-1          7     李盼盼                      南京                          2010-1-1          8     陈小东                      长沙                          2010-1-1          9     王强                        成都                          2010-1-1         10     孙菲菲                      重庆                          2010-1-110 rows selectedSQL> select * from orders;  ORDER_ID CUSTOMER_ID     ORDER_TYPE          ORDER_DATE---------- -----------     ------------------- -----------         1           3     外套                 2010-1-1         2           2     裤子                 2010-1-1         3           5     帽子                 2010-1-1         4           8     鞋子                 2010-1-1         5           7     外套                 2010-1-1         6           6     裤子                 2010-1-1         7           4     帽子                 2010-1-1         8          10     鞋子                 2010-1-1         9           1     外套                 2010-1-1        10           9     裤子                 2010-1-110 rows selected

3.创建存储过程

create or replace procedure p_delete_orders(v_ordertype in varchar2)asi int :=0;beginfor c in  (select customer_id from orders where trim(order_type)=trim(v_ordertype)    ) loop   delete from orders where customer_id=c.customer_id;   delete from customers where customer_id=c.customer_id;   i:=i+1;   if mod(i,5000)=0   then commit;   end if;   end loop;commit;end;

4.执行存储过程

begin  p_delete_orders('外套');end;

5.验证结果

SQL> select * from customers;CUSTOMER_ID     CUSTOMER_NAME              CUSTOMER_LOCATION             CUSTOMER_DATE-----------     -------------------------  ----------------------------- -------------          2     李四                        深圳                          2010-1-1          4     赵六                        北京                          2010-1-1          5     李刚                        上海                          2010-1-1          6     张婷婷                      杭州                          2010-1-1          8     陈小东                      长沙                          2010-1-1          9     王强                        成都                          2010-1-1         10     孙菲菲                      重庆                          2010-1-17 rows selectedSQL> select * from orders;  ORDER_ID CUSTOMER_ID     ORDER_TYPE          ORDER_DATE---------- -----------     ------------------- -----------         2           2     裤子                 2010-1-1         3           5     帽子                 2010-1-1         4           8     鞋子                 2010-1-1         6           6     裤子                 2010-1-1         7           4     帽子                 2010-1-1         8          10     鞋子                 2010-1-1        10           9     裤子                 2010-1-17 rows selected

6.说明

此环境有其特殊性，父表和子表的数据具有一一对应关系，每一条父表id在子表中只有一条子表id与其对应。若环境有变，应随之改变存储过程，在此不做其它演示。

7.拓展

顺便在此说一下常用的删除外键关联的表的数据方法：

（1）级联删除，要求建立外键时加on delete cascade;

（2）触发器；

（3）用存储过程实现，即本文所述方法。

以上各具体实现方法，不在此演示。

0 0