分支结构
0.准备工作
-
drop database if exists new; create database new; create table new.employees as select * from northwind.employees; show databases; show tables from new; select * from new.employees;
1. if分支语句
-
用于实现简单的条件判断。
-
基本语法：
if 条件表达式 then 执行操作; elseif 条件表达式 then 执行操作; else 执行操作; end if; -
声明存储过程
raise_salary1，输入员工编号。如果员工薪资低于 8000 元且工龄超过 5 年，涨薪 500 元；否则不变。drop procedure if exists new.raise_salary1; delimiter $ create procedure new.raise_salary1(in emp_id int) begin declare emp_sal double; # 员工工资 declare hire_year double; # 入职时间（年） select salary, datediff(curdate(), hire_date)/365 into emp_sal, hire_year from new.employees where(employee_id = emp_id); if emp_sal < 8000 and hire_date >= 5 then update new.employees set salary = salary + 500 where employee_id = emp_id; end if; end $ delimiter ; -
声明存储过程
raise_salary2，输入员工编号。如果员工薪资低于 9000 元且工龄超过 5 年，涨薪 500 元；否则涨薪 100 元。
delimiter $
create procedure new.raise_salary2(in emp_id int)
begin
declare emp_sal double; # 员工工资
declare hire_year double; # 入职年份
declare new_salary double; # 新工资
select salary, datediff(curdate(), hire_date)/365
into emp_sal, hire_year
from new.employees
where(employee_id = emp_id);
if emp_sal < 9000 and hire_year >= 5 then
set new_salary = emp_sal + 500;
else
set new_salary = emp_sal + 100;
end if;
update new.employees
set salary = new_salary
where employee_id = emp_id;
end $
delimiter ;
2.case分支语句
-
据不同的条件自动选择执行的逻辑分支。
-
基本语法
case when 条件表达式（值） then 操作语句 when 条件表达式（值） then 操作语句 …… else 操作语句 end case; -
声明存储过程
raise_salary3，输入员工编号。如果员工薪资低于 9000 元，则涨薪到 9000，薪资大于 9000 且低于 10000，并且没有提成的员工，涨薪 500 元；其他涨薪 100 元。delimiter $ create procedure new.raise_salary3(in emp_id int) begin declare emp_sal double; declare bouns double; declare new_salary double; select salary, commission_pct into emp_sal, bouns from new.employees where employee_id = emp_id; case when emp_sal < 9000 then set new_salary = 9000; when emp_sal < 10000 and bonus is null then set new_salary = emp_sal + 500; else set new_salary = emp_sal + 100; end case; update new.employees set salary = new_salary where employee_id = emp_id; end $ delimiter ; -
声明存储过程
raise_salary4，输入员工编号。如果员工工龄是 0 年，涨薪 50；如果工龄是 1 年，涨薪 100；如果工龄是 2 年，涨薪 200；如果工龄是 3 年，涨薪 300；如果工龄是 4 年，涨薪 400；其他的涨薪 500。delimiter $ create procedure new.raise_salary4(emp_id int) begin declare emp_sal double; # 员工薪资 declare hire_year int; # 员工入职年头 declare new_salary double; select salary, floor(datediff(curdate(), hire_date)/365) into emp_sal, hire_year from new.employees where employee_id = emp_id; case hire_year when 0 then set new_salary = emp_sal + 50; when 1 then set new_salary = emp_sal + 100; when 2 then set new_salary = emp_sal + 200; when 3 then set new_salary = emp_sal + 300; when 4 then set new_salary = emp_sal + 400; else set new_salary = emp_sal + 500; end case; update new.employees set salary = new_salary where employee_id = emp_id; end $ delimiter ;
循环结构
1. loop循环结构
-
使用
loop循环结构来实现重复执行一段代码的功能。 -
可以使用
leave语句来退出循环。 -
基本语法
label: LOOP # 开始循环 -- 代码块 if condition then leave label; # 提前退出循环 end if; -- 继续执行的代码 end label; # 结束循环 -
计算n的阶乘（n! = 1 * 2 * 3 * …… * n）
drop function if exists new.factorial; delimiter $ create function new.factorial(n int) # 函数不需要标参数in/out returns int begin declare fct, i int default 1; fun: loop set fct = fct * i; set i = i + 1; if i > n then leave fun; end if; end loop fun; return fct; end $ delimiter ; -
声明存储过程
raise_salary6，给全体员工涨薪，每次涨幅为 10%，直到全公司的平均薪资达到 12000 为止，返回上涨次数。drop procedure if exists new.raise_salary5; delimiter $ create procedure new.raise_salary6(out num int) begin declare avg_sal double; declare n int default 0; loop_lab: loop select avg(salary) into avg_sal from cs.employees; if avg_sal >= 12000 then leave loop_lab; end if; update new.employees set salary = salary * 1.1; set n = n+1; end loop loop_lab; set num = n; end $ delimiter ;
2. while循环结构
-
用于多次执行一段代码，直到满足指定的条件为止。
-
基本语法
while condition do # 执行的代码块 end while; -
condition是一个布尔表达式，表示循环要继续执行的条件。只要condition为真（即非零），循环就会一直执行。 -
生成九九乘法表
drop procedure if exists new.mult_table; delimiter $ create procedure new.mult_table() begin declare x, i int default 1; declare expr varchar(10) default ''; declare result varchar(2048) default '\n'; while i <= 9 do set x = 1; while x <= i do set expr = rpad(concat(x, '×', i, '=', x * i), 6, ' '); set result = concat(result, ' ', expr); set x = x + 1; end while; set result = concat(result, '\n'); set i = i + 1; end while; select result; end $ delimiter ; call new.mult_table()\G -
# 声明存储过程
raise_salary6，给全体员工涨薪，每次涨幅为 10%，直到全公司的平均薪资达到 12000 为止，返回上涨次数。drop procedure if exists new.raise_salary6; delimiter $ create procedure new.raise_salary6(out num int) begin declare avg_sal double; declare n int default 0; select avg(salary) into avg_sal from new.employees; while avg_sal < 12000 do update cs.employees set salary = salary * 1.1; set n = n + 1; select avg(salary) into avg_sal from new.employees; end while; set num = n; end $ delimiter ;
3. repeat循环结构
-
与 WHILE 循环不同，REPEAT 循环在执行代码块之前首先执行一次，然后检查条件是否为真。
-
基本语法
repeat -- 执行的代码块 until condition end repeat; -
计算n的阶乘（n! = 1 * 2 * 3 * …… * n）
drop function if exists new.factorial; delimiter $ create function new.factorial(n int) returns int begin declare fct, i int default 1; repeat set fct = fct * i; set i = i + 1; until i>n end repeat; return fct; end $ delimiter ;
补充：存储函数与存储过程的区别
-
返回值类型：
- 存储函数可以返回一个单一的值作为结果，例如整数、字符串、日期等。可以像使用内置函数一样直接在 SQL 查询中使用函数。
- 存储过程没有返回值或者说返回值不被直接使用。
-
用法：
- 存储函数可以在 SQL 语句中直接调用，并且函数的返回值可以用于计算、过滤结果集等。
- 存储过程需要使用
CALL语句来调用。
-
执行方式：
- 存储函数在调用时会直接返回结果，可以嵌套使用，并且可以通过定义参数来接收输入值。
- 存储过程在调用时需要显式使用
CALL语句，其执行过程中可以包含多个语句，可以执行一系列操作并且可以包含输入/输出参数。
-
数据库操作：
- 存储函数可以对数据库进行读取操作（SELECT），但是不能对数据库进行写入操作（INSERT、UPDATE、DELETE）。存储函数中不允许修改数据库状态。
- 存储过程可以对数据库进行读取和写入操作，可以修改数据库状态，并且允许使用事务控制。
别哭前面一定有路，仿佛幸福在不远处。 —— 康姆士《你要如何 我们就如何》
599
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
