创建一个简单的存储过程

存储过程proc_adder功能很简单，两个整型输入参数a和b，一个整型输出参数sum，功能就是计算输入参数a和b的结果，赋值给输出参数sum；

几点说明：

DELIMITER ;;：之前说过了，把默认的输入的结束符;替换成;;。

DEFINER：创建者；

call  : 调用存储过程，用 call 命令

-- ------------------------------ Procedure structure for `proc_adder`-- ----------------------------DROP PROCEDURE IF EXISTS `proc_adder`;DELIMITER ;;CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)BEGIN    #Routine body goes here...    DECLARE c int;    if a is null then set a = 0;     end if;      if b is null then set b = 0;    end if;    set sum  = a + b;END;;DELIMITER ;

　　执行以上存储结果，验证是否正确，如下图，结果OK：

set @b=5;call proc_adder(2,@b,@s);select @s as sum;

存储过程中的控制语句

IF语句：

-- ------------------------------ Procedure structure for `proc_if`-- ----------------------------DROP PROCEDURE IF EXISTS `proc_if`;DELIMITER ;;CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_if`(IN type int)BEGIN    #Routine body goes here...    DECLARE c varchar(500);    IF type = 0 THEN        set c = 'param is 0';    ELSEIF type = 1 THEN        set c = 'param is 1';    ELSE        set c = 'param is others, not 0 or 1';    END IF;    select c;END;;DELIMITER ;

CASE语句：

-- ------------------------------ Procedure structure for `proc_case`-- ----------------------------DROP PROCEDURE IF EXISTS `proc_case`;DELIMITER ;;CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_case`(IN type int)BEGIN    #Routine body goes here...    DECLARE c varchar(500);    CASE type    WHEN 0 THEN        set c = 'param is 0';    WHEN 1 THEN        set c = 'param is 1';    ELSE        set c = 'param is others, not 0 or 1';    END CASE;    select c;END;;DELIMITER ;

循环while语句：

-- ------------------------------ Procedure structure for `proc_while`-- ----------------------------DROP PROCEDURE IF EXISTS `proc_while`;DELIMITER ;;CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_while`(IN n int)BEGIN    #Routine body goes here...    DECLARE i int;    DECLARE s int;    SET i = 0;    SET s = 0;    WHILE i <= n DO        set s = s + i;        set i = i + 1;    END WHILE;    SELECT s;END;;DELIMITER ;