mysql存储过程

循环用法

• 游标方式循环：
  适用场景：从A表中查出来的记录，插入到B表中。

CREATE DEFINER=`root`@`%` PROCEDURE `sunpy_procedure`()
BEGIN
    DECLARE s INT DEFAULT 0;
    DECLARE teacher_id VARCHAR(32) DEFAULT "";
    DECLARE teacher_name VARCHAR(255) DEFAULT "";
    将查询的记录赋值给游标
    DECLARE report CURSOR FOR SELECT id, username from edu_user where role_id = 3;
    # 游标循环结束的标志位s为1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
    # 打开游标
    OPEN report;
        # 将第一次的游标，赋值给指定变量
        fetch report into teacher_id, teacher_name;
        # 循环不结束标志
        WHILE s <> 1 DO
            # 判断不存在相同值
            IF (SELECT COUNT(*) FROM edu_teacher WHERE id = teacher_id) = 0 THEN
                INSERT INTO edu_teacher(id, teacher_name) VALUES(teacher_id, teacher_name);
            END IF;
            # 取出新的游标赋值给指定变量
            fetch report into teacher_id, teacher_name;
        END WHILE;
    CLOSE report;
END

• case when else代替IF语句

CREATE DEFINER=`root`@`%` PROCEDURE `sunpy_procedure`()
BEGIN
    DECLARE s INT DEFAULT 0;
    DECLARE teacher_id VARCHAR(32) DEFAULT "";
    DECLARE teacher_name VARCHAR(255) DEFAULT "";
        
    DECLARE report CURSOR FOR SELECT id, username from edu_user where role_id = 3;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
    OPEN report;
        # 第一次
        fetch report into teacher_id, teacher_name;
        WHILE s <> 1 DO
            CASE (SELECT COUNT(*) FROM edu_teacher WHERE id = teacher_id)
                WHEN 0 THEN
                    INSERT INTO edu_teacher(id, teacher_name) VALUES(teacher_id, teacher_name);
                ELSE
                    SELECT "记录已重复";
            END CASE;
            
            fetch report into teacher_id, teacher_name;
    END WHILE;
    CLOSE report;
END

换种写法：

CREATE DEFINER=`root`@`%` PROCEDURE `sunpy_procedure`()
BEGIN
    DECLARE s INT DEFAULT 0;
    DECLARE teacher_id VARCHAR(32) DEFAULT "";
    DECLARE teacher_name VARCHAR(255) DEFAULT "";
    DECLARE record_num INT DEFAULT 0;
    DECLARE report CURSOR FOR SELECT id, username from edu_user where role_id = 3;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
    OPEN report;
        fetch report into teacher_id, teacher_name;
        WHILE s <> 1 DO
            SELECT COUNT(id) INTO record_num FROM edu_teacher WHERE id = teacher_id;
            CASE WHEN record_num = 0 THEN
                    INSERT INTO edu_teacher(id, teacher_name) VALUES(teacher_id, teacher_name);
                ELSE
                    SELECT "记录已重复";
            END CASE;
            
            fetch report into teacher_id, teacher_name;
    END WHILE;
    CLOSE report;
END

• 普通循环：
  适用场景：数值叠加

CREATE DEFINER=`root`@`%` PROCEDURE `sunpy_procedure`(IN n INT)
BEGIN
    DECLARE total INT DEFAULT 0;
    WHILE n > 0 DO
        SET total:= total + n;
        SET n:=n-1;
    END WHILE;
    SELECT total;
END

repeat写法：

CREATE DEFINER=`root`@`%` PROCEDURE `sunpy_procedure`(IN n INT)
BEGIN
    DECLARE total INT DEFAULT 0;
    REPEAT
        set total := total + n;
    set n := n-1;
    UNTIL n <= 0 END REPEAT;
    select total;
END

LOOP写法：

CREATE DEFINER=`root`@`%` PROCEDURE `sunpy_procedure`(IN n INT)
BEGIN
    DECLARE total INT DEFAULT 0;
    flag: LOOP
      IF n<=0 THEN
     LEAVE flag;
    END IF;

        set total := total + n;
    set n := n -1;

    END LOOP flag;
    
  select total;
END