SQL视图、存储过程和触发器

视图、存储过程和触发器是SQL中的重要数据库对象，用于封装业务逻辑、简化操作和保证数据完整性。它们在数据库开发中有着广泛应用，能够提高代码复用性和系统安全性。

一、视图（View）

视图是从一个或多个表中导出的虚拟表，它本身不存储数据，只保存查询定义。视图可以简化复杂查询，限制数据访问，提供数据独立性。

1. 视图的特点

- 虚拟表，不实际存储数据，数据来源于基础表

- 可以像表一样进行查询操作

- 基表数据变化时，视图数据会自动更新

- 可以限制用户访问的数据范围，提高安全性

2. 视图的创建

CREATE VIEW 视图名 AS
SELECT 字段1, 字段2, ...
FROM 表名
WHERE 条件;

示例：创建员工部门视图

CREATE VIEW employee_department AS
SELECT e.emp_id, e.emp_name, d.dept_name, e.salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

3. 视图的查询

查询视图与查询表的语法相同：

-- 查询销售部员工
SELECT * FROM employee_department
WHERE dept_name = '销售部';-- 统计各部门平均工资
SELECT dept_name, AVG(salary) AS avg_salary
FROM employee_department
GROUP BY dept_name;

4. 视图的修改

-- 方式1：CREATE OR REPLACE
CREATE OR REPLACE VIEW employee_department AS
SELECT e.emp_id, e.emp_name, d.dept_name, e.salary, e.hire_date
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;-- 方式2：ALTER
ALTER VIEW employee_department AS
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

5. 视图的删除

DROP VIEW IF EXISTS employee_department;

6. 视图的使用场景

- 简化复杂查询，将常用的多表查询封装为视图

- 限制用户访问敏感数据（如只暴露部分字段）

- 提供数据独立性，当基表结构变化时，可通过修改视图保持接口稳定

7. 视图的注意事项

- 大部分视图可更新（INSERT/UPDATE/DELETE），但有诸多限制（如不能包含聚合函数、DISTINCT等）

- 复杂视图查询性能可能较差，避免在视图上再创建视图

- 视图不适合频繁更新的场景

二、存储过程（Stored Procedure）

存储过程是预编译并存储在数据库中的一组SQL语句集合，可通过名称和参数调用。它能封装复杂业务逻辑，减少网络传输，提高执行效率。

1. 存储过程的特点

- 预编译存储，多次执行效率高

- 可包含条件判断、循环等复杂逻辑

- 支持输入输出参数，增强灵活性

- 减少应用程序与数据库的交互次数

2. 存储过程的创建

DELIMITER //
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN-- SQL语句集合
END //
DELIMITER ;

参数类型：

- IN：输入参数（默认）

- OUT：输出参数

- INOUT：既可以输入也可以输出

示例1：无参数存储过程（查询所有员工）

DELIMITER //
CREATE PROCEDURE get_all_employees()
BEGINSELECT * FROM employees;
END //
DELIMITER ;

示例2：带输入参数的存储过程（查询指定部门员工）

DELIMITER //
CREATE PROCEDURE get_employees_by_dept(IN dept_id INT)
BEGINSELECT e.emp_id, e.emp_name, d.dept_nameFROM employees eINNER JOIN departments d ON e.dept_id = d.dept_idWHERE e.dept_id = dept_id;
END //
DELIMITER ;

示例3：带输出参数的存储过程（统计部门员工数）

DELIMITER //
CREATE PROCEDURE count_employees_by_dept(IN dept_id INT,OUT emp_count INT
)
BEGINSELECT COUNT(*) INTO emp_countFROM employeesWHERE employees.dept_id = dept_id;
END //
DELIMITER ;

3. 存储过程的调用

-- 调用无参数存储过程
CALL get_all_employees();-- 调用带输入参数的存储过程
CALL get_employees_by_dept(1);-- 调用带输出参数的存储过程
SET @count = 0;
CALL count_employees_by_dept(1, @count);
SELECT @count AS employee_count;

4. 存储过程的修改与删除

-- 修改存储过程（MySQL中需先删除再重建）
DROP PROCEDURE IF EXISTS get_all_employees;-- 删除存储过程
DROP PROCEDURE IF EXISTS get_employees_by_dept;

5. 存储过程中的控制结构

（1）条件判断

