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);
| 
No comments:
Post a Comment