#变量、流程控制与游标
#变量
#变量：系统变量(全局系统变量、会话系统变量) vs 用户自定义变量
#查看系统变量
#查看全局系统变量
SHOW GLOBAL VARIABLES;#624
#查询会话系统变量
SHOW SESSION VARIABLES;#647
SHOW VARIABLES;#默认查询的是会话系统变量
#查询部分系统变量
SHOW GLOBAL VARIABLES LIKE 'admin_%';
SHOW SESSION VARIABLES LIKE'character_%';
SHOW VARIABLES LIKE'character_%';
#查看指定系统变量
SELECT @@global.max_connections;#151
#错误：Variable 'max_connections' is a GLOBAL variable 是全局系统变量
SELECT @@session.max_connections;
#错误：Variable 'pseudo_thread_id' is a SESSION variable 是会话系统变量
SELECT @@global.pseudo_thread_id;
SELECT @@session.character_set_client;
SELECT @@global.character_set_client;
SELECT @@session.pseudo_thread_id;
SELECT @@character_set_client;#先查询会话系统变量，再查询全局系统变量
#修改系统变量的值
/*
有些时候，数据库管理员需要修改系统变量的默认值，以便修改当前会话或者MySQL服务实例的属性、
特征。具体方法：
方式1：修改MySQL 配置文件 ，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）重启服务器成本太高（不推荐）
方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值
*/
#全局系统变量
#SET方式一：
SET @@global.max_connections = 161;
#SET方式二：
SET GLOBAL max_connections = 171;
#针对于当前的数据库实例是有效的，一旦重启MySQL服务，就失效了
#会话系统变量
#方式一：
SET @@session.character_set_client = 'gbk';
#方式二:
SET SESSION character_set_client ='utf8';
#针对于当前会话是有效的，一旦结束会话重写建立起新的会话，则失效
#用户变量
/*
① 用户变量 ： 会话用户变量 vs 局部变量
② 会话用户变量：使用"@"开头，作用域为当前会话。
③ 局部变量：只能使用在存储过程和存储函数中的。
*/
#会话用户变量
#变量的声明和赋值
#方式1：“=”或“:=”
/*
SET @用户变量 = 值;
SET @用户变量 := 值;
*/
#方式2：“:=” 或 INTO关键字
/*
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];
查看 select @变量名
*/
#准备
CREATE DATABASE dbtest16;
USE dbtest16;
CREATE TABLE employees
AS
SELECT * FROM atguigudb.employees;
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;
SELECT * FROM employees;
SELECT * FROM departments;
#测试：
#方式一：
SET @m1 = 1;
SET @m2 := 2;
SET @sum :=@m1+@m2;
SELECT @sum;
#方式二：
SELECT @count:=COUNT(*) FROM employees;
SELECT @count;
SELECT AVG(salary) INTO @avg_sal FROM employees;
SELECT @avg_sal;
#局部变量
/*
局部变量必须
①使用DECLARE声明
②必须声明并使用再 BEGIN ... END 中(使用在存储过程、存储函数中)
③DECLARE声明的局部变量必须声明在BEGIN首行的位置。
声明格式
DECLARE 变量名 类型 [default 值]; #如果没有DEFAULT子句，初始值为NULL
赋值
方式1：一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
方式2：一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;
使用
SELECT 局部变量名;
*/
#举例：
DELIMITER //
CREATE PROCEDURE test_var()
BEGIN
#1.声明局部变量
DECLARE a INT DEFAULT 0;
DECLARE b INT ;
#DECLARE a,b INT DEFAULT 0;
DECLARE emp_name VARCHAR(25);
#2.赋值
SET a=1;
SET b:=2;
SELECT last_name INTO emp_name FROM employees WHERE employee_id = 101;
#3.使用
SELECT a,b,emp_name;
END//
DELIMITER ;
DROP PROCEDURE test_var;
#调用存储过程
CALL test_var();
DESC employees;
#举例1：声明局部变量，并分别赋值为employees表中employee_id为102的last_name和salary
DELIMITER //
CREATE PROCEDURE test_var2()
BEGIN
#声明局部变量
DECLARE `name` VARCHAR(25);
DECLARE salary DOUBLE(10,2) DEFAULT 0.0;
#给变量赋值
SELECT e.last_name,e.salary INTO `name`,salary
FROM employees e
WHERE employee_id=102;
#使用变量
SELECT `name`,salary;
END//
DELIMITER;
#调用存储过程
CALL test_var2();
#举例2：声明两个变量，求和并打印 （分别使用会话用户变量、局部变量的方式实现）
#方式一：会话用户变量
SET @value1:=66;
SET @value2:=77;
SET @sumvalue=@value1+@value2;
SELECT @sumvalue;
#方式二:局部变量
DELIMITER //
CREATE PROCEDURE sum_value()
BEGIN
#声明变量
DECLARE value_1,value_2,value_sum INT;
DECLARE value_1 INT DEFAULT 0;
DECLARE value_2 INT DEFAULT 0;
#给变量赋值
SET value_1:=55;
SET value_2:=85;
SET value_sum := value_1 + value_2;
#使用变量
SELECT value_sum;
END//
DELIMITER ;
#调用存储过程
CALL sum_value();
#举例3：创建存储过程“different_salary”查询某员工和他领导的薪资差距，并用IN参数emp_id接收员工id，用OUT参数dif_salary输出薪资差距结果。
DELIMITER //
CREATE PROCEDURE different_salary(IN emp_id INT,OUT dif_salary DOUBLE)
BEGIN
#定义局部变量
DECLARE emp_salary DOUBLE(10,2);
DECLARE mrg_salary DOUBLE(10,2);
#给变量赋值
SELECT salary INTO emp_salary
FROM employees e
WHERE e.employee_id = emp_id;
SELECT salary INTO mrg_salary
FROM employees emp
WHERE emp.employee_id=(
SELECT manager_id
FROM employees mrg
WHERE employee_id=emp_id
);
#使用变量
SELECT mrg_salary-emp_salary INTO dif_salary;
#😍SET dif_salary =mrg_salary - emp_salary;
END//
DELIMITER;
#调用存储过程
SET @id:=103;
CALL different_salary(@id,@test);
SELECT @test;
DROP PROCEDURE different_salary;
#验证
SELECT *
FROM employees
WHERE employee_id=101;#17000
SELECT salary
FROM employees
WHERE employee_id =100;#24000
SELECT 24000-17000;
#定义条件与处理程序
#错误演示：
#报错：Field 'email' doesn't have a default value 有非空约束
INSERT INTO employees(last_name)
VALUES('Tom');
DESC employees;
#错误演示：
DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
SET @x = 1;
UPDATE employees SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3;
END //
DELIMITER ;
#调用存储过程
/*
错误代码： 1048
Column 'email' cannot be null email有非空约束
*/
CALL UpdateDataNoCondition();
SELECT @x;
#定义条件的格式：DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）
#举例1：定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应。
#方式一：使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048；
#方式二：使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION SQLSTATE FOR '23000';
#举例2：定义"ERROR 1148(42000)"错误，名称为command_not_allowed。
DECLARE command_not_allowed CONDITION SQLSTATE FOR '42000';
DECLARE command_not_allowed CONDITION FOR 1148;
#定义处理程序
#格式 DECLARE 处理方式 HANDLER FOR 错误类型 处理语句
#举例
#方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
#方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
#方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';
#案例解决
DROP PROCEDURE UpdateDataNoCondition;
#重新定义存储过程，体现错误的处理程序
DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
#声明处理程序
#处理方式1：
DECLARE CONTINUE HANDLER FOR 1048 SET @prc_value = -1;
#处理方式2：
#declare continue handler for sqlstate '23000' @prc_value = -1;
SET @x = 1;
UPDATE employees SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3;
END //
DELIMITER ;
CALL UpdateDataNoCondition();
SELECT @prc_value;
SELECT @x;
SELECT email
FROM employees
WHERE last_name = 'Abel';
#举例：
#创建一个名称为“InsertDataWithCondition”的存储过程，代码如下
#准备工作
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;
DESC departments;
ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE(department_id);
#定义存储过程：
DELIMITER //
CREATE PROCEDURE InsertDataWithCondition()
BEGIN
SET @x = 1;
INSERT INTO departments(department_name) VALUES('测试');#违反唯一性约束 因为department_id默认值约束是零
SET @x = 2;
INSERT INTO departments(department_name) VALUES('测试');#不指明id字段添加 错误 唯一性约束是id 这里有问题
SET @x = 3;
END //
DELIMITER ;
SELECT * FROM departments;
#调用
CALL InsertDataWithCondition();
SELECT @x;#2 添加第二次时报错
#删除此存储过程
DROP PROCEDURE IF EXISTS InsertDataWithCondition;
#重新定义存储过程（考虑到错误的处理程序）
DELIMITER //
CREATE PROCEDURE InsertDataWithCondition()
BEGIN
#处理程序
#方式1：
#declare exit handler for 1062 set @pro_value = -1;
#方式2：
#DECLARE EXIT HANDLER FOR sqlstate '23000' SET @pro_value = -1;
#方式3:
DECLARE duplicate_entry CONDITION FOR 1062;
DECLARE EXIT HANDLER FOR duplicate_entry SET @pro_value = -1;
SET @x = 1;
INSERT INTO departments(department_name) VALUES('测试');#错误 唯无唯一约束 无法执行
SET @x = 2;
INSERT INTO departments(department_name) VALUES('测试');
SET @x = 3;
END //
DELIMITER ;
DESC departments;
SHOW CREATE PROCEDURE InsertDataWithCondition;
CALL InsertDataWithCondition();
SELECT @x,@pro_value;
DROP PROCEDURE InsertDataWithCondition;
#流程控制
#分支结构之 IF
#举例
DELIMITER //
CREATE PROCEDURE test_if()
BEGIN
#情况1
#声明局部变量
/*
declare stu_name varchar(15);
if stu_name is null
then select 'stu_name is null';
end if;
*/
#情况2
/*
declare email varchar(25) default 'aaa';
if email is null
then select'email is null';
else
select'email is not null';
end if;*/
#情况3
DECLARE age INT DEFAULT 20;
IF age > 40
THEN SELECT '中老年';
ELSEIF age >18
THEN SELECT '青壮年';
ELSEIF age>8
THEN SELECT '青少年';
ELSE SELECT '婴幼儿';
END IF;
END//
DELIMITER;
DROP PROCEDURE IF EXISTS test_if;
#调用
CALL test_if();
#举例2：声明存储过程“update_salary_by_eid1”，定义IN参数emp_id，输入员工编号。
#判断该员工薪资如果低于8000元并且入职时间超过5年，就涨薪500元；否则就不变。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
BEGIN
#定义变量 员工工资 入职时间 5年
DECLARE emp_salary DOUBLE(8,2);
DECLARE hr_date INT;
DECLARE work_day INT;
#给变量赋值
SELECT salary INTO emp_salary
FROM employees e
WHERE e.employee_id = emp_id;
SET work_day := 365*5;
SELECT DATEDIFF(NOW(),hire_date)INTO hr_date
FROM employees e
WHERE e.employee_id = emp_id;
#使用变量加上条件
IF hr_date > work_day AND emp_salary < 8000
THEN UPDATE employees SET salary = salary + 500 WHERE employee_id =emp_id;
END IF;
END//
DELIMITER ;
SET @emp := 104;
CALL update_salary_by_eid1(@emp);
SELECT DATEDIFF(NOW(),hire_date)/365,salary,employee_id
FROM employees
WHERE salary<8000 AND DATEDIFF(NOW(),hire_date)/365>5;
DROP PROCEDURE IF EXISTS update_salary_by_eid1;
DESC employees;
SELECT * FROM employees;
#举例3：声明存储过程“update_salary_by_eid2”，定义IN参数emp_id，输入员工编号。
#判断该员工薪资如果低于9000元并且入职时间超过5年，就涨薪500元；否则就涨薪100元。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid2(IN emp_id INT)
BEGIN
#声明局部变量
DECLARE emp_sal DOUBLE(8,2);
DECLARE date_year DOUBLE(8,2);
#变量赋值
SELECT salary INTO emp_sal
FROM employees
WHERE employee_id = emp_id;
SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO date_year
FROM employees
WHERE employee_id = emp_id;
#使用变量
IF emp_sal < 9000 AND date_year > 5
THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
ELSE UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
END IF;
END//
DELIMITER ;
DROP PROCEDURE IF EXISTS update_salary_by_eid2;
SET @emp_id := 140;
CALL update_salary_by_eid2(@emp_id);
SELECT *
FROM employees
WHERE employee_id=140;
#举例4：声明存储过程“update_salary_by_eid3”，定义IN参数emp_id，输入员工编号。判断该员工
#薪资如果低于9000元，就更新薪资为9000元；薪资如果大于等于9000元且低于10000的，但是奖金
#比例为NULL的，就更新奖金比例为0.01；其他的涨薪100元。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid3(IN emp_id INT)
BEGIN
#声明变量 工资
DECLARE emp_sal DOUBLE(10,2);
DECLARE emp_com DOUBLE(2,2);
#变量赋值
SELECT salary INTO emp_sal
FROM employees
WHERE employee_id = emp_id;
SELECT commission_pct INTO emp_com
FROM employees
WHERE employee_id= emp_id;
#加条件使用变量
IF emp_sal < 9000
THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
ELSEIF emp_sal < 10000 AND emp_com IS NULL#条件一没进去
THEN UPDATE employees SET commission_pct = 0.2 WHERE employee_id = emp_id;
ELSE UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
END IF;
END//
DELIMITER ;
DROP PROCEDURE IF EXISTS update_salary_by_eid3;
SET @value := 103;
CALL update_salary_by_eid3(@value);
CALL update_salary_by_eid3();
SELECT * FROM employees WHERE employee_id = 103;
DESC employees;
SELECT * FROM employees;
#分支结构之CASE
#举例一:基本的使用
DELIMITER //
CREATE PROCEDURE test_case()
BEGIN
#演示1:case ... when ... then...
/*
DECLARE var INT DEFAULT 2;
case var
when 1 then select 'var = 1';
when 2 then select 'var = 2';
when 3 then select 'var = 3';
else select 'other value';
end case;*/
#演示2：case ... when ...
DECLARE var1 INT DEFAULT 10;
CASE
WHEN var1 >= 100 THEN SELECT '三位数';
WHEN var1 >= 10 THEN SELECT '两位数';
ELSE SELECT '个位数';
END CASE;
END//
DELIMITER ;
DROP PROCEDURE IF EXISTS test_case;
CALL test_case();
/*
举例3：声明存储过程“update_salary_by_eid4”，定义IN参数emp_id，输入员工编号。判断该员工
薪资如果低于9000元，就更新薪资为9000元；薪资大于等于9000元且低于10000的，但是奖金比例
为NULL的，就更新奖金比例为0.01；其他的涨薪100元。
*/
DELIMITER //
CREATE PROCEDURE update_salary_by_eid4(IN emp_id INT)
BEGIN
#定义变量 emp_sal emp_pct
DECLARE emp_sal DOUBLE;
DECLARE emp_pct DOUBLE;
#变量赋值
SELECT salary INTO emp_sal
FROM employees
WHERE employee_id = emp_id;
SELECT commission_pct INTO emp_pct
FROM employees
WHERE employee_id = emp_id;
#加条件使用变量
CASE WHEN emp_sal < 9000
THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id ;
WHEN emp_sal < 10000 AND emp_pct IS NULL
THEN UPDATE employees SET commission_pct = 0.1 WHERE employee_id = emp_id;
ELSE
UPDATE employees SET salary =salary + 100 WHERE employee_id = emp_id;
END CASE;
END//
DELIMITER ;
SET @value := 102;
CALL update_salary_by_eid4(@value);
SELECT *
FROM employees
WHERE employee_id IN(104,105,102);
DROP PROCEDURE update_salary_by_eid4;
/*
举例4：声明存储过程update_salary_by_eid5，定义IN参数emp_id，输入员工编号。判断该员工的
入职年限，如果是0年，薪资涨50；如果是1年，薪资涨100；如果是2年，薪资涨200；如果是3年，
薪资涨300；如果是4年，薪资涨400；其他的涨薪500。
*/
DELIMITER //
CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT)
BEGIN
#定义变量 emp_year
DECLARE emp_year DOUBLE;
#变量赋值
SELECT ROUND(DATEDIFF(CURDATE(),hire_date))/365 INTO emp_year
FROM employees
WHERE employee_id = emp_id;
#加条件使用变量
CASE emp_year
WHEN 0 THEN UPDATE employees SET salary = salary +50 WHERE employee_id = emp_id;
WHEN 1 THEN UPDATE employees SET salary = salary +100 WHERE employee_id = emp_id;
WHEN 2 THEN UPDATE employees SET salary = salary +200 WHERE employee_id = emp_id;
WHEN 3 THEN UPDATE employees SET salary = salary +300 WHERE employee_id = emp_id;
WHEN 4 THEN UPDATE employees SET salary = salary +400 WHERE employee_id = emp_id;
ELSE UPDATE employees SET salary = salary +500 WHERE employee_id = emp_id;
END CASE;
END//
DELIMITER ;
#调用
SET @year_value := 100;
CALL update_salary_by_eid5(100);
SELECT * FROM employees;
DROP PROCEDURE update_salary_by_eid5;
#循环结构之LOOP
/*
[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label]
*/
#举例1：
DELIMITER //
CREATE PROCEDURE test_loop()
BEGIN
#声明局部变量
DECLARE num INT DEFAULT 1;
loop_label:LOOP
#重新赋值
SET num = num + 1;
IF num >= 10
THEN LEAVE loop_label;
END IF;
END LOOP loop_label;
SELECT num;
END//
DELIMITER ;
DROP PROCEDURE test_loop;
#调用
CALL test_loop();
/*
举例2：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程
“update_salary_loop()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨为
原来的1.1倍。直到全公司的平均薪资达到12000结束。并统计循环次数。
*/
DELIMITER //
CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
#声明变量
DECLARE avg_sal DOUBLE;
DECLARE loop_count INT DEFAULT 0;
#赋值
SELECT AVG(salary) INTO avg_sal FROM employees;
loop_label:LOOP
#满足条件 退出
IF avg_sal >= 12000
THEN LEAVE loop_label;
END IF;
#循环增加
UPDATE employees
SET salary = salary * 1.1;
SELECT AVG(salary) INTO avg_sal
FROM employees;
#赋值 计算循环次数
SET loop_count = loop_count + 1;
END LOOP loop_label;
SET num = loop_count;
END//
DELIMITER ;
DROP PROCEDURE update_salary_loop;
#调用
CALL update_salary_loop(@value);
SELECT @value;
SELECT AVG(salary) FROM employees;
#循环结构之WHILE
/*
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];
*/
DELIMITER //
CREATE PROCEDURE test_while()
BEGIN
DECLARE num INT DEFAULT 0;
#迭代条件
w_label:WHILE num <=10 DO
#循环体
#迭代条件
SET num = num + 1;
END WHILE w_label;
SELECT num;
END//
DELIMITER ;
DROP PROCEDURE test_while;
#调用
CALL test_while();
/*
举例2：市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。声明存储过程
“update_salary_while()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家降薪，薪资降
为原来的90%。直到全公司的平均薪资达到5000结束。并统计循环次数。
*/
DELIMITER //
CREATE PROCEDURE update_salary_while(OUT num INT)
BEGIN
#声明变量
DECLARE avg_salary DOUBLE;
DECLARE sal_count INT DEFAULT 0;
#变量赋值
SELECT AVG(salary) INTO avg_salary FROM employees;
#循环条件
w_label:WHILE avg_salary >= 5000 DO
#循环体
UPDATE employees SET salary = salary * 0.9;
#迭代条件
SELECT AVG(salary) INTO avg_salary FROM employees;
#记录次数
SET sal_count = sal_count + 1;
END WHILE w_label;
SET num = sal_count;
SELECT num;
END//
DELIMITER ;
DROP PROCEDURE update_salary_while;
CALL update_salary_while(@value);
SELECT @value;
SELECT AVG(salary) FROM employees;
#循环结构之 REPEAT
/*
[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]
*/
DELIMITER //
CREATE PROCEDURE repeat_test()
BEGIN
DECLARE num INT DEFAULT 0;
repeat_label:REPEAT
SET num = num + 1;
UNTIL num > 10
END REPEAT repeat_label;
SELECT num;
END//
DELIMITER ;
#调用
CALL repeat_test();
/*
举例2：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程
“update_salary_repeat()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨
为原来的1.15倍。直到全公司的平均薪资达到13000结束。并统计循环次数。
*/
DELIMITER //
CREATE PROCEDURE update_salary_repeat(OUT num INT)
BEGIN
#声明变量
DECLARE avg_sal DOUBLE; #平均工资
DECLARE avg_count INT DEFAULT 0; #循环次数
#变量赋值
SELECT AVG(salary) INTO avg_sal FROM employees;
#循环
REPEAT
#循环体增长工资
UPDATE employees SET salary = salary * 1.15;
#迭代重新赋值
SELECT AVG(salary) INTO avg_sal FROM employees;
#记录循环次数
SET avg_count = avg_count + 1;
#循环条件满足退出
UNTIL avg_sal >=13000
END REPEAT;
SET num = avg_count;
SELECT num;
END//
DELIMITER ;
CALL update_salary_repeat(@num);
SELECT AVG(salary) FROM employees;
/*
对比三种循环结构：
1、这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称。
2、 LOOP：一般用于实现简单的"死"循环 WHILE：先判断后执行 REPEAT：先执行后判断，无条件至少执行一次
*/
#LEAVE的使用
/*
举例1：创建存储过程 “leave_begin()”，声明INT类型的IN参数num。给BEGIN...END加标记名，并在
BEGIN...END中使用IF语句判断num参数的值。
如果num<=0，则使用LEAVE语句退出BEGIN...END；
如果num=1，则查询“employees”表的平均薪资；
如果num=2，则查询“employees”表的最低薪资；
如果num>2，则查询“employees”表的最高薪资。
IF语句结束后查询“employees”表的总人数。
*/
DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
b_label:BEGIN
IF num <= 0
THEN LEAVE b_label;
ELSEIF num = 1
THEN SELECT AVG(salary) FROM employees;
ELSEIF num = 2
THEN SELECT MIN(salary) FROM employees;
ELSE
SELECT MAX(salary) FROM employees;
END IF;
SELECT COUNT(*) FROM employees;
END//
DELIMITER ;
#调用
CALL leave_begin(0);
CALL leave_begin(1);
/*
举例2：
当市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。声明存储过程“leave_while()”，声明
OUT参数num，输出循环次数，存储过程中使用WHILE循环给大家降低薪资为原来薪资的90%，直到全公
司的平均薪资小于等于10000，并统计循环次数。
*/
DELIMITER //
CREATE PROCEDURE leave_while(OUT num INT)
BEGIN
#定义变量
DECLARE avg_sal DOUBLE;
DECLARE while_count INT DEFAULT 0;
#变量赋值
SELECT AVG(salary) INTO avg_sal FROM employees;
w_label:WHILE TRUE DO
IF avg_sal <= 10000
THEN LEAVE w_label;
END IF;
UPDATE employees SET salary = salary * 0.9;
SELECT AVG(salary) INTO avg_sal FROM employees;
SET while_count = while_count + 1;
END WHILE w_label;
SET num = while_count;
SELECT num;
END//
DELIMITER ;
DROP PROCEDURE leave_while;
#调用
CALL leave_while(@num);
SELECT AVG(salary) FROM employees;
#ITERATE 的使用
/*
举例： 定义局部变量num，初始值为0。循环结构中执行num + 1操作。
如果num < 10，则继续执行循环；
如果num > 15，则退出循环结构；
*/
DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
DECLARE num INT DEFAULT 0;
loop_label:LOOP
SET num = num + 1;
IF num < 10
THEN ITERATE loop_label;
ELSEIF num > 15
THEN LEAVE loop_label;
END IF;
SELECT '最难不过坚持';
END LOOP;
END//
DELIMITER ;
DROP PROCEDURE test_iterate;
CALL test_iterate();
SELECT * FROM employees;
#游标的使用
/*
游标使用的步骤：
① 声明游标
② 打开游标
③ 使用游标（从游标中获取数据）
④ 关闭游标
*/
/*
创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary，DOUBLE类型；声明
OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和
达到limit_total_salary参数的值，返回累加的人数给total_count。
*/
DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
BEGIN
#声明变量
DECLARE emp_sum_salary DOUBLE DEFAULT 0; #全部工资的累加
DECLARE emp_salary DOUBLE; #存储游标中读取的工资
DECLARE emp_count INT DEFAULT 0; #记录次数
#声明游标
DECLARE cur_sor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
#开启游标
OPEN cur_sor;
#使用游标 重复读取 （循环）
l_label:LOOP
IF emp_sum_salary >= limit_total_salary
THEN LEAVE l_label;
END IF;
FETCH cur_sor INTO emp_salary;#游标取出工资
SET emp_sum_salary = emp_sum_salary + emp_salary;#工资的累加
SET emp_count = emp_count + 1;#记录循环次数
END LOOP l_label;
#关闭游标
CLOSE cur_sor;
SET total_count = emp_count;
END//
DELIMITER ;
DROP PROCEDURE get_count_by_limit_total_salary;
CALL get_count_by_limit_total_salary(200000,@num);
SELECT @num;
#课后练习
#准备工作
SET GLOBAL log_bin_trust_function_creators = 1;
CREATE DATABASE test16_var_cur;
USE test16_var_cur;
CREATE TABLE employees
AS
SELECT * FROM atguigudb.`employees`;
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;
#无参有返回
#1. 创建函数get_count(),返回公司的员工个数有参有返回
DELIMITER //
CREATE FUNCTION get_count()
RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;
SELECT COUNT(*) INTO c
FROM employees;
RETURN c;
END//
DELIMITER ;
SELECT get_count();
DROP FUNCTION get_count;
#2. 创建函数ename_salary(),根据员工姓名，返回它的工资
DELIMITER //
CREATE FUNCTION ename_salary( emp_name VARCHAR(20)) RETURNS DOUBLE#参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参
数
BEGIN
DECLARE emp_salary DOUBLE;
SELECT salary INTO emp_salary
FROM employees
WHERE last_name = emp_name;
RETURN emp_salary;
END//
DELIMITER ;
#调用
SELECT ename_salary('Abel');
#3. 创建函数dept_sal() ,根据部门名，返回该部门的平均工资
DELIMITER //
CREATE FUNCTION dept_sal(dept_name VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE avg_dept_sal DOUBLE;
SELECT AVG(salary) INTO avg_dept_sal
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE department_name = dept_name
GROUP BY d.department_id;
RETURN avg_dept_sal;
END//
DELIMITER ;
#调用
SELECT dept_sal('IT');
#验证
SELECT *,AVG(salary)AS avg_sal
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE department_name = 'IT'
GROUP BY e.department_id;
#4. 创建函数add_float()，实现传入两个float，返回二者之和
DELIMITER //
CREATE FUNCTION add_float(num_1 FLOAT,num_2 FLOAT,num_sum FLOAT) RETURNS FLOAT
BEGIN
SET num_sum = num_1+num_2;
RETURN num_sum;
END//
DELIMITER ;
SELECT add_float(1.5,1.8,@num);
#1. 创建函数test_if_case()，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
#要求：分别使用if结构和case结构实现
#方式一：
DELIMITER //
CREATE FUNCTION test_if_case(grade INT) RETURNS VARCHAR(5)
BEGIN
IF grade > 90
THEN RETURN 'A';
ELSEIF grade > 80
THEN RETURN 'B';
ELSEIF grade > 60
THEN RETURN 'C';
ELSE RETURN 'D' ;
END IF;
END//
DELIMITER ;
SELECT test_if_case(95);
DROP FUNCTION test_if_case;
#方式二：
DELIMITER //
CREATE FUNCTION test_if_case(grade INT) RETURNS VARCHAR(5)
BEGIN
CASE
WHEN grade > 90 THEN RETURN 'A';
WHEN grade > 80 THEN RETURN 'B';
WHEN grade > 60 THEN RETURN 'C';
ELSE RETURN 'D';
END CASE;
END//
DELIMITER ;
SELECT test_if_case(95);
#2. 创建存储过程test_if_pro()，传入工资值，如果工资值<3000,则删除工资为此值的员工，如果3000 <= 工资值 <= 5000,则修改此工资值的员工薪资涨1000，否则涨工资500
DELIMITER //
CREATE PROCEDURE test_if_pro(IN e_salary DOUBLE)
BEGIN
IF e_salary < 3000
THEN DELETE FROM employees WHERE salary = e_salary;
ELSEIF 3000 <= e_salary AND e_salary <= 5000
THEN UPDATE employees SET salary = salary + 1000 WHERE salary = e_salary;
ELSE UPDATE employees SET salary = salary + 500 WHERE salary = e_salary;
END IF;
END//
DELIMITER ;
DROP PROCEDURE test_if_pro;
#调用
CALL test_if_pro(3000);
SELECT employee_id,last_name,salary
FROM employees
WHERE employee_id = 187;
#3. 创建存储过程insert_data(),传入参数为 IN 的 INT 类型变量 insert_count,实现向admin表中批量插入insert_count条记录
CREATE TABLE admin(
id INT,
user_name VARCHAR(25)
);
SELECT * FROM admin;
DELIMITER //
CREATE PROCEDURE insert_dat(IN insert_count INT)
BEGIN
#声明变量
DECLARE total INT DEFAULT 0;
#赋值
w_label:WHILE total < insert_count DO
INSERT INTO admin(id,user_name)
VALUES(total,CONCAT('zx',total));
SET total = total + 1;
END WHILE w_label;
END//
DELIMITER ;
DROP PROCEDURE insert_dat;
CALL insert_dat(5);
SELECT * FROM admin;
DELETE FROM admin;
/*创建存储过程update_salary()，参数1为 IN 的INT型变量dept_id，表示部门id；参数2为 IN的INT型变量
change_sal_count，表示要调整薪资的员工个数。查询指定id部门的员工信息，按照salary升序排列，根
据hire_date的情况，调整前change_sal_count个员工的薪资，详情如下。*/
DELIMITER //
CREATE PROCEDURE update_salary(IN dept_id INT,IN change_sal_count INT)
BEGIN
#声明变量
DECLARE total INT DEFAULT 0;
DECLARE salary_rate DOUBLE;
DECLARE emp_id INT;
DECLARE emp_hire_date DATE;
#声明游标
DECLARE cur_sor CURSOR FOR SELECT employee_id,hire_date
FROM employees
WHERE department_id = dept_id
ORDER BY salary ASC;
#开启游标
OPEN cur_sor;
#使用游标
WHILE total < change_sal_count DO
FETCH cur_sor INTO emp_id,emp_hire_date;
IF (YEAR(emp_hire_date) < 1995)
THEN SET salary_rate = 1.2;
ELSEIF(YEAR(emp_hire_date) <= 1998)
THEN SET salary_rate = 1.15;
ELSEIF(YEAR(emp_hire_date) <= 2001)
THEN SET salary_rate = 1.10;
ELSEIF(YEAR(emp_hire_date) >2001)
THEN SET salary_rate = 1.05;
END IF;
UPDATE employees SET salary = salary * salary_rate
WHERE employee_id = emp_id;
SET total = total + 1;
END WHILE;
#关闭游标
CLOSE cur_sor;
END//
DELIMITER ;
#调用
CALL update_salary(50,2);
SELECT *
FROM employees
WHERE department_id = 50
ORDER BY salary;
|
