数据库存储过程与存储函数

1. 存储过程、存储函数概念

    存储过程是一组为了完成特定功能的SQL语句集，经过编译后存储在数据库中，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它，叫做存储过程、存储函数。

2. 存储过程与存储函数的区别

    存储函数：可以通过return语句返回函数值；主要用于计算并返回一个值；

    存储过程：不能；存储过程没有直接返回值，主要用于执行操作；

3.  存储过程及存储函数的特点

     (1) 存储过程只在创建时进行编译，以后每次执行存储过程都不需要再重新编译；而一般的SQL语句每执行一次就需要编译一次，所以使用存储过程可以提高数据库执行速度。

     (2) 当地数据库进行复杂操作(如对多个表进行update、insert、delete、query)时，可以将此复杂操作用存储过程封装起来与数据库提供的事务处理结合起来一起使用。

     (3) 存储过程可以重复使用可以减少数据库开发人员的工作量。

     (4) 安全性高，可以设定只有某指定用户才具有对指定的存储过程使用的权限。

4. 使用存储过程或存储函数的优缺点

    相对于直接使用SQL语句，在应用程序中直接调用存储过程有以下好处：

    (1) 减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，但是若存储过程包含上百行SQL语句，那么其性能绝对比一条一条调用SQL语句要高得多；

    (2) 执行速度更快。有两方面的原因：首先，在存储过程创建的时候，数据库已经对其进行了解析和优化。其次，存储过程一旦执行，在内存中就会保存一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。

    (3) 重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。

    (4) 安全性。参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程(即，为存储过程设置权限)。

    (5) 更强的适应性。由于存储过程对数据库的访问是通过存储过程来执行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。

    (6) 分布式工作。应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

存储过程的缺点：

     (1) 调式麻烦，但是使用PL/SQL Developer调式比较方便，弥补了这个缺点。

     (2) 可移植性差。由于存储过程将应用程序绑定到SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。但是如果是工程型项目，基本不存在移植问题。

     (3) 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译(不过页可以设置成运行时自动编译)。

     (4) 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结果的变化，接着就是系统的相关问题了，这将会为该系统后期的维护工作带来较大的麻烦。

5. 存储过程与存储函数的创建语法

    (1) 存储过程

          1) 创建语法：

CREATE [OR REPLACE] PROCEDURE procedure_name[(parameter_name in|out|in out parameter_type, [, ...])]
{IS | AS}
  [columnName1 tableName.columnName1%type;
   columnName2 tableName.columnName2%type;
   ...
  ]
BEGIN
  < procedure_body >
END;

-- 语法说明：
-- ① 用[]包含的内容为可有可无，根据实际情况而定；
-- ② procedure_name：存储过程名称；
-- ③ parameter_name：参数名称；
-- ④ 参数模式：
--   in: 是参数的默认模式，这种模式就是在程序运行的时候已经具有值，在程序体中值不会改变；即，可以传入参数；
--   out：该模式定义的参数只能在过程体内部赋值，标识该参数可以将某个值传递回调用它的过程；即，可以返回值；
--   in out：表示该参数可以向过程中传递值，也可以将某个值传出去；即，既可以传入参数，也可以返回值；
-- ⑤ parameter_type：参数数据类型；
-- ⑥ is、as：在存储过程中，两者没有任何区别；但是在视图中只能用as，在游标中只能用is；
-- ⑦ procedure_body：PL/SQL子程序体；即该存储过程要执行的操作内容；
-- ⑧ 创建存储过程时，可以在is或者as后面添加对类型或变量的说明；

             备注，创建存储过程时需要注意的一些细节问题： 

                      ① 定义无参存储过程时，过程名称后面不能加括号，否则将会创建失败；

                      ② 无参存储过程调用时，可以加括号也可不加，可通过PL/SQL调用，也可以通过execute关键字调用；

                      ③ 有参存储过程的参数类型一定不能指定精度，否则将会报错，或创建失败；

                      ④ 利用“表名.字段名%TYPE” 可以获取到表中字段的具体类型，以便声明变量时设置其类型；

                      ⑤ 当需要通过：DBMS_OUTPUT.PUT_LINE('... ...'); 向SQL Developer控制台输出脚本内容时，注意需要先开启serveroutput，开启命令为如下：

set serveroutput on;

                      ⑥ 关于存储过程的参数模式说明如下：

 

          2) 实例：创建添加员工信息的存储过程。

create or replace procedure addEmployee(eNo in out number, uName in out varchar2, dNo in out number, sal in out number, com in out number)
as
  empNo emp.empNo%type;
  usernName emp.username%type;
  deptNo emp.deptno%type;
  salary emp.salary%type;
  comm emp.comm%type;
begin
  insert into emp(empNo, username, deptNo, salary, comm)values(eNo, uName, dNo, sal, com);
end;

  PL/SQL调用存储过程：

declare
  empNo emp.empno%type := 7777;
  username emp.username%type := 'Hellen';
  deptNo emp.deptno%type := 10;
  salary emp.salary%type := 3800;
  comm emp.comm%type := 700;
begin
  addEmployee(empNo, username, deptno, salary, comm);
end;

          3) 实例： 创建无参存储过程。

create or replace procedure firstProcedure as
begin
  DBMS_OUTPUT.PUT_LINE('你好，我是第一个存储过程...');
end;

 PL/SQL调用存储过程：

-- 方式一：
begin
  firstprocedure();
end;

-- 方式二：
begin
  firstprocedure;
end;

-- 脚本输出：
匿名块已完成
你好，我是第一个存储过程...

    (2) 存储函数

         1) 创建语法：

CREATE [OR REPLACE] FUNCTION function_name[(parameter_name in parameter_type, [, ...])]
    RETURN returnValType
{IS | AS}
  [variable1 type;
   variable2 type;
   ...
  ]
BEGIN
  < procedure_body >
END;

-- 语法说明：
-- ① 用[]包含的内容为可有可无，根据实际情况而定；
-- ② function_name：存储函数名称；
-- ③ parameter_name：参数名称；
-- ④ in: 参数模式，存储函数只有in模式；
-- ⑤ parameter_type：参数数据类型；
-- ⑥ returnValType：函数返回值数据类型；
-- ⑦ is、as：在存储过程中，两者没有任何区别；但是在视图中只能用as，在游标中只能用is；
-- ⑧ variable、type：分别用于声明函数在执行过程中所需要的变量名称和数据类型； 
-- ⑨ procedure_body：PL/SQL子程序体；即该存储函数要执行的操作内容；
-- ⑩ 创建存储过程时，可以在is或者as后面添加对类型或变量的说明；

 备注，使用存储函数需要注意的一些细节问题： 

            ① 存储函数必须包含一个return子句，用于指定函数要返回的数据类型；

            ② 函数定义的时候，无参函数名后面一定不能加括号；否则将创建失败；

            ③ 函数调用时，无参函数方法名后面可以不加括号，但是有参函数必须加括号、并传入参数；

          2) 实例：创建为员工加薪的存储函数。

create or replace FUNCTION addSalary(eNo in number, addVal in number)
  RETURN number
AS
  pNo number;         -- 定义变量保存员工编号
  pName varchar2(30); -- 定义变量保存员工用户名
  pDeptNo number;     -- 定义变量保存员工部门编号
  pSal number;        -- 定义变量保存员工的工资
  pComm number;       -- 定义变量保存员工的奖金
  pTotal number;      -- 定义变量保存员工的总收入
  newSalary number;   -- 定义变量保存加薪后的工资
  newComm number;     -- 定义变量保存加薪后的奖金
BEGIN
  SELECT empNo, username, deptNo, salary, comm INTO pNo, pName, pDeptNo, pSal, pComm FROM EMP WHERE empNo=eNo;
  pTotal := (pSal+pComm)*12;
  DBMS_OUTPUT.PUT_LINE('员工'||pName||'：初始工资为='||pSal||'；初始奖金为='||pComm||'；初始年薪为='||pTotal);

  newSalary := pSal+pSal*addVal;
  newComm := newSalary*0.12;
  UPDATE EMP SET username=pName, deptNo=pDeptNo, salary=newSalary, comm=newComm WHERE empNo=pNo;
  RETURN newSalary;
END;

  PL/SQL调用存储函数，为编号为"2222"的员工加薪30%。

declare
  a number;        -- 定义变量，接受存储函数返回值
begin
  a := addSalary(1111, 0.3);    -- 调用存储函数
  DBMS_OUTPUT.put_line(a);      -- 打印存储函数返回值
end;

  以上调用存储函数运行结果为：