定义存储过程和函数

定义存储过程和函数
本文出处：https://www.modb.pro/db/222642

学习地址
https://www.modb.pro/course/133

学习目标
学习 openGauss 定义存储过程和函数

课后作业
1.创建带有入参和出参的函数 1，调用函数时使用按参数值传递和命名标记法传参
omm=# create function func_multiply (a integer,b integer) return integer
omm-# as
omm$# begin
omm$# return a * b;
omm$# end ;
omm$# /
CREATE FUNCTION
omm=# call func_multiply(2,3);
func_multiply

         6

(1 row)
omm=# call func_multiply(a => 4,b => 5);
func_multiply

        20

(1 row)
omm=# call func_multiply(a := 3,b := 6);
func_multiply

        18

(1 row)
omm=#

2.创建返回类型为 record 的函数 2，重命名函数 2
omm=# create function func_add_multiply (a integer,out result1 integer,out result2 integer,out result3 integer,out result4 integer)
omm-# returns setof record
omm-# as $$
omm$# begin
omm$# result1 = a + 5;
omm$# result2 = a - 5;
omm$# result3 = a * 5;
omm$# result4 = a / 5;
omm$# return next;
omm$# end;
omm$# $$language plpgsql;
CREATE FUNCTION
omm=# call func_add_multiply(5,1,1,1,1);
result1 | result2 | result3 | result4
---------+---------+---------+---------
10 | 0 | 25 | 1
(1 row)
omm=# call func_add_multiply(100,1,1,1,1);
result1 | result2 | result3 | result4
---------+---------+---------+---------
105 | 95 | 500 | 20
(1 row)
omm=#

3.使用\sf 和系统函数查看函数定义
omm=# \sf func_multiply
CREATE OR REPLACE FUNCTION public.func_multiply(a integer, b integer)
RETURNS integer
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $function$ DECLARE
begin
return a * b;
end $function$;
omm=# \sf func_add_multiply
CREATE OR REPLACE FUNCTION public.func_add_multiply(a integer, OUT result1 integer, OUT result2 integer, OUT result3 integer, OUT result4 integer)
RETURNS SETOF record
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $function$
begin
result1 = a + 5;
result2 = a - 5;
result3 = a * 5;
result4 = a / 5;
return next;
end;
$function$;
omm=#
omm=# \x
Expanded display is on.
omm=# select * from pg_proc where proname='func_multiply';
-[ RECORD 1 ]----+--------------
proname | func_multiply
pronamespace | 2200
proowner | 10
prolang | 11750
procost | 100
prorows | 0
provariadic | 0
protransform | -
proisagg | f
proiswindow | f
prosecdef | f
proleakproof | f
proisstrict | f
proretset | f
provolatile | v
pronargs | 2
pronargdefaults | 0
prorettype | 23
proargtypes | 23 23
proallargtypes |
proargmodes |
proargnames | {a,b}
proargdefaults |
prosrc | DECLARE
| begin
| return a * b;
| end
probin |
proconfig |
proacl |
prodefaultargpos |
fencedmode | f
proshippable | f
propackage | f
prokind | f

omm=# select * from pg_proc where proname='func_add_multiply';
-[ RECORD 1 ]----+------------------------------------
proname | func_add_multiply
pronamespace | 2200
proowner | 10
prolang | 11750
procost | 100
prorows | 1000
provariadic | 0
protransform | -
proisagg | f
proiswindow | f
prosecdef | f
proleakproof | f
proisstrict | f
proretset | t
provolatile | v
pronargs | 1
pronargdefaults | 0
prorettype | 2249
proargtypes | 23
proallargtypes | {23,23,23,23,23}
proargmodes | {i,o,o,o,o}
proargnames | {a,result1,result2,result3,result4}
proargdefaults |
prosrc |
| begin
| result1 = a + 5;
| result2 = a - 5;
| result3 = a * 5;
| result4 = a / 5;
| return next;
| end;
|
probin |
proconfig |
proacl |
prodefaultargpos |
fencedmode | f
proshippable | f
propackage | f
prokind | f

omm=#

4.删除函数
omm=# drop function func_multiply;
DROP FUNCTION
omm=# drop function func_add_multiply;
DROP FUNCTION
omm=#