PostgreSQL函数TEXT[]参数与ANY操作的Oracle等效实现问询

错误根源 #

PLS-00642错误的核心原因：Oracle的SQL引擎无法直接识别PL/SQL级别的VARRAY类型作为IN运算符的操作数，必须使用SQL级别的集合类型（嵌套表或VARRAY），并通过特定方式转换为SQL可处理的数据集。

等效实现方案 #

方案1：自定义SQL级嵌套表 + MEMBER OF/TABLE() #

先在SQL层定义嵌套表类型（SQL级类型可被SQL引擎直接识别），再在存储过程中使用。

步骤1：创建SQL级嵌套表类型

CREATE TYPE text_table AS TABLE OF VARCHAR2(100);
/

步骤2：编写存储过程（两种查询方式）

方式A：使用MEMBER OF运算符

CREATE OR REPLACE PROCEDURE get_users(p_user_ids IN text_table, p_result OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN p_result FOR
    SELECT * FROM users
    WHERE user_id MEMBER OF p_user_ids;
END;
/

方式B：使用TABLE()函数转换为数据集

CREATE OR REPLACE PROCEDURE get_users(p_user_ids IN text_table, p_result OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN p_result FOR
    SELECT * FROM users
    WHERE user_id IN (SELECT COLUMN_VALUE FROM TABLE(p_user_ids));
END;
/

方案2：自定义SQL级VARRAY + TABLE() #

如果偏好使用VARRAY，同样需要定义为SQL级类型：

步骤1：创建SQL级VARRAY类型

CREATE TYPE text_varray AS VARRAY(100) OF VARCHAR2(100);
/

步骤2：编写存储过程

CREATE OR REPLACE PROCEDURE get_users(p_user_ids IN text_varray, p_result OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN p_result FOR
    SELECT * FROM users
    WHERE user_id IN (SELECT COLUMN_VALUE FROM TABLE(p_user_ids));
END;
/

方案3：Oracle 21c简化方案（使用预定义集合类型） #

Oracle 21c可直接使用系统预定义的DBMS_SQL.VARCHAR2_TABLE（SQL级嵌套表类型），无需自定义类型：

CREATE OR REPLACE PROCEDURE get_users(p_user_ids IN DBMS_SQL.VARCHAR2_TABLE, p_result OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN p_result FOR
    SELECT * FROM users
    WHERE user_id IN (SELECT COLUMN_VALUE FROM TABLE(p_user_ids));
END;
/

调用示例 #

以方案1的嵌套表为例，在PL/SQL块中调用存储过程：

DECLARE
  v_user_ids text_table := text_table('U001', 'U002', 'U003');
  v_result_cursor SYS_REFCURSOR;
  v_user_record users%ROWTYPE;
BEGIN
  get_users(v_user_ids, v_result_cursor);
  
  -- 遍历游标输出结果
  FETCH v_result_cursor INTO v_user_record;
  WHILE v_result_cursor%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('用户ID: ' || v_user_record.user_id || '，用户名: ' || v_user_record.username);
    FETCH v_result_cursor INTO v_user_record;
  END LOOP;
  
  CLOSE v_result_cursor;
END;
/

内容的提问来源于stack exchange，提问作者Alan Pollard