版权声明：本文为博主原创文章，转载请注明出处。 https://blog.csdn.net/twilight_karl/article/details/74942272
创建存储过程
CREATE PROCEDURE sp_name ([proc_parameter[,…]])
routine_body
proc_parameter:
- [IN|OUT|INOUT] param_name type
参数
- IN，表示给参数的值必须在调用存储过程时指定
- OUT，表示该参数的额值可以被存储过程改变，并可以返回
- INOUT，表示该参数的调用时指定，并且可以被改变和返回
调用存储过程
- CALL sp_name([parameter[,…]])
- CALL sp_name[()]
无参过程的调用可以不写小括号
# 创建无参数的储存过程
mysql> CREATE PROCEDURE sp1() SELECT VERSION();
# 调用无参数的储存过程可以不加括号
mysql> CALL sp1;
mysql> CALL sp1();
+------------+
| VERSION() |
+------------+
| 5.7.18-log |
+------------+# 创建IN类型参数
mysql> CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
-> BEGIN
-> DELETE FROM user WHERE id = p_id ;
-> END
-> //
mysql> CALL removeUserById(9)//删除储存过程
DROP PROCEDURE [IF EXISTS] sp_name
示例
// 删除指定的记录，并返回剩余记录的数量
mysql> CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
-> BEGIN
-> DELETE FROM user WHERE id = p_id;
-> SELECT COUNT(id) FROM user INTO userNums;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)- ROW_COUNT(); 被增删改更改的行数
# 使用ROW_COUNT() 获得被更改的行数
mysql> DELIMITER //
mysql> CREATE PROCEDURE removeUserByUserName(IN user_name VARCHAR(20),OUT deleteCount INT,OUT restCount INT)
-> BEGIN
-> DELETE FROM user WHERE username = user_name;
-> SELECT ROW_COUNT() INTO deleteCount;
-> SELECT COUNT(id) FROM user INTO restCount;
-> END
-> //
mysql> SELECT @a,@b;
+------+------+
| @a | @b |
+------+------+
| 3 | 4 |
+------+------+
