◉ Setup
The examples in this article follow the same pattern. They populate a collection, then instantly select from the collection using the TABLE operator, and print out the contents. The code is merely to demonstrate the fact the local collection is accessible using the TABLE operator. It is not suggesting it is sensible to populate the collection then instantly select from it to process the data.
All examples use the EMP table from the SCOTT schema. If it is not present you can install the schema using the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script on the server, or build the tables in your local schema using the script below.
CREATE TABLE DEPT (
DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
) ;
CREATE TABLE EMP (
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
◉ Associative Array Example
This example uses an associative array based on a %ROWTYPE defined in the package specification.
CREATE OR REPLACE PACKAGE test_api AS
TYPE t_tab IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
PROCEDURE test1;
END;
/
CREATE OR REPLACE PACKAGE BODY test_api AS
PROCEDURE test1 IS
l_tab1 t_tab;
BEGIN
SELECT *
BULK COLLECT INTO l_tab1
FROM emp
WHERE deptno = 10;
DBMS_OUTPUT.put_line('Loop Through Collection');
FOR cur_rec IN (SELECT *
FROM TABLE(l_tab1))
LOOP
DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
END LOOP;
END;
END;
/
SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782 : CLARK
7839 : KING
7934 : MILLER
PL/SQL procedure successfully completed.
SQL>
◉ Nested Table Example
CREATE OR REPLACE PACKAGE test_api AS
TYPE t_tab IS TABLE OF emp%ROWTYPE;
PROCEDURE test1;
END;
/
CREATE OR REPLACE PACKAGE BODY test_api AS
PROCEDURE test1 IS
l_tab1 t_tab;
BEGIN
SELECT *
BULK COLLECT INTO l_tab1
FROM emp
WHERE deptno = 10;
DBMS_OUTPUT.put_line('Loop Through Collection');
FOR cur_rec IN (SELECT *
FROM TABLE(l_tab1))
LOOP
DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
END LOOP;
END;
END;
/
SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782 : CLARK
7839 : KING
7934 : MILLER
PL/SQL procedure successfully completed.
SQL>
◉ Varray Example
This example uses a varray based on a %ROWTYPE defined in the package specification.
CREATE OR REPLACE PACKAGE test_api AS
TYPE t_tab IS VARRAY(10) OF emp%ROWTYPE;
PROCEDURE test1;
END;
/
CREATE OR REPLACE PACKAGE BODY test_api AS
PROCEDURE test1 IS
l_tab1 t_tab;
BEGIN
SELECT *
BULK COLLECT INTO l_tab1
FROM emp
WHERE deptno = 10;
DBMS_OUTPUT.put_line('Loop Through Collection');
FOR cur_rec IN (SELECT *
FROM TABLE(l_tab1))
LOOP
DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
END LOOP;
END;
END;
/
SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782 : CLARK
7839 : KING
7934 : MILLER
PL/SQL procedure successfully completed.
SQL>
◉ Supported Types
The previous examples all use a %ROWTYPE of a schema table for the collection type. It is also possible to use %TYPE, a scalar and a local record type.
The following example uses an associated array based on a %TYPE. Notice the column is referenced as COLUMN_VALUE.
CREATE OR REPLACE PACKAGE test_api AS
TYPE t_tab IS TABLE OF emp.empno%TYPE
INDEX BY BINARY_INTEGER;
PROCEDURE test1;
END;
/
CREATE OR REPLACE PACKAGE BODY test_api AS
PROCEDURE test1 IS
l_tab1 t_tab;
BEGIN
SELECT empno
BULK COLLECT INTO l_tab1
FROM emp
WHERE deptno = 10;
DBMS_OUTPUT.put_line('Loop Through Collection');
FOR cur_rec IN (SELECT *
FROM TABLE(l_tab1))
LOOP
DBMS_OUTPUT.put_line(cur_rec.column_value);
END LOOP;
END;
END;
/
SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782
7839
7934
PL/SQL procedure successfully completed.
SQL>
The following example uses an associated array based on a scalar. Notice the column is referenced as COLUMN_VALUE.
CREATE OR REPLACE PACKAGE test_api AS
TYPE t_tab IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
PROCEDURE test1;
END;
/
CREATE OR REPLACE PACKAGE BODY test_api AS
PROCEDURE test1 IS
l_tab1 t_tab;
BEGIN
SELECT empno
BULK COLLECT INTO l_tab1
FROM emp
WHERE deptno = 10;
DBMS_OUTPUT.put_line('Loop Through Collection');
FOR cur_rec IN (SELECT *
FROM TABLE(l_tab1))
LOOP
DBMS_OUTPUT.put_line(cur_rec.column_value);
END LOOP;
END;
END;
/
SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782
7839
7934
PL/SQL procedure successfully completed.
SQL>
The following example uses an associated array based on a local record type.
CREATE OR REPLACE PACKAGE test_api AS
TYPE t_row IS RECORD (
empno NUMBER(4),
ename VARCHAR2(10)
);
TYPE t_tab IS TABLE OF t_row
INDEX BY BINARY_INTEGER;
PROCEDURE test1;
END;
/
CREATE OR REPLACE PACKAGE BODY test_api AS
PROCEDURE test1 IS
l_tab1 t_tab;
BEGIN
SELECT empno, ename
BULK COLLECT INTO l_tab1
FROM emp
WHERE deptno = 10;
DBMS_OUTPUT.put_line('Loop Through Collection');
FOR cur_rec IN (SELECT *
FROM TABLE(l_tab1))
LOOP
DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
END LOOP;
END;
END;
/
SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782 : CLARK
7839 : KING
7934 : MILLER
PL/SQL procedure successfully completed.
SQL>
◉ Location of Type Definition
For this new functionality to work, the type must be defined in package specification. The examples below show how alternate locations fail.
The following example moves the type definition to the package body, which results in a compilation failure.
CREATE OR REPLACE PACKAGE test_api AS
PROCEDURE test1;
END;
/
CREATE OR REPLACE PACKAGE BODY test_api AS
TYPE t_tab IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
PROCEDURE test1 IS
l_tab1 t_tab;
BEGIN
SELECT *
BULK COLLECT INTO l_tab1
FROM emp
WHERE deptno = 10;
DBMS_OUTPUT.put_line('Loop Through Collection');
FOR cur_rec IN (SELECT *
FROM TABLE(l_tab1))
LOOP
DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
END LOOP;
END;
END;
/
Warning: Package Body created with compilation errors.
SQL> show errors
Errors for PACKAGE BODY TEST_API:
LINE/COL ERROR
-------- -----------------------------------------------------------------
15/21 PL/SQL: SQL Statement ignored
16/28 PL/SQL: ORA-22905: cannot access rows from a non-nested table
item
16/34 PLS-00382: expression is of wrong type
18/7 PL/SQL: Statement ignored
18/28 PLS-00364: loop index variable 'CUR_REC' use is invalid
SQL>
The following example attempts to use the functionality in an anonymous block, which results in an error.
DECLARE
TYPE t_tab IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
l_tab1 t_tab;
BEGIN
SELECT *
BULK COLLECT INTO l_tab1
FROM emp
WHERE deptno = 10;
DBMS_OUTPUT.put_line('Loop Through Collection');
FOR cur_rec IN (SELECT *
FROM TABLE(l_tab1))
LOOP
DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
END LOOP;
END;
/
ERROR at line 14:
ORA-06550: line 14, column 32:
PLS-00382: expression is of wrong type
ORA-06550: line 14, column 26:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 13, column 19:
PL/SQL: SQL Statement ignored
ORA-06550: line 16, column 26:
PLS-00364: loop index variable 'CUR_REC' use is invalid
ORA-06550: line 16, column 5:
PL/SQL: Statement ignored
SQL>
Posts
0 comments:
Post a Comment