循环语句

– case when 语句
– 判断条件范围
SELECT c.course_name,c.description,
CASE
WHEN course_id<3 THEN ‘A’
ELSE ‘B’
END ‘total’
FROM course c;

– 判断具体值
SELECT c.course_name,c.description,
CASE c.course_name
WHEN ‘MYSQL’ THEN ‘数据库’
WHEN ‘Oracle’ THEN ‘数据库’
ELSE ‘编程语言’
END ‘课程类别’
FROM course c;

if语句 在流程控制中的使用
DELIMITER $$

CREATE
/[DEFINER = { user | CURRENT_USER }]/
PROCEDURE test.x_test()
/LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’/
BEGIN
DECLARE X INT DEFAULT 10;
IF X<10 THEN
SELECT ‘1’;
ELSEIF X=10 THEN
SELECT ‘2’;
ELSE
SELECT ‘3’;
END IF;
END$$

DELIMITER ;

语句:CALL 存储过程名();

while 语句
DELIMITER $$

CREATE
/[DEFINER = { user | CURRENT_USER }]/
PROCEDURE test.sp_user()
/LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’/
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE sum_ INT DEFAULT 0;
WHILE i<10 DO
SET sum_=sum_+i;
SET i=i+1;
END WHILE;
SELECT sum_;
END$$

DELIMITER ;

CALL 储存过程名();

– repeat语句
DELIMITER $$

CREATE
/[DEFINER = { user | CURRENT_USER }]/
PROCEDURE test.sp_repeat()
/LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’/
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE sum_ INT DEFAULT 0;
REPEAT
SET sum_=sum_+i;
SET i=i+1;
UNTIL i>=10 END REPEAT;
SELECT sum_;
END$$

DELIMITER ;

CALL sp_repeat();

– loop循环
DELIMITER $$

CREATE
/[DEFINER = { user | CURRENT_USER }]/
PROCEDURE test.sp_loop()
/LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’/
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE sum_ INT DEFAULT 0;
myloop:LOOP
SET sum_=sum_+i;
SET i=i+1;
IF i>=10
THEN LEAVE myloop;
END IF;
END LOOP myloop;
SELECT sum_;

END$$

DELIMITER ;

CALL sp_loop();

– 查看数据库的存储过程
SHOW PROCEDURE STATUS WHERE db=‘test’;
SELECT specific_name FROM mysql.proc WHERE db=‘test’;