Oracle通過Job調(diào)用存儲過程

1、這是本人第一次處理Oracle的存儲過程以及計劃任務(wù)（job）的事情；期間遇到了很多問題在此將這次試用job調(diào)用自己的編寫的存儲過程的過程中遇到的問題做一總結(jié)；任務(wù)：實時采集系統(tǒng)會每5分鐘給中心系統(tǒng)傳送一個流量數(shù)據(jù)，但是沒有給出累計流量數(shù)據(jù)；1、編寫一個存儲過程WATERQCAL用于計算流量增量以及得出最后的累計流量；2、用一個任務(wù)計劃每隔5分鐘，統(tǒng)一進(jìn)行執(zhí)行一次WATERQCAL存儲過程；創(chuàng)建存儲過程代碼如下：CREATE OR REPLACE PROCEDURE WaterQCal AS-如果用引號將存儲過程名引起來后就會保存成WaterQCal存儲過程；如果不引起來的話最終保存的是WAT

2、ERQCAL；oldACC_W NUMBER;oldACC_W_PQ NUMBER;newACC_W NUMBER;BEGINDECLARE-查詢出一個集合 CURSOR mpcd_number ISSELECT DISTINCT MP_CD from WR_MP_Q_R WHERE ACC_W is NULL;-定義行游標(biāo)變量rcd_number mpcd_number % ROWTYPE; -循環(huán)開始 BEGIN-開啟游標(biāo) open mpcd_number; loop-開始游標(biāo)循環(huán)操作 fetch mpcd_number into rcd_number; exit when mpcd_nu

3、mber%notfound; -開始查詢某一個個測站數(shù)據(jù)DECLARE -查詢出一個某測站的所有未計算瀏覽的集合CURSOR sinSTB ISselect * from WR_MP_Q_R WHERE ACC_W is null and MP_CD=rcd_number.MP_CD order by TM ASC;-定義行游標(biāo)變量sinSt sinSTB % ROWTYPE;BEGIN-開啟單站游標(biāo)open sinSTB;loopfetch sinSTBinto sinSt;exit when sinSTB%notfound;BEGIN-開始計算流量-查詢出最有一條計算過流量的記錄BEGIN

4、 select ACC_W into oldACC_W from WR_MP_Q_R WHERE ACC_W is not null and MP_CD=sinSt.MP_CD and rownum=1 order by TM DESC; exception-處理異常沒有數(shù)據(jù)時；該如何處理；因為有異常處理此處的select語句以及異常都必須單獨用begin end括起來；when no_data_found thenoldACC_W:=0;END;-計算流量增量oldACC_W_PQ:=ABS(sinSt.MP_Q)/12;-計算當(dāng)前累計流量newACC_W:=ABS(oldACC_W)+AB

5、S(oldACC_W_PQ);-更新當(dāng)前累計流量update WR_MP_Q_R set ACC_W=newACC_W where MP_CD=sinSt.MP_CD and TM=sinSt.TM;END;END LOOP;close sinSTB; -關(guān)閉游標(biāo)END; END loop; close mpcd_number; END; END;創(chuàng)建Oracle計劃任務(wù)的代碼如下：declare jobNum NUMBER;begin sys.dbms_job.submit(job = jobNum, what = SWATER.WaterQCal;, next_date = trunc(s

6、ysdate)+1+8/1440, interval = trunc(sysdate,mi)+1+8/1440); commit;end;/通過plsql工具創(chuàng)建：在編制存儲過程時應(yīng)該注意的是1、每一個語句結(jié)束時都必須采用；結(jié)束。否則會報sql語句不完整；2、CREATE OR REPLACE PROCEDURE PROCEDURE_NAME AS -該語句部分PROCEDURE_NAME不能夠用引號引起來；如果引號引起來的話，名字中的小寫字母之類的還會原樣保留；但是Oracle默認(rèn)是需要將存儲過程的名字轉(zhuǎn)換成大寫的；并且后面創(chuàng)建Job是如果輸入之前帶小寫字母的名字時又會轉(zhuǎn)換成大寫的時候就會報

7、找不到存儲過程；3、for循環(huán)來操作游標(biāo)，不用open 游標(biāo);以及 close游標(biāo) 操作否則會報錯；4、針對每一個包含異常處理語句的sql都必須防止在begin以及end之中；否則可能會出現(xiàn)部分語句不執(zhí)行，直接跳轉(zhuǎn)到下一個end處； 5、循環(huán)處理1）、For 循環(huán)For . in . LOOP-執(zhí)行語句end LOOP;(1)循環(huán)遍歷游標(biāo)create or replace procedure test() asCursor cursor is select name from student; name varchar(20);beginfor name in cursor LOOPbegin

8、 dbms_output.putline(name); end;end LOOP;end test;(2)循環(huán)遍歷數(shù)組 create or replace procedure test(varArray in myPackage.TestArray) as-(輸入?yún)?shù)varArray 是自定義的數(shù)組類型，定義方式見標(biāo)題6)i number;begini := 1; -存儲過程數(shù)組是起始位置是從1開始的，與java、C、C+等語言不同。因為在Oracle中本是沒有數(shù)組的概念的，數(shù)組其實就是一張-表(Table),每個數(shù)組元素就是表中的一個記錄，所以遍歷數(shù)組時就相當(dāng)于從表中的第一條記錄開始遍歷fo

9、r i in 1.varArray.count LOOP dbms_output.putline('The No.'| i | 'record in varArray is:'|varArray(i); end LOOP;end test;2)、While 循環(huán)while 條件語句 LOOPbeginend;end LOOP;E.gcreate or replace procedure test(i in number) asbeginwhile i SWATER.WaterQCal;的WaterQCal的存儲過程名必須以;結(jié)尾；否則會報如下錯誤：Err

10、 ORA-06550: 第 1 行, 第 110 列: PLS-00103: 出現(xiàn)符號 END在需要下列之一時： := . ( % ;符號 ; 被替換為 END 后繼續(xù)。ORA-06512: 在 SYS.DBMS_JOB, line 82ORA-06512: 在 SYS.DBMS_JOB, line 140ORA-06512: 在 line 32、如果前面創(chuàng)建存儲過程中將存儲過程名用引號引起來后，因為存儲郭晨被保存成了WaterQCal；而what中指定的存儲過程SWATER.WaterQCal會被轉(zhuǎn)換SWATER.WATERQCAL；所以會報如下錯誤：Err ORA-06550: 第 1 行

11、, 第 93 列: PLS-00201: 必須聲明標(biāo)識符 SWATER.WATERQCALORA-06550: 第 1 行, 第 93 列: PL/SQL: Statement ignoredORA-06512: 在 SYS.DBMS_JOB, line 82ORA-06512: 在 SYS.DBMS_JOB, line 140ORA-06512: 在 line 33、時間間隔設(shè)置必須是在指定的時間必須是當(dāng)前時間的后面的值（即如果由interval計算出來的next time如果在當(dāng)前時間之前，會怎么處理？就沒法處理，會報錯），否則會報如下錯誤ORA-23420:間隔必須以將來的一個時間作評估

12、ORA-06512:在 SYS.DBMS_JOB,line 57ORA-06512:在 SYS.DBMS_JOB,line 134ORA-06512:在 line 34、如果 sys.dbms_job.submit(job = :job,用的是:job作為參數(shù)，并且是通過查詢語句執(zhí)行的話會報如下錯誤：Err ORA-01008: 并非所有變量都已綁定解決辦法如本示例：declare job NUMBER;-定義變量；并采用sys.dbms_job.submit(job = job方式去掉冒號；5、參數(shù)說明1)、job參數(shù)job是一個整數(shù)，用來唯一地標(biāo)示一個任務(wù)。該參數(shù)既可由用戶指定也可由系統(tǒng)自

13、動賦予，這完全取決于提交任務(wù)時選用了那一個任務(wù)提交過程。DBMS_JOB.SUBMIT過程通過獲得序列SYS.JOBSEQ的下一個值來自動賦予一個任務(wù)號。該任務(wù)號是作為一個OUT參數(shù)返回的，所以調(diào)用者隨后可以識別出提交的任務(wù)。而DBMS_JOB.ISUBMIT過程則由調(diào)用者給任務(wù)指定一個識別號，這時候，任務(wù)號的唯一性就完全取決于調(diào)用者了。除了刪除或者重新提交任務(wù)，一般來說任務(wù)號是不能改變的。即使當(dāng)數(shù)據(jù)庫被導(dǎo)出或者被導(dǎo)入這樣極端的情況，任務(wù)號也將被保留下來。所以在執(zhí)行含有任務(wù)的數(shù)據(jù)的導(dǎo)入/導(dǎo)出操作時很可能會發(fā)生任務(wù)號沖突的現(xiàn)象。 2)、whatwhat參數(shù)是一個可以轉(zhuǎn)化為合法PL/SQL調(diào)用的字

14、符串，該調(diào)用將被任務(wù)隊列自動執(zhí)行。在what參數(shù)中，如果使用文字字符串，則該字符串必須用單引號括起來。 what參數(shù)也可以使用包含我們所需要字符串值的VARCHAR2變量。實際的PL/SQL調(diào)用必須用分號隔開。在PL/SQL調(diào)用中如果要嵌入文字字符串，則必須使用兩個單引號。 what參數(shù)的長度在Oracle7.3中限制在2000個字節(jié)以內(nèi)，在Oracle 8.0以后，擴大到了4000個字節(jié)，這對于一般的應(yīng)用已完全足夠。該參數(shù)的值一般情況下都是對一個PL/SQL存儲過程的調(diào)用。在實際應(yīng)用中，盡管可以使用大匿名Pl/SQL塊，但建議大家最好不要這樣使用。還有一個實際經(jīng)驗就是最好將存儲過程調(diào)用封裝在

15、一個匿名塊中，這樣可以避免一些比較莫名錯誤的產(chǎn)生。我來舉一個例子，一般情況下，what參數(shù)可以這樣引用： what =my_procedure(parameter1);但是比較安全的引用，應(yīng)該這樣寫：what =begin my_procedure(parameter1); end; 任何時候，我們只要通過更改what參數(shù)就可以達(dá)到更改任務(wù)定義的目的。但是有一點需要注意，通過改變what參數(shù)來改變?nèi)蝿?wù)定義時，用戶當(dāng)前的會話設(shè)置也被記錄下來并成為任務(wù)運行環(huán)境的一部分。如果當(dāng)前會話設(shè)置和最初提交任務(wù)時的會話設(shè)置不同，就有可能改變?nèi)蝿?wù)的運行行為。意識到這個潛在的副作用是非常重要的，無論何時只要應(yīng)用到

16、任何DBMS_JOB過程中的what參數(shù)時就一定要確保會話設(shè)置的正確。 3)、next_dateNext_date參數(shù)是用來調(diào)度任務(wù)隊列中該任務(wù)下一次運行的時間。這個參數(shù)對于DBMS_JOB.SUBMIT和DBMS_JOB.BROKEN這兩個過程確省為系統(tǒng)當(dāng)前時間，也就是說任務(wù)將立即運行。 當(dāng)將一個任務(wù)的next_date參數(shù)賦值為null時，則該任務(wù)下一次運行的時間將被指定為4000年1月1日，也就是說該任務(wù)將永遠(yuǎn)不再運行。在大多數(shù)情況下，這可能是我們不愿意看到的情形。但是，換一個角度來考慮，如果想在任務(wù)隊列中保留該任務(wù)而又不想讓其運行，將next_date設(shè)置為null卻是一個非常簡單的辦

17、法。 Next_date也可以設(shè)置為過去的一個時間。這里要注意，系統(tǒng)任務(wù)的執(zhí)行順序是根據(jù)它們下一次的執(zhí)行時間來確定的，于是將next_date參數(shù)設(shè)置回去就可以達(dá)到將該任務(wù)排在任務(wù)隊列前面的目的。這在任務(wù)隊列進(jìn)程不能跟上將要執(zhí)行的任務(wù)并且一個特定的任務(wù)需要盡快執(zhí)行時是非常有用的。 4)、IntervalInternal參數(shù)是一個表示Oracle合法日期表達(dá)式的字符串。這個日期字符串的值在每次任務(wù)被執(zhí)行時算出，算出的日期表達(dá)式有兩種可能，要么是未來的一個時間要么就是null。這里要強調(diào)一點：很多開發(fā)者都沒有意識到next_date是在一個任務(wù)開始時算出的，而不是在任務(wù)成功完成時算出的。 當(dāng)任務(wù)成

18、功完成時，系統(tǒng)通過更新任務(wù)隊列目錄表將前面算出的next_date值置為下一次任務(wù)要運行的時間。當(dāng)由interval表達(dá)式算出next_date是null時，任務(wù)自動從任務(wù)隊列中移出，不會再繼續(xù)執(zhí)行。因此，如果傳遞一個null值給interval參數(shù)，則該任務(wù)僅僅執(zhí)行一次。 通過給interval參數(shù)賦各種不同的值，可以設(shè)計出復(fù)雜運行時間計劃的任務(wù)。附錄的“任務(wù)間隔和日期算法”將對interval表達(dá)式進(jìn)行詳細(xì)討論，并給出一個實際有用interval表達(dá)式的例子。6、時間間隔指定函數(shù)：TRUNC函數(shù)返回以指定元素格式截去一部分的日期值；語法格式TRUNC（date,fmt）date是時間；fm

19、t格式化字符串；默認(rèn)截取出日期部分值。1）:每分鐘執(zhí)行Interval = TRUNC(sysdate,mi) + 1/ (24*60)2）:每天定時執(zhí)行例如：每天的凌晨1點執(zhí)行Interval = TRUNC(sysdate) + 1 +1/ (24)3）:每周定時執(zhí)行例如：每周一凌晨1點執(zhí)行Interval = TRUNC(next_day(sysdate,星期一)+1/244）:每月定時執(zhí)行例如：每月1日凌晨1點執(zhí)行Interval =TRUNC(LAST_DAY(SYSDATE)+1+1/245）:每季度定時執(zhí)行例如每季度的第一天凌晨1點執(zhí)行Interval = TRUNC(ADD_MONTHS(SYSDATE,3),Q) + 1/246）:每半年定時執(zhí)行例如：每年7月1日和1月1日凌晨1點Interval = ADD_MONTHS(trunc(sysdate,yyyy),6)+1/247）:每年定時執(zhí)行例如：每年1月1日凌晨1點執(zhí)行Interval =ADD_MONTHS(trunc(sysdate,yyyy),12)+1/24示例時間間隔：trunc(sysdate) + 1/1440為當(dāng)天