1. 存储过程的概念
存储过程 (Stored Procedure) 是在大型数据库系统中 , 一组为了
完成特定功能的 SQL 语句集,存储在数据库中 , 经过第一次编译后再次调用不需要再次编译, 用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数)来执行它 , 存储过程是数据库中的一个重要对象 ; 存储过程中可以包含逻辑控制语句和数据操纵语句, 它可以接受参数 , 输出参数 , 返回单个或多个结果集以及返回值;
从存储过程的概念中可以看出，存储过程和编程语言中的函数存在很大的相似性，存储过程是由数据库本身去维持的，用户通过名称和参数来调用它。存储过程本身非常灵活，因此可以将动态传递简单参数但是过程复杂的sql语句改写为存储过程，从而进行非常方便的调用，并且由于一次编译后无需再次编译，速度会超过一般的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;
调用存储过程 :
call GetNameByID(1, @userName);
select @userName;
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;
该存储过程传入三个参数 , 货品 ID , 是否折扣以及返回的价格 , 在存储过程内部 , 定义两个局部变量 tmpPrice 和 prodDiscRate , 把查询出来的结果赋给临时变量 , 再判断是否折扣 , 最后把局部变量的值赋给输出参数 ; 调用如下 :
call GetPriceByID(1, true, @prodPrice);
select @prodPrice;
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();
