存储过程的常用用法

最近在用存储过程,在网上找了很多的资料,下面就参考各种资料整理的一些小例子做些阐述：
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