oracle的儲存過程格式

因為工作的需要，最近一直在寫儲存過程。工作了3年，一直都是做管理，也沒有正兒八經的去寫過儲存過程，這次正好可以好好練習一下。

在這裡說一條使用儲存過程很重要的理由：儲存過程只在創造時進行編譯，以後每次執行儲存過程都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用儲存過程可提高資料庫執行速度。

Oracle 儲存過程定義和優點與函式區別

http://blog.csdn.net/tianlesoftware/archive/2010/01/27/5261364.aspx

Oracle 檢視表儲存過程觸發器函式等物件定義語句的方法

http://blog.csdn.net/tianlesoftware/archive/2010/06/19/5679293.aspx

1. 儲存過程格式

/* Formatted on 2011/1/17 13:20:44 (QP5 v5.115.810.9015) */

CREATE OR REPLACE procedure proc_trade(

v_tradeid in number, --交易id

v_third_ip in varchar2, --第三方ip

v_third_time in date , --第三方完成時間

v_thire_state in number , --第三方狀態

o_result out number, --返回值

o_detail out varchar2 --詳細描述

)

as

-- 定義變數

v_error varchar2(500);

begin

--對變數賦值

o_result:=0;

o_detail:='驗證失敗';

--業務邏輯處理

if v_tradeid >100 then

insert into table_name(...) values(...);

commit;

elsif v_tradeid < 100 and v_tradeid>50 then

insert into table_name(...) values(...);

commit;

else

goto log;

end if;

--跳轉標誌符，名稱自己指定

<>

o_result:=1;

--捕獲異常

exception

when no_data_found

then

result := 2;

when dup_val_on_index

then

result := 3;

when others

then

result := -1;

end proc_trade;

在上面這個儲存過程中使用了輸入引數，並返回輸出引數，這裡的引數型別是我們自己指定的。這種寫法可行，但是最好使用%type 來獲取引數的型別(table_name.column_name%TYPE)。這樣就不會出現引數型別的錯誤。

如：

CREATE OR REPLACE PROCEDURE spdispsms (

aempid IN otherinfo.empid%TYPE,

amsg IN otherinfo.msg%TYPE,

abillno IN otherinfo.billno%TYPE,

ainfotype IN otherinfo.infotype%TYPE,

aopid IN otherinfo.OPERATOR%TYPE,

ainfoid OUT otherinfo.infoid%TYPE,

RESULT OUT INTEGER

)

2. 儲存過程中的迴圈

儲存過程寫的是業務邏輯，迴圈是常用的處理方法之一。

2.1 for ... in ... loop 迴圈

2.1.1：迴圈遍歷遊標

示例1：

CREATE OR REPLACE PROCEDURE proc_test

AS

CURSOR c1

IS

SELECT * FROM dat_trade;

BEGIN

FOR x IN c1

LOOP

DBMS_OUTPUT.put_line (x.id);

END LOOP;

END proc_test;

示例2：

CREATE OR REPLACE PROCEDURE proc_test

AS

BEGIN

FOR x IN (SELECT power_id FROM sys_power)

LOOP

DBMS_OUTPUT.put_line (x.power_id);

END LOOP;

END proc_test;

2.1. 2：根據數值進行迴圈

示例1：

CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER)

AS

BEGIN

for x in 1..100 loop

dbms_output.put_line(x);

end loop;

END proc_test;

示例2：在過程裡指定輸入引數v_num. 在呼叫過程時指定迴圈次數。

CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER)

AS

BEGIN

FOR x IN 1 .. v_num

LOOP

DBMS_OUTPUT.put_line (x);

END LOOP;

END proc_test;

2.2 loop 迴圈

LOOP

DELETE FROM orders

WHERE senddate < TO_CHAR (ADD_MONTHS (SYSDATE, -3),

'yyyy-mm-dd')

AND ROWNUM < 1000;

EXIT WHEN SQL%ROWCOUNT < 1;

COMMIT;

END LOOP;

這裡的SQL%ROWCOUNT 是隱士遊標。除了這個，還有其他幾個：%found，%notfound， %isopen。

2.3 while 迴圈

CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER)

AS

i NUMBER := 1;

BEGIN

WHILE i < v_num

LOOP

BEGIN

i := i + 1;

DBMS_OUTPUT.put_line (i);

END;

END LOOP;

END proc_test;

3. 儲存過程中的判斷

判斷也是儲存過程中最常用的方法之一。

3.1 if ... elsif ... else ... 判斷

CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER)

AS

BEGIN

IF v_num < 10

THEN

DBMS_OUTPUT.put_line (v_num);

ELSIF v_num > 10 AND v_num < 50

THEN

DBMS_OUTPUT.put_line (v_num - 10);

ELSE

DBMS_OUTPUT.put_line (v_num - 50);

END IF;

END proc_test;

3.2 case ... when ... end case 判斷

CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER)

AS

BEGIN

case v_num

when 1 then

DBMS_OUTPUT.put_line (v_num);

when 2 then

DBMS_OUTPUT.put_line (v_num);

when 3 then

DBMS_OUTPUT.put_line (v_num);

else null;

end case;

END proc_test;

4. 遊標

儲存過程中使用遊標也是很常見的。這裡的遊標分兩種：

4.1 Cursor型遊標(不能用於引數傳遞)

這種方法具體參考 2.1.1：迴圈遍歷遊標中的示例。

4.2 SYS_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(然後當前遊標所指向的行位置)

CREATE OR REPLACE PROCEDURE proc_test (

checknum IN NUMBER, --每次返回的資料量

ref_cursor OUT sys_refcursor --返回的結果集，遊標

)

AS

t_tmp table_name%ROWTYPE;

BEGIN

OPEN ref_cursor FOR

SELECT *

FROM ( SELECT *

FROM table_name

WHERE state = 41

ORDER BY id)

WHERE ROWNUM < checknum;

--迴圈遊標

LOOP

FETCH ref_cursor INTO t_tmp;

EXIT WHEN ref_cursor%NOTFOUND;

-- DBMS_OUTPUT.put_line (t_tmp.id);

UPDATE table_name

SET state = 53

WHERE id = t_tmp.id;

COMMIT;

END LOOP;

CLOSE ref_cursor;

END proc_test;

五. 儲存過程的除錯

如果使用PL/SQL Developer 或者TOAD 工具的話，除錯還是很方便的。如果是在Sqlplus裡，我們可以使用：

SQL>show errors

來檢視錯誤。不過在開發中估計也很少有人直接使用sqlplus來寫儲存過程。效率低，除錯又麻煩。還是使用工具方便點。我一直使用的是Toad的。

如果想在某處退出儲存過程，直接使用Return;就可以了。與儲存過程編寫相關的陣列和遊標，這兩塊說起來還是有很多東西。在上面的示例中，也簡單的舉了幾個有關遊標與儲存過程編寫的例子。

總之，寫程式碼都是都是費腦子的事，相比之下還是做管理DBA舒服點，雖然壓力大很多，至少不用這麼費心思去整理業務邏輯。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/235507/viewspace-704384/，如需轉載，請註明出處，否則將追究法律責任。

oracle的儲存過程格式

相關文章