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

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

CREATE FUNCTION fun_1(num1 IN integer, num2 IN integer, res OUT integer) 

RETURN integer 

AS 

BEGIN

 res := num1 + num2; 

END; 

/

CALL fun_1(1,2,1);

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

omm$# omm$#
omm$# /
CREATE FUNCTION

omm=# CALL fun_1(1,2,1);
res
-----
3
(1 row)

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

CREATE OR REPLACE FUNCTION func_2
(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;
/

call func_2(1, 0, 0);

ALTER FUNCTION func_2(int) RENAME TO func_3;

omm=# CREATE OR REPLACE FUNCTION func_2(i int, out result_1 bigint, out result_2 bigint)
omm-# returns SETOF RECORD
as $$
omm-# omm$# omm$# result_1 = i + 1;
begin
omm$# result_2 = i * 10;
omm$# return next;
omm$# end;
omm$# $$language plpgsql;omm=#
CREATE FUNCTION

omm=# call func_2(1, 0, 0);
(1 row)

omm=# result_1 | result_2
----------+----------
2 | 10

omm=# ALTER FUNCTION func_2(int) RENAME TO func_3;

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

omm=# \sf func_3
CREATE OR REPLACE FUNCTION public.func_3(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=#

select * from pg_proc where proname = 'func_3'; 

4.删除函数

DROP FUNCTION fun_1;

DROP FUNCTION func_3;