存储过程与触发器

本章将详细介绍PostgreSQL中的存储过程和触发器，包括如何创建、使用和管理这些数据库对象来实现复杂的业务逻辑。

8.1 存储过程概述

存储过程概念

存储过程是存储在数据库中的可执行代码块，具有以下特点：

预编译：存储过程在创建时被编译，执行效率高
模块化：可以将复杂的业务逻辑封装在存储过程中
安全性：通过存储过程控制数据访问权限
减少网络流量：在服务器端执行，减少客户端和服务器间的数据传输

PostgreSQL过程语言

PostgreSQL支持多种过程语言：

PL/pgSQL：PostgreSQL的过程语言，类似Oracle的PL/SQL
PL/Python：使用Python编写存储过程
PL/Perl：使用Perl编写存储过程
PL/Tcl：使用Tcl编写存储过程

8.2 PL/pgSQL基础

基本语法结构

-- PL/pgSQL函数基本结构
CREATE OR REPLACE FUNCTION function_name(parameter_name parameter_type)
RETURNS return_type AS $$
DECLARE
    -- 变量声明
    variable_name variable_type;
BEGIN
    -- 函数体
    -- SQL语句和控制结构
    RETURN return_value;
EXCEPTION
    -- 异常处理
    WHEN condition THEN
        -- 处理逻辑
END;
$$ LANGUAGE plpgsql;

变量声明和使用

CREATE OR REPLACE FUNCTION example_function()
RETURNS VOID AS $$
DECLARE
    user_count INTEGER;
    user_name VARCHAR(100);
    current_time TIMESTAMP := CURRENT_TIMESTAMP;
    avg_price NUMERIC(10,2);
BEGIN
    -- 查询赋值
    SELECT COUNT(*) INTO user_count FROM users;
    
    -- 表达式赋值
    user_name := 'John Doe';
    
    -- 执行SQL语句
    INSERT INTO logs (message, created_at) 
    VALUES ('Function executed', current_time);
    
    RAISE NOTICE 'User count: %, Current time: %', user_count, current_time;
END;
$$ LANGUAGE plpgsql;

8.3 控制结构

条件语句

CREATE OR REPLACE FUNCTION check_user_status(user_id INTEGER)
RETURNS VARCHAR(20) AS $$
DECLARE
    user_active BOOLEAN;
    result VARCHAR(20);
BEGIN
    SELECT is_active INTO user_active FROM users WHERE id = user_id;
    
    IF user_active THEN
        result := 'Active';
    ELSE
        result := 'Inactive';
    END IF;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- 多条件判断
CREATE OR REPLACE FUNCTION get_user_level(score INTEGER)
RETURNS VARCHAR(20) AS $$
DECLARE
    level VARCHAR(20);
BEGIN
    IF score >= 90 THEN
        level := 'Excellent';
    ELSIF score >= 80 THEN
        level := 'Good';
    ELSIF score >= 60 THEN
        level := 'Average';
    ELSE
        level := 'Poor';
    END IF;
    
    RETURN level;
END;
$$ LANGUAGE plpgsql;

循环语句

-- LOOP循环
CREATE OR REPLACE FUNCTION loop_example(max_count INTEGER)
RETURNS INTEGER AS $$
DECLARE
    counter INTEGER := 1;
    total INTEGER := 0;
BEGIN
    LOOP
        total := total + counter;
        counter := counter + 1;
        
        EXIT WHEN counter > max_count;
    END LOOP;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

-- WHILE循环
CREATE OR REPLACE FUNCTION while_example(max_count INTEGER)
RETURNS INTEGER AS $$
DECLARE
    counter INTEGER := 1;
    total INTEGER := 0;
BEGIN
    WHILE counter <= max_count LOOP
        total := total + counter;
        counter := counter + 1;
    END LOOP;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

-- FOR循环
CREATE OR REPLACE FUNCTION for_example(max_count INTEGER)
RETURNS INTEGER AS $$
DECLARE
    total INTEGER := 0;
    i INTEGER;
BEGIN
    FOR i IN 1..max_count LOOP
        total := total + i;
    END LOOP;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

-- FOR查询循环
CREATE OR REPLACE FUNCTION process_users()
RETURNS INTEGER AS $$
DECLARE
    user_record RECORD;
    processed_count INTEGER := 0;
BEGIN
    FOR user_record IN SELECT id, username FROM users WHERE is_active = true LOOP
        -- 处理每个用户
        INSERT INTO user_logs (user_id, action, created_at)
        VALUES (user_record.id, 'processed', CURRENT_TIMESTAMP);
        
        processed_count := processed_count + 1;
    END LOOP;
    
    RETURN processed_count;
END;
$$ LANGUAGE plpgsql;

8.4 游标操作

游标基础

CREATE OR REPLACE FUNCTION cursor_example()
RETURNS TABLE(user_id INTEGER, username VARCHAR(100)) AS $$
DECLARE
    user_cursor CURSOR FOR 
        SELECT id, username FROM users WHERE is_active = true;
    user_record RECORD;
