Oracle中如何从记录数组查询字段值？解决PLS-00642错误

在Oracle数据库中定义了如下本地记录类型与集合类型：

type r_student_type is record(
  id    number,
  name  varchar2(32),
  class varchar2(32));

type t_students_type is table of r_student_type;

t_students t_students_type := t_students_type();

该数组t_students已填充示例数据：

+------+--------+-------+
| id   | name   | class |
+------+--------+-------+
| 4551 | Walter | A     |
+------+--------+-------+
| 4552 | Marie  | B     |
+------+--------+-------+
| 4553 | Hank   | B     |
+------+--------+-------+
| 4554 | Skyler | A     |
+------+--------+-------+

需求是通过单个SELECT语句查询数组中记录的特定字段值（例如统计不同班级数量，判断所有学生是否同属一个班级），但尝试以下SQL时触发错误PLS-00642: local collection types not allowed in SQL statements：

select count(distinct class)
  into l_class_cnt
  from (select class
          from table(t_students));

本地PL/SQL集合类型无法直接在SQL语句中使用，以下两种方案可实现单个SELECT查询的需求：

方案1：将集合类型改为SQL级别的全局类型 #

将原本的本地记录/集合类型迁移到SQL层定义（全局类型允许在SQL中直接使用）：

1. 先创建SQL级别的对象类型与集合类型：

CREATE OR REPLACE TYPE r_student_obj AS OBJECT (
  id    NUMBER,
  name  VARCHAR2(32),
  class VARCHAR2(32)
);
/

CREATE OR REPLACE TYPE t_students_tab AS TABLE OF r_student_obj;
/

2. 在PL/SQL中使用该全局类型并执行查询：

DECLARE
  t_students t_students_tab := t_students_tab();
  l_class_cnt NUMBER;
BEGIN
  -- 填充示例数据
  t_students.extend(4);
  t_students(1) := r_student_obj(4551, 'Walter', 'A');
  t_students(2) := r_student_obj(4552, 'Marie', 'B');
  t_students(3) := r_student_obj(4553, 'Hank', 'B');
  t_students(4) := r_student_obj(4554, 'Skyler', 'A');

  -- 单个SELECT语句完成统计
  SELECT COUNT(DISTINCT class)
  INTO l_class_cnt
  FROM TABLE(t_students);

  DBMS_OUTPUT.PUT_LINE('不同班级数量：' || l_class_cnt);
END;
/

方案2：使用XMLTABLE转换本地集合 #

无需修改原有本地类型，通过XMLTYPE将集合转换为XML格式，再用XMLTABLE解析后查询：

DECLARE
  type r_student_type is record(
    id    number,
    name  varchar2(32),
    class varchar2(32));

  type t_students_type is table of r_student_type;

  t_students t_students_type := t_students_type();
  l_class_cnt NUMBER;
BEGIN
  -- 填充示例数据
  t_students.extend(4);
  t_students(1).id := 4551; t_students(1).name := 'Walter'; t_students(1).class := 'A';
  t_students(2).id := 4552; t_students(2).name := 'Marie'; t_students(2).class := 'B';
  t_students(3).id := 4553; t_students(3).name := 'Hank'; t_students(3).class := 'B';
  t_students(4).id := 4554; t_students(4).name := 'Skyler'; t_students(4).class := 'A';

  -- 用XMLTABLE实现单个SELECT查询
  SELECT COUNT(DISTINCT class)
  INTO l_class_cnt
  FROM XMLTABLE(
    '/ROWSET/ROW'
    PASSING XMLTYPE(t_students)
    COLUMNS class VARCHAR2(32) PATH 'CLASS'
  );

  DBMS_OUTPUT.PUT_LINE('不同班级数量：' || l_class_cnt);
END;
/

补充说明 #

如果仅需判断所有学生是否属于同一班级，可直接修改SELECT语句为：

SELECT CASE WHEN COUNT(DISTINCT class) = 1 THEN '是' ELSE '否' END
INTO l_is_same_class
FROM ...;

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