黄杉

Roads Untraveled

  • 博客访问: 3432738
  • 博文数量: 176
  • 用 户 组: 普通用户
  • 注册时间: 2011-09-05 16:54
  • 认证徽章:
个人简介

Happy is the man who is living by his hobby.

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(176)

文章存档

2015年(33)

2014年(129)

2013年(13)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

分类: Oracle

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:


 ----------------------------------------------------------------------------------------------------------------
<版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!>
原博客地址:       http://blog.itpub.net/26230597/viewspace-1480680/
原作者:黄杉 (mchdba)
----------------------------------------------------------------------------------------------------------------

阅读(9686) | 评论(0) | 转发(2) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册