ORACLE 11G在預存程序裡面遍曆遊標， 調用job任務定時運行

標籤：示範   content   rom   法律   bsp   line   類型   substr   一個   

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-1479382/
原黃杉 (mchdba)
----------------------------------------------------------------------------------------------------------------

ORACLE 11G在預存程序裡面遍曆遊標， 調用job任務定時運行