Re: Newbie question about PL/SQL

From: Matt B. <mcb_at_fightspam.sd.znet.com>
Date: Thu, 24 Aug 2000 18:56:27 -0700
Message-ID: <sqbkahd9t9111_at_corp.supernews.com>


[Quoted] "Simon Filiatrault" <simon.marie_at_sympatico.ca> wrote in message news:f3kp5.187106$Gh.3411817_at_news20.bellglobal.com...
> Hi all,
>
> All I know for now is sqlplus and how to do a simple select statement.... I
> want to be able to do something like that:
>
> if (select count() from table) > 100
> then
> update a table
> else
> insert in a table
> end if;

Use PL/SQL:

declare

    i_counter number := 0;
    cursor my_cursor is

        select column1, column2, column3 (etc...)
        from my_table
        where...;

    my_cursor_row my_cursor%ROWTYPE;
begin

    for my_cursor_row in my_cursor loop

        i_counter := i_counter + 1;
        if i_counter <= 100 then
            insert into other_table(columns...)
            values(values...);
        else
            update other_table
            set...;
        end if;

    end loop;
end;
/
commit;

If you need to refer to the actual columns in the select after beginning the loop, it's like this:

my_cursor_row.column1
my_cursor_row.column2
etc...

Now that I think about it, when I did this declaration statement:

my_cursor_row my_cursor%ROWTYPE;

It might not be necessary because I think it's implied in my for-loop. Try it with and without that declaration statement (not sure if it has to be there or not).

-Matt Received on Fri Aug 25 2000 - 03:56:27 CEST

Original text of this message