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

最近生产环境上遇到一需求，要求清理有主外键关联的两个大表的部分历史数据。由于在建立外键的时候没有加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-1
10 rows selected

SQL> 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-1
10 rows selected

3.创建存储过程

create or replace procedure p_delete_orders(v_ordertype in varchar2)
as
i int :=0;
begin
for 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-1
7 rows selected

SQL> 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-1
7 rows selected

6.说明

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

7.拓展

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

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

（2）触发器；

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

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