PL SQL Collections and Records

Composite Data Types
Are of two types:
– PL/SQL RECORDs
– PL/SQL Collections
– INDEX BY Table
– Nested Table
– VARRAY

PL/SQL Records

Must contain one or more components of any scalar, RECORD, or INDEX BY table data type, called fields.
Are similar in structure to records in a third generation language (3GL).
Are not the same as rows in a database table.
Treat a collection of fields as a logical unit.
Are convenient for fetching a row of data from a table for processing.

TYPE emp_record_type IS RECORD
(last_name VARCHAR2(25),
job_id VARCHAR2(10),
salary NUMBER(8,2));
emp_record emp_record_type;

%ROWTYPE Attribute

Declare a variable to store the information about a
department from the DEPARTMENTS table.
                dept_record departments%ROWTYPE;

Declare a variable to store the information about an
employee from the EMPLOYEES table.
                emp_record employees%ROWTYPE;

INDEX BY Tables
• Are composed of two components:
      – Primary key of data type BINARY_INTEGER
      – Column of scalar or record data type
• Can increase in size dynamically because they are
unconstrained

TYPE ename_table_type IS TABLE OF employees.last_name%TYPE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;

DECLARE
TYPE ename_table_type IS TABLE OF employees.last_name%TYPE
INDEX BY BINARY_INTEGER;
TYPE hiredate_table_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
hiredate_table hiredate_table_type;
BEGIN
ename_table(1) := ‘CAMERON’;
hiredate_table(8) := SYSDATE + 7;
IF ename_table.EXISTS(1) THEN
INSERT INTO …

END;

The following methods make INDEX BY tables
easier to use:
– NEXT
– TRIM
– DELETE
– EXISTS
– COUNT
– FIRST and LAST
– PRIOR

INDEX BY Table of Records
• Define a TABLE variable with a permitted PL/SQL
data type.
• Declare a PL/SQL variable to hold department information.

Example:
DECLARE
TYPE dept_table_type IS TABLE OF
departments%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_table dept_table_type;
— Each element of dept_table is a record

 

DECLARE
TYPE emp_table_type is table of
employees%ROWTYPE INDEX BY BINARY_INTEGER;
my_emp_table emp_table_type;
v_count NUMBER(3):= 104;
BEGIN
FOR i IN 100..v_count
LOOP
SELECT * INTO my_emp_table(i) FROM employees
WHERE employee_id = i;
END LOOP;
FOR i IN my_emp_table.FIRST..my_emp_table.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);
END LOOP;
END;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: