图片 10

mysql 中的存储过程

存储过程中的控制语句

IF语句:

图片 1

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

图片 2

图片 3

CASE语句:

图片 4

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

图片 5

图片 6

循环while语句:

图片 7

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

图片 8

图片 9

4 存储过程中的控制语句

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 ;

执行以上存储结果,验证是否正确:

set @type=1;
call proc_if(@type);

如果结果 c = param is 1;
ok

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 ;

执行以上存储结果,验证是否正确:

set @type=1;
call proc_case(@type);

如果结果 c = param is 1;
ok

循环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 ;

执行以上存储结果,验证是否正确:

set @type=100;
call proc_while(@type);

如果结果 s = 5050;
ok

创建一个简单的存储过程

存储过程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;

图片 10

5 存储过程弊端

  • 不同数据库,语法差别很大,移植困难,换了数据库,需要重新编写;
  • 不好管理,把过多业务逻辑写在存储过程不好维护,不利于分层管理,容易混乱,一般存储过程适用于个别对性能要求较高的业务,其它的必要性不是很大。

3 创建一个简单的存储过程

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

几点说明

  • DELIMITER ;; :之前说过了,把默认的输入的结束符;替换成;;。
  • DEFINER:创建者;

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

执行以上存储结果,验证是否正确:

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

如果结果 sum = 7;
ok