华为GaussDB T 自定义函数

墨天轮 2019-10-12

2562

自定义函数

功能描述

函数用于返回特定数据，可以返回一个或多个值。在一个函数中必须包含一个或多个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..

  gaussdb t
  「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
 关注作者
 
  
 【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
   评论

 领墨值

 
  有奖问卷
  意见反馈 
 客服小墨