procedure

存储过程

一组为了完成特定功能的预编译Sql语句，存储在数据库中，一次编译后即运行在内存中，以后可以再次调用，效率高

优点：

• 一次编译，多次调用：编译后书籍存储于内存，可以被后续调用
• 高效：效率高于一般的sql语句
• 简化操作：通过把处理封装在容易使用的单元中，简化复杂的操作；
• 解耦，独立和优化管理：如果表名、列名或业务逻辑有变化。只需要更改存储过程的代码，使用人员无需更改代码；
• 存储过程有助于减少应用程序和数据库服务器之间的流量：因为应用程序不必发送多个冗长的 SQL 语句，而只用发送存储过程的名称和参数；
• 存储的程序是安全的：数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

语法：

• 创建：CREATE PROCEDURE 过程名 (参数列表) [characteristic ...] 
• 修改：ALTER PROCEDURE 过程名 [characteristic ...]
• 删除：DROP PROCEDURE [IF EXISTS] 过程名
• 调用：CALL 过程名(参数列表)//如果过程里面有select out参数；则call即可返回out参数，否则再调用一次select out参数
• 定义: declare 变量名 数据类型 default value;
• 全局变量:set @变量名 = value，直接将@变量放入存储过程的参数列表使用，也可以直接传参数
• delimiter $ 设置结束标记
• end $ 结束

【注：】参数列表里的参数：in,out或者inout。其中out和input必须是@变量（也就是声明变量或者全局变量）

举例:

//例一
set @i = 1;

delimiter $
create procedure test(out o int)
begin
declare innera int default 1;
declare innerb int default 1;
while innera<=10 do
set innera = innera + 1;
set innerb = innera;
end while;
set o = innera;
end $
 
call test(@i);
select @i;


//例二
delimiter $
create procedure test(in i int)
begin
declare innera int default 1;
declare innerb int default 1;
while innera<=10 do
set innera = innera + 1;
set innerb = innera;
end while;
select i;
end $

call test(@i);

//例三
delimiter $
create procedure test(inout o int)
begin
declare innera int default 1;
declare innerb int default 1;
while innera<=10 do
set innera = innera + 1;
set innerb = innera;
end while;
set o = innera;
select @i;
end $

call test(@i);

流程控制语句

1、IF 语句包含多个条件判断，根据结果为 TRUE、FALSE 执行语句，与编程语言中的 if、else if、else 语法类似。例如：
输入一个整数，使用 if 语句判断是正数还是负数，代码如下所示：

– 创建过程
delimiter $;
create procedure mypro2(in num int)
begin
if num<0 then – 条件开始
select ‘负数’;
elseif num=0 then
select ‘不是正数也不是负数’;
else
select ‘正数’;
end if;-- 条件结束
end $;
– 调用过程
call mypro2(-1);

2、case 是另一个条件判断的语句，类似于编程语言中的 choose、when 语法。MySQL 中的 case 语句有两种语法格式。例如：
输入一个整数，使用 case 语句判断是正数还是负数，代码如下所示：

– 创建过程
delimiter $
create procedure mypro3(in num int)
begin
case – 条件开始
when num<0 then select ‘负数’;
when num=0 then select ‘不是正数也不是负数’;
else select ‘正数’;
end case; – 条件结束
end $;
– 调用过程
call mypro3(1);

3、while 语句的用法和 java 中的 while 循环类似。例如：
定义存储过程，使用 while 循环输出 1 到 10 的累加和，代码如下所示：

– 创建过程
delimiter $
create procedure mypro5(out sum int)
begin
declare num int default 0;
set sum = 0;
while num<10 do – 循环开始
set num = num+1;
set sum = sum+num;
end while; – 循环结束
end $;
– 调用过程
call mypro5(@sum);
– 查询变量值
select @sum;

存储过程的管理

1、显示所有库的存储过程 ：SHOW PROCEDURE STATUS;

2、显示特定数据库的存储过程：SHOW PROCEDURE status where db = ‘schooldb’;

3、显示名称中包含“my”的存储过程：SHOW PROCEDURE status where name like ‘%my%’;

4、显示存储过程源码：SHOW CREATE PROCEDURE mypro1;

5、删除存储过程 ：drop PROCEDURE mypro1;