Composite Datatypes   «Prev  Next»

Lesson 3 PL/SQL record
Objective Create a PL/SQL record.

PL/SQL Record

A record is a group of related data items stored in fields, each with its own name and datatype. For example, suppose you have different kinds of data about a customer, such as name, address, city, state, zip code, and phone number. As shown below, a record allows you to relate dissimilar information in a way that appears cohesive and logical:
  1. PL/SQL records are similar in structure to records in a 3GL.
  2. PL/SQL records are not the same as rows within a database table.
  3. PL/SQL records treat a collection of fields as a logical unit of work.
  4. PL/SQL records are convenient for fetching a row of data from a table for processing.
  5. PL/SQL records must contain one or more components of any SCALAR, RECORD, or PL/SQL TABLE datatype, called fields.

Creating a PL/SQL Record

Like PL/SQL tables, records must be declared in two steps. First, define the record type, then declare user-defined records of that type. The record type can be declared within the declarative part of any block, subprogram, or package.
The field declarations are like variable declarations. Each field has a unique name and specific datatype. The NOT NULL constraint can also be added to any field declaration, provided the declaration is initialized.

The following graphic explains the variables in the syntax:
Creating a PL/SQL record 
TYPE type_name IS RECORD
  (field_name1 (field_type | variable%TYPE |
      table.column%TYPE | table%ROWTYPE )
     [NOT NULL],
   field_name2 (field_type | variable%TYPE |
       table.column%TYPE | table%ROWTYPE)
       [NOT NULL] )
1) type_name: A type specifier used within subsequent declarations of records
2) field_type: Any data including RECORD and TABLE

The following graphic shows an example of declaring a PL/SQL record:
Creating a PL/SQL record 
DECLARE
  TYPE ProductRecTyp IS RECORD
      (pname      VARCHAR(30), 
       sale_price NUMBER (10,2),
  product_recProductRecTyp;
BEGIN
  SELECT PRODUCT_NAME, SALE_PRICE
    INTO product_rec FROM PRODUCT
  WHERE PRODUCT_ID=2;
END;

Composite Types used In PL/SQL

In the next lesson, you will learn about %Type attributes.
Click the link below to read about how to build a PL/SQL block with the RECORD datatype by using an evaluative simulation. PL/SQL Composite Types