最近在用存儲過程,在網(wǎng)上找了很多的資料,下面就參考各種資料整理的一些小例子做些闡述：
1. Oralce TO_NUMBER() function 的改進：
用Oracle TO_NUMBER進行類型轉(zhuǎn)換的時候，如果有無效數(shù)字，將返回錯誤。遺憾的是，Oralce TO_NUMBER并不是在query運行開始就報錯，而是到碰到無效數(shù)字的紀(jì)錄才報錯，而且只是說無效輸入，并不提示是哪條紀(jì)錄，也沒有任何輸出。如果是一張大表，query運行很久才來這么一下，而且根本不知道那里出的錯，是什么值，豈不痛苦？偶就經(jīng)常跟這樣的表打交道，遂自建一個函數(shù)，GET_NUM：
CREATE OR REPLACE FUNCTION GET_NUM(IN_TXT in VARCHAR2) RETURN NUMBER IS
RETRUN_VALUE NUMBER;
BEGIN
?select to_number(IN_TXT) into RETRUN_VALUE from dual;
?RETURN RETRUN_VALUE;
?
?EXCEPTION
?WHEN others
?THEN
?RETURN null;
END GET_NUM;
/
這樣無效數(shù)字全轉(zhuǎn)換成 Null,糾錯容易多了。
寫個存儲過程測試一下：
CREATE OR REPLACE PROCEDURE TestFun IS
??? v_name varchar2(10);/***** 存放cursor_para中的name *****/
??? CURSOR cursor_name IS select name from test;
??? cursor cursor_para(p_num number) is select name from test where num = p_num;
begin
? /******* t_name為循環(huán)的臨時變量******/
? FOR t_name IN cursor_name LOOP
?
? ? if (get_num(t_name.name) is not null) then
?? ?
???open cursor_para(get_num(t_name.name));
???
???fetch cursor_para into v_name;
???
???DBMS_OUTPUT.PUT_LINE('Name:'|| v_name);
???
???close cursor_para;
????? ?
?? end if;
??
? END LOOP;
END TestFun;
/
存儲過程也用到了帶參數(shù)的游標(biāo)和游標(biāo)For循環(huán),稍后介紹.
其中測試表的創(chuàng)建：
create table test
(
???? name varchar2(10),
??? num number
)
測試數(shù)據(jù):
insert into test(name) values ('12_');
insert into test(name) values ('123');
2.帶參數(shù)的游標(biāo)
與存儲過程和函數(shù)相似，可以將參數(shù)傳遞給游標(biāo)并在查詢中使用。這對于處理在某種條件下打開游標(biāo)的情況非常有用。它的語法如下：
CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
定義參數(shù)的語法如下：
Parameter_name [IN] data_type[{:=|DEFAULT} value]
與存儲過程不同的是，游標(biāo)只能接受傳遞的值，而不能返回值。參數(shù)只定義數(shù)據(jù)類型，沒有大小。
另外可以給參數(shù)設(shè)定一個缺省值，當(dāng)沒有參數(shù)值傳遞給游標(biāo)時，就使用缺省值。游標(biāo)中定義的參數(shù)只是一個占位符，在別處引用該參數(shù)不
一定可靠。
在打開游標(biāo)時給參數(shù)賦值，語法如下：
OPEN cursor_name[value[,value]....];
3.游標(biāo)FOR循環(huán)
在大多數(shù)時候我們在設(shè)計程序的時候都遵循下面的步驟：
1、打開游標(biāo)
2、開始循環(huán)
3、從游標(biāo)中取值
4、檢查那一行被返回
5、處理
6、關(guān)閉循環(huán)
7、關(guān)閉游標(biāo)
可以簡單的把這一類代碼稱為游標(biāo)用于循環(huán)。但還有一種循環(huán)與這種類型不相同，這就是FOR循環(huán)，用于FOR循環(huán)的游標(biāo)按照正常的聲明方
式聲明，它的優(yōu)點在于不需要顯式的打開、關(guān)閉、取數(shù)據(jù)，測試數(shù)據(jù)的存在、定義存放數(shù)據(jù)的變量等等。游標(biāo)FOR 循環(huán)的語法如下：
FOR record_name IN
(corsor_name[(parameter[,parameter]...)]
| (query_difinition)
LOOP
statements
END LOOP;
上面的這兩個用法也在例子中提到了！
同理類推，也可以將TO_DATE()函數(shù)改進。
下面的例子自http://www.nikicn.com/bbs/dispbbs.asp?boardid=23&id=908
沒有測試，留作備份之用：
4. 將Oracle 的Last_Day()函數(shù)改寫到DB2上：
DROP SPECIFIC FUNCTION LAST_DAYDATE
;
CREATE FUNCTION LAST_DAY(D DATE)
RETURNS???? DATE
??? SPECIFIC LAST_DAYDATE
??? LANGUAGE SQL
??? DETERMINISTIC
??? CONTAINS SQL
??? CALLED ON NULL INPUT
??? NO EXTERNAL ACTION
??? RETURN
D + 1 month - day(D + 1 month) day
;
同理，DB2下的first day:
DROP SPECIFIC FUNCTION FIRST_DAYDATE
;
CREATE FUNCTION FIRST_DAY(D DATE)
RETURNS???? DATE
??? SPECIFIC FIRST_DAYDATE
??? LANGUAGE SQL
??? DETERMINISTIC
??? CONTAINS SQL
??? CALLED ON NULL INPUT
??? NO EXTERNAL ACTION
??? RETURN
D - day(D) day + 1 day