存储过程

存储过程（Stored Procedure）是存储在数据库中的预编译SQL语句集合。与函数不同，存储过程可以执行更复杂的操作，包括数据修改、事务控制等，且不必须返回值。PostgreSQL从11版本开始正式支持存储过程。

存储过程与函数的区别

特性	存储过程	函数
返回值	可选	必须有
事务控制	支持COMMIT/ROLLBACK	不支持
调用方式	CALL procedure()	SELECT function()
在SQL中使用	不能在SQL表达式中调用	可以在SQL表达式中调用
参数模式	IN, OUT, INOUT	IN, OUT, INOUT, VARIADIC

创建存储过程

基本语法

sql

-- 创建存储过程的基本语法
CREATE [OR REPLACE] PROCEDURE 过程名(参数列表)
AS $$
    过程体
$$ LANGUAGE plpgsql;

-- 参数格式：[IN|OUT|INOUT] 参数名 参数类型

简单存储过程

sql

-- 创建示例表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    total_amount NUMERIC(10, 2),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(order_id),
    product_name VARCHAR(100),
    quantity INTEGER,
    price NUMERIC(10, 2)
);

-- 创建简单存储过程
CREATE OR REPLACE PROCEDURE print_hello()
AS $$
BEGIN
    RAISE NOTICE 'Hello, PostgreSQL!';
END;
$$ LANGUAGE plpgsql;

-- 调用存储过程
CALL print_hello();

-- 带参数的存储过程
CREATE OR REPLACE PROCEDURE greet_user(user_name TEXT)
AS $$
BEGIN
    RAISE NOTICE '欢迎, %!', user_name;
END;
$$ LANGUAGE plpgsql;

CALL greet_user('张三');

带默认参数的存储过程

sql

-- 创建带默认参数的存储过程
CREATE OR REPLACE PROCEDURE create_order(
    p_customer_name VARCHAR(100),
    p_total_amount NUMERIC(10, 2),
    p_status VARCHAR(20) DEFAULT 'pending'
)
AS $$
BEGIN
    INSERT INTO orders (customer_name, total_amount, status)
    VALUES (p_customer_name, p_total_amount, p_status);
    
    RAISE NOTICE '订单创建成功，客户: %', p_customer_name;
END;
$$ LANGUAGE plpgsql;

-- 调用方式
CALL create_order('李四', 1000.00);                    -- 使用默认状态
CALL create_order('王五', 2000.00, 'confirmed');       -- 指定状态
CALL create_order(p_customer_name := '赵六', p_total_amount := 3000.00);  -- 命名参数

事务控制

存储过程的一个重要特性是可以在过程中控制事务：

sql

-- 包含事务控制的存储过程
CREATE OR REPLACE PROCEDURE transfer_order(
    p_from_order_id INTEGER,
    p_to_order_id INTEGER,
    p_amount NUMERIC(10, 2)
)
AS $$
DECLARE
    v_from_amount NUMERIC(10, 2);
    v_to_amount NUMERIC(10, 2);
BEGIN
    -- 获取源订单金额
    SELECT total_amount INTO v_from_amount
    FROM orders WHERE order_id = p_from_order_id;
    
    -- 检查源订单是否存在
    IF v_from_amount IS NULL THEN
        RAISE EXCEPTION '源订单 % 不存在', p_from_order_id;
    END IF;
    
    -- 检查金额是否足够
    IF v_from_amount < p_amount THEN
        RAISE EXCEPTION '源订单金额不足';
    END IF;
    
    -- 获取目标订单金额
    SELECT total_amount INTO v_to_amount
    FROM orders WHERE order_id = p_to_order_id;
    
    IF v_to_amount IS NULL THEN
        RAISE EXCEPTION '目标订单 % 不存在', p_to_order_id;
    END IF;
    
    -- 更新源订单
    UPDATE orders 
    SET total_amount = total_amount - p_amount
    WHERE order_id = p_from_order_id;
    
    -- 更新目标订单
    UPDATE orders 
    SET total_amount = total_amount + p_amount
    WHERE order_id = p_to_order_id;
    
    RAISE NOTICE '转账成功: % 从订单 % 转到订单 %', 
        p_amount, p_from_order_id, p_to_order_id;
        
    -- 可以在这里提交事务
    COMMIT;
    
