Home » SQL & PL/SQL » SQL & PL/SQL » I have a question about procedure
I have a question about procedure [message #37216] Thu, 24 January 2002 16:08 Go to next message
gxg
Messages: 1
Registered: January 2002
Junior Member
Dear sir:
A result can be return from a procedure by a SELECT
sentece in Sbase or SQL Server database. For examle:
CREATE PROCEDURE mytest
AS
SELECT name,age,sex FROM employees

My question is how implement the goal in Oracle
Procedure?
Re: I have a question about procedure [message #37217 is a reply to message #37216] Thu, 24 January 2002 18:05 Go to previous messageGo to next message
sokeh
Messages: 77
Registered: August 2000
Member
With Oracle, you create variables either as global variables as parameters or local variables to store the select query into.
example:
create or replace PROCEDURE mytest
AS
v_name varchar2(25);
v_age number; -- assuming age is a number datatype
v_sex varchar2(25);
begin

SELECT name,age,sex
into v_name, v_age, v_sex
FROM employees;
end;
these variables are local.
Like I said you could pass them as parameter by
simply including them within the procedure such as
create or replace procedure mytest(v_name out varcahr2,
v_age out number,
v_sex out varchar2)
as
begin
then your select statement
Re: I have a question about procedure [message #37232 is a reply to message #37216] Fri, 25 January 2002 08:18 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You use a ref cursor to return a result set from a procedure. Here is a great link for this:

http://osi.oracle.com/~tkyte/ResultSets/index.html
Previous Topic: Uploading Files in Oracle
Next Topic: Execute Immediate
Goto Forum:
  


Current Time: Fri Mar 29 08:18:39 CDT 2024