CREATE FUNCTION语句（SQL表函数）

CREATE FUNCTION（SQL表）语句在当前服务器上创建一个SQL表函数。 该函数返回一组行。

CREATE FUNCTION（SQL表）调用

只有在动态规则运行行为生效时，才能动态准备此语句。 更多信息，请参阅授权ID和动态SQL。

CREATE FUNCTION（SQL表）授权

以下定义的特权集必须至少包含以下特权或权限之一：

  • 模式中的CREATEIN特权
  • SYSADM 权限
  • SYSCTRL 权限
  • 系统 DBADM
  • 更改开始安装 SYSOPR 权限（当前进程的 SQLID 设置为 SYSINSTL 时）更改结束

与模式名称匹配的授权ID隐式拥有该模式的CREATEIN权限。

更改开始如果用于创建函数的授权ID具有安装SYSADM权限或安装SYSOPR权限，并且当前SQLID设置为SYSINSTL，则该函数将被识别为系统定义的函数。更改结束

如果引用了特定类型（例如作为参数或SQL变量的数据类型），则权限集还必须至少包含以下内容之一：

  • 独特类型的所有权
  • 独特类型的“使用”特权
  • SYSADM 权限
  • SYSDBADM权限

如果函数使用表作为参数，则权限集还必须至少包含以下内容之一：

  • 对表的所有权
  • 表中的 SELECT 权限
  • SYSADM 权限

如果指定了 SECURED 选项，则至少需要以下一项附加权限：

  • SECADM 权限
  • CREATE_SECURE_OBJECT权限

权限设置：

如果语句嵌入到应用程序中，权限集就是计划或软件包所有者所拥有的权限集。 如果所有者是一个角色，则隐式模式名称的匹配不适用，并且该角色必须包含前面列出的权限或授权之一。

如果语句动态生成，且未在指定了 ROLE AS OBJECT OWNER 子句的可信环境中运行，则权限集是指进程的 SQL 授权 ID 所拥有的权限集。 如果架构名称与进程的SQL授权ID不同，则必须满足以下条件之一：

  • 特权集包括系统管理员权限
  • 特权集包括SYSCTRL权限
  • 该进程的SQL授权ID在模式上具有CREATEIN权限

CREATE FUNCTION（SQL表）的语法

阅读语法图跳过可视化语法图CREATE FUNCTION功能名称( ,parameter-declaration )功能定义WRAPPED模糊的声明文本

参数声明：

阅读语法图跳过可视化语法图parameter-name参数类型

参数类型：

阅读语法图跳过可视化语法图data-type1TABLE LIKEtable-name视图名称AS LOCATOR

data-type1, data-type2:

阅读语法图跳过可视化语法图built-in-type独特类型名称

内置型：

阅读语法图跳过可视化语法图SMALLINTINTEGERINTBIGINTDECIMALDECNUMERIC(5,0)( 整数整数)FLOAT(53)( 整数)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)( 整数)CHARACTERCHARVARYINGVARCHAR( 整数)CCSIDASCIIEBCDICUNICODEFORSBCSMIXEDBITDATACHARACTERCHARLARGE OBJECTCLOB(1M)( 整数KMG)CCSIDASCIIEBCDICUNICODEFORSBCSMIXEDDATAGRAPHIC(1)( 整数)VARGRAPHIC(整数)DBCLOB(1M)( 整数KMG)CCSIDASCIIEBCDICUNICODEBINARY(1)( 整数)BINARY VARYINGVARBINARY( 整数)BINARY LARGE OBJECTBLOB(1M)( 整数KMG)DATETIMETIMESTAMP(6)(整数)WITHOUT TIME ZONEWITH TIME ZONEROWIDXML

功能定义：

阅读语法图跳过可视化语法图 RETURNS TABLE ( ,列名data-type2 )option-list SQL 常规语句主体

选项列表：

阅读语法图跳过可视化语法图1LANGUAGE SQLSPECIFIC专有名词NOT DETERMINISTICDETERMINISTICEXTERNAL ACTIONNO EXTERNAL ACTIONREADS SQL DATACONTAINS SQLCALLED ON NULL INPUTINHERIT SPECIAL REGISTERSSTATIC DISPATCHCARDINALITY整数PARAMETER CCSIDASCIIEBCDICUNICODENOT SECUREDSECURED
注意：
  • 1 选项列表中的选项可以任意顺序排列。 但是，同一条款不能重复使用。

