openGauss每日一练第15天 课程笔记和作业

数据库环境

openGauss:2.0.0 - 数据库实训平台

学习目标

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

学习笔记

• 创建存储过程

create procedure procedure_insert(a int, b int)
as
begin
        insert into item values(a, b);
end;
/

• 创建函数

CREATE FUNCTION func_add_sql(num1 integer, num2 integer) RETURN integer
AS
BEGIN
RETURN num1 + num2;
END;
/

CREATE FUNCTION func_increment_sql(num1 IN integer, num2 IN integer, res OUT integer)
RETURN integer
AS
BEGIN
res := num1 + num2;
END;
/

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;

课后作业

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

omm=# create function fun_add(num1 in integer,num2 in integer,result out integer)
omm-# return integer
omm-# as
omm$# begin
omm$# result := num1+num2;
omm$# end;
omm$# /
CREATE FUNCTION

omm=# \sf fun_add
CREATE OR REPLACE FUNCTION public.fun_add(num1 integer, num2 integer, OUT result integer)
 RETURNS integer
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$ DECLARE 
begin
result := num1+num2;
end$function$;

omm=# call fun_add(1,2,0);
 result 
--------
      3
(1 row)

omm=# call fun_add(num1:=3,num2:=4,result:=0);
 result 
--------
      7
(1 row)

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

create or replace function fun_mul(num int,out result1 bigint,out result2 bigint)
returns setof record
as $$
begin
result1 = num * 10;
result2 = num * 20;
return next;
end;
$$language plpgsql;
CREATE FUNCTION

omm=# call fun_mul(2,0,0);
 result1 | result2 
---------+---------
      20 |      40
(1 row)

omm=# alter function fun_mul(num int,out result1 bigint,out result2 bigint) rename to fun_mul_v2;
ALTER FUNCTION

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

omm=# \sf fun_mul_v2
CREATE OR REPLACE FUNCTION public.fun_mul_v2(num integer, OUT result1 bigint, OUT result2 bigint)
 RETURNS SETOF record
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$
begin
result1 = num * 10;
result2 = num * 20;
return next;
end;
$function$;

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

4.删除函数

omm=# drop function fun_add;
DROP FUNCTION
omm=# drop function fun_mul_v2;
DROP FUNCTION

学习资源

• openGauss SQL学习参考资料
• 每日一练：openGauss数据库在线实训课程
• openGauss每日一练 | 21期养成好习惯，提升技术能力！
• 墨天轮Markdown编辑器使用介绍
• 墨天轮数据库在线实训平台V1.0操作手册
• 墨天轮数据社区

欢迎各位同学一起来交流学习心得！