6月10日数据库学习笔记

一、过程化 SQL 语句概述
（一）定义与特点
过程化 SQL 语句是指在传统 SQL 语句基础上，增加了程序控制结构（如条件判断、循环、变量声明等）的 SQL 代码块。与单条 SQL 语句相比，过程化 SQL 语句可以实现更复杂的业务逻辑，减少与应用服务器之间的网络交互，提高数据库操作的效率和性能。
（二）优点
代码复用性：将常用的 SQL 逻辑封装成过程或函数，便于在不同程序中复用。
减少网络通信：在数据库服务器端执行复杂的逻辑，减少与客户端之间的数据传输。
提高执行效率：数据库服务器对过程化 SQL 语句进行预编译和优化，提高执行效率。
增强安全性：通过权限控制，限制对过程化 SQL 语句的访问，保护数据库的安全。
二、存储过程
（一）存储过程的定义与作用
存储过程是一组为了完成特定功能的 SQL 语句集合，预先存储在数据库中。用户可以通过指定存储过程的名称并给出参数（如果需要）来调用执行它。
（二）存储过程的创建与管理
创建存储过程
sql

-- MySQL 创建存储过程示例
DELIMITER //
CREATE PROCEDURE GetEmployeeInfo (IN emp_id INT)
BEGIN
SELECT name, position, salary FROM employees WHERE id = emp_id;
END //
DELIMITER ;
sql

-- SQL Server 创建存储过程示例
CREATE PROCEDURE GetEmployeeInfo @emp_id INT
AS
BEGIN
SELECT name, position, salary FROM employees WHERE id = @emp_id;
END
修改存储过程
sql

-- MySQL 修改存储过程示例
DELIMITER //
ALTER PROCEDURE GetEmployeeInfo (IN emp_id INT)
BEGIN
SELECT name, position, salary, department FROM employees WHERE id = emp_id;
END //
DELIMITER ;
sql

-- SQL Server 修改存储过程示例
ALTER PROCEDURE GetEmployeeInfo @emp_id INT
AS
BEGIN
SELECT name, position, salary, department FROM employees WHERE id = @emp_id;
END
删除存储过程
sql

-- MySQL 删除存储过程示例
DROP PROCEDURE GetEmployeeInfo;
sql

-- SQL Server 删除存储过程示例
DROP PROCEDURE GetEmployeeInfo;
（三）存储过程的调用
sql

-- MySQL 调用存储过程示例
CALL GetEmployeeInfo(1001);
sql

-- SQL Server 调用存储过程示例
EXEC GetEmployeeInfo 1001;
（四）带输出参数的存储过程
sql

-- MySQL 带输出参数的存储过程示例
DELIMITER //
CREATE PROCEDURE GetEmployeeSalary (IN emp_id INT, OUT emp_salary DECIMAL(10, 2))
BEGIN
SELECT salary INTO emp_salary FROM employees WHERE id = emp_id;
END //
DELIMITER ;
-- 调用示例
CALL GetEmployeeSalary(1001, @salary);
SELECT @salary;
sql

-- SQL Server 带输出参数的存储过程示例
CREATE PROCEDURE GetEmployeeSalary @emp_id INT, @emp_salary DECIMAL(10, 2) OUTPUT
AS
BEGIN
SELECT @emp_salary = salary FROM employees WHERE id = @emp_id;
END
-- 调用示例
DECLARE @salary DECIMAL(10, 2);
EXEC GetEmployeeSalary 1001, @salary OUTPUT;
SELECT @salary;
三、函数
（一）函数的定义与作用
函数是一种特殊的存储过程，它必须返回一个值。函数可以嵌入在 SQL 语句中使用，类似于内置函数。
（二）函数的创建与管理
创建函数
sql

