Returning and using a result set

[welchdor]

I'm trying to create a packaged procedure to receive a person ID and return related premises associated to this person. There could be 0 to many related premises. I need 4 nested "for loops" to get from person to premise. I have 2 questions about the code below:
1) Am I opening the "result set cursor" at the right place?
2) How would I call this from another procedure, and how would I process through the 0 to many possible rows returned?

Any help is appreciated.

CREATE OR REPLACE PACKAGE PERSON_TO_PREMS AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE GET_PREMS
(p_per_id IN VARCHAR2,
O_RESULT_SET OUT CURSOR_TYPE);
END;
/

CREATE OR REPLACE PACKAGE BODY PERSON_TO_PREMS AS
PROCEDURE GET_PREMS
(p_per_id IN VARCHAR2,
O_RESULT_SET OUT CURSOR_TYPE)
IS

BEGIN
OPEN O_RESULT_SET FOR
SELECT acct_id
FROM TABLE_A
WHERE per_id = p_per_id;
LOOP
/* For each acct, get related SA's */
FOR sa_rec IN (SELECT a.sa_id
FROM TABLE_B a, TABLE_C b
WHERE acct_id = acct_rec.acct_id
AND a.sa_type_cd = b.sa_type_cd
AND b.svc_type_cd = 'E ')
LOOP
/* For each SA, get related SP */
FOR sa_sp_rec in (SELECT sp_id
FROM TABLE_D
WHERE sa_id = sa_rec.sa_id
AND stop_dttm IS NULL)
LOOP
/* For each SP_ID, get related premise */
SELECT prem_id
FROM TABLE_E
WHERE sp_id = sa_sp_rec.sp_id
AND sp_status_flg = 'R ';
END LOOP;
END LOOP;
END LOOP;
END GET_PREMS;
END PERSON_TO_PREMS;
/

[RBARAER]

Well, welchdorn with all my respect, that is AWFUL !!!

3 imbricated cursor loops which will process your data row by row by row by row...

ONE SINGLE SQL STATEMENT. Always try to do what you want in one single sql statement. It is the best way to do what you want, and will always be MUCH FASTER and CONSUME MUCH LESS RESSOURCES than procedural code. RDBMSs are optimized for SQL, Oracle excels at that, and there are very few things that cannot be done in a single sql statement with Oracle.

This SQL should do more or less what you want, though I'm not sure that the field "acct_id" belongs to TABLE_B (not stated in your code), and I've not tested it, it might need some little changes :

SELECT e.prem_id
FROM TABLE_A a, TABLE_B b, TABLE_C c, TABLE_D d, TABLE_E e
WHERE b.acct_id = a.acct_id
AND c.sa_type_cd = b.sa_type_cd
AND d.sa_id = b.sa_id
AND e.sp_id = d.sp_id
AND a.per_id = p_per_id
AND c.svc_type_cd = 'E '
AND d.stop_dttm IS NULL
AND e.sp_status_flg = 'R ';

The principle is : instead of looping, simply add the correct join condition(s) between the different tables.

If the query I gave you doesn't work as expected, then it would be easier if you gave us the structure of each table and what you want to get exactly, then we could certainly give you a correct query.

HTH & Regards,

RBARAER