mysql 中的存储过程
程序员文章站
2022-07-27 21:14:48
创建一个简单的存储过程 存储过程proc_adder功能很简单,两个整型输入参数a和b,一个整型输出参数sum,功能就是计算输入参数a和b的结果,赋值给输出参数sum; 几点说明: DELIMITER ;;:之前说过了,把默认的输入的结束符;替换成;;。 DEFINER:创建者; call : 调用 ......
创建一个简单的存储过程
存储过程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 ;
上一篇: jQuery之修改li下样式和图片