mysql学习笔记（四）

存储过程

• mysql命令的执行流程：SQL命令--->MySql引擎--->（分析）语法正确--->（编译）可识别命令--->执行结果--->返回给客户端
• 存储过程：SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。
• 存储过程的优点：
  1、增强了sql语句的功能和灵活性；
  2、实现较快的执行速度；
  3、减少了网络流量。
• 创建存储过程

CREATE [DEFINER = {user | CURRENT_USER}] PROCEDURE sp_name([proc_parameter[,...]])] [characteristic ...] routine_body

• proc_parameter:[IN | OUT | INOUT] param_name type

• 参数类型：
  1、IN：表示该参数的值必须在调用存储过程时指定；
  2、OUT：表示该参数的值可以被存储过程改变，并且可以返回；
  3、INOUT：表示该参数的调用时指定，并且可以被改变和返回。

• 过程体：
  1、由合法的SQL语句构成；
  2、可以是'任意'SQL语句；
  3、若为复合结构，则使用BEGIN...END语句；复合结构可以包含声明，循环，控制结构。

• 调用存储过程

# 方括号为可选项
# 如果存储过程在封装时没有带参数，那么调用时小圆括号可有可无，否则不能省略。
CALL sp_name([parameter[, ...]])
CALL sp_name[()]

# 创建一个存储过程
mysql> CREATE PROCEDURE sp1() SELECT VERSION();
Query OK, 0 rows affected (0.00 sec)

# 调用存储过程sp1
mysql> CALL sp1;
+-----------+
| VERSION() |
+-----------+
| 5.7.26    |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp1();
+-----------+
| VERSION() |
+-----------+
| 5.7.26    |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM cyc;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张三 |  20 |
|  2 | 张刘 |  11 |
|  3 | 李四 |  16 |
|  4 | tom% |  16 |
|  5 | nick |  23 |
|  6 | 王五 |  56 |
|  7 | 赵六 |  26 |
+----+------+-----+
7 rows in set (0.00 sec)

# 先修改定界符标志
mysql> DELIMITER //
# 创建一个删除记录的存储过程
mysql> CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
    -> BEGIN
    -> DELETE FROM cyc WHERE id = p_id;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL removeUserById(5);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM cyc;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张三 |  20 |
|  2 | 张刘 |  11 |
|  3 | 李四 |  16 |
|  4 | tom% |  16 |
|  6 | 王五 |  56 |
|  7 | 赵六 |  26 |
+----+------+-----+
6 rows in set (0.00 sec)

# 先修改定界符标志
mysql> DELIMITER //
# 创建一个删除记录并返回剩余记录总数的存储过程
mysql> CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED, OUT userNums INT UNSIGNED)
    -> BEGIN
    -> DELETE FROM cyc WHERE id = p_id;
    -> SELECT count(id) FROM cyc INTO userNums;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

# 还原定界符
mysql> DELIMITER ;
mysql> SELECT COUNT(id) FROM cyc;
+-----------+
| COUNT(id) |
+-----------+
|         6 |
+-----------+
1 row in set (0.00 sec)
# 调用存储过程
mysql> CALL removeUserAndReturnUserNums(3, @nums);
Query OK, 1 row affected (0.00 sec)
# 用一个用户变量@nums来接收返回值并查询
mysql> SELECT @nums;
+-------+
| @nums |
+-------+
|     5 |
+-------+
1 row in set (0.00 sec)

• 用户变量@变量名和mysql客户端绑定在一起，通过设置此变量只对当前客户端生效。BEGIN...END之间声明的变量为局部变量，通过SELECT语句、SELECT...INTO...、SET @变量名=变量值 称为用户变量。

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

mysql> SELECT @i;
+------+
| @i   |
+------+
|  666 |
+------+
1 row in set (0.00 sec)

• 修改存储过程，如下代码不能修改过程体，若想修改过程体，只能先将原来的存储过程删除，然后再重新创建。

ALTER PROCEDURE sp_name [characteristic ...]
COMMENT 'string' | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY {DEFINER | INVOKER}
# COMMENT：注释
# CONTAINS SQL：包含SQL语句，但不包含读和写数据的语句
# NO SQL：不包含SQL语句
# READS SQL DATA：包含读数据的语句
# MODIFIES SQL DATA：包含写数据的语句
# SQL SECURITY {DEFINER | INVOKER} 指明谁有权限来执行

• 删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name;

• 存储过程与自定义函数的区别
  1、存储过程实现的功能要复杂一些，而函数的针对性更强；
  2、存储过程可以返回多个值，而函数只能有一个返回值；
  3、存储过程一般独立地来执行，而函数可以作为其他SQL语句的组成部分来出现。
  4、创建存储过程或者自定义函数时需要通过delimiter语句修改定界符。

# 取得插入、删除、更新后被影响的记录数：ROW_COUNT()
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

mysql> DESC cyc;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)          | NO   |     | NULL    |                |
| age   | smallint(5) unsigned | NO   |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT cyc(name, age) VALUES('Mike', 19), ('Jhon', 20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

# 暂时更换定界符
mysql> DELIMITER //
# 创建一个通过age年龄来删除记录并返回删除记录个数和表中剩余记录总数
mysql> CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED, OUT delNums SMALLINT UNSIGNED, OUT userCounts SMALLINT UNSIGNED)
    -> BEGIN
    -> DELETE FROM cyc WHERE age = p_age;
    -> SELECT ROW_COUNT() INTO delNums;
    -> SELECT COUNT(id) FROM cyc INTO userCounts;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT * FROM cyc;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张三 |  20 |
|  2 | 张刘 |  11 |
|  8 | Mike |  19 |
|  4 | tom% |  16 |
|  9 | Jhon |  20 |
|  6 | 王五 |  56 |
|  7 | 赵六 |  26 |
+----+------+-----+
7 rows in set (0.00 sec)

mysql> SELECT COUNT(id) FROM cyc WHERE age = 20;
+-----------+
| COUNT(id) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)
# 调用存储过程
mysql> CALL removeUserByAgeAndReturnInfos(20, @a, @b);
Query OK, 1 row affected (0.00 sec)
# 用户变量@a，@分别来接收返回值
mysql> SELECT @a, @b;
+------+------+
| @a   | @b   |
+------+------+
|    2 |    5 |
+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM cyc;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | 张刘 |  11 |
|  8 | Mike |  19 |
|  4 | tom% |  16 |
|  6 | 王五 |  56 |
|  7 | 赵六 |  26 |
+----+------+-----+
5 rows in set (0.00 sec)