Re: Stored Procedure Question
Date: 1998/11/13
Message-ID: <364d40f9.93572880_at_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 CET