PostgreSQL / JDBC 存储过程

让我来解释PostgreSQL中存储过程（PROCEDURE）和函数（FUNCTION）的主要区别：

1. 返回值
   - FUNCTION：必须有返回值（RETURNS 子句是必需的）
   - PROCEDURE：不能有返回值，但可以通过 OUT 或 INOUT 参数返回数据

2. 事务处理
   - PROCEDURE：可以包含事务控制（如 COMMIT、ROLLBACK）
   - FUNCTION：不能包含事务控制语句

3. 调用方式
   - PROCEDURE：使用 CALL 语句调用（例如：CALL procedure_name(args))
   - FUNCTION：在 SELECT 语句或其他 SQL 表达式中调用（例如：SELECT function_name(args))

4. 使用场景
   - PROCEDURE：适合执行一系列操作，特别是涉及数据修改（INSERT、UPDATE、DELETE）的操作
   - FUNCTION：适合计算并返回值，通常用在 SELECT 查询中

5. 上下文
   - PROCEDURE：可以在独立的事务上下文中运行
   - FUNCTION：在调用它的查询的上下文中运行

示例对比：

-- 存储过程示例：处理订单
CREATE PROCEDURE process_order(order_id int)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 可以包含事务控制
    BEGIN
        UPDATE orders SET status = 'processed' WHERE id = order_id;
        INSERT INTO order_history (order_id, processed_at) VALUES (order_id, NOW());
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
    END;
END;
$$;

-- 函数示例：计算订单总额
CREATE FUNCTION calculate_order_total(order_id int)
RETURNS decimal
LANGUAGE plpgsql
AS $$
DECLARE
    total decimal;
BEGIN
    SELECT SUM(quantity * price)
    INTO total
    FROM order_items
    WHERE order_id = $1;

    RETURN total;
END;
$$;

使用方式：

-- 调用存储过程
CALL process_order(123);

-- 调用函数
SELECT calculate_order_total(123);

选择建议：
1. 如果需要执行一系列数据修改操作，并可能需要事务控制，使用 PROCEDURE
2. 如果需要返回计算结果并在查询中使用，使用 FUNCTION
3. 如果操作比较简单且只需要返回单个值，使用 FUNCTION 会更合适

这种区分方式与其他数据库系统（如 Oracle、SQL Server）的设计理念是一致的，有助于更好地组织和管理数据库代码。

让我详细介绍PostgreSQL存储过程中的条件判断和循环控制：

1. IF 条件判断：

-- 基本IF语句
IF condition THEN
    -- statements;
ELSIF condition THEN
    -- statements;
ELSE
    -- statements;
END IF;

-- 实际例子
CREATE PROCEDURE check_score(score int)
LANGUAGE plpgsql
AS $$
BEGIN
    IF score >= 90 THEN
        RAISE NOTICE '优秀';
    ELSIF score >= 80 THEN
        RAISE NOTICE '良好';
    ELSIF score >= 60 THEN
        RAISE NOTICE '及格';
    ELSE
        RAISE NOTICE '不及格';
    END IF;
END;
$$;

2. FOR 循环有多种形式：

a. 数字范围循环：

-- 遍历1到10
FOR i IN 1..10 LOOP
    -- statements;
END LOOP;

-- 实际例子
CREATE PROCEDURE print_numbers()
LANGUAGE plpgsql
AS $$
DECLARE
    sum int := 0;
BEGIN
    FOR i IN 1..5 LOOP
        sum := sum + i;
        RAISE NOTICE '当前数字: %, 当前总和: %', i, sum;
    END LOOP;
END;
$$;

b. 反向循环：

-- 使用REVERSE
FOR i IN REVERSE 10..1 LOOP
    -- statements;
END LOOP;

c. 遍历查询结果：

-- 遍历查询结果
FOR record_variable IN SELECT * FROM table_name LOOP
    -- statements;
END LOOP;

-- 实际例子
CREATE PROCEDURE process_employees()
LANGUAGE plpgsql
AS $$
DECLARE
    total_salary numeric := 0;
