%ROWTYPE attribute - Oracle

What if we want more and more columns of values to be retrieved and pushed into variables? Consider a table of fifteen columns. Is it necessary to declare all fifteen variables to retrieve those values? This is where %ROWTYPE comes in. It can be used to declare a variable to store an entire row from a specified table. Let us go through a simple example again.

Declare
     v_empRec emp%rowtype;
     v_empno emp.empno%type := &empno;
begin
     select * into v_emprec
     from emp
     where empno=v_empno;
     dbms_output.put_line('Name: '||v_emprec.ename);
     dbms_output.put_line('Salary: '||v_emprec.sal);
     dbms_output.put_line('Job: '||v_emprec.job);
end;

In the above example, we declared a variable "v_empRec," which can hold an entire row from the table "emp" (which is what "emp%rowtype" means). Within the body, we fetch an entire row (all of the columns) of information (using "*" in the "select" statement) and place it in "v_emprec." To fetch the value of each and every column, we specify the column name with the "%rowtype" variable separated with a dot (watch the dbms_output statements).

Note that it is not compulsory to use all of the columns in our program. This concept of "%rowtype" is not suitable for retrieving information based on joins, but indeed it helps a lot by excluding unnecessary variable declarations. Let us conclude this section with one more example.

Declare
     v_empRec emp%rowtype;
     v_deptRec dept%rowtype;
     v_empno emp.empno%type := &empno;
begin
     select * into v_emprec
     from emp where empno=v_empno;
     Select * into v_deptRec
     from dept where deptno=v_empRec.deptno;
     dbms_output.put_line('Name: '||v_emprec.ename);
     dbms_output.put_line('Salary: '||v_emprec.sal);
     dbms_output.put_line('Job: '||v_emprec.job);
     dbms_output.put_line('Department: '||v_deptrec.dname);
     dbms_output.put_line('Location: '||v_deptrec.loc);
end;

Since this is not very complicated, I leave it to the readers to understand the above program.