变量,流程控制,游标

一. 变量

1.1 系统变量

1.1.1 系统变量分类

系统变量定义了当前 MySQL 服务实例的属性、特征。

这些系统变量的值要么是 编译 MySQL 时参数 的默认值，要么是 配置文件 （例如 my.ini 等）中的参数值。

通过网址 https://dev.mysql.com/doc/refman/8.0/en/server - system - variables.html

查看 MySQL 文档的系统变量。

分类:

• 全局系统变量 : global  --> 针对于所有会话有效,不能跨重启 --> 启动服务器
• 会话系统变量 :session(默认)  ->客户端发送请求
• 关系: 

1.1.2 查看系统变量

#查看所有全局变量
SHOW GLOBAL VARIABLES;
#查看所有会话变量
SHOW SESSION VARIABLES;
或者
SHOW VARIABLES;
#查看满足条件的部分系统变量。 
SHOW GLOBAL VARIABLES LIKE '%标识符%'; 
#查看满足条件的部分会话变量 
SHOW SESSION VARIABLES LIKE '%标识符%';
例子:SHOW GLOBAL VARIABLES LIKE 'admin_%';
#查看指定的系统变量的值 
SELECT @@global.变量名; 
#查看指定的会话变量的值 
SELECT @@session.变量名; 
#或者 
SELECT @@变量名;

1.1.3 修改系统变量的值

方式1：修改MySQL 配置文件 ，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）
方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值
SET @@global.变量名=变量值; 
SET GLOBAL 变量名=变量值; 
#为某个会话变量赋值 
#方式1：
SET @@session.变量名=变量值;
#方式2：
SET SESSION 变量名=变量值;
例子:
SELECT @@global.autocommit; 
SET GLOBAL autocommit=0;
SELECT @@session.tx_isolation; 
SET @@session.tx_isolation='read-uncommitted';
SET GLOBAL max_connections = 1000; 
SELECT @@global.max_connections;

1.2 用户变量

1.2.1 用户变量分类

用户变量是用户自己定义的，作为 MySQL 编码规范， MySQL 中的用户变量以 一个 “@” 开头。根据作用范围不同，又分为 会话用户变量 和 局部变量

• 会话用户变量：作用域和会话变量一样，只对 当前连接 会话有效。
• 局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在 存储过程和函数 中使用

1.2.2  会话用户变量

#1. 变量的定义
#方式1：“=”或“:=”
SET @用户变量 = 值; 
SET @用户变量 := 值; 

#方式2：“:=” 或 INTO关键字 
SELECT @用户变量 := 表达式 [FROM 等子句]; 
SELECT 表达式 INTO @用户变量 [FROM 等子句];

#2. 查看用户变量的值 （查看、比较、运算等）
SELECT @用户变量;

#3. 举例
-- 方式一
SET @m1=1;
SET @m2 := 2;
SET @sum = @m1+@m2;
SELECT @sum;
-- 方式二
SELECT @count := COUNT(*) FROM employees;
SELECT AVG(salary) INTO @avg_tal FROM employees;
SELECT @avg_tal;

1.3 局部变量

1.3.1 声明+赋值+使用

1. 必须使用 declare 声明

2. 仅仅在定义它的 BEGIN ... END 中有效

3. 只能放在 BEGIN ... END 中，而且只能放在第一句

BEGIN
     #声明局部变量 
     DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值]; 
     DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];

     #为局部变量赋值
     SET 变量名1 = 值;
     SELECT 值 INTO 变量名2 [FROM 子句];

     #查看局部变量的值
     SELECT 变量1,变量2,变量3;
END

#举例
DELIMITER $
CREATE PROCEDURE test_var()
BEGIN
  #声明
  DECLARE a INT DEFAULT 0;
  DECLARE b INT;
  #declare a,b int default 0;
  DECLARE emp_name VARCHAR(25);
  #赋值
  SET a = 1;
  SET b:= 2;
  SELECT last_name INTO emp_name FROM employees WHERE employee_id=101;
  #使用
  SELECT a,b,emp_name;
