Oracle 存储过程及游标，目的提高delete效率

最新推荐文章于 2023-07-15 11:56:02 发布

曼森

最新推荐文章于 2023-07-15 11:56:02 发布

阅读量990

点赞数 1

分类专栏： Oracle 文章标签：数据库

本文链接：https://blog.youkuaiyun.com/duanhwonline/article/details/108438224

版权

Oracle 专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍了一种在Oracle数据库中进行批量数据删除的方法，并通过循环处理减少每次删除的行数来提高操作效率。此外，还介绍了如何创建、运行、停止和删除Oracle定时任务，以实现定期自动执行特定数据库操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT s.sid, s.serial# FROM v$locked_object lo, dba_objects ao, v$session s 
WHERE ao.object_id = lo.object_id AND lo.session_id = s.sid; 

--ALTER system KILL session '3767,33667';


declare
v_indx number:=0;
v_totalRow number;
v_count number;
begin
select /*+parallel(32)*/ count(*) into v_totalRow  from othpdata.T_PRD_TYDK_PR_BDM_PP   where day_id='20170614'; 
select ceil(v_totalRow/10000) into v_count from dual; 
loop
  exit when v_indx=v_count;
  delete /*+parallel(32)*/  from othpdata.T_PRD_TYDK_PR_BDM_PP   where rownum<=10000 and day_id='20170614'  ;  
  commit;
  dbms_output.put_line(to_char(sysdate,'yyyymmdd hh24:mi:ss')||'  '||(v_indx+1)*10000||'删除!');
  v_indx:=v_indx+1;
end loop;
end;


DECLARE
  V_INDX  NUMBER;
  V_COUNT NUMBER;
  CURSOR C_DAYID IS
    SELECT /*+PARALLEL(32)*/
    DISTINCT A.DAY_ID, COUNT(*) AS TOTALROW
      FROM OTHPDATA.T_PRD_TYDK_PR_BDM_PP A
     WHERE DAY_ID BETWEEN '20170610' AND '20171031'
     GROUP BY DAY_ID
    HAVING COUNT(*) < 100000;

BEGIN
  DBMS_OUTPUT.ENABLE(BUFFER_SIZE => NULL);
  FOR OBJ0 IN C_DAYID LOOP
    DBMS_OUTPUT.PUT_LINE(OBJ0.DAY_ID);
    SELECT CEIL(OBJ0.TOTALROW / 10000) INTO V_COUNT FROM DUAL;
    V_INDX := 0;
    LOOP
      EXIT WHEN V_INDX = V_COUNT;
      --DELETE /*+PARALLEL(32)*/  FROM OTHPDATA.T_PRD_TYDK_PR_BDM_PP   WHERE ROWNUM<=10000 AND DAY_ID=OBJ0.DAY_ID  ;  
      --COMMIT;
      DBMS_OUTPUT.PUT_LINE(CHR(9) ||
                           TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS') ||CHR(13)||
                           (V_INDX + 1) * 10000 || '删除!');
      V_INDX := V_INDX + 1;
    END LOOP;
  END LOOP;
END;

--创建定时任务

select * from dba_jobs;
select * from dba_jobs_running;

declare

     job number;

begin

    sys.dbms_job.submit(job => :72,

    what => 'TEST0;',

    interval => ' TRUNC(sysdate,'mi') + 2/ (24*60)');--每天86400秒钟，即一秒钟运行prc_name过程一次

    commit;

end;

--停止定时任务
begin

dbms_job.broken(27, true, sysdate);---该27表示oracle系统中job定时任务的job字段

commit;

end；
--开始定时任务
begin

dbms_job.run(27);

end；

begin

    dbms_job.broken(423, false,sysdate);---该423表示的是oracle系统中job定时任务的job字段

    commit;

end;

--删除定时任务
begin

dbms_job.remove(27);    ---该27表示的是oracle系统中job定时任务的job字段

end；



===========================发布版
DECLARE
V_LAST_TXDATE ODSETL.ETL_JOB.LAST_TXDATE%TYPE;--T_BWT_DAPD_PR_BDM_PP 查询D-1是否有数据
V_LAST_JOBSTATUS ODSETL.ETL_JOB.LAST_JOBSTATUS%TYPE;
  V_INDX  NUMBER;--循环变量
  V_COUNT NUMBER;--循环次数
  CURSOR C_DAYID IS
    SELECT /*+PARALLEL(32)*/
    DISTINCT A.DAY_ID, COUNT(*) AS TOTALROW
      FROM OTHPDATA.T_PRD_TYDK_PR_BDM_PP A
     WHERE DAY_ID BETWEEN '20170901' AND '20171031'
     GROUP BY DAY_ID
    HAVING COUNT(*) < 100000;

BEGIN
 DBMS_OUTPUT.ENABLE(BUFFER_SIZE => NULL);
SELECT LAST_TXDATE ,LAST_JOBSTATUS INTO  V_LAST_TXDATE,V_LAST_JOBSTATUS FROM ODSETL.ETL_JOB A  WHERE A.ETL_SYSTEM='BWT' AND A.ETL_JOB='T_BWT_DAPD_PR_BDM_PP' ;
IF V_LAST_TXDATE=TO_CHAR(SYSDATE-1,'YYYY-MM-DD') AND V_LAST_JOBSTATUS='Done' THEN
 DBMS_OUTPUT.PUT_LINE(CHR(9)||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')||' 已完成');
   FOR OBJ0 IN C_DAYID LOOP
    DBMS_OUTPUT.PUT_LINE(OBJ0.DAY_ID);
    SELECT CEIL(OBJ0.TOTALROW / 10000) INTO V_COUNT FROM DUAL;
    V_INDX := 0;
    LOOP
      EXIT WHEN V_INDX = V_COUNT;
      DELETE /*+PARALLEL(32)*/  FROM OTHPDATA.T_PRD_TYDK_PR_BDM_PP   WHERE ROWNUM<=10000 AND DAY_ID=OBJ0.DAY_ID  ;  
      COMMIT;
      DBMS_OUTPUT.PUT_LINE(CHR(9) ||
                           TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS') ||CHR(13)||
                           (V_INDX + 1) * 10000 || '删除!');
      V_INDX := V_INDX + 1;
    END LOOP;
  END LOOP;
ELSE 
  DBMS_OUTPUT.PUT_LINE(CHR(9)||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')||' 未完成!');
END IF;
 
END;