oracle课堂收录-参数

在创建存储过程时，通过使用参数可以使程序单元变得很灵活，参数是一种向程序单元输入和输出数据的机制，存储过程可以接受和返回零到多个参数。

1.IN参数

该类型的参数值由调用者传入，并且只能被存储过程读取。

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
  7  )is
  8  begin
  9  insert into scott.emp(empno,ename,job,hiredate,sal)
 10  values(id_param,name_param,job_param,hire_param,salary_param);
 11  end add_employee;
 12  /

过程已创建。

通过名称传递发传递参数

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(8021,'刘丽','SALESMAN','1995-10-17',2000);

PL/SQL 过程已成功完成。

还可以采用混合表示法传递参数。

2.OUT参数

OUT类型的参数由存储过程传入值，然后由用户接受参数值。

SQL> create or replace procedure search_employee(
  2     empno_param in number,
  3     name_param out scott.emp.ename%type,
  4     salary_param out scott.emp.sal%type
  5  )is
  6  begin
  7     select ename,sal
  8     into name_param,salary_param
  9     from scott.emp
 10     where empno=empno_param;
 11  exception
 12     when no_data_found then
 13         name_param:='NULL';
 14         salary_param:=-1;
 15     dbms_output.put_line('未找到指定编号的员工信息!');
 16  end search_employee;
 17  /

过程已创建。

因为过程中有返回值，所以调用它时必须提供能够接受返回值的变量。

调用刚才创建的存储过程search_employee

SQL> variable name varchar2(10);
SQL> variable sal number;
SQL> exec search_employee(7499,:name,:sal);

PL/SQL 过程已成功完成。

查看执行结果

SQL> print name

NAME
--------------------------------
ALLEN

SQL> print sal

       SAL
----------
      1600

也可以通过SELECT语句检索绑定的变量值

SQL> select :name,:sal
  2  from dual;

:NAME                                  :SAL
-------------------------------- ----------
ALLEN                                  1600

在匿名程序块重调用存储过程search_employee

SQL> set serverout on
SQL> declare
  2  name scott.emp.ename%type;
  3  sal scott.emp.sal%type;
  4  begin
  5     search_employee(7499,name,sal);
  6     dbms_output.put_line('name:'||name);
  7     dbms_output.put_line('sal:'||sal);
  8  end;
  9  /
name:ALLEN
sal:1600

PL/SQL 过程已成功完成。

在调用具有OUT参数的存储过程时，必须为OUT参数提供变量，即使OUT参数在过程中没有设置返回值，调用时也要为其提供接收变量，否则报错。

3.IN OUT参数

IN OUT参数在调用过程时，既可以向该类型的参数传入值，也可以从该参数接收值。

举个栗子，交换两个数据的位置

SQL> create or replace procedure swap(
  2     num1_param in out number,
  3     num2_param in out number) is
  4     var_temp number;
  5  begin
  6     var_temp:=num1_param;
  7     num1_param:=num2_param;
  8     num2_param:=var_temp;
  9  end swap;
 10  /

过程已创建。

在栗子中SWAP过程为了交换两个变量的值，需要同时向它传入两个参数，交换完之后还需要同时返回两个参数。

匿名程序块调用SWAP存储过程

SQL> set serverout on
SQL> declare
  2     var_max number:=23;
  3     var_min number:=45;
  4  begin
  5     if var_max<var_min then
  6             swap(var_max,var_min);
  7     end if;
  8     dbms_output.put_line(var_max||'>'||var_min);
  9  end;
 10  /
45>23

PL/SQL 过程已成功完成。