END $
DELIMITER ;
#调用存储过程
CALL test_var();
-- 声明赋值使用
# 举例一: 声明局部变量,并分别赋值为employees表中employee_id为102的last_name和salary
DELIMITER //
CREATE PROCEDURE test_var1()
BEGIN
   DECLARE emp_name VARCHAR(25);
   DECLARE sal DOUBLE(8,2) DEFAULT 0.0;
   SELECT last_name,salary INTO emp_name,sal FROM employees WHERE employee_id=102;
   SELECT emp_name,sal;
   
END //
DELIMITER ;
CALL test_var1(); #局部变量名不能与字段名相同
#举例2：声明两个变量，求和并打印 （分别使用会话用户变量、局部变量的方式实现）
#方式一 : 使用会话用户变量
SET @v1=10;
SET @v2 =20;
SET @sum1 = @v1+@v2;
SELECT @sum1;
#方式二 : 局部变量
DELIMITER //
CREATE PROCEDURE test_var3()
BEGIN
  DECLARE var1 INT DEFAULT 0;
  DECLARE var2 INT DEFAULT 0;
  DECLARE var3 INT DEFAULT 0;
  SET var1=10;
  SET var2=30;
  SET var3=var1+var2;
  SELECT var1,var2,var3;
END //
DELIMITER ;
CALL test_var3();
#举例3：创建存储过程“different_salary”查询某员工和他领导的薪资差距，并用IN参数emp_id接收员工
id，用OUT参数dif_salary输出薪资差距结果。
DELIMITER //
CREATE PROCEDURE different_salary(IN emp_id INT,OUT dif_salary DOUBLE(8,2))
BEGIN
   
   DECLARE emp_sal DOUBLE(8,2);
   DECLARE mgr_sal DOUBLE(8,2);
   SELECT salary INTO mgr_sal FROM employees WHERE employee_id=(SELECT manager_id FROM employees WHERE employee_id = emp_id);
   SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
   SET  dif_salary = mgr_sal-emp_sal;
END //
DELIMITER ;
DROP PROCEDURE different_salary;
CALL different_salary(102,@dif_salary);
SELECT @dif_salary;

 1.3.2 对比会话用户变量与局部变量

 二. 定义条件与处理程序 

见16章ppt

三. 流程控制

3.1 分支结构之 IF  

IF 表达式1 THEN 操作1 
[ELSEIF 表达式2 THEN 操作2]…… 
[ELSE 操作N] END IF

特点：① 不同的表达式对应不同的操作 ② 使用在begin end中

# 举例: 情况一
DELIMITER //
CREATE PROCEDURE test_if()
BEGIN
  #声明局部变量
  DECLARE stu_name VARCHAR(15);
  IF stu_name IS NULL THEN SELECT 'stu_name is null';
  END IF;
END //
DELIMITER ;
CALL test_if();

#举例: 情况二:2选1
DELIMITER //
CREATE PROCEDURE test_if1()
BEGIN
  #声明局部变量
  DECLARE email VARCHAR(25);
  IF email IS NULL THEN SELECT 'email is null';
  ELSE  SELECT 'email is not null';
  END IF;
END //
DELIMITER ;
CALL test_if1();

#举例: 情况三 :多选一
DELIMITER //
CREATE PROCEDURE test_if2()
BEGIN
  
  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 ;
CALL test_if2();

举例1：声明存储过程“update_salary_by_eid1”，定义IN参数emp_id，输入员工编号。判断该员工
薪资如果低于8000元并且入职时间超过5年，就涨薪500元；否则就不变。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
BEGIN
  DECLARE sal DOUBLE(8,2);
  DECLARE year1 INT;
  SELECT salary INTO sal FROM employees WHERE employee_id=emp_id;
  SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO year1 FROM employees WHERE employee_id=emp_id;
  IF sal <8000 AND year1>5 THEN UPDATE employees SET salary = salary+500 WHERE employee_id=emp_id;
  END IF;
END //
DELIMITER ;
CALL update_salary_by_eid1(115);
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 sal DOUBLE(8,2);
  DECLARE h_date INT;
  SELECT salary INTO sal FROM employees WHERE employee_id = emp_id;
  SELECT DATEDIFF(NOW(),hire_date)/365 INTO h_date FROM employees WHERE employee_id = emp_id;
  IF sal <9000 AND h_date >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 ;
CALL update_salary_by_eid2(115);
CALL update_salary_by_eid2(100);

