You use cursors like so:
create or replace procedure my_procedure( ... )
as
CURSOR c1 IS SELECT name FROM thistable WHERE group = 'high';
my_rec c1%rowtype;
begin
open c1;
loop
fetch c1 into my_rec;
exit when c1%NOTFOUND;
...
end loop;
end;
Cheers,
Dave
Paul wrote:
> thanks Dave, so how do you use cursors? can you give me a quick example,
> thanks in advance
>
> Dave Hau wrote:
>
>> Paul wrote:
>>
>>> hi all
>>>
>>> if i have a table with
>>>
>>> name char(30)
>>> group char(30)
>>>
>>> the name refers to 'schema' names, can i circulate through this table
>>> and union the results of other tables across more than one schema
>>>
>>> for example say we have the following data in this table
>>>
>>> name group
>>> fred high
>>> thomas low
>>> john high
>>> mark medium
>>>
>>> so i first to get the schema's i am interested in "select name from
>>> thistable where group = 'high'" - no i want to do this select
>>> firstschema.result+secondschema.result from commontable;
>>>
>>> now in this example firstschema would be fred, and secondschema would
>>> be john, bearing in mind that we dont always have a static number of
>>> names returned from the first select, how do a union the results
>>> together?
>>
>>
>>
>> You can do this by writing a PL/SQL procedure. Do the first select
>> ("select name from thistable where group = 'high'") and put the result
>> into a cursor. Then iterate through this cursor while building the
>> dynamic SQL statement for the second statement ("select
>> firstschema.result+secondschema.result from commontable"). Then
>> execute the dynamic SQL and return the result.
>>
>> Cheers,
>> Dave
>>
>>
>>
>>
>>
>>>
>>> thanks in advance for your assistance
>>> paul
>>
>>
>>
>
>
Received on Wed Jul 02 2003 - 17:36:23 CDT
