Creating Oracle stored procedure that returns data

Posted by user3614327 on Stack Overflow See other posts from Stack Overflow or by user3614327
Published on 2014-08-18T21:55:21Z Indexed on 2014/08/18 22:20 UTC
Read the original article Hit count: 132

Filed under:

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?

© Stack Overflow or respective owner

Related posts about Oracle