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&#xFF0C; &#x6210;&#x7EE9;&#x9519;&#x8BEF;
    score < 60&#xFF1A;&#x4E0D;&#x53CA;&#x683C;
    score >= 60 and score < 80&#xFF0C;&#x53CA;&#x683C;
    score >= 80 and score < 90&#xFF0C;&#x826F;&#x597D;
    score >=90 and score <= 100，优秀 * delimiter $$ create procedure proc_if1 ( in score int ) begin if> 100 OR score < 0 THEN SELECT '&#x6210;&#x7EE9;&#x9519;&#x8BEF;';
    ELSEIF score < 60 THEN SELECT '&#x4E0D;&#x53CA;&#x683C;';
    ELSEIF score >= 60 AND score < 80 THEN SELECT '&#x53CA;&#x683C;';
    ELSEIF score >= 80 AND score < 90 THEN SELECT '&#x826F;&#x597D;';
    ELSE SELECT '&#x4F18;&#x79C0;';
    END IF;
END $$
DELIMITER ;

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

-- &#x4F8B;2&#xFF1A;&#x8F93;&#x5165;&#x5458;&#x5DE5;&#x540D;&#x5B57;&#xFF0C;&#x5224;&#x65AD;&#x5DE5;&#x8D44;&#x7684;&#x60C5;&#x51B5;
/*
    sal < 10000&#xFF0C;&#x8BD5;&#x7528;&#x85AA;&#x8D44;
    sal >= 10000 and sal < 20000&#xFF1A;&#x8F6C;&#x6B63;&#x85AA;&#x8D44;
    sal >= 20000&#xFF1A;&#x5143;&#x8001;&#x85AA;&#x8D44;
*/
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 '&#x8BD5;&#x7528;&#x85AA;&#x8D44;';
    ELSEIF empsal >= 10000 AND empsal < 20000 THEN SELECT '&#x8F6C;&#x6B63;&#x85AA;&#x8D44;';
    ELSE SELECT '&#x5143;&#x8001;&#x85AA;&#x8D44;';
    END IF;
END $$
DELIMITER ;

SET @ename = '&#x5173;&#x7FBD;';
CALL proc_if2 ( @ename );
CALL proc_if2 ( '&#x7518;&#x5B81;' );</=>

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; $$ delimiter; call proc_while(10)< code></=insertcount>

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 while $$ delimiter; call proc_while(10)< code></=insertcount>

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)

Original: https://blog.csdn.net/m0_63115132/article/details/124150431
Author: m0_63115132
Title: MySQL的存储过程——流程控制