Oracle PL/SQL - PL SQL Data Type RECORD Types

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. RECORD Types

Introduction

A RECORD type defined in a PL/SQL block is a local type.

A RECORD type defined in a package is a public item.

You cannot create a RECORD type at schema level.

To define a RECORD type, specify its name and define its fields.

The following code defines a RECORD type named DeptRecType.

Demo

SQL>
SQL> DECLARE-- ww  w . ja  v  a  2 s.  c o m
  2    TYPE DeptRecType IS RECORD (
  3      dept_id    NUMBER(4) NOT NULL := 10,
  4      dept_name  VARCHAR2(30) NOT NULL := 'Administration',
  5      mgr_id     NUMBER(6) := 200,
  6      loc_id     NUMBER(4)
  7    );
  8
  9    dept_rec DeptRecType;
 10    dept_rec_2 dept_rec%TYPE;
 11  BEGIN
 12    DBMS_OUTPUT.PUT_LINE('dept_rec:');
 13    DBMS_OUTPUT.PUT_LINE('dept_id:   ' || dept_rec.dept_id);
 14    DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec.dept_name);
 15    DBMS_OUTPUT.PUT_LINE('mgr_id:    ' || dept_rec.mgr_id);
 16    DBMS_OUTPUT.PUT_LINE('loc_id:    ' || dept_rec.loc_id);
 17
 18    DBMS_OUTPUT.PUT_LINE('dept_rec_2:');
 19    DBMS_OUTPUT.PUT_LINE('dept_id:   ' || dept_rec_2.dept_id);
 20    DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec_2.dept_name);
 21    DBMS_OUTPUT.PUT_LINE('mgr_id:    ' || dept_rec_2.mgr_id);
 22    DBMS_OUTPUT.PUT_LINE('loc_id:    ' || dept_rec_2.loc_id);
 23  END;
 24  /
dept_rec:
dept_id:   10
dept_name: Administration
mgr_id:    200
loc_id:
dept_rec_2:
dept_id:   10
dept_name: Administration
mgr_id:    200
loc_id:

PL/SQL procedure successfully completed.

SQL>

Related Topics