第八章 MySQL存储过程

Posted on | In | | Visitors:

本章介绍存储过程，可以提高MySQL的运行效率。

1. 存储过程简介

存储过程是sql语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理

  • 存储过程存放在数据库内，可以由应用程序调用执行，允许用户声明变量以及进行流程控制，可以接收参数（输入输出参数都可以接收）并且可以存在多个返回值
  • 存储过程的效率要比单纯的SQL语句的执行效率高
    优点：
    （1）增强sql语句的功能和灵活性；
    （2）实现较快的执行速度；
    （3）减少网络流量

2. 创建存储结构语法分析

创建存储过程
CREATE
[DEFINER={user|CURRENT_USER}]
PROCEDURE sp_name([proc_paremeter[,…]])
[characteristic…] routine_body
proc_paremeter:
[IN|OUT|INOUT]param_name type
创建存储过程语法结构基本和创建自定义函数语法结构相同，最大不同就是参数部分。
首先可以指定创建者DEFINER，如果省略，则默认指向（当前用户））当前登入到Mysql客户端的用户。
sp_name指的是存储过程的名字，它后面的参数可以有多个，存储过程名使用PROCEDURE修饰，参数前面可以使用IN|OUT|INOUT修饰。
IN:表示该参数的值必须在调用存储过程时指定（存储过程中该值不能返回，只能进不能出）。
OUT：表示该参数的值可以被存储过程代码改变，并且可以返回。
INOUT：表示该参数的可以被指定，并且可以被存储过程体改变和返回。
存储过程的特性（【characteristic…】）与自定义函数特性是完全相同的。
特性
COMMENT:注释
CONTAINS SQL:包含sql语句，但不包含读或者写数据的语句；
NOT SQL：不包含sql语句；
READS SQL DATA:包含读数据的语句；
MODIFIES SQL DATEA:包含写数据的语句；
SQL SECURITY{DEFINER|INVOKER}:指明谁有权限连执行
过程体
（1）过程体由合法的sql语句构成；
（2）过程体可以是任意的sql语句；
（3）过程体如果为复合结构则使用begin_end语句；
（4）复合结构可以包含声明、循环、控制结构

3.创建不带参数的存储过程

MySQL创建不带参数的存储过程（该存储过程获取Mysql版本的功能）
创建存储过程：

1
2
3
CREATE PROCEDURE sp1() SELECT VERSION();
mysql> CREATE PROCEDURE sp1() SELECT VERSION();
Query OK, 0 rows affected (0.00 sec)

调用存储过程
【1】CALL sp_name([parameter[,…]])
【2】CALL sp_name[()]
如果存储过程在封装的过程中没有参数，小括号带不带都可以。但是如果封装时有参数，则小括号必须带。
例如上面调用：CALL sp1或者CALL sp1();

1
2
3
4
5
6
7
8
mysql> CALL sp1;
+-----------+
| VERSION() |
+-----------+
| 5.5.62    |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

4. 创建带有IN类型参数的存储过程

下面将创建一个可以删除指定id记录的存储过程。
首先看一下test数据表中有哪些记录：

1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
|  1 | hao      |
|  2 | yuan     |
|  3 | ye       |
|  4 | xiaohua  |
+----+----------+
4 rows in set (0.04 sec)

下面就创建带有IN类型参数的存储过程：

1
2
3
4
5
6
7
8
mysql> DELIMITER //
mysql> CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
    -> BEGIN
    -> DELETE FROM test WHERE id = p_id;
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;

注意：p_id与id的名字不能一样，如果一样在调用此过程时将删除表的所有记录。
下面调用此过程：

1
2
3
4
mysql> CALL removeUserById(2);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test WHERE id = 2;
Empty set (0.00 sec)

可以看出已经删除。
如果要修改存储过程的过程体：
1）必须先删除存储过程
DROP PROCEDURE [ IF EXISTS ] sp_name
2） 再重建存储过程
每次自定义函数或者存储过程之前：
1）DELIMITER //
先把界定符重新定义下 不要用；
2）定义完后，再改成DELIMETER ；

5. 创建带有IN和OUT类型参数的存储过程

下面创建一个不仅可以根据id删除记录的过程，并且可以返回剩余记录的数量。

