Can a Stored Procedure return more than one row?

[CraigBest]

Looking for some help...

I want to build a stored procedure that I pass in a value and get back 5 fields from a table. It's likely the query would return more than one record when it is called. Can an Oracle SP do this? The text I have seems to indicate it will only return a single row.

I have constructed what appears to be a properly worded SP according the the text I have below:

----------------

PROCEDURE P_LogGrid (
P_PatrolNum IN Number,
P_PatrolRecNum OUT Number,
P_AmendedDT OUT DATE,
P_Location OUT VarChar2,
P_PlComment OUT VarChar2,
P_IncidentRpt OUT Number
) IS

BEGIN

SELECT PatrolRecNum, AmendedDT, Location, PLComment, IncidentRpt FROM PatrolLog
WHERE PatrolNum = P_PatrolNum;

RETURN P_PatrolRecNum, P_AmendedDT, P_Location, P_PlComment, P_IncidentRpt;

END

-----------------

I have not been able to test this yet. Can anyone tell me if this will work, or if I need to change something?

Thanks very much

CraigHartz


CraigHartz

 

[SantaMufasa]

Craig,

Here are a few thoughts/issues:

* The verb, "RETURN" is for use in FUNCTIONs only, not for PROCEDUREs. The way that you "return" values from a procedure is by your assigning the OUT variables their appropriate returning values. The RETURN statment is a source of at least one of your syntax errors.

* In PL/SQL (which includes user-defined functions and procedures), when you have a SELECT statement that does not reside in either an implicit or explicit CURSOR, then you must use the form: "SELECT <expressing list> INTO <matching PL/SQL variable list> FROM <tablename>...". Therefore, the lack of the appropriate INTO clause is the source of one or more of your additional syntax errors.

* If the code you posted is the logic extent of your PROCEDURE, then, IMHO, you would be better served by just doing the SELECT in your calling environment, perhaps using an in-line, implicit CURSOR (which we can show you how to accomplish if you are unfamiliar with that powerful, excellent syntax construct.)

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[nshen]

Mufasa was right.
If you need to do more in this stored procedure plus returning a result set, you need to use a REF CURSOR.
Just be sure the REF CURSOR is in IN OUT mode and it must be the very last parameter.
Regards, Nancy