search
sun-outline
light

openGauss

开源数据库

openGauss社区官网

开源社区

博客chevron-right定义存储过程和函数

定义存储过程和函数

云和恩墨2022-04-22定义存储过程和函数

定义存储过程和函数 ​

本文出处：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=#