09-视图与存储过程

视图与存储过程

本节介绍视图、存储过程、函数和触发器等数据库对象。

视图 (View)

视图是一个虚拟表，基于 SQL 查询定义，不存储实际数据。

创建视图

-- 基本语法
CREATE VIEW 视图名 AS SELECT 语句;
 
-- 示例：员工部门视图
CREATE VIEW v_employee_dept AS
SELECT 
    e.id,
    e.name AS employee_name,
    e.salary,
    d.name AS department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;
 
-- 使用视图
SELECT * FROM v_employee_dept WHERE salary > 15000;

视图选项

-- WITH CHECK OPTION：更新视图时检查是否满足视图条件
CREATE VIEW v_active_users AS
SELECT * FROM users WHERE status = 'active'
WITH CHECK OPTION;
 
-- 通过这个视图只能插入/更新 status='active' 的记录
INSERT INTO v_active_users (name, status) VALUES ('张三', 'active');  -- ✅
INSERT INTO v_active_users (name, status) VALUES ('李四', 'inactive');  -- ❌

修改视图

-- 使用 CREATE OR REPLACE
CREATE OR REPLACE VIEW v_employee_dept AS
SELECT 
    e.id,
    e.name,
    e.salary,
    d.name AS dept_name,
    e.hire_date
FROM employees e
JOIN departments d ON e.department_id = d.id;
 
-- 使用 ALTER VIEW
ALTER VIEW v_employee_dept AS
SELECT * FROM employees WHERE salary > 10000;

删除视图

DROP VIEW v_employee_dept;
DROP VIEW IF EXISTS v_employee_dept;

查看视图

-- 查看所有视图
SHOW FULL TABLES WHERE Table_type = 'VIEW';
 
-- 查看视图定义
SHOW CREATE VIEW v_employee_dept;

视图的优缺点

优点：

• 简化复杂查询
• 提供数据安全（隐藏敏感列）
• 逻辑数据独立性

缺点：

• 性能开销（每次查询都要执行基础 SQL）
• 更新限制（复杂视图不可更新）

存储过程 (Stored Procedure)

存储过程是预编译的 SQL 语句集合，存储在数据库中，可重复调用。

创建存储过程

-- 基本语法
DELIMITER //
 
CREATE PROCEDURE 过程名(参数列表)
BEGIN
    SQL 语句;
END //
 
DELIMITER ;
 
-- 示例：查询员工数量
DELIMITER //
 
CREATE PROCEDURE get_employee_count()
BEGIN
    SELECT COUNT(*) AS total FROM employees;
END //
 
DELIMITER ;
 
-- 调用存储过程
CALL get_employee_count();

参数类型

类型	说明
IN	输入参数（默认）
OUT	输出参数
INOUT	输入输出参数

DELIMITER //
 
-- IN 参数：按部门查询员工
CREATE PROCEDURE get_employees_by_dept(IN dept_id INT)
BEGIN
    SELECT * FROM employees WHERE department_id = dept_id;
END //
 
