ORACLE 11G在預存程序裡面遍曆遊標, 調用job任務定時執行,11gjob
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)
----------------------------------------------------------------------------------------------------------------