SQL存储过程

SQL 存储过程 (Stored Procedure)

存储过程是预编译的SQL语句集合，存储在数据库中，可以通过名称调用执行。它们可以接受参数、包含流程控制语句，并提高应用程序的性能和安全性。

存储过程的基本语法

创建存储过程

sql

	CREATE PROCEDURE procedure_name([parameters])
	BEGIN
	-- SQL语句和流程控制
	END;

调用存储过程

sql

CALL procedure_name([parameters]);

修改存储过程

sql

	ALTER PROCEDURE procedure_name([parameters])
	BEGIN
	-- 修改后的SQL语句和流程控制
	END;

删除存储过程

sql

DROP PROCEDURE IF EXISTS procedure_name;

存储过程参数

存储过程可以接受三种类型的参数：

1. IN（默认）：输入参数
2. OUT：输出参数
3. INOUT：既是输入也是输出参数

sql

	CREATE PROCEDURE example_proc(
	IN param1 INT,
	OUT param2 VARCHAR(50),
	INOUT param3 DECIMAL(10,2)
	)
	BEGIN
	-- 过程体
	END;

存储过程示例

示例1：简单存储过程

sql

	CREATE PROCEDURE GetEmployeeCount(OUT emp_count INT)
	BEGIN
	SELECT COUNT(*) INTO emp_count FROM employees;
	END;
	-- 调用
	CALL GetEmployeeCount(@count);
	SELECT @count AS employee_count;

示例2：带条件的存储过程

sql

	CREATE PROCEDURE GetEmployeesByDept(IN dept_name VARCHAR(50))
	BEGIN
	SELECT e.*
	FROM employees e
	JOIN dept_emp de ON e.emp_no = de.emp_no
	JOIN departments d ON de.dept_no = d.dept_no
	WHERE d.dept_name = dept_name;
	END;
	-- 调用
	CALL GetEmployeesByDept('Sales');

示例3：带事务处理的存储过程

sql

	CREATE PROCEDURE UpdateEmployeeSalary(
	IN emp_id INT,
	IN salary_increase DECIMAL(10,2),
	OUT new_salary DECIMAL(10,2)
	)
	BEGIN
	DECLARE current_salary DECIMAL(10,2);
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
	ROLLBACK;
	SELECT 'An error occurred, transaction rolled back';
	END;
	START TRANSACTION;
	SELECT salary INTO current_salary FROM salaries
	WHERE emp_no = emp_id AND to_date = '9999-01-01';
	IF current_salary IS NOT NULL THEN
	UPDATE salaries
	SET salary = salary + salary_increase,
	to_date = CURRENT_DATE()
	WHERE emp_no = emp_id AND to_date = '9999-01-01';
	INSERT INTO salaries (emp_no, salary, from_date, to_date)
	VALUES (emp_id, current_salary + salary_increase, CURRENT_DATE(), '9999-01-01');
	SET new_salary = current_salary + salary_increase;
	END IF;
	COMMIT;
	END;
	-- 调用
	CALL UpdateEmployeeSalary(10001, 5000.00, @new_salary);
	SELECT @new_salary;

流程控制语句

存储过程支持多种流程控制语句：

IF 语句

sql

	IF condition THEN
	statements;
	ELSEIF condition THEN
	statements;
	ELSE
	statements;
	END IF;

CASE 语句

sql

	CASE case_value
	WHEN when_value1 THEN statements
	WHEN when_value2 THEN statements
	...
	ELSE statements
	END CASE;

LOOP 循环

sql

	[begin_label:] LOOP
	statements;
	IF condition THEN
	LEAVE [begin_label];
	END IF;
	END LOOP [begin_label];

WHILE 循环

sql

	WHILE condition DO
	statements;
	END WHILE;

REPEAT 循环

sql

	REPEAT
	statements;
	UNTIL condition
	END REPEAT;

存储过程的优点

1. 性能提升：预编译执行，减少解析和优化时间
2. 减少网络流量：只需传递过程调用而非多条SQL语句
3. 增强安全性：可以限制用户直接访问表，只允许通过存储过程访问
4. 代码重用：多个应用可以共享同一个存储过程
5. 维护方便：修改存储过程即可影响所有调用它的应用

不同数据库的存储过程语法差异

特性	MySQL/MariaDB	SQL Server	Oracle	PostgreSQL
参数默认值	支持	支持	支持	支持
异常处理	DECLARE HANDLER	TRY/CATCH	异常块	BEGIN...EXCEPTION
游标支持	支持	支持	支持	支持
返回多个结果集	支持	支持	支持	支持
调试工具	有限	强大	强大	有限

最佳实践

1. 为存储过程使用有意义的命名约定
2. 添加注释说明存储过程的用途和参数
3. 避免在存储过程中编写过于复杂的逻辑
4. 考虑错误处理和事务管理
5. 定期审查和优化存储过程性能
6. 记录存储过程的依赖关系和版本变更

存储过程是数据库编程的强大工具，合理使用可以显著提高应用程序的性能和安全性。