PL/SQL record data type

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.

PL/SQL cursors
PL/SQL cursors - What are Cursors?, Types of cursors in PL/SQL - Implicit cursors, Explicit cursors, What are cursor variables?, Significance of SELECT FOR UPDATE clause, Significance of WHERE CURRENT OF clause...
PL/SQL error handling
PL/SQL error handling - Overview of PL/SQL Error Handling, Advantages of PL/SQL Exceptions, commonly used Predefined PL/SQL Exceptions, What is user-defined exception?, how PL/SQL exceptions are raised, reraising a PL/SQL Exception, how to handle raised PL/SQL exceptions...
PL/SQL subprograms
PL/SQL subprograms - What are PL/SQL Subprograms?, What are the parts of PL/SQL Subprograms?, Advantages of PL/SQL Subprograms, PL/SQL has two types of subprograms called procedures and functions, How does subprograms pass information? What are actual and formal subprogram parameters...
Post your comment

You may like