存储过程

存储过程

文前话：
内心一场金戈铁马

---

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

特点：
优：

1. 方便更改

由于应用程序随着时间推移会不断更改 , 增删功能 , SQL 语句会变得更复杂 , 存储过程为封装此类代码提供了一个替换位置 ;

2. 快

由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中 , 所以存储过程运行要比单个的 SQL 语句块要快 ;

3. 减轻网络负担

由于在调用时只需用提供存储过程名和必要的参数信息 , 所以在一定程度上也可以减少网络流量 , 简单网络负担 ;

4. 可维护性高

可维护性高 , 更新存储过程通常比更改 , 测试以及重新部署程序集需要较少的时间和精力 ;

5. 代码精简

代码精简一致 , 一个存储过程可以用于应用程序代码的不同位置 ;

6. 安全

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

缺：

1. 可移植性差

可移植性差 , 由于存储过程将应用程序绑定到 Server , 因此使用存储过程封装业务逻辑将限制应用程序的可移植性 ; 如果应用程序的可移植性在您的环境中非常重要 , 则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择 ;

2. 更新性差

如果更改范围大到需要对输入存储过程的参数进行更改 , 或者要更改由其返回的数据 , 则仍需要更新程序集中的代码以添加参数 , 等等 ;

创建存储过程

create procedure myBook()
begin
select * from book;
end

调用存储过程：

    call myBook()

查看存储过程
1、通过数据字典表查询
 存储过程的定义信息保存在数据字典表information_schema.routines中：

select ROUTINE_NAME, ROUTINE_TYPE
from information_schema.ROUTINES  
where ROUTINE_SCHEMA='db1';

2、使用show语句查询

SHOW PROCEDURE STATUS WHERE db='数据库名';  

3、查看存储过程详细的定义信息

    SHOW CREATE PROCEDURE 数据库.存储过程名;

存储过程的修改

ALTER {PROCEDURE | FUNCTION}……语句只能改变存储过程的特征，
不能修改过程的参数以及过程体。如果想做这样的修改，
必须先使用DROP PROCEDURE 删除过程，然后使用and CREATE PROCEDURE重建过程。

存储过程的删除

    DROP PROCEDURE [IF EXISTS] db_name.sp_name;

存储过程的安全

不是每个用户都可以调用一个存储过程；一个用户想调用其它用户创建的过程，必须被授予过程的execute权限：

    GRANT  EXECUTE
    ON PROCEDURE  <过程名>
    TO  <user>

但是在mysql命令行中由于结束是“；”
所以需要修改一下结束符，命令为：

    delimiter //

---

带参数的存储过程

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

示例一：

第一步创建

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;

第二步调用存储过程参数必须用@开始该调用并没有任何输出 , 只是把调用的结果赋给了调用时传入的变量 @minScore, @avgScore, @maxScore ,

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

然后即可调用显示该变量的值 :

select @minScore, @avgScore, @maxScore;

示例二：
使用传入(in)传出(out)
 使用 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;