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.