What is a PL/SQL Record data type?
A record data type represents a data type for that row in a database table. It lets u define your own records and not your own fields.
Define and declare Records
A record has its own name and type that stores group of related data items.
%ROWTYPE helps you declare a rowtype.Example
TYPE employeeRec IS RECORD
Different Types of Records - Table-based, Cursor-based, Programmer-defined
-Table based records
:- Such records are based on tables. This means that the structure of the records is based on structure of the tables. The record field corresponds to a column of the table.Example
Table structure:Cursor based
CREATE TABLE salary
(employee_code NUMBER (5),
Lets say, record for the above table were named salary_rec, then the fields would each be referred to as:
:- Such records are based on select list of a cursor. Each field in record is connected to some column in the cursor query.Example
<record_name> <cursor_name>%ROWTYPE;Programmer defined
:- These are defined by programmer and have got nothing to do with cursors or tables. In order to define it, first, a record TYPE containing the structure needs to be created and then this record type can be used in actual records having that structure.Example
TYPE <type_name> IS RECORD
Benefits of using Records
They help you treat data as logical units. This makes it easier to organize and represent information.
Guidelines for using Records
a) Nested record types are not supported
b) ROW cannot be used with a subquery.
c) Record variables are not allowed in select, where or group by clause
d) The keyword ROW is allowed only on the left side of a SET clause
Rules you must follow for referencing a record in its entirety or a particular field in the record.
Fields in a record are accessed by name.Example
You must always use the fully qualified name of a field when referencing that field. There is no need to use dot notation when you reference the record as a whole; you simply provide the name of the record.