Re: Newbie question about PL/SQL

From: bany1 <kheunc_at_yahoo.com>
Date: Sat, 26 Aug 2000 10:12:04 -0400
Message-ID: <39A7D034.6A1CB20A@yahoo.com>

You can also do this with cursor for loop:

declare

    i_counter number := 0;
begin

    for my_cursor_row in (

                 select column1, column2, column3 (etc...)
                from my_table
                where...; )  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;

"Matt B." wrote:

> "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 Sat Aug 26 2000 - 09:12:04 CDT