User-Defined Functions
User-defined functions combine the advantages of stored procedures with the capabilities of SQL predefined functions. They can accept parameters, perform specific calculations based on data retrieved by one or more SELECT statement, and return results directly to the calling SQL statement.Cross-References | Built-in SQL functions are discussed in Chapter 10. |
CREATE FUNCTION syntax
The CREATE FUNCTION syntax is different for different implementations. The simplified SQL99 syntax as well as syntaxes for all "big three" SQL procedural extensions are given here:
SQL99
CREATE FUNCTION <function_name>
<function_definition_includes_return_statement>
PL/SQL
CREATE [OR REPLACE] FUNCTION [qualifier.]<function_name>
([<argument_name> IN | OUT | IN OUT <datatype>
[DEFAULT <default>],...])
RETURN <datatype>
{IS | AS}
[<variable_name <datatype> [DEFAULT <default>];],...
BEGIN
<function_body_includes_return_statement>
[EXCEPTION
<exception_statements>]
END;
DB2 procedural language
CREATE FUNCTION <function_name>
([<argument_name> <datatype>,...])
RETURNS <datatype>
LANGUAGE SQL
[[NOT] DETERMINISTIC]
{MODIFIES SQL DATA |
NO SQL |
CONTAINS SQL |
READS SQL DATA}
RETURN {<value> | <sql_statement>}
Transact-SQL
CREATE FUNCTION <function_name> ([@<parameter_name> <datatype> [ = <default>]],...)
RETURNS <datatype>
[AS]
BEGIN
<function_body>
RETURN <value>
END
Creating a simple function
Functions can be very useful in many situations. For example, imagine you need to extract order totals for customers with state sales tax added to the total price. The sales tax rate is different in different states; for example, in California it is 7.25 percent at this writing. In addition, in the ACME database the total price for orders is not stored anywhere (normalization tradeoffs) but has to be calculated dynamically by adding prices for all order items multiplied by item quantities. The latter is done in the ACME view V_CUSTOMER_TOTALS (see Chapter 4); user-defined function UF_ORDERTAX takes two parameters, tax factor and order number, and returns the total order amount multiplied by that factor.
PL/SQL
PL/SQL syntax to create user-defined functions is not much different from that for stored procedures. The main difference is it has to specify the return data type in the header section and return a value somewhere in the function body. Any code after the RETURN statement in the function body will be ignored. The code below creates function UF_ORDERTAX.
The slash (/) at the end is necessary to compile the function code from a SQLPLUS command line. (That is also true for compiling PL/SQL stored procedures and triggers.) It could usually be omitted when using a GUI tool:
CREATE OR REPLACE FUNCTION uf_ordertax
(
v_tax NUMBER,
v_ordnum VARCHAR2
)
RETURN NUMBER
AS
-- Declare local variables
v_result NUMBER;
v_ordamt NUMBER;
BEGIN
-- This query performs variable v_ordamt assignment
SELECT total_price
INTO v_ordamt
FROM v_customer_totals
WHERE order_number = v_ordnum;
-- Variable v_result is v_ordamt multiplied by tax
v_result := v_ordamt * v_tax;
-- Return result
RETURN v_result;
END;
/
Note | PL/SQL user-defined functions have an optional EXCEPTION clause in the same way the stored procedures do. The foregoing example skips the EXCEPTION section. |
As we mentioned before, the function could be called from within SQL statements using either literals or actual column names as the parameters:
SELECT ordhdr_nbr_s,
ordhdr_orderdate_d,
uf_ordertax(1.0725, ordhdr_nbr_s) AS amt_incl_tax
FROM order_header
JOIN
customer
ON (ordhdr_custid_fn = cust_id_n)
JOIN
address
ON (cust_id_n = addr_custid_fn)
WHERE addr_state_s = 'CA'
ORDHDR_NBR_S ORDHDR_ORDERDATE_D AMT_INCL_TAX
------------------------------ ----------------------- ------------
523774 2002-08-21 00:00:00.000 7037.52
523775 2002-08-21 00:00:00.000 16461.49
523776 2002-08-21 00:00:00.000 13734.45
523777 2002-08-21 00:00:00.000 10660.65
523778 2002-08-21 00:00:00.000 7037.52
523779 2002-08-21 00:00:00.000 7037.52
523780 2002-08-21 00:00:00.000 7037.52
523781 2002-08-21 00:00:00.000 16403.34
523782 2002-08-21 00:00:00.000 8984.34
523783 2002-08-21 00:00:00.000 12927.60
10 rows selected.
Note that the function was executed ten times, once for each row returned by the SELECT statement.
DB2 UDB
DB2 procedural SQL extension has syntax for creating user-defined functions that is quite dissimilar from both PL/SQL and Transact SQL grammar (provided later in this chapter):
CREATE FUNCTION uf_ordertax
(
v_tax DECIMAL(12,2),
v_ordnum VARCHAR(10)
)
RETURNS DECIMAL(12,2)
LANGUAGE SQL NOT DETERMINISTIC
READS SQL DATA
RETURN
-- This query performs variable v_ordamt assignment
SELECT total_price * v_tax
FROM v_customer_totals
WHERE order_number = v_ordnum
@
This function can be compiled in exactly the same way DB2 stored procedures are:
db2 –td@ -vf "C:\SQLBIB\DB2_ACME\UF_PRODUCT.SQL"
You can then call it from a SQL statement using the syntax given previously in the PL/SQL section.
Transact-SQL
The Transact-SQL syntax for user-defined functions is quite similar to the PL/SQL one — more than it resembles the Transact-SQL syntax for creating stored procedures. The parameters are enclosed by parentheses, and the function body is enclosed by the BEGIN and END keywords:
CREATE FUNCTION uf_ordertax
(
@v_tax NUMERIC(12,4),
@v_ordnum VARCHAR(30)
)
RETURNS NUMERIC(12,4)
AS
BEGIN
-- Declare local variables
declare @v_result NUMERIC(12,4)
declare @v_ordamt NUMERIC(12,4)
-- Assign variable @v_ordamt using SELECT statement
SELECT @v_ordamt = total_price
FROM v_customer_totals
WHERE ORDER_NUMBER = @v_ordnum;
-- Variable @v_result is @v_ordamt multiplied by tax
SET @v_result = @v_ordamt * @v_tax
-- Return result
RETURN @v_result
END
The call for a user-defined function from a SQL statement must be qualified with the user name, forming the so-called two-part name of the function:
SELECT ordhdr_nbr_s,
ordhdr_orderdate_d,
dbo.uf_ordertax(1.065, ordhdr_nbr_s)
FROM order_header
JOIN
customer
ON (ordhdr_custid_fn = cust_id_n)
JOIN
address
ON (cust_id_n = addr_custid_fn)
WHERE addr_state_s = 'CA'
A function could also be called with literals for both parameters:
SELECT dbo.uf_ordertax(1.065, '523774') AS ORDER_TOTAL
GO
ORDER_TOTAL
----------------
7037.5200
(1 row affected)
Removing a user-defined function
The basic syntax to remove a user-defined function is identical for all three databases:
DROP FUNCTION [qualifier.]<function_name>
Again, as in case with the stored procedures, Transact-SQL allows you to drop multiple functions within a single DROP FUNCTION statement.