关系数据库之存储过程

Editor：闫玉良

很多人在上学期间，也许并未学习过关系数据库中「存储过程」的相关内容，但在工作上是绕不开的，那今天就来学习一下吧。

更多精彩文章请关注公众号『Pythonnote』或者『全栈技术精选』

无论是 MySQL 还是 SQL Server 数据库，最新版本都已支持『存储过程』。但是为了通用性，本文章以 MySQL 为例。

因为 SQL Server 数据库为商业软件，所以其使用率较开源的 MySQL 来说要低一些。对于大数据处理来说， MySQL 更有优势，这就更使得很多中型、大型网站都使用 MySQL 。二者语法上也有些许差别，考虑到学习之后的应用情况，特选 MySQL。

存储过程

1.简介

存储过程『Stored Procedure』是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象，即面向对象的思想。它通过编译后存储在数据库中，用户可以像调用函数一样通过特定的方式执行存储过程。

简单来讲，存储过程就是封装了一堆 SQL 语句，方便复用。如此直白，应该不会不理解了。

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 label1

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：传入传出参数，比如传入一个值，存储过程中引用后再修改，向调用者传出新值。

变量赋值：

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 就是将数字类型无符号化

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： case

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 标号：标号可以用在begin repeat while 或者 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 ;

参考文章：

https://www.cnblogs.com/geaozhang/p/6797357.html

http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html

更多精彩文章请关注公众号『Pythonnote』或者『全栈技术精选』

打赏