MySQL基础八：MySQL存储过程

来源：互联网发布：步人甲知乎编辑：程序博客网时间：2024/06/06 09:12

本文参考：http://www.imooc.com/video/2998

创建没有参数的存储过程；

mysql> #创建存储过程；mysql> CREATE PROCEDURE sp1() SELECT VERSION();Query OK, 0 rows affected (0.00 sec)mysql> #调用存储过程；mysql> CALL sp1;+------------+| VERSION()  |+------------+| 5.7.10-log |+------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> CALL sp1();+------------+| VERSION()  |+------------+| 5.7.10-log |+------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

带IN类型的存储过程；

mysql> #创建存储过程；mysql> CREATE PROCEDURE sp1() SELECT VERSION();Query OK, 0 rows affected (0.00 sec)mysql> #调用存储过程；mysql> CALL sp1;                                                +------------+| VERSION()  |+------------+| 5.7.10-log |+------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> CALL sp1();+------------+| VERSION()  |+------------+| 5.7.10-log |+------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> DESC users;+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || username | varchar(20)          | NO   |     | NULL    |                || password | varchar(32)          | NO   |     | NULL    |                || age      | tinyint(3) unsigned  | NO   |     | 10      |                || sex      | tinyint(1)           | YES  |     | NULL    |                |+----------+----------------------+------+-----+---------+----------------+5 rows in set (0.01 sec)mysql> SELECT * FROM users;+----+----------+----------------------------------+-----+------+| id | username | password                         | age | sex  |+----+----------+----------------------------------+-----+------+|  1 | Tom      | 123                              |  29 |    0 ||  2 | John     | 456                              |  38 |    0 ||  3 | John     | 456                              |  27 |    0 ||  4 | John     | 4852                             |  27 |    0 ||  5 | John     | 4852                             |  10 |    0 ||  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 ||  8 | Ben      | 456                              |  17 |    0 ||  9 | 111      | 222                              |  33 | NULL |+----+----------+----------------------------------+-----+------+8 rows in set (0.00 sec)mysql> #更换结束符；mysql> DELIMITER //mysql>mysql> #创建IN类型的存储过程；mysql> CREATE PROCEDURE removeUserById(IN id INT UNSIGNED)    -> BEGIN    -> DELETE FROM users WHERE id=id;#<span style="color:#ff0000;">注意这里的id会把数据表中全部元素都删除</span>    -> END    -> //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> CALL removeUserById(3);Query OK, 8 rows affected (0.07 sec)mysql> SELECT * FROM users;Empty set (0.00 sec)mysql> DROP PROCEDURE removeUserById;Query OK, 0 rows affected (0.00 sec)mysql> mysql> SHOW TABLES;+------------------+| Tables_in_world  |+------------------+| city             || country          || countrylanguage  || provinces        || tdb_goods        || tdb_goods_brands || tdb_goods_cate   || tdb_goods_types  || test             || users            |+------------------+10 rows in set (0.00 sec)mysql> SELECT * FROM users;Empty set (0.00 sec)mysql> SELECT * FROM test;+----+----------+| id | username |+----+----------+|  1 | John     ||  2 | 111      ||  3 | tom%     ||  4 | NULL     ||  5 | AA       ||  6 | BB       ||  7 | Rose     |+----+----------+7 rows in set (0.00 sec)mysql> DELIMITER //mysql>mysql> CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)    -> BEGIN    -> DELETE FROM users WHERE id=p_id;    -> END    -> //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> DROP PROCEDURE removeUserById;Query OK, 0 rows affected (0.00 sec)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.00 sec)mysql> DELIMITER ;mysql> CALL removeUserById(2);Query OK, 1 row affected (0.05 sec)mysql> SELECT * FROM test;+----+----------+| id | username |+----+----------+|  1 | John     ||  3 | tom%     ||  4 | NULL     ||  5 | AA       ||  6 | BB       ||  7 | Rose     |+----+----------+6 rows in set (0.00 sec)

mysql> USE world;Database changedmysql> SHOW TABLES;+------------------+| Tables_in_world  |+------------------+| city             || country          || countrylanguage  || provinces        || tdb_goods        || tdb_goods_brands || tdb_goods_cate   || tdb_goods_types  || test             || users            |+------------------+10 rows in set (0.00 sec)mysql> DELIMITER //mysql> CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)    -> BEGIN    -> DELETE FROM test WHERE id=p_id;    -> SELECT count(id) FROM test INTO userNums;    -> END    -> //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> SELECT COUNT(id) FROM test;+-----------+| COUNT(id) |+-----------+|         6 |+-----------+1 row in set (0.00 sec)mysql> #变量用@mysql> CALL removeUserAndReturnUserNums(4,@nums);Query OK, 1 row affected (0.04 sec)mysql> SELECT @nums;+-------+| @nums |+-------+|     5 |+-------+1 row in set (0.01 sec)mysql> #@是全局变量；mysql> #@是用户变量；mysql> SET @i=7;Query OK, 0 rows affected (0.00 sec)mysql>mysql> DESC test;+----------+---------------------+------+-----+---------+----------------+| Field    | Type                | Null | Key | Default | Extra          |+----------+---------------------+------+-----+---------+----------------+| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment || username | varchar(20)         | YES  |     | NULL    |                |+----------+---------------------+------+-----+---------+----------------+2 rows in set (0.00 sec)mysql> SELECT * FROM test;+----+----------+| id | username |+----+----------+|  1 | John     ||  3 | tom%     ||  5 | AA       ||  6 | BB       ||  7 | Rose     |+----+----------+5 rows in set (0.00 sec)mysql> #下面这个存储过程返回两个值；mysql> #得到行数；mysql> SELECT ROW_COUNT();+-------------+| ROW_COUNT() |+-------------+|          -1 |+-------------+1 row in set (0.00 sec)mysql> DESC test;+----------+---------------------+------+-----+---------+----------------+| Field    | Type                | Null | Key | Default | Extra          |+----------+---------------------+------+-----+---------+----------------+| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment || username | varchar(20)         | YES  |     | NULL    |                |+----------+---------------------+------+-----+---------+----------------+2 rows in set (0.00 sec)mysql> INSERT test(username) VALUES('A'),('B'),('C');Query OK, 3 rows affected (0.04 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> SELECT ROW_COUNT();+-------------+| ROW_COUNT() |+-------------+|           3 |+-------------+1 row in set (0.00 sec)

mysql> UPDATE test SET username=CONCAT(username,'--imooc') WHERE id>6;Query OK, 4 rows affected (0.05 sec)Rows matched: 4  Changed: 4  Warnings: 0mysql> SELECT ROW_COUNT();+-------------+| ROW_COUNT() |+-------------+|           4 |+-------------+1 row in set (0.00 sec)

mysql> DELIMITER //mysql> CREATE PROCEDURE removeUserByIdAndReturnInfos(    -> IN p_id SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)    -> BEGIN    -> DELETE FROM test WHERE id=p_id;    -> SELECT ROW_COUNT() INTO deleteUsers;    -> SELECT COUNT(id) FROM test INTO userCounts;    -> END    -> //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> SELECT * FROM test;+----+-------------+| id | username    |+----+-------------+|  1 | John        ||  3 | tom%        ||  5 | AA          ||  6 | BB          ||  7 | Rose--imooc ||  8 | A--imooc    ||  9 | B--imooc    || 10 | C--imooc    |+----+-------------+8 rows in set (0.00 sec)mysql> SELECT COUNT(id) FROM test WHERE username='%imooc';+-----------+| COUNT(id) |+-----------+|         0 |+-----------+1 row in set (0.00 sec)mysql> SELECT COUNT(id) FROM test WHERE username='%imooc%';+-----------+| COUNT(id) |+-----------+|         0 |+-----------+1 row in set (0.00 sec)mysql> SELECT COUNT(id) FROM test WHERE username='B%';+-----------+| COUNT(id) |+-----------+|         0 |+-----------+1 row in set (0.00 sec)mysql> CALL removeUserByIdAndReturnInfos(23,@a,@b);Query OK, 1 row affected (0.00 sec)mysql> CALL removeUserByIdAndReturnInfos(1,@a,@b);Query OK, 1 row affected (0.04 sec)mysql> SELECT @a,@b;+------+------+| @a   | @b   |+------+------+|    1 |    7 |+------+------+1 row in set (0.00 sec)mysql> SELECT * FROM test;+----+-------------+| id | username    |+----+-------------+|  3 | tom%        ||  5 | AA          ||  6 | BB          ||  7 | Rose--imooc ||  8 | A--imooc    ||  9 | B--imooc    || 10 | C--imooc    |+----+-------------+7 rows in set (0.00 sec)

0 0