Database Interaction with PL/SQL, RECORD and TABLE in Sub-programs - Working with PL/SQL TABLE and sub-programs
(Page 5 of 5 )
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).
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 17 
