Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Magazine Blooper
Daniel Morgan wrote:
>
> Serge Rielau wrote:
>
> > I didn't miss that and I stated CONNECT BY as the Oracle way of doing
> > it, presuming soemone knows how to translate. Unfortunetely I neither
> > have CONNECT BY experience, nor (obviously) an Oracle system to try it.
> >
> > The point I tried to make however is simple enough:
> > If there is a way to pull the INSERT out of the loop then that would be
> > better coding style. The means used (varray, table function, recursion,
> > ..?) are secondary.
> > If it can't be done I withdraw my example :-)
> >
> > BTW, doesn't Oracle support a FOR loop which "blocks" the body and
> > pushed the whole beast deeper into the engine? That as well would be
> > better :-)
> >
> > Cheers
> > Serge
>
> The best way, in Oracle, would be no loop at all. For example:
>
> CREATE OR REPLACE PROCEDURE fast_way IS
>
> TYPE PartNum IS TABLE OF parent.part_num%TYPE INDEX BY BINARY_INTEGER;
> x PartNum;
>
> TYPE PartName IS TABLE OF parent.part_name%TYPE INDEX BY BINARY_INTEGER;
> y PartName;
>
> BEGIN
> SELECT part_num, part_name
> BULK COLLECT INTO x, y
> FROM t1;
>
> FORALL i IN x.FIRST .. x.LAST
> INSERT INTO t2
> (part_num, part_name)
> VALUES
> (x(i), y(i));
> COMMIT;
> END fast_way;
> /
>
> --
> 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)
or
insert into t2
select part_num, part_name
from t1;
-- Connor McDonald Co-author: "Mastering Oracle PL/SQL - Practical Solutions" ISBN: 1590592174 web: http://www.oracledba.co.uk web: http://www.oaktable.net email: connor_mcdonald_at_yahoo.com Coming Soon! "Oracle Insight - Tales of the OakTable" "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" ------------------------------------------------------------Received on Tue May 11 2004 - 08:23:14 CDT
![]() |
![]() |