Re: Help with simple PL/SQL

From: DFS <nospam_at_dfs_.com>
Date: Thu, 13 Oct 2005 18:31:14 -0400
Message-ID: <SeB3f.5842$1X5.2898@fe05.lga>

DA Morgan wrote:

> William Robertson wrote:

>>
>> You can simplify it even firther with something like (untested):
>>
>> CREATE OR REPLACE PROCEDURE setlistorder
>> ( p_projectid INTEGER )
>> IS
>> i INTEGER := 1;
>> BEGIN
>> FOR r IN
>> (
>> SELECT projcpid, itemorder
>> FROM table_project
>> WHERE projid = projectid
>> ORDER BY itemorder
>> )
>> LOOP
>> UPDATE table_project
>> SET itemorder = i
>> WHERE projcpid = r.projcpid;
>>
>> i := i + 1;
>> END LOOP;
>> END;
>>
>> However I might have missed what all the variables were for. It also
>> seems like something you should be able to do with a straight UPDATE
>> statement.

>
> Why would you use a cursor loop to do what could be done 100X faster
> with array processing?

100x sounds exaggerated, don't you think DA? Notice each iteration contains an update statement.

In case you're interested, that loop will operate on no more than 300 records. It currently executes in half a second or so.

But I'm curious what the array processing code would look like. Received on Thu Oct 13 2005 - 17:31:14 CDT