存储过程

存储过程特点

存储过程是指被命名的PL/SQL块，这种块可以带有参数，可以在不同应用中多次调用。PL/SQL有两种类型的存储过程：过程和函数。其中，过程用于执行特定操作，而函数则用于返回特定数据。通过将商业逻辑和企业规则集成到PL/SQL子程序中，可以简化客户端应用的开发和维护，提高应用的性能。

---

创建存储过程语法

过程一般用于执行一个指定的操作，可以将常用的特定操作封装成过程。
语法：

CREATE [OR REPLACE] PROCEDURE procedure_name
(argument1  [mode1]  datatype1, argument2  [mode2]  datatype2,...)
IS [AS]
   声明部分
BEGIN
   执行部分
EXCEPTION
   异常处理部分
END;

上述语法中，procedure_name 用于指定过程名称，argument1 、argument2 等则用于指定过程的参数，IS 或 AS 用于开始一个 PL/SQL 块。当指定参数数据类型时，不能指定其长度。另外，创建存储过程时，既可以指定输入参数（IN），又可以指定输出参数（OUT）及输入输出参数（IN OUT）。通过在过程中使用输入参数，可以将应用环境的数据传递到执行部分。通过使用输出参数，可以将执行部分的数据传递到应用环境。定义子程序参数时，如果不指定参数模式，则默认为输入参数；如果需要定义输出参数，则必须指定OUT关键字；如果定义输入输出参数，则必须指定 IN OUT关键字。

---

不带参数存储过程

创建不带参数的存储过程

现有表tb_test的数据如下：

A	B
aa	bb
aa	cc
bb	cc
aa	bb
aa	cc
bb	cc

创建一个无参存储过程，用于删除tb_test表中重复记录的操作。代码如下：

CREATE OR REPLACE PROCEDURE proc_del_dup_rec
AS
BEGIN
DELETE tb_test a
WHERE a_ROWID=
<
   SELECT MAX<ROWID> FROM tb_test b
   WHERE
   a.a=b.a AND
   a.b=b.b
>;
END;

执行不带参数的存储过程

在SQL*Plus环境中，调用过程有两种方法：使用exec命令和使用call命令。使用exec命令调用过程proc_del_dup_rec的代码如下：

exec proc_del_dup_rec;

经验：在调用过程时，也可以使用PL/SQL块的方式，以下代码将以PL/SQL块的方式来调用proc_del_dup_rec过程：

begin
proc_del_dup_rec
end;

调用过程中，不需要添加exec或call关键字。

---

带输入参数的存储过程

创建过程中，可以使用输入参数将应用程序的数据传递到过程中。当为过程定义参数时，如果不指定参数模式，将默认为输入参数。另外，可以使用IN关键字显示地定义输入参数。
以下代码将示例在emp_copy中添加一条记录，empno为已有empno的最大值+1，ename不能为空却长度必须大于0，depton为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(ema_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;
 

调用存储过程：

execute insert_emp('Li Si',60);

---

带输出参数的存储过程

在带输入参数的存储过程insert_emp的需求之上增加需求：要分别统计表emp_copy插入数据前后的记录数。代码如下：

create or replace procedure insert_emp(emp_name in varchar2,dept_no in number,original_count ont number,current_count out number) as
   begin
    declare max_empno number;
    begin
       if(emp_name is null or length(ema_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,ename,deptno) values(max_empno+1,emp_name,dept_no);
       select count(1) into current_count from emp_copy;
    end;
end insert_emp;

调用存储过程：

declare count1 number;
declare 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;