泰涨知识 | 【openGauss详解】自定义函数与触发器
本帖最后由 泰克Tech 于 2024-10-18 15:47 编辑openGauss数据库
自定义函数与触发器
一、
自定义函数
自定义函数的整体与存储过程类似（存储过程可回顾上一篇文章：【openGauss】数据库存储过程的应用），但使用上有些区别：
（1）数据库默认兼容的是Oracle，所以默认自定义函数必须要有返回值，存储过程不需要返回值。
（2）数据库兼容模式为Oracle时，自定义函数支持IN/OUT/INOUT模式的参数。
（3）函数可以出现同名不同参数的定义，也就是多个函数，其中函数名相同，但是参数数量不同或者参数类型不一致的时候可以同时存在，同类型参数也不可以，比如fun1(a int)和fun1(b bigint)，这两个函数不可以同时存在；存储过程不允许出现同名存储过程存在。
（4）自定义函数可用于触发器，存储过程不可以等。
1
自定义函数语法如下：
https://picx.zhimg.com/80/v2-f7cc7eb4041cf0eab9208659cd736c72_720w.jpg
添加图片注释，不超过 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：创建一个自定义函数
https://pic1.zhimg.com/80/v2-0bf7c87acd2c9711475eee810ed72bc0_720w.webp
添加图片注释，不超过 140 字（可选）
实例2：调用函数
https://pica.zhimg.com/80/v2-3e067e0205b112d50a1a66e0caf5246d_720w.webp
添加图片注释，不超过 140 字（可选）
https://picx.zhimg.com/80/v2-9f08008e0d4d9f9c4d72c978728131cb_720w.webp
添加图片注释，不超过 140 字（可选）
实例3：删除函数
https://pic1.zhimg.com/80/v2-04adcd95bcb5d15d06e99aa7751aeaaa_720w.webp
添加图片注释，不超过 140 字（可选）
实例4：创建一个应用于触发器的函数
https://pic1.zhimg.com/80/v2-a0cbfc892b49bf477497d53e71ed67e1_720w.webp
添加图片注释，不超过 140 字（可选）
二、
触发器
1
触发器语法如下：
https://pica.zhimg.com/80/v2-42f4b35e255bab34a9377023682e8501_720w.webp
添加图片注释，不超过 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用于测试：
https://picx.zhimg.com/80/v2-b1dd7dd0e33c6ff4b3e49529c060ee9f_720w.webp
添加图片注释，不超过 140 字（可选）
实例1：创建一个触发器，实现当用户往t1表插入数据后，将该数据也同步插入一条道t2表中
触发器所需要的函数定义：
https://picx.zhimg.com/80/v2-83b0bf3ecf4776043452d936d85644ad_720w.webp
添加图片注释，不超过 140 字（可选）
触发器定义：
https://pic1.zhimg.com/80/v2-4984516b6a974d81823023c6bd6fc9c5_720w.webp
添加图片注释，不超过 140 字（可选）
insert可以批量插入多条记录，为了保证插入t1表的每条数据都能插入t2表，所以选择FOR EACH ROW。
https://picx.zhimg.com/80/v2-81f173eaf12068ec821c2413a734f804_720w.webp
添加图片注释，不超过 140 字（可选）
验证：往t1表中插入2行数据
https://pica.zhimg.com/80/v2-8d76b206f5fff198840a93bf86e03fbc_720w.webp
添加图片注释，不超过 140 字（可选）
实例2：创建一个触发器，实现当用户删除t1表中的数据前，同步删除t2表的数据
触发器所需要的函数定义：
https://picx.zhimg.com/80/v2-02757b0abb5921035d396b3068d0fcdd_720w.webp
添加图片注释，不超过 140 字（可选）
https://picx.zhimg.com/80/v2-eee975124b5fbd72979fe11c76c124ae_720w.webp
添加图片注释，不超过 140 字（可选）
触发器定义：
https://picx.zhimg.com/80/v2-2b5c16bb6a7abcc4ad29c7258a0594c2_720w.webp
添加图片注释，不超过 140 字（可选）
如果触发器处理更改为：当用户删除t1表数据时，同步删除t2的数据，t1表保留不动。
https://picx.zhimg.com/80/v2-84b2ac77fcb7af3d9bc1cc513d7d5aec_720w.webp
添加图片注释，不超过 140 字（可选）
验证：删除t1表中的数据
https://picx.zhimg.com/80/v2-f10259636ad991b21e8a893d879498c6_720w.webp
添加图片注释，不超过 140 字（可选）
从以上截图可以看出，t1表中的数据没有删除成功，但是t2表中的数据删除成功了，是因为触发器是在事件执行前触发函数的执行，在函数执行后return为null，所以导致函数中的删除正常执行，但是删除事件本身删除的记录是null，需要将函数中是return改为old才能实现在delete事件执行前触发了函数执行后，delete事件还能正常执行，或者将触发器的before改为alter，在事件执行后再执行触发器函数。
执行以下SQL将函数返回修改为old，并重新执行删除操作（触发器不需要修改）。
https://pic1.zhimg.com/80/v2-8dd7649c791903407f1d110f43116f90_720w.webp
添加图片注释，不超过 140 字（可选）
重新执行后结果如下：
https://pica.zhimg.com/80/v2-e2f8a5f514496a8eb69be16a1bd404d1_720w.webp
添加图片注释，不超过 140 字（可选）
实例3：创建一个触发器，实现当用户更新t1表中的数据后，同步更新t2表的数据
触发器所需要的函数定义：
https://picx.zhimg.com/80/v2-269fe5b7cac774bb43fc7afe1b110fa3_720w.webp
添加图片注释，不超过 140 字（可选）
触发器定义：
https://picx.zhimg.com/80/v2-754b10a4c130af4cbe4cddbcb4eb3f2d_720w.webp
添加图片注释，不超过 140 字（可选）
https://picx.zhimg.com/80/v2-5e4f215d8578e8fa8068572fd4810109_720w.webp
添加图片注释，不超过 140 字（可选）
如果需要指定触发器仅在更改c1列时才会被触发，配置如下：
https://pic1.zhimg.com/80/v2-d9a905d0d28eb88261df7874a207e725_720w.webp
添加图片注释，不超过 140 字（可选）
验证：将t1表中的1修改为10：
https://picx.zhimg.com/80/v2-0a011bb924db88d51f7ba3f344289e06_720w.webp
添加图片注释，不超过 140 字（可选）
END
更多精彩内容 尽在泰克教育
请持续关注
页:
[1]