openGauss每日一练第15天 openGauss定义存储过程和函数
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.删除存储过程
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
```
