oracle使用游标的存储过程，清理带外键的数据。

最新推荐文章于 2022-02-07 01:37:02 发布

weixin_37911140

最新推荐文章于 2022-02-07 01:37:02 发布

阅读量130

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/weixin_37911140/article/details/88695839

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

好久没有写oracle 的存储过程跟游标。今天接了个需求刚好练练手。好记性不如烂笔头

create or replace PROCEDURE QQQ
is
      tempKey number;
      tempAge number;
      tempInstCursor SYS_REFCURSOR;
      tempMessage varchar2(300);
      workDate date;
      V_DATACOUNT NUMBER;
BEGIN
v_dataCount :=1;
tempMessage:='success';

   workDate := sysdate-30;
   if workDate is not null then
     begin

        open tempInstCursor for
              select AAAKey,AAAage from AAA it where it.whenmodified <TRUNC(workDate) ;
        loop
       -- step1 使用游标方式查询出符合条件的执行遍历
       fetch tempInstCursor into tempKey,tempAge;
              dbms_output.put_line('AAAKey='||tempKey ||'AAAage='||tempAge);
              exit when tempInstCursor%NOTFOUND;

delete from BBB where AAAKEY=tempKey;

delete from CCC where DDDDKEY=(select t.DDDDKEY from DDD t where t.AAAKEY=tempKey);
delete from DDD where AAAKEY=tempKey;

delete from AAA where AAAKEY = AAAKey;

              v_dataCount := v_dataCount + 1;
             if v_dataCount >= 500 then
                dbms_output.put_line('commit; v_dataCount'||v_dataCount);
                v_dataCount :=0;
                commit;
              end if;
        end loop;

        commit;
      exception
            when OTHERS then
              tempMessage:='Failed';
              update BBB set version=0 ;
              commit;
              update AAA set version=0 ;
              commit;
              update CCC set version=0 ;
              commit;
      end;
      tempMessage := tempMessage||',finish time:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');

end if;

END QQQ;