plsql – 如何使用每日预定作业调用oracle中的存储过程?
发布时间:2021-01-23 22:20 所属栏目:[站长百科] 来源:网络整理
导读:我是oracle工作脚本的新手.我写了一些清除程序来清理所有旧数据并保留最近3个月的数据…程序执行成功.我也在手动调用时工作.程序如下: CREATE OR REPLACE PROCEDURE Archive IS v_query varchar2(2048); v_tablename VARCHAR2(50); v_condition varchar2(
我是oracle工作脚本的新手.我写了一些清除程序来清理所有旧数据并保留最近3个月的数据…程序执行成功.我也在手动调用时工作.程序如下: CREATE OR REPLACE PROCEDURE Archive IS v_query varchar2(2048); v_tablename VARCHAR2(50); v_condition varchar2(50); TYPE cur_typ IS REF CURSOR; c cur_typ; BEGIN OPEN c for 'select tablename,columnname from pseb.purge_tables'; FETCH c INTO v_tablename,v_condition; LOOP EXIT WHEN c%NOTFOUND; if(v_tablename ='cfw.DCTBLPERFCUMULATIVEMASTER') then v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB3MAINREG where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB4TODENERG where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERDFCUMULATIVEB5MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB6TODREG where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB7MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB8MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; v_query:='delete FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; else begin v_query:='delete FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; end; end if; FETCH c INTO v_tablename,v_condition; end LOOP; close c; END; --Procedure 我的JOb脚本如下: begin DBMS_SCHEDULER.CREATE_JOB ( job_name => 'purgeproc_automation',job_type => 'STORED_PROCEDURE',job_action => 'call pseb.archive();',repeat_interval => 'FREQ=DAILY;INTERVAL=2',/* every other day */ auto_drop => false,enabled => true,comments => 'My new job'); end; / 作业已成功创建,但作业状态失败,但未成功.它背后的原因是什么?它返回以下错误: ORA-06550: line 1,column 728: PLS-00103: Encountered the symbol "PSEB" when expecting one of the following: := . ( @ % ; The symbol ":=" was substituted for "PSEB" to continue. 请指导我解决这个问题…… 解决方法哦,你的代码看起来很复杂.首先考虑这种简化:CREATE OR REPLACE PROCEDURE Archive IS v_query varchar2(2048); BEGIN FOR REC IN (select tablename,columnname condition from pseb.purge_tables) LOOP if(rec.tablename ='cfw.DCTBLPERFCUMULATIVEMASTER') then v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB3MAINREG where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB4TODENERG where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERDFCUMULATIVEB5MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB6TODREG where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB7MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB8MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; v_query:='delete FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; else v_query:='delete FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; end if; END LOOP; END; --Procedure dbms_job.submit的替代作业定义: declare jid number; begin dbms_job.submit( JOB => jid,WHAT => 'pseb.archive;',NEXT_DATE => SYSDATE,INTERVAL => 'sysdate +2'); end; / commit; -- <<--added commit here 一种检查工作的方法: select * from user_jobs; 【免责声明】本站内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。 |
推荐文章
热点阅读