11.3 Calling Stored Functions from Queries
As mentioned earlier, stored functions may be called from within SQL statements. Since stored functions can in
turn
make calls to stored procedures, it can also be said that stored procedures may be called, albeit indirectly, from within SQL statements. Since stored functions may be used in expressions, they may be included wherever expressions are allowed in a query, including:
-
The SELECT clause
-
The WHERE clause
-
The GROUP BY and HAVING clauses
-
The ORDER BY clause
-
The START WITH/CONNECT BY clauses (for hierarchical queries)
-
The FROM clause (indirectly by using inline views or TABLE statements)
One of the most common uses of stored functions is to isolate commonly-used functionality to facilitate code reuse and simplify maintenance. For example, imagine that you are working with a large team to build a custom N-
tier
application. To simplify integration efforts between the various
layers
, it has been decided that all dates will be passed back and forth as the number of
milliseconds
since January 1, 1970. You could include the conversion logic in all of your queries, as in:
SELECT co.order_nbr, co.cust_nbr, co.sale_price,
ROUND((co.order_dt - TO_DATE('01011970','MMDDYYYY')) * 86400 * 1000)
FROM cust_order co
WHERE ship_dt = TRUNC(SYSDATE);
However, this would become somewhat
tedious
and
prove
problematic
should you wish to modify your logic in the future. Instead, build a utility package that includes functions for translating between Oracle's internal date format and the desired format:
CREATE OR REPLACE PACKAGE BODY pkg_util AS
FUNCTION translate_date(dt IN DATE) RETURN NUMBER IS
BEGIN
RETURN ROUND((dt - TO_DATE('01011970','MMDDYYYY')) * 86400 * 1000);
END translate_date;
FUNCTION translate_date(dt IN NUMBER) RETURN DATE IS
BEGIN
RETURN TO_DATE('01011970','MMDDYYYY') + (dt / (86400 * 1000));
END translate_date;
END pkg_util;
If you think you're seeing double, don't worry; the package contains two identically-named functions, one that requires a DATE parameter and returns a NUMBER, and another that requires a NUMBER parameter and returns a DATE. This strategy, called
overloading
, is only possible when your functions are contained in a package.
Your development team can now use these functions whenever they need to convert date formats, as in:
SELECT co.order_nbr, co.cust_nbr, co.sale_price,
pkg_util.translate_date(co.order_dt) utc_order_dt
FROM cust_order co
WHERE co.ship_dt = TRUNC(SYSDATE);
Another common use of stored functions is to simplify and hide complex IF-THEN-ELSE logic from your SQL statements. Suppose you have to generate a report detailing all customer orders for the past month. You want to
sort
the orders using the
ship_dt
column if an order has been shipped, the
expected_ship_dt
column if a ship date has been assigned and is not in the past, the current day if the
expected_ship_dt
is in the past, or the
order_dt
column if the order hasn't been assigned a ship date. You could utilize a CASE statement in the ORDER BY clause:
SELECT co.order_nbr, co.cust_nbr, co.sale_price
FROM cust_order co
WHERE co.order_dt > TRUNC(SYSDATE, 'MONTH')
AND co.cancelled_dt IS NULL
ORDER BY
CASE
WHEN co.ship_dt IS NOT NULL THEN co.ship_dt
WHEN co.expected_ship_dt IS NOT NULL
AND co.expected_ship_dt > SYSDATE
THEN co.expected_ship_dt
WHEN co.expected_ship_dt IS NOT NULL
THEN GREATEST(SYSDATE, co.expected_ship_dt)
ELSE co.order_dt
END;
However, there are two problems with this approach:
-
The resulting ORDER BY clause is
fairly
complex.
-
You may wish to use this logic elsewhere, and duplicating it will create maintenance problems.
Instead, add a stored function to the
pkg_util
package that returns the appropriate date for a given order:
FUNCTION get_best_order_date(ord_dt IN DATE, exp_ship_dt IN DATE,
ship_dt IN DATE) RETURN DATE IS
BEGIN
IF ship_dt IS NOT NULL THEN
RETURN ship_dt;
ELSIF exp_ship_dt IS NOT NULL AND exp_ship_dt > SYSDATE THEN
RETURN exp_ship_dt;
ELSIF exp_ship_dt IS NOT NULL THEN
RETURN SYSDATE;
ELSE
RETURN ord_dt;
END IF;
END get_best_order_date;
You may then call this function from both the SELECT and ORDER BY clauses:
SELECT co.order_nbr, co.cust_nbr, co.sale_price,
pkg_util.get_best_order_date(co.order_dt, co.expected_ship_dt,
co.ship_dt) best_date
FROM cust_order co
WHERE co.order_dt > TRUNC(SYSDATE, 'MONTH')
AND co.cancelled_dt IS NULL
ORDER BY pkg_util.get_best_order_date(co.order_dt, co.expected_ship_dt,
co.ship_dt);
If you are bothered by the fact that the stored function is called twice per row with the same parameters, you can always retrieve the data within an inline view and sort the results afterward, as in:
SELECT orders.order_nbr, orders.cust_nbr,
orders.sale_price, orders.best_date
FROM
(SELECT co.order_nbr order_nbr, co.cust_nbr cust_nbr,
co.sale_price sale_price,
pkg_util.get_best_order_date(co.order_dt, co.expected_ship_dt,
co.ship_dt) best_date
FROM cust_order co
WHERE co.order_dt > TRUNC(SYSDATE, 'MONTH')
AND co.cancelled_dt IS NULL) orders
ORDER BY orders.best_date;
11.3.1 Stored Functions and Views
Since a view is nothing more than a stored query and stored functions can be called from the SELECT clause of a query,
columns
of a view can map to stored function calls. This is an
excellent
way to shield your
user
community from complexity, and it has another interesting benefit as well. Consider the following view definition, which includes calls to several different stored functions:
CREATE OR REPLACE VIEW vw_example
(col1, col2, col3, col4, col5, col6, col7, col8)
AS SELECT t1.col1,
t1.col2,
t2.col3,
t2.col4,
pkg_example.func1(t1.col1, t2.col3),
pkg_example.func2(t1.col2, t2.col4),
pkg_example.func3(t1.col1, t2.col3),
pkg_example.func4(t1.col2, t2.col4)
FROM tab1 t1 INNER JOIN tab2 t2
ON t1.col1 = t2.col3;
Whereas the first four columns of the view map to columns of the
tab1
and
tab2
tables, values for the remaining columns are generated by calling various functions in the
pkg_example
package. If one of your users executes the following query:
SELECT col2, col4, col7
FROM vw_example
WHERE col1 = 1001;
Only one stored function (
pkg_example.func3
) is actually executed even though the view contains four columns that map to stored function calls. This is because when a query is executed against a view, the Oracle server constructs a new query by combining the original query and the view definition. In this case, the query that is actually executed looks like:
SELECT t1.col2,
t2.col4,
pkg_example.func3(t1.col1, t2.col3)
FROM tab1 t1 INNER JOIN tab2 t2
ON t1.col1 = t2.col3
WHERE t1.col1 = 1001;
Therefore, your view could contain dozens of stored function calls, but only those that are explicitly referenced by queries will be executed.
11.3.2 Avoiding Table Joins
Imagine that you have deployed a set of views for your users to generate
reports
and ad-hoc queries against, and one of your users asks that a new column be added to one of the views. The column is from a table not yet included in the FROM clause, and the column is only needed for a single report issued once a month. You could add the table to the FROM clause, add the column to the SELECT clause, and add the join conditions to the ON clause. However, every query issued against the view would include the new table, even though most queries don't reference the new column.
An alternative strategy is to write a stored function that queries the new table and returns the desired column. The stored function can then be added to the SELECT clause without the need to add the new table to the FROM clause. To
illustrate
, let's expand on the previous simple example. If the desired column is
col6
in the
tab3
table, you could add a new function to the
pkg_example
package such as:
FUNCTION func5(param1 IN NUMBER) RETURN VARCHAR2 IS
ret_val VARCHAR2(20);
BEGIN
SELECT col6 INTO ret_val
FROM tab3
WHERE col5 = param1;
RETURN ret_val;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN null;
END func5;
You can now add a column to the view that maps to the new function, as in:
CREATE OR REPLACE VIEW vw_example
(col1, col2, col3, col4, col5, col6, col7, col8, col9)
AS SELECT t1.col1,
t1.col2,
t2.col3,
t2.col4,
pkg_example.func1(t1.col1, t2.col3),
pkg_example.func2(t1.col2, t2.col4),
pkg_example.func3(t1.col1, t2.col3),
pkg_example.func4(t1.col2, t2.col4),
pkg_example.func5(t2.col3)
FROM tab1 t1 INNER JOIN tab2 t2
ON t1.col1 = t2.col3;
Thus, you have provided your users access to column
col6
of the
tab3
table without adding the
tab3
table to the view's FROM clause. Users who don't reference the new
col9
column of the view will experience no changes to the performance of their queries against
vw_example
.
Even though the column was originally
targeted
for a single report, don't be surprised if other users decide to include the new column in their queries. As the column utilization
increases
, it may be advantageous to abandon the stored function strategy and include the
tab3
table in the FROM clause. Since a view was employed, however, you would be able to make this change without the need for any of your users to modify their queries.
11.3.3 Deterministic Functions
Earlier in this section, we created a package containing two functions to be used for translating between a date and the number of milliseconds since January 1, 1970. Because these functions do not depend on data stored in the database or in package
variables
, they will always return the same result for any given input parameter. Any function having this property can be
marked
as DETERMINISTIC in the function declaration, as in:
CREATE OR REPLACE PACKAGE BODY pkg_util AS
FUNCTION translate_date(dt IN DATE) RETURN NUMBER
DETERMINISTIC
;
FUNCTION translate_date(dt IN NUMBER) RETURN DATE
DETERMINISTIC
;
END pkg_util;
Marking your functions as DETERMINISTIC allows the Oracle server to perform certain optimizations, such as storing a function's parameters and results in memory so that
subsequent
calls to the same function can be handled without the need to call the function again.
|
