openGauss每日一练第15天|openGauss定义存储过程和函数

本次课学习的内容是学习openGauss定义存储过程和函数。

函数和存储过程是数据库中的一种重要对象，主要功能将用户特定功能的SQL语句集进行封装，并方便调用。

课程学习前还是先进入实训环境，输入su - omm口令和密码连接openGauss。

root@modb:~# 
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

作业打卡

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

omm=# CREATE FUNCTION function1(num1 IN integer, num2 IN integer,res OUT integer)
omm-# RETURN integer
omm-# AS
omm$# BEGIN 
omm$# res :=num1*num2-(num1+num2);
omm$# END;
omm$# /
CREATE FUNCTION

omm=# call function1(2,3,0);
 res 
-----
   1
(1 row)
omm=# call function1(num1 => 1,num2 =>3,0);
 res 
-----
  -1
(1 row)

omm=# call function1(num2:=100,num1:=1,0);
 res 
-----
  -1
(1 row)

2、创建返回类型为record的函数2，重命名函数2

omm=# CREATE OR REPLACE FUNCTION function2(i int ,out res1 bigint,out res2 bigint)
omm-# returns SETOF RECORD
omm-# AS $$
omm$# BEGIN
omm$# res1 =i+1;
omm$# res2 =i*5;
omm$# return next;
omm$# end;
omm$# $$ language plpgsql;
CREATE FUNCTION
omm=# CREATE OR REPLACE FUNCTION function2(i int ,out res1 bigint,out res2 bigint)
omm-# returns SETOF RECORD
omm-# AS $$
omm$# BEGIN
omm$# res1 =i+1;
omm$# res2 =i*5;
omm$# return next;
omm$# end;
omm$# $$ language plpgsql;
CREATE FUNCTION
omm=# call function2(1,0,0);
 res1 | res2 
------+------
    2 |    5
(1 row)
omm=# alter function function2(int) rename to function20;             
ALTER FUNCTION

3、使用\sf和系统函数查看函数定义

omm=# \sf function1 
CREATE OR REPLACE FUNCTION public.function1(num1 integer, num2 integer, OUT res integer)
 RETURNS integer
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$ DECLARE 
BEGIN
res :=num1*num2-(num1+num2);
END$function$;
omm=# select * from pg_proc where proname='function1';
  proname  | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindow | prosecdef | proleakproof | p
roisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmodes |   proargnames   | proargd
efaults |            prosrc            | probin | proconfig | proacl | prodefaultargpos | fencedmode | proshippable | propackage | prokind 
-----------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------------+-----------+--------------+--
-----------+-----------+-------------+----------+-----------------+------------+-------------+----------------+-------------+-----------------+--------
--------+------------------------------+--------+-----------+--------+------------------+------------+--------------+------------+---------
 function1 |         2200 |       10 |   11750 |     100 |       0 |           0 | -            | f        | f           | f         | f            | f
           | f         | v           |        2 |               0 |         23 | 23 23       | {23,23,23}     | {i,i,o}     | {num1,num2,res} |        
        |  DECLARE                    +|        |           |        |                  | f          | f            | f          | f
           |              |          |         |         |         |             |              |          |             |           |              |  
           |           |             |          |                 |            |             |                |             |                 |        
        | BEGIN                       +|        |           |        |                  |            |              |            | 
           |              |          |         |         |         |             |              |          |             |           |              |  
        | res :=num1*num2-(num1+num2);+|        |           |        |                  |            |              |            | 
           |           |             |          |                 |            |             |                |             |                 |        
           |           |             |          |                 |            |             |                |             |                 |        
        | END                          |        |           |        |                  |            |              |            | 
(1 row)

           |              |          |         |         |         |             |              |          |             |           |              |  


omm=# \sf function20
omm=# CREATE OR REPLACE FUNCTION public.function20(i integer, OUT res1 bigint, OUT res2 bigint)
 RETURNS SETOF record
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$
BEGIN
res1 =i+1;
res2 =i*5;
return next;
end;
$function$;
omm=# select * from pg_proc where proname='function2';
 proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindow | prosecdef | proleakproof | pro
isstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmodes | proargnames | proargdefault

s | prosrc | probin | proconfig | proacl | prodefaultargpos | fencedmode | proshippable | propackage | prokind 
---------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------------+-----------+--------------+----
---------+-----------+-------------+----------+-----------------+------------+-------------+----------------+-------------+-------------+--------------
--+--------+--------+-----------+--------+------------------+------------+--------------+------------+---------
(0 rows)

4、删除函数

omm=# drop function function1;
DROP FUNCTION
omm=# drop function function20;
DROP FUNCTION