Re: Newbie question about PL/SQL

From: Eric Leung <eleungks_at_netvigator.com>
Date: Tue, 29 Aug 2000 22:21:05 +0800
Message-ID: <39ABC6D1.62AED6CE_at_netvigator.com>


You don't need to declare the cursor variable:

    my_cursor_row my_cursor%ROWTYPE;

You can just use

    my_cursor_row.column1
    my_cursor_row.column2
    ...
inside the loop.

Eric Leung

"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 Tue Aug 29 2000 - 16:21:05 CEST

Original text of this message