shakespeare wrote:
> "DA Morgan" <damorgan_at_psoug.org> schreef in bericht
> news:1193950181.169048_at_bubbleator.drizzle.com...
>> Ken Denny wrote:
>>> On Nov 1, 11:26 am, DA Morgan <damor..._at_psoug.org> wrote:
>>>> Now my examples does not include a cursor loop. But if you have
>>>> DMBS_ERRLOG then you are in a version of Oracle more recent than 8i.
>>>> And that means you've no reason for ever writing a cursor loop: Ever!
>>> It can save you a lot of typing. Is it worth an extra 1000 keystrokes
>>> in code to save one or two microseconds in execution time?
>> Lets see ... with cursor loop.
>>
>> CREATE OR REPLACE PROCEDURE slow_way IS
>>
>> BEGIN
>> FOR r IN (SELECT * FROM parent) LOOP
>> -- modify record values
>> r.part_num := r.part_num * 10;
>> -- store results
>> INSERT INTO child
>> VALUES
>> (r.part_num, r.part_name);
>> END LOOP;
>> COMMIT;
>> END slow_way;
>> /
>>
>> -- 7.71 seconds
>>
>> without cursor loop.
>>
>> CREATE OR REPLACE PROCEDURE fast_way IS
>>
>> TYPE myarray IS TABLE OF parent%ROWTYPE;
>> l_data myarray;
>>
>> CURSOR r IS
>> SELECT part_num, part_name
>> FROM parent;
>>
>> BEGIN
>> OPEN r;
>> LOOP
>> FETCH r BULK COLLECT INTO l_data LIMIT 1000;
>>
>> FOR j IN 1 .. l_data.COUNT
>> LOOP
>> l_data(1).part_num := l_data(1).part_num * 10;
>> END LOOP;
>>
>> FORALL i IN 1..l_data.COUNT
>> INSERT INTO child VALUES l_data(i);
>>
>> EXIT WHEN r%NOTFOUND;
>> END LOOP;
>> COMMIT;
>> CLOSE r;
>> END fast_way;
>> /
>>
>> -- 0.50 seconds
>>
>> <SARCASM>
>> Oh my gawd my fingers are killing me. All the heavy lifting and hard
>> work. Please excuse me for a minute. I need to sit down and catch my
>> breath ... make an appointment to have my carpel tunnel syndrome
>> treated ... make out my will.
>>
>> And all I did was speed the code up by a lousy 15.42 times. Hardly
>> worth it eh.
>> </SARCASM>
>>
>> Good thing the web is near anonymous Ken. You might have just talked
>> yourself out of quite a few job opportunities if recruiters look for
>> you on the web. A few microseconds my ....
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org
>
> Daniel,
>
> I think error handling should be at the start of the loop and could be
>
> EXIT WHEN l_data.COUNT = 0;
> which might save another millisecond... <g>
>
> Picked this from the net:
> "Also, within the loop (using LIMIT), you cannot rely on cursor%FOUND to
> determine if the last fetch returned any rows."
>
> But maybe this has changed over the years?
>
> Shakepeare
Seem to work ... but of course one should pay remember that there
are environment variables just for BULK COLLECT such as SQL%BULK_ROWCOUNT.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Nov 05 2007 - 20:45:38 CST
