Re: Stored Procedure Question

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: 1998/11/13
Message-ID: <364d40f9.93572880@inet16.us.oracle.com>#1/1

On Thu, 12 Nov 1998 16:48:30 -0800, "Brent Moonen" <brentm_at_flash.net> wrote:

>>> I am new to Oracle (version 7.3.3) and I am having difficulty creating a
>>> stored procedure. I want to be able to pass a value into this procedure
 and
>>> have it run a simple select statement using the value passed in the where
>>> clause. I believe this is possible in SQL Server, so I figured it would
 be
>>> possible in Oracle. I have not been able to do this and the Oracle
>>> documentation only gives examples using action queries (I have had
 success
>>> using Inserts, Updates, & Deletes in Procedure). This is my statement:
>>>
>>> CREATE PROCEDURE TEST (xyz IN number)
>>> AS
>>> BEGIN
>>> SELECT * FROM SomeTable
>>> WHERE SomeField = xyz;
>>> END;
>>> /
>>>
>>> This results in a compiliation error. Is this sort of thing possible or
>>> not? I thought of trying to use function, but I believe that it will
 only
>>> return one value rather than all the fields in the table. I'll be
>calling

You need to select the values into something.

eg.

CREATE PROCEDURE TEST (xyz IN number)
AS
  l_row SomeTable%rowtype
BEGIN

     SELECT * 
       into l_row
       FROM SomeTable
      WHERE SomeField = xyz;

END;
/

This will work great if you are assured that you will only ever get back one row. If you might get back more then you will need to process in a loop.

eg.

CREATE PROCEDURE TEST (xyz IN number)
AS
BEGIN

     for c1 in ( SELECT * 
                   FROM SomeTable
                  WHERE SomeField = xyz )
     loop
       do something with c1.column1;
     end loop;

END;
/

You can return values in two ways. Use a function or out parameters.

eg.

create or replace
procedure my_proc( xyz in number, abc out number, def out number ) as begin
  select col1, col2
    into abc, def
    from SomeTable
   where SomeColumn = xyz;
end;
/

or

create or replace
function my_proc( xyz in number ) return number as

   abc number;
begin
  select col1
    into abc
    from SomeTable
   where SomeColumn = xyz;
  return abc;
end;
/

You can also have out parameters in functions.

hope this helps.

chris. Received on Fri Nov 13 1998 - 00:00:00 CST