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

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)

create procedure xx(name) as
begin

	select * from all_users 
	where username = name;

I then run it as such:

execute xx('M%');

What I expect back is 0 or more rows containing all users on the system whose username begins with 'M'.

I've gotten some suggestions from people:

1. use a cursor. Not quite sure how to do this, not sure whether this will return all of the rows at once.
2. use arrays passed in as parameters and select into the arrays. Not quite sure that you can have arrays of strings here.

Advice on any of the above suggestions welcome.

I'm thought that I could trick the stored procedure by creating a table from the results of the select, and then issuing another generic query to return all the rows in that table. So I'd try something like:

create procedure xx(name)
as begin

	create table xx_1 as
		select * from all_users where username = name;

execute xx('M%');
select * from xx_1;

This would add the overhead of building the table (to Oracle) but maybe it would still be faster than re-parsing the query over and over again. Another question came out talking with the boys... If you parse the full query every time but use different values in the "where" conditionals, is Oracle smart enough to figure out that the query is really the same as the one it just got only the values to the "where field = " are different? We need to be cognizant of execution speed and server utilization due to the fact that this is a real-time application with many clients connected and accessing the database.

Any suggestions at all (Oracle are you listening) on how to do this would be appreciated. I wanted to get the net.wisdom first before going the Oracle support route. I've been told that I can get a call in, but it would have to be through another contractor who is also working on this contract.

Oh by the way, this is an HP 9000/700 class machine with 9.01 if that makes any difference.

Kevin Smith
smithkl_at_cpva.saic.com Received on Thu Jan 27 1994 - 05:36:39 CET