SQLite视图、触发器与存储过程

本文档详细介绍 SQLite 视图、触发器和存储过程的创建与使用方法。

视图

什么是视图

视图是一个虚拟表，其内容由查询定义。视图本身不存储数据，而是根据定义查询底层表的数据。

视图的优点

• 简化查询：将复杂查询封装为视图，简化应用程序代码
• 数据安全：只暴露必要的数据，隐藏敏感信息
• 逻辑独立性：当底层表结构变化时，只需修改视图定义，不需要修改应用程序

创建视图

CREATE [TEMPORARY] [VIEW] [IF NOT EXISTS] view_name AS SELECT_statement;

示例

-- 创建简单视图
CREATE VIEW view_active_users AS
SELECT id, name, email
FROM users
WHERE active = 1;

-- 创建复杂视图
CREATE VIEW view_user_orders AS
SELECT u.id AS user_id, u.name, o.id AS order_id, o.product, o.total, o.created_at
FROM users u
JOIN orders o ON u.id = o.user_id
ORDER BY o.created_at DESC;

查看视图

.tables -- 视图会显示在表列表中
.schema view_name

更新视图

-- SQLite 支持通过视图更新底层表，但有一定限制
-- 视图必须满足以下条件才能更新：
-- 1. 视图定义中只包含一个表
-- 2. 视图定义中不包含 DISTINCT、GROUP BY、HAVING、UNION 等

-- 更新视图示例
UPDATE view_active_users
SET name = 'New Name'
WHERE id = 1;

删除视图

DROP VIEW [IF EXISTS] view_name;

触发器

什么是触发器

触发器是一种特殊的存储过程，当指定的事件发生时自动执行。

触发器类型

• BEFORE 触发器：在事件发生前执行
• AFTER 触发器：在事件发生后执行

触发事件

• INSERT：插入数据时触发
• UPDATE：更新数据时触发
• DELETE：删除数据时触发

创建触发器

CREATE [TEMPORARY] TRIGGER [IF NOT EXISTS] trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
[FOR EACH ROW] [WHEN condition]
BEGIN
    -- 触发器逻辑
END;

示例

-- 插入前触发器
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    -- 设置默认值
    IF NEW.created_at IS NULL THEN
        SET NEW.created_at = CURRENT_TIMESTAMP;
    END IF;
    -- 验证数据
    IF NEW.email IS NULL OR NEW.email = '' THEN
        RAISE(ABORT, 'Email is required');
    END IF;
END;

-- 更新后触发器
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    -- 记录日志
    INSERT INTO user_audit_logs (user_id, action, old_value, new_value, changed_at)
    VALUES (OLD.id, 'UPDATE', JSON_OBJECT('email', OLD.email), JSON_OBJECT('email', NEW.email), CURRENT_TIMESTAMP);
END;

-- 删除前触发器
CREATE TRIGGER before_order_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    -- 级联删除相关数据
    DELETE FROM order_items WHERE order_id = OLD.id;
    -- 记录日志
    INSERT INTO order_audit_logs (order_id, action, deleted_at)
    VALUES (OLD.id, 'DELETE', CURRENT_TIMESTAMP);
END;

查看触发器

.schema trigger_name

删除触发器

DROP TRIGGER [IF EXISTS] trigger_name;

存储过程

什么是存储过程

存储过程是一组预编译的 SQL 语句，用于执行特定的操作。

SQLite 对存储过程的支持

SQLite 本身不直接支持存储过程，但可以通过以下方式实现类似功能：

1. 使用触发器：通过触发器实现自动执行的逻辑
2. 使用 SQLite 扩展：安装第三方扩展，如 SQLite3 Multiple Statement
3. 在应用层实现：在应用程序中封装复杂逻辑

使用 SQLite 扩展实现存储过程

-- 加载扩展
.load /path/to/libsqlite3-multiple-statement.so

-- 创建存储过程（模拟）
CREATE PROCEDURE sp_create_user(name TEXT, email TEXT)
BEGIN
    INSERT INTO users (name, email) VALUES (name, email);
    SELECT last_insert_rowid();
END;

-- 调用存储过程（模拟）
CALL sp_create_user('Test User', 'test@example.com');

在应用层实现存储过程

import sqlite3

def sp_create_user(conn, name, email):
    cursor = conn.cursor()
    cursor.execute('INSERT INTO users (name, email) VALUES (?, ?)', (name, email))
    user_id = cursor.lastrowid
    conn.commit()
    return user_id

# 使用示例
conn = sqlite3.connect('database.db')
user_id = sp_create_user(conn, 'Test User', 'test@example.com')
conn.close()

常见问题（FAQ）

Q: SQLite 支持真正的存储过程吗？

A: 不，SQLite 本身不直接支持存储过程，但可以通过触发器、扩展或应用层实现类似功能。

Q: 视图可以更新吗？

A: 是的，但有一定限制，视图必须满足特定条件才能更新。

Q: 如何查看触发器定义？

A: 使用 .schema trigger_name 命令查看触发器定义。

Q: 触发器可以嵌套吗？

A: 是的，一个触发器的执行可以触发另一个触发器，但要注意避免无限循环。