BEGIN
    OPEN user_cursor;
    
    LOOP
        FETCH user_cursor INTO user_record;
        EXIT WHEN NOT FOUND;
        
        -- 返回结果
        user_id := user_record.id;
        username := user_record.username;
        RETURN NEXT;
    END LOOP;
    
    CLOSE user_cursor;
END;
$$ LANGUAGE plpgsql;

游标FOR循环

CREATE OR REPLACE FUNCTION cursor_for_example()
RETURNS INTEGER AS $$
DECLARE
    processed_count INTEGER := 0;
BEGIN
    FOR user_record IN 
        SELECT id, username FROM users WHERE is_active = true
    LOOP
        -- 处理每个用户
        INSERT INTO user_logs (user_id, action, created_at)
        VALUES (user_record.id, 'processed', CURRENT_TIMESTAMP);
        
        processed_count := processed_count + 1;
    END LOOP;
    
    RETURN processed_count;
END;
$$ LANGUAGE plpgsql;

8.5 异常处理

基本异常处理

CREATE OR REPLACE FUNCTION safe_divide(dividend NUMERIC, divisor NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    result NUMERIC;
BEGIN
    IF divisor = 0 THEN
        RAISE EXCEPTION 'Division by zero error';
    END IF;
    
    result := dividend / divisor;
    RETURN result;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Cannot divide by zero';
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE NOTICE 'An error occurred: %', SQLERRM;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

自定义异常

CREATE OR REPLACE FUNCTION validate_user_age(user_id INTEGER, age INTEGER)
RETURNS VOID AS $$
BEGIN
    IF age < 0 OR age > 150 THEN
        RAISE EXCEPTION 'Invalid age: %', age USING ERRCODE = 'invalid_parameter_value';
    END IF;
    
    UPDATE users SET age = age WHERE id = user_id;
EXCEPTION
    WHEN OTHERS THEN
        RAISE LOG 'Error updating user age: %', SQLERRM;
        RAISE EXCEPTION 'Failed to update user age';
END;
$$ LANGUAGE plpgsql;

8.6 触发器

触发器函数

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_user_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建BEFORE触发器
CREATE TRIGGER update_user_timestamp_trigger
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_user_timestamp();

不同类型的触发器

-- BEFORE INSERT触发器
CREATE OR REPLACE FUNCTION set_default_values()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.created_at IS NULL THEN
        NEW.created_at := CURRENT_TIMESTAMP;
    END IF;
    
    IF NEW.is_active IS NULL THEN
        NEW.is_active := true;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_default_values_trigger
    BEFORE INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION set_default_values();

-- AFTER INSERT触发器
CREATE OR REPLACE FUNCTION log_user_creation()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_logs (user_id, action, created_at)
    VALUES (NEW.id, 'created', CURRENT_TIMESTAMP);
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_user_creation_trigger
    AFTER INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION log_user_creation();

-- AFTER UPDATE触发器
CREATE OR REPLACE FUNCTION log_user_update()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.email != NEW.email THEN
        INSERT INTO user_logs (user_id, action, details, created_at)
        VALUES (NEW.id, 'email_changed', 
                'From: ' || OLD.email || ' To: ' || NEW.email, 
                CURRENT_TIMESTAMP);
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_user_update_trigger
    AFTER UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION log_user_update();

8.7 存储过程

创建存储过程

-- PostgreSQL 11+支持CREATE PROCEDURE
CREATE OR REPLACE PROCEDURE create_user(
    p_username VARCHAR(50),
    p_email VARCHAR(255),
    p_first_name VARCHAR(50),
    p_last_name VARCHAR(50)
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO users (username, email, first_name, last_name, created_at)
    VALUES (p_username, p_email, p_first_name, p_last_name, CURRENT_TIMESTAMP);
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

-- 调用存储过程
CALL create_user('newuser', 'newuser@example.com', 'New', 'User');

复杂存储过程示例

CREATE OR REPLACE PROCEDURE process_monthly_orders(p_month DATE)
LANGUAGE plpgsql
AS $$
DECLARE
    user_record RECORD;
    total_sales NUMERIC(10,2) := 0;
    order_count INTEGER := 0;
BEGIN
    -- 创建临时表存储结果
    CREATE TEMP TABLE monthly_summary (
        user_id INTEGER,
        username VARCHAR(100),
        order_count INTEGER,
        total_amount NUMERIC(10,2)
    );
    
    -- 处理每个用户的订单
    FOR user_record IN 
        SELECT u.id, u.username, COUNT(o.id) as cnt, SUM(o.total_amount) as amount
        FROM users u
        LEFT JOIN orders o ON u.id = o.user_id
        WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', p_month)
        GROUP BY u.id, u.username
    LOOP
        INSERT INTO monthly_summary (user_id, username, order_count, total_amount)
        VALUES (user_record.id, user_record.username, user_record.cnt, user_record.amount);
        
        total_sales := total_sales + COALESCE(user_record.amount, 0);
        order_count := order_count + user_record.cnt;
    END LOOP;
    
    -- 生成汇总报告
    INSERT INTO monthly_reports (report_month, total_sales, order_count, created_at)
    VALUES (p_month, total_sales, order_count, CURRENT_TIMESTAMP);
    
    RAISE NOTICE 'Processed % orders with total sales of % for month %', 
                 order_count, total_sales, p_month;
    
    -- 清理临时表
    DROP TABLE monthly_summary;
END;
$$;

-- 调用存储过程
CALL process_monthly_orders('2023-10-01');

8.8 函数与存储过程的区别

主要区别

特性	函数	存储过程
返回值	必须返回值	可选返回值
调用方式	SELECT function_name()	CALL procedure_name()
事务控制	不能包含事务控制语句	可以包含事务控制语句
SQL语句中使用	可以在SQL语句中调用	不能在SQL语句中直接调用
版本支持	所有版本支持	PostgreSQL 11+支持

8.9 管理存储过程和函数

查看存储过程和函数

-- 查看所有函数
SELECT 
    proname AS function_name,
    pg_get_function_arguments(p.oid) AS arguments,
    pg_get_function_result(p.oid) AS return_type,
    prosecdef AS security_definer
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public';

-- 查看特定函数的定义
SELECT pg_get_functiondef('update_user_timestamp'::regproc);

-- 查看触发器
SELECT 
    tgname AS trigger_name,
    relname AS table_name,
    tgtype AS trigger_type,
    tgfoid::regproc AS function_name
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
WHERE NOT tgisinternal;

修改和删除

-- 修改函数
CREATE OR REPLACE FUNCTION function_name()
RETURNS return_type AS $$
-- 新的函数体
$$ LANGUAGE plpgsql;

-- 删除函数
DROP FUNCTION IF EXISTS function_name(argument_types);

-- 删除触发器
DROP TRIGGER IF EXISTS trigger_name ON table_name;

-- 删除触发器函数
DROP FUNCTION IF EXISTS trigger_function_name();

8.10 最佳实践

性能优化

避免在循环中执行SQL：尽量使用集合操作代替循环
合理使用游标：只在必要时使用游标，优先考虑FOR循环
异常处理：适当使用异常处理，但不要过度使用
事务管理：在存储过程中合理管理事务

安全性

输入验证：对所有输入参数进行验证
权限控制：使用适当的权限设置
SQL注入防护：使用参数化查询
日志记录：记录重要的操作日志

实际应用示例

-- 创建订单处理函数
CREATE OR REPLACE FUNCTION process_order(
    p_user_id INTEGER,
    p_products JSONB
)
RETURNS INTEGER AS $$
DECLARE
    order_id INTEGER;
    product_item JSONB;
    product_id INTEGER;
    quantity INTEGER;
    price NUMERIC(10,2);
    total_amount NUMERIC(10,2) := 0;
BEGIN
    -- 开始事务
    BEGIN
        -- 创建订单
        INSERT INTO orders (user_id, total_amount, status, created_at)
        VALUES (p_user_id, 0, 'pending', CURRENT_TIMESTAMP)
        RETURNING id INTO order_id;
        
        -- 处理每个产品
        FOR product_item IN SELECT jsonb_array_elements(p_products)
        LOOP
            product_id := (product_item->>'product_id')::INTEGER;
            quantity := (product_item->>'quantity')::INTEGER;
            
            -- 获取产品价格
            SELECT price INTO price FROM products WHERE id = product_id;
            
            -- 检查库存
            IF (SELECT stock_quantity FROM products WHERE id = product_id) < quantity THEN
                RAISE EXCEPTION 'Insufficient stock for product %', product_id;
            END IF;
            
            -- 添加订单项
            INSERT INTO order_items (order_id, product_id, quantity, price)
            VALUES (order_id, product_id, quantity, price);
            
            -- 更新库存
            UPDATE products 
            SET stock_quantity = stock_quantity - quantity 
            WHERE id = product_id;
            
            -- 计算总金额
            total_amount := total_amount + (price * quantity);
        END LOOP;
        
        -- 更新订单总金额
        UPDATE orders 
        SET total_amount = total_amount 
        WHERE id = order_id;
        
        -- 记录日志
        INSERT INTO order_logs (order_id, action, details, created_at)
        VALUES (order_id, 'created', 'Order processed successfully', CURRENT_TIMESTAMP);
        
        -- 提交事务
        RETURN order_id;
    EXCEPTION
        WHEN OTHERS THEN
            -- 回滚事务
            RAISE NOTICE 'Order processing failed: %', SQLERRM;
            RAISE;
    END;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT process_order(1, '[{"product_id": 1, "quantity": 2}, {"product_id": 2, "quantity": 1}]');

提示：存储过程和触发器虽然功能强大，但也要谨慎使用。过度依赖数据库逻辑会增加维护难度，建议将复杂的业务逻辑放在应用程序层。