今天学习openGauss定义存储过程和函数
1.创建存储过程
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，调用函数时使用按参数值传递和命名标记法传参
create function fun_add(num1 in integer,num2 in integer,result out integer)
return integer
as
begin
result := num1+num2;
end;
/
CREATE FUNCTION
\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 functionfunction DECLARE begin result := num1+num2; endfunctionfunction;
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 functionfunction begin result1 = num * 10; result2 = num * 20; return next; end; functionfunction; 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
