Below are some of the PL/SQL Collections
OBJECT Type
TABLE Type
RECORD Type
Hope you can google about these to get more information.
In this article I like to explain how to retrieve records from a table and store it in a TABLE type.
Create a table emp_tb as given below and insert some values
CREATE TABLE emp_tb ( empid NUMBER , NAME VARCHAR2(20) );
INSERT INTO emp_tb VALUES (10, 'Bala');
INSERT INTO emp_tb VALUES (20, 'Kaarhtik');
Now create a OBJECT type same like the table structure.
CREATE OR REPLACE TYPE emp_type IS OBJECT (
empid NUMBER,
NAME VARCHAR2 (20)
);
Create TABLE type with the object object
CREATE OR REPLACE TYPE emp_tb_type IS TABLE OF emp_type;
Below is a PLSQL block to retrieve the data from emp_tb and store it in the emp_tb_type object.
SET serveroutput on
DECLARE
v_tab emp_tb_type;
v_rec emp_type;
BEGIN
v_tab := emp_tb_type ();
FOR i IN (SELECT * FROM emp_tb)
LOOP
v_tab.EXTEND ();
v_rec := emp_type (i.empid, i.name);
v_tab (v_tab.LAST) := v_rec;
END LOOP;
FOR i IN v_tab.FIRST .. v_tab.LAST
LOOP
DBMS_OUTPUT.put_line (v_tab (i).empid || '-' || v_tab (i).name);
END LOOP;
END;
/
v_tab - is a variable for the TABLE type emp_tb_type
v_rec - is a variable for the OBJECT type emp_type. This variable is create to assign the value in the TABLE type variable v_tab. This is how a record is inserted into the table type object
emp_tb_type() - This is a constructor function which will be created along with the OBJECT type definition. We need to call this to initialize the variable v_tab.
EXTEND() - This is used to create one record index (Last record in the TABLE type variable)
emp_type(i.empid,i.name) - This is how a record is created from a OBJECT type so that we can assign it to its variable v_rec
v_tab(v_tabl.LAST) - This is used to navigate to the end of the TABLE type record so that we can store the new value at the end.
Later of the code is use to retrieve the records from the TABLE type to display.
Below is the sample output.
10-Bala
20-Kaarhtik