Creating Oracle stored procedure that returns data

Question

In Firebird you can create a stored procedure that returns data an invoke it like a table passing the arguments:

create or alter procedure SEL_MAS_IVA (
    PCANTIDAD double precision)
returns (
    CANTIDAD_CONIVA double precision)
as
begin
 CANTIDAD_CONIVA = pCANTIDAD*(1.16);
 suspend;
end

select * from SEL_MAS_IVA(100)

will return a single row single column (named CANTIDAD_CONIVA) relation with the value 116

This is a very simple example. The stored procedure can of course have any number of input and output parameters and do whatever it needs to return data (including multiple rows), which is accomplished by the "suspend" statement (which as it name implies, suspends the SP execution, returns data to the caller, and resumes with the next statement)

How can I create such kind of stored procedures in Oracle?

Please look at the [PL/SQL documentation](http://docs.oracle.com/cd/E18283_01/appdev.112/e17126/subprograms.htm) to get a grasp of the concepts. You can't really come here to be taught everything that contains. — Alex Poole, Aug 18 '14 at 22:11
I removed the Firebird tag. It's nice that you can do it in Firebird, but the question is how to do it in Oracle. — GolezTrol, Aug 18 '14 at 22:18
Put simply, no. In Oracle you'd do something like `SELECT SEL_MAS_IVA(100) AS CANTIDAD_CONIVA FROM DUAL`. Share and enjoy. — Bob Jarvis - Слава Україні, Aug 18 '14 at 22:48
These are known as "REF CURSORS" in Oracle. You can search on that for examples, etc. For example, see: http://stackoverflow.com/questions/5822241/pl-sql-print-out-ref-cursor-returned-by-a-stored-procedure — WW., Aug 19 '14 at 00:35

Rimas · Answer 1 · 2014-08-19T19:38:01.457

In Oracle it is possible by using pipelined functions. Your example:

-- define column names:
CREATE OR REPLACE TYPE SEL_MAS_IVA_obj AS OBJECT (
  CANTIDAD_CONIVA NUMBER)
/
CREATE OR REPLACE TYPE SEL_MAS_IVA_type AS TABLE OF SEL_MAS_IVA_obj
/

CREATE OR REPLACE
FUNCTION SEL_MAS_IVA(PCANTIDAD IN NUMBER)
RETURN SEL_MAS_IVA_type
PIPELINED
 IS
BEGIN
 pipe row(SEL_MAS_IVA_obj(PCANTIDAD * 1.16));
 RETURN;
END;
/

and get values:

SELECT * FROM TABLE(SEL_MAS_IVA(100));

Also see another sample where function returns more columns and rows: https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4447489221109

score 0 · Answer 2 · answered Aug 19 '14 at 07:58

0

You need to use a function:

create or replace function SEL_MAS_IVA (
    pCANTIDAD IN NUMBER
)
return NUMBER
as
begin
  return pCANTIDAD*(1.16);
end SEL_MAS_IVA;

select SEL_MAS_IVA(100) CANTIDAD_CONIVA from dual;

answered Aug 19 '14 at 07:58

neshkeev

6,280
3
26
47

A selectable procedure in Firebird can produce multiple rows with multiple columns (somewhat like a parametrized view), so this isn't equivalent for the general case – Mark Rotteveel Aug 19 '14 at 10:24
@MarkRotteveel If you took a look at this: "will return a single row single column (named CANTIDAD_CONIVA) relation with the value 116" you would've understood that my code worked as the author of the question wanted. – neshkeev Aug 19 '14 at 11:00
It is indeed an answer for this specific example, but given the second part of the question (_"This is a very simple example. The stored procedure can of course have **any number of ... output parameters** and do whatever it needs to return data (including **multiple rows**)"_) it doesn't answer the general case. – Mark Rotteveel Aug 19 '14 at 12:46

Creating Oracle stored procedure that returns data

2 Answers2