openGauss每日一练第15天|《openGauss存储过程》学习心得体会和课后练习

openGauss每日一练第15天|《openGauss存储过程》学习心得体会和课后练习

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

课程学习

连接数据库

#第一次进入等待15秒
#数据库启动中...
su - omm
gsql -r

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;
/

omm=# \df procedure_insert
                                                List of functions
 Schema |       Name       | Result data type | Argument data types  |  Type  | fencedmode | p
ropackage | prokind 
--------+------------------+------------------+----------------------+--------+------------+--
----------+---------
 public | procedure_insert | void             | a integer, b integer | normal | f          | f
          | p
(1 row)

–查看存储过程

select * from pg_proc where proname = 'procedure_insert';
omm=# select * from pg_proc where proname = 'procedure_insert';
-[ RECORD 1 ]----+---------------------------------------
proname          | procedure_insert
pronamespace     | 2200
proowner         | 10
prolang          | 11750
procost          | 100
prorows          | 0
provariadic      | 0
protransform     | -
proisagg         | f
proiswindow      | f
prosecdef        | f
proleakproof     | f
proisstrict      | f
proretset        | f
provolatile      | v
pronargs         | 2
pronargdefaults  | 0
prorettype       | 2278
proargtypes      | 23 23
proallargtypes   | 
proargmodes      | 
proargnames      | {a,b}
proargdefaults   | 
prosrc           |  DECLARE 
                 | begin
                 |         insert into item values(a, b);
                 | end
probin           | 
proconfig        | 
proacl           | 
prodefaultargpos | 
fencedmode       | f
proshippable     | f
propackage       | f
prokind          | p

2.调用存储过程

select procedure_insert(1,100);
select * from item;
omm=# select * from item;
-[ RECORD 1 ]
c1 | 1
c2 | 100

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';

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';
-[ RECORD 1 ]----+--------------------
proname          | func_add_sql
pronamespace     | 2200
proowner         | 10
proretset        | f
provolatile      | v
pronargs         | 2
prolang          | 11750
procost          | 100
prorows          | 0
provariadic      | 0
protransform     | -
proisagg         | f
proiswindow      | f
prosecdef        | f
proleakproof     | f
proisstrict      | f
proallargtypes   | 
proargmodes      | 
proargnames      | {num1,num2}
--More--pronargdefaults  | 0
prorettype       | 23
proargtypes      | 23 23
proargdefaults   | 
prosrc           |  DECLARE 
                 | BEGIN
                 | RETURN num1 + num2;
                 | END
probin           | 
proconfig        | 
proacl           | 
prodefaultargpos | 
fencedmode       | f
proshippable     | f
propackage       | f
prokind          | f

–按参数值传递。

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，调用函数时使用按参数值传递和命名标记法传参

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

CALL test_fun(1, 2);
CALL test_fun(num1 => 1,num2 => 2);
CALL test_fun(num2 := 2, num1 := 2);

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

CREATE OR REPLACE FUNCTION record_test(i int, out result_1 bigint, out result_2bigint)
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);

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

\sf record_test
select * from pg_proc where name ~ 'record_test'

4.删除函数

drop FUNCTION record_test;