EXCEPTION
    WHEN OTHERS THEN
        -- 发生错误时回滚
        ROLLBACK;
        RAISE NOTICE '转账失败: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;

使用SAVEPOINT

sql

-- 使用保存点的存储过程
CREATE OR REPLACE PROCEDURE batch_insert_orders(
    p_orders JSONB
)
AS $$
DECLARE
    order_record JSONB;
    success_count INTEGER := 0;
    fail_count INTEGER := 0;
BEGIN
    -- 遍历JSON数组中的每个订单
    FOR order_record IN SELECT * FROM jsonb_array_elements(p_orders)
    LOOP
        BEGIN
            -- 创建保存点
            SAVEPOINT insert_order;
            
            -- 插入订单
            INSERT INTO orders (customer_name, total_amount, status)
            VALUES (
                order_record->>'customer_name',
                (order_record->>'total_amount')::NUMERIC,
                COALESCE(order_record->>'status', 'pending')
            );
            
            success_count := success_count + 1;
            
        EXCEPTION
            WHEN OTHERS THEN
                -- 回滚到保存点
                ROLLBACK TO insert_order;
                fail_count := fail_count + 1;
                RAISE NOTICE '插入失败: %, 错误: %', 
                    order_record->>'customer_name', SQLERRM;
        END;
    END LOOP;
    
    -- 提交事务
    COMMIT;
    
    RAISE NOTICE '批量插入完成: 成功 % 条, 失败 % 条', success_count, fail_count;
END;
$$ LANGUAGE plpgsql;

-- 调用存储过程
CALL batch_insert_orders('[
    {"customer_name": "客户A", "total_amount": 1000},
    {"customer_name": "客户B", "total_amount": 2000},
    {"customer_name": "客户C", "total_amount": 3000}
]'::JSONB);

OUT参数

存储过程可以使用OUT参数返回多个值：

sql

-- 使用OUT参数的存储过程
CREATE OR REPLACE PROCEDURE get_order_stats(
    p_customer_name VARCHAR(100),
    OUT total_orders INTEGER,
    OUT total_amount NUMERIC(10, 2),
    OUT avg_amount NUMERIC(10, 2)
)
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_name = p_customer_name;
END;
$$ LANGUAGE plpgsql;

-- 调用存储过程
DO $$
DECLARE
    v_total_orders INTEGER;
    v_total_amount NUMERIC(10, 2);
    v_avg_amount NUMERIC(10, 2);
BEGIN
    CALL get_order_stats('李四', v_total_orders, v_total_amount, v_avg_amount);
    RAISE NOTICE '订单数: %, 总金额: %, 平均金额: %', 
        v_total_orders, v_total_amount, v_avg_amount;
END;
$$;

-- 或者使用位置参数
CALL get_order_stats('李四', NULL, NULL, NULL);

INOUT参数

sql

-- 使用INOUT参数
CREATE OR REPLACE PROCEDURE double_amount(INOUT p_amount NUMERIC)
AS $$
BEGIN
    p_amount := p_amount * 2;
END;
$$ LANGUAGE plpgsql;

-- 调用
DO $$
DECLARE
    amount NUMERIC := 100;
BEGIN
    CALL double_amount(amount);
    RAISE NOTICE '翻倍后的金额: %', amount;
END;
$$;

动态SQL

存储过程中可以执行动态SQL：

sql

-- 执行动态SQL的存储过程
CREATE OR REPLACE PROCEDURE dynamic_query(
    p_table_name TEXT,
    p_column_name TEXT,
    p_value TEXT
)
AS $$
DECLARE
    query TEXT;
    result RECORD;