（1）创建带有in和out参数类型的存储过程【输入+返回】

1
2
3
4
5
6
7
8
9
mysql> DELIMITER //
mysql> CREATE PROCEDURE removeUserAndReturnNum(IN p_id INT UNSIGNED , OUT num INT UNSIGNED)
 -> BEGIN
 -> DELETE FROM test WHERE id = p_id;
 -> SELECT COUNT(id) FROM test INTO num;
 -> END
 -> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

看看test数据表中还有几条记录

1
2
3
4
5
6
7
mysql> SELECT COUNT(id) FROM test;
+-----------+
| COUNT(id) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

（2）过程调用

1
2
mysql> CALL removeUserAndReturnNum(1,@nums);
Query OK, 1 row affected (0.03 sec)

将剩余的记录数量存储在nums变量之中。

1
2
3
4
5
6
7
mysql> SELECT @nums;
+-------+
| @nums |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

局部变量
局部变量只在begin和end之间的语句块有效；语句块运行完毕，局部变量消失；
在begin..end语句中声明。专门用declare语句声明，而且必须放在begin..end语句的第一行。作用范围在begin到end语句块之间。在该语句块里设置的变量称为局部变量。
语法结构：
BEGIN
declare test int;
…
END
用户变量
通过SELECT INTO语句或者 SET语句 （尤其带有@符号） 声明的为用户变量 只在该用户使用的客户端生效。

1
2
mysql> SET @i = 7;
Query OK, 0 rows affected (0.00 sec)

6. 创建带有多个OUT类型参数的存储过程

MySQL创建带有多个OUT类型参数的存储过程（创建存储过程根据年龄age字段删除记录，返回的信息有两个，一个是删除的用户数，一个是剩余的用户数）

（1）系统函数——ROW_COUNT（）

作用：返回插入，删除或者更新 被影响到的记录总数

1
2
3
4
5
6
7
8
9
10
11
mysql> INSERT users(username,age) VALUES('e',12),('f',13),('h',14),('j',13);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

（2）创建存储过程

1
2
3
4
5
6
7
8
mysql> CREATE PROCEDURE removeUserReturnInfo(IN p_age INT UNSIGNED, OUT dels SMALLINT UNSIGNED, OUT rests SMALLINT UNSIGNED)
    -> BEGIN
    -> DELETE FROM users WHERE age=p_age;
    -> SELECT ROW_COUNT() INTO dels;
    -> SELECT COUNT(id) FROM users INTO rests;
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)

先看一下users中的记录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> DELIMITER ;
mysql> SELECT * FROM users;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | A        |   12 |
|  2 | B        |   13 |
|  3 | C        |   14 |
|  4 | D        |   13 |
|  5 | e        |   12 |
|  6 | f        |   13 |
|  7 | h        |   14 |
|  8 | j        |   13 |
+----+----------+------+
8 rows in set (0.00 sec)

一共八条记录下面删除age为13的用户

1
2
3
4
5
6
7
8
9
mysql> CALL removeUserReturnInfo(13,@dels,@rests);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT @dels,@rests;
+-------+--------+
| @dels | @rests |
+-------+--------+
|     4 |      4 |
+-------+--------+

删除了四条还剩余四条。

7. 存储过程与自定义函数的区别

存储过程与自定义函数的区别
【1】存储过程实现的功能要复杂一些；而函数的针对性更强（实际工作中很少使用函数针对表进行操作，都是使用存储过程对表进行操作）。
【2】存储过程可以返回多个值，而函数只能有一个返回值。
【3】存储过程一般独立的来执行（CALL 存储过程名），而函数一般作为SQL语句的组成来出现和内置函数功能一致(SELECT)。
如果开发过程中经常使用到某个功能，一般将它封装为存储过程。

小结

  1. 存储过程：是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。

  2. 参数：输入类型，输出类型，输入&&输出；

  3. 创建：CREATE…PROCEDURE…;

  4. 注意事项：

1) 创建存储过程或者自定义函数时需要通过delimiter语句修改定界符。

2) 如果函数体或过程体有多个语句，需要包含在BEGIN…END语句块中。

3) 存储过程通过call调用。