暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

华为GaussDB T 自定义函数

墨天轮 2019-10-12
2457

自定义函数

功能描述

函数用于返回特定数据,可以返回一个或多个值。在一个函数中必须包含一个或多个RETURN语句。

自定义函数可以直接被存储过程调用,也可以像普通函数一样在SQL语句中使用。使用自定义函数时,表1如下:

表1 校验规则

使用自定义函数的外层SQL

自定义函数的SQL

使用自定义函数的外层SQL的返回信息

SELECT任意表

SELECT

返回执行结果。

SELECT自定义函数中SQL使用的表

INSERT/UPDATE/DELETE/MERGE

返回错误“GS-00927, The trigger or user-defined function used by a SQL statement which is adjusting a table %s.%s did not find the table.”。

UPDATE/DELETE自定义函数中SQL使用的表

SELECT/INSERT/UPDATE/DELETE/MERGE

返回错误“GS-00927, The trigger or user-defined function used by a SQL statement which is adjusting a table %s.%s did not find the table.”。

INSERT任意表

SELECT/INSERT/UPDATE/DELETE/MERGE

记录插入数据库成功,并返回成功信息“n rows affected.”。

MERGE任意表

SELECT

返回执行结果。

MERGE自定义函数中SQL使用的表

INSERT/UPDATE/DELETE/MERGE

返回错误“GS-00927, TThe trigger or user-defined function used by a SQL statement which is adjusting a table %s.%s did not find the table.”。

注意事项

  • 自定义函数和系统函数重名时,数据库会优先调用系统函数。如果需要优先调用重名的自定义函数,则需要将该自定义函数配置在$GSDB_DATA/cfg/udf.ini中,格式为user_name.function_name,一行只能写一个,不支持写注释,udf.ini文件支持配置对象名称大小写敏感。配置完成后,需要重启数据库,配置才会生效。
  • udf.ini中提高优先级的自定义函数如果存在编译错误,GaussDB 100会判定为自定义函数无法匹配,继续尝试用系统函数去验证。这种情况下,自定义函数的编译错误信息会被覆盖。
  • udf.ini文件的权限应设置为只有数据库用户组dbgrp中的用户有权限修改,权限为600。
  • 执行不带参数的自定义函数,可以直接给定自定义函数名,不需要带括号。
  • 自定义函数过程体内建议慎用影响事务提交或回滚的语句,如果出现该类语句,自定义函数出现在DML语句中将报GS-00973错误,不予执行;但在存储过程或匿名块中该类语句可以正常调用。
  • 影响事务提交或回滚的语句枚举如下:
    • 所有DDL语句
    • COMMIT语句
    • ROLLBACK语句
    • SAVEPOIINT语句
    • SET TRANSACTION语句
    • ALTER SESSION语句
    • ALTER SYSTEM KILL SESSION语句
    • GRANT语句
    • REVOKE语句

语法格式

CREATE [OR REPLACE] [IF [NOT] EXIST] FUNCTION [schema_name.]function_name [(args_list)] RETURN data_type { IS | AS } [param-list] BEGIN statement; RETURN expression; ... END;

参数说明

  • schema_name

    函数所有者

  • function_name

    函数名

  • args_list

    输入参数列表。可以指定输入参数的缺省值。

  • data_type

    返回值的数据类型。

  • param-list

    声明的变量列表,可以指定变量的缺省值。格式为“variant_name data_type [:= default_expr];”。

  • statement

    函数语句。

    可使用基本语句、动态语句、控制语句、异常语句或其他语句。基本语句详情请参见基本语句,动态语句详情请参见动态语句,控制语句详情请参见控制语句,其他语句详情请参见其他语句。

  • expression

    返回值表达式。可以是普通变量或表达式,不支持直接返回子查询。

