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