BEGIN
    -- 构建动态SQL（注意SQL注入风险）
    query := format('SELECT * FROM %I WHERE %I = %L', 
        p_table_name, p_column_name, p_value);
    
    RAISE NOTICE '执行SQL: %', query;
    
    -- 执行动态SQL
    EXECUTE query;
    
END;
$$ LANGUAGE plpgsql;

-- 调用
CALL dynamic_query('orders', 'customer_name', '李四');

-- 使用USING参数化（更安全）
CREATE OR REPLACE PROCEDURE safe_dynamic_query(
    p_table_name TEXT,
    p_column_name TEXT,
    p_value TEXT
)
AS $$
DECLARE
    query TEXT;
BEGIN
    -- 使用format构建安全的动态SQL
    query := format('SELECT * FROM %I WHERE %I = $1', 
        p_table_name, p_column_name);
    
    RAISE NOTICE '执行SQL: %', query;
    
    -- 使用USING传递参数
    EXECUTE query USING p_value;
    
END;
$$ LANGUAGE plpgsql;

实用存储过程示例

批量处理订单

sql

-- 批量处理订单的存储过程
CREATE OR REPLACE PROCEDURE process_pending_orders(
    INOUT processed_count INTEGER DEFAULT 0
)
AS $$
DECLARE
    order_record RECORD;
BEGIN
    processed_count := 0;
    
    -- 遍历所有待处理订单
    FOR order_record IN 
        SELECT order_id, customer_name, total_amount 
        FROM orders 
        WHERE status = 'pending'
    LOOP
        BEGIN
            -- 模拟订单处理逻辑
            -- 这里可以添加复杂的业务逻辑
            
            -- 更新订单状态
            UPDATE orders 
            SET status = 'processed'
            WHERE order_id = order_record.order_id;
            
            -- 记录日志
            RAISE NOTICE '处理订单 %: 客户 %, 金额 %', 
                order_record.order_id, 
                order_record.customer_name, 
                order_record.total_amount;
            
            processed_count := processed_count + 1;
            
        EXCEPTION
            WHEN OTHERS THEN
                RAISE WARNING '处理订单 % 失败: %', 
                    order_record.order_id, SQLERRM;
        END;
    END LOOP;
    
    COMMIT;
    RAISE NOTICE '批量处理完成，共处理 % 个订单', processed_count;
END;
$$ LANGUAGE plpgsql;

-- 调用
CALL process_pending_orders(NULL);

数据归档

sql

-- 创建归档表
CREATE TABLE orders_archive (
    LIKE orders INCLUDING ALL
);

CREATE TABLE order_items_archive (
    LIKE order_items INCLUDING ALL
);

-- 数据归档存储过程
CREATE OR REPLACE PROCEDURE archive_old_orders(
    p_days_old INTEGER DEFAULT 90,
    INOUT archived_count INTEGER DEFAULT 0
)
AS $$
DECLARE
    v_cutoff_date TIMESTAMP;
BEGIN
    v_cutoff_date := CURRENT_TIMESTAMP - (p_days_old || ' days')::INTERVAL;
    archived_count := 0;
    
    -- 开始事务
    BEGIN
        -- 归档订单项
        INSERT INTO order_items_archive
        SELECT oi.* 
        FROM order_items oi
        JOIN orders o ON oi.order_id = o.order_id
        WHERE o.created_at < v_cutoff_date;
        
        -- 归档订单
        INSERT INTO orders_archive
        SELECT * FROM orders
        WHERE created_at < v_cutoff_date;
        
        GET DIAGNOSTICS archived_count = ROW_COUNT;
        
        -- 删除已归档的订单项
        DELETE FROM order_items
        WHERE order_id IN (
            SELECT order_id FROM orders
            WHERE created_at < v_cutoff_date
        );
        
        -- 删除已归档的订单
        DELETE FROM orders
        WHERE created_at < v_cutoff_date;
        
        COMMIT;
        
        RAISE NOTICE '归档完成: 归档 % 条订单', archived_count;
        
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE EXCEPTION '归档失败: %', SQLERRM;
    END;
