Oracle PL/SQL自定义RECORD集合如何与物理表进行关联查询

报错原因 #

你当前定义的record和recordTable都属于PL/SQL私有类型，仅能在PL/SQL引擎中被识别，SQL执行引擎无法解析该类型，因此直接在SELECT语句中引用集合变量会提示表或视图不存在。

解决方案 #

方案1：Oracle 12c及以上版本直接使用 #

12c及更高版本的Oracle已经支持在SQL语句中直接引用PL/SQL定义的集合类型，你只需要用TABLE()函数包裹变量即可正常关联查询：

SELECT t.*, 业务表.其他字段
FROM TABLE(recordVar) t
JOIN 你的业务表 ON t.a = 业务表.关联字段;

如果出现识别异常，可添加VALUE提示强制SQL引擎识别变量：

SELECT t.*
FROM TABLE(/*+ VALUE(recordVar) */ recordVar) t;

方案2：兼容全版本（需创建SQL级公共类型） #

如果使用11g及更早版本的Oracle，需要先在SQL层面创建公共的对象类型和嵌套表类型，步骤如下：

1. 执行SQL创建对象类型，替换原PL/SQL内定义的RECORD：

CREATE OR REPLACE TYPE sql_record AS OBJECT (
  a NUMBER,
  b VARCHAR2(20)
);
/

2. 执行SQL创建对应的嵌套表类型：

CREATE OR REPLACE TYPE sql_record_table IS TABLE OF sql_record;
/

3. 在PL/SQL中改用新的SQL级类型声明变量：

-- 声明变量，替代原来的recordTable类型
recordVar sql_record_table := sql_record_table();
-- 填充数据示例，需要用对象构造函数赋值
recordVar.EXTEND;
recordVar(recordVar.LAST) := sql_record(1, '测试数据');

4. 此时即可正常在SQL中关联查询：

SELECT t.*, s.其他字段
FROM TABLE(recordVar) t
JOIN 业务表 s ON t.a = s.关联字段;

注意：如果需要在PL/SQL块/存储过程外部直接执行关联查询，只能使用方案2创建公共类型，PL/SQL内部定义的私有类型无法在块外被识别。

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