MySQL的存储过程——流程控制

--数据准备

CREATE DATABASE IF NOT EXISTS mydb_proc;

USE mydb_proc;

#部门表

CREATE TABLE IF NOT EXISTS dept(
	deptno INT PRIMARY KEY,
  dname VARCHAR(20),
	loc VARCHAR(20)
);
INSERT INTO dept VALUES
(10, '教研部','北京'),
(20, '学工部','上海'),
(30, '销售部','广州'),
(40, '财务部','武汉');

#员工表

CREATE TABLE IF NOT EXISTS emp(
	empno INT PRIMARY KEY,
	ename VARCHAR(20),
	job VARCHAR(20),
	mgr INT,
	hiredate DATE,
	sal NUMERIC(8,2),
	comm NUMERIC(8, 2),
	deptno INT,
-- 	FOREIGN KEY (mgr) REFERENCES emp(empno),
	FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO emp VALUES
(1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, NULL, 20),
(1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30),
(1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30),
(1004, '刘备', '经理', 1009, '2001-4-02', 29750.00, NULL, 20),
(1005, '谢逊', '销售员', 1006, '2001-9-28', 12500.00, 14000.00, 30),
(1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, NULL, 30),
(1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, NULL, 10),
(1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, NULL, 20),
(1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000.00, NULL, 10),
(1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30),
(1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, NULL, 20),
(1012, '程普', '文员', 1006, '2001-12-03', 9500.00, NULL, 30),
(1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, NULL, 20),
(1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, NULL, 10);

#员工工资等级表

CREATE TABLE IF NOT EXISTS salgrade(
	grade INT PRIMARY KEY,
	losal INT,
	hisal INT
);
INSERT INTO salgrade VALUES
(1, 7000, 12000),
(2, 12010, 14000),
(3, 14010, 20000),
(4, 20010, 30000),
(5, 30010, 99990);

1、流程控制（判断）if
if search_condition_1

        then statement_list_1
[elseif search_condition_2

        then statement_list_2]
[else statement_list_n]        

end if

-- 流程控制语句-if
-- 例1：输入学生的成绩，来判断成绩的级别。
/*
	score > 100 or score < 0， 成绩错误
	score < 60：不及格
	score >= 60 and score < 80，及格
	score >= 80 and score < 90，良好
	score >=90 and score <= 100，优秀
*/
DELIMITER $$
CREATE PROCEDURE proc_if1 ( IN score INT )
BEGIN
	IF score > 100 OR score < 0 THEN SELECT '成绩错误';
	ELSEIF score < 60 THEN SELECT '不及格'; 
	ELSEIF score >= 60 AND score < 80 THEN SELECT '及格'; 
	ELSEIF score >= 80 AND score < 90 THEN SELECT '良好';
	ELSE SELECT '优秀';
	END IF;
END $$
DELIMITER ;

SET @score = 80;
CALL proc_if1(@score);

-- 例2：输入员工名字，判断工资的情况
/*
	sal < 10000，试用薪资
	sal >= 10000 and sal < 20000：转正薪资
	sal >= 20000：元老薪资
*/
DELIMITER $$
CREATE PROCEDURE proc_if2 (IN ename VARCHAR ( 20 ))
BEGIN
	DECLARE empsal DECIMAL ( 8, 2 );
	SELECT sal INTO empsal FROM emp WHERE emp.ename = ename;
	IF empsal < 10000 THEN SELECT '试用薪资';
	ELSEIF empsal >= 10000 AND empsal < 20000 THEN SELECT '转正薪资';
	ELSE SELECT '元老薪资';
	END IF;
END $$
DELIMITER ;

SET @ename = '关羽';
CALL proc_if2 ( @ename );
CALL proc_if2 ( '甘宁' );

2、控制判断—case when 
case是另一个条件判断的语句，类似于编程语言中的Switch语法    

/*
	支付方式：
		1：微信支付
		2：支付宝支付
		3：银行卡支付
		4：其他方式支付
*/
--例1
DELIMITER $$
CREATE PROCEDURE proc_case1(IN pay_type INT)
BEGIN
CASE pay_type
WHEN 1 THEN SELECT '微信支付';
WHEN 2 THEN SELECT '支付宝支付';
WHEN 3 THEN SELECT '银行卡支付';
ELSE SELECT '其他支付方式';
END CASE;
END $$
DELIMITER;
CALL proc_case1(2)

--例2
DELIMITER $$
CREATE PROCEDURE proc_case2(IN pay_type INT)
BEGIN
CASE  
WHEN pay_type=1 THEN SELECT '微信支付';
WHEN pay_type=2 THEN SELECT '支付宝支付';
WHEN pay_type=3 THEN SELECT '银行卡支付';
ELSE SELECT '其他支付方式';
END CASE;
END $$
DELIMITER;
CALL proc_case2(2)

3、流程控制—循环
循环是一段在程序中只出现一次，但可能会连续运行多次的代码。
循环中的代码会运行特定的次数，或者是运行到特定条件成立时结束循环。
循环分类：while  repeat  loop
3-1 while 的使用、语法如下：
leave 类似 break，跳出，结束当前所在的循环
iterate 类似于 continue，继续，结束本次循环，继续下一次
存储过程 while

CREATE TABLE IF NOT EXISTS user(
uid INT PRIMARY key,
username VARCHAR(10),
password varchar(50)
);
delimiter $$
create procedure proc_while(in insertcount int)
begin
declare i int default 1;
label:while i<=insertcount do
insert into user(uid,username,password)values(i,concat('user-',i),'123456');
set i=i+1;
end while label;
end $$
delimiter;
call proc_while(10)

leave 的使用（leave 类似 break，跳出，结束当前所在的循环）leave，iterate语法相同
iterate 类似于 continue，继续，结束本次循环，继续下一次
 

delimiter $$
create procedure proc_while(in insertcount int)
begin
declare i int default 1;
label:while i<=insertcount do
insert into user(uid,username,password)values(i,concat('user-',i),'123456');
if i=5 then leave label;
end if;
set i=i+1;
end while label;
end $$
delimiter;
call proc_while(10)

3-2 repeat 的使用、语法如下：（最少执行一次）
 

delimiter $$
create procedure proc_repeat(in insertcount int)
begin
declare i int default 1;
label:repeat 
insert into user(uid,username,password)values(i,concat('user-',i),'123456');
set i=i+1;
until i>=insertcount 
end repeat label;
select '循环失败';
end $$
delimiter;
call proc_repeat(10)

3-3 loop 的使用、语法如下：
 

delimiter $$
create procedure proc_loop(in insertcount int)
begin
declare i int default 1;
label:loop
insert into user(uid,username,password)values(i,concat('user-',i),'123456');
set i=i+1;
if i>insertcount then leave label;
end if;
end loop label;
select '循环失败';
end $$
delimiter;
call proc_loop(10)