举例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 sal DOUBLE(8,2);
  DECLARE  emp_cp DOUBLE(2,2);
  SELECT salary INTO sal FROM employees WHERE employee_id = emp_id;
  SELECT commission_pct INTO emp_cp FROM employees WHERE employee_id = emp_id;
  IF sal <9000  THEN UPDATE employees SET salary =9000 WHERE employee_id = emp_id;
  ELSEIF sal<10000 AND emp_cp IS NULL THEN UPDATE employees SET commission_pct=0.01 WHERE employee_id = emp_id;
  ELSE  UPDATE employees SET salary =salary +100 WHERE employee_id = emp_id;
  END IF;
END //
DELIMITER ;
CALL update_salary_by_eid3(100);
DESC employees;

3.2 分支结构之 CASE

举例1：
使用CASE流程控制语句的第1种格式，判断val值等于1、等于2，或者两者都不等。
DELIMITER //
CREATE PROCEDURE test_case()
BEGIN
  DECLARE val INT DEFAULT 2;
  CASE val WHEN 1 THEN SELECT '=1';
  WHEN 2 THEN SELECT '=2';
  ELSE SELECT '都不等';
  END CASE;
END //
DELIMITER ;
CALL test_case();
#或者
DELIMITER //
CREATE PROCEDURE test_case1()
BEGIN
  DECLARE val INT DEFAULT 2;
  CASE  WHEN val=1 THEN SELECT '=1';
  WHEN val=2 THEN SELECT '=2';
  ELSE SELECT '都不等';
  END CASE;
END //
DELIMITER ;
CALL test_case1();

举例2：声明存储过程“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
 DECLARE sal DOUBLE;
 DECLARE emp_cp DOUBLE;
 SELECT salary INTO sal FROM employees WHERE employee_id=emp_id;
  SELECT commission_pct INTO emp_cp FROM employees WHERE employee_id = emp_id;
 CASE WHEN sal<9000 THEN UPDATE employees SET salary=9000 WHERE employee_id=emp_id;
 WHEN sal<10000 AND emp_cp IS NULL THEN UPDATE employees SET commission_pct=0.01 WHERE employee_id = emp_id;
 ELSE UPDATE employees SET salary =salary +100 WHERE employee_id = emp_id;
 END CASE;
END //
DELIMITER ;
DROP PROCEDURE update_salary_by_eid4;
CALL update_salary_by_eid4(100);
SELECT * FROM employees;

举例3：声明存储过程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
   DECLARE h_date INT;
   SELECT DATEDIFF(NOW(),hire_date)/365 INTO h_date FROM employees WHERE employee_id = emp_id;
   CASE h_date 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 ;
CALL update_salary_by_eid5(100);

3.3 循环结构之LOOP

循环结构四要素: 初始条件,循环条件,循环体,迭代条件

LOOP 循环语句用来重复执行某些语句。 LOOP 内的语句一直重复执行直到循环被退出（使用 LEAVE 子句），跳出循环过程

/*
[loop_label:] LOOP
    循环执行的语句
end loop [loop_label]
*/
#举例一
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 ;
CALL test_loop();
举例2：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程
“update_salary_loop()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨为
原来的1.1倍。直到全公司的平均薪资达到12000结束。并统计循环次数
USE dbtest16;
DELIMITER //
CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
   DECLARE avg_sal DOUBLE;
   DECLARE loop_count INT DEFAULT 0;
   loop_label:LOOP
   SELECT AVG(salary) INTO avg_sal FROM employees;
   IF avg_sal <12000 THEN UPDATE employees SET salary=salary*1.1;
    SET loop_count=loop_count+1;
   ELSE LEAVE loop_label;
   END IF;
   END LOOP loop_label;
   SET num = loop_count;
END //
DELIMITER ;
DROP PROCEDURE update_salary_loop;
SELECT AVG(salary) FROM employees;
CALL  update_salary_loop(@num);
SELECT @num; 

3.4 循环结构之WHILE

WHILE 语句创建一个带条件判断的循环过程。 WHILE 在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。WHILE 语句的基本格式如下：

[while_label:] WHILE 循环条件 DO 
    循环体 
END WHILE [while_label];

