因為工作的需要，最近一直在寫存儲過程。 工作了3年，一直都是做管理，也沒有正兒八經的去寫過存儲過程， 這次正好可以好好練習一下。
在這裡說一條使用存儲過程很重要的理由：存儲過程隻在創造時進行編譯，以後每次執行存儲過程都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,是以使用存儲過程可提高資料庫執行速度。
Oracle存儲過程定義和優點與函數差別
Oracle檢視表存儲過程觸發器函數等對象定義語句的方法
1.存儲過程格式
CREATEORREPLACEprocedureproc_trade(
v_tradeidinnumber,--交易id
v_third_ipinvarchar2,--第三方ip
v_third_timeindate,--第三方完成時間
v_thire_stateinnumber,--第三方狀态
o_resultoutnumber,--傳回值
o_detailoutvarchar2--較長的描述
)
as
--定義變量
v_errorvarchar2(500);
begin
--對變量指派
o_result:=0;
o_detail:='驗證失敗';
--業務邏輯處理
ifv_tradeid>100then
insertintotable_name(...)values(...);
commit;
elsifv_tradeid<100andv_tradeid>50then
insertintotable_name(...)values(...);
commit;
else
gotolog;
endif;
--跳轉标志符，名稱自己指定
<>
o_result:=1;
--捕獲異常
exception
whenno_data_found
then
result:=2;
whendup_val_on_index
then
result:=3;
whenothers
then
result:=-1;
endproc_trade;
在上面這個存儲過程中使用了輸入參數，并傳回輸出參數，這裡的參數類型是我們自己指定的。 這種寫法可行，但是最好使用%type來擷取參數的類型(table_name.column_name%TYPE)。 這樣就不會出現參數類型的錯誤。
如：
CREATEORREPLACEPROCEDUREspdispsms(
aempidINotherinfo.empid%TYPE,
amsgINotherinfo.msg%TYPE,
abillnoINotherinfo.billno%TYPE,
ainfotypeINotherinfo.infotype%TYPE,
aopidINotherinfo.OPERATOR%TYPE,
ainfoidOUTotherinfo.infoid%TYPE,
RESULTOUTINTEGER
)
2.存儲過程中的循環
存儲過程寫的是業務邏輯，循環是常用的處理方法之一。
2.1for ... in ... loop循環
2.1.1：循環周遊遊标
示例1：
CREATEORREPLACEPROCEDUREproc_test
AS
CURSORc1
IS
SELECT*FROMdat_trade;
BEGIN
FORxINc1
LOOP
DBMS_OUTPUT.put_line(x.id);
ENDLOOP;
ENDproc_test;
示例2：
CREATEORREPLACEPROCEDUREproc_test
AS
BEGIN
FORxIN(SELECTpower_idFROMsys_power)
LOOP
DBMS_OUTPUT.put_line(x.power_id);
ENDLOOP;
ENDproc_test;
2.1. 2：根據數值進行循環
示例1：
CREATEORREPLACEPROCEDUREproc_test(v_numINNUMBER)
AS
BEGIN
forxin1..100loop
dbms_output.put_line(x);
endloop;
ENDproc_test;
示例2：在過程裡指定輸入參數v_num.在調用過程時指定循環次數。
CREATEORREPLACEPROCEDUREproc_test(v_numINNUMBER)
AS
BEGIN
FORxIN1..v_num
LOOP
DBMS_OUTPUT.put_line(x);
ENDLOOP;
ENDproc_test;
2.2 loop循環
LOOP
DELETEFROMorders
WHEREsenddate
'yyyy-mm-dd')
ANDROWNUM<1000;
EXITWHENSQL%ROWCOUNT<1;
COMMIT;
ENDLOOP;
這裡的SQL%ROWCOUNT是隐士遊标。除了這個，還有其他幾個：%found，%notfound，%isopen。
2.3while循環
CREATEORREPLACEPROCEDUREproc_test(v_numINNUMBER)
AS
iNUMBER:=1;
BEGIN
WHILEi
LOOP
BEGIN
i:=i+1;
DBMS_OUTPUT.put_line(i);
END;
ENDLOOP;
ENDproc_test;
3.存儲過程中的判斷
判斷也是存儲過程中最常用的方法之一。
3.1 if ... elsif ... else ...判斷
CREATEORREPLACEPROCEDUREproc_test(v_numINNUMBER)
AS
BEGIN
IFv_num<10
THEN
DBMS_OUTPUT.put_line(v_num);
ELSIFv_num>10ANDv_num<50
THEN
DBMS_OUTPUT.put_line(v_num-10);
ELSE
DBMS_OUTPUT.put_line(v_num-50);
ENDIF;
ENDproc_test;
3.2 case ... when ... end case判斷
CREATEORREPLACEPROCEDUREproc_test(v_numINNUMBER)
AS
BEGIN
casev_num
when1then
DBMS_OUTPUT.put_line(v_num);
when2then
DBMS_OUTPUT.put_line(v_num);
when3then
DBMS_OUTPUT.put_line(v_num);
elsenull;
endcase;
ENDproc_test;
4.遊标
存儲過程中使用遊标也是很常見的。 這裡的遊标分兩種：
4.1Cursor型遊标(不能用于參數傳遞)
這種方法具體參考2.1.1：循環周遊遊标中的示例。
4.2SYS_REFCURSOR型遊标
該遊标是Oracle以預先定義的遊标，可作出參數進行傳遞。
注意一點：SYS_REFCURSOR隻能通過OPEN方法來打開和指派
4.2.1我們可以使用這種類似的遊标來傳回一個結果集：
CREATE OR REPLACE procedure proc_test(
checknum in number,--每次傳回的資料量
ref_cursor out sys_refcursor--傳回的結果集，遊标
)
as
begin
open ref_cursor for select * from (select * from dat_trade where state=41 order by id) where rownum
end proc_test;
/
SYS_REFCURSOR中可使用三個狀态屬性：
(1).%NOTFOUND(未找到記錄資訊)
(2).%FOUND(找到記錄資訊)
(3).%ROWCOUNT(然後目前遊标所指向的行位置)
CREATEORREPLACEPROCEDUREproc_test(
checknumINNUMBER,--每次傳回的資料量
ref_cursorOUTsys_refcursor--傳回的結果集，遊标
)
AS
t_tmptable_name%ROWTYPE;
BEGIN
OPENref_cursorFOR
SELECT*
FROM(SELECT*
FROMtable_name
WHEREstate=41
ORDERBYid)
WHEREROWNUM
--循環遊标
LOOP
FETCHref_cursorINTOt_tmp;
EXITWHENref_cursor%NOTFOUND;
--DBMS_OUTPUT.put_line(t_tmp.id);
UPDATEtable_name
SETstate=53
WHEREid=t_tmp.id;
COMMIT;
ENDLOOP;
CLOSEref_cursor;
ENDproc_test;
五.存儲過程的調試
如果使用PL/SQL Developer或者TOAD工具的話，調試還是很友善的。 如果是在Sqlplus裡，我們可以使用：
SQL>show errors
來檢視錯誤。不過在開發中估計也很少有人直接使用sqlplus來寫存儲過程。 效率低，調試又麻煩。 還是使用工具友善點。我一直使用的是Toad的。
如果想在某處退出存儲過程，直接使用Return;就可以了。 與存儲過程編寫相關的數組和遊标， 這兩塊說起來還是有很多東西。 在上面的示例中， 也簡單的舉了幾個有關遊标與存儲過程編寫的例子。
總之，寫代碼都是都是費腦子的事，相比之下還是做管理DBA舒服點，雖然壓力大很多，至少不用這麼費心思去整理業務邏輯。