很多人在上学期间，也许并未学习过关系数据库中「存储过程」的相关内容，但在工作上是绕不开的，那今天就来学习一下吧。更多精彩文章请关注公众号『Pythonnote』或者『全栈技术精选』
无论是 MySQL 还是 SQL Server 数据库，最新版本都已支持『存储过程』。但是为了通用性，本文章以 MySQL 为例。
因为
SQL Server数据库为商业软件，所以其使用率较开源的MySQL来说要低一些。对于大数据处理来说，MySQL更有优势，这就更使得很多中型、大型网站都使用MySQL。二者语法上也有些许差别，考虑到学习之后的应用情况，特选MySQL。
存储过程
1.简介
存储过程『Stored Procedure』是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象，即面向对象的思想。它通过编译后存储在数据库中，用户可以像调用函数一样通过特定的方式执行存储过程。
简单来讲，存储过程就是封装了一堆 SQL 语句，方便复用。如此直白，应该不会不理解了。更多精彩文章请关注公众号『Pythonnote』或者『全栈技术精选』
1.1 优点
1) 通过封装，隐藏执行的 SQL 语句，以及逻辑操作。
2) 可以像函数一样传递参数，并回传值。
1.2 缺点
1) 存储过程根据数据库的不同而不同，移植性弱。比如切换不同厂商的数据库，由于编程语言的差别，需要重新编译。
2) 无法使用 select 指令来执行，它属于子程序，区别于用户定义的函数。
2.创建和调用
1) 自定义结束符号。一般在 mysql 中，我们使用封号来结束语句，可以临时修改为其他符号：
-- 将语句的结束符号从封号;临时改为两个$$或//(可以是自定义) DELIMITER $$ 或 DELIMITER //
2) 创建存储过程
CREATE PROCEDURE 存储过程名称(参数...)
3) 一个存储过程开始与结束符号
BEGIN .... END
4) 创建存储函数
create function 存储函数名称(参数)
5) 调用存储过程
call 存储过程名称(参数);
6) 存储过程体以 begin 开始，以 end 结束，当然，这是可以嵌套的。
注意：每个嵌套块以及其中的每条语句，都必须以封号结尾，最外层（表示过程体结束的
begin-end块不需要写）
7) 为每一个语句块打标签，可以增强可读性，并不易丢掉过多嵌套块中的某个结尾。
label1: BEGIN label2: BEGIN label3: BEGIN statements; END label3 ; END label2; END label
2.1 示例
mysql> delimiter $$ -- 将语句的结束符号从封号;临时改为两个$$(可以是自定义) mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER) -> BEGIN -> DELETE FROM MATCHES -> WHERE playerno = p_playerno; -> END$$ Query OK, 0 rows affected (0.01 sec) mysql> delimiter; -- 将语句的结束符号恢复为封号
上面的存储过程解读：存储过程名为「delete_matches」；存储过程需要传入的参数为 INTEGER 类型的 p_playerno （球员编号）；由 BEGIN 表示过程体开始，执行的语句为删除 MATCHES 表中字段 playerno 的值等于传入参数值的记录。以 END 表示过程体结束。
调用这一存储过程时：
mysql> call delete_matches(57); Query OK, 1 row affected (0.03 sec)
3.参数
从示例中我们可以看到传入参数表示为：
CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
已知参数名称与参数类型，那么括号中 IN 代表什么意思呢？
答： IN 代表传入参数，也就是调用存储过程时，向存储过程内传入值。
除此之外还有另外两个参数：OUT 和 INOUT
OUT ：传出参数，也就是存储过程向调用者传出值。类似于程序中的 return
INOUT：传入传出参数，比如传入一个值，存储过程中引用后再修改，向调用者传出新值。更多精彩文章请关注公众号『Pythonnote』或者『全栈技术精选』
变量赋值：
SET @变量=值
3.1 in
mysql> delimiter $$ -- 将语句的结束符号从封号;临时改为两个$$(可以是自定义) mysql> create procedure in_param(in p_in int) -> begin -> select p_in; -- 查询传入的变量 -> set p_in=2; -- 重新设置变量 -> select P_in; -- 查询修改后的变量 -> end$$ mysql> delimiter ; mysql> set @p_in=1; --设置全局变量 mysql> call in_param(@p_in); -- 调用存储过程，传入设置的变量值 +------+ | p_in | +------+ | 1 | +------+ +------+ | P_in | +------+ | 2 | +------+ mysql> select @p_in; -- 查询全局变量 +-------+ | @p_in | +-------+ | 1 | +-------+
以上可以看出，p_in 在存储过程中被修改，但并不影响 @p_in 的值，因为前者为局部变量、后者为全局变量。
3.2 out
mysql> delimiter // mysql> create procedure out_param(out p_out int) -> begin -> select p_out; -> set p_out=2; -> select p_out; -> end -> // mysql> delimiter ; mysql> set @p_out=1; mysql> call out_param(@p_out); +-------+ | p_out | +-------+ | NULL | +-------+ -- 因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null +-------+ | p_out | +-------+ | 2 | +-------+ mysql> select @p_out; +--------+ | @p_out | +--------+ | 2 | +--------+ -- 调用了out_param存储过程，输出参数，改变了p_out变量的值
3.3 inout
mysql> delimiter $$ mysql> create procedure inout_param(inout p_inout int) -> begin -> select p_inout; -> set p_inout=2; -> select p_inout; -> end -> $$ mysql> delimiter ; mysql> set @p_inout=1; mysql> call inout_param(@p_inout); +---------+ | p_inout | +---------+ | 1 | +---------+ +---------+ | p_inout | +---------+ | 2 | +---------+ mysql> select @p_inout; +----------+ | @p_inout | +----------+ | 2 | +----------+ -- 调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量
4.变量
4.1 变量声明
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
注意：局部变量声明一定要放置在存储过程体的开始。
datatype为MySQL的数据类型，如：int，float，date，varchar(length)
示例：
DECLARE l_int int unsigned default 4000000; DECLARE l_numeric number(8,2) DEFAULT 9.95; DECLARE l_date date DEFAULT '1999-12-31'; DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59'; DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
unsigned 就是将数字类型无符号化更多精彩文章请关注公众号『Pythonnote』或者『全栈技术精选』
4.2 变量赋值
SET 变量名 = 表达式值 [,variable_name = expression ...]
4.3 用户变量
大家一定会有些迷惑，为什么有些变量是直接为变量名赋值，而有些变量则需要在变量名前加 @ 呢？那是因为添加后，变为了用户变量名。
mysql > SET @name='Ethan Yan'; mysql > SELECT @name; +---------------------+ | @name | +---------------------+ | Ethan Yan | +---------------------+
在存储过程中使用用户变量：
mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World'); mysql > SET @greeting='Hello'; mysql > CALL GreetWorld( ); +----------------------------+ | CONCAT(@greeting,' World') | +----------------------------+ | Hello World | +----------------------------+
5.注释
单行注视：--
多行注视：
/* 此处为多行注释 */
6.常用操作
6.1 MySQL 存储过程的查询
1) 查询数据库中的所有存储过程
select name from mysql.proc where db='数据库名'; 或者 select routine_name from information_schema.routines where routine_schema='数据库名'; 或者 show procedure status where db='数据库名';
2) 查询某个存储过程详细内容
SHOW CREATE PROCEDURE 数据库.存储过程名;
6.2 MySQL 存储过程的修改
ALTER PROCEDURE
6.3 MySQL 存储过程的删除
DROP PROCEDURE
6.4 MySQL 存储过程的控制语句
1) 变量作用域：局部变量在存储过程内优先级高，当执行到 END 时，局部变量消失。如果不想消失，可以通过参数 OUT 将其传出给调用者。
2) 条件语句1：if-then-else
if ... then ... ; else ...; end if;
3) 条件语句2：
mysql > DELIMITER // mysql > CREATE PROCEDURE proc3 (in parameter int) -> begin -> declare var int; -> set var=parameter+1; -> case var -> when 0 then -> insert into t values(17); -> when 1 then -> insert into t values(18); -> else -> insert into t values(19); -> end case; -> end; -> // mysql > DELIMITER ; case when var=0 then insert into t values(30); when var>0 then when var<0 then else end case
4) 循环语句1 while ···· end while
while 条件 do --循环体 endwhile
5) 循环语句2 repeat···· end repea
repeat --循环体 until 循环条件 end repeat;
6) 循环语句3 loop ·····endloop
loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat循环一样不需要结束条件,leave 语句的意义是离开循环。
mysql > DELIMITER // mysql > CREATE PROCEDURE proc6 () -> begin -> declare v int; -> set v=0; -> LOOP_LABLE:loop -> insert into t values(v); -> set v=v+1; -> if v >=5 then -> leave LOOP_LABLE; -> end if; -> end loop; -> end; -> // mysql > DELIMITER ;
LABLES 标号：标号可以用在beginrepeatwhile 或者 loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。
7) 迭代
ITERATE 通过引用复合语句的标号，来从新开始复合语句：
mysql > DELIMITER // mysql > CREATE PROCEDURE proc10 () -> begin -> declare v int; -> set v=0; -> LOOP_LABLE:loop -> if v=3 then -> set v=v+1; -> ITERATE LOOP_LABLE; -> end if; -> insert into t values(v); -> set v=v+1; -> if v>=5 then -> leave LOOP_LABLE; -> end if; -> end loop; -> end; -> // mysql > DELIMITER ;
参考文章：