/*
[while_label:] WHILE 循环条件 DO 
     循环体 
 END WHILE [while_label];
*/
#举例一
DELIMITER //
CREATE PROCEDURE test_while()
BEGIN 
   DECLARE num INT DEFAULT 1;
   WHILE num <= 10 DO 
   SET num=num+1;
   END WHILE;
   SELECT num;
END //
DELIMITER ;
CALL test_while();
举例2：市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。声明存储过程
“update_salary_while()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家降薪，薪资降
为原来的90%。直到全公司的平均薪资达到5000结束。并统计循环次数
DELIMITER //
CREATE PROCEDURE update_salary_while(OUT num INT)
BEGIN
  DECLARE while_count INT DEFAULT 0;
  DECLARE avg_sal DOUBLE(8,2);
  
  SELECT AVG(salary) INTO avg_sal FROM employees;
  WHILE avg_sal>5000 DO
      UPDATE employees SET salary=salary*0.9;
     SET while_count=while_count+1;
  SELECT AVG(salary) INTO avg_sal FROM employees;
  END WHILE;
  SET num=while_count;
END //
DELIMITER ;
CALL update_salary_while(@num);
SELECT @num;
SELECT AVG(salary) FROM employees;

3.5 循环结构之REPEAT  

REPEAT 语句创建一个带条件判断的循环过程。与 WHILE 循环不同的是， REPEAT 循环首先会执行一次循 环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT ；如果条件不满足，则会 就继续执行循环，直到满足退出条件为止。

/*
[repeat_label:] repeat
   循环体的语句
 until 结束循环的条件表达式  #后面无分号
 end repeat [repeat_label]
*/
#举例一
DELIMITER //
CREATE PROCEDURE test_repeat()
BEGIN 
   DECLARE num INT DEFAULT 1;
   REPEAT 
   SET num = num+1;
   UNTIL num >=10
 END REPEAT;
 SELECT num;
END //
DELIMITER ;
CALL test_repeat();
举例2：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程
“update_salary_repeat()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨
为原来的1.15倍。直到全公司的平均薪资达到13000结束。并统计循环次数
DELIMITER //
CREATE PROCEDURE update_salary_repeat(OUT num INT)
BEGIN 
    DECLARE repeat_count INT DEFAULT 0;
    DECLARE avg_sal DOUBLE;
    SELECT AVG(salary) INTO avg_sal FROM employees;
    REPEAT
    UPDATE employees SET salary=salary*1.15;
    SET repeat_count=repeat_count+1;
    SELECT AVG(salary) INTO avg_sal FROM employees;
    UNTIL avg_sal >=13000
    END REPEAT;
    SET num=repeat_count;
END //
DELIMITER ;
CALL update_salary_repeat(@num);
SELECT @num;

三种循环对比

1.  循环中如果添加了循环控制语句(如 leave,iterate)必须添加名称

loop: 一般用于死循环

while : 先判断在执行

repeat : 先执行后判断,无条件至少执行一次

 3.6 跳转语句之LEAVE语句

LEAVE 语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出 程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 break

LEAVE 标记名

举例1：创建存储过程 “leave_begin()”，声明INT类型的IN参数num。给BEGIN...END加标记名，并在
BEGIN...END中使用IF语句判断num参数的值
DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
begin_label:BEGIN 
  IF num<0 THEN LEAVE begin_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);
举例2：当市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。声明存储过程“leave_while()”，声明
OUT参数num，输出循环次数，存储过程中使用WHILE循环给大家降低薪资为原来薪资的90%，直到全公
司的平均薪资小于等于10000，并统计循环次数。
DELIMITER //
CREATE PROCEDURE leave_while()(OUT num INT)
BEGIN 
    DECLARE while_count INT DEFAULT 0;
    DECLARE avg_sal DOUBLE;
    SELECT AVG(salary) INTO avg_sal FROM employees;
    while_label:WHILE TRUE DO
    IF avg_sal <= 10000 THEN LEAVE while_label;
    END IF;
    UPDATE employees SET salary = salary * 0.9;
    SET while_count=while_count+1;
    SELECT AVG(salary) INTO avg_sal FROM employees;
    END WHILE;
    SET num=while_count;
END //
DELIMITER ;
CALL update_salary_repeat(@num);
SELECT @num;

3.7 跳转语句之ITERATE语句

