Re: Stored procedures and "select" requires "into"...why?

In a previous article, smithkl_at_cpva.saic.com () says:

>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....
>
>I would like to perform a query which is rather involved with multiple
>sub-queries and a union with other sub-queries. I don't want to have
>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
>documented anywhere, but the parser generates a "expected 'into' but
>found 'from'" type error when I do a "show errors" after I enter the
>query. This procedure has 8 or so parameters where any or all default
>to '%'. Let me give a simple example of what I'm trying to do: (note:
>this is not the real query, just an example)
>
Just to answer this part of the question, the reason you must 'select...into..' is that stored procedures are PL/SQL, which does not return rows like a SQL query does. There is no point in having a select statement in PL/SQL without selecting it into something. Cursors are the way to retrieve more than one row, but it still wouldn't give you the result you are looking for.

Oracle 7 does cache the parsed queries. Probably that's the easiest way to go.

-- 
Diana Tracy, System Designer		-- Excitement, Adventure
bs794_at_cleveland.Freenet.Edu		-- and Really Wild Things