示例

  • 创建自定义函数ztest_f1。

    存储过程和函数存储在同一个系统表中,如果新创建的自定义函数和已有存储过程重名,将导致创建自定义函数失败。因此,在创建自定义函数之前,需要删除重名的已有存储过程。

    --删除重名的存储过程。 DROP PROCEDURE IF EXISTS ztest_f1;
    --删除重名的自定义函数。 DROP FUNCTION IF EXISTS ztest_f1;

    创建自定义函数时,最后的“/”符号用于标示自定义函数定义语句的结束,不能省略,且必须单独成行。

    --创建一个自定义函数。 CREATE OR REPLACE FUNCTION ztest_f1(a INT, b VARCHAR2) RETURN INT AS c INT; BEGIN c := a; RETURN c; END ztest_f1; /
  • 创建并使用自定义函数USER_FUNC_QUERY_TEMP。
    --删除重名表USER_FUNC_TEMP。 DROP TABLE IF EXISTS USER_FUNC_TEMP;
    --创建表USER_FUNC_TEMP。 CREATE TABLE USER_FUNC_TEMP(ID INT);
    --向表USER_FUNC_TEMP中插入记录。 INSERT INTO USER_FUNC_TEMP VALUES(1);
    --创建自定义函数USER_FUNC_QUERY_TEMP,该自定义函数语句块中SQL语句用于查询表USER_FUNC_TEMP。 CREATE OR REPLACE FUNCTION USER_FUNC_QUERY_TEMP(a INT) RETURN INT AS c INT; d INT; BEGIN c := a; SELECT ID INTO d FROM USER_FUNC_TEMP WHERE ROWNUM = c; RETURN d; END USER_FUNC_QUERY_TEMP; /
    --使用自定义函数USER_FUNC_QUERY_TEMP的外层SQL是SELECT操作,所使用的表和自定义函数中SQL使用的表相同,返回SELECT语句的执行结果。 SELECT * FROM USER_FUNC_TEMP WHERE USER_FUNC_QUERY_TEMP(1) = 1; ID ------------ 1 1 rows fetched.
    --使用自定义函数USER_FUNC_QUERY_TEMP的外层SQL是INSERT操作,所使用的表和自定义函数中SQL使用的表相同,返回INSERT语句的执行结果。 INSERT INTO USER_FUNC_TEMP VALUES(USER_FUNC_QUERY_TEMP(1)); 1 rows affected.
    --使用自定义函数USER_FUNC_QUERY_TEMP的外层SQL是UPDATE操作,所使用的表和自定义函数中SQL使用的表相同,ERR_TAB_MUTATING,返回错误GS-00927:"The trigger or user-defined function used by a SQL statement which is adjusting a table %s.%s did not find the table."。 UPDATE USER_FUNC_TEMP SET ID=100 WHERE ID = USER_FUNC_QUERY_TEMP(1); GS-00927, [7:1]The trigger or user-defined function used by a SQL statement which is adjusting a table %s.%s did not find the table..
    --使用自定义函数USER_FUNC_QUERY_TEMP的外层SQL是DELETE操作,所使用的表和自定义函数中SQL使用的表相同,返回错误GS-00927:"The trigger or user-defined function used by a SQL statement which is adjusting a table %s.%s did not find the table."。 DELETE FROM USER_FUNC_TEMP WHERE ID = USER_FUNC_QUERY_TEMP(1); GS-00927, [7:1]The trigger or user-defined function used by a SQL statement which is adjusting a table %s.%s did not find the table..
  • 创建并使用自定义函数USER_FUNC_DELETE_TEMP。
    --删除表USER_FUNC_TEMP01。 DROP TABLE IF EXISTS USER_FUNC_TEMP01;
    --创建表USER_FUNC_TEMP01。 CREATE TABLE USER_FUNC_TEMP01(ID INT);
    --向表USER_FUNC_TEMP01中插入记录。 INSERT INTO USER_FUNC_TEMP01 VALUES(1);
    --创建自定义函数USER_FUNC_DELETE_TEMP,该自定义函数语句块中SQL语句用于从表USER_FUNC_TEMP01中删除记录。 CREATE OR REPLACE FUNCTION USER_FUNC_DELETE_TEMP(a INT) RETURN INT AS c INT; BEGIN c := a; DELETE FROM USER_FUNC_TEMP01 WHERE ID = c; return c; END USER_FUNC_DELETE_TEMP; /
    --使用自定义函数USER_FUNC_DELETE_TEMP的外层SQL是SELECT操作,所使用的表和自定义函数中SQL使用的表相同:SELECT USER_FUNC_DELETE_TEMP(100) FROM USER_FUNC_TEMP01; --返回错误 GS-00927, [5:1]The trigger or user-defined function used by a SQL statement which is adjusting a table %s.%s did not find the table..
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论