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）

　　例子：

　　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

　　do something

　　end if;

　　4.while 循環

　　while v_test=1 loop

　　xxxx

　　end loop;

　　5.變量指派

　　v_test := 123;

　　6.用for in 使用cursor

　　is

　　cursor cur is select * from xxx;

　　for cur_result in cur loop

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

　　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;

　　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)/10000usd_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;

　　--用輸入參數給變量賦初值，用到了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

　　where ym >= vs_ym_beg

　　and ym <= vs_ym_end

　　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;

　　commit;

　　--錯誤處理部分。others表示除了聲明外的任意錯誤。sqlerrm是系統内置變量儲存了目前錯誤的詳細資訊。

　　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);

　　return;

=========================================

幾個對job執行時間設定的例子，對于oracle日志設定不熟悉，借鑒一下：

描述

interval參數值

每天午夜12點

'trunc(sysdate + 1)'

每天早上8點30分

'trunc(sysdate + 1) + （8*60+30）/(24*60)'

每星期二中午12點

'next_day(trunc(sysdate ), ''tuesday'' ) + 12/24'

每個月第一天的午夜12點

'trunc(last_day(sysdate ) + 1)'

每個季度最後一天的晚上11點

'trunc(add_months(sysdate + 2/24, 3 ), 'q' ) -1/24'

每星期六和日早上6點10分

'trunc(least(next_day(sysdate, ''saturday"), next_day(sysdate, "sunday"))) + （6×60+10）/（24×60）'