关系数据库之存储过程

小闫同学啊

发布于 2020-02-29 16:41:25

1.2K0

发布于 2020-02-29 16:41:25

文章被收录于专栏：小闫笔记小闫笔记

A strong, positive self-image is the best possible preparation for success.

—— Joyce Brothers

无论是 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 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：传入传出参数，比如传入一个值，存储过程中引用后再修改，向调用者传出新值。更多精彩文章请关注公众号『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： 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    elseend 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 ;