11.3 Calling Stored Functions from Queries

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 clause (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 in order 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. In order 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 could 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;

Note that the package contains two identically-named functions; one requires a DATE parameter and returns a NUMBER, while the second 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, let's add a stored function to our 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, tab2 t2
WHERE t1.col1 = t2.col3;

While 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 as follows:

SELECT t1.col2,
  t2.col4,
  pkg_example.func3(t1.col1, t2.col3)
FROM tab1 t1, tab2 t2
WHERE t1.col1 = 1001 AND t1.col1 = t2.col3;

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 WHERE 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, tab2 t2
WHERE 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.