MYSQL存储过程（一）

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

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);