Database Interaction with PL/SQL, RECORD and TABLE in Sub-programs - Using RECORD with sub-programs
(Page 3 of 5 )
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.
Next: Returning a RECORD from FUNCTION >>
More Oracle Articles
More By Jagadish Chatarji
/ 17 
