Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Wednesday, 6 April 2016

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

   TYPE myrec_type IS RECORD (
      eno    emp.empno%TYPE,
      NAME   emp.ename%TYPE,
      esal   emp.sal%TYPE

   emp_record   myrec_type;
   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);
Share this article :


Post a Comment