Re: Newbie question about PL/SQL
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