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
