PL/SQL Record Type Variable Example

%TYPE and %ROWTYPE work with single value and one complete record respectively

How do we create our own composite data type, with our own specified number of values to hold?

Let us consider a table of about 20 columns

We need to work with only seven of those columns

If we use %ROWTYPE, we get all 20 values unnecessarily

If we use seven %TYPE declarations it will be bit clumsy

A better way to solve this problem is by defining our own data type, which can hold seven values

PL/SQL Record Type Variable Example

DECLARE

   TYPE myrec_type IS RECORD (

      eno    emp.empno%TYPE,

      NAME   emp.ename%TYPE,

      esal   emp.sal%TYPE

   );

   emp_record   myrec_type;

BEGIN

   SELECT empno, ename, sal

     INTO emp_record.eno, emp_record.NAME, emp_record.esal

     FROM emp

    WHERE empno = 7839;

   DBMS_OUTPUT.put_line ('Empno :' || emp_record.eno);

   DBMS_OUTPUT.put_line ('Ename :' || emp_record.NAME);

   DBMS_OUTPUT.put_line ('Salary :' || emp_record.esal);

END;