DELIMITER //
CREATE PROCEDURE check_salary(IN emp_id INT)
BEGINDECLARE emp_salary DECIMAL(10,2);SELECT salary INTO emp_salary FROM employees WHERE id = emp_id;IF emp_salary > 10000 THENSELECT '高薪员工' AS result;ELSEIF emp_salary > 5000 THENSELECT '中等收入' AS result;ELSESELECT '低收入' AS result;END IF;
END //
DELIMITER ;

（2）循环结构

DELIMITER //
CREATE PROCEDURE insert_test_data(IN count INT)
BEGINDECLARE i INT DEFAULT 1;WHILE i <= count DOINSERT INTO test (name) VALUES (CONCAT('test_', i));SET i = i + 1;END WHILE;
END //
DELIMITER ;

6. 存储过程的使用场景

- 复杂业务逻辑处理（如订单创建、支付流程）

- 批量数据处理（如数据迁移、批量插入）

- 需要多次执行的SQL语句集合

- 增强数据库安全性（限制直接表访问，通过存储过程操作）

三、触发器（Trigger）

触发器是与表关联的特殊存储过程，当表发生特定事件（INSERT、UPDATE、DELETE）时自动执行。它主要用于保证数据完整性和实现复杂业务规则。

1. 触发器的特点

- 自动触发，无需手动调用

- 与特定表和事件关联

- 可在事件发生前（BEFORE）或后（AFTER）执行

- 常用于数据校验、日志记录、级联操作等

2. 触发器的创建

DELIMITER //
CREATE TRIGGER 触发器名
触发时机（BEFORE/AFTER） 触发事件（INSERT/UPDATE/DELETE）
ON 表名 FOR EACH ROW
BEGIN-- 触发器执行的SQL语句
END //
DELIMITER ;

特殊变量：

- NEW：表示新插入或更新后的数据（仅用于INSERT和UPDATE）

- OLD：表示更新前或删除的数据（仅用于UPDATE和DELETE）

示例1：插入前验证数据（确保工资为正数）

DELIMITER //
CREATE TRIGGER check_salary_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGINIF NEW.salary <= 0 THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '工资必须大于0';END IF;
END //
DELIMITER ;

示例2：更新后记录日志（记录工资变动）

DELIMITER //
CREATE TRIGGER log_salary_change
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN-- 当工资发生变化时记录日志IF OLD.salary != NEW.salary THENINSERT INTO salary_logs (emp_id, old_salary, new_salary, change_time)VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());END IF;
END //
DELIMITER ;

示例3：删除时级联操作（删除部门前先处理员工）

DELIMITER //
CREATE TRIGGER handle_department_delete
BEFORE DELETE ON departments
FOR EACH ROW
BEGIN-- 将该部门员工移至默认部门（ID=0）UPDATE employees SET dept_id = 0 WHERE dept_id = OLD.dept_id;
END //
DELIMITER ;

3. 触发器的查看与删除

-- 查看触发器
SHOW TRIGGERS;-- 查看特定表的触发器
SHOW TRIGGERS LIKE 'employees';-- 删除触发器
DROP TRIGGER IF EXISTS check_salary_before_insert;

4. 触发器的使用场景

- 数据校验（如确保字段值符合业务规则）

- 自动记录日志（如数据修改历史）

- 实现级联操作（如删除主表记录时处理关联表）

- 维护衍生数据（如自动计算汇总值）

5. 触发器的注意事项

- 避免在触发器中执行复杂操作，以免影响性能

- 触发器中不能直接引用该表本身，可能导致循环触发

- 过多触发器会使数据逻辑复杂化，增加维护难度

- 触发器执行失败会导致原操作（INSERT/UPDATE/DELETE）失败

四、视图、存储过程和触发器的比较

1. 视图：虚拟表，主要用于简化查询和控制数据访问，无逻辑处理能力

2. 存储过程：需手动调用，可包含复杂逻辑，适合封装业务流程

3. 触发器：自动触发，与表事件关联，适合数据完整性维护和自动响应

五、最佳实践

1. 适度使用：避免过度依赖这些对象，简单逻辑可在应用程序中实现

2. 命名规范：为视图、存储过程和触发器制定清晰的命名规则，如：

- 视图：v_xxx

- 存储过程：sp_xxx

- 触发器：tr_xxx_before_insert

3. 文档化：对复杂的存储过程和触发器进行文档说明，便于维护

4. 测试：修改这些数据库对象后需充分测试，避免影响现有功能

5. 性能监控：定期检查这些对象的执行性能，优化低效代码