1. 变量
准备工作:
#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`;
SET GLOBAL log_bin_trust_function_creators = 1;
1. 创建函数get_count(),返回公司的员工个数
#无参有返回
#1. 创建函数get_count(),返回公司的员工个数
DELIMITER //
CREATE FUNCTION get_count()
RETURNS INT
BEGIN
# 声明局部变量
DECLARE emp_count INT;
SELECT COUNT(*) INTO emp_count
FROM employees;
RETURN emp_count;
END //
DELIMITER ;
SELECT get_count(); #107
2. 创建函数ename_salary(),根据员工姓名,返回它的工资
#有参有返回
#2. 创建函数ename_salary(),根据员工姓名,返回它的工资
DELIMITER //
CREATE FUNCTION ename_salary(emp_name VARCHAR(25))
RETURNS DOUBLE(8,2)
BEGIN
# 声明局部变量
DECLARE emp_sal DOUBLE(8,2);
SELECT salary INTO emp_sal
FROM employees
WHERE last_name = emp_name;
RETURN emp_sal;
END //
DELIMITER ;
SELECT ename_salary('Abel'); #12000.00
3. 创建函数dept_sal() ,根据部门名,返回该部门的平均工资
#3. 创建函数dept_sal() ,根据部门名,返回该部门的平均工资
DELIMITER //
CREATE FUNCTION dept_sal(dept_name VARCHAR(30))
RETURNS DOUBLE
BEGIN
DECLARE dept_sal DOUBLE;
SELECT AVG(salary) INTO dept_sal
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE department_name = dept_name;
RETURN dept_sal;
END //
DELIMITER ;
SELECT dept_sal('IT'); #5760
4. 创建函数add_float(),实现传入两个float,返回二者之和
#4. 创建函数add_float(),实现传入两个float,返回二者之和
DELIMITER //
CREATE FUNCTION add_float(f1 FLOAT,f2 FLOAT)
RETURNS FLOAT
BEGIN
DECLARE sum FLOAT;
SET sum = f1 + f2;
RETURN sum;
END //
DELIMITER ;
SET @f1 = 12.25;
SET @f2 = 36.55;
SELECT add_float(@f1,@f2); #48.8
2. 流程控制
1. 创建函数test_if_case(),实现传入成绩,如果成绩>90,返回A,如果成绩>80,返回B,如果成绩>60,返回 C,否则返回D ,要求:分别使用if结构和case结构实现
#1. 创建函数test_if_case(),实现传入成绩,
# 如果成绩>90,返回A,如果成绩>80,返回B,如果成绩>60,返回C,否则返回D
# 要求:分别使用if结构和case结构实现
# 方式1:使用if结构实现
DELIMITER //
CREATE FUNCTION test_if(score INT )
RETURNS CHAR
BEGIN
DECLARE grade CHAR;
IF score > 90
THEN SET grade = 'A';
ELSEIF score > 80
THEN SET grade = 'B';
ELSEIF score > 60
THEN SET grade = 'C';
ELSE
SET grade = 'D';
END IF;
RETURN grade;
END //
DELIMITER ;
SELECT test_if(95); #A
SELECT test_if(65); #C
# 方式2:使用case结构实现
DELIMITER //
CREATE FUNCTION test_case(score INT)
RETURNS CHAR
BEGIN
DECLARE grade CHAR;
CASE WHEN score > 90 THEN SET grade = 'A';
WHEN score > 80 THEN SET grade = 'B';
WHEN score > 60 THEN SET grade = 'C';
ELSE SET grade = 'D';
END CASE;
RETURN grade;
END //
DELIMITER ;
SELECT test_case(87); # B
SELECT test_case(56); # D
2. 创建存储过程test_if_pro(),传入工资值,如果工资值<3000,则删除工资为此值的员工,如果3000 <= 工 资值 <= 5000,则修改此工资值的员工薪资涨1000,否则涨工资500
#2. 创建存储过程test_if_pro(),传入工资值,
# 如果工资值<3000,则删除工资为此值的员工,如果3000 <= 工
# 资值 <= 5000,则修改此工资值的员工薪资涨1000,否则涨工资500
DELIMITER //
CREATE PROCEDURE test_if_pro(IN salary DOUBLE(8,2))
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 ;
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;
答案:
#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; # NULL NULL NULL
DELIMITER //
CREATE PROCEDURE insert_data(IN insert_count INT)
BEGIN
DECLARE num INT DEFAULT 1;#记录循环次数
WHILE num <= insert_count DO
INSERT INTO admin(user_name,user_pwd)
VALUES(CONCAT('Tom',num),ROUND(RAND() * 100000000));
SET num = num + 1;
END WHILE;
END //
DELIMITER ;
CALL insert_data(5);
SELECT * FROM admin;
3. 游标的使用
创建存储过程update_salary()
,参数
1
为
IN
的
INT
型变量
dept_id
,表示部门
id
;参数
2
为
IN
的
INT
型变量 change_sal_count,表示要调整薪资的员工个数。查询指定
id
部门的员工信息,按照
salary
升序排列,根 据hire_date
的情况,调整前
change_sal_count
个员工的薪资,详情如下。
# 游标的使用
#创建存储过程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 hire_year DOUBLE; #记录某个员工的入职年份
DECLARE id INT; #记录员工的id
DECLARE num INT DEFAULT 0; #记录循环的次数
#定义游标
DECLARE emp_cursor CURSOR FOR SELECT YEAR(hire_date),employee_id
FROM employees
WHERE department_id = dept_id
ORDER BY salary ASC;
#打开游标
OPEN emp_cursor;
#使用游标
WHILE num <= change_sal_count DO
FETCH emp_cursor INTO hire_year,id;
IF hire_year < 1995
THEN UPDATE employees SET salary = salary * 1.2 WHERE employee_id = id;
ELSEIF hire_year < 1998
THEN UPDATE employees SET salary = salary * 1.15 WHERE employee_id = id;
ELSEIF hire_year < 2001
THEN UPDATE employees SET salary = salary * 1.10 WHERE employee_id = id;
ELSE
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = id;
END IF;
SET num = num + 1;
END WHILE;
# 关闭游标
CLOSE emp_cursor;
END //
DELIMITER ;
SELECT employee_id ,YEAR(hire_date),salary
FROM employees
WHERE department_id = 100
ORDER BY salary ASC;
CALL update_salary(100,3);
SELECT employee_id ,YEAR(hire_date),salary
FROM employees
WHERE department_id = 100
ORDER BY salary ASC;