自定义函数是数据库里的一种PL对象，简称UDF。

YashanDB支持直接创建将PL语言按函数进行组织的自定义函数，也支持在PL中调用外部Java方法或外部C函数，实现数据库中的外置自定义函数功能。

自定义函数不适用于分布式部署。

用户自定义函数

自定义函数是PL语言按函数进行组织的数据库对象形式，类似Pascal（结构化编程语言）中的函数。

创建自定义函数

使用CREATE FUNCTION语句创建自定义函数，其语法定义为：

create function::=

语法图

代码

syntax

function_head_clause::=

语法图

代码

syntax

invoker_rights_clause::=

语法图

代码

syntax

function_body_clause::=

语法图

代码

syntax

or replace

当要创建的自定义函数已经存在时，将其进行重建。

editionable | noneditionable

用于语法兼容，无实际含义。

function_name

自定义函数的名称，不可省略，且需符合YashanDB的对象命名规范。

argument_define

自定义函数的参数（形参），可省略，此时function_name()=function_name。最多可以指定4095个参数。

更多详情请查阅PL参数描述。

return_datatype

自定义函数的返回值类型，此处为隐式形参，因此不可以为其指定长度、精度等属性。

pipelined

用于标识管道表函数（简称管道函数）。

管道函数返回集合类型，在管道函数体中通过PIPE ROW语句产生一行数据，用户可以使用SELECT语句查询管道函数立即获取该行，无需等待整体结果集生成，能够有效缩短查询的响应时间。

使用管道函数需遵循如下规则：

• 管道函数允许与内置函数同名。
• 管道函数仅支持作为查询数据源使用，且一条查询语句中只能使用一个管道函数。
• 管道函数单行数据最大长度始终以行长度规格中较小值为准（即单行数据最大长度为64512Bytes）。
• 管道函数参数只能为入参，不能为出参、出入参。
• 管道函数的RETURN语句不能指定返回值。
• 若管道函数不在自治事务中则不允许有DML/DDL语句。
• 管道函数不能被过程体调用，不能递归调用自己。
• 管道函数只能返回集合类型（关联数组除外），PIPE ROW写入的行数据必须是SQL类型或全局UDT类型。
• 管道函数返回的集合元素类型不能为RECORD类型。
• 目前管道函数参数暂无法使用游标类型以及游标表达式。
• 目前管道函数暂无法输出DBMS_OUTPUT信息。
• 在管道函数中修改UDP变量，其他线程不可见。
• 暂不支持UDT方法定义为管道函数。
• 管道函数在并行线程中连续执行，如果函数中有DML语句且查询条件限定获取行数少于管道产生行数，则DML对象受影响行数不固定。
• 暂不支持PARALLEL_ENABLE选项。
• 暂不支持DETERMINISTRIC关键字。

使用CREATE FUNCTION语句在函数头指定PIPELINED创建的管道函数为独立函数，还可以在创建自定义高级包时在函数声明与定义中指定PIPELINED创建包内管道函数。

invoker_rights_clause

详情请查阅invoker_rights_clause相关描述。

variable_declare

声明PL过程体的全局变量，可省略。

更多详情请查阅PL变量声明。

plsql_statements

定义过程体中的执行语句，为PL语句中的一项或多项。

过程体中的每个执行路径（流程控制语句的分支，异常处理单元的分支）里都必须包含至少一个RETURN Statement。

示例（单机、共享集群部署）

-- 普通函数
CREATE OR REPLACE FUNCTION ya_func(i INT) RETURN VARCHAR
IS
BEGIN
CASE i
WHEN 1 THEN
RETURN 'hello';
WHEN 2 THEN
RETURN 'world';
END CASE;
END ya_func;
/

-- 管道函数
CREATE OR REPLACE TYPE ya_pipe_func_typ1 IS TABLE OF INT;
/