END;
$$ LANGUAGE plpgsql;

-- 调用归档存储过程
CALL archive_old_orders(90, NULL);

生成报表

sql

-- 创建报表存储过程
CREATE OR REPLACE PROCEDURE generate_sales_report(
    p_start_date DATE,
    p_end_date DATE
)
AS $$
DECLARE
    v_total_orders INTEGER;
    v_total_amount NUMERIC(12, 2);
    v_avg_order_amount NUMERIC(10, 2);
    v_top_customer TEXT;
    v_top_amount NUMERIC(10, 2);
BEGIN
    -- 计算总订单数和总金额
    SELECT 
        COUNT(*),
        COALESCE(SUM(total_amount), 0),
        COALESCE(AVG(total_amount), 0)
    INTO v_total_orders, v_total_amount, v_avg_order_amount
    FROM orders
    WHERE created_at >= p_start_date 
      AND created_at < p_end_date + 1;
    
    -- 找出消费最多的客户
    SELECT customer_name, SUM(total_amount)
    INTO v_top_customer, v_top_amount
    FROM orders
    WHERE created_at >= p_start_date 
      AND created_at < p_end_date + 1
    GROUP BY customer_name
    ORDER BY SUM(total_amount) DESC
    LIMIT 1;
    
    -- 输出报表
    RAISE NOTICE '';
    RAISE NOTICE '========== 销售报表 ==========';
    RAISE NOTICE '报表周期: % 至 %', p_start_date, p_end_date;
    RAISE NOTICE '----------------------------';
    RAISE NOTICE '总订单数: %', v_total_orders;
    RAISE NOTICE '总销售额: %', v_total_amount;
    RAISE NOTICE '平均订单金额: %', v_avg_order_amount;
    RAISE NOTICE '----------------------------';
    RAISE NOTICE '最佳客户: % (消费: %)', v_top_customer, v_top_amount;
    RAISE NOTICE '==============================';
    RAISE NOTICE '';
    
END;
$$ LANGUAGE plpgsql;

-- 调用报表存储过程
CALL generate_sales_report('2024-01-01', '2024-12-31');

存储过程管理

查看存储过程

sql

-- 查看所有存储过程
\df

-- 查看存储过程定义
\df+ procedure_name

-- 使用SQL查询存储过程信息
SELECT 
    proname AS 过程名,
    pg_get_function_arguments(oid) AS 参数,
    proowner::regrole AS 所有者
FROM pg_proc
WHERE prokind = 'p'  -- p表示存储过程
AND pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

-- 查看存储过程源代码
SELECT pg_get_functiondef(oid) 
FROM pg_proc 
WHERE proname = 'create_order';

修改存储过程

sql

-- 修改存储过程定义
CREATE OR REPLACE PROCEDURE create_order(
    p_customer_name VARCHAR(100),
    p_total_amount NUMERIC(10, 2),
    p_status VARCHAR(20) DEFAULT 'pending'
)
AS $$
BEGIN
    INSERT INTO orders (customer_name, total_amount, status)
    VALUES (p_customer_name, p_total_amount, p_status);
    
    RAISE NOTICE '订单创建成功，订单ID: %', currval('orders_order_id_seq');
END;
$$ LANGUAGE plpgsql;

-- 修改存储过程所有者
ALTER PROCEDURE create_order(VARCHAR, NUMERIC, VARCHAR) OWNER TO postgres;

-- 重命名存储过程
ALTER PROCEDURE create_order(VARCHAR, NUMERIC, VARCHAR) 
RENAME TO add_new_order;

删除存储过程

sql

