跳到主要内容

PostgreSQL 存储过程和函数

PostgreSQL 提供了强大的存储过程和函数功能，允许你在数据库服务器端封装业务逻辑。本章将介绍如何创建和使用存储过程、函数，以及 PostgreSQL 特有的 PL/pgSQL 语言。

函数与存储过程的区别​

在 PostgreSQL 中，函数（Function）和存储过程（Procedure）有以下区别：

特性函数存储过程
返回值必须返回值可以不返回值
事务控制不能包含 COMMIT/ROLLBACK可以包含事务控制语句
调用方式SELECT func()CALL proc()
SQL 语句中可以在 SELECT 中使用不能在 SQL 语句中使用
参数模式IN、OUT、INOUTIN、OUT、INOUT

PL/pgSQL 语言​

PL/pgSQL 是 PostgreSQL 的过程语言，是编写函数和存储过程的首选语言。

基本结构​

CREATE [OR REPLACE] FUNCTION function_name(param1 type, param2 type)
RETURNS return_type AS $$
DECLARE
    -- 变量声明
    variable_name data_type;
BEGIN
    -- 函数体
    RETURN value;
END;
$$ LANGUAGE plpgsql;

基本函数示例​

-- 简单的加法函数
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT add_numbers(10, 20);  -- 结果: 30

变量和数据类型​

变量声明​

CREATE OR REPLACE FUNCTION variable_demo()
RETURNS VOID AS $$
DECLARE
    -- 基本类型声明
    user_name VARCHAR(50);
    age INTEGER := 25;  -- 带默认值
    salary NUMERIC(10, 2) DEFAULT 0.00;
    is_active BOOLEAN := TRUE;
    
    -- 使用表字段类型
    customer_record customers%ROWTYPE;  -- 整行类型
    customer_name customers.name%TYPE;   -- 字段类型
    
    -- 常量
    max_size CONSTANT INTEGER := 100;
    
    -- 行类型变量
    user_data RECORD;
BEGIN
    -- 赋值
    user_name := '张三';
    age := age + 1;
    
    -- 打印调试信息（在客户端显示）
    RAISE NOTICE '用户名: %, 年龄: %', user_name, age;
END;
$$ LANGUAGE plpgsql;

变量作用域​

CREATE OR REPLACE FUNCTION scope_demo()
RETURNS INTEGER AS $$
DECLARE
    outer_var INTEGER := 10;
BEGIN
    -- 外部变量可见
    RAISE NOTICE '外部变量: %', outer_var;
    
    -- 嵌套块
    DECLARE
        inner_var INTEGER := 20;
        outer_var INTEGER := 30;  -- 遮蔽外部变量
    BEGIN
        RAISE NOTICE '内部变量: %, 遮蔽的外部变量: %', inner_var, outer_var;
    END;
    
    -- 外部变量不变
    RAISE NOTICE '回到外部: %', outer_var;
    
    RETURN outer_var;
END;
$$ LANGUAGE plpgsql;

控制结构​

条件语句​

CREATE OR REPLACE FUNCTION get_grade(score NUMERIC)
RETURNS VARCHAR AS $$
BEGIN
    IF score >= 90 THEN
        RETURN 'A';
    ELSIF score >= 80 THEN
        RETURN 'B';
    ELSIF score >= 70 THEN
        RETURN 'C';
    ELSIF score >= 60 THEN
        RETURN 'D';
    ELSE
        RETURN 'F';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- CASE 语句
CREATE OR REPLACE FUNCTION get_status_text(status INTEGER)
RETURNS VARCHAR AS $$
BEGIN
    RETURN CASE status
        WHEN 0 THEN '待处理'
        WHEN 1 THEN '处理中'
        WHEN 2 THEN '已完成'
        WHEN 3 THEN '已取消'
        ELSE '未知状态'
    END;
END;
$$ LANGUAGE plpgsql;

循环语句​

-- 基本循环
CREATE OR REPLACE FUNCTION loop_demo(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    sum_val INTEGER := 0;
    i INTEGER := 1;
BEGIN
    LOOP
        sum_val := sum_val + i;
        i := i + 1;
        EXIT WHEN i > n;  -- 退出条件
    END LOOP;
    RETURN sum_val;
END;
$$ LANGUAGE plpgsql;

-- WHILE 循环
CREATE OR REPLACE FUNCTION while_demo(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    sum_val INTEGER := 0;
    i INTEGER := 1;
BEGIN
    WHILE i <= n LOOP
        sum_val := sum_val + i;
        i := i + 1;
    END LOOP;
    RETURN sum_val;
END;
$$ LANGUAGE plpgsql;

-- FOR 循环（整数范围）
CREATE OR REPLACE FUNCTION for_demo(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    sum_val INTEGER := 0;
BEGIN
    FOR i IN 1..n LOOP
        sum_val := sum_val + i;
    END LOOP;
    RETURN sum_val;
END;
$$ LANGUAGE plpgsql;

-- FOR 循环（遍历查询结果）
CREATE OR REPLACE FUNCTION process_orders()
RETURNS VOID AS $$
DECLARE
    order_record RECORD;
BEGIN
    FOR order_record IN 
        SELECT id, customer_id, total_amount 
        FROM orders 
        WHERE status = 'pending'
    LOOP
        -- 处理每个订单
        RAISE NOTICE '处理订单 %, 金额: %', 
            order_record.id, order_record.total_amount;
        
        -- 更新订单状态
        UPDATE orders SET status = 'processing' 
        WHERE id = order_record.id;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- FOREACH 循环（遍历数组）
CREATE OR REPLACE FUNCTION array_demo(arr INTEGER[])
RETURNS INTEGER AS $$
DECLARE
    total INTEGER := 0;
    val INTEGER;
BEGIN
    FOREACH val IN ARRAY arr LOOP
        total := total + val;
    END LOOP;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

SELECT array_demo(ARRAY[1, 2, 3, 4, 5]);  -- 结果: 15

CONTINUE 语句​

-- 跳过特定迭代
CREATE OR REPLACE FUNCTION skip_even_numbers(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    sum_val INTEGER := 0;
BEGIN
    FOR i IN 1..n LOOP
        -- 跳过偶数
        IF i % 2 = 0 THEN
            CONTINUE;
        END IF;
        sum_val := sum_val + i;
    END LOOP;
    RETURN sum_val;
END;
$$ LANGUAGE plpgsql;

参数模式​

IN 参数（默认）​

CREATE OR REPLACE FUNCTION greet(name VARCHAR)
RETURNS VARCHAR AS $$
BEGIN
    RETURN '你好, ' || name || '!';
END;
$$ LANGUAGE plpgsql;

SELECT greet('张三');  -- 你好, 张三!

OUT 参数​

CREATE OR REPLACE FUNCTION get_user_info(
    user_id INTEGER,
    OUT user_name VARCHAR,
    OUT user_email VARCHAR
)
AS $$
BEGIN
    SELECT name, email INTO user_name, user_email
    FROM users WHERE id = user_id;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_user_info(1);

INOUT 参数​

CREATE OR REPLACE FUNCTION double_value(INOUT val INTEGER)
AS $$
BEGIN
    val := val * 2;
END;
$$ LANGUAGE plpgsql;

SELECT double_value(5);  -- 10

多个 OUT 参数返回记录​

CREATE OR REPLACE FUNCTION get_order_summary(
    p_customer_id INTEGER,
    OUT total_orders INTEGER,
    OUT total_amount NUMERIC,
    OUT avg_amount NUMERIC
)
AS $$
BEGIN
    SELECT 
        COUNT(*),
        COALESCE(SUM(total_amount), 0),
        COALESCE(AVG(total_amount), 0)
    INTO total_orders, total_amount, avg_amount
    FROM orders
    WHERE customer_id = p_customer_id;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_order_summary(1);

返回集合​

RETURNS SETOF​

-- 返回多行
CREATE OR REPLACE FUNCTION get_high_value_customers(min_total NUMERIC)
RETURNS SETOF customers AS $$
BEGIN
    RETURN QUERY
    SELECT c.*
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id
    HAVING SUM(o.total_amount) >= min_total;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_high_value_customers(500);

RETURNS TABLE​

-- 返回表格形式的结果
CREATE OR REPLACE FUNCTION get_customer_stats()
RETURNS TABLE(
    customer_id INTEGER,
    customer_name VARCHAR,
    order_count BIGINT,
    total_spent NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        c.id,
        c.name,
        COUNT(o.id),
        COALESCE(SUM(o.total_amount), 0)
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name
    ORDER BY total_spent DESC;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_customer_stats();

异常处理​

基本异常捕获​

CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE '除数不能为零';
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

SELECT safe_divide(10, 0);  -- 返回 NULL 并打印通知

事务控制​

CREATE OR REPLACE FUNCTION transfer_funds(
    from_account INTEGER,
    to_account INTEGER,
    amount NUMERIC
)
RETURNS BOOLEAN AS $$
BEGIN
    -- 检查余额
    IF (SELECT balance FROM accounts WHERE id = from_account) < amount THEN
        RAISE EXCEPTION '余额不足';
    END IF;
    
    -- 扣款
    UPDATE accounts SET balance = balance - amount 
    WHERE id = from_account;
    
    -- 存款
    UPDATE accounts SET balance = balance + amount 
    WHERE id = to_account;
    
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE '转账失败: %', SQLERRM;
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

自定义异常​

CREATE OR REPLACE FUNCTION validate_order(
    p_customer_id INTEGER,
    p_amount NUMERIC
)
RETURNS INTEGER AS $$
DECLARE
    v_customer_exists BOOLEAN;
BEGIN
    -- 检查客户是否存在
    SELECT EXISTS(SELECT 1 FROM customers WHERE id = p_customer_id)
    INTO v_customer_exists;
    
    IF NOT v_customer_exists THEN
        RAISE EXCEPTION '客户 % 不存在', p_customer_id
            USING HINT = '请检查客户ID是否正确',
                  ERRCODE = 'P0001';
    END IF;
    
    -- 检查金额是否有效
    IF p_amount <= 0 THEN
        RAISE EXCEPTION '订单金额必须大于零';
    END IF;
    
    -- 创建订单
    INSERT INTO orders (customer_id, total_amount, status)
    VALUES (p_customer_id, p_amount, 'pending')
    RETURNING id INTO validate_order;
    
    RETURN validate_order;
END;
$$ LANGUAGE plpgsql;

存储过程​

创建存储过程​

-- 创建存储过程
CREATE OR REPLACE PROCEDURE process_monthly_report(
    p_year INTEGER,
    p_month INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    report_date DATE;
BEGIN
    report_date := make_date(p_year, p_month, 1);
    
    -- 清理旧报告
    DELETE FROM monthly_reports 
    WHERE report_month = report_date;
    
    -- 生成新报告
    INSERT INTO monthly_reports (report_month, total_sales, order_count)
    SELECT 
        report_date,
        SUM(total_amount),
        COUNT(*)
    FROM orders
    WHERE EXTRACT(YEAR FROM order_date) = p_year
      AND EXTRACT(MONTH FROM order_date) = p_month;
    
    -- 存储过程可以包含事务控制
    COMMIT;
    
    RAISE NOTICE '月度报告已生成: %-%', p_year, p_month;
END;
$$;

-- 调用存储过程
CALL process_monthly_report(2024, 1);

带输出参数的存储过程​

CREATE OR REPLACE PROCEDURE get_customer_balance(
    p_customer_id INTEGER,
    OUT p_balance NUMERIC,
    OUT p_order_count INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT 
        COALESCE(SUM(total_amount), 0),
        COUNT(*)
    INTO p_balance, p_order_count
    FROM orders
    WHERE customer_id = p_customer_id;
END;
$$;

-- 调用
CALL get_customer_balance(1, NULL, NULL);

动态 SQL​

EXECUTE 语句​

CREATE OR REPLACE FUNCTION get_table_count(table_name VARCHAR)
RETURNS BIGINT AS $$
DECLARE
    result BIGINT;
BEGIN
    EXECUTE format('SELECT COUNT(*) FROM %I', table_name)
    INTO result;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT get_table_count('users');

使用参数​

CREATE OR REPLACE FUNCTION search_orders(
    p_column VARCHAR,
    p_value VARCHAR
)
RETURNS TABLE(id INTEGER, total_amount NUMERIC) AS $$
BEGIN
    RETURN QUERY EXECUTE format(
        'SELECT id, total_amount FROM orders WHERE %I = $1',
        p_column
    ) USING p_value;
END;
$$ LANGUAGE plpgsql;

触发器函数​

触发器函数是特殊的函数，用于在表上执行特定操作时自动运行。

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

-- 创建触发器
CREATE TRIGGER trigger_update_modified_time
BEFORE UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION update_modified_time();

游标​

游标用于处理大量数据，可以逐行获取结果。

CREATE OR REPLACE FUNCTION process_large_dataset()
RETURNS VOID AS $$
DECLARE
    cur CURSOR FOR SELECT * FROM large_table;
    rec RECORD;
    batch_count INTEGER := 0;
BEGIN
    OPEN cur;
    
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;
        
        -- 处理每条记录
        -- ...
        
        batch_count := batch_count + 1;
        
        -- 每 1000 条提交一次
        IF batch_count % 1000 = 0 THEN
            COMMIT;
            RAISE NOTICE '已处理 % 条记录', batch_count;
        END IF;
    END LOOP;
    
    CLOSE cur;
    RAISE NOTICE '处理完成，共 % 条记录', batch_count;
END;
$$ LANGUAGE plpgsql;

函数管理​

查看函数定义​

-- 查看函数列表
\df

-- 查看函数定义
\df+ function_name

-- 使用 SQL 查询
SELECT prosrc FROM pg_proc 
WHERE proname = 'function_name';

修改函数​

-- 修改函数定义
CREATE OR REPLACE FUNCTION function_name(...)
...

-- 修改函数所有者
ALTER FUNCTION function_name(INTEGER) OWNER TO new_owner;

-- 重命名函数
ALTER FUNCTION function_name(INTEGER) RENAME TO new_name;

删除函数​

-- 删除函数（需要指定参数类型）
DROP FUNCTION IF EXISTS function_name(INTEGER, VARCHAR);

-- 删除存储过程
DROP PROCEDURE IF EXISTS procedure_name(INTEGER);

性能优化​

使用 SETOF 代替循环​

-- 不好的做法：循环插入
CREATE OR REPLACE FUNCTION bad_generate_series(n INTEGER)
RETURNS VOID AS $$
BEGIN
    FOR i IN 1..n LOOP
        INSERT INTO numbers (value) VALUES (i);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 好的做法：使用集合操作
CREATE OR REPLACE FUNCTION good_generate_series(n INTEGER)
RETURNS VOID AS $$
BEGIN
    INSERT INTO numbers (value)
    SELECT generate_series(1, n);
END;
$$ LANGUAGE plpgsql;

减少上下文切换​

-- 使用 RETURN QUERY 代替循环
CREATE OR REPLACE FUNCTION efficient_query()
RETURNS SETOF orders AS $$
BEGIN
    RETURN QUERY SELECT * FROM orders WHERE status = 'pending';
END;
$$ LANGUAGE plpgsql;

小结​

本章我们学习了：

  1. 函数与存储过程的区别：返回值、事务控制、调用方式
  2. PL/pgSQL 语言：变量声明、控制结构
  3. 参数模式：IN、OUT、INOUT
  4. 返回集合：RETURNS SETOF、RETURNS TABLE
  5. 异常处理：捕获异常、自定义异常
  6. 动态 SQL：EXECUTE 语句
  7. 游标操作：处理大量数据
  8. 性能优化：减少上下文切换

练习​

  1. 创建一个函数，计算两个日期之间的工作日数量
  2. 创建一个存储过程，批量更新订单状态
  3. 创建一个触发器函数，记录用户登录日志
  4. 使用游标处理大数据集

参考资源​