CREATE OR REPLACE FUNCTION ya_pipe_func(p1 INT) RETURN ya_pipe_func_typ1 PIPELINED IS
BEGIN
    FOR i IN 1..p1 LOOP
      pipe row(i);
    END LOOP;
    RETURN;
END;
/

使用自定义函数

在sql语句中调用

与内置函数的调用方法相同，作为SQL表达式使用，例如：

SELECT function_name(arguments) FROM dual；

arguments为与argument_define一一对应的实参，且argument_define不能为IN OUT或OUT形参。

示例（单机、共享集群部署）

SELECT ya_func(1)||' '||ya_func(2) res FROM dual;
RES                                             
---------------------------------------------------------------- 
hello world 

在过程体中调用

与内置函数的调用方法相同，作为表达式在PL语句中使用，或者用于给变量赋值，例如：

variable_name := procedure_name(arguments);

arguments为与argument_define一一对应的实参。

示例（单机、共享集群部署）

DECLARE
  a VARCHAR(10);
  b VARCHAR(10);
BEGIN
  a := ya_func(1);
  b := ya_func(2);
  DBMS_OUTPUT.PUT_LINE(a||' '||b);
END;
/

--result
hello world

查询管道函数

通过SELECT语句查询管道函数，TABLE关键字可选。

示例（单机、共享集群部署）

SELECT * FROM TABLE(ya_pipe_func(5));
COLUMN_VALUE 
------------ 
           1
           2
           3
           4
           5

SELECT * FROM TABLE(ya_pkg1.ya_pkg_pipe_func(6));
COLUMN_VALUE 
------------ 
           1
           2
           3
           4
           5
           6

-- TABLE关键字可省略
SELECT * FROM ya_pipe_func(5);
COLUMN_VALUE 
------------ 
           1
           2
           3
           4
           5

SELECT * FROM ya_pkg1.ya_pkg_pipe_func(6);
COLUMN_VALUE 
------------ 
           1
           2
           3
           4
           5
           6

管道函数结果集投影列只有一列，列名为COLUMN_VALUE。如果投影列类型为UDT_OBJECT，则结果集投影列自动展开为对象类型的属性列。

示例（单机、共享集群部署）

CREATE OR REPLACE TYPE ya_pipe_type_obj1 IS OBJECT(C1 INT, C2 VARCHAR(100));
/

CREATE OR REPLACE TYPE YA_PIPE_TYPE2 IS TABLE OF ya_pipe_type_obj1;
/

CREATE OR REPLACE FUNCTION ya_pipe_fun2(p1 INT) RETURN YA_PIPE_TYPE2 PIPELINED IS
    REC1 ya_pipe_type_obj1 := ya_pipe_type_obj1(NULL, NULL);
BEGIN
    FOR i IN 1..p1 LOOP
        rec1.c1 := i;
        rec1.c2 := CONCAT('pipe row ' , i);
        pipe row(rec1);
    END LOOP;
    RETURN;
END;
/

SELECT * FROM TABLE(ya_pipe_fun2(3));
          C1 C2                                                               
------------ ---------------------------------------------------------------- 
           1 pipe row 1                                                      
           2 pipe row 2                                                      
           3 pipe row 3                                                      

查询语句触发管道函数执行，当查询条件已满足，不再需要后续行数据时，PIPE ROW语句会抛出NO_DATA_NEEDED异常。

管理自定义函数

删除自定义函数

使用DROP FUNCTION语句删除自定义函数。

修改自定义函数

使用ALTER FUNCTION语句修改自定义函数。

外置自定义函数

外置自定义函数是自定义函数的特殊形式，包含外置Java语言和外置C语言，简称外置UDF或Ext-UDF。

环境要求（java）

使用Java语言的外置UDF要求数据库服务器已安装JDK（1.8及其以上版本），并配置如下环境变量：

# 如下路径需更换为实际的jdk安装路径
$ export LD_LIBRARY_PATH=/etc/jdk-18.0.2/lib/server:$LD_LIBRARY_PATH