SQL-routine-body：

阅读语法图跳过可视化语法图RETURN 语句BEGINATOMICRETURN 语句END

CREATE FUNCTION（SQL表）描述

功能名称
命名用户定义的函数。 名称由模式名隐式或显式限定。 名称、模式名称、参数数量和每个参数的数据类型（不考虑数据类型的任何长度、精度、比例、子类型或编码方案属性）的组合不能识别当前服务器上存在的用户定义的函数。 更多信息，请参阅 CREATE FUNCTION语句（概述 ）中的选择架构和函数名称以及确定架构中函数的唯一性。
(parameter-declaration,...)
确定函数的输入参数数量，并指定每个参数的名称和数据类型。 函数的全部参数均为输入参数，且可为空。 函数期望接收的每个参数列表中必须有一个条目。
参数名称
指定输入参数的名称。 参数列表中的每个名称都不得与其他名称相同。
data-type1
指定输入参数的数据类型。 数据类型可以是内置数据类型，也可以是不同的类型。
内置式
参数的数据类型是内置数据类型。 有关内置数据类型的说明，请参阅 CREATE TABLE 语句中的数据类型和 built-in-type 的说明。

然而，对于此语句，不同长度的字符串数据类型具有不同的最大长度，这与CREATE TABLE语句不同。 参数（和SQL变量）的最大长度如下：

  • VARCHAR或VARBINARY：32704
  • vargraphic: 16352

对于字符或图形数据类型的参数，PARAMETER CCSID子句或CCSID子句指示参数的编码方案。 如果您没有指定CCSID条款，则编码方案为安装面板DSNTIPF上DEF ENCODING SCHEME字段的值。

虽然字符数据类型的输入参数具有隐式或显式指定的子类型（BIT、SBCS或MIXED），但实际传入输入参数的值可以具有任何子类型。 因此，在调用函数时，输入数据可能会转换为参数的子类型。 在使用ASCII或EBCDIC时，如果将实际包含DBCS字符的混合数据用作以SBCS子类型声明的输入参数的值，则会发生错误。

distinct-type-name
参数的数据类型是不同的类型。 参数的任何长度、精度、比例、子类型或编码方案属性都是不同类型的源类型的属性。
TABLE LIKE 表名 AS LOCATOR
指定参数为转换表。 然而，当函数被调用时，转换表中的实际值不会传递给函数。 而是传递一个单一的值。 该值是表的定位器，函数使用它来访问转换表的列。 已识别的表可以包含XML列，但函数不能引用这些XML列。

带表参数的函数只能从触发器的触发操作中调用。

RETURNS TABLE
指定函数的输出是一个表格。 SQL表函数中的RETURN语句必须返回一个表结果。 RETURNS TABLE关键字后面的括号用于分隔输出表列的名称和数据类型对。 输出表的所有列都是空值。
列名
指定列的名称。 该名称不能重复，且在函数的 RETURNS TABLE 子句中必须是唯一的。
data-type2
指定输出表列的数据类型和属性。

对于SQL表函数，函数的结果表可能包含多种编码方案——类似于视图定义可以包含的内容。

LANGUAGE SQL
指定函数仅以SQL编写。
SPECIFIC 专有名词
指定函数的唯一名称。
非确定性或 DETERMINISTIC
指定每次使用相同的输入自变量调用函数时函数是否返回相同的结果。 Db2 不验证功能程序是否符合非确定性或确定性的规范。
NOT DETERMINISTIC

指定即使数据库中的引用数据未发生变化，函数每次调用时，即使输入参数相同，函数也可能不会返回相同的结果表。 函数取决于一些可能影响结果的状态值。 表函数不确定性的一个例子是引用特殊寄存器、其他不确定的函数或影响表函数结果表的序列。

更改开始当 MATERIALIZE_NODET_SQLTUDF 子系统参数设置为 "是 "时， Db2 将禁止合并定义为 "非确定 "的 SQL 表函数。 更多信息，请参见宏 DSN6SPRM 中的 MATERIALIZE_NODET_SQLTUDF。更改结束

