第一种情况:不带参数的存储过程:
DELIMITER $$
USE `city`$$
DROP PROCEDURE IF EXISTS `PROCEDURE_ONE`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `PROCEDURE_ONE`()
BEGIN
SELECT * FROM city;
END$$
DELIMITER ;
//方法调用
CALL PROCEDURE_ONE();
第二种:带输入参数的存储过程:
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
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `city`.`PROCEDURE_FOUR`(INOUT sp INT)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
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);