Using RECORD with sub-programs - Oracle

In part two, I introduced the concept of RECORD. Now I would like to use RECORD together with sub-programs. Let us start with an example:

declare

TYPE t_emprec IS RECORD

(

name emp.ename%type,

salary emp.sal%type,

job emp.job%type

);

CURSOR c_emp is

select ename,sal,job from emp;

r_emp t_emprec;

procedure dispEmp(v_emprec t_emprec) is

begin

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

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

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

dbms_output.put_line ('-----------------------');

end;

begin

for r_emp in c_emp

loop

dispEmp(r_emp);

end loop;

end;

Let me explain this program part by part. The first part could be:

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’.

CURSOR c_emp is

select ename,sal,job from emp;

The above statement defined a cursor ‘c_emp’ based on SELECT statement provided.

r_emp t_emprec;

The above statement declares a variable ‘r_emp’ based on the datatype ‘t_emprec’. This means ‘r_emp’ internally contains the fields ‘name’, `salary’ and ‘job’. All the above declarations are available at the main program level. So, according to logic, we can use them throughout our program (including sub-programs).

procedure dispEmp(v_emprec t_emprec) is

begin

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

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

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

dbms_output.put_line ('-----------------------');

end;

The above is a procedure named ‘dispEmp’ which accepts a RECORD of type ‘t_emprec’ as a parameter and displays all the values available within the variable ‘v_emprec’ (of type ‘t_emprec’).

for r_emp in c_emp

loop

dispEmp(r_emp);

end loop;

By using the above loop, we iterate through all employees. For each iteration, we are sending the information to procedure ‘dispEmp’ (as a parameter value) to get them displayed on the screen.