Creating Oracle stored procedure that returns data
- by user3614327
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?