BEGIN
    FOR emp_record IN SELECT employee_id, salary FROM employees LOOP
        total_salary := total_salary + emp_record.salary;
        RAISE NOTICE '员工ID: %, 工资: %', emp_record.employee_id, emp_record.salary;
    END LOOP;
    RAISE NOTICE '总工资: %', total_salary;
END;
$$;

3. WHILE 循环：

-- 基本WHILE循环
WHILE condition LOOP
    -- statements;
END LOOP;

-- 实际例子
CREATE PROCEDURE countdown(start_num int)
LANGUAGE plpgsql
AS $$
DECLARE
    counter int := start_num;
BEGIN
    WHILE counter > 0 LOOP
        RAISE NOTICE '倒计时: %', counter;
        counter := counter - 1;
    END LOOP;
END;
$$;

4. 循环控制：

-- EXIT：退出循环
-- CONTINUE：跳到下一次循环
-- 带条件的EXIT
CREATE PROCEDURE demo_loop_control()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 1..10 LOOP
        -- 跳过偶数
        IF i % 2 = 0 THEN
            CONTINUE;
        END IF;

        -- 当i大于7时退出
        IF i > 7 THEN
            EXIT;
        END IF;

        RAISE NOTICE '处理数字: %', i;
    END LOOP;
END;
$$;

5. 综合实例：

CREATE PROCEDURE process_orders(p_date date)
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_count int;
    v_total_amount numeric := 0;
    v_order record;
BEGIN
    -- 获取订单数量
    SELECT COUNT(*) INTO v_order_count 
    FROM orders 
    WHERE order_date = p_date;

    -- 如果没有订单，直接返回
    IF v_order_count = 0 THEN
        RAISE NOTICE '该日期 % 没有订单', p_date;
        RETURN;
    END IF;

    -- 遍历所有订单
    FOR v_order IN 
        SELECT order_id, amount 
        FROM orders 
        WHERE order_date = p_date 
    LOOP
        -- 累加订单金额
        v_total_amount := v_total_amount + v_order.amount;

        -- 处理大额订单
        IF v_order.amount > 1000 THEN
            UPDATE orders 
            SET status = 'HIGH_VALUE' 
            WHERE order_id = v_order.order_id;
        END IF;
    END LOOP;

    RAISE NOTICE '处理完成 - 总订单数: %, 总金额: %', v_order_count, v_total_amount;
END;
$$;

使用注意事项：

1. 变量声明：
   - 使用 DECLARE 块声明变量
   - 变量必须在过程开始时声明

2. 异常处理：

BEGIN
    -- 你的代码
EXCEPTION
    WHEN condition THEN
        -- 处理异常
END;

3. 调试：
   - 使用 RAISE NOTICE 输出调试信息
   - 可以使用 RAISE DEBUG、RAISE INFO 等不同级别的消息

4. 性能考虑：
   - 避免在循环中执行重复的SQL查询
   - 对于大量数据处理，考虑使用批处理而不是逐条处理

这些控制结构可以根据需要组合使用，创建复杂的业务逻辑处理流程。

public class StoredProcedureExample {
    private final DataSource dataSource;

    public StoredProcedureExample(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void executeProcedure(int inputValue) {
        try (Connection conn = dataSource.getConnection()) {
            // 设置消息监听器
            org.postgresql.PGConnection pgConn = conn.unwrap(org.postgresql.PGConnection.class);
            pgConn.addNotificationListener((processId, channelName, payload) -> {
                System.out.println("收到消息: " + payload);
            }, null);

            // 调用存储过程
            try (CallableStatement stmt = conn.prepareCall("{ call process_data(?, ?) }")) {
                // 设置输入参数
                stmt.setInt(1, inputValue);

                // 注册输出参数
                stmt.registerOutParameter(2, Types.INTEGER);

                // 执行存储过程
                stmt.execute();

                // 获取返回值
                int result = stmt.getInt(2);
                System.out.println("存储过程返回值: " + result);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}