印刻数据库

外观

PostgreSQL函数与存储过程 ​

PostgreSQL支持创建自定义函数和存储过程，用于封装可重用的SQL逻辑。本文将介绍PostgreSQL函数与存储过程的创建和使用。

函数与存储过程概述 ​

函数（Functions） ​

函数是一种数据库对象，用于执行特定的操作并返回结果。函数可以在SQL查询中使用，也可以作为表达式的一部分。

特点：

  • 必须返回一个值或结果集
  • 可以在SELECT语句中调用
  • 可以在其他函数中嵌套调用
  • 支持多种编程语言

存储过程（Procedures） ​

存储过程是一种数据库对象，用于执行一系列操作。与函数不同，存储过程不返回值，而是通过OUT参数或结果集返回数据。

特点：

  • 可以不返回值
  • 支持事务控制（COMMIT/ROLLBACK）
  • 可以通过CALL语句调用
  • 支持INOUT参数

支持的编程语言 ​

PostgreSQL支持多种编程语言编写函数和存储过程：

  • PL/pgSQL：PostgreSQL的默认过程语言，类似于Oracle的PL/SQL
  • PL/Python：使用Python编写函数
  • PL/Perl：使用Perl编写函数
  • PL/Tcl：使用Tcl编写函数
  • C：使用C语言编写函数，性能最高

PL/pgSQL语言基础 ​

PL/pgSQL是PostgreSQL的默认过程语言，它结合了SQL的强大功能和过程语言的灵活性。

基本语法 ​

sql
CREATE [OR REPLACE] FUNCTION function_name (parameter_list)
RETURNS return_type
LANGUAGE plpgsql
AS $$
DECLARE
    -- 变量声明
    variable_name data_type [:= default_value];
BEGIN
    -- 函数体
    -- SQL语句
    RETURN return_value;
EXCEPTION
    -- 异常处理
    WHEN exception_type THEN
        -- 异常处理逻辑
END;
$$;

变量声明与赋值 ​

sql
-- 变量声明
DECLARE
    counter INTEGER := 0;
    total_amount DECIMAL(10, 2);
    user_name VARCHAR(50);
    is_active BOOLEAN := true;

-- 变量赋值
counter := counter + 1;
total_amount := (SELECT SUM(amount) FROM sales WHERE user_id = 1);
SELECT name INTO user_name FROM users WHERE id = 1;

控制结构 ​

IF-THEN-ELSE语句 ​

sql
IF condition THEN
    -- 条件为真时执行的代码
ELSIF condition THEN
    -- 另一个条件为真时执行的代码
ELSE
    -- 所有条件都为假时执行的代码
END IF;

CASE语句 ​

sql
CASE variable
    WHEN value1 THEN
        -- 代码块1
    WHEN value2 THEN
        -- 代码块2
    ELSE
        -- 默认代码块
END CASE;

-- 或
CASE
    WHEN condition1 THEN
        -- 代码块1
    WHEN condition2 THEN
        -- 代码块2
    ELSE
        -- 默认代码块
END CASE;

LOOP语句 ​

sql
-- 基本LOOP
LOOP
    -- 循环体
    EXIT WHEN condition; -- 退出循环条件
END LOOP;

-- WHILE LOOP
WHILE condition LOOP
    -- 循环体
END LOOP;

-- FOR LOOP
FOR counter IN 1..10 LOOP
    -- 循环体，counter从1到10
END LOOP;

-- 反向FOR LOOP
FOR counter IN REVERSE 10..1 LOOP
    -- 循环体，counter从10到1
END LOOP;

异常处理 ​

sql
bEGIN
    -- 可能抛出异常的代码
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Division by zero error';
        RETURN 0;
    WHEN unique_violation THEN
        RAISE EXCEPTION 'Unique constraint violated';
    WHEN OTHERS THEN
        RAISE EXCEPTION 'An error occurred: %', SQLERRM;
END;

创建和使用函数 ​

简单函数示例 ​

sql
-- 创建一个计算两个数之和的函数
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a + b;
END;
$$;

-- 调用函数
SELECT add_numbers(10, 20); -- 返回30

-- 在查询中使用函数
SELECT id, name, add_numbers(quantity, 10) AS new_quantity FROM products;

返回结果集的函数 ​

sql
-- 创建一个返回产品列表的函数
CREATE OR REPLACE FUNCTION get_products_by_category(category_id INTEGER)
RETURNS TABLE(id INTEGER, name VARCHAR(100), price DECIMAL(10, 2))
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT p.id, p.name, p.price
    FROM products p
    WHERE p.category_id = category_id;
END;
$$;

-- 调用返回结果集的函数
SELECT * FROM get_products_by_category(1);

使用OUT参数的函数 ​

sql
-- 创建一个使用OUT参数的函数
CREATE OR REPLACE FUNCTION calculate_stats(
    IN table_name VARCHAR,
    OUT total_rows BIGINT,
    OUT avg_price DECIMAL(10, 2)
)
LANGUAGE plpgsql
AS $$
DECLARE
    query TEXT;
