Recently I did some work where we had a big selection of data that was returned by a function into a collection. The data needed to be manipulated in a few different ways. One of the great things in PL/SQL is you can cast the collection or nested table as normal database table.
This means you can use the processing power on SQL to do all sorts of things that would require a lot of work if you were just using standard array logic.
Below is an example of creating a simple nested table and then using SQL to sort it. This is a very simple example to show you how to use it. You can treat this table like any other table. Join it to normal database tables or even join it to another version of itself.
CREATE OR REPLACE
TYPE
pet_details AS OBJECT
(
pet_id NUMBER(3),
pet_name VARCHAR2(200),
pet_age NUMBER(3)
)
/
CREATE OR REPLACE
TYPE
list_of_pets AS TABLE OF pet_details
/
DECLARE
v_list_of_pets list_of_pets := list_of_pets();
v_pet_details pet_details;
-- pass the nested table in as a parameter to the cursor
CURSOR c_test (cp_input_list list_of_pets) IS
SELECT *
FROM TABLE(cp_input_list)
ORDER BY pet_age;
BEGIN
-- put some sample data into a nested table
v_pet_details := pet_details(1, 'Tom', 14);
v_list_of_pets.EXTEND;
v_list_of_pets(1) := v_pet_details;
v_pet_details := pet_details(2, 'Jenny', 2);
v_list_of_pets.EXTEND;
v_list_of_pets(2) := v_pet_details;
v_pet_details := pet_details(3, 'Rambo', 5);
v_list_of_pets.EXTEND;
v_list_of_pets(3) := v_pet_details;
-- Now pass the nested table into the cursor and loop
FOR v_test IN c_test(v_list_of_pets) LOOP
dbms_output.put_line('Show me some pets ' ||
to_char(v_test.pet_id) ||','||
v_test.pet_name ||','||
to_char(v_test.pet_age));
END LOOP;
END;
