Tag: PL/SQL

Composite data type: Composite data type stores values that have internal components. You can pass entire composite variables to subprograms as parameters, and you can access internal components of composite variables individually. Internal components can be either scalar or composite. You can use scalar components wherever you can use scalar variables or you can use composite components wherever you can use composite variables of the same type. There are two types of composite data type…

• 1. PL/SQL records.
• 2. PL/SQL collection.
  • INDEX by Table
  • Nested Table
  • VARRAY

1. PL/SQL records: Use PL/SQL records when you want to store values of different data types but only one occurrence at a time. PL/SQL record must contain one or more components (called fields) of any scalar, RECORD or Index by table data type.

Creating PL/SQL records:

Syntax:
TYPE type_name is RECORD
    (field_declaration [, field_declaration ]...);
identifier type name;

field_declaration:
field_name {field_type | variable%type
           | table.column%type | table%rowtype}
           [[NOT NULL] {:= | DEFAULT} expr]

Example:

...
TYPE emp_record_type IS RECORD
     (lname VARCHAR2(20),
      job_id VARCHAR2(30)  DEFAULT 'Developer',
      salary NUMBER emp.salary%TYPE);
emp_type emp_record_type;
...

Using %ROWTYPE in composite data type: The %ROWTYPE attribute is useful when the number and data types of the underlying database columns is unknown and  retrieving a row with the SELECT * from statement.

Example:

DECLARE
  emp_rec employees%ROWTYPE;
BEGIN
  SELECT * INTO emp_rec FROM employees
    WHERE employee_id = 124;
  emp_rec.hire_date := SYSDATE;
 UPDATE EMP SET ROW = emp_rec;
END;