DB2开发系列之三——SQL函数

来源：互联网发布：淘宝生意参谋入口编辑：程序博客网时间：2024/05/16 10:09

1、内置函数分类（SYSIBM模式内）

1）标量函数：返回一个标量值的函数；

2）聚合函数：也叫列函数，也返回一个标量值，这个值是一组输入值的计算结果；
3）表函数：向引用它的 SQL 语句返回一个表；

4）行函数：返回单一表行；

2、用户定义的函数

1）有源（或模板）函数：有源函数是从一个已经向数据库注册的函数（称为源函数）构造出的 UDF。有源函数在本质上可以是标量、列或表函数，还可以用它们覆盖 +、-、* 和 / 等操作符。

--语法

CREATE FUNCTION [FunctionName] ( <<[ParameterName]> [InputDataType] ,...> )
    RETURNS [OutputDataType]
    <SPECIFIC [SpecificName]>
    SOURCE [SourceFunction] <([DataType] ,...)>
    <AS TEMPLATE>

--示例

CREATE FUNCTION AVG(YEAR) RETURNS YEAR
SOURCE SYSIBM.AVG(INTEGER)

2）SQL 标量、表或行函数：SQL 函数是只使用过程式 SQL 语句构造的 UDF。

--语法

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]

--示例

CREATE FUNCTION julian_date(in_date DATE)
    RETURNS CHAR(7)
    LANGUAGE SQL
    RETURN RTRIM(CHAR(YEAR(in_date))) ||
      SUBSTR(DIGITS(DAYOFYEAR(in_date)), 8)

3）外部标量函数：外部标量函数是用 C、C++ 或 Java™ 等高级编程语言编写的返回单一值的函数。

--创建外部函数的步骤

i)使用支持的高级编程语言编写 UDF 体。

ii)编译 UDF。

iii)链接 UDF 以创建库（或动态链接库）。

iv)调试 UDF 并重复第 2 到第 4 步，直到解决所有问题。

v)把包含 UDF 的库存储在服务器工作站上。另外，必须修改包含 UDF 的库文件的系统权限，让所有用户都可以执行它。例如，在 UNIX 环境中，使用chmod 命令让一个文件可由所有用户执行；在 Windows 环境中，使用attrib 完成此任务。

vi)使用适当形式的 CREATE FUNCTION SQL 语句向 DB2 数据库注册 UDF。

--语法

CREATE FUNCTION center(INT, DOUBLE)
    RETURNS DOUBLE
    EXTERNAL NAME '/home/db2inst1/myfuncs/double'
    LANGUAGE C
    PARAMETER STYLE SQL
    DETERMINISTIC
    NO SQL

4）外部表函数：与外部标量函数一样，外部表函数是用高级编程语言编写的。外部标量函数返回一个标量值，而外部表函数在每次调用时返回一个数据集。外部表函数的强大之处在于，它们能够让几乎任何数据源看起来像是 DB2 基表。

--语法

CREATE FUNCTION empdata (VARCHAR(30), VARCHAR(255))
    RETURNS TABLE (empid INT, lname CHAR(20), fname CHAR(20))
    EXTERNAL NAME '/home/db2inst1/myfuncs/EMPDATA'
    LANGUAGE C
    PARAMETER STYLE SQL
    DETERMINISTIC
    NOT FENCED
    NO SQL
    NO EXTERNAL ACTION

5）OLE DB 外部表函数：Microsoft OLE DB 是一组应用程序编程接口 (API)，用于访问各种数据源。数据源由数据本身、相关联的数据库管理系统 (DBMS)、运行 DBMS 的平台以及用来访问此平台的网络组成。OLE DB 可以为 OLE Component Object Model (COM) 环境中的所有数据源类型提供访问。

--语法

CREATE FUNCTION [FunctionName] ( <<[ParameterName]> [InputDataType] ,...> )
    RETURNS TABLE ( [ColumnName] [ColumnDataType] ,... )
    <SPECIFIC [SpecificName]>
    EXTERNAL <NAME [ExternalName]>
    LANGUAGE OLEDB
    <DETERMINISTIC | NOT DETERMINISTIC>
    <RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT>
    <EXTERNAL ACTION | NO EXTERNAL ACTION>
    <CARDINALITY [NumRows]>
--示例

CREATE FUNCTION orders()
    RETURNS TABLE (orderid      INTEGER,
                   customerid   CHAR(5),
                   employeeid   INTEGER,
                   orderdate    TIMESTAMP,
                   requiredate TIMESTAMP,
                   shipdate     TIMESTAMP,
                   shipcharges DECIMAL(19,4))
    LANGUAGE OLEDB
    EXTERNAL NAME '!orders!Provider=Microsoft.Jet.OLEDB.3.51;
        Data Source=c:\sqllib\samples\oledb\nwind.mdb'

3、SQL函数的结构

1）组成部分

i)函数名

ii)一系列参数声明（如果有参数的话）

iii)函数返回值的声明

iv)一个或多个函数选项

v)函数体

2）结构示意图

SQL 函数的结构

3）SQL函数中可以使用的SQL语句

SQL 语句CONTAINS SQLREADS SQL DATAMODIFIES SQL DATACALLYesYesYesDELETENoNoYesFORYesYesYesGET DIAGNOSTICSYesYesYesIFYesYesYesINSERTNoNoYesITERATEYesYesYesLEAVEYesYesYesMERGENoNoYesSELECTNoYesYesSET variableYesYesYesSIGNALYesYesYesVALUESNoYesYesWHILEYesYesYes