自定义函数
功能描述
函数用于返回特定数据,可以返回一个或多个值。在一个函数中必须包含一个或多个RETURN语句。
自定义函数可以直接被存储过程调用,也可以像普通函数一样在SQL语句中使用。使用自定义函数时,表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进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。评论