用户自定义函数

----start

    我们可以创建五种类型的用户自定义函数：

1、有源（或模板） 2、SQL 标量、表或行 3、外部标量 4、外部表 5、OLE DB 外部表 

一：有源函数：Sourced Function

    顾名思义，有源函数是从一个已经存在的函数中构造出来的函数。它通常应用于操作用户自定义数据类型。因为现有的函数和操作符(+ - * /)不支持用户自定义的数据类型，所以当我们需要操作用户自定义数据类型时，就可以根据现有函数定义一个有源函数。

1、语法：

CREATE FUNCTION [FunctionName] ( <<[ParameterName]> [InputDataType] ,...> ) RETURNS [OutputDataType] <SPECIFIC [SpecificName]> SOURCE [SourceFunction] <([DataType] ,...)> <AS TEMPLATE> 说明： FunctionName 指定要创建的有源函数的名称。 ParameterName 指定一个或多个函数参数的名称。 InputDataType 指定 ParameterName 所识别的参数所需的数据类型。 OutputDataType 指定函数返回的数据的类型。 SpecificName 指定分配给这个 UDF 的特定名称。这个名称可以用来引用或删除函数；但是，不能用来调用函数。 SourceFunction 指定用来创建这个有源函数的现有函数。 DataType 指定现有函数的每个参数期望接收的数据类型。

2、示例：

--连接数据库 CONNECT TO SAMPLE; --定义美元数据类型 CREATE DISTINCT TYPE US_DOLLARS AS DEC(7,2) WITH COMPARISONS; --定义表 CREATE TABLE CUSTOMERS ( ID SMALLINT NOT NULL, BALANCE US_DOLLARS NOT NULL ); --插入值 INSERT INTO CUSTOMERS VALUES (1 ,111.11),(2 ,222.22); --查询SQL SELECT ID,BALANCE*10 FROM CUSTOMERS; --上面的语句会发生异常，原因操作符*不支持US_DOLLARS，为了上面的语句能执行，定义下面的函数： CREATE FUNCTION "*" (US_DOLLARS,INT) RETURNS US_DOLLARS SOURCE SYSIBM."*"(DECIMAL,INT); --再次执行查询SQL SELECT ID,BALANCE*10 FROM CUSTOMERS; --删除用户定义函数 DROP FUNCTION "*" (US_DOLLARS,INT); --删除表 DROP TABLE CUSTOMERS; --删除美元数据类型 DROP DISTINCT TYPE US_DOLLARS; --关闭连接 CONNECT RESET;  

3、运行示例：

将上面的代码保存为c:/test.sql,然后在DB2 命令窗口中执行如下命令 db2 -tvf c:/test.sql  

二：SQL 标量、表或行函数

    标量函数只返回一个值，标量函数中不允许INSERT、UPDATE、DELETE 语句。

    表函数返回一个表或者若干行，在FROM 子句调用它们。表函数与标量函数不同，它能够使用INSERT、UPDATE、DELETE 语句。

1、语法

