Database Interaction with PL/SQL, part 2 - Accessing more than one row in PL/SQL (TYPE with TABLE)
(Page 4 of 4 )
In all of the previous sample programs, we discussed retrieving and manipulating only a single row. How about retrieving more than one (or a set of rows based on a SELECT statement)? Before going into too many explanations, let us go through the following beautiful example:
declare
type t_emptbl is table of emp%rowtype;
v_emptbl t_emptbl;
begin
select * bulk collect into v_emptbl from emp;
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 program displays all of the employee names with their respective salaries. Here I introduced a new declaration, TYPE..IS TABLE. 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 that it can store any number of rows (just like a table) from the table "emp" (as we included "emp%rowtype") in memory (without having any relation to the physical database). Make sure that it is only a data type. If you know the C language, you can think of it as 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."
select * bulk collect into v_emptbl from emp;
This is the most important statement, which does all of 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 in a TABLE typed variable. From the above statement, it is quite clear that we are retrieving all of the 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 of 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.
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;
The above part is a FOR loop that 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 by using the notation TABLE(INDEX).FIELDNAME (which is done in 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. |
