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

学习目标

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

连接数据库

root@modb:~# su - omm
omm@modb:~$ gsql -r

课程作业

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

创建函数
omm=# CREATE FUNCTION func_add_sql(num1 IN integer, num2 IN integer, res OUT integer)
RETURN integer
AS
BEGIN
res := num1 + num2;
END;
omm-# /
CREATE FUNCTION
omm=# CALL func_add_sql(1,2,1);
 res 
-----
   3
(1 row)
--使用命名标记法传参方式1
omm=# CALL func_add_sql(num1 => 1,num2 => 3,res => 1);
 res 
-----
   4
(1 row)
--使用命名标记法传参方式2
omm=# CALL func_add_sql(num1 := 2, num2 := 3,res := 1);
omm=#  res 
-----
   5
(1 row)
omm=# 

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

omm=# 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; omm-# 
CREATE FUNCTION
omm=# call func_increment_sql1(1, 0, 0);
 result_1 | result_2 
----------+----------
        2 |       10
(1 row)
omm=# 

重命名函数

注：重命名函数时，函数名后只带入参的数据类型

omm=# ALTER FUNCTION func_increment_sql1(int) RENAME TO func_increment_sql2;
ALTER FUNCTION
omm=# 

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

omm=# \sf func_add_sql
CREATE OR REPLACE FUNCTION public.func_add_sql(num1 integer, num2 integer, OUT res integer)
 RETURNS integer
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$ DECLARE 
BEGIN
res := num1 + num2;
END$function$;
omm=# 
omm=# \sf func_increment_sql1
ERROR:  function "func_increment_sql1" does not exist
omm=# 
omm=# \sf func_increment_sql2
CREATE OR REPLACE FUNCTION public.func_increment_sql2(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=# 

-使用系统函数查看函数定义

omm=# select * from pg_proc where proname ='func_add_sql';
   proname    | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proisw
indow | prosecdef | proleakproof | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | p
roargtypes | proallargtypes | proargmodes |   proargnames   | proargdefaults |       prosrc        | probin | proconfi
g | proacl | prodefaultargpos | fencedmode | proshippable | propackage | prokind 
--------------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------
------+-----------+--------------+-------------+-----------+-------------+----------+-----------------+------------+--
-----------+----------------+-------------+-----------------+----------------+---------------------+--------+---------
--+--------+------------------+------------+--------------+------------+---------
 func_add_sql |         2200 |       10 |   11750 |     100 |       0 |           0 | -            | f        | f     
      | f         | f            | f           | f         | v           |        2 |               0 |         23 | 2
3 23       | {23,23,23}     | {i,i,o}     | {num1,num2,res} |                |  DECLARE           +|        |         
              |              |          |         |         |         |             |              |          |       
      |           |              |             |           |             |          |                 |            |  
           |                |             |                 |                | res := num1 + num2;+|        |         
  |        |                  | f          | f            | f          | f
              |              |          |         |         |         |             |              |          |       
      |           |              |             |           |             |          |                 |            |  
           |                |             |                 |                | BEGIN              +|        |         
  |        |                  |            |              |            | 
      |           |              |             |           |             |          |                 |            |  
           |                |             |                 |                | END                 |        |         
--More--  |        |                  |            |              |            | 
              |              |          |         |         |         |             |              |          |       
  |        |                  |            |              |            | 
(1 row)

4.删除函数

omm=# drop function func_add_sql;
DROP FUNCTION
omm=# drop function func_increment_sql2;
DROP FUNCTION
omm=#