CREATE FUNCTION [FunctionName] ( <<[ParameterName]> [InputDataType] ,...> ) RETURNS [[OutputDataType] | TABLE ( [ColumnName] [ColumnDataType] ,... ) | ROW ( [ColumnName] [ColumnDataType] ,... )] <SPECIFIC [SpecificName]> <LANGUAGE SQL> <DETERMINISTIC | NOT DETERMINISTIC> <EXTERNAL ACTION | NO EXTERNAL ACTION> <CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA> <STATIC DISPATCH> <CALLED ON NULL INPUT> [SQLStatements] | RETURN [ReturnStatement] 说明： FunctionName 指定要创建的 SQL 函数的名称。 ParameterName 指定一个或多个函数参数的名称。 InputDataType 指定 ParameterName 所识别的参数所需的数据类型。 OutputDataType 指定函数返回的数据的类型。 ColumnName 指定函数返回的一列或多列的名称（如果此函数返回表或行的话）。 ColumnDataType 指定 ColumnName 所识别的列返回的数据类型。 SpecificName 指定分配给这个 UDF 的特定名称。这个名称可以用来引用或删除函数；但是，不能用来调用函数。 SQLStatements 指定在调用函数时执行的一个或多个 SQL 语句。这些语句组合成一个动态复合 SQL 语句。 ReturnStatement 指定用于返回调用函数的应用程序的 RETURN SQL 语句。（如果 SQL 函数体由动态复合语句组成，那么它必须包含至少一个 RETURN 语句；在调用函数时，必须执行一个 RETURN 语句。如果函数是表函数或行函数，那么只能包含一个 RETURN 语句，而且此语句必须是使用的最后一个语句）。 <LANGUAGE SQL> 指定函数是用 SQL 编写的。 <DETERMINISTIC | NOT DETERMINISTIC> 表示在用相同的参数值调用函数时，函数是否总是返回相同的标量值、表或行（DETERMINISTIC 代表确定性函数，NOT DETERMINISTIC 表示非确定性函数）。如果没有指定这两个子句，那么在用相同的参数值调用函数时函数可能返回不同的结果。 <EXTERNAL ACTION | NO EXTERNAL ACTION> 表示函数执行的操作是否会改变不由 DB2 管理的对象的状态（EXTERNAL ACTION 表示会改变，NO EXTERNAL ACTION 表示不改变）。外部操作包括发送电子邮件或在外部文件中写记录等。如果没有指定这两个子句，那么意味着函数可能执行某种外部操作。 <CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA> 表示在 UDF 体中编写的 SQL 语句的类型。有三个值可用： CONTAINS SQL：UDF 体包含的可执行 SQL 语句既不读数据，也不修改数据。 READS SQL DATA：UDF 体包含的可执行 SQL 语句读数据，但是不修改数据。 MODIFIES SQL DATA：UDF 体包含的可执行 SQL 语句既可以读数据，也可以修改数据。 <STATIC DISPATCH> 表示在函数解析时 DB2 根据函数参数的静态类型（声明的类型）选择函数。 <CALLED ON NULL INPUT> 表示无论任何参数是否包含 null 值，都调用此函数。 

2、示例

--连接数据库 CONNECT TO SAMPLE! --创建标量函数 CREATE FUNCTION CHECK_LEN(INSTR VARCHAR(50)) RETURNS SMALLINT BEGIN ATOMIC IF INSTR IS NULL THEN RETURN NULL; END IF; IF LENGTH(INSTR) < 6 THEN SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'INPUT STRING IS < 6'; ELSEIF LENGTH(INSTR) < 7 THEN RETURN -1; END IF; RETURN LENGTH(INSTR); END! --测试标量函数 VALUES CHECK_LEN('WAVE')! VALUES CHECK_LEN('12345678')! --删除标量函数 DROP FUNCTION CHECK_LEN! --创建表函数 CREATE FUNCTION TESTTF() RETURNS TABLE (ID SMALLINT,NAME VARCHAR(9)) RETURN SELECT * FROM (VALUES (1,'张三'),(2,'李四'))! --测试表函数:需要应用TABLE()函数和别名 SELECT * FROM TABLE(TESTTF()) AS TEMP! --删除表函数 DROP FUNCTION TESTTF! --关闭连接 CONNECT RESET;

3、运行示例：

将上面的代码保存为c:/test.sql,然后在DB2 命令窗口中执行如下命令 db2 -td! -vf c:/test.sql

三：其他函数

    外部标量函数、外部表函数和Microsoft OLE DB 外部表函数，它们是用C、C++ 或 Java™ 等外部高级编程语言编写的，这里就不做介绍了，更多细节请参考DB2 信息中心。

----更多参见：SQL PL 精萃

----声明：转载请注明出处。

----last updated on 2010.2.9

----written by ShangBo on 2010.2.8

----end