openGauss每日一练第15天 | 学习openGauss定义存储过程和函数-学习笔记

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

课程学习
连接数据库

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.创建存储过程
omm=# create table item(c1 int, c2 int);
CREATE TABLE
omm=# create procedure procedure_insert(a int, b int)
omm-# as
omm$# begin
omm$# insert into item values(a, b);
omm$# end;
omm$# /
CREATE PROCEDURE
–查看存储过程
omm=# select * from pg_proc where proname = 'procedure_insert';
2.调用存储过程
omm=#select procedure_insert(1,100);
omm=# procedure_insert
------------------

(1 row)

select * from item;
c1 | c2
----+-----
1 | 100
3.删除存储过程

omm=# drop procedure if exists procedure_insert;
DROP PROCEDURE

4.创建函数
–创建一个函数func_add_sql，计算两个整数的和，并返回结果
omm=# CREATE FUNCTION func_add_sql(num1 integer, num2 integer) RETURN integer
omm-# AS
omm$# BEGIN
omm$# END;
RETURN num1 + num2;
omm$# /
omm$# CREATE FUNCTION
–查看函数定义
omm=# \sf func_add_sql
CREATE OR REPLACE FUNCTION public.func_add_sql(num1 integer, num2 integer)
RETURNS integer
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $function$ DECLARE
BEGIN
RETURN num1 + num2;
END$function$;
omm=# select * from pg_proc where proname = 'func_add_sql';
–按参数值传递。
omm=# CALL func_add_sql(1, 3);
func_add_sql
--------------
4
(1 row)
--使用命名标记法传参。
omm=# CALL func_add_sql(num1 => 1,num2 => 3);
func_add_sql
--------------
4
(1 row)

omm=# CALL func_add_sql(num2 := 2, num1 := 3);
func_add_sql
--------------
5
(1 row)

–创建带出参的函数

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

–出参传入常量

omm=# CALL func_increment_sql(1,2,1);
res
-----
3
(1 row)
–创建返回RECORD类型的函数
omm=# CREATE OR REPLACE FUNCTION func_increment_sql1(i int, out result_1 bigint, out result_2 bigint)
omm-# returns SETOF RECORD
omm# as $$
omm-# begin
omm$# result_1 = i + 1;
omm$# result_2 = i * 10;
omm$# return next;
omm$# $$language plpgsql;
end;
CREATE FUNCTION
omm=# call func_increment_sql1(1, 0, 0);
omm=# result_1 | result_2
----------+----------
2 | 10
(1 row)
5.修改函数定义
–重命名
omm=# ALTER FUNCTION func_add_sql(INTEGER, INTEGER) RENAME TO add_two_number;
omm=# ALTER FUNCTION
6.删除函数
omm=# DROP FUNCTION add_two_number;
DROP FUNCTION
omm=# Drop function func_increment_sql1;
DROP FUNCTION
omm=# Drop function func_increment_sql;
DROP FUNCTION

课程作业
1.创建带有入参和出参的函数1，调用函数时使用按参数值传递和命名标记法传参
CREATE FUNCTION func_reduce1(num1 IN integer, num2 IN integer, res OUT integer)
RETURN integer
AS
BEGIN
res := num1 - num2;
END;
/
CALL func_reduce1(2,1,1);

CALL func_reduce1(num1 := 3, num2 := 1,res := 1);


omm=# CREATE FUNCTION func_reduce1(num1 IN integer, num2 IN integer, res OUT integer)
omm-# RETURN integer
omm-# AS
omm$# BEGIN
omm$# res := num1 - num2;
omm$# /END;
omm$#
CREATE FUNCTION
omm=# CALL func_reduce1(2,1,1);
res
-----
1
(1 row)
omm=# CALL func_reduce1(num1 := 3, num2 := 1,res := 1);
res
-----
2
(1 row)

2.创建返回类型为record的函数2，重命名函数2
CREATE OR REPLACE FUNCTION func_reduce2(i int, out result_1 bigint, out result_2 bigint)
returns SETOF RECORD
as $$
begin
result_1 = i - 1;
result_2 = i * 20;
return next;
end;
$$language plpgsql;
call func_reduce2(2, 0, 0);
ALTER FUNCTION func_reduce2(i int, out result_1 bigint, out result_2 bigint) RENAME TO func_reduce2_new;

omm=# omm-# CREATE OR REPLACE FUNCTION func_reduce2(i int, out result_1 bigint, out result_2 bigint)
returns SETOF RECORD
omm-# as $$
omm$# begin
omm$# result_1 = i - 1;
omm$# result_2 = i * 20;
omm$# return next;
omm$# omm$# end;
$$language plpgsql;
CREATE FUNCTION
omm=# call func_reduce2(2, 0, 0);
result_1 | result_2
----------+----------
1 | 40
(1 row)

omm=# ALTER FUNCTIONNCTION func_reduce2(i int, out result_1 bigint, out result_2 bigint) RENAME TO func_reduce2_new;omm=#

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


select * from pg_proc where proname = 'func_reduce1';
select * from pg_proc where proname = 'func_reduce2_new';

omm=# \sf func_reduce1
CREATE OR REPLACE FUNCTION public.func_reduce1(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=# \sf func_reduce2_new
CREATE OR REPLACE FUNCTION public.func_reduce2_new(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 * 20;
return next;
end;
$function$;

omm=#select * from pg_proc where proname = 'func_reduce1';

omm=#select * from pg_proc where proname = 'func_reduce2_new';
4.删除函数
Drop function func_reduce1;
Drop function func_reduce2_new;
omm=# Drop function func_reduce1;
DROP FUNCTION
omm=# Drop function func_reduce2_new;
DROP FUNCTION