Re: Oracle Dope needs help with retrieving records from stored procedure

spawncamper wrote:

> I'm fairly new to Oracle, so I apologize in advance if this question
> makes you shake your head. However, I'm trying to create a stored
> procedure in Oracle 8i to return a value that would be called using
> SELECT EMPLOYEEID FROM EMPLOYEELASTNAME('JONES') where
> EMPLOYEELAASTNAME would be the name of the procedure and would
> obviously return the id.
>
> When I create a proc to do a select, I get a "an INTO clause is
> expected in this SELECT statement" error. Doing research on the
> internet has not been fruitful. Any assistance for would be greatly
> appreciated.
>
> Thanks,
>
> An Oracle Dope

Perhaps something along these lines:

Create or replace procedure
myproc(in_name varchar2,out_name out varchar2) as

     tempvar varchar2(25) := null;

     select employeeid
     into tempvar
     from employee
     where last_name = in_name;

     out_name := tempvar;
exception
     when no_data_found then
         return;

You would call it with the desired last name as a parameter and the caller would have to define a variable to receive the results.

Of course there are more elegant ways of doing this and this might not be a case where a stored procedure is the best way of handling this type of thing. There are all sorts of pit-falls with my example (for instance, two employees with the same last name). But hopefully you get the idea.

I've never been a fan of SELECT...INTO though since they are implicit cursors. It almost always comes back to bite you in some way. Explicit cursors are generally preferred in most cases since they provide greater control. Received on Thu Jun 17 2004 - 22:07:56 CDT