-- 删除存储过程（需要指定参数类型）
DROP PROCEDURE create_order(VARCHAR, NUMERIC, VARCHAR);

-- 如果存在则删除
DROP PROCEDURE IF EXISTS create_order(VARCHAR, NUMERIC, VARCHAR);

-- 级联删除
DROP PROCEDURE create_order(VARCHAR, NUMERIC, VARCHAR) CASCADE;

存储过程权限

sql

-- 授予执行权限
GRANT EXECUTE ON PROCEDURE create_order(VARCHAR, NUMERIC, VARCHAR) TO user1;

-- 撤销执行权限
REVOKE EXECUTE ON PROCEDURE create_order(VARCHAR, NUMERIC, VARCHAR) FROM user1;

-- 授予所有用户执行权限
GRANT EXECUTE ON PROCEDURE create_order(VARCHAR, NUMERIC, VARCHAR) TO PUBLIC;

最佳实践

命名规范

sql

-- 使用有意义的前缀
-- sp_ 或 proc_ 前缀表示存储过程
CREATE OR REPLACE PROCEDURE sp_process_order(...) ...
CREATE OR REPLACE PROCEDURE proc_archive_data(...) ...

-- 使用动词开头
CREATE OR REPLACE PROCEDURE calculate_monthly_stats(...) ...
CREATE OR REPLACE PROCEDURE validate_user_input(...) ...

错误处理

sql

-- 完善的错误处理
CREATE OR REPLACE PROCEDURE safe_operation(
    p_param INTEGER
)
AS $$
BEGIN
    -- 参数验证
    IF p_param IS NULL THEN
        RAISE EXCEPTION '参数不能为空';
    END IF;
    
    IF p_param < 0 THEN
        RAISE EXCEPTION '参数必须为正数';
    END IF;
    
    -- 业务逻辑
    BEGIN
        -- 执行操作
        -- ...
        
    EXCEPTION
        WHEN unique_violation THEN
            RAISE EXCEPTION '数据已存在';
        WHEN foreign_key_violation THEN
            RAISE EXCEPTION '关联数据不存在';
        WHEN OTHERS THEN
            RAISE EXCEPTION '操作失败: %', SQLERRM;
    END;
    
END;
$$ LANGUAGE plpgsql;

性能优化

sql

-- 避免在循环中执行单条SQL
-- 不好的做法
CREATE OR REPLACE PROCEDURE bad_example(ids INTEGER[])
AS $$
DECLARE
    id_val INTEGER;
BEGIN
    FOREACH id_val IN ARRAY ids LOOP
        UPDATE orders SET status = 'processed' WHERE order_id = id_val;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 好的做法：批量操作
CREATE OR REPLACE PROCEDURE good_example(ids INTEGER[])
AS $$
BEGIN
    UPDATE orders 
    SET status = 'processed' 
    WHERE order_id = ANY(ids);
END;
$$ LANGUAGE plpgsql;

小结

本章介绍了PostgreSQL存储过程的主要内容：

内容	说明
基本语法	CREATE PROCEDURE创建存储过程
参数模式	IN、OUT、INOUT参数
事务控制	COMMIT、ROLLBACK、SAVEPOINT
动态SQL	EXECUTE执行动态SQL
管理操作	查看、修改、删除存储过程

存储过程适合执行复杂业务逻辑和批量操作，下一章我们将学习触发器的使用。

存储过程 ​

存储过程与函数的区别 ​

创建存储过程 ​

基本语法 ​

简单存储过程 ​

带默认参数的存储过程 ​

事务控制 ​

使用SAVEPOINT ​

OUT参数 ​

INOUT参数 ​

动态SQL ​

实用存储过程示例 ​

批量处理订单 ​

数据归档 ​

生成报表 ​

存储过程管理 ​

查看存储过程 ​

修改存储过程 ​

删除存储过程 ​

存储过程权限 ​

最佳实践 ​

命名规范 ​

错误处理 ​

性能优化 ​

小结 ​