Connor McDonald wrote:
> 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;
I thought about that ... but I wanted to share a bit of Oracle
syntax with Serge ... that he likely would not have seen before.
--
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 Tue May 11 2004 - 09:04:45 CDT
