RE: Repost: Nested loop in PL/SQL

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Tue, 02 Oct 2001 10:12:25 -0700
Message-ID: <F001.0039F108.20011002102455@fatcity.com>

Jun,

What exactly do you mean that it doesn't work after the first inner loop?

One thing you should do is close and open the inner cursor after the inner loop completes.

Are you sure the outer loop returns more than one row?

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Tuesday, October 02, 2001 11:05 AM To: Multiple recipients of list ORACLE-L

Sorry, I meant to say doesn't work.

-----Original Message-----
Sent: Monday, October 01, 2001 6:50 PM
To: Multiple recipients of list ORACLE-L

Could anyone tell me why following nested loop does work? It stopped after inner loop finished. I tried for loop, it worked ok.

Thanks,

Jun

declare v_plan_id number;
        v_channel_id pricing_plan.channel_id%TYPE;
        v_row pricing_plan%ROWTYPE;

cursor plan_id is
select distinct pricing_Plan_id from pricing_plan

where CHANNEL_ID = 'GROUP19'
and promotion_cd = 'INFO'
and trunc(end_date) >= trunc(sysdate)

and p_mode = 'LIVE'
group by PRICING_PLAN_ID;

cursor channel_id is
select distinct channel_id from pricing_plan where CHANNEL_ID not in ('GROUP1','GROUP2', 'GROUP3') and transaction_type = 'REGISTRATION'
order by channel_id;

begin
open plan_id;
open channel_id;
loop
fetch plan_id into v_plan_id;
exit when plan_id%NOTFOUND;

loop
fetch channel_id into v_channel_id;
exit when channel_id%NOTFOUND;

        select * into v_row
        from pricing_plan 
        where PRICING_PLAN_ID = 1;

dbms_output.put_line(v_row.price_cd);

end loop;
end loop;
close plan_id;
close channel_id;
end;
/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Feng, Jun
INET: jfeng_at_verisign.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Feng, Jun
INET: jfeng_at_verisign.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------