MySQL存储过程实战SQL脚本

/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50709 Source Host : localhost:3306 Source Schema : test Target Server Type : MySQL Target Server Version : 50709 File Encoding : 65001 Date: 21/07/2023 19:47:52 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for employees -- ---------------------------- DROP TABLE IF EXISTS `employees`; CREATE TABLE `employees` ( `id` int(11) NOT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, `salary` decimal(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of employees -- ---------------------------- INSERT INTO `employees` VALUES (1, '张三', 25, 5500.00); INSERT INTO `employees` VALUES (2, '李四', 30, 6600.00); INSERT INTO `employees` VALUES (3, '王五', 35, 7700.00); INSERT INTO `employees` VALUES (4, '赵六', 40, 8800.00); INSERT INTO `employees` VALUES (5, '钱七', 45, 9900.00); -- ---------------------------- -- Procedure structure for categorizeEmployee -- ---------------------------- DROP PROCEDURE IF EXISTS `categorizeEmployee`; delimiter ;; CREATE PROCEDURE `categorizeEmployee`() BEGIN SELECT id, name, CASE WHEN name = '张三' THEN '管理人员' WHEN name = '李四' THEN '管理人员' ELSE '普通员工' END AS category FROM employees; END ;; delimiter ; -- ---------------------------- -- Procedure structure for categorizeEmployees -- ---------------------------- DROP PROCEDURE IF EXISTS `categorizeEmployees`; delimiter ;; CREATE PROCEDURE `categorizeEmployees`() BEGIN SELECT id, name, CASE WHEN position = '张三' THEN '管理人员' WHEN position = '李四' THEN '管理人员' ELSE '普通员工' END AS category FROM employees; END ;; delimiter ; -- ---------------------------- -- Procedure structure for DisplayEmployeesWithLoop -- ---------------------------- DROP PROCEDURE IF EXISTS `DisplayEmployeesWithLoop`; delimiter ;; CREATE PROCEDURE `DisplayEmployeesWithLoop`() BEGIN DECLARE emp_count INT; DECLARE i INT DEFAULT 1; DECLARE emp_id INT; DECLARE emp_name VARCHAR(50); DECLARE emp_age INT; DECLARE emp_salary DECIMAL(10, 2); -- 获取员工数量 SELECT COUNT(*) INTO emp_count FROM employees; -- 循环遍历并输出员工信息 emp_loop: LOOP -- 获取当前员工信息 SELECT id, name, age, salary INTO emp_id, emp_name, emp_age, emp_salary FROM employees WHERE id = i; -- 输出员工信息 SELECT CONCAT('ID: ', emp_id, ', Name: ', emp_name, ', Age: ', emp_age, ', Salary: ', emp_salary) AS employee_info; SET i = i + 1; IF i > emp_count THEN LEAVE emp_loop; END IF; END LOOP; END ;; delimiter ; -- ---------------------------- -- Procedure structure for DisplayEmployeesWithRepeatUntil -- ---------------------------- DROP PROCEDURE IF EXISTS `DisplayEmployeesWithRepeatUntil`; delimiter ;; CREATE PROCEDURE `DisplayEmployeesWithRepeatUntil`() BEGIN DECLARE emp_count INT; DECLARE i INT DEFAULT 1; DECLARE emp_id INT; DECLARE emp_name VARCHAR(50); DECLARE emp_age INT; DECLARE emp_salary DECIMAL(10, 2); -- 获取员工数量 SELECT COUNT(*) INTO emp_count FROM employees; -- 循环遍历并输出员工信息 REPEAT -- 获取当前员工信息 SELECT id, name, age, salary INTO emp_id, emp_name, emp_age, emp_salary FROM employees WHERE id = i; -- 输出员工信息 SELECT CONCAT('ID: ', emp_id, ', Name: ', emp_name, ', Age: ', emp_age, ', Salary: ', emp_salary) AS employee_info; SET i = i + 1; UNTIL i > emp_count END REPEAT; END ;; delimiter ; -- ---------------------------- -- Procedure structure for getAllEmployees -- ---------------------------- DROP PROCEDURE IF EXISTS `getAllEmployees`; delimiter ;; CREATE PROCEDURE `getAllEmployees`() BEGIN SELECT * FROM employees; END ;; delimiter ; -- ---------------------------- -- Procedure structure for getEmployeeById -- ---------------------------- DROP PROCEDURE IF EXISTS `getEmployeeById`; delimiter ;; CREATE PROCEDURE `getEmployeeById`(IN empId INT) BEGIN SELECT * FROM employees WHERE id = empId; END ;; delimiter ; -- ---------------------------- -- Procedure structure for getEmployeeSalaryGrade -- ---------------------------- DROP PROCEDURE IF EXISTS `getEmployeeSalaryGrade`; delimiter ;; CREATE PROCEDURE `getEmployeeSalaryGrade`(IN empId INT) BEGIN DECLARE empSalary DECIMAL(10, 2); DECLARE empGrade VARCHAR(10); SELECT salary INTO empSalary FROM employees WHERE id = empId; IF empSalary < 5000 THEN SET empGrade = '低级'; ELSEIF empSalary >= 5000 AND empSalary < 8000 THEN SET empGrade = '中级'; ELSE SET empGrade = '高级'; END IF; SELECT empGrade AS grade; END ;; delimiter ; -- ---------------------------- -- Procedure structure for IncreaseSalary -- ---------------------------- DROP PROCEDURE IF EXISTS `IncreaseSalary`; delimiter ;; CREATE PROCEDURE `IncreaseSalary`() BEGIN DECLARE emp_count INT; DECLARE i INT DEFAULT 0; DECLARE emp_id INT; DECLARE emp_salary DECIMAL(10, 2); -- 获取员工数量 SELECT COUNT(*) INTO emp_count FROM employees; -- 循环遍历并更新薪水 WHILE i < emp_count DO SET i = i + 1; -- 获取当前员工的id和薪水 SELECT id, salary INTO emp_id, emp_salary FROM employees WHERE id = i; -- 对薪水进行增加 UPDATE employees SET salary = emp_salary * 1.1 WHERE id = emp_id; END WHILE; END ;; delimiter ; SET FOREIGN_KEY_CHECKS = 1;