Re: Stored procedures and "select" requires "into"...why?
Date: 1 Feb 1994 09:30:59 +1100
Message-ID: <2ik0r3$3bi_at_werple.apana.org.au>
smithkl_at_cpva.saic.com writes:
>First the particulars:
>Oracle7 Server Release 7.0.13.1.0 with procedural and distributed options.
>PL/SQL Release 2.0.15.1.0
>In attempting to use "stored procedures" I have run across a snag....
...
>the parser parse this thing more than once, so I am trying to create
>a stored procedure (like I was able to do in Sybase). The problem seems
>to be that any "select" statement in a stored procedure must select
>"into" a variable. This won't work very well due to the fact that
>most of these queries return multiple rows. This doesn't seem to be
.....
>What I expect back is 0 or more rows containing all users on the system
>whose username begins with 'M'.
Sybase stored procedures are very different from ORACLE stored procedures in that you can fetch rows from the sybase s.p. In other words, a sybase stored procedure returns a relation/result set/virtual table what have you. I was very dissapointed that ORACLE did not see fit to implement this feature. I think what you can do is create a package which contains a cursor for the query. then you can fetch the rows from a PL/SQL block.
for example:
create package test as
cursor test_c is
select * from dual;
END;
.
/
declare
mydummy varchar2(1);
begin
open test.test_c;
loop
fetch test.test_c into mydummy;
exit when test.test_c%NOTFOUND;
end loop;
close test.test_c;
end;
/
Hope this helps,
Guy
Guy Harrison gharriso_at_werple.apana.org.au ORACLE Database Consultant gharriso_at_vitgwms1.telecom.com.auMelbourne Australia Received on Mon Jan 31 1994 - 23:30:59 CET