MYSQL存储过程

第一种情况：不带参数的存储过程：

DELIMITER $$

USE `city`$$
DROP PROCEDURE IF EXISTS `PROCEDURE_ONE`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `PROCEDURE_ONE`()
BEGIN
SELECT * FROM city;
END$$
DELIMITER ;

第二种：带输入参数的存储过程：

DELIMITER $$
USE `city`$$
DROP PROCEDURE IF EXISTS `PROCEDURE_TWO`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `PROCEDURE_TWO`(IN id INT)
BEGIN
SET @id=id;
SELECT * FROM city WHERE city.`id`=@id;
END$$
DELIMITER ;

CALL PROCEDURE_TWO(12345);

第三种：带输入输出参数的存储过程

DELIMITER $$
USE `city`$$
DROP PROCEDURE IF EXISTS `PROCEDURE_THREE`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `PROCEDURE_THREE`(IN id INT,OUT truthname VARCHAR(30))
BEGIN
SET @id=id;
SELECT NAME INTO truthname FROM city WHERE id=@id LIMIT 1;
END$$
DELIMITER ;

CALL PROCEDURE_THREE(12345,@a);
SELECT @a;

第四种:既做输入又做输出参数的存储过程

DELIMITER $$
CREATE

PROCEDURE `city`.`PROCEDURE_FOUR`(INOUT sp INT)

BEGIN
IF sp=12345 THEN
SET @sps=1;
ELSE
SET @sps=2;
END IF;
SELECT @sps;
END$$
DELIMITER ;

CALL PROCEDURE_FOUR(@pp);
SET @pp=12345;
CALL PROCEDURE_FOUR(@pp);