PL/SQL: join tables together tips

Question:  How do I do a nested loops join in PL/SQL if I don't want to do the join in Oracle SQL?  I want to simulate a nested loops join in PL/SQL and use PL/SQL to join the tables together.

Answer:  Oracle allows you to join table together in PL/SQL and in some cases a PL/SQL table join can perform faster than a SQL table join.  Here is pseudocode that mimics what a nested loop join is doing:

for x in ( select * from T1 )
loop
     for y in ( select * from t2
                where t2.joins = t1.joins )
     loop
          output records
     end loop
end loop

Here is how a nested loop join would use an index in PL/SQL:

Here is a working PL/SQL stored procedure that joins a sales table to a title table, removing the need to do a table join in SQL:

create or replace procedure join_tables
as
v_title_id titles.title_id%TYPE;
v_title titles.title%TYPE;
v_price titles.price%TYPE;

v_title_id2 sales.title_id%TYPE;
v_sum_qty sales.qty%TYPE;

cursor c1 is
select
   title_id,
   title,
   price
from
titles;

cursor c2 is
select
   title_id,
   sum(qty) sum_qty
   from
sales
   where
   title_id = v_title_id
group by
   title_id;

begin
open c1;
fetch c1 into v_title_id, v_title, v_price;
while c1%FOUND
loop
   open c2;
   fetch c2 into v_title_id2, v_sum_qty;
      dbms_output.put_line(v_title_id||' '||v_price||' '||v_price*v_sum_qty);
   close c2;
fetch c1 into v_title_id, v_title, v_price;
end loop;

close c1;
end;
/

show errors

exec join_tables;

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.