数据库的存储过程

1. 存储过程的概念

存储过程 (Stored Procedure) 是在大型数据库系统中 , 一组为了完成特定功能的 SQL 语句集 , 存储在数据库中 , 经过第一次编译后再次调用不需要再次编译 , 用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数) 来执行它 , 存储过程是数据库中的一个重要对象 ; 存储过程中可以包含 逻辑控制语句 和 数据操纵语句 , 它可以接受参数 , 输出参数 , 返回单个或多个结果集以及返回值 ;

2. 存储过程的优缺点

优点 :

由于应用程序随着时间推移会不断更改 , 增删功能 , SQL 语句会变得更复杂 , 存储过程为封装此类代码提供了一个替换位置 ;
由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中 , 所以存储过程运行要比单个的 SQL 语句块要快 ;
由于在调用时只需用提供存储过程名和必要的参数信息 , 所以在一定程度上也可以减少网络流量 , 简单网络负担 ;
可维护性高 , 更新存储过程通常比更改 , 测试以及重新部署程序集需要较少的时间和精力 ;
代码精简一致 , 一个存储过程可以用于应用程序代码的不同位置 ;
增强安全性 :

通过向用户授予对存储过程 (而不是基于表) 的访问权限 , 它们可以提供对特定数据的访问 ;
提高代码安全 , 防止 SQL注入 (但未彻底解决 , 例如将数据操作语言 DML 附加到输入参数) ;
SQLParameter 类指定存储过程参数的数据类型 , 作为深层次防御性策略的一部分 , 可以验证用户提供的值类型 (但也不是万无一失 , 还是应该传递至数据库前得到附加验证) ;

 

缺点 :

如果更改范围大到需要对输入存储过程的参数进行更改 , 或者要更改由其返回的数据 , 则仍需要更新程序集中的代码以添加参数 , 等等 ;
可移植性差 , 由于存储过程将应用程序绑定到 Server , 因此使用存储过程封装业务逻辑将限制应用程序的可移植性 ; 如果应用程序的可移植性在您的环境中非常重要 , 则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择 ;

3. 编写简单存储过程
             创建一个存储过程：

create procedure GetUsers()
begin 
    select * from user; 
end;

          调用存储过程：

call GetUsers();

         删除存储过程：

drop procedure if exists GetUsers;

4. 带参数的存储过程
MySql 支持 IN (传递给存储过程) , OUT (从存储过程传出) 和 INOUT (对存储过程传入和传出) 类型的参数 , 存储过程的代码位于 BEGIN 和 END 语句内 , 它们是一系列 SQL 语句 , 用来检索值 , 然后保存到相应的变量 (通过指定INTO关键字) ;

下面的存储过程接受三个参数 , 分别用于获取用户表的最小 , 平均 , 最大分数 , 每个参数必须具有指定的类型 , 这里使用十进制值(decimal(8,2)) ,  关键字 OUT 指出相应的参数用来从存储过程传出：

create procedure GetScores(
    out minScore decimal(8,2),
    out avgScore decimal(8,2),
    out maxScore decimal(8,2)
)
begin
    select min(score) into minScore from user;
    select avg(score) into avgScore from user;
    select max(score) into maxScore from user;
end;

调用此存储过程 , 必须指定3个变量名(所有 MySql 变量都必须以 @ 开始) , 如下所示 :

call GetScores(@minScore, @avgScore, @maxScore);

 

该调用并没有任何输出 , 只是把调用的结果赋给了调用时传入的变量 @minScore, @avgScore, @maxScore , 然后即可调用显示该变量的值 :
select @minScore, @avgScore, @maxScore;

使用 IN 参数 , 输入一个用户 id ,  返回该用户的名字 :

create procedure GetNameByID(
 in userID int,
 out userName varchar(200)
)
begin
 select name from user
 where id = userID
 into userName;
end;
123456789
调用存储过程 :
call GetNameByID(1, @userName);
select @userName;
12

 

5. 复杂一点示例
           根据 ID 获取货品的价格 , 并根据参数判断是否折扣 :

create procedure GetPriceByID(
 in prodID int,
 in isDisc boolean,
 out prodPrice decimal(8,2)
) 
begin
 declare tmpPrice decimal(8,2);
 declare prodDiscRate decimal(8,2);
 set prodDiscRate = 0.88;
 select price from products
 where id = prodID
 into tmpPrice;
 if isDisc then
  select tmpPrice*prodDiscRate into tmpPrice;
 end if;
 select tmpPrice into prodPrice;
end;
1234567891011121314151617181920
该存储过程传入三个参数 , 货品 ID , 是否折扣以及返回的价格 , 在存储过程内部 , 定义两个局部变量 tmpPrice 和 prodDiscRate , 把查询出来的结果赋给临时变量 , 再判断是否折扣 , 最后把局部变量的值赋给输出参数 ; 调用如下 :
call GetPriceByID(1, true, @prodPrice);
select @prodPrice;
12

6. DELIMITER

MySql 的命令行客户机的语句分隔符默认为分号 ; ，而实用程序也是用 ; 作为分隔符，这会使得存储过程的 SQL 出现语法错误，使用 DELIMITER $$ 告诉命令行实用程序将 $$ 作为新的语句结束分隔符，最后再使用 DELIMITER ; 改回来，如下所示：

DELIMITER $$ 
create procedure getUsers() begin select * from user; 
end $$ 
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE getcount()
BEGIN
SELECT
*
FROM
contract_user;
END $$
DELIMITER ;
CALL getcount();