Re: Newbie Oracle sp question

In article <8spqmg$59f$1_at_news.panix.com>,   "Thelonious Georgia" <keepberthasurfin_at_hotmail.com> wrote:
> Hey all-
>
> I've been using sql server for a number of years, and this is the first time
> I've used Oracle 8i heavily. I have a very large sql query I'd like to put
> in a stored procedure so that I merely have to pass the start and end dates,
> and get back a dataset based on that.
>
> Problem is, I cannot seem to write one properly. In transact-sql I'd write:
>
> create procedure foo
> as
> -- super simple
> select * from a_table
>
> which I assumed translated into (with the help of the manual):
>
> create procedure foo
> as
> begin
> select a_column from a_table;
> end;
> /
>
> except that this gives me compilation errors, specifically
> 4/1 PLS-00428: an INTO clause is expected in this SELECT statement
> 4/1 PL/SQL: SQL Statement ignored
>
> The select, when run by itself in sql+, works fine.
>
> I whipped up a quick procedure that did an update:
>
> create procedure foo2
> as
> begin
> update a_table set a_column = 3;
> end;
> /
> and it works perfectly.
>
> So suddenly I've got to ask a very silly question: Can you return a result
> set from a stored procedure? I can't believe that with all the power of
> pl/sql, java, etc., that the only way to return a result set is to push
> dynamic sql. So what am I doing wrong?
>
> Thanks for any info,
>
> Theo
>
>

PL/SQL doesn't allow you write a select statement inside of a block (begin/end). You have to use a cursor to point to a record set in memory. Then use a loop to go thru each each record in record.

create procedure foo as
cursor cur_test is
 select a_column from a_table;
begin
 for rec in cur_test loop
 .....(your logic goes here);
 end loop;
end;

You don't have define the variable rec, which is implicitly defined by Oracle.

Kevin Gao
Consulet Solution

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 20 2000 - 15:22:50 CDT