Oracle带输入输出参数的存储过程

（一）使用输入参数
需求：在emp_copy中添加一条记录，empno为已有empno的最大值+1，ename不能为空且长度必须大于0，deptno为60。
创建存储过程：

create or replace procedure insert_emp(emp_name in varchar2,dept_no in number) as
begin
  declare max_empno number;
  begin
    if(emp_name is null or length(emp_name) = 0) then
      return;
    end if;

    if(dept_no != 60) then
      return;
    end if;

    select max(empno) into max_empno from emp_copy;
    insert into emp_copy(empno,ename,deptno) values(max_empno + 1,emp_name,dept_no);
  end;
end insert_emp;
/

调用存储过程并验证：
(1)

SQL>execute insert_emp(‘Li Si‘,60);
PL/SQL procedure successfully completed.

SQL>
col empno format 99999;
col ename format a15;
col deptno format 99999;
select empno,deptno from emp_copy where deptno = 60;

 EMPNO ENAME           DEPTNO
------ --------------- ------
  7981 Li Si           60

(2)

SQL> execute insert_emp(‘‘,6);
PL/SQL procedure successfully completed.

SQL> select empno,deptno from emp_copy where deptno = 6;

SQL> 

（二）使用输出参数
需求：在上个需求的基础上，要分别统计表emp_copy插入数据前后的记录数。
创建存储过程：

create or replace procedure insert_emp(emp_name in varchar2,dept_no in number,original_count out number,current_count out number) as
begin
  declare max_empno number;
  begin
    if(emp_name is null or length(emp_name) = 0) then
      return;
    end if;

    if(dept_no != 60) then
      return;
    end if;

    select count(1) into original_count from emp_copy;
    select max(empno) into max_empno from emp_copy;
    insert into emp_copy(empno,dept_no);
    select count(1) into current_count from emp_copy;
  end;
end insert_emp;
/

调用存储过程：

declare count1 number;
        count2 number;
begin
  insert_emp(‘Wang Wu‘,60,count1,count2);
  dbms_output.put_line(‘Original count of table emp_copy is ‘ || count1);
  dbms_output.put_line(‘Current count of table emp_copy is ‘ || count2);
end;
/

Original count of table emp_copy is 15
Current count of table emp_copy is 16

PL/SQL procedure successfully completed.

（三）使用输入输出参数
in out参数综合了上述两种参数类型，既向过程体传值，也被赋值而传到过程体外。in out参数既可以用作输入也可以用作输出。
需求:实现两数交换。
创建存储过程：

create or replace procedure swap(value1 in out number,value2 in out number) as
begin
  value1 := value1 + value2;
  value2 := value1 - value2;
  value1 := value1 - value2;
end swap;
/

调用存储过程：

declare a number := 22;
        b number := 33;
begin
  dbms_output.put_line(‘Before swap: a = ‘ || a || ‘,b = ‘ || b);
  swap(a,b);
  dbms_output.put_line(‘After swap: a = ‘ || a || ‘,b = ‘ || b);
end;
/

Before swap: a = 22,b = 33
After swap: a = 33,b = 22

PL/SQL procedure successfully completed.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!