子程序
嵌套在程序(匿名块/存储过程/函数)中的存储过程或函数,可以显著降低业务代码复杂度。
语法格式
创建子程序
CREATE [OR REPLACE] PROCEDURE procedure_name [...] { IS | AS } [subprogram] BEGIN procedure_body END /
CREATE FUNCTION function_name [...] { IS | AS } [subprogram] [...] /
参数说明
subprogram
创建的子程序。
功能说明
- 子程序只能被父程序调用,其他程序无法调用
- 当前不支持包中的子程序
- 当前子程序仅支持三层嵌套
- 当前子程序中不支持自治事务
示例
--创建函数子程序
openGauss=# CREATE or replace FUNCTION func() returns int AS $$
openGauss$# DECLARE
openGauss$# q int :=0;
openGauss$# function sum1(a int, b int) return int
openGauss$# as
openGauss$# declare
openGauss$# res int:=0;
openGauss$# begin
openGauss$# res = a + b;
openGauss$# return res;
openGauss$# end;
openGauss$# BEGIN
openGauss$# q = sum1(3,4);
openGauss$# return q;
openGauss$# END;
openGauss$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
--调用子程序
openGauss=# call func();
func
------
7
(1 row)
--存储过程子程序
openGauss=# CREATE or replace PROCEDURE proc(a int, b int) AS
openGauss$# DECLARE
openGauss$# ans int:=0;
openGauss$# function sum1(c int) return int
openGauss$# as
openGauss$# declare
openGauss$# var int:=5;
openGauss$# function sum2() return int
openGauss$# as
openGauss$# declare
openGauss$#
openGauss$# res int:=0;
openGauss$# begin
openGauss$#
TRANSACTION WORK
openGauss$# res = res + c;
openGauss$# return res;
openGauss$# end;
openGauss$# begin
openGauss$# return sum2() + var + b;
openGauss$# end;
openGauss$# BEGIN
openGauss$# ans = sum1(a);
openGauss$# raise notice 'ans=%',ans;
openGauss$# END;
openGauss$# /
CREATE PROCEDURE
--调用子程序
openGauss=# select proc(1,2);
NOTICE: ans=8
CONTEXT: referenced column: proc
proc
------
(1 row)
--创建匿名块子程序
openGauss=# DECLARE
FUNCTION square (original NUMBER)
RETURN NUMBER
AS
original_squared NUMBER;
BEGIN
original_squared := original * original;
RETURN original_squared;
END;
BEGIN
raise notice '%',square(1);
END;
openGauss$# /
NOTICE: 1
ANONYMOUS BLOCK EXECUTE
意见反馈