openGauss每日一练第十五天

openGauss每日一练第十五天

学习地址

https://www.modb.pro/course/133

学习目标

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

课后作业

1.创建带有入参和出参的函数1，调用函数时使用按参数值传递和命名标记法传参

--SQL文本：
create function func_multiply (a integer,b integer) return integer
as 
begin
return a * b;
end ;
/
call func_multiply(2,3);
call func_multiply(a => 4,b => 5);
call func_multiply(a := 3,b := 6);

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

--SQL文本：
create function func_add_multiply (a integer,out result1 integer,out result2 integer,out result3 integer,out result4 integer)
returns setof record
as $$
begin
result1 = a + 5;
result2 = a - 5;
result3 = a * 5;
result4 = a / 5;
return next;
end;
$$language plpgsql;
call func_add_multiply(5,1,1,1,1);
call func_add_multiply(100,1,1,1,1);

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和系统函数查看函数定义

--SQL文本：
\sf func_multiply
\sf func_add_multiply
\x
select * from pg_proc where proname='func_multiply';
select * from pg_proc where proname='func_add_multiply';

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.删除函数

--SQL文本：
drop function func_multiply;
drop function func_add_multiply;

omm=# drop function func_multiply;
DROP FUNCTION
omm=# drop function func_add_multiply;
DROP FUNCTION
omm=#