更改开始使用该子句或关键字可能会使依赖于目标对象的软件包或通过级联效应依赖于相关对象的软件包失效。 请参阅使软件包失效的更改。更改结束

DETERMINISTIC

指定当数据库中的引用数据未发生变化时，每次使用相同的输入参数调用该函数时，该函数始终返回相同的结果表。

更改开始Db2 可以合并使用该选项定义的 SQL 表函数。更改结束

如果适用，请指定“确定性”，以防止为引用此函数的 SQL 语句选择非最佳访问路径。 更多信息，请参阅合并与实体化的性能对比。

更改开始使用该子句或关键字可能会使依赖于目标对象的软件包或通过级联效应依赖于相关对象的软件包失效。 请参阅使软件包失效的更改。更改结束

外部行为或 NO EXTERNAL ACTION
指定函数是否包含外部操作。 Db2 不验证功能程序是否符合外部动作或无外部动作的规范。
外部行为
该函数执行一些外部操作（在函数程序范围之外）。 因此，在调用后续函数时，必须调用该函数。 如果函数调用另一个具有外部动作的函数，则必须指定外部动作。 默认情况下， 外部操作处于启用状态。
NO EXTERNAL ACTION
该功能不会执行任何外部操作。 在连续调用每个函数时无需调用它。 定义为“无外部操作”的函数可能比定义为“有外部操作”的函数性能更好，因为该函数可能不会在每次连续调用时都调用。

虽然全局变量的范围超出了常规，但在指定了“无外部操作”时，可以在常规主体中设置全局变量。

读取 SQL 数据或 CONTAINS SQL
指定此例程可以执行或调用的SQL语句和嵌套例程的分类。 数据库管理员负责验证函数发出的SQL语句以及本地调用的所有例程是否符合规范；当调用嵌套的远程例程时，不会进行验证。 有关每条语句的分类，请参阅例程的SQL语句数据访问分类。
读取 SQL 数据
指定函数可以执行带有数据访问指示的语句，如READS SQL DATA或CONTAINS SQL。 该功能无法执行修改数据的SQL语句。

读取SQL数据是默认设置。

CONTAINS SQL
指定该函数只能执行数据访问指示为CONTAINS SQL的SQL语句。 该函数不能执行读取或修改数据的语句。
调用空输入
指定无论输入参数是否为空，函数都会调用，使函数负责测试空参数值。 根据其逻辑，该函数可能会返回一个空表。

默认值为“调用空输入”。

继承特殊登记
指定特殊寄存器的现有值在进入函数时继承。 默认设置是“INHERIT SPECIAL REGISTERS”。
静态调度
在函数解析时， Db2 会根据函数参数的静态（或声明的）类型来选择函数。 默认值为静态调度。
CARDINALITY 整数
指定函数返回的预计行数。 该数字用于优化目的。 整数值必须在0到2147483647之间。

如果您没有指定 CARDINALITY，则 Db2 将采用有限值。 有限值是 Db2 为RUNSTATS实用程序未收集统计数据的表所设定的值。

如果一个函数具有无限基数（函数永远不会返回表尾条件，而总是返回一行），那么需要表尾条件才能正常运行的查询将需要中断。

PARAMETER CCSID
指定字符和图形字符串参数的编码方案为ASCII、EBCDIC或UNICODE。 默认编码方案是在参数列表的 CCSID 子句或 RETURNS 子句中指定的值，或在安装面板 DSNTIPF 上的 DEF ENCODING SCHEME 字段中指定的值。 该条款为指定字符和图形字符串参数的编码方案提供了便利。 如果除了此 PARAMETER CCSID 子句外，还为单个参数指定了单独的 CCSID 子句，则所有 CCSID 子句中指定的值必须与此子句中指定的值相同。 该条款还规定了用于系统生成的常规参数（如消息标记和DBINFO）的编码方案。
NOT SECURED 或者 SECURED
指定该功能在行访问控制和列访问控制中是否安全。 “安全”或“不安全”选项适用于该功能的所有未来版本。
NOT SECURED
指定该功能在行访问控制和列访问控制方面不安全。

NOT SECURED 是默认值。

当函数被调用时，如果表使用活动列访问控制，则函数的参数不得引用已启用列掩码的列。

SECURED
指定该功能在行访问控制和列访问控制中是安全的。

当在行权限或列掩码中引用函数时，函数必须安全。

SQL-例程-主体
返回语句
指定函数的返回值。 SQL表函数必须指定RETURN语句。
ATOMIC
ATOMIC表示RETURN语句中存在未处理的异常情况，导致语句回滚。
更改开始WRAPPED 混淆语句文本更改结束
更改开始FL 500指定函数的编码定义。 CREATE FUNCTION 语句可以使用 WRAP 标量函数进行编码。

WRAPPED 不能在静态 CREATE 语句中指定。

更改结束

CREATE FUNCTION（SQL表）的注释

所有类型的用户自定义功能注意事项：
有关适用于所有类型用户定义函数的注意事项，请参阅 CREATE FUNCTION语句（概述 ）。
自引用功能：
SQL函数的主体（即CREATE FUNCTION语句主体中的RETURN语句中的表达式或NULL）不能包含对自身或调用它的另一个函数的递归调用，因为这样的函数不存在，无法引用。
从属对象：
SQL 例程依赖于例程主体中引用的对象。
更改开始混淆视听的发言：更改结束
更改开始CREATE FUNCTION 语句可以混淆形式执行。 在一段晦涩难懂的语句中，只有函数名称、参数和WRAPPED关键字是可读的。 该语句的其余部分将以不可读方式进行编码，但支持模糊语句的数据库服务器可以解码该部分。 WRAP标量函数生成混淆语句。 根据混淆语句创建函数时指定的任何调试选项都将被忽略。更改结束
对象名称解析：
Db2 根据非限定对象名称解析中的规则和对象类型解析函数体内的对象名称。 创建函数时，名称解析即发生。
参考日期和时间特殊登记簿：
如果SQL函数包含对日期或时间特殊寄存器的多次引用，则所有引用将返回相同的值。 此外，该值与调用函数的语句中特殊寄存器的取回值相同。
使用字段程序定义的列注意事项：
SQL表函数的主体不能引用由字段过程定义的列，SQL表函数的RETURNS子句也不能引用由字段过程定义的列。 SQL表函数不能使用从字段过程定义的列派生的表达式来调用。
涉及待定义变更的限制：
SQL表函数的主体不能引用定义有更改的表。
其他语法和同义词：
为了与 Db2 或 Db2 系列中其他产品的早期版本兼容， Db2 支持以下替代语法：
  • VARIANT作为“非确定性”的同义词
  • NOT VARIANT 并非“确定性”的同义词
  • NULL CALL作为CALLED ON NULL INPUT的同义词

CREATE FUNCTION（SQL表）示例

示例 1

定义一个表函数JTABLE，用于返回一个包含3列的表：

CREATE FUNCTION JTABLE (COLD_VALUE CHAR(9), T2_FLAG CHAR(1))
	RETURNS TABLE (COLA INT, COLB INT, COLC INT)
	LANGUAGE SQL
	SPECIFIC DEPTINFO
	NOT DETERMINISTIC
	READS SQL DATA
	RETURN
		SELECT A.COLA, B.COLB, B.COLC
			FROM TABLE1 AS A
				LEFT OUTER JOIN
				TABLE2 AS B
			ON A.COL1 = B.COL1 AND T2_FLAG = 'Y'
		WHERE A.COLD = COLD_VALUE;
示例 2

定义一个表函数，用于返回指定部门编号的员工。 该函数仅返回指定部门的员工：

CREATE FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
	RETURNS TABLE (EMPNO CHAR(6), LASTNAME VARCHAR(15), FIRSTNAME VARCHAR(12))
	LANGUAGE SQL
	READS SQL DATA
	NO EXTERNAL ACTION
	DETERMINISTIC
	RETURN
		SELECT EMPNO, LASTNAME, FIRSTNME
			FROM YEMP
		WHERE YEMP.WORKDEPT = DEPTEMPLOYEES.DEPTNO;