| recusion & curor [message #20266] |
Thu, 09 May 2002 12:27  |
Graham
Messages: 9
Registered: March 2002
|
Junior Member |
|
|
Interesting problem, as few problems
are suitable for recursion.
Most apps have and maintain group info,
i.e. departments and employees. This
particular app does not.
I need to find all the facilities of a
certain type that are upstream of a
particular terminal and use the output
cursor as criteria to select all the
field receipts. Selecting the field
receipts is the easy part.
The simple select in the procedure
below will find only the upstream
facilities directly connected to
one facility. I then need to do
two things:
1. recursively call the procedure to
find any facilities upstream of the
first selections with the terminating
condition based on the targetType
- max recursions ~ 5, norm ~ 2
2. Insert the facilities from the
recursive calls only if they have
not been previously selected.
Here is what I have so far, though the
body does not seem complete to me. I
havent tryed to compile it yet.
create or replace package pkgGetUpstreamFacilities
as
type upstreamFacilities is ref cursor;
procedure getFacilityList (
flowDirection in number,
srcFacilitySeq in number,
targetType in number,
facilityList in 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 in out upstreamFacilities);
as
sqlSelect varchar2(2000);
facilitySeq number;
facilityID varchar2;
facilityType number;
begin
set serveroutput on;
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 ;
put_line('facilityList: ' || :facilitySeq || ', ' || :facilityID || ', ' || :facilityType )
if not (facilityType = :targetType) then
/*
1. DO some sort of recursive call here
2. Then add distinct records to the facilityList
*/
end if;
end loop;
end getFacilityList;
/*
procedure getReceiptList (
This is the easy part ...
snip...
*/
/ -- end pkgGetUpstreamFacilities;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
