Re: And again: 1NF may not be dead

Kenneth Downs wrote:
> Tony Andrews wrote:
>
> > Oracle has this syntax in its procedural extension to SQL (PL/SQL):
> >
> > FORALL i IN 1..ArrayExample.COUNT
> > INSERT INTO CHILDREN (parent_id,item_id)
> > SELECT 'X',ArrayExample(i);
> > I don't know whether standard SQL has an equivalent.
>
> no good, you have a procedural loop. The OP is a wishlist item, so
it gets
> to stay pure. The pure expression is a single command that inserts
the
> entire array in one smack.
>
> It does what you mean, perl-style.

Don't be fooled by the loop-ish keyword name, this is NOT the same as:

FOR i IN 1..ArrayExample.COUNT LOOP
INSERT INTO CHILDREN (parent_id,item_id) SELECT 'X',ArrayExample(i);
END LOOP; The former is a bulk operation, the latter is N single operations.

More details are available here:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#28181 Received on Sat Nov 06 2004 - 12:04:07 CET