Repost: Nested loop in PL/SQL

From: Feng, Jun <jfeng_at_verisign.com>
Date: Tue, 02 Oct 2001 07:29:52 -0700
Message-ID: <F001.0039EADD.20011002070521@fatcity.com>

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