Database Interaction with PL/SQL, RECORD and TABLE in Sub-programs

This is part 14 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we concentrated on some tips for using procedures together with the introduction to functions. In this article we will look through several examples that cover using sub-programs in a very effective manner. I suggest you to go through part two in order to follow this article properly.

Please note that all the examples in this series have been tested only with Oracle 10g. I didn’t really test them with all the previous versions of Oracle. I suggest you to refer the documentation of respective version you are using, if any of the programs failed to execute.

More about FUNCTION

My previous article actually stopped at a simple example on FUNCTION. Now we shall extend its limit. Consider the following example:

declare

CURSOR c_emp is

select ename,deptno from emp;

function getDname(p_deptno dept.deptno%type) return varchar2 is

v_dname dept.dname%type;

begin

select dname into v_dname

from dept where deptno = p_deptno;

 

return v_dname;

end;

begin

for r_emp in c_emp

loop

dbms_output.put_line(r_emp.ename || ‘,’ || getDname(r_emp.deptno));

end loop;

end;

The above program really makes use of FUNCTION properly. We are sending ‘deptno’ as a parameter to the function ‘getDname’ which returns the department name back to the main program. Actually we can store the value returned back by ‘getDname’ (demonstrated in part 13), but it would be easy to write directly within the DBMS_OUTPUT statement in the main program as above.

{mospagebreak title=Mixing PROCEDURE and FUNCTION}

Can we mix PROCEDURE and FUNCTION together in a single program? Why not? After all, any of those two belongs to “sub-program.” And a PL/SQL program can include any number of sub-programs (regardless of whether the sub-program involves PROCEDURE or FUNCTION). Let us look at a practical example of this issue:

declare

function getDname(p_deptno dept.deptno%type) return varchar2 is

v_dname dept.dname%type;

begin

select dname into v_dname

from dept where deptno = p_deptno;

 

return v_dname;

end;

procedure dispEmp is

CURSOR c_emp is

select ename,deptno from emp;

begin

for r_emp in c_emp

loop

dbms_output.put_line(r_emp.ename || ‘,’ || getDname(r_emp.deptno));

end loop;

end;

BEGIN

dispEmp;

END;

I just modified the program given in the previous section to follow this topic. From the above program, first of all there exist no variable declarations in the main program. The declaration of CURSOR as well as the FOR loop to the CURSOR have been encapsulated into a separate procedure named ‘dispEmp’. So, there exists literally no processing to deal with at the main program, apart from calling the procedure ‘dispEmp’.

{mospagebreak title=Using RECORD with sub-programs}

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.

{mospagebreak title=Returning a RECORD from FUNCTION}

In the previous section, we have seen how to deal with RECORD using procedures. Now we shall see the same, using FUNCTION. Instead of passing a RECORD to the FUNCTION, we shall return a RECORD from the FUNCTION back to the calling program. Consider the following program:

declare

TYPE t_deptrec IS RECORD

(

name dept.dname%type,

location dept.loc%type

);

CURSOR c_emp is

select ename,deptno from emp;

r_dept t_deptrec;

function getDept(p_deptno dept.deptno%type) return t_deptrec is

r_dept t_deptrec;

begin

select dname,loc into r_dept

from dept where deptno = p_deptno;

return r_dept;

end;

BEGIN

for r_emp in c_emp

loop

r_dept := getDept(r_emp.deptno);

dbms_output.put_line(r_emp.ename || ‘,’ || r_dept.name || ‘,’ || r_dept.location);

end loop;

END;

Let me explain part by part. Consider the following:

TYPE t_deptrec IS RECORD

(

name dept.dname%type,

location dept.loc%type

);

The above defines a new data type named ‘t_deptrec’ (just like %ROWTYPE with limited specified fields) which can hold only two fields, namely ‘name’ and ‘location’.

CURSOR c_emp is

select ename,deptno from emp;

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

r_dept t_deptrec;

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

function getDept(p_deptno dept.deptno%type) return t_deptrec is

r_dept t_deptrec;

begin

select dname,loc into r_dept

from dept where deptno = p_deptno;

return r_dept;

end;

The above FUNCTION receives only a department number as a parameter and retrieves the department name along with the location. The values retrieved get gathered into the ‘r_dept’ variable of type ‘t_deptrec’ (which is local to ‘getDept’ only). And finally we return ‘r_dept’ (which is a RECORD type of variable) back to the main program.

for r_emp in c_emp

loop

r_dept := getDept(r_emp.deptno);

dbms_output.put_line(r_emp.ename || ‘,’ || r_dept.name || ‘,’ || r_dept.location);

end loop;

The main program uses the above loop to display all employee details along with their department names and locations. In the above loop, you should note that we are assigning the value returned by ‘getDept’ into a variable named ‘r_dept’, which is declared in the main program. This has no relation to ‘r_dept’ in ‘getDept’.

{mospagebreak title=Working with PL/SQL TABLE and sub-programs}

It is not simply a database table. As you may recall from part two, it is a PL/SQL TABLE. Now I would like to work with a PL/SQL TABLE together with sub-programs. Let us consider the following program:

declare

type t_emptbl is table of emp%rowtype;

v_emptbl t_emptbl;

procedure dispEmp is

 

begin

for i in v_emptbl.first .. v_emptbl.last

loop

dbms_output.put_line(v_emptbl(i).ename || ‘ earns ‘ || v_emptbl(i).sal);

end loop;

end;

BEGIN

select * bulk collect into v_emptbl from emp;

dispEmp;

END;

Let me explain the above program part by part.

type t_emptbl is table of emp%rowtype;

The above statement defines a new data type ‘t_emptbl’ as an in-memory table representation. This means it can store any number of rows, just like a table, from the table ‘emp’ in memory, without having any relation to a physical database. As you will note, we included ‘emp%rowtype’. Make sure that it is only a data type. If you know the C language, you can consider it a structured array.

v_emptbl t_emptbl;

The above statement declares a variable ‘v_emptbl’ which is authorized to store information based on the table type defined by ‘t_emptbl’. The above declarations can be used globally in the entire program, including sub-programs.

select * bulk collect into v_emptbl from emp;

This is the most important statement; it performs all the operations. If you remove BULK COLLECT, it would raise an error, because you cannot work with more than one row at a time. The BULK COLLECT lets you fetch any number of rows based on the SELECT statement issued, and store all of them into a TABLE typed variable. From the above statement, it is quite clear that we are retrieving all rows from the table ‘emp’ and collecting them into the TABLE typed variable ‘v_emptbl’.

Now the next issue is to fetch all those rows of values and display them back on the screen. In general, each and every row in the TABLE typed variable is indexed starting from one (just like an array). We retrieve each and every row with that index using a FOR loop.

procedure dispEmp is

 

begin

for i in v_emptbl.first .. v_emptbl.last

loop

dbms_output.put_line(v_emptbl(i).ename || ‘ earns ‘ || v_emptbl(i).sal);

end loop;

end;

The above part is a procedure named ‘dispEmp’ containing a FOR loop which starts from the first index of the TABLE typed variable ‘v_emptbl’ and ends at the last index of the same. Here ‘first’ and ‘last’ are keywords which can be used with any TABLE typed variables. We retrieve each and every field of information using the notation TABLE(INDEX).FIELDNAME (which is implemented using the DBMS_OUTPUT statement).

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye