Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Seeking recursive solution to replace nested for-loops

Re: Seeking recursive solution to replace nested for-loops

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 03 Mar 2004 09:36:56 -0800
Message-ID: <1078335382.500969@yasure>


Michelle Romano wrote:

>>Ah...I see. Would you mind posting your current nested-loop solution
>>as a starting point?
>>
>>Thanks,
>>Dave

>
>
> Not at all...here it is. Note that this is a scaled down version,
> which will basically generate each combination via parm_list. This
> version will just display the pair combinations (using put_text). You
> will notice that I need to comment out the outer loop to prevent
> generating individual parameters. That is one of the issues with the
> nested for-loop solution. Within the loop, I also run a function
> against each combination generated (normally resides where I've added
> the comment "Run function against parm list here"). My goal is to
> create a function to return combination values to replace parm_list
> values currently generated by the for-loop.
>
> CREATE OR REPLACE procedure run_combo is
>
> parm_count pls_integer := 0;
> parm_list varchar2(500);
>
> Type parmArrayType is table of varchar2(100)
> index by pls_integer;
> parm_array parmArrayType;
> cursor ptx_cur is
> select parm
> from parm_table_xref
> order by parm;
>
> ptx_rec ptx_cur%rowtype;
>
> begin
> open ptx_cur;
>
> loop
> fetch ptx_cur into ptx_rec;
> exit when (ptx_cur%notfound);
>
> parm_count := parm_count + 1;
> parm_array(parm_count) := ptx_rec.parm;
>
> end loop;
>
> close ptx_cur;
>
> for i in 1..parm_count loop
>
> -- parm_list := parm_array(i);
> -- put_text(parm_list);
>
> -- Run function against parm list here --
>
> for j in i+1..parm_count loop -- parameter pairs
>
> parm_list:= parm_array(i)||', '||parm_array(j);
> put_text(parm_list);
>
> -- Run function against parm list here --
>
> end loop; -- j loop
>
> end loop; -- i loop
>
> end run_combo;
> /

This appears to be an example of using version 7 PL/SQL when newer constructs would greatly improve performance and scalability. Look at this for comparison.

CREATE OR REPLACE PROCEDURE nrows_at_a_time ( p_array_size IN PLS_INTEGER DEFAULT 100) IS

TYPE ARRAY IS TABLE OF all_objects%ROWTYPE; l_data ARRAY;

CURSOR c IS
SELECT *
FROM all_objects;

BEGIN
   OPEN c;
   LOOP
     FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

     FORALL i IN 1..l_data.COUNT
     INSERT INTO t2 VALUES l_data(i);

     EXIT WHEN c%NOTFOUND;

   END LOOP;
   CLOSE c;
END nrows_at_a_time;
/
-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Wed Mar 03 2004 - 11:36:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US