PLSQL Record

This is a user define datatype, which is used to define different data items into single unit, it is also same as structures of ‘C’ language.

     We are creating PL/SQL Record into two step process

1.     Create datatype

2.     Create variable of that datatype

Syntax:

1.     Type Typename is record (Arrtributename1 datatype(Size), Arrtributename1 datatype(Size),……………….);

2.     Variableaname Typename:

Example:

Package Specification

CREATE OR REPLACE PACKAGE record_type_pkg

AS

   TYPE t1 IS RECORD (

      a1   NUMBER (10),

      a2   VARCHAR2 (10),

      a3   NUMBER (10)

   );                                             -----Record Type declaration

   PROCEDURE record_type_proc;

END;

/

Body of Package

CREATE OR REPLACE PACKAGE BODY record_type_pkg

AS

   PROCEDURE record_type_proc

   AS

      v_type   t1;                           -----Define record type variable

   BEGIN

      SELECT empno,

             ename,

             sal

        INTO v_type

        FROM emp

       WHERE empno = 7788;

      DBMS_OUTPUT.put_line (v_type.a1 || ' ' || v_type.a2 || ' ' || v_type.a3);

   END record_type_proc;

END;

/