BEGIN
    -- 构建动态SQL查询
    query := format('SELECT COUNT(*), AVG(price) FROM %I', table_name);
    
    -- 执行动态SQL并将结果赋值给OUT参数
    EXECUTE query INTO total_rows, avg_price;
END;
$$;

-- 调用使用OUT参数的函数
SELECT * FROM calculate_stats('products');

带默认值参数的函数 ​

sql
-- 创建一个带默认值参数的函数
CREATE OR REPLACE FUNCTION get_users(
    status VARCHAR DEFAULT 'active',
    limit_count INTEGER DEFAULT 10
)
RETURNS TABLE(id INTEGER, username VARCHAR(50))
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.username
    FROM users u
    WHERE u.status = status
    LIMIT limit_count;
END;
$$;

-- 调用函数，使用默认参数
SELECT * FROM get_users();

-- 调用函数，指定部分参数
SELECT * FROM get_users('inactive');

-- 调用函数，指定所有参数
SELECT * FROM get_users('active', 20);

-- 调用函数，使用命名参数
SELECT * FROM get_users(limit_count => 15, status => 'active');

创建和使用存储过程 ​

PostgreSQL 11引入了真正的存储过程支持，存储过程与函数的主要区别是存储过程不返回值，并且支持事务控制。

存储过程基本语法 ​

sql
CREATE [OR REPLACE] PROCEDURE procedure_name (parameter_list)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 存储过程体
    -- SQL语句
    -- 可以包含COMMIT/ROLLBACK
END;
$$;

存储过程示例 ​

