PLSQL Collections and Records
Collections -
In PL/SQL, array types are known as varrays (short for variable-size arrays), set types are known as nested tables, and hash table types are known as associative arrays.
Use TYPE definition to declare collections.
TYPE staff_list IS TABLE OF employees.employee_id%TYPE;
staff staff_list;
lname employees.las
t_name%TYPE;
Collections can be passed as parameters, so that subprograms can process arbitrary numbers of elements.
You can use collections to move data into and out of database tables using high-performance language features known as bulk SQL.
Records
- Records are data structures which has fields and can have different datatypes. You can pass them to subprograms with a single parameter.
You can use the %ROWTYPE attribute to declare a record that represents a row in a table or a row from a query result set, without specifying the names and types for the fields.
TYPE timerec IS RECORD (hours SMALLINT, minutes SMALLINT);
TYPE meetin_typ IS RECORD (
date_held DATE,
duration timerec, -- nested record
location VARCHAR2(20));
Collections -
In PL/SQL, array types are known as varrays (short for variable-size arrays), set types are known as nested tables, and hash table types are known as associative arrays.
Use TYPE definition to declare collections.
TYPE staff_list IS TABLE OF employees.employee_id%TYPE;
staff staff_list;
lname employees.las
t_name%TYPE;Collections can be passed as parameters, so that subprograms can process arbitrary numbers of elements.
You can use collections to move data into and out of database tables using high-performance language features known as bulk SQL.
Records
- Records are data structures which has fields and can have different datatypes. You can pass them to subprograms with a single parameter.
You can use the %ROWTYPE attribute to declare a record that represents a row in a table or a row from a query result set, without specifying the names and types for the fields.
TYPE timerec IS RECORD (hours SMALLINT, minutes SMALLINT);
TYPE meetin_typ IS RECORD (
date_held DATE,
duration timerec, -- nested record
location VARCHAR2(20));
Comments