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.^[3]

^[3] 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.

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.