11.5 Stored Functions in DML Statements

Stored functions may also be called from INSERT, UPDATE, and DELETE statements. Whereas most of the restrictions outlined earlier apply equally to stored functions called from DML statements, there is one major difference: since the parent DML statement is changing the state of the database, stored functions invoked from DML statements do not need to abide by the WNDS restriction. However, such stored functions may not read or modify the same table as the parent DML statement.

Like queries, DML statements may call stored functions where expressions are allowed, including:

The VALUES clause of an INSERT statement
The SET clause of an UPDATE statement
The WHERE clause of an INSERT, UPDATE, or DELETE statement

Any subqueries called from a DML statement may also call stored functions as well under the same set of restrictions as the parent DML statement.

Often, sets of complementary stored functions are called from both queries and DML statements. For example, you saw earlier how the pkg_util.translate_date function could be called from a query to translate from the Oracle date format stored in the database to the format needed by a Java client. Similarly, the overloaded pkg_util.translate_date function may be used within an update statement to perform the reverse translation, as in:

UPDATE cust_order

SET expected_ship_dt = pkg_util.translate_date(:1)

WHERE order_nbr = :2;

where :1 and :2 are placeholders for the UTC timedate and order number passed in by the Java client.

Stored functions may also be used in the WHERE clause in place of correlated subqueries, both to simplify the DML statement and to facilitate code reuse. For example, suppose you have been asked to push the expected ship date by five days for any order containing part number F34-17802. You could issue an UPDATE statement against the cust_order table using a correlated subquery, as in:

UPDATE cust_order co

SET co.expected_ship_dt = NVL(co.expected_ship_dt, SYSDATE) + 5

WHERE co.cancelled_dt IS NULL and co.ship_dt IS NULL

  AND EXISTS (SELECT 1 FROM line_item li

    WHERE li.order_nbr = co.order_nbr

      AND li.part_nbr = 'F34-17802');

After having written many subqueries against the line_item table, however, you feel it's time to write a multipurpose function and add it to the pkg_util package:

FUNCTION get_part_count(ordno IN NUMBER, 

  partno IN VARCHAR2 DEFAULT NULL, max_cnt IN NUMBER DEFAULT 9999)

  RETURN NUMBER IS

  tot_cnt NUMBER(5) := 0;

  li_part_nbr VARCHAR2(20);

  CURSOR cur_li(c_ordno IN NUMBER) IS

    SELECT part_nbr

    FROM line_item

    WHERE order_nbr = c_ordno;

BEGIN

  OPEN cur_li(ordno);

  WHILE tot_cnt < max_cnt LOOP

    FETCH cur_li INTO li_part_nbr;

    EXIT WHEN cur_li%NOTFOUND;



    IF partno IS NULL OR 

      (partno IS NOT NULL AND partno = li_part_nbr) THEN

      tot_cnt := tot_cnt + 1;

    END IF;

  END LOOP;

  CLOSE cur_li;



  RETURN tot_cnt;

END get_part_count;

The function may be used for multiple purposes, including:

To count the number of line items in a given order
To count the number of line items in a given order containing a given part
To determine whether the given order has at least X occurrences of a given part

The UPDATE statement may now use the function to locate open orders that have at least one occurrence of part F34-17802:

UPDATE cust_order co

SET co.expected_ship_dt = NVL(co.expected_ship_dt, SYSDATE) + 5

WHERE co.cancelled_dt IS NULL and co.ship_dt IS NULL

  AND 1 = pkg_util.get_part_count(co.order_nbr, 'F34-17802', 1);