PostgreSQL中BULK COLLECT的使用方法及相关报错与集合计数用法求助

嘿，看得出来你可能之前用惯了Oracle的PL/SQL，刚转PostgreSQL踩了语法差异的坑对吧？我来帮你逐个解决这两个问题：

1. 解决BULK COLLECT的语法错误 #

PostgreSQL的PL/pgSQL不支持Oracle风格的EXECUTE ... BULK COLLECT INTO语法，这是两者PL/SQL方言的核心差异之一。要批量获取动态SQL的结果，推荐用游标结合FETCH ALL INTO的方式，或者直接将结果存入数组（前提是你定义了对应的数组类型）。

另外还要提醒你：直接拼接变量到SQL字符串里有SQL注入风险，一定要用USING子句传递参数，避免安全问题。

关键修改点： #

• 定义对应的行类型和数组类型（如果还没定义的话）
• 用游标执行动态SQL，再批量抓取结果到数组
• 用USING传递参数替代字符串拼接

2. 集合数量判断：用cardinality()替代.COUNT #

PostgreSQL的数组/集合类型没有.COUNT属性（这是Oracle的用法），要获取集合的元素个数，必须用cardinality()函数。同时建议先判断数组是否为NULL，避免空指针错误。

错误写法： #

IF IFSC_FETCH_OBJ.COUNT > 0 THEN

正确写法： #

IF IFSC_FETCH_OBJ IS NOT NULL AND cardinality(IFSC_FETCH_OBJ) > 0 THEN

修改后的完整代码示例 #

首先确保你已经定义了对应的自定义类型（如果还没定义）：

-- 定义存储单条记录的行类型
CREATE TYPE ifsc_record AS (
    IFS_CODE VARCHAR(50),
    IFS_BANK_NAME VARCHAR(50),
    IFS_BRN_NAME VARCHAR(50)
);

-- 定义存储多条记录的数组类型
CREATE TYPE ifsc_fetch_string AS ARRAY OF ifsc_record;

然后是修改后的PL/pgSQL代码：

DECLARE
    TMP_RECORD ifsc_record; -- 使用自定义行类型
    W_SQL VARCHAR(500);
    W_IFSC VARCHAR(50);
    W_BANK_NAME VARCHAR(50);
    W_BRN_NAME VARCHAR(50);
    W_CITY_NAME VARCHAR(50);
    W_STATE_NAME VARCHAR(50);
    W_SQL_DATA VARCHAR(500);
    IFSC_FETCH_OBJ ifsc_fetch_string; -- 自定义数组类型
    cur refcursor; -- 声明游标变量
BEGIN
    W_SQL := 'SELECT IFSRID_IFS_CODE, IFSRID_BRN_NAME || '' - '' || IFSRID_BRN_PLACE, IFSRID_BRN_ADDR1 FROM IFSRID WHERE 1=1';
    
    IF TRIM(P_IFSC) IS NOT NULL THEN
        W_SQL := W_SQL || ' AND IFSRID_IFS_CODE LIKE ''%'' || $1 || ''%''';
        -- 打开游标执行动态SQL，用USING传递参数
        OPEN cur FOR EXECUTE W_SQL USING W_IFSC;
        -- 批量抓取所有结果到数组
        FETCH ALL FROM cur INTO IFSC_FETCH_OBJ;
        CLOSE cur;
    ELSE
        IF TRIM(P_BANK_NAME) IS NOT NULL THEN
            W_SQL := W_SQL || ' AND IFSRID_BRN_NAME LIKE ''%'' || $1 || ''%''';
            OPEN cur FOR EXECUTE W_SQL USING W_BANK_NAME;
            FETCH ALL FROM cur INTO IFSC_FETCH_OBJ;
            CLOSE cur;
        ELSE
            -- 无筛选条件的情况
            OPEN cur FOR EXECUTE W_SQL;
            FETCH ALL FROM cur INTO IFSC_FETCH_OBJ;
            CLOSE cur;
        END IF;
    END IF;

    -- 判断数组是否有数据
    IF IFSC_FETCH_OBJ IS NOT NULL AND cardinality(IFSC_FETCH_OBJ) > 0 THEN
        -- PostgreSQL数组索引从1开始，循环遍历数组
        FOR IDX IN 1..cardinality(IFSC_FETCH_OBJ) LOOP
            TMP_RECORD.IFS_CODE := IFSC_FETCH_OBJ[IDX].IFS_CODE;
            TMP_RECORD.IFS_BANK_NAME := IFSC_FETCH_OBJ[IDX].IFS_BANK_NAME;
            TMP_RECORD.IFS_BRN_NAME := IFSC_FETCH_OBJ[IDX].IFS_BRN_NAME;
            RETURN NEXT TMP_RECORD;
        END LOOP;
    END IF;
END;

另外补充一点：PostgreSQL的数组默认索引是从1开始的，和Oracle的集合索引逻辑一致，但如果你的数组是自定义起始索引的情况，需要调整循环范围，不过大部分场景下用1到cardinality()就够了。

内容的提问来源于stack exchange，提问作者KSMNOP k