Suppose you have data in your PL/SQL program stored in a collection. I am using the EMP table (14 records) but you should imagine you have many, many more records in your collection. If you want to order (sort) your collection in a different manner somewhere in your code you can of course write your own sorting routine but the Oracle database is doing this for ages and probably a lot smarter (after all these years) than you can ever do it.
If you are running an Oracle pre 12c database you can use the SQL-engine when you have your data in a Nested Table. I have created this little test script to try this. Again, the EMP table has only 14 records defined, but it is used to drive the point home.
CREATE OR REPLACE TYPE emp_t AS OBJECT ( empno number(4) , ename varchar2(10) , job varchar2(9) , mgr number(4) , hiredate DATE , sal number(7,2) , comm number(7,2) , deptno number(2) )
CREATE OR REPLACE TYPE emp_ntt AS TABLE OF emp_t
CREATE OR REPLACE PACKAGE emp_test IS FUNCTION get_emp RETURN emp_ntt; PROCEDURE emp_prc; END;
CREATE OR REPLACE PACKAGE BODY emp_test IS PROCEDURE separator IS BEGIN dbms_output.put_line('-=-=-=-=-'); END; -- FUNCTION get_emp RETURN emp_ntt IS l_returnvalue emp_ntt; CURSOR c_emp IS SELECT emp_t(empno, ename, job, mgr, hiredate, sal, comm, deptno) FROM emp; BEGIN OPEN c_emp; FETCH c_emp BULK COLLECT INTO l_returnvalue; CLOSE c_emp; RETURN l_returnvalue; END; -- PROCEDURE emp_prc IS l_emps emp_ntt; l_emps_sorted emp_ntt; CURSOR c_emp_sort(emps_in emp_ntt) IS SELECT emp_t(empno, ename, job, mgr, hiredate, sal, comm, deptno) FROM table(emps_in) ORDER BY ename; BEGIN l_emps := get_emp; FOR rec IN (select ename FROM table(l_emps)) loop dbms_output.put_line(rec.ename); END loop; -- separator; -- OPEN c_emp_sort(l_emps); FETCH c_emp_sort BULK COLLECT INTO l_emps_sorted; CLOSE c_emp_sort; FOR indx IN l_emps_sorted.first .. l_emps_sorted.last loop dbms_output.put_line(l_emps_sorted(indx).ename); END loop; END; END;
If I run this code
EXEC emp_test.emp_prc
the output looks like this:
SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER -=-=-=-=- ADAMS ALLEN BLAKE CLARK FORD JAMES JONES KING MARTIN MILLER SCOTT SMITH TURNER WARD
This is a great way of using the SQL engine to do the work you need done.
But what if you don’t have your data in a Nested Table, but in an Associative Array, which is PL/SQL only. If you are using Database 12c you’re in luck, because you can now use your Associative Array in de SQL statement.
I have created a similar script as the one before, except that I cannot define a TYPE in the SQL layer, it should be defined in a package specification.
PACKAGE emp_test IS TYPE emp_aat IS TABLE OF emp%rowtype INDEX BY PLS_INTEGER; FUNCTION get_emp RETURN emp_aat; PROCEDURE emp_prc; END;
PACKAGE BODY emp_test IS PROCEDURE separator IS BEGIN dbms_output.put_line('-=-=-=-=-'); END; -- FUNCTION get_emp RETURN emp_aat IS l_returnvalue emp_aat; CURSOR c_emp IS SELECT * FROM emp; BEGIN OPEN c_emp; FETCH c_emp BULK COLLECT INTO l_returnvalue; CLOSE c_emp; RETURN l_returnvalue; END; -- PROCEDURE emp_prc IS l_emps emp_aat; l_emps_sorted emp_aat; CURSOR c_emp_sort(emps_in emp_aat) IS SELECT * FROM table(emps_in) ORDER BY ename; BEGIN l_emps := get_emp; FOR rec IN (select ename FROM TABLE(l_emps)) LOOP dbms_output.put_line(rec.ename); END LOOP; -- separator; -- OPEN c_emp_sort(l_emps); FETCH c_emp_sort BULK COLLECT INTO l_emps_sorted; CLOSE c_emp_sort; FOR indx IN l_emps_sorted.first .. l_emps_sorted.last LOOP dbms_output.put_line(l_emps_sorted(indx).ename); END LOOP; END; END;
If I run this code on my 11Gr2 database it results in the following error:
No errors for PACKAGE DEMO.EMP_TEST
Warning: Package body created with compilation errors
Errors for PACKAGE BODY DEMO.EMP_TEST:
LINE/COL ERROR
-------- ---------------------------------------------------------------------------
27/5 PL/SQL: SQL Statement ignored
28/12 PL/SQL: ORA-22905: cannot access rows from a non-nested table item
28/18 PLS-00382: expression is of wrong type
33/17 PL/SQL: SQL Statement ignored
33/35 PL/SQL: ORA-22905: cannot access rows from a non-nested table item
33/41 PLS-00382: expression is of wrong type
34/7 PL/SQL: Statement ignored
34/28 PLS-00364: loop index variable 'REC' use is invalid
As mentioned before, you cannot access an Associative Array in the SQL layer. To use the SQL layer anyway you can of course create the necessary objects (record type and nested table type), copy all the data from the Associative Array to a Nested Table, run the SQL statement and copy the results back to an Associative Array. This means a lot of coding and the risk of errors. But if I run the same code on my 12Cr1 database, it completes successfully and displays the following result:
SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER -=-=-=-=- ADAMS ALLEN BLAKE CLARK FORD JAMES JONES KING MARTIN MILLER SCOTT SMITH TURNER WARD
I think Oracle does a good job extending the possibilities of the PL/SQL language, especially integrating to SQL features into the PL/SQL layer. Although this might look like a small enhancement, but it can have a big impact on the performance of your program.