创建存储过程

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 | +------+------+