因為工作的需要，最近一直在寫存儲過程。 工作了3年，一直都是做管理，也沒有正兒八經(jīng)的去寫過存儲過程， 這次正好可以好好練習一下。
在這里說一條使用存儲過程很重要的理由：存儲過程只在創(chuàng)造時進行編譯，以后每次執(zhí)行存儲過程都不需再重新編譯，而一般SQL語句每執(zhí)行一次就編譯一次,所以使用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度。
Oracle 存儲過程 定義 和 優(yōu)點 與 函數(shù) 區(qū)別
http://blog.csdn.net/tianlesoftware/archive/2010/01/27/5261364.aspx
Oracle 查看 表 存儲過程 觸發(fā)器 函數(shù) 等對象定義語句的方法
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 , --第三方狀態(tài)
o_result out number, --返回值
o_detail out varchar2 --詳細描述
)
as
-- 定義變量
v_error varchar2(500);
begin
--對變量賦值
o_result:=0;
o_detail:='驗證失敗';
--業(yè)務邏輯處理
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;
--跳轉(zhuǎn)標志符，名稱自己指定
<<log>>
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;
在上面這個存儲過程中使用了輸入?yún)?shù)，并返回輸出參數(shù)，這里的參數(shù)類型是我們自己指定的。 這種寫法可行，但是最好使用%type 來獲取參數(shù)的類型(table_name.column_name%TYPE)。 這樣就不會出現(xiàn)參數(shù)類型的錯誤。
如：
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. 存儲過程中的循環(huán)
存儲過程寫的是業(yè)務邏輯，循環(huán)是常用的處理方法之一。
2.1 for ... in ... loop 循環(huán)
2.1.1：循環(huán)遍歷游標
示例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：根據(jù)數(shù)值進行循環(huán)
示例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：在過程里指定輸入?yún)?shù)v_num. 在調(diào)用過程時指定循環(huán)次數(shù)。
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 循環(huán)
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 循環(huán)
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型游標(不能用于參數(shù)傳遞)
這種方法具體參考 2.1.1：循環(huán)遍歷游標 中的示例。
4.2 SYS_REFCURSOR型游標
該游標是Oracle以預先定義的游標，可作出參數(shù)進行傳遞。
注意一點：SYS_REFCURSOR只能通過OPEN方法來打開和賦值
4.2.1 我們可以使用這種類似的游標來返回一個結(jié)果集：
CREATE OR REPLACE procedure proc_test(
checknum in number, --每次返回的數(shù)據(jù)量
ref_cursor out sys_refcursor --返回的結(jié)果集，游標
)
as
begin
open ref_cursor for select * from (select * from dat_trade where state=41 order by id) where rownum<checknum;
end proc_test;
/
SYS_REFCURSOR中可使用三個狀態(tài)屬性：
（1）. %NOTFOUND(未找到記錄信息)
（2）. %FOUND(找到記錄信息)
（3）. %ROWCOUNT(然后當前游標所指向的行位置)
CREATE OR REPLACE PROCEDURE proc_test (
checknum IN NUMBER, --每次返回的數(shù)據(jù)量
ref_cursor OUT sys_refcursor --返回的結(jié)果集，游標
)
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;
--循環(huán)游標
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;
五. 存儲過程的調(diào)試
如果使用PL/SQL Developer 或者TOAD 工具的話，調(diào)試還是很方便的。 如果是在Sqlplus里，我們可以使用：
SQL>show errors
來查看錯誤。不過在開發(fā)中估計也很少有人直接使用sqlplus來寫存儲過程。 效率低，調(diào)試又麻煩。 還是使用工具方便點。我一直使用的是Toad的。
如果想在某處退出存儲過程，直接使用Return;就可以了。 與存儲過程編寫相關(guān)的數(shù)組和游標， 這兩塊說起來還是有很多東西。 在上面的示例中， 也簡單的舉了幾個有關(guān)游標與存儲過程編寫的例子。
總之，寫代碼都是都是費腦子的事，相比之下還是做管理DBA舒服點，雖然壓力大很多，至少不用這么費心思去整理業(yè)務邏輯。
------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
網(wǎng)上資源： http://tianlesoftware.download.csdn.net
相關(guān)視頻：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(滿); DBA2 群：62697977(滿)
DBA3 群：62697850 DBA 超級群：63306533;
聊天 群：40132017
--加群需要在備注說明Oracle表空間和數(shù)據(jù)文件的關(guān)系，否則拒絕申請