-- OUT 参数：获取员工数量
CREATE PROCEDURE count_employees(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM employees;
END //
 
-- INOUT 参数：薪资调整
CREATE PROCEDURE adjust_salary(INOUT salary DECIMAL(10,2), IN rate DECIMAL(3,2))
BEGIN
    SET salary = salary * (1 + rate);
END //
 
DELIMITER ;
 
-- 调用示例
CALL get_employees_by_dept(1);
 
CALL count_employees(@total);
SELECT @total;
 
SET @salary = 10000;
CALL adjust_salary(@salary, 0.1);
SELECT @salary;  -- 11000

变量与控制结构

DELIMITER //
 
CREATE PROCEDURE example_procedure()
BEGIN
    -- 声明变量
    DECLARE v_count INT DEFAULT 0;
    DECLARE v_name VARCHAR(50);
    DECLARE v_done INT DEFAULT FALSE;
    
    -- 变量赋值
    SET v_count = 10;
    SELECT name INTO v_name FROM employees WHERE id = 1;
    
    -- IF 条件
    IF v_count > 5 THEN
        SELECT 'Count is greater than 5';
    ELSEIF v_count > 0 THEN
        SELECT 'Count is between 1 and 5';
    ELSE
        SELECT 'Count is 0';
    END IF;
    
    -- CASE 语句
    CASE v_count
        WHEN 0 THEN SELECT 'Zero';
        WHEN 1 THEN SELECT 'One';
        ELSE SELECT 'Other';
    END CASE;
    
    -- WHILE 循环
    WHILE v_count > 0 DO
        SET v_count = v_count - 1;
    END WHILE;
    
    -- LOOP 循环
    simple_loop: LOOP
        SET v_count = v_count + 1;
        IF v_count >= 10 THEN
            LEAVE simple_loop;
        END IF;
    END LOOP simple_loop;
    
    -- REPEAT 循环
    REPEAT
        SET v_count = v_count - 1;
    UNTIL v_count <= 0
    END REPEAT;
    
END //
 
DELIMITER ;

游标 (Cursor)

游标用于逐行处理查询结果。

DELIMITER //
 
CREATE PROCEDURE process_employees()
BEGIN
    DECLARE v_id INT;
    DECLARE v_name VARCHAR(50);
    DECLARE v_done INT DEFAULT FALSE;
    
    -- 声明游标
    DECLARE emp_cursor CURSOR FOR
        SELECT id, name FROM employees;
    
    -- 声明结束处理器
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    -- 打开游标
    OPEN emp_cursor;
    
    -- 遍历
    read_loop: LOOP
        FETCH emp_cursor INTO v_id, v_name;
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        -- 处理每行数据
        SELECT CONCAT('Processing: ', v_id, ' - ', v_name);
    END LOOP;
    
    -- 关闭游标
    CLOSE emp_cursor;
END //
 
DELIMITER ;

错误处理

DELIMITER //
 
CREATE PROCEDURE safe_insert(IN p_name VARCHAR(50))
BEGIN
    -- 声明异常处理
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Error occurred, transaction rolled back';
    END;
    
    START TRANSACTION;
    
    INSERT INTO users (name) VALUES (p_name);
    
    COMMIT;
    SELECT 'Insert successful';
END //
 
DELIMITER ;

管理存储过程

-- 查看存储过程
SHOW PROCEDURE STATUS WHERE Db = 'database_name';
SHOW CREATE PROCEDURE procedure_name;
 
-- 删除存储过程
DROP PROCEDURE IF EXISTS procedure_name;

函数 (Function)

函数与存储过程类似，但必须返回一个值。

创建函数

DELIMITER //
 
CREATE FUNCTION get_employee_name(emp_id INT)
RETURNS VARCHAR(50)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE emp_name VARCHAR(50);
    SELECT name INTO emp_name FROM employees WHERE id = emp_id;
    RETURN emp_name;
END //
 
DELIMITER ;
 
-- 使用函数
SELECT get_employee_name(1);
SELECT id, get_employee_name(id) AS name FROM orders;

函数特性

-- DETERMINISTIC: 相同输入总是返回相同输出
-- NOT DETERMINISTIC: 可能返回不同结果（如使用 NOW()）
-- READS SQL DATA: 只读取数据
-- MODIFIES SQL DATA: 会修改数据
-- NO SQL: 不包含 SQL 语句
-- CONTAINS SQL: 包含 SQL 但不读写数据

函数 vs 存储过程

特性	函数	存储过程
返回值	必须返回单个值	可以没有或多个 OUT 参数
调用方式	SELECT 中使用	CALL 调用
事务	不能使用 COMMIT/ROLLBACK	可以
用途	计算和转换	业务逻辑

触发器 (Trigger)

触发器在指定事件（INSERT、UPDATE、DELETE）发生时自动执行。

创建触发器

-- 语法
CREATE TRIGGER 触发器名
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON 表名
FOR EACH ROW
BEGIN
    触发器逻辑;
END;
 
-- 示例：记录用户操作日志
DELIMITER //
 
CREATE TRIGGER tr_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, action, created_at)
    VALUES (NEW.id, 'INSERT', NOW());
END //
 
CREATE TRIGGER tr_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, action, old_data, new_data, created_at)
    VALUES (NEW.id, 'UPDATE', 
            CONCAT('name:', OLD.name, ',email:', OLD.email),
            CONCAT('name:', NEW.name, ',email:', NEW.email),
            NOW());
END //
 
CREATE TRIGGER tr_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, action, old_data, created_at)
    VALUES (OLD.id, 'DELETE', 
            CONCAT('name:', OLD.name, ',email:', OLD.email),
            NOW());
END //
 
DELIMITER ;

NEW 和 OLD

触发事件	NEW	OLD
INSERT	新插入的行	不可用
UPDATE	更新后的行	更新前的行
DELETE	不可用	被删除的行

常见应用场景

-- 1. 自动更新时间戳
DELIMITER //
 
CREATE TRIGGER tr_update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END //
 
DELIMITER ;
 
-- 2. 数据校验
DELIMITER //
 
CREATE TRIGGER tr_check_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END //
 
DELIMITER ;
 
-- 3. 级联更新
DELIMITER //
 
CREATE TRIGGER tr_update_order_total
AFTER UPDATE ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders 
    SET total_amount = (
        SELECT SUM(quantity * unit_price) 
        FROM order_items 
        WHERE order_id = NEW.order_id
    )
    WHERE id = NEW.order_id;
END //
 
DELIMITER ;

管理触发器

-- 查看触发器
SHOW TRIGGERS;
SHOW CREATE TRIGGER trigger_name;
 
-- 删除触发器
DROP TRIGGER IF EXISTS trigger_name;

总结

对象	用途	特点
视图	简化查询、数据安全	虚拟表，不存储数据
存储过程	封装业务逻辑	可有多个参数，无返回值
函数	计算和转换	必须返回单个值
触发器	自动响应数据变化	事件驱动，自动执行

使用建议

1. 视图：用于简化复杂查询和权限控制
2. 存储过程：封装复杂的业务逻辑
3. 函数：封装可复用的计算逻辑
4. 触发器：审计日志、数据校验、级联更新

---

上一节：08-事务与并发控制 下一节：10-SQL性能优化 — 查询优化与索引调优

---

sql 视图 存储过程 触发器 函数