sql
-- 创建一个处理订单的存储过程
CREATE OR REPLACE PROCEDURE process_order(
    IN p_user_id INTEGER,
    IN p_product_id INTEGER,
    IN p_quantity INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_price DECIMAL(10, 2);
    v_order_id INTEGER;
BEGIN
    -- 开始事务
    BEGIN
        -- 获取产品价格
        SELECT price INTO v_price FROM products WHERE id = p_product_id;
        
        -- 插入订单
        INSERT INTO orders (user_id, total_amount, status)
        VALUES (p_user_id, v_price * p_quantity, 'pending')
        RETURNING id INTO v_order_id;
        
        -- 插入订单项
        INSERT INTO order_items (order_id, product_id, quantity, unit_price)
        VALUES (v_order_id, p_product_id, p_quantity, v_price);
        
        -- 更新库存
        UPDATE inventory SET quantity = quantity - p_quantity WHERE product_id = p_product_id;
        
        -- 提交事务
        COMMIT;
        
        -- 输出订单ID
        RAISE NOTICE 'Order processed successfully. Order ID: %', v_order_id;
        
    EXCEPTION
        -- 异常处理
        WHEN OTHERS THEN
            -- 回滚事务
            ROLLBACK;
            
            -- 抛出异常
            RAISE EXCEPTION 'Failed to process order: %', SQLERRM;
    END;
END;
$$;

-- 调用存储过程
CALL process_order(1, 1, 2);

存储过程与事务控制 ​

sql
-- 创建一个使用事务控制的存储过程
CREATE OR REPLACE PROCEDURE batch_update_products(
    IN p_category_id INTEGER,
    IN p_price_increase DECIMAL(5, 2)
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 开始事务
    BEGIN
        -- 更新产品价格
        UPDATE products 
        SET price = price * (1 + p_price_increase / 100)
        WHERE category_id = p_category_id;
        
        -- 检查更新的行数
        IF NOT FOUND THEN
            -- 回滚事务
            ROLLBACK;
            RAISE NOTICE 'No products found for category ID: %', p_category_id;
        ELSE
            -- 提交事务
            COMMIT;
            RAISE NOTICE 'Products updated successfully for category ID: %', p_category_id;
        END IF;
        
    EXCEPTION
        WHEN OTHERS THEN
            -- 回滚事务
            ROLLBACK;
            RAISE EXCEPTION 'Failed to update products: %', SQLERRM;
    END;
END;
$$;

-- 调用存储过程
CALL batch_update_products(1, 10.0); -- 增加10%的价格

函数和存储过程的高级特性 ​

动态SQL ​

动态SQL允许在函数或存储过程中构建和执行SQL语句。

sql
-- 创建一个使用动态SQL的函数
CREATE OR REPLACE FUNCTION count_records(
    table_name VARCHAR,
    condition VARCHAR DEFAULT NULL
)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
    query TEXT;
    result BIGINT;
BEGIN
    -- 构建基本查询
    query := format('SELECT COUNT(*) FROM %I', table_name);
    
    -- 如果提供了条件，添加WHERE子句
    IF condition IS NOT NULL THEN
        query := query || ' WHERE ' || condition;
    END IF;
    
    -- 执行动态SQL
    EXECUTE query INTO result;
    
    RETURN result;
END;
$$;

-- 调用使用动态SQL的函数
SELECT count_records('products');
SELECT count_records('products', 'price > 100');

递归函数 ​

递归函数是调用自身的函数，用于处理层次结构或树状数据。

sql
-- 创建一个递归函数来计算阶乘
CREATE OR REPLACE FUNCTION factorial(n INTEGER)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
BEGIN
    IF n <= 1 THEN
        RETURN 1;
    ELSE
        RETURN n * factorial(n - 1);
    END IF;
END;
$$;

-- 调用递归函数
SELECT factorial(5); -- 返回120

函数重载 ​

PostgreSQL支持函数重载，即可以创建同名但参数类型或数量不同的函数。

sql
-- 创建重载函数
CREATE OR REPLACE FUNCTION calculate_discount(price DECIMAL, percentage INTEGER)
RETURNS DECIMAL
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN price * (1 - percentage / 100.0);
END;
$$;

-- 创建另一个重载函数
CREATE OR REPLACE FUNCTION calculate_discount(price DECIMAL, fixed_amount DECIMAL)
RETURNS DECIMAL
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN GREATEST(price - fixed_amount, 0);
END;
$$;

-- 调用不同的重载函数
SELECT calculate_discount(100.0, 10); -- 使用第一个函数，返回90.0
SELECT calculate_discount(100.0, 20.0); -- 使用第二个函数，返回80.0

函数和存储过程的管理 ​

查看函数和存储过程 ​

sql
-- 查看所有函数
SELECT proname, proargnames, prorettype
FROM pg_proc
WHERE pronamespace = 'public'::regnamespace
ORDER BY proname;

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

-- 查看存储过程
SELECT proname, proargnames
FROM pg_proc
WHERE pronamespace = 'public'::regnamespace
AND prokind = 'p' -- 'p'表示存储过程
ORDER BY proname;

修改函数和存储过程 ​

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

-- 修改存储过程
CREATE OR REPLACE PROCEDURE procedure_name(parameter_list)
LANGUAGE plpgsql
AS $$
-- 新的存储过程体
$$;

删除函数和存储过程 ​

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

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

-- 删除所有重载函数
DROP FUNCTION IF EXISTS function_name;

最佳实践 ​

使用适当的语言 ​

  • 对于简单的查询，使用SQL语言
  • 对于复杂的逻辑，使用PL/pgSQL
  • 对于性能敏感的操作，考虑使用C语言

函数命名规范 ​

  • 使用清晰、描述性的名称
  • 考虑使用前缀（如fn_或func_）区分函数
  • 函数名使用小写，单词之间用下划线分隔

参数设计 ​

  • 保持参数数量合理（建议不超过10个）
  • 为参数提供有意义的名称
  • 尽量使用基本数据类型
  • 为可选参数提供默认值

返回值设计 ​

  • 对于简单结果，返回单个值
  • 对于多行结果，使用TABLE返回类型
  • 考虑使用OUT参数返回多个值

性能考虑 ​

  • 避免在函数中使用大量动态SQL
  • 对于频繁调用的函数，考虑使用IMMUTABLE或STABLE volatility类别
  • 适当使用缓存机制

错误处理 ​

  • 始终包含异常处理逻辑
  • 提供清晰的错误信息
  • 在存储过程中正确使用事务控制

文档化 ​

  • 为函数和存储过程添加注释
  • 描述函数的用途、参数和返回值
  • 记录函数的副作用（如修改数据）

常见问题（FAQ） ​

函数和存储过程有什么区别？ ​

  • 函数必须返回一个值，存储过程可以不返回值
  • 函数可以在SELECT语句中使用，存储过程必须使用CALL语句调用
  • 存储过程支持事务控制（COMMIT/ROLLBACK），函数不支持
  • 存储过程可以有OUT/INOUT参数，函数也支持但通常使用RETURN

如何选择函数的volatility类别？ ​

  • IMMUTABLE：函数结果只依赖于输入参数，对于相同的输入总是返回相同的结果
  • STABLE：函数结果在事务内稳定，但可能随时间变化
  • VOLATILE：函数结果可能随时变化，即使使用相同的输入

如何调试PL/pgSQL函数？ ​

  • 使用RAISE NOTICE语句输出调试信息
  • 使用pgAdmin等工具的调试功能
  • 查看PostgreSQL日志文件

如何提高函数的性能？ ​

  • 避免在循环中执行SQL语句
  • 尽可能使用集合操作替代循环
  • 适当使用索引
  • 考虑使用物化视图缓存函数结果

如何处理大型结果集？ ​

  • 使用RETURN QUERY返回大型结果集
  • 避免将大型结果集存储在变量中
  • 考虑使用游标处理非常大的结果集

总结 ​

PostgreSQL函数和存储过程是强大的数据库对象，可以帮助你封装和重用SQL逻辑。PL/pgSQL是PostgreSQL的默认过程语言，提供了丰富的控制结构和异常处理机制。

通过使用函数和存储过程，你可以：

  • 提高代码的可重用性
  • 简化复杂查询
  • 实现业务逻辑封装
  • 提高数据库安全性
  • 优化性能

在实际项目中，建议根据具体需求选择合适的函数类型和实现方式，并遵循最佳实践，确保函数和存储过程的可维护性和性能。