一. 变量
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 理解为 breakLEAVE 标记名
举例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);