创建自定义库

使用外置UDF首先需要将外置UDF所需要的自定义库创建到数据库中，可以通过[CREATE LIBRARY]创建自定义库。

• Java语言的外置UDF：YashanDB支持创建包含自定义Java函数的.class文件或jar包的自定义库。

• C语言的外置UDF：YashanDB支持创建包含C函数的.dll（Windows）和.so（Linux）动态库文件。

创建自定义库时不检查库文件是否存在，在执行外置UDF时才检查并加载库文件。当class文件、jar包、so文件或dll文件内容发生变化时，需重新生成自定义库。

示例（单机、共享集群部署）

-- 创建Java语言的自定义库
CREATE OR REPLACE LIBRARY ya_java_lib IS
'/home/yasdb/example/UDFexample.class';
/

UDFexample.class对应UDFexample.java文件内容如下：

package example;

public class UDFexample {
    public static String execJdbcexample(int ctrls) {
        switch (ctrls) {
        case 1:return "Hello";
        case 2:return "World";
        default:return "!";
        }
    }

    public static void main(String[] args) {
       String a = execJdbcexample(1);
    }
}

示例（单机、共享集群部署）

-- 创建C语言的自定义库
CREATE OR REPLACE LIBRARY ya_c_lib IS
'/home/yasdb/example/libUDFexample.so';
/

libUDFexample.so对应的UDFexample.c文件内容如下：

#include "string.h"
#include "yacli.h"

YacResult udfExample(YacHandle hProc)
{
    YacInt32  id;
    YacChar   buf[1024];

    YAC_CALL(yepGetInt32(hProc, 0, &id, NULL));
    (YacVoid)snprintf(buf, 1024, "Hello World, id: %d", id);
    YAC_CALL(yepReturnString(hProc, buf));

    return YAC_SUCCESS;
}

定义java语言的外置udf

通过创建一个函数，实现对Java语言的外置UDF的定义，语法如下：

语法图

代码

syntax

其中，为实现向外置UDF传递参数和获得返回值，对函数定义的参数和返回值应与java_method里的参数和返回值一致，此处一致的具体含义为：

• 参数个数及顺序一致。

• 数据类型兼容，即显式地满足YashanDB和java数据类型对应关系，或者通过隐式转换后满足对应关系，对应关系见下面描述。

call_spec::=

语法图

代码

syntax

language

指定外置UDF所用的语言，这里为JAVA。

name

指定需引用的java函数的函数签名，java_package.java_class部分长度需小于128字节，其格式为：

java_package.java_class.java_method(argu1_datatype,argu2_datatype...) [return return_datatype]

此处引用的java函数必须为static的java方法，且该方法的参数和返回类型必须在如下列示的java数据类型范围内：

• bool：对应YashanDB的BOOLEAN
• byte：对应YashanDB的TINYINT
• char：对应YashanDB的INT（取值范围[0,65535]，即0-16位无符号整数）
• short：对应YashanDB的SMALLINT
• int：对应YashanDB的INT
• long：对应YashanDB的BIGINT
• float：对应YashanDB的FLOAT
• double：对应YashanDB的DOUBLE
• string：对应YashanDB的VARCHAR

library_name

指定已经创建的自定义库名称。

library_path

方式一：创建function时，指定library为已创建成功的library名称。

方式二：创建function时，指定library为通过执行DBMS_STANDARD.LOADJAVA方法加载的自定义库路径（此方式不建议使用）。

示例（单机、共享集群部署）

-- 方式一
CREATE OR REPLACE FUNCTION udf_func_java(argu INT) RETURN VARCHAR IS
LANGUAGE java
NAME 'example.UDFexample.execJdbcexample(int) return string'
LIBRARY ya_java_lib;
/

-- 方式二
call DBMS_STANDARD.LOADJAVA('/home/yasdb/example/UDFexample.class');
CREATE OR REPLACE FUNCTION udf_func_java(argu INT) RETURN VARCHAR IS
LANGUAGE java
NAME 'example.UDFexample.execJdbcexample(int) return string'
LIBRARY '/home/yasdb/example/UDFexample.class';
/

