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. |
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:
CREATE FUNCTION <function_name> <function_definition_includes_return_statement>
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;
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>}
CREATE FUNCTION <function_name> ([@<parameter_name> <datatype> [ = <default>]],...) RETURNS <datatype> [AS] BEGIN <function_body> RETURN <value> END
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 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 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.
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)
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.