Working With Composite Data Types: PL/SQL Records Database Management System

Working With Composite Data Types: PL/SQL Records Database Management System

Working With Composite Data Types: PL/SQL Records     

  • Must contain one or more components of any scalar, RECORD, or INDEXBY table data type, called
  • Are similar instructure 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.


PL/SQL Records

WORKING WITH COMPOSITE DATA TYPES: PL/SQL RECORDS Database Management System : A record is a group of related data items stored in fields, each with its own name and data type. For example, Suppose you have different kinds of data about an emp, Such as name, Sal, hire date, and So on. This data is dissimilar in type but logically related. A record that contains such fields as the name, Sal, and hire date of an emp allows you to treat the data as a logical unit. When you declare a record type for these fields, they can be manipulated as a unit.

  • Each record defined can have as many fields as necessary.
  • Records can be assigned initial values and can be defined as NOT NULL.
  • Fields without initial values are initialized to NULL.
  • The DEFAULT keyword can also be used when defining fields.
  • You can define RECORD types and declare user-defined records in the declarative part of any block, Subprogram, or package.
  • You can declare and reference nested records. One record can be the component of another record.


Creating a PL/SQL Record


Where field_declaration is:

TYPE type_name IS RECORD


identifier type_name;

field_name {field_type | variable% TYPE

| table.column%TYPE | table%ROWTYPE}

 [[NOT NULL] {:= | DEFAULT} expr]

Creating a PL/SQL Record

Declare variables to store the name, job, and sal of a new emp.


TYPE emp_record_type IS RECORD

(last_name VARCHAR2(25),

Job_id VARCHAR2(10),

sal NUMBER(8,2));

emp_record emp_record_type;

Creating a PL/SQL Record

Field declarations are like variable declarations. Each field has a unique name and a specific data type.

There are no predefined data types for PL/SQL records, as there are for scalar variables. Therefore, you must Create the record type first and then declare an identifier using that type.

In the example on the slide, a EMP_RECORD_TYPE record type is defined to hold the values for the last_name, job_id, and sal. In the next step, a record EMP_RECORD, of the type

EMP_RECORD_TYPE is declared.

The following example shows that you can use the %TYPE attribute to specify a field data type:


TYPE emp_record_type IS RECORD

(emp_id NUMBER(6) NOT NULL := 100,

Last_name emp.last_name%TYPE,

Job_id emp.job_id%TYPE);

emp_record emp_record type;

Note: You can add the NOT NULL constraint to any field declaration to prevent assigning nulls to that field. Remember, fields declared as NOT NULL must be initialized.

Declaring Records with the %ROWTYPE Attribute

To declare a record based on a Collection of Columns in a database table or view, you use the %ROWTYPE attribute. The fields in the record take their names and data types from the Columns of the table or view. The record can also store an entire row of data fetched from a Cursor or Cursor variable.

In the following example, a record is declared using %ROWTYPE as a data type specifier.


emp_record emp%ROWTYPE,

The emp_record record will have a structure consisting of the following fields, each representing a

Column in the EMP table.

Note: This is not code, but simply the Structure of the composite variable.

(emp_id NUMBER(6),

first_name VARCHAR220),

last_name VAR CHAR2(20),

email VARCHAR2(20),

phone_number VARCHAR2 (20),

hire_date DATE,

sal NUMBER (8,2),

Commission_pct NUMBER(2,2),

manager_id NUMBER(6),

department_id NUMBER(4)

Declaring Records with the %ROWTYPE Attribute (continued)



identifier reference%ROWTYPE;

where: identifier is the name chosen for the record as a whole.

reference is the name of the table, view, cursor, or cursor

variable on which the record is to be based. The table or view must exist for this reference to be valid.

To reference an individual field, you use dot notation and the following syntax :


For example, you reference the Commission pct field in the emp record record as follows :


You can then assign a value to the record field as follows:

emp_record.commission_pct:= .35;

Assigning Values to Records

You can assign a list of common values to a record by using the SELECT or FETCH statement Make sure that the Column names appear in the same order as the fields in your record. You can also assign one record to another if they have the same data type. A user-defined record and a %ROWTYPE record never have the Same data type.


Advantages of Using %ROWTYPE

The advantages of using the %ROWTYPE attribute are listed on the slide. Use the %ROWTYPE attribute when you are not sure about the structure of the underlying database table. Using this attribute also ensures that the data types of the variables declared using this attribute change dynamically, in case the underlying table is altered. This attribute is particularly useful when you want to retrieve an entire row from a table. In the absence of this attribute, you would be forced to declare a variable for each of the columns retrieved by

the SELECT * Statement.

The %ROWTYPE Attribute


 Declare a variable to store the information about a department from the DEPARTMENTS table.

Declare a variable to store the information about an emp from the EMP table.

Dept_record departments%ROWTYPE;

emp_record emp%ROWTYPE;

The %ROWTYPE Attribute

The first declaration on the slide creates a record with the same field names and field data types as a row in the DEPARTMENTS table. The fields are DEPARTMENT ID, DEPARTMENT_NAME, MANAGER ID, and LOCATION ID. The second declaration creates a record with the same field names, field data types, and order as a row in the EMP table. The fields are EMP ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE NUMBER, HIRE DATE, JOB LID, SAL, COMMISSION PCT, MANAGER ID, DEPARTMENT_ID.

The %ROWTYPE Attribute (continued)      

in the following example, an emp is retiring. Information about a retired emp is added to a table that holds information about retired emp. The user supplies the emp’s number. The record of the emp specified by the user is retrieved from the EMP and stored into the emp_rec  variable, which is declared using the %ROWTYPE attribute.

DEFINE emp_number = 124


Emp_rec emp%ROWTYPE,


SELECT * INTO emp_rec

FROM emp

WHERE emp_id=&emp number;

INSERT INTO retired_emp(empno, ename, job, mgr, hiredate,

leavedate, sal, Comm, deptno)

VALUES (emp_rec.emp_id, emp_rec.last name, emp_rec.job_id,

emp_rec.manager_id, emp_rec. hire_date, SYSDATE, emp_rec. sal,

emp rec.commission_pct, emp_rec. department_id);




The record that is inserted into the RETIRED_EMP table is shown below: