学习目标
学习openGauss定义存储过程和函数
课程学习实操
连接数据库
#第一次进入等待15秒 #数据库启动中... su - omm gsql -r复制
1.创建存储过程
create table item(c1 int, c2 int); create procedure procedure_insert(a int, b int) as begin insert into item values(a, b); end; /omm=# create table item(c1 int, c2 int); CREATE TABLE omm=# create procedure procedure_insert(a int, b int) omm-# as omm$# begin omm$# insert into item values(a, b); omm$# end; omm$# / CREATE PROCEDURE omm=#复制
–查看存储过程
select * from pg_proc where proname = 'procedure_insert';omm=# select * from pg_proc where proname = 'procedure_insert'; proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindo w | prosecdef | proleakproof | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmodes | proargnames | proargdefaults | prosrc | probin | proconfig | p roacl | prodefaultargpos | fencedmode | proshippable | propackage | prokind ------------------+--------------+----------+---------+---------+---------+-------------+--------------+----------+----------- --+-----------+--------------+-------------+-----------+-------------+----------+-----------------+------------+-------------+ ----------------+-------------+-------------+----------------+----------------------------------------+--------+-----------+-- ------+------------------+------------+--------------+------------+--------- | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | insert into item values(a, b);+| | | | | | | begin +| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | procedure_insert | 2200 | 10 | 11750 | 100 | 0 | 0 | - | f | f | f | f | f | f | v | 2 | 0 | 2278 | 23 23 | | | {a,b} | | DECLARE +| | | | | f | f | f | p | | | | | | | | | | | | | end | | | | | | | | (1 row) omm=#复制
2.调用存储过程
select procedure_insert(1,100); select * from item;omm=# select procedure_insert(1,100); procedure_insert ------------------ (1 row) omm=# select * from item; (1 row) omm=# c1 | c2 ----+----- 1 | 100 omm=#
3.删除存储过程
drop procedure if exists procedure_insert;omm=# drop procedure if exists procedure_insert; DROP PROCEDURE omm=#
4.创建函数
–创建一个函数func_add_sql，计算两个整数的和，并返回结果
CREATE FUNCTION func_add_sql(num1 integer, num2 integer) RETURN integer AS BEGIN RETURN num1 + num2; END; /omm=# CREATE FUNCTION func_add_sql(num1 integer, num2 integer) RETURN integer omm-# AS omm$# BEGIN omm$# RETURN num1 + num2; omm$# END; omm$# / CREATE FUNCTION omm=#复制
–查看函数定义
\sf func_add_sql select * from pg_proc where proname = 'func_add_sql';omm=# \sf func_add_sql CREATE OR REPLACE FUNCTION public.func_add_sql(num1 integer, num2 integer) RETURNS integer LANGUAGE plpgsql NOT FENCED NOT SHIPPABLE AS $function$ DECLARE BEGIN RETURN num1 + num2; END$function$; omm=# select * from pg_proc where proname = 'func_add_sql'; allargtypes | proargmodes | proargnames | proargdefaults | prosrc | probin | proconfig | proacl | prodefaultargpo s | fencedmode | proshippable | propackage | prokind --------------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------------+- ----------+--------------+-------------+-----------+-------------+----------+-----------------+------------+-------------+---- ------------+-------------+-------------+----------------+---------------------+--------+-----------+--------+---------------- --+------------+--------------+------------+--------- proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindow | prosecdef | proleakproof | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | pro func_add_sql | 2200 | 10 | 11750 | 100 | 0 | 0 | - | f | f | f | f | f | f | v | 2 | 0 | 23 | 23 23 | | | {num1,num2} | | DECLARE +| | | | | | | | BEGIN +| | | | | | | | | | | | | | | | | | | f | f | f | f | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | RETURN num1 + num2;+| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | END | | | | | | | | (1 row) omm=#
–按参数值传递。
CALL func_add_sql(1, 3); --使用命名标记法传参。 CALL func_add_sql(num1 => 1,num2 => 3); CALL func_add_sql(num2 := 2, num1 := 3);omm=# CALL func_add_sql(1, 3); func_add_sql -------------- 4 (1 row) omm=# --使用命名标记法传参。 omm=# CALL func_add_sql(num1 => 1,num2 => 3); func_add_sql -------------- 4 (1 row) omm=# CALL func_add_sql(num2 := 2, num1 := 3); omm=# func_add_sql -------------- 5 (1 row)
–创建带出参的函数
CREATE FUNCTION func_increment_sql(num1 IN integer, num2 IN integer, res OUT integer) RETURN integer AS BEGIN res := num1 + num2; END; /omm=# CREATE FUNCTION func_increment_sql(num1 IN integer, num2 IN integer, res OUT integer) omm-# RETURN integer omm-# AS omm$# BEGIN omm$# omm$# /res := num1 + num2; omm$# END; CREATE FUNCTION omm=#复制
–出参传入常量
CALL func_increment_sql(1,2,1);omm=# CALL func_increment_sql(1,2,1); res ----- 3 (1 row)
–创建返回RECORD类型的函数
CREATE OR REPLACE FUNCTION func_increment_sql1(i int, out result_1 bigint, out result_2 bigint) returns SETOF RECORD as $$ begin result_1 = i + 1; result_2 = i * 10; return next; end; $$language plpgsql; call func_increment_sql1(1, 0, 0);omm=# CREATE OR REPLACE FUNCTION func_increment_sql1(i int, out result_1 bigint, out result_2 omm(# bigint) omm-# returns SETOF RECORD omm-# as $$ omm$# begin omm$# result_1 = i + 1; omm$# result_2 = i * 10; omm$# return next; omm$# end; omm$# $$language plpgsql; CREATE FUNCTION omm=# call func_increment_sql1(1, 0, 0); result_1 | result_2 ----------+---------- 2 | 10 (1 row) omm=#
5.修改函数定义
–重命名
ALTER FUNCTION func_add_sql(INTEGER, INTEGER) RENAME TO add_two_number;omm=# ALTER FUNCTION func_add_sql(INTEGER, INTEGER) RENAME TO add_two_number; ALTER FUNCTION omm=#
6.删除函数
DROP FUNCTION add_two_number; Drop function func_increment_sql1; Drop function func_increment_sql;omm=# DROP FUNCTION add_two_number; DROP FUNCTION omm=# Drop function func_increment_sql1; DROP FUNCTION omm=# Drop function func_increment_sql; DROP FUNCTION omm=#
课程作业
1.创建带有入参和出参的函数1，调用函数时使用按参数值传递和命名标记法传参
omm=# CREATE FUNCTION func1(num1 IN integer, num2 IN integer, res OUT integer) omm-# omm-# omm$# BEGIN omm$# RETURN integer AS res := num1 * num2; omm$# END; omm$# / CREATE FUNCTION ^ omm=# call func1(2,8,0); res ----- 16 (1 row) omm=# CALL func1(num1 => 2,num2 => 8, res => 0); res ----- 16 (1 row) omm=# CALL func1(num2 := 8, num1 := 2, res := 0); res ----- 16 (1 row) omm=#
2.创建返回类型为record的函数2，重命名函数2
omm=# CREATE OR REPLACE FUNCTION func2(i int, out result_1 bigint, out result_2 bigint) omm-# returns SETOF RECORD result_1 = i + 10; omm$# omm-# as $$ omm$# begin omm$# result_2 = i * 10; omm$# return next; omm$# end; $$language plpgsql;omm$# CREATE FUNCTION omm=# call func2(1, 0, 0); (1 row) omm=# result_1 | result_2 ----------+---------- 11 | 10 ^ omm=# alter function func2(i int, out result_1 bigint, out result_2 bigint) rename to func3; ALTER FUNCTION omm=#复制
3.使用\sf和系统函数查看函数定义
omm=# \sf func3 CREATE OR REPLACE FUNCTION public.func3(i integer, OUT result_1 bigint, OUT result_2 bigint) RETURNS SETOF record LANGUAGE plpgsql NOT FENCED NOT SHIPPABLE AS $function$ begin result_1 = i + 10; result_2 = i * 10; return next; end; $function$; omm=#omm=# select * from pg_proc where proname = 'func3'; proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindow | prose cdef | proleakproof | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallar gtypes | proargmodes | proargnames | proargdefaults | prosrc | probin | proconfig | proacl | prodefaulta rgpos | fencedmode | proshippable | propackage | prokind ---------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------------+------ -----+--------------+-------------+-----------+-------------+----------+-----------------+------------+-------------+--------- | | | | | | | | | | | | | begin +| | | | | | | | | | | | | | | | | | | | | | | | | | | -------+-------------+-----------------------+----------------+--------------------+--------+-----------+--------+------------ ------+------------+--------------+------------+--------- func3 | 2200 | 10 | 11750 | 100 | 1000 | 0 | - | f | f | f | f | f | t | v | 1 | 0 | 2249 | 23 | {23,20,2 0} | {i,o,o} | {i,result_1,result_2} | | +| | | | | f | f | f | f | | | | | | | | | | | | | | | | | | | | | | | | result_2 = i * 10;+| | | | --More-- | | | | | | | | | | | | | result_1 = i + 10;+| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | return next; +| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | end; +| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | (1 row) omm=#
4.删除函数
omm=# \df List of functions Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind --------+-------+------------------+-----------------------------------------------------+--------+------------+------------+- -------- public | func1 | integer | num1 integer, num2 integer, OUT res integer | normal | f | f | f public | func3 | SETOF record | i integer, OUT result_1 bigint, OUT result_2 bigint | normal | f | f | f (2 rows) ^ omm=# drop function func1; DROP FUNCTION ^ omm=# drop function func3; DROP FUNCTION omm=#复制
学习总结
通过本节课的学习，我掌握了存储过程和函数的基本操作，其中包括函数和存储过程的创建，调用，删除等。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
星星之火
11月前
评论
0通过本节课的学习，我掌握了存储过程和函数的基本操作，其中包括函数和存储过程的创建，调用，删除等。
11月前
点赞 评论
相关阅读
2025年3月国产数据库大事记
墨天轮编辑部
866次阅读
2025-04-03 15:21:16
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
MogDB
286次阅读
2025-04-17 10:41:41
openGauss 7.0.0-RC1 版本正式发布!
Gauss松鼠会
199次阅读
2025-04-01 12:27:03
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
云和恩墨
182次阅读
2025-04-16 09:52:02
openGauss 7.0.0-RC1 版本体验：一主一备快速安装指南
孙莹
175次阅读
2025-04-01 10:30:07
鲲鹏RAG一体机解决方案正式发布 openGauss DataVec向量数据库助力DeepSeek行业应用
Gauss松鼠会
120次阅读
2025-03-31 10:00:29
荣誉时刻！openGauss认证证书快递已发，快来看看谁榜上有名！
墨天轮小教习
103次阅读
2025-04-23 17:39:13
openGauss6.0.0适配操作系统自带的软件，不依赖三方库
来杯拿铁
74次阅读
2025-04-18 10:49:53
opengauss使用gs_probackup进行增量备份恢复
进击的CJR
70次阅读
2025-04-09 16:11:58
Postgresql数据库单个Page最多存储多少行数据
maozicb
53次阅读
2025-04-23 16:02:19