ITERATE 语句：只能用在循环语句（LOOP、 REPEAT 和 WHILE 语句）内，表示重新开始循环，将执行顺序 转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue ，意思为“ 再次循环 ” 。

ITERATE label

举例： 定义局部变量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 '执行iterate 不会执行此语句';
   END LOOP;
END //
DELIMITER ;
CALL test_iterate();

四.  游标

在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标 充当了 指针的作用 ，我们可以通过操作游标来对数据行进行操作。

4.1 游标使用步骤

 第一步，声明游标

DECLARE cursor_name CURSOR FOR select_statement;

第二步，打开游标

OPEN cursor_name

第三步，使用游标（从游标中取得数据）

FETCH cursor_name INTO var_name [, var_name] ...

第四步，关闭游标

CLOSE cursor_name

因为游标会 占用系统资源 

创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary，DOUBLE类型；声明
OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和
达到limit_total_salary参数的值，返回累加的人数给total_count
USE dbtest16;
DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
BEGIN 
   DECLARE sum_sal DOUBLE DEFAULT 0.0;
   DECLARE emp_sal DOUBLE;
   DECLARE emp_count INT DEFAULT 0;
   DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
   OPEN emp_cursor;
   REPEAT
   
   FETCH emp_cursor INTO emp_sal;
   SET sum_sal=sum_sal+emp_sal;
   SET emp_count=emp_count+1;
   UNTIL sum_sal >= limit_total_salary
   END REPEAT;
   SET total_count=emp_count;
   CLOSE emp_cursor;
END //
DELIMITER ;
CALL get_count_by_limit_total_salary(200000,@total_count);
SELECT @total_count;

 

游标是 MySQL 的一个重要的功能，为 逐条读取 结果集中的数据，提供了完美的解决方案。跟在应用层 面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。

 

但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行 加锁 ，这样在业务并发量大 的时候，不仅会影响业务之间的效率，还会 消耗系统资源 ，造成内存不足，这是因为游标是在内存中进 行的处理。

建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率。

补充 8.0新特性 系统变量持久化

 

五. 综合练习 

