Oracle SQL 19c中如何在后续查询中使用数组？

问题 #

需要从单个entitykey出发，关联多表生成密钥数组，再用这个数组执行关联查询获取最终结果。现有可生成密钥数组的PL/SQL脚本，但后续使用数组的查询无法正常运行。

已实现的数组生成脚本 #

CREATE OR REPLACE TYPE varray IS TABLE OF number;

DECLARE
      releasableentitykey  varray := varray();
BEGIN
      FOR i IN (SELECT * FROM masterbatchrecordreleasesignat  
              INNER JOIN masterbatchrecord ON masterbatchrecordreleasesignat.releasableentitykey = masterbatchrecord.entitykey
              WHERE masterbatchrecord.entitykey='7884867406')
            LOOP
        releasableentitykey.extend;
        releasableentitykey(releasableentitykey.count) := i.Releasesignaturecollectionenti;
            END LOOP;
 END;

无法运行的查询 #

SELECT * FROM signature  
INNER JOIN releasesignature ON signature.entitykey = releasesignature.signatureentitykey
INNER JOIN masterbatchrecordreleasesignat ON releasesignature.entitykey = masterbatchrecordreleasesignat.releasesignaturecollectionenti
WHERE masterbatchrecordreleasesignat.releasesignaturecollectionenti IN (SELECT column_value FROM TABLE(releasableentitykey);

解决方案 #

问题根源 #

1. releasableentitykey是PL/SQL块的局部变量，外部SQL语句无法直接访问该变量。
2. 最后一条查询存在语法错误：TABLE(releasableentitykey)后缺少闭合括号。

方案一：PL/SQL块内直接执行查询 #

如果需要保留数组逻辑，可在PL/SQL块内部完成数组填充+结果查询的全流程，避免变量作用域问题：

CREATE OR REPLACE TYPE varray IS TABLE OF number;

DECLARE
      releasableentitykey  varray := varray();
      -- 定义游标存储查询结果结构
      CURSOR c_final_result IS
          SELECT s.*, rs.*, mbrs.*
          FROM signature s
          INNER JOIN releasesignature rs ON s.entitykey = rs.signatureentitykey
          INNER JOIN masterbatchrecordreleasesignat mbrs ON rs.entitykey = mbrs.releasesignaturecollectionenti
          WHERE mbrs.releasesignaturecollectionenti IN (SELECT column_value FROM TABLE(releasableentitykey));
      v_result_row c_final_result%ROWTYPE;
BEGIN
      -- 第一步：填充密钥数组
      FOR i IN (SELECT * FROM masterbatchrecordreleasesignat  
              INNER JOIN masterbatchrecord ON masterbatchrecordreleasesignat.releasableentitykey = masterbatchrecord.entitykey
              WHERE masterbatchrecord.entitykey='7884867406')
      LOOP
          releasableentitykey.extend;
          releasableentitykey(releasableentitykey.count) := i.Releasesignaturecollectionenti;
      END LOOP;

      -- 第二步：执行查询并处理结果（示例为输出数据，可按需修改）
      OPEN c_final_result;
      LOOP
          FETCH c_final_result INTO v_result_row;
          EXIT WHEN c_final_result%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE('签名ID：' || v_result_row.entitykey || '，关联密钥：' || v_result_row.signatureentitykey);
      END LOOP;
      CLOSE c_final_result;
END;
/

方案二：用子查询替代数组（最简方案） #

无需单独生成数组，直接将密钥生成逻辑作为子查询嵌入最终SQL，完全避免变量作用域问题：

SELECT s.*, rs.*, mbrs.*
FROM signature s
INNER JOIN releasesignature rs ON s.entitykey = rs.signatureentitykey
INNER JOIN masterbatchrecordreleasesignat mbrs ON rs.entitykey = mbrs.releasesignaturecollectionenti
WHERE mbrs.releasesignaturecollectionenti IN (
    SELECT mbrs_inner.Releasesignaturecollectionenti
    FROM masterbatchrecordreleasesignat mbrs_inner
    INNER JOIN masterbatchrecord mbr ON mbrs_inner.releasableentitykey = mbr.entitykey
    WHERE mbr.entitykey='7884867406'
);

方案三：全局包变量复用数组 #

如果需要在多个SQL/PL/SQL块中复用密钥数组，可将变量定义为包级全局变量：

-- 第一步：创建存储全局变量的包
CREATE OR REPLACE PACKAGE key_storage_package IS
    releasableentitykey varray := varray();
END key_storage_package;
/

-- 第二步：填充全局数组
DECLARE
BEGIN
    FOR i IN (SELECT * FROM masterbatchrecordreleasesignat  
            INNER JOIN masterbatchrecord ON masterbatchrecordreleasesignat.releasableentitykey = masterbatchrecord.entitykey
            WHERE masterbatchrecord.entitykey='7884867406')
    LOOP
        key_storage_package.releasableentitykey.extend;
        key_storage_package.releasableentitykey(key_storage_package.releasableentitykey.count) := i.Releasesignaturecollectionenti;
    END LOOP;
END;
/

-- 第三步：执行查询调用全局数组
SELECT s.*, rs.*, mbrs.*
FROM signature s
INNER JOIN releasesignature rs ON s.entitykey = rs.signatureentitykey
INNER JOIN masterbatchrecordreleasesignat mbrs ON rs.entitykey = mbrs.releasesignaturecollectionenti
WHERE mbrs.releasesignaturecollectionenti IN (SELECT column_value FROM TABLE(key_storage_package.releasableentitykey));

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