创建自己的类型。下面是一个基于Scott的示例模式的示例（因此表与数据模型略有不同，您必须调整此代码）。

SQL> create or replace type t_row is object
  2    (ename varchar2(10),
  3     dname varchar2(10),
  4     loc   varchar2(10)
  5    );
  6  /

Type created.

SQL> create or replace type t_tab is table of t_row;
  2  /

Type created.

样本数据：

SQL> select ename from emp where deptno = 10;

ENAME
----------
CLARK
KING
MILLER

程序：

SQL> set serveroutput on
SQL> declare
  2    l_tab t_tab;
  3  begin
  4    select t_row(e.ename, d.dname, d.loc)
  5      bulk collect into l_tab
  6      from emp e join dept d on e.deptno = d.deptno
  7      where d.deptno = 10;
  8
  9    for i in 1 .. l_tab.count loop
 10      dbms_output.put_line(l_tab(i).dname ||' - '|| l_tab(i).loc ||' - '|| l_tab(i).ename);
 11    end loop;
 12  end;
 13  /
ACCOUNTING - NEW YORK - CLARK
ACCOUNTING - NEW YORK - KING
ACCOUNTING - NEW YORK - MILLER

PL/SQL procedure successfully completed.

SQL>

但显示输出不按顺序
所有集合都将具有相同数量的元素;循环一次并输出每次循环迭代的值：

DECLARE
  TYPE empno_tab  IS TABLE OF EMP.EMPNO%TYPE;
  TYPE deptno_tab IS TABLE OF DEPT.DEPTNO%TYPE;
  TYPE locid_tab  IS TABLE OF LOC.LOCID%TYPE;
  empnos  empno_tab;
  deptnos deptno_tab;
  locids  locid_tab;
BEGIN
  SELECT E.EMPNO,
         D.DEPTNO,
         L.LOCID
  BULK COLLECT INTO
         empnos,
         deptnos,
         locids
  FROM   EMP  E
         INNER JOIN DEPT D
         ON (E.DEPTNO = D.DEPTNO)
         INNER JOIN LOC L
         ON (D.LOCID = L.LOCID)
  ORDER BY e.empno, d.deptno, l.locid;

  FOR i IN 1 .. empnos.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(empnos(i) || ', ' || deptnos(i) || ', ' || locids(i));
  END LOOP;
END;
/

或者，使用记录作为列的组合：

DECLARE
  TYPE emp_dept_loc_rec IS RECORD(
    empno  EMP.EMPNO%TYPE,
    deptno DEPT.DEPTNO%TYPE,
    locid  LOC.LOCID%TYPE
  );
  TYPE emp_dept_loc_tab IS TABLE OF emp_dept_loc_rec;
  emp_dept_locs emp_dept_loc_tab;
BEGIN
  SELECT E.EMPNO,
         D.DEPTNO,
         L.LOCID
  BULK COLLECT INTO
         emp_dept_locs
  FROM   EMP  E
         INNER JOIN DEPT D
         ON (E.DEPTNO = D.DEPTNO)
         INNER JOIN LOC L
         ON (D.LOCID = L.LOCID)
  ORDER BY e.empno, d.deptno, l.locid;

  FOR i IN 1 .. emp_dept_locs.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(emp_dept_locs(i).empno || ', ' || emp_dept_locs(i).deptno || ', ' || emp_dept_locs(i).locid);
  END LOOP;
END;
/

• 注意：您可以：使用现代连接语法;不需要使用关联数组;在定义集合类型时直接使用列类型;并在查询中添加ORDER BY子句以指定序列的顺序。*

其中，对于样本数据：

CREATE TABLE emp(empno, deptno) AS
  SELECT 1, 1 FROM DUAL UNION ALL
  SELECT 2, 1 FROM DUAL UNION ALL
  SELECT 3, 2 FROM DUAL;

CREATE TABLE dept(deptno, locid) AS
  SELECT 1, 1 FROM DUAL UNION ALL
  SELECT 2, 2 FROM DUAL;

CREATE TABLE loc(locid) AS
  SELECT 1 FROM DUAL UNION ALL
  SELECT 2 FROM DUAL;

两个输出：

1, 1, 1
2, 1, 1
3, 2, 2

fiddle