TYPE with RECORD declaration - Oracle

Until now, we have been working either with %TYPE or %ROWTYPE. This means we are working with either one value or one complete record. How do we create our own data type, with our own specified number of values to hold? This is where TYPE and RECORD come in. Those who are familiar with the C language can consider this to be a structure declaration. Let me explain this to you in detail.

Let us consider a table of about 20 columns.  I always need to work with only seven of those columns. If I use %ROWTYPE, I get all 20 values unnecessarily. At the same time, my program will be bit clumsy if I use seven %TYPE declarations. A better way to solve this solution is by defining my own data type, which can hold seven values. Then I declare the variables of my new data type and work with them. The following example illustrates this.

declare

TYPE t_emprec IS RECORD

(

name emp.ename%type,

salary emp.sal%type,

job emp.job%type

);

v_emp t_emprec;

v_empno emp.empno%type := &empno;

begin

select ename,sal,job into v_emp

from emp where empno = v_empno;

dbms_output.put_line ('Name : ' || v_emp.name);

dbms_output.put_line ('Salary : ' || v_emp.salary);

dbms_output.put_line ('Job : ' || v_emp.job);

end;

Let us examine the above program part by part. First, consider the following:

TYPE t_emprec IS RECORD

(

name emp.ename%type,

salary emp.sal%type,

job emp.job%type

);

The above defines a new data type named "t_emprec" (just like %ROWTYPE with limited specified fields) which can hold three fields, namely "name," "salary" and "job."

v_emp t_emprec;

The above statement declares a variable "v_emp" based on the datatype "t_emprec." This means that "v_emp" internally contains the fields "name," "salary" and "job."

select ename,sal,job into v_emp

from emp where empno = v_empno;

The above statement places the values of "ename," "sal" and "job" into fields "name," "salary" and "job" of the variable "v_emp" respectively.

dbms_output.put_line ('Name : ' || v_emp.name);

dbms_output.put_line ('Salary : ' || v_emp.salary);

dbms_output.put_line ('Job : ' || v_emp.job);

And the above displays all the values available fields of the variable "v_emp."