11.5 Stored Functions in DML Statements

Stored functions may also be called from INSERT, UPDATE, and DELETE statements. While 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 complimentary stored functions are called from both queries and DML statements. For example, we 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:

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);