[DB-SIG] How to return results from stored procedures in Oracle
Sun, 01 Sep 2002 12:52:56 +0100
Marcos Sánchez Provencio wrote:
> El sáb, 31-08-2002 a las 18:10, Andy Todd escribió:
>>Marcos Sánchez Provencio wrote:
[snip original question and response]
>>Of course, YMMV.
> I mean the case in which the objective of the procedure (the name
> function would be more correct, I agree) is to return the data, not to
> do any data processing at all. It is very useful to return data to
> Crystal Reports (client/server) and save bandwidth by doing _all_ the
> data processing in the server, for example. I also have two versions of
> an 'application explorer', in VB and Python, and I try to move all the
> functions into the data server to make them completely compatible.
> I will try the various options offered in the list and tell you about
> the results. Thank you.
In that case I would try a slightly different approach. Returning the
results of a select and some calculations from a stored procedure is the
common approach in Sybase and SQL Server but is achieved in a different
way when you've got an Oracle back end - of course.
Oracle allows you to include *functions* in the SELECT clause of your
SQL statement. Thus, instead of fetching your data and applying
modifications to it in a stored code module you can simply write your
modification code and include it in your select statement.
Suppose you want to calculate the tax payable on an invoice amount when
you get the data from invoices table. The Oracle solution would be to
specify a calculation function;
FUNCTION calc_tax( p_inv_amt IN NUMBER ) IS
/* Obviously you wouldn't define this here */
l_tax_rate = 0.15;
RETURN p_inv_amt * l_tax_rate;
And then in your select statement;
SELECT invoice_amt, calc_tax(invoice_amt) tax_amt
WHERE invoice_date < sysdate - 7;
And Bob is - indeed - your Auntie's live in lover.
By the way, functions and procedures ARE different creatures in PL/SQL.
Note the difference is that procedures don't return a value but
functions must. Hence you can only use functions in your SQL statements.
From the desk of Andrew J Todd esq - http://www.halfcooked.com