#0.准备工作 
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`;
SHOW TABLES;
#无参有返回 
#1. 创建函数get_count(),返回公司的员工个数
DELIMITER //
CREATE FUNCTION get_count()
RETURNS INT
BEGIN
RETURN (SELECT COUNT(*) FROM employees);
END //
DELIMITER ;
SELECT get_count();
#有参有返回 
#2. 创建函数ename_salary(),根据员工姓名，返回它的工资
DELIMITER //
CREATE FUNCTION ename_salary( emp_name VARCHAR(25))
RETURNS DOUBLE
BEGIN
   DECLARE sal DOUBLE;
   SELECT salary INTO sal FROM employees WHERE last_name = emp_name;
   RETURN sal;
END //
DELIMITER ;
SELECT ename_salary('Abel');
DESC employees;
#3. 创建函数dept_sal() ,根据部门名，返回该部门的平均工资
DELIMITER $
CREATE FUNCTION dept_sal(dept_name VARCHAR(25))
RETURNS DOUBLE
BEGIN
   DECLARE id INT;
   DECLARE sal DOUBLE;
   SELECT department_id INTO id FROM departments WHERE department_name=dept_name;
   SELECT AVG(salary) INTO sal FROM employees WHERE department_id = id;
   RETURN sal;
END $
DELIMITER ;
SELECT dept_sal('IT');
#4. 创建函数add_float()，实现传入两个float，返回二者之和
DELIMITER //
CREATE FUNCTION add_float(num1 FLOAT,num2 FLOAT)
RETURNS FLOAT
BEGIN
  DECLARE sum1 FLOAT;
  SET sum1 = num1 +num2;
  RETURN sum1;
END //
DELIMITER ;
SELECT add_float(1.1,1.3);

#1. 创建函数test_if_case()，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回 C，否则返回D 
#要求：分别使用if结构和case结构实现
DELIMITER //
CREATE FUNCTION test_if_case(grade DOUBLE)
RETURNS CHAR
BEGIN 
  DECLARE score_level CHAR;
  IF grade >90 THEN SET score_level='A';
  ELSEIF grade > 80 THEN SET score_level='B';
  ELSEIF grade > 60 THEN SET score_level='C';
  ELSE SET score_level='D';
  END IF;
  RETURN score_level;
END //
DELIMITER ;
SELECT test_if_case1(91);
DELIMITER //
CREATE FUNCTION test_if_case1(grade DOUBLE)
RETURNS CHAR
BEGIN 
  DECLARE score_level CHAR;
  CASE WHEN grade >90 THEN SET score_level='A';
  WHEN grade > 80 THEN SET score_level='B';
  WHEN grade > 60 THEN SET score_level='C';
  ELSE SET score_level='D';
  END CASE;
  RETURN score_level;
END //
DELIMITER ;
#2. 创建存储过程test_if_pro()，传入工资值，如果工资值<3000,则删除工资为此值的员工，
#如果3000 <= 工 资值 <= 5000,则修改此工资值的员工薪资涨1000，否则涨工资500
DELIMITER //
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
 IF sal<3000 
 THEN DELETE FROM employees WHERE salary=sal;
 ELSEIF sal <=5000 
 THEN UPDATE employees SET salary=salary+1000 WHERE salary=sal;
 ELSE  
 UPDATE employees SET salary=salary+500 WHERE salary=sal;
 END IF;
END //
DELIMITER ;
CALL test_if_pro(2900);
SELECT * FROM employees WHERE salary=2900;
#3. 创建存储过程insert_data(),传入参数为 IN 的 INT 类型变量 insert_count,
#实现向admin表中批量插 入insert_count条记录
CREATE TABLE ADMIN( id INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(25) NOT NULL, user_pwd VARCHAR(35) NOT NULL );
SELECT * FROM ADMIN;
DELIMITER //
CREATE PROCEDURE insert_data(IN insert_count INT)
BEGIN
  DECLARE in_count INT DEFAULT 0;
  DECLARE id INT DEFAULT 1;
  WHILE in_count != insert_count DO
  SET in_count = in_count+1;
  SET id = id+1;
  INSERT INTO ADMIN VALUES(id,'King','123456');
  END WHILE;
END //
DELIMITER ;
DROP PROCEDURE  insert_data;
CALL insert_data(3);

创建存储过程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 emp_id INT;
  DECLARE emp_hire_date DATE;
  DECLARE init_count INT DEFAULT 1;
  DECLARE add_sal_rate DOUBLE;
  DECLARE emp_cursor CURSOR FOR SELECT employee_id,hire_date FROM employees WHERE department_id = dept_id ORDER BY salary;
  OPEN emp_cursor;
  WHILE init_count <= change_sal_count DO
  FETCH emp_cursor INTO emp_id,emp_hire_date;
  IF YEAR(emp_hire_date) <1995 THEN SET add_sal_rate=1.2;
  ELSEIF  YEAR(emp_hire_date) <=1998 THEN SET add_sal_rate=1.15;
  ELSEIF YEAR(emp_hire_date) <=2001 THEN SET add_sal_rate=1.10;
  ELSE  SET add_sal_rate=1.05;
  END IF;
  UPDATE employees SET salary = salary*add_sal_rate WHERE employee_id = emp_id;
  SET init_count = init_count+1;
  END WHILE;
  CLOSE emp_cursor;
END //
DELIMITER ;
CALL update_salary(40,1);

 

DELIMITER // 
CREATE PROCEDURE update_salary(IN dept_id INT,IN change_sal_count INT) 
BEGIN
#声明变量 
DECLARE int_count INT DEFAULT 0;
 DECLARE salary_rate DOUBLE DEFAULT 0.0; 
DECLARE emp_id INT; DECLARE emp_hire_date DATE;
#声明游标 
DECLARE emp_cursor CURSOR FOR SELECT employee_id,hire_date FROM employees WHERE department_id = dept_id ORDER BY salary ; 
#打开游标 OPEN emp_cursor;
WHILE int_count < change_sal_count DO
 #使用游标
 FETCH emp_cursor 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; 
ELSE SET salary_rate = 1.05; 
END IF;
#更新工资
 UPDATE employees SET salary = salary * salary_rate WHERE employee_id = emp_id; 
#迭代条件 
SET int_count = int_count + 1;
 END WHILE;
 #关闭游标 CLOSE emp_cursor;
 END // 
DELIMITER ; 
# 调用
 CALL update_salary(50,2);