Oracle 存储过程参数

Oracle存储过程参数

在创建存储过程时，需要考虑的一件重要事情就是过程的灵活性，以方便随后可以重新使用。通过使用“参数”可以使程序单元变得很灵活，参数是一种向程序单元输入和输出数据的机制，存储过程可以接受和返回0到多个参数。Oracle有三种参数模式：IN、OUT和IN OUT。

1．IN参数

该类型的参数值由调用者传入，并且只能够被存储过程读取。这种模式的参数是最常用的，也是默认的参数模式。

例如，下面以SCOTT用户连接到数据库，并建立一个简单的存储过程ADD_EMPLOYEE。顾名思义，该过程将接受一系列参数，并且将它们添加到SCOTT.EMP 表中：

SQL> create or replace procedure add_employee(

2 id_param in number,

3 name_param in varchar2,

4 job_param in varchar2,

5 hire_param in date,

6 salary_param in number) is

7 begin

8 insert into scott.emp(empno,ename,job,hiredate,sal)

9 values(id_param,name_param,job_param,hire_param,salary_param);

10 end add_employee;

11 /

过程已创建。

在为存储过程定义参数时，参数的数据类型不能包括大小和精度信息。在调用该存储过程时，用户需要传递一系列参数，以便过程的执行部使用这些参数向SCOTT.EMP表添加一条记录。在调用存储过程时有三种向其传递参数的方法：名称表示法、位置表示法和混合表示法。

名称表示法

名称表示法是指为各个参数传递参数值时，指定传入数值的参数名。使用名称表示法传递参数的语法形式如下：

prcedure_name(param_name=>value[,param_name=>value]);

例如，在下面的示例中使有名称表示法传递参数，以调用存储过程ADD_EMPLOYEE：SQL> alter session set nls_date_format = 'yyyy-mm-dd';

会话已更改。

SQL> begin

2 add_employee(id_param=>8000,name_param =>'ATG',

3 job_param =>'CLERK',hire_param =>'1997-12-20',

4 salary_param =>1500);

5 end;

6 /

PL/SQL 过程已成功完成。

通过名称传递法传递参数的好处：规定了各个值被赋予哪个参数。

由于明确指定了向各个参数传递的值，因此在调用过程时就不再需要考虑创建过程时定义的参数顺序。在使用名称表示法时，如果参数命名合理，这可以方便用户阅读、查阅以及调试代码。 下面使用打乱次序的参数调用过程，查看它是否能够正常运行：

SQL> exec add_employee(name_param =>'LI',job_param =>'CLERK',

hire_param =>'1999-10-20',id_param=>8120,salary_param =>1500);

SQL> select empno,ename,job,hiredate,sal

2 from emp

3 where empno>8000;

● 位置表示法

当参数比较多时，通过名称表示法调用过程时可能会非常长。为了克服名称表示法的弊端，可以采用位置表示法。采有位置表示法传递参数时，用户提供的参数值顺序必须与过程中定义的参数顺序相同。

例如，下面的程序使用位置表示法传递参数调用ADD_EMPLOYEE 过程：

SQL> exec add_employee(8021,'刘丽','SALESMAN','1995-10-17',2000);

PL/SQL 过程已成功完成。

前面说过，存储过程会被保存在数据库中，这也就意味着，可以像使用DESCRIBE 命令列出表的结构一样，可以使用DESCRIBE 命令列出关于存储过程的详细信息。在调用存储过程时，可以使用DESCRIBE 命令来查看过程定义的参数次序是否相同。

● 混合表示法

从上面的示例可以看出，位置表示法和名称表示法各有自己的优缺点，为了弥补这两者的不足，还可以采用混合表示法发挥两者的优点。下面的示例是采用了混合表示法调用存储过程ADD_EMPLOYEE ：

SQL> exec add_employee(8022,'王芳',hire_param =>'1996-11-17',job_param=>'clerk',

salary_param=>3000)

PL/SQL 过程已成功完成。

在上面的程序中，两个参数采用了位置表示法传递值，随后则切换为名称表示法传递参数。当切换为名称表示法传递参数后，在后续的参数也必须使用名称表示法。这就说，当ADD_EMPLOYEE 过程的第3个参数采用名称表示法传入值时，其第4、5个参数也必须使用名称表示法。

2．OUT 参数

OUT 类型的参数则由存储过程传入值，然后由用户接收参数值。下面通过SCOTT.EMP 表创建一个搜索过程，该过程将根据提供的EMPNO 列的值，检索雇员的ENAME 和SAL ：

注 意 在使用位置表示法传递参数调用过程时，用户需要确定过程中定义参数的次序，

如果传递参数的次序与定义时的次序不相同，则调用过程时可能会失败，也可

以得到非常难以预料的结果。