openGauss每日一练第15天 加油

openGauss每日一练第15天 加油

1.创建存储过程

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

–查看存储过程

select * from pg_proc where proname = ‘procedure_insert’;

2.调用存储过程

select procedure_insert(1,100);
select * from item;

3.删除存储过程

drop procedure if exists procedure_insert;

4.创建函数

–创建一个函数func_add_sql，计算两个整数的和，并返回结果

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

–查看函数定义

\sf func_add_sql
select * from pg_proc where proname = ‘func_add_sql’;
–按参数值传递。

CALL func_add_sql(1, 3);
–使用命名标记法传参。
CALL func_add_sql(num1 => 1,num2 => 3);
CALL func_add_sql(num2 := 2, num1 := 3);
–创建带出参的函数

CREATE FUNCTION func_increment_sql(num1 IN integer, num2 IN integer, res OUT integer)
RETURN integer
AS
BEGIN
res := num1 + num2;
END;
/
–出参传入常量

CALL func_increment_sql(1,2,1);
–创建返回RECORD类型的函数

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; call func_increment_sql1(1, 0, 0);

5.修改函数定义

–重命名 ALTER FUNCTION func_add_sql(INTEGER, INTEGER) RENAME TO add_two_number;

6.删除函数

DROP FUNCTION add_two_number; Drop function func_increment_sql1; Drop function func_increment_sql; 

课后作业

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