select * from scott.emp| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | 7839 | KING | PRESIDENT | - | 17-NOV-81 | 5000 | - | 10 | 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 | 2850 | - | 30 | 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 | 2450 | - | 10 | 7566 | JONES | MANAGER | 7839 | 02-APR-81 | 2975 | - | 20 | 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 | 3000 | - | 20 | 7902 | FORD | ANALYST | 7566 | 03-DEC-81 | 3000 | - | 20 | 7369 | SMITH | CLERK | 7902 | 17-DEC-80 | 800 | - | 20 | 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 | 1600 | 300 | 30 | 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 | 1250 | 500 | 30 | 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 | 1250 | 1400 | 30 | 7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 | 1500 | 0 | 30 | 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 | 1100 | - | 20 | 7900 | JAMES | CLERK | 7698 | 03-DEC-81 | 950 | - | 30 | 7934 | MILLER | CLERK | 7782 | 23-JAN-82 | 1300 | - | 10 |
|---|
select * from scott.dept| DEPTNO | DNAME | LOC | 10 | ACCOUNTING | NEW YORK | 20 | RESEARCH | DALLAS | 30 | SALES | CHICAGO | 40 | OPERATIONS | BOSTON |
|---|
Creating a schema-level nested table
create or replace type enames_type is table of varchar2(10); Type created.
Converting a database table to nested table using MULTISET operator
DECLARE
CURSOR dept_cur IS SELECT deptno,dname,loc,
CAST(MULTISET(SELECT ename FROM scott.emp e WHERE e.deptno=d.deptno) AS enames_type) enames
FROM scott.dept d WHERE deptno=30;
cur_row dept_cur%ROWTYPE;
idx PLS_INTEGER;
BEGIN
OPEN dept_cur;
FETCH dept_cur INTO cur_row;
CLOSE dept_cur;
idx := cur_row.enames.FIRST;
DBMS_OUTPUT.put_line('Employees of Department '||cur_row.dname||' are: ');
WHILE idx IS NOT NULL
LOOP
DBMS_OUTPUT.put_line(cur_row.enames(idx));
idx:=cur_row.enames.NEXT(idx);
END LOOP;
END;
Statement processed.
Employees of Department SALES are:
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES