MYSQL---存储过程

不带参数的存储过程

CREATE PROCEDURE sp1()

SELECT VERSION();

调用存储过程：CALL sp1  //不带参数的存储过程在调用时，可以不加括号

DELIMITER //

带IN参数的存储过程

CREATE PROCEDURE removeUserById(IN uid INT UNSIGNED)

BEGIN

 DELETE FROM users WHERE id=uid;

END//

调用存储过程：CALL removeUserById(3);

删除存储过程

DROP PROCEDURE [IF EXISTS] removeUserById

带IN和OUT参数的存储过程

DELIMITER //

CREATE PROCEDURE removeUserAndReturnUserNums(IN uid INT UNSIGNED,OUT userNums INT UNSIGNED)

BEGIN

DELETE FROM users WHERE id=uid;

SELECT COUNT(id) FROM users INTO userNums;

END//

DELIMITER ;

调用：

CALL removeUserAndReturnUserNum(27,@nums);

SELECT @nums;

带有多个OUTO类型参数的存储过程

DELIMITER //

CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age INT UNSIGNED,OUT deleteUsers INT UNSIGNED,OUT userCount INT UNSIGNED )

BEGIN

DELETE FROM users WHERE age=p_age;

SELECT ROW_COUNT() INTO deleteUsers;

SELECT COUNT(id) FROM users INTO userCount;

END//

DELIMITER ;

CALL removeUserByAgeAndReturnInfos(20,@a,@b);

SELECT @a,@b;