Database Interaction with PL/SQL: Nested Tables - A Powerful FOR loop in PL/SQL
(Page 4 of 4 )
Till now to work with more than one row in PL/SQL, we are using a BULK COLLECT together with TABLE type of variable. But there is another powerful way of doing the same without using any of the above two. Let us consider a very simple example as following:
begin
for i in (select ename,sal from emp)
loop
dbms_output.put_line(i.ename || ',' || i.sal);
end loop;
end;
A wonder? Yes! You can directly use a FOR loop with a SELECT inside it without using any complicated structures. It is also called as a FOR loop with inline SELECT statement. This is really a very powerful technique of doing things very fast, without even really thinking about any declarations or any such nonsense. You can also simplify the above complicated program a bit as following:
declare
v_experiences t_experience_tbl;
begin
for j in (select name from employees)
loop
select experiences into v_experiences
from employees where name=j.name;
dbms_output.put_line('-----------------------');
dbms_output.put_line('Experience list of ' || j.name);
dbms_output.put_line('-----------------------');
for i in v_experiences.first .. v_experiences.last
loop
dbms_output.put(v_experiences(i).Position);
dbms_output.put_line (',' || v_experiences(i).NoOfYears);
end loop;
end loop;
end;
The above program removed several statements of declarations and even a SELECT also. Not only that, this also looks very simplified in terms of readability.
We shall discuss in my up-coming articles about the pros and cons of each approach, but not yet. The reason we must wait is that I need to cover another powerful topic called as CURSOR (which also does the same as above and bit more too). After completing the explanations of all approaches, then it would be best to discuss the pros and cons of each and every approach in comparison with other approaches. I hope you do agree with me.
| 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. |
