Re: 2 similar statements, one works, one doesn't. Why?

On Thu, 16 Oct 1997 20:18:54 -0500, rsenn <rsenn_at_capaccess.org> wrote:

>Gentle readers, can someone tell me why one of these statements works
>and the other
>doesn't ? Thanks.
>
>SQL> create or replace procedure test
> 2 is
> 3 begin
> 4 update emp_hist
> 5 set id = 13
> 6 where id = 9999 ;
> 7 end test ;
> 8 /
>
>Procedure created.
>
>SQL> create or replace procedure test
> 2 is
> 3 begin
> 4 select * from emp_hist ;
> 5 -- update emp_hist
> 6 -- set id = 13
> 7 -- where id = 9999 ;
> 8 end test ;
> 9 /
>

If the select is only going to return 1 row you need to declare a variable of type emp_hist%rowtype and select into it

eg

create or replace procedure test is

        myvar emp_hist%rowtype;

begin

        select * into myvar from emp_hist where <condition>;

end;

If the select is going to return multiple rows declare a cursor to hold the result set and process the cursor using open, fetch, close or use a cursor for loop.

eg

create or replace procedure test is

        cursor mycur is select * from emp_hist; begin

	for i in mycur loop
		-- do your processing here
	end loop;

Hope this helps,

Simon Morley. Received on Mon Oct 20 1997 - 00:00:00 CDT