Re: Repost: Nested loop in PL/SQL

From: <DBarbour_at_austin.isd.tenet.edu>
Date: Tue, 02 Oct 2001 11:17:29 -0700
Message-ID: <F001.0039F240.20011002102903@fatcity.com>

The reasons this doesn't work are fairly numerous. What is it you're trying to accomplish here? Perhaps if you could explain your requirement, somebody could help you out. I'm quite surprised you were able to get a version of this to compile and run even if you did use a 'FOR' loop. Is it possible we're not seeing all the code?

David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002

                                                                                       
                       
                    "Feng, Jun"                                                        
                       
                    <jfeng_at_verisig       To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>  
                    n.com>               cc:                                           
                       
                    Sent by:             Subject:     Repost: Nested loop in PL/SQL    
                       
                    root_at_fatcity.c                                                     
                       
                    om                                                                 
                       
                                                                                       
                       
                                                                                       
                       
                    10/02/2001                                                         
                       
                    10:05 AM                                                           
                       
                    Please respond                                                     
                       
                    to 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


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

--

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

Author:
INET: DBarbour_at_austin.isd.tenet.edu

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