最近在用存储过程,在网上找了很多的资料,下面就参考各种资料整理的一些小例子做些阐述：
1. Oralce TO_NUMBER() function 的改进：
用Oracle TO_NUMBER进行类型转换的时候，如果有无效数字，将返回错误。遗憾的是，Oralce TO_NUMBER并不是在query运行开始就报错，而是到碰到无效数字的纪录才报错，而且只是说无效输入，并不提示是哪条纪录，也没有任何输出。如果是一张大表，query运行很久才来这么一下，而且根本不知道那里出的错，是什么值，岂不痛苦？偶就经常跟这样的表打交道，遂自建一个函数，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;
/
这样无效数字全转换成 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为循环的临时变量******/
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;
/
存储过程也用到了带参数的游标和游标For循环,稍后介绍.
其中测试表的创建：
create table test
(
name varchar2(10),
num number
)
测试数据:
insert into test(name) values ('12_');
insert into test(name) values ('123');
2.带参数的游标
与存储过程和函数相似，可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用。它的语法如下：
CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
定义参数的语法如下：
Parameter_name [IN] data_type[{:=|DEFAULT} value]
与存储过程不同的是，游标只能接受传递的值，而不能返回值。参数只定义数据类型，没有大小。
另外可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。游标中定义的参数只是一个占位符，在别处引用该参数不
一定可靠。
在打开游标时给参数赋值，语法如下：
OPEN cursor_name[value[,value]....];
3.游标FOR循环
在大多数时候我们在设计程序的时候都遵循下面的步骤：
1、打开游标
2、开始循环
3、从游标中取值
4、检查那一行被返回
5、处理
6、关闭循环
7、关闭游标
可以简单的把这一类代码称为游标用于循环。但还有一种循环与这种类型不相同，这就是FOR循环，用于FOR循环的游标按照正常的声明方
式声明，它的优点在于不需要显式的打开、关闭、取数据，测试数据的存在、定义存放数据的变量等等。游标FOR 循环的语法如下：
FOR record_name IN
(corsor_name[(parameter[,parameter]...)]
| (query_difinition)
LOOP
statements
END LOOP;
上面的这两个用法也在例子中提到了！
同理类推，也可以将TO_DATE()函数改进。
下面的例子自http://www.nikicn.com/bbs/dispbbs.asp?boardid=23&id=908
没有测试，留作备份之用：
4. 将Oracle 的Last_Day()函数改写到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