ORACLE存儲過程裡游標遍歷、調用job定時執行
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;
注意,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;
使用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;
--存儲過程如下:
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; /