-- MySQL 创建函数示例
DELIMITER //
CREATE FUNCTION CalculateBonus (salary DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
BEGIN
DECLARE bonus DECIMAL(10, 2);
SET bonus = salary * 0.1;
RETURN bonus;
END //
DELIMITER ;
sql

-- SQL Server 创建函数示例
CREATE FUNCTION CalculateBonus (@salary DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
AS
BEGIN
DECLARE @bonus DECIMAL(10, 2);
SET @bonus = @salary * 0.1;
RETURN @bonus;
END
修改函数
sql

-- MySQL 修改函数示例
DELIMITER //
ALTER FUNCTION CalculateBonus (salary DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
BEGIN
DECLARE bonus DECIMAL(10, 2);
SET bonus = salary * 0.2;
RETURN bonus;
END //
DELIMITER ;
sql

-- SQL Server 修改函数示例
ALTER FUNCTION CalculateBonus (@salary DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
AS
BEGIN
DECLARE @bonus DECIMAL(10, 2);
SET @bonus = @salary * 0.2;
RETURN @bonus;
END
删除函数
sql

-- MySQL 删除函数示例
DROP FUNCTION CalculateBonus;
sql

-- SQL Server 删除函数示例
DROP FUNCTION CalculateBonus;
（三）函数的调用
sql

-- MySQL 调用函数示例
SELECT CalculateBonus(5000);
sql

-- SQL Server 调用函数示例
SELECT dbo.CalculateBonus(5000);
四、条件控制语句
（一）IF 语句
IF 语句用于根据条件执行不同的 SQL 代码块。
sql

-- MySQL IF 语句示例
DELIMITER //
CREATE PROCEDURE CheckEmployeeSalary (IN emp_id INT)
BEGIN
DECLARE salary DECIMAL(10, 2);
SELECT salary INTO salary FROM employees WHERE id = emp_id;
IF salary > 10000 THEN
SELECT 'High Salary' AS salary_level;
ELSE
SELECT 'Normal Salary' AS salary_level;
END IF;
END //
DELIMITER ;
sql

-- SQL Server IF 语句示例
CREATE PROCEDURE CheckEmployeeSalary @emp_id INT
AS
BEGIN
DECLARE @salary DECIMAL(10, 2);
SELECT @salary = salary FROM employees WHERE id = @emp_id;
IF @salary > 10000
SELECT 'High Salary' AS salary_level;
ELSE
SELECT 'Normal Salary' AS salary_level;
END IF;
END
（二）CASE 语句
CASE 语句用于更复杂的条件判断，可以有多个分支条件。
sql

-- MySQL CASE 语句示例
DELIMITER //
CREATE PROCEDURE CheckEmployeePerformance (IN emp_id INT)
BEGIN
DECLARE performance_score INT;
SELECT performance_score INTO performance_score FROM employees WHERE id = emp_id;
CASE
WHEN performance_score >= 90 THEN
SELECT 'Excellent' AS performance_level;
WHEN performance_score >= 70 THEN
SELECT 'Good' AS performance_level;
WHEN performance_score >= 50 THEN
SELECT 'Average' AS performance_level;
ELSE
SELECT 'Poor' AS performance_level;
END CASE;
END //
DELIMITER ;
sql

-- SQL Server CASE 语句示例
CREATE PROCEDURE CheckEmployeePerformance @emp_id INT
AS
BEGIN
DECLARE @performance_score INT;
SELECT @performance_score = performance_score FROM employees WHERE id = @emp_id;
SELECT
CASE
WHEN @performance_score >= 90 THEN 'Excellent'
WHEN @performance_score >= 70 THEN 'Good'
WHEN @performance_score >= 50 THEN 'Average'
ELSE 'Poor'
END AS performance_level;
END
五、循环控制语句
（一）WHILE 循环
WHILE 循环在满足条件时重复执行代码块。
sql

-- MySQL WHILE 循环示例
DELIMITER //
CREATE PROCEDURE PrintNumbers (IN max_num INT)
BEGIN
DECLARE num INT DEFAULT 1;
WHILE num <= max_num DO
SELECT num;
SET num = num + 1;
END WHILE;
END //
DELIMITER ;
sql

-- SQL Server WHILE 循环示例
CREATE PROCEDURE PrintNumbers @max_num INT
AS
BEGIN
DECLARE @num INT = 1;
WHILE @num <= @max_num
BEGIN
SELECT @num;
SET @num = @num + 1;
END
END
（二）LOOP 循环
LOOP 循环无条件循环执行代码块，需配合条件控制语句跳出循环。
sql

-- MySQL LOOP 循环示例
DELIMITER //
CREATE PROCEDURE PrintNumbers (IN max_num INT)
BEGIN
DECLARE num INT DEFAULT 1;
my_loop: LOOP
SELECT num;
SET num = num + 1;
IF num > max_num THEN
LEAVE my_loop;
END IF;
END LOOP;
END //
DELIMITER ;
（三）REPEAT 循环
REPEAT 循环在条件为假时重复执行代码块，直到条件为真时停止。
sql

-- MySQL REPEAT 循环示例
DELIMITER //
CREATE PROCEDURE PrintNumbers (IN max_num INT)
BEGIN
DECLARE num INT DEFAULT 1;
REPEAT
SELECT num;
SET num = num + 1;
UNTIL num > max_num END REPEAT;
END //
DELIMITER ;