Re: Help with simple PL/SQL

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.

Nice. That works fine.

I figured there was an easier way, but everywhere I searched for 'dynamic SQL' I got back all these examples with CURSORs and EXECUTE IMMEDIATE.

I'm also going to need to write code with 2 or maybe 3 nested loops, and your example should help.

Thanks William Received on Thu Oct 13 2005 - 14:47:03 CDT