MYSQL存储过程&循环&游标&IF$CASE

测试用表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for stu
-- ----------------------------
DROP TABLE IF EXISTS `stu`;
CREATE TABLE `stu` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`kecheng` varchar(255) DEFAULT NULL,
`fenshu` int(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of stu
-- ----------------------------
INSERT INTO `stu` VALUES ('1', '张三', '语文', '40');
INSERT INTO `stu` VALUES ('2', '张三', '数学', '60');
INSERT INTO `stu` VALUES ('3', '李四', '数学', '110');
INSERT INTO `stu` VALUES ('4', '李四', '语文', '40');
INSERT INTO `stu` VALUES ('7', '你本来就很二', '德语', '110');
INSERT INTO `stu` VALUES ('8', '王五', '数学', null);

-- 多个out参数的存储过程

DROP PROCEDURE IF EXISTS moreOutPro;

CREATE PROCEDURE moreOutPro(OUT outParam1 INT, OUT outParam2 INT)

BEGIN
SELECT id, fenshu INTO outParam1, outParam2 FROM stu LIMIT 1;
END;


-- CALL moreOutPro(@outParam1, @outParam2);
-- SELECT @outParam1, @outParam2;

-- 显示存储过程源代码
-- SHOW CREATE PROCEDURE inPro;

-- while循环

DROP PROCEDURE IF EXISTS while_loop;

CREATE PROCEDURE while_loop(OUT outParam VARCHAR(30))
BEGIN
DECLARE str VARCHAR(30);
    DECLARE i INT;

SET str = '';
SET i = 0;

WHILE i < 10
DO
SET i = i + 1;
SET str = CONCAT(str,i,' ');
END WHILE;

SELECT str;
END;

-- CALL while_loop(@outParam);
-- SELECT @outParam;

-- REPEAT循环

DROP PROCEDURE IF EXISTS repeat_loop;

CREATE PROCEDURE repeat_loop(OUT outParam VARCHAR(30))
BEGIN
DECLARE str VARCHAR(30);
    DECLARE i INT;

SET str = '';
SET i = 0;

REPEAT
SET str = CONCAT(str,i,' ');
SET i = i + 1;
UNTIL i > 10
END REPEAT;

SELECT str;
END;

CALL repeat_loop(@outParam);
SELECT @outParam;

-- loop循环

DROP PROCEDURE IF EXISTS loop_test;

CREATE PROCEDURE loop_test(OUT outParam VARCHAR(30))

BEGIN
DECLARE i INT;
    DECLARE str VARCHAR(30);

SET i = 0;
SET str = '';

    loop_label: LOOP
IF i > 10 THEN
            LEAVE loop_label;       -- LEAVE语句允许您立即退出循环，而不用等待检查条件。相当于java/c/c++中的break
END IF;

SET i = i + 1;
IF (i MOD 2) THEN           -- MOD是模运算,即求余运算,例如 1 MOD 2 = 1, 4 MOD 2 = 0, mysql中1代表true, 0代表false
        ITERATE                             -- ITERATION语句允许您跳过剩下的那些代码，并重新开始新的迭代.相当于java/c/c++中的continue
            loop_label;
ELSE
SET str = CONCAT(str,i,' ');
END IF;
END LOOP;

SELECT str;

END;

-- 
-- CALL loop_test(@outParam);
-- SELECT @outParam;

-- 游标

DROP PROCEDURE IF EXISTS corPro;
CREATE PROCEDURE corPro()

BEGIN

DECLARE finished INT DEFAULT 0;

    DECLARE param int;

    DECLARE cor CURSOR FOR SELECT id FROM stu;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN cor;

        loop_label: LOOP

            FETCH cor INTO param;

IF finished = 1 THEN
                LEAVE loop_label;
END IF;

            UPDATE stu SET fenshu = fenshu * 2 WHERE id = param;

END LOOP loop_label;

        CLOSE cor;

END;

-- CALL corPro();

-- IFNULL(expr1,expr2)
SELECT id, `name`, IFNULL(fenshu,100) FROM stu;

-- IF(expr1,expr2,expr3)
SELECT id, `name`, IF(fenshu > 80,'优秀','不行啊') FROM stu;

-- IF ELSE

DROP PROCEDURE IF EXISTS ifElsePro;

CREATE PROCEDURE ifElsePro(OUT outParam1 INT, OUT outParam VARCHAR(20))
BEGIN
SELECT MAX(fenshu) INTO outParam1 FROM stu;

IF (outParam1 > 90) THEN SET outParam = '优秀';
    ELSEIF (outParam1 > 60) THEN SET outParam = '及格';
ELSE SET outParam = '不及格';
END IF;

END;

-- 
-- CALL ifElsePro(@score, @lev);
-- SELECT @score, @lev;

SELECT `name`, kecheng, 
CASE 
WHEN fenshu > 90 THEN '优秀'
WHEN fenshu > 60 THEN '及格'
ELSE '不及格'
END fenshu
FROM stu;