PL/SQL Tables

[TheEntertainer]

Dear All,

I apologise if this is covered elsewhere I've had a look and can't find the answer to this question.

Can PL/SQL tables be generated containing data from multiple tables?

I've got this simple script below:

-- Declare the PL/SQL table
type testarr is table of title%rowtype
index by binary_integer;
t_arr testarr;

-- Declare cursor
type t_cur is ref cursor return title%rowtype;
c1 t_cur;

i number := 1;
begin
-- Populate the PL/SQL table from the cursor
open c1 for select * from title;
loop
exit when c1%NOTFOUND;
fetch c1 into t_arr(i);
i := i+1;
end loop;
close c1;

-- Display the entire PL/SQL table on screen
for i in 1..t_arr.last loop
dbms_output.put_line('ID : '||t_arr(i).ID );
dbms_output.put_line('CODE : '||t_arr(i).CODE );
dbms_output.put_line('DESCRIPTION : '||t_arr(i).DESCRIPTION );
dbms_output.put_line('---------------------------');
end loop;
end;

Its just selecting from 1 table called title both the cursor and the table are bounded to this table:

type testarr is table of title%rowtype
type t_cur is ref cursor return title%rowtype;

as I'm only getting data from this table:

open c1 for select * from title;

How do I change the :

type testarr is table of title%rowtype
type t_cur is ref cursor return title%rowtype;

to take account of multiple tables if I'm taking data from more than one table :

open c1 for select title.description, person.title_id from title, person where title.id=person.title_id;

Thanks

 

[taupirho]

Hi, you would use a template cursor instead

Something like:-

cursor my_template is select t1.col1,t2.col2
from table1 t1, table2 t2
where t1.colA = t2.colB;

my_rec my_template%rowtype;


Hope this helps