定义c语言的外置udf

通过创建一个函数，实现对C语言的外置UDF的定义，语法如下：

语法图

代码

syntax

call_spec::=

语法图

代码

syntax

language

指定外置UDF所用的语言，这里为C。

external

指定为C语言的外置UDF（建议使用LANGUAGE指定外置UDF所使用的语言）。

name

通过c_string_literal_name指定需引用的C函数的函数名，默认转为全大写，如需保留大小写，请使用双引号包围，如果省略NAME，默认使用外置UDF的名称。

c_string_literal_name需要为合法的标识符，其最大长度为64字节。

library_name

指定已经创建的自定义库名称。

示例（单机、共享集群部署）

CREATE OR REPLACE FUNCTION udf_func_c(argu INT) RETURN VARCHAR IS
LANGUAGE C
NAME "udfExample"
LIBRARY ya_c_lib;
/

编写c语言外置udf指导

C语言外置UDF是调用C语言编写的函数，将C语言文件编译为动态库文件后，在创建为自定义库被数据库调用。

C函数有唯一参数hProc，不可修改其值。通过YashanDB提供的一系列yepGet、yepOutPut、yepReturn接口实现获取入参、设置出参、设置返回值功能。

使用yepGet接口时，需根据入参类型调用相同类型或可以隐式转换类型的yepGet接口。

使用yepOutPut接口时，需根据入参类型调用相同类型的yepOutPut接口。

使用yepReturn接口时，需根据返回值类型调用相同类型或者可以隐式转换类型的yepReturn接口，且自定义函数必须调用yepReturn接口设置返回值。

接口参数id为函数定义的参数序号，起始值为0。

接口参数lenOrInd用于获取入参长度或是否为NULL值，若其值返回YAC_NULL_DATA，则说明入参为NULL，否则返回入参的字节长度。lenOrInd可为NULL。

YashanDB提供给编写C语言动态库的接口如下：

