Returning and using a result set
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Returning and using a result set

  1. #1
    Join Date
    Jan 2001
    Posts
    63

    Returning and using a result set

    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;
    /

  2. #2
    Join Date
    May 2005
    Location
    France
    Posts
    34
    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 :
    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width