泰涨知识 | 【openGauss详解】自定义函数与触发器

泰克Tech

openGauss数据库

自定义函数与触发器




一、

自定义函数




自定义函数的整体与存储过程类似（存储过程可回顾上一篇文章：【openGauss】数据库存储过程的应用），但使用上有些区别：




（1）数据库默认兼容的是Oracle，所以默认自定义函数必须要有返回值，存储过程不需要返回值。

（2）数据库兼容模式为Oracle时，自定义函数支持IN/OUT/INOUT模式的参数。

（3）函数可以出现同名不同参数的定义，也就是多个函数，其中函数名相同，但是参数数量不同或者参数类型不一致的时候可以同时存在，同类型参数也不可以，比如fun1(a int)和fun1(b bigint)，这两个函数不可以同时存在；存储过程不允许出现同名存储过程存在。

（4）自定义函数可用于触发器，存储过程不可以等。




1

自定义函数语法如下：

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




OR REPLACE：存储过程没有alter procedure，但可以使用or replace替换原存储过程，create or replace当存储过程不存在时做创建，当存储过程存在时做替换。




DEFINER = user：用于指定当前存储过程定义者，默认DEFINER=CURRENT_USER。




argmode：参数模式，取值为：IN（输入参数）、OUT（输出参数）、INOUT（输入输出参数），默认是IN。




{ DEFAULT | := | = } ：可以给输入参数设置默认值，DEFAULT、:=、=作用是一样的。




RETURNS：配置返回值类型




IS、AS：语法格式要求，必须写其中一个。两个相同。




BEGIN、END：语法格式要求，必须写。




2

自定义函数实例：




实例1：创建一个自定义函数

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




实例2：调用函数

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




实例3：删除函数

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




实例4：创建一个应用于触发器的函数

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




二、

触发器




1

触发器语法如下：

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




CONSTRAINT ：触发器作为约束来使用。




BEFORE | AFTER | INSTEAD OF：用于指定触发器在什么时候被处罚，BEFORE表示在执行event前，AFTER表示在event之后，INSTEAD OF表示触发器操作直接替换event，也就是event不会被执行。




event：触发触发器的事件，当在指定表上执行event指定的操作时触发触发器，event取值为INSERT（插入数据时）/UPDATE（更新数据时）/UPDATE OF COLUMN（更新指定列时，列可以是多个）/DELETE（删除数据时）/TRUNCATE（清空表时）。




ON table_name：触发器是作用于一张具体的表，结合上两个参数形成了触发器条件（在对指定表执行具体的操作（event）的前/后触发）。




FOR [ EACH ] { ROW | STATEMENT }：默认值为STATEMENT，触发器的执行只在被触发时执行一次，可选值ROW表示当触发器被触发后原操作每操作一行，触发器就会被触发一次，常用值FOR EACH ROW。




WHEN ( condition )：触发器执行过滤条件。




EXECUTE PROCEDURE function_name：触发器被触发的时候执行的操作。




2

触发器实例：




创建两张表t1和t2用于测试：

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




实例1：创建一个触发器，实现当用户往t1表插入数据后，将该数据也同步插入一条道t2表中




触发器所需要的函数定义：

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




触发器定义：

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




insert可以批量插入多条记录，为了保证插入t1表的每条数据都能插入t2表，所以选择FOR EACH ROW。

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




验证：往t1表中插入2行数据

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




实例2：创建一个触发器，实现当用户删除t1表中的数据前，同步删除t2表的数据




触发器所需要的函数定义：

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




触发器定义：

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




如果触发器处理更改为：当用户删除t1表数据时，同步删除t2的数据，t1表保留不动。

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




验证：删除t1表中的数据

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




从以上截图可以看出，t1表中的数据没有删除成功，但是t2表中的数据删除成功了，是因为触发器是在事件执行前触发函数的执行，在函数执行后return为null，所以导致函数中的删除正常执行，但是删除事件本身删除的记录是null，需要将函数中是return改为old才能实现在delete事件执行前触发了函数执行后，delete事件还能正常执行，或者将触发器的before改为alter，在事件执行后再执行触发器函数。




执行以下SQL将函数返回修改为old，并重新执行删除操作（触发器不需要修改）。

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




重新执行后结果如下：

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




实例3：创建一个触发器，实现当用户更新t1表中的数据后，同步更新t2表的数据




触发器所需要的函数定义：

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




触发器定义：

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




如果需要指定触发器仅在更改c1列时才会被触发，配置如下：

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）




验证：将t1表中的1修改为10：

[backcolor=rgba(0, 0, 0, 0.1)]
[backcolor=rgba(0, 0, 0, 0.1)]

添加图片注释，不超过 140 字（可选）

END




更多精彩内容 尽在泰克教育

请持续关注