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;
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.
 This is one reason why you should never use SELECT * when working with a view. Always explicitly name the columns that you need so that the server doesn't waste time generating data that you never reference.
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.
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.