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
(emp_id emp.empno%TYPE,
emp_name VARCHAR2(14),
emp_loc VARCHAR2(13));
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:
CREATE TABLE salary
(employee_code NUMBER (5),
Employee_sal NUMBER);
Lets say, record for the above table were named salary_rec, then the fields would each be referred to as:
salary_rec.employee_code
salary_rec.employee_sal
Cursor based:- 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
(<field_name1> <datatype1>)
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:
emp_info.joining_date ...
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.
