Re: get multiple rows to columns - howto replace temp-tables with virtual ones

I would like to change my procedures from using temp tables to virtual ones.

Right now the procedure is working like this:

1. A select statement with 6-10 joins reads the data in a temp table
2. 1 to many cursors cycle through this temp table adding information mostly from 1->n relations
3. The whole temp table is passed to crystal report and deleted.

Here's my test example.

CREATE OR REPLACE PROCEDURE coll_emp4 IS

begin

delete from tora_coll_emp;
insert into tora_coll_emp (job_id,members) select job_id,'N/A' from jobs;

declare cursor c1 is
select e.last_name,t.job_id from employees e,tora_coll_emp t where t.job_id=e.job_id
for update of members ;

begin
for m_rec in c1 loop

	update tora_coll_emp
	set members=decode(members,'N/A',m_rec.last_name,members||',
'||m_rec.last_name)
	where current of c1;

end loop;
end;

commit;

end coll_emp4;

Now would i use a record or a index by table or temporary tables for this purpose? What is fastest to do everything in RAM and not write to disk?

How do I fill such a table from a select statement?

How do I add or reserve and pre fill the necessary columns for the updates required afterwards?

Any help would be greatly appreciated.

thanks

ken Received on Fri May 31 2002 - 13:40:51 CEST