接口名称	接口函数声明	接口使用说明
yepGetCharsetId	YacResult yepGetCharsetId(YacHandle hProc, YacUint16* charsetId)	获取数据库的字符集编号，赋值给charsetId。
yepGetBool	YacResult yepGetBool(YacHandle hProc, YacInt32 id, YacBool* v, YacInt32* lenOrInd)	获取第id个BOOLEAN类型入参的值，赋值给v。
yepGetInt8	YacResult yepGetInt8(YacHandle hProc, YacInt32 id, YacInt8* v, YacInt32* lenOrInd)	获取第id个TINYINT类型入参的值，赋值给v。
yepGetInt16	YacResult yepGetInt16(YacHandle hProc, YacInt32 id, YacInt16* v, YacInt32* lenOrInd)	获取第id个SMALLINT类型入参的值，赋值给v。
yepGetInt32	YacResult yepGetInt32(YacHandle hProc, YacInt32 id, YacInt32* v, YacInt32* lenOrInd)	获取第id个INT类型入参的值，赋值给v。
yepGetInt64	YacResult yepGetInt64(YacHandle hProc, YacInt32 id, YacInt64* v, YacInt32* lenOrInd)	获取第id个BIGINT类型入参的值，赋值给v。
yepGetFloat	YacResult yepGetFloat(YacHandle hProc, YacInt32 id, YacFloat* v, YacInt32* lenOrInd)	获取第id个FLOAT类型入参的值，赋值给v。
yepGetDouble	YacResult yepGetDouble(YacHandle hProc, YacInt32 id, YacDouble* v, YacInt32* lenOrInd)	获取第id个DOUBLE类型入参的值，赋值给v。
yepGetNumber	YacResult yepGetNumber(YacHandle hProc, YacInt32 id, YacNumber* v, YacInt32* lenOrInd)	获取第id个NUMBER类型入参的值，赋值给v。
yepGetDate	YacResult yepGetDate(YacHandle hProc, YacInt32 id, YacDate* v, YacInt32* lenOrInd)	获取第id个DATE类型入参的值，赋值给v。
yepGetTimestamp	YacResult yepGetTimestamp(YacHandle hProc, YacInt32 id, YacTimestamp* v, YacInt32* lenOrInd)	获取第id个TIMESTAMP类型入参的值，赋值给v。
yepGetYMInterval	YacResult yepGetYMInterval(YacHandle hProc, YacInt32 id, YacYMInterval* v, YacInt32* lenOrInd)	获取第id个INTERVAL YEAR TO MONTH类型入参的值，赋值给v。
yepGetDSInterval	YacResult yepGetDSInterval(YacHandle hProc, YacInt32 id, YacDSInterval* v, YacInt32* lenOrInd)	获取第id个INTERVAL DAY TO SECOND类型入参的值，赋值给v。
yepGetString	YacResult yepGetString(YacHandle hProc, YacInt32 id, YacChar* str, YacUint32 bufSize, YacInt32* lenOrInd)	获取第id个字符串类型（CHAR、VARCHAR）入参的值，需要提前分配内存，传入指针str和bufSize。
yepGetBytes	YacResult yepGetBytes(YacHandle hProc, YacInt32 id, YacUint8* bytes, YacUint32 bufSize, YacInt32* lenOrInd)	获取第id个RAW类型入参的值，需要提前分配内存，传入指针bytes和bufSize。
yepOutputNull	YacResult yepOutputNull(YacHandle hProc, YacInt32 id)	将第id个出参设置为NULL。
yepOutputBool	YacResult yepOutputBool(YacHandle hProc, YacInt32 id, YacBool v)	将第id个BOOLEAN类型出参的值设置为v。
yepOutputInt8	YacResult yepOutputInt8(YacHandle hProc, YacInt32 id, YacInt8 v)	将第id个TINYINT类型出参的值设置为v。
yepOutputInt16	YacResult yepOutputInt16(YacHandle hProc, YacInt32 id, YacInt16 v)	将第id个SMALLINT类型出参的值设置为v。
yepOutputInt32	YacResult yepOutputInt32(YacHandle hProc, YacInt32 id, YacInt32 v)	将第id个INT类型出参的值设置为v。
yepOutputInt64	YacResult yepOutputInt64(YacHandle hProc, YacInt32 id, YacInt64 v)	将第id个BIGINT类型出参的值设置为v。
yepOutputFloat	YacResult yepOutputFloat(YacHandle hProc, YacInt32 id, YacFloat v)	将第id个FLOAT类型出参的值设置为v。
yepOutputDouble	YacResult yepOutputDouble(YacHandle hProc, YacInt32 id, YacDouble v)	将第id个DOUBLE类型出参的值设置为v。
yepOutputNumber	YacResult yepOutputNumber(YacHandle hProc, YacInt32 id, YacNumber* v)	将第id个NUMBER类型出参的值设置为v。
yepOutputDate	YacResult yepOutputDate(YacHandle hProc, YacInt32 id, YacDate v)	将第id个DATE类型出参的值设置为v。
yepOutputTimestamp	YacResult yepOutputTimestamp(YacHandle hProc, YacInt32 id, YacTimestamp* v)	将第id个TIMESTAMP类型出参的值设置为v。
yepOutputYMInterval	YacResult yepOutputYMInterval(YacHandle hProc, YacInt32 id, YacYMInterval v)	将第id个INTERVAL YEAR TO MONTH类型出参的值设置为v。
yepOutputDSInterval	YacResult yepOutputDSInterval(YacHandle hProc, YacInt32 id, YacDSInterval v)	将第id个INTERVAL DAY TO SECOND类型出参的值设置为v。
yepOutputString	YacResult yepOutputString(YacHandle hProc, YacInt32 id, YacChar* str)	将第id个字符串类型（CHAR、VARCHAR）类型出参的值设置为str指向的字符串。
yepOutputBytes	YacResult yepOutputBytes(YacHandle hProc, YacInt32 id, YacUint8* bytes, YacUint32 size)	将第id个RAW类型出参的值设置为bytes指向的字节，大小为size。
yepReturnNull	#define yepReturnNull(hProc) yepOutputNull(hProc, YEP_RETURN)	将返回值设置为NULL。
yepReturnBool	#define yepReturnBool(hProc, value) yepOutputBool(hProc, YEP_RETURN, value)	将返回值设置为BOOLEAN类型，值为value。
yepReturnInt8	#define yepReturnInt8(hProc, value) yepOutputInt8(hProc, YEP_RETURN, value)	将返回值设置为TINYINT类型，值为value。
yepReturnInt16	#define yepReturnInt16(hProc, value) yepOutputInt16(hProc, YEP_RETURN, value)	将返回值设置为SMALLINT类型，值为value。
yepReturnInt32	#define yepReturnInt32(hProc, value) yepOutputInt32(hProc, YEP_RETURN, value)	将返回值设置为INT类型，值为value。
yepReturnInt64	#define yepReturnInt64(hProc, value) yepOutputInt64(hProc, YEP_RETURN, value)	将返回值设置为BIGINT类型，值为value。
yepReturnFloat	#define yepReturnFloat(hProc, value) yepOutputFloat(hProc, YEP_RETURN, value)	将返回值设置为FLOAT类型，值为value。
yepReturnDouble	#define yepReturnDouble(hProc, value) yepOutputDouble(hProc, YEP_RETURN, value)	将返回值设置为DOUBLE类型，值为value。
yepReturnNumber	#define yepReturnNumber(hProc, value) yepOutputNumber(hProc, YEP_RETURN, value)	将返回值设置为NUMBER类型，值为value。
yepReturnDate	#define yepReturnDate(hProc, value) yepOutputDate(hProc, YEP_RETURN, value)	将返回值设置为DATE类型，值为value。
yepReturnTimestamp	#define yepReturnTimestamp(hProc, value) yepOutputTimestamp(hProc, YEP_RETURN, value)	将返回值设置为TIMESTAMP类型，值为value。
yepReturnYMInterval	#define yepReturnYMInterval(hProc, value) yepOutputYMInterval(hProc, YEP_RETURN, value)	将返回值设置为INTERVAL YEAR TO MONTH类型，值为value。
yepReturnDSInterval	#define yepReturnDSInterval(hProc, value) yepOutputDSInterval(hProc, YEP_RETURN, value)	将返回值设置为INTERVAL DAY TO SECOND类型，值为value。
yepReturnString	#define yepReturnString(hProc, value) yepOutputString(hProc, YEP_RETURN, value)	将返回值设置为VARCHAR类型，值为value指向的字符串。
yepReturnBytes	#define yepReturnBytes(hProc, value, size) yepOutputBytes(hProc, YEP_RETURN, value, size)	将返回值设置为RAW类型，值为bytes指向的字节，大小为size。

使用外置udf

对于已成功定义的外置UDF，其调用方法与函数一致。

示例（单机、共享集群部署）

SELECT udf_func_java(1) FROM dual;
UDF_FUNC_JAVA(1)                                                      
---------------------------------------------------------------- 
Hello  

SELECT udf_func_java(2) FROM dual;
UDF_FUNC_JAVA(2)                                                      
---------------------------------------------------------------- 
World 

SELECT udf_func_c(1) FROM dual;
UDF_FUNC_C(1)
----------------------------------------------------------------
Hello World, id: 1

删除外置udf

删除定义了某个外置UDF的函数，即实现对这个外置UDF的删除。

删除自定义库

确认外置UDF使用的自定义库不再使用时，可对其进行删除，使用DROP_LIBRARY语句删除自定义库。