composite data type Are of two types:
– PL/SQL RECORDs
– PL/SQL Collections
– INDEX BYTable
– Nested Table
– VARRAY
• Contain internal components
• Are reusable
PL/SQL Records
• Must contain one or more components of any scalar, RECORD, or INDEX BYtable 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
Creating a PL/SQL Record
Declare variables to store the name, job, and salary of a new employee.
Example:
TYPE emp_record_type IS RECORD
(last_name VARCHAR2(25),
job_id VARCHAR2(10),
salary NUMBER(8,2));
emp_record emp_record_type;
Advantages of Using %ROWTYPE
• The number and data types of the underlying database columns need not be known.
• The number and data types of the underlying database column may change at run time.
• The attribute is useful when retrieving a row with the SELECT *statement.
Examples:
Declare a variable to store the information about a department from the DEPARTMENTStable.
dept_record departments%ROWTYPE;
INDEXBYTables
• 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
Example of INDEXBYTable of Records
SET SERVEROUTPUT ON
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;
