Oracle透過job定時建立表

整個過程總共分為兩步。雖然整個過程都非常簡單，但是對於初學Oracle的生手還是有很多地方需要注意的。

首先介紹一下，建立該JOB的背景，因為每天更新的直播和點播節目資訊比較多，為了方便處理，需要每天建立一張表來記錄更新的節目資訊，當前資料庫中已經有一張tbl_programme的表，每天建立的表的欄位需要同tbl_programme保持一致，每天新建立的表的名稱格式為tbl_programme_日期（例如：tbl_programme_20090214）規定每天晚上1點鐘生成該天的新表。

第一步：建立一個執行建立操作的儲存過程

在這一步首先要解決的問題就是構造表名。在Oracle中格式話輸出時間可以用to_char函式來處理，例如：

以上SQL格式化輸出了時間，要得到我們所需要的格式直接修改一下SQL即可

得到時間格式字串後我們就可以將表名的字首和時間連線在一起形成完整的表名。這裡需要注意，在Oracle中連結兩個字串需要使用‘||’符號，而在Sql Server中直接使用‘+’號就可以了，因為我以前一直在Sql Server下程式設計，好久都沒編寫Oracle的SQL所以費了很大的功夫才發現這個問題。完整的Sql就是

接下來就是建立表的程式碼了，因為新表需要tbl_programme保持一致，所以直接CTAS來建立表那是非常適合的了，程式碼如下：

Create table tablename as select * from tbl_programme

如果需要指定一個TableSpace則將該SQL做適當修改：

Create table tablename tablespace p2p as select * from tbl_programme

所以整個建立儲存過程的SQL就是

這裡還需要注意一下在Oracle裡面如果要對一個變數賦值的話有兩種方式:

(1) 使用:=進行賦值

(2) 使用select ‘xjkxj ‘ into 變數名稱 from tabname

另外，在儲存過程中定義變數的時候一般放在as/is後begin前面。在儲存過程一般是不能直接使用create table，truncate table這類似的語句的，如果要使用這些語句必須使用excute immediate + 所要執行的sql語句來實現。

注意上面用紅色標誌的語句：Authid Current_User

這個語句比較重要，如果我們在建立儲存過程的時候不新增這條語句執行該儲存過程將不會成功，原因是預設情況向儲存過程是沒有Create table等許可權的，即使當前使用者有DBA的許可權也不行，如果儲存過程中存在建立表的操作，可以有以下兩種方式來解決該問題。

(1) 顯示的賦予該使用者Create table的許可權，grant create table to user。

(2) 在儲存過程中使用Authid Current_User 標識使用當前使用者的許可權。

第二步：建立JOB

建立JOB就比較簡單了，下面就是建立JOB的程式碼

這裡需要注意的是，在submit方法的前面一定要先定義job這個變數，另外，submit方法的第二個引數是一個儲存過程的名，記得在後面新增“;“號，在next_date是一個時間型別變數而不是一個字串，所以需要注意不要把它當成字串，不需要對該引數加引號。最後一個引數interval是一個字串型別，記得新增引號。最常見的錯誤如下圖所示：

ORA-01008: not all variables bound就是沒有定義變數的意思。一定記的在使用submit方法時定義jobid變數。

下面是常有的設定Interval的方法：

² 每天固定時間執行，比如早上8:10分鐘：Trunc(Sysdate+1) + 8/24

² 每天：trunc(sysdate+1)

² 每週：trunc(sysdate+7)

² 每月：trunc(sysdate+30)

² 每個星期日：next_day(trunc(sysdate),'SUNDAY')

² 每天6點：trunc(sysdate+1)+6/24

² 半個小時：sysdate+30/1440

需要用到的完整SQL如下：

第三步：異常情況處理

JOB不能執行情況處理

1.先來了解一下JOB的引數說明：與job相關的引數一個是job_queue_processes，這個是執行JOB時候所起的程式數，當然系統裡面JOB大於這個數值後，就會有排隊等候的，最小值是0，表示不執行JOB，最大值是36，在OS上對應的程式時SNPn，9i以後OS上管理JOB的程式叫CJQn。可以使用下面這個SQL確定目前有幾個SNP/CJQ在執行。

select * from v$bgprocess，這個paddr不為空的snp/cjq程式就是目前空閒的程式，有的表示正在工作的程式。

另外一個是job_queue_interval，範圍在1--3600之間，單位是秒，這個是喚醒JOB的process，因為每次snp執行完他就休息了，需要定期喚醒他，這個值不能太小，太小會影響資料庫的效能。

2.診斷：先確定上面這兩個引數設定是否正確，特別是第一個引數，設定為0了，所有JOB就不會跑，確認無誤後，我們繼續向下。

3.使用下面的SQL察看JOB的的broken,last_date和next_date，last_date是指最近一次job執行成功的結束時間，next_date是根據設定的頻率計算的下次執行時間，根據這個資訊就可以判斷JOB上次是否正常，還可以判斷下次的時間對不對，SQL如下：

select * from dba_jobs

有時候我們發現他的next_date是4000年1月1日，說明job要不就是在running，要不就是狀態是break(broken=Y)，如果發現JOB的broken值為Y，找使用者瞭解一下，確定該JOB是否可以broken，如果不能broken，那就把broken值修改成N，修改再使用上面的SQL察看就發現他的last_date已經變了，JOB即可正常執行，修改broken狀態的SQL如下：

declare

BEGIN

DBMS_JOB.BROKEN(,FALSE);

END;

4.使用下面的SQL查詢是否JOB還在Running

select * from dba_jobs_running

如果發現JOB已經Run了很久了還沒有結束，就要查原因了。一般的JOB running時會鎖定相關的相關的資源，可以檢視一下v$access和v$locked_object這兩個view，如果發現其他程式鎖定了與JOB相關的Object，包括PKG/Function/Procedure/Table等資源，那麼就要把其他程式刪除，有必要的話，把JOB的程式也刪除，再重新跑看看結果。

5.如果上面都正常，但是JOB還不run，怎麼辦？那我們要考慮把JOB程式重啟一次，防止是SNP程式死了造成JOB不跑，指令如下：

alter system set job_queue_processes=0 --關閉job程式，等待5--10秒鐘

alter system set job_quene_processes=5 --恢復原來的值

參考文獻：

fyer/blog/item/f0705c22cc4bfca74623e808.html

4shop/blog/item/7b7e7809ebacf3c83bc763bc.html

%C7%ED%D0%A1%C2%BF/blog/item/e746b6a972e31bfa1e17a2b5.html

4shop/blog/item/0d64b418603c040035fa413b.html

xdz66/blog/item/a550212d31518633349bf7e9.html

http://viwo.javaeye.com/blog/58222#comments

附錄：Oracle儲存過程基本語法

1.基本結構

CREATE OR REPLACE PROCEDURE 儲存過程名字

(

引數1 IN NUMBER,

引數2 IN NUMBER

) IS

變數1 INTEGER :=0;

變數2 DATE;

BEGIN

END 儲存過程名字

2.SELECT INTO STATEMENT

將select查詢的結果存入到變數中，可以同時將多個列儲存多個變數中，必須有一條

記錄，否則丟擲異常(如果沒有記錄丟擲NO_DATA_FOUND)

例子：

BEGIN

SELECT col1,col2 into 變數1,變數2 FROM typestruct where xxx;

EXCEPTION

WHEN NO_DATA_FOUND THEN

xxxx;

END;

...

3.IF 判斷

IF V_TEST=1 THEN

BEGIN

do something

END;

END IF;

4.while 迴圈

WHILE V_TEST=1 LOOP

BEGIN

XXXX

END;

END LOOP;

5.變數賦值

V_TEST := 123;

6.用for in 使用cursor

...

IS

CURSOR cur IS SELECT * FROM xxx;

BEGIN

FOR cur_result in cur LOOP

BEGIN

V_SUM :=cur_result.列名1+cur_result.列名2

END;

END LOOP;

END;

7.帶引數的cursor

CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;

OPEN C_USER(變數值);

LOOP

FETCH C_USER INTO V_NAME;

EXIT FETCH C_USER%NOTFOUND;

do something

END LOOP;

CLOSE C_USER;

8.用pl/sql developer debug

連線資料庫後建立一個Test WINDOW

在視窗輸入呼叫SP的程式碼,F9開始debug,CTRL+N單步除錯

透過一個實際的例子學習Oracle儲存過程

--建立儲存過程

CREATE OR REPLACE PROCEDURE xxxxxxxxxxx_p

(

--引數IN表示輸入引數，OUT表示輸入引數，型別可以使用任意Oracle中的合法型別。

is_ym IN CHAR

)

AS

--定義變數

vs_msg VARCHAR2(4000); --錯誤資訊變數

vs_ym_beg CHAR(6); --起始月份

vs_ym_end CHAR(6); --終止月份

vs_ym_sn_beg CHAR(6); --同期起始月份

vs_ym_sn_end CHAR(6); --同期終止月份

--定義遊標(簡單的說就是一個可以遍歷的結果集)

CURSOR cur_1 IS

SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,SUM(usd_amt)/10000 usd_amt_sn

FROM BGD_AREA_CM_M_BASE_T

WHERE ym >= vs_ym_sn_beg

AND ym <= vs_ym_sn_end

GROUP BY area_code,CMCODE;

BEGIN

--用輸入引數給變數賦初值，用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS TO_DATE 等很常用的函式。

vs_ym_beg := SUBSTR(is_ym,1,6);

vs_ym_end := SUBSTR(is_ym,7,6);

vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'), -12),'yyyymm');

vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyyymm'), -12),'yyyymm');

--先刪除表中特定條件的資料。

DELETE FROM xxxxxxxxxxx_T WHERE ym = is_ym;

--然後用內建的DBMS_OUTPUT物件的put_line方法列印出影響的記錄行數，其中用到一個系統變數SQL%rowcount

DBMS_OUTPUT.put_line('del上月記錄='||SQL%rowcount||'條');

INSERT INTO xxxxxxxxxxx_T(area_code,ym,CMCODE,rmb_amt,usd_amt)

SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000

FROM BGD_AREA_CM_M_BASE_T

WHERE ym >= vs_ym_beg

AND ym <= vs_ym_end

GROUP BY area_code,CMCODE;

DBMS_OUTPUT.put_line('ins當月記錄='||SQL%rowcount||'條');

--遍歷遊標處理後更新到表。遍歷遊標有幾種方法，用for語句是其中比較直觀的一種。

FOR rec IN cur_1 LOOP

UPDATE xxxxxxxxxxx_T

SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn

WHERE area_code = rec.area_code

AND CMCODE = rec.CMCODE

AND ym = is_ym;

END LOOP;

COMMIT;

--錯誤處理部分。OTHERS表示除了宣告外的任意錯誤。SQLERRM是系統內建變數儲存了當前錯誤的詳細資訊。

EXCEPTION

WHEN OTHERS THEN

vs_msg := 'ERROR IN xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500);

ROLLBACK;

--把當前錯誤記錄進日誌表。

INSERT INTO LOG_INFO(proc_name,error_info,op_date)

VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE);

COMMIT;

RETURN;

END;