学习目标
学习openGauss定义存储过程和函数
课程学习
连接openGauss
#第一次进入等待15秒
#数据库启动中…
su - omm
gsql -r
课后作业
1.创建带有入参和出参的函数1，调用函数时使用按参数值传递和命名标记法传参
CREATE FUNCTION func_add_sql1(num1 integer, num2 integer) RETURN integer
AS
BEGIN
RETURN num1 + num2;
END;
/
\sf func_add_sql1
select * from pg_proc where proname = 'func_add_sql1';
--使用命名标记法传参。
CALL func_add_sql1(num1 => 1,num2 => 3);
CALL func_add_sql1(num2 := 2, num1 := 3);
2.创建返回类型为record的函数2，重命名函数2
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);
ALTER FUNCTION func_increment_sql1( int ) RENAME TO func_increment_sql2;
3.使用\sf和系统函数查看函数定义
\sf func_add_sql1
\sf func_increment_sql1
select * from pg_proc where proname = 'func_add_sql1' or proname = 'func_increment_sql1' ;
4.删除函数
DROP FUNCTION func_add_sql1;
Drop function func_increment_sql2;
作业执行结果
omm=# CREATE FUNCTION func_add_sql1(num1 integer, num2 integer) RETURN integer
omm$# END;
omm-# AS
omm$# BEGIN
omm$# RETURN num1 + num2;
omm$# /
CREATE FUNCTION
omm=#
omm=# \sf func_add_sql1
CREATE OR REPLACE FUNCTION public.func_add_sql1(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_sql1';
proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransfo
rm | proisagg | proiswindow | prosecdef | proleakproof | proisstrict | proretset | provolatile |
pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmodes | proargname
s | proargdefaults | prosrc | probin | proconfig | proacl | prodefaultargpos | fence
dmode | proshippable | propackage | prokind
---------------+--------------+----------+---------+---------+---------+-------------+-----------
---+----------+-------------+-----------+--------------+-------------+-----------+-------------+-
---------+-----------------+------------+-------------+----------------+-------------+-----------
--+----------------+---------------------+--------+-----------+--------+------------------+------
------+--------------+------------+---------
func_add_sql1 | 2200 | 10 | 11750 | 100 | 0 | 0 | -
| f | f | f | f | f | f | v |
2 | 0 | 23 | 23 23 | | | {num1,num2
} | | DECLARE +| | | | | f
| f | f | f
| | | | | |
| | BEGIN +| | | | |
| | |
| | | | | | |
| | | | | | | |
| | | | | | |
| | | | | | | |
| | | | | |
| | RETURN num1 + num2;+| | | | |
| | |
| | | | | | |
| | | | | | | |
| | | | | |
| | END | | | | |
| | |
(1 row)
^
omm=# CALL func_add_sql1(num2 := 2, num1 := 3);
func_add_sql1
---------------
5
(1 row)
omm=#
omm=#
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;
return next;
omm$# omm$# end;
omm$# $$language plpgsql;
CREATE FUNCTION
call func_increment_sql1(1, 0, 0);
omm=# result_1 | result_2
----------+----------
2 | 10
omm=# (1 row)
omm=#
omm=# ALTER FUNCTION func_increment_sql1( int, bigint, bigint) RENAME TO func_increment_sql2;
ERROR: function func_increment_sql1(integer, bigint, bigint) does not exist
omm=# ALTER FUNCTION func_add_sql(INTEGER, INTEGER) RENAME TO add_two_number;
ERROR: function func_add_sql(integer, integer) does not exist
omm=# \sf func_add_sql1
CREATE OR REPLACE FUNCTION public.func_add_sql1(num1 integer, num2 integer)
RETURNS integer
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $function$ DECLARE
BEGIN
RETURN num1 + num2;
END$function$;
\sf func_increment_sql1
omm=# CREATE OR REPLACE FUNCTION public.func_increment_sql1(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 + 1;
result_2 = i * 10;
return next;
end;
$function$;
omm=# select * from pg_proc where proname = 'func_add_sql1' or proname = 'func_increment_sql1' ;
proname | pronamespace | proowner | prolang | procost | prorows | provariadic | prot
ransform | proisagg | proiswindow | prosecdef | proleakproof | proisstrict | proretset | provolat
ltargpos | fencedmode | proshippable | propackage | prokind
---------------------+--------------+----------+---------+---------+---------+-------------+-----
---------+----------+-------------+-----------+--------------+-------------+-----------+---------
ile | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmodes |
proargnames | proargdefaults | prosrc | probin | proconfig | proacl | prodefau
----+----------+-----------------+------------+-------------+----------------+-------------+-----
------------------+----------------+---------------------+--------+-----------+--------+---------
---------+------------+--------------+------------+---------
func_add_sql1 | 2200 | 10 | 11750 | 100 | 0 | 0 | -
| f | f | f | f | f | f | v
| 2 | 0 | 23 | 23 23 | | | {num
| f | f | f | f
| | | | | | |
| | | | | | |
1,num2} | | DECLARE +| | | |
| | | | | | |
| | BEGIN +| | | |
| | | |
| | | | | | |
| | | | | | |
| | | | | | |
| | RETURN num1 + num2;+| | | |
| | | |
| | | | | | |
| | | | | | |
| | | | | | |
| | END | | | |
| | | |
func_increment_sql1 | 2200 | 10 | 11750 | 100 | 1000 | 0 | -
| f | f | f | f | f | t | v
| 1 | 0 | 2249 | 23 | {23,20,20} | {i,o,o} | {i,r
esult_1,result_2} | | +| | | |
| f | f | f | f
| | | | | | |
| | | | | | |
| | | | | | |
| | begin +| | | |
| | | |
| | | | | | |
| | | | | | |
| | | | | | |
| | result_1 = i + 1; +| | | |
| | | |
| | | | | | |
| | | | | | |
| | | | | | |
| | result_2 = i * 10; +| | | |
| | | |
| | | | | | |
| | | | | | |
| | | | | | |
| | return next; +| | | |
| | | |
| | | | | | |
| | | | | | |
| | | | | | |
| | end; +| | | |
| | | |
| | | | | | |
| | | | | | |
| | | | | | |
| | | | | |
| | | |
(2 rows)
omm=#
omm=#
omm=# ALTER FUNCTION func_increment_sql1( int ) RENAME TO func_increment_sql2;
ALTER FUNCTION
omm=#
omm=# \sf func_add_sql1
CREATE OR REPLACE FUNCTION public.func_add_sql1(num1 integer, num2 integer)
RETURNS integer
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $function$ DECLARE
BEGIN
RETURN num1 + num2;
END$function$;
omm=# \sf func_increment_sql1
ERROR: function "func_increment_sql1" does not exist
omm=# select * from pg_proc where proname = 'func_add_sql1' or proname = 'func_increment_sql1' ;
proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransfo
rm | proisagg | proiswindow | prosecdef | proleakproof | proisstrict | proretset | provolatile |
func_add_sql1 | 2200 | 10 | 11750 | 100 | 0 | 0 | -
| f | f | f | f | f | f | v |
2 | 0 | 23 | 23 23 | | | {num1,num2
pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmodes | proargname
s | proargdefaults | prosrc | probin | proconfig | proacl | prodefaultargpos | fence
dmode | proshippable | propackage | prokind
---------------+--------------+----------+---------+---------+---------+-------------+-----------
---+----------+-------------+-----------+--------------+-------------+-----------+-------------+-
---------+-----------------+------------+-------------+----------------+-------------+-----------
--+----------------+---------------------+--------+-----------+--------+------------------+------
------+--------------+------------+---------
} | | DECLARE +| | | | | f
| f | f | f
| | | | | | |
| | | | | | | |
| | | | | |
| | BEGIN +| | | | |
| | | | | | | |
| | | | | |
--More-- | | |
| | | | | | |
| | RETURN num1 + num2;+| | | | |
| | |
| | | | | | |
| | | | | | | |
| | | | | |
| | END | | | | |
| | |
(1 row)
omm=#
omm=#
omm=# DROP FUNCTION func_add_sql1;
DROP FUNCTION
omm=# Drop function func_increment_sql2;
DROP FUNCTION
最后修改时间：2021-12-22 14:47:03
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
