ORACLE 11G在存储过程里面遍历游标，调用job任务定

ORACLE 11G在存储过程里面遍历游标，调用job任务定时执行

ORACLE存储过程里游标遍历、调用job定时执行

1，第一种使用For 循环

for循环是比较简单实用的方法。

首先，它会自动open和close游标。解决了你忘记打开或关闭游标的烦恼。

其次,自动声明一个记录类型及定义该类型的变量，并自动fetch数据到这个变量。

注意C_ROW 这个变量无需要在循环外进行声明，无需为其指定数据类型。它是一个记录类型，具体的结构是由游标决定的。

这个变量的作用域仅仅是在循环体内。

最后，与该游标关联的所有记录都已经被取回后，循环无条件结束，不必判定游标的%NOTFOUND属性为TRUE。

for循环是用来循环游标的最好方法。高效，简洁，安全。

CREATE OR REPLACE PROCEDURE PRC_LJ IS

CURSOR C_EMP IS --声明显式游标

SELECT EMPNO,ENAME FROM EMP;

C_ROW C_EMP%ROWTYPE; --定义游标变量，该变量的类型为基于游标C_EMP的记录

BEGIN

--For 循环

FORC_ROW IN C_EMP LOOP

DBMS_OUTPUT.PUT_LINE(C_ROW.EMPNO || '--' || C_ROW.ENAME);

ENDLOOP;

 

--Fetch 循环

OPEN C_EMP;--必须要明确的打开和关闭游标

LOOP

FETCH C_EMP

INTO C_ROW;

EXIT WHEN C_EMP%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(C_ROW.EMPNO || '++' || C_ROW.ENAME);

ENDLOOP;

CLOSE C_EMP;

--While 循环

OPEN C_EMP;--必须要明确的打开和关闭游标

FETCH C_EMP INTO C_ROW;

WHILE C_EMP%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(C_ROW.EMPNO || '**' || C_ROW.ENAME);

FETCH C_EMP INTO C_ROW;

END LOOP;

CLOSE C_EMP;

END PRC_LJ;

 

 

2，第二种使用Fetch循环

注意，exit when语句一定要紧跟在fetch之后，避免多余的数据处理。

处理逻辑需要跟在exit when之后。

循环结束后要记得关闭游标。

 

CREATE OR REPLACE PROCEDURE PRC_LJ IS

CURSOR C_EMP IS --声明显式游标

SELECT EMPNO,ENAME FROM EMP;

C_ROW C_EMP%ROWTYPE; --定义游标变量，该变量的类型为基于游标C_EMP的记录

BEGIN

 

--Fetch 循环

OPEN C_EMP;--必须要明确的打开和关闭游标

LOOP

FETCH C_EMP

INTO C_ROW;

EXIT WHEN C_EMP%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(C_ROW.EMPNO || '++' || C_ROW.ENAME);

ENDLOOP;

CLOSE C_EMP;





END PRC_LJ;

3，第三种使用While循环

使用while 循环时，需要在循环之前进行一次fetch动作，游标的属性才会起作用。

而且数据处理动作必须放在循环体内的fetch方法之前，循环体内的fetch方法要放在最后，否则就会多处理一次。

CREATE OR REPLACE PROCEDURE PRC_LJ IS

CURSOR C_EMP IS --声明显式游标

SELECT EMPNO,ENAME FROM EMP;

C_ROW C_EMP%ROWTYPE; --定义游标变量，该变量的类型为基于游标C_EMP的记录

BEGIN

--While 循环

OPEN C_EMP;--必须要明确的打开和关闭游标

FETCH C_EMP INTO C_ROW;

WHILE C_EMP%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(C_ROW.EMPNO || '**' || C_ROW.ENAME);

FETCH C_EMP INTO C_ROW;

END LOOP;

CLOSE C_EMP;

END PRC_LJ;

 

4，存储过程选用FOR循环遍历游标，并做成定时job调用执行

--存储过程如下：

createor replace procedure BIS_QUIC_REPORT is

--声明游标

cursorcur_proids is select t.bis_project_id from bis_project t;

--定义游标变量

cur_pidscur_proids%rowtype;

v_monthvarchar2(2);

v_yearvarchar2(4);

begin

/**forjack.liu on 20150331*/

select to_char(sysdate,'yyyy') into v_yearfrom dual;

select case whensubstr(to_char(sysdate,'mm'),1,1)='0' then substr(to_char(sysdate,'mm'),2,1)else to_char(sysdate,'mm') end into v_month from dual;

--开始遍历

for cur_pids in cur_proids loop

insertinto zzz_test(id,name,create_time)values(v_month,'PKP_BIS_REPORT.buildQuickReport:'||cur_pids.bis_project_id,sysdate);

commit;

PKP_BIS_REPORT.buildQuickReport(cur_pids.bis_project_id,v_year,v_month,null,null,null,null);

end loop;

endBIS_QUIC_REPORT;

 

--job任务如下：

begin

sys.dbms_scheduler.create_job(job_name => 'JACK.BUILDQUICKREPORT',

job_type => 'STORED_PROCEDURE',

job_action => 'BIS_QUIC_REPORT',

start_date => to_date('01-04-2015 03:00:00','dd-mm-yyyy hh24:mi:ss'),

repeat_interval => 'Freq=Daily',

end_date => to_date(null),

job_class => 'DBMS_JOB$',

enabled => true,

auto_drop => false,

comments => '');

end;

/

5，在Plsql里面上面sql，job界面显示如下1.png：