| combining cursor of recursive iterations [message #20282] |
Fri, 10 May 2002 11:49  |
Graham
Messages: 9
Registered: March 2002
|
Junior Member |
|
|
I have a recursive procedure which successfully
creates a cursor in each recursive iteration.
How would I combine the cursor outputs of each
iteration, tossing the duplicates?
Thx
create or replace package pkgGetUpstreamFacilities
as
type upstreamFacilities is ref cursor;
procedure getFacilityList (
flowDirection in number,
prodMonth in number,
srcFacilitySeq in number,
targetType in number,
facilityList out upstreamFacilities);
end;
/
create or replace package body pkgGetUpstreamFacilities
as
procedure getFacilityList (
flowDirection in number,
prodMonth in number,
srcFacilitySeq in number,
targetType in number,
facilityList out upstreamFacilities)
as
sqlSelect varchar2(2000);
facilitySeq number;
facilityID varchar2(25);
facilityType number;
begin
dbms_output.put_line('paramList: ' || flowDirection || ', ' || prodMonth || ', ' || srcFacilitySeq || ', ' || targetType );
sqlSelect := 'select f.facilitySeq,
f.facilityID,
f.facilityType
from facility f,
segmentFlow s
where f.facilitySeq = s.fromFacilitySeq
and s.direction = :flowDirection
and s.toFacilitySeq = :srcFacilitySeq
and s.effectiveDate <= :prodMonth
and s.expiryDate >= :prodMonth ';
open facilityList
for sqlSelect
using flowDirection,
prodMonth,
srcFacilitySeq ;
loop
fetch facilityList
into facilitySeq,
facilityID,
facilityType ;
exit when facilityList%NOTFOUND ;
dbms_output.put_line('facilityList: ' || facilitySeq || ', ' || facilityID || ', ' || facilityType );
if not (facilityType = targetType) then
getFacilityList(flowDirection, prodMonth, facilitySeq, targetType, facilityList);
--TO DO: Combine distinct records to the root / parent facilityList
end if;
end loop;
--close facilityList;
end; --begin
end; -- proc getFacilityList;
-- end pkgGetUpstreamFacilities;
/
|
|
|
|
| Re: combining cursor of recursive iterations [message #20283 is a reply to message #20282] |
Fri, 10 May 2002 13:40  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Try populating a pl/sql table with all your intermediate results and then querying that in any way you want (e.g distinct).
create or replace type myTableType as table of number;
declare
l_x myTableType :=
myTableType( 8, 5, 3, 7, 9, 1, 2, 6, 4 );
begin
dbms_output.put_line( 'Unsorted' );
for x in ( select a.column_value val
from THE ( select cast( l_x as mytableType )
from dual ) a )
loop
dbms_output.put_line( x.val );
end loop;
dbms_output.put_line( 'Sorted Desc' );
for x in ( select a.column_value val
from THE ( select cast( l_x as mytableType )
from dual ) a
order by a.column_value desc )
loop
dbms_output.put_line( x.val );
end loop;
dbms_output.put_line( '---------' );
for x in ( select sum( a.column_value ) val
from THE ( select cast( l_x as mytableType )
from dual ) a
)
loop
dbms_output.put_line( x.val );
end loop;
end;
Unsorted
8
5
3
7
9
1
2
6
4
Sorted Desc
9
8
7
6
5
4
3
2
1
---------
45
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
