【我和 openGauss 的故事】带有 out 参数的存储过程及自定义函数的重载测试
DarkAthena [openGauss](javascript:void(0);) 2023-08-04 18:01 发表于四川
背景
先说下数据库里说的函数重载是什么。比如我们知道数据库可能会有同名的函数，但是函数的参数声明不一样
select to_char(sysdate,'yyyymmdd') from dual;select to_char(0.1,'fm9990.00') from dual;
复制代码
上面这种情况就叫函数的重载，虽然函数名一致，但实际在数据库中对应的是不同的函数。
在 ORACLE 中，本身并不支持同名对象，ORACLE 中的函数重载，基本都是在 PACKAGE 中实现的，比如上面的 to_char 函数，其实是在一个名为"STANDARD"的 PACKAGE 中。
然后在 ORACLE 中，对于是否重载的判断，会同时考虑出入参的个数和类型，因此支持这样的重载
procedure proc1(p1 number,p2 varchar2,p3 out number);procedure proc1(p1 number,p2 varchar2);
复制代码
而在 openGauss 中，支持在非 package 中重载存储过程，默认情况是只考虑存储过程名称以及入参的匹配，即上述两个同名存储过程由于都是有两个入参，且类型一致，会被识别为同一个存储过程，即不能重载。不过，在 openGauss 中，有参数来控制是否支持重载
相关参数
官方文档：https://docs.opengauss.org/zh/docs/5.0.0/docs/DatabaseReference/%E5%B9%B3%E5%8F%B0%E5%92%8C%E5%AE%A2%E6%88%B7%E7%AB%AF%E5%85%BC%E5%AE%B9%E6%80%A7.html
set behavior_compat_options='proc_outparam_override';
复制代码
官方文档中，对此参数的说明为
控制存储过程出参的重载行为，打开该参数后，对于存储过程只有 out 出参部分不同的情况下，也可以正常调用。
结合我上面提到的那个重载的例子，这段话就好理解了。
但是，实际上 proc_outparam_override 这个选项不仅如此
测试
一、创建
A、package 外的 procedure
1.关闭选项，创建 in 参数一致但 out 参数不一致的同名 procedure，报错
testdb=# set behavior_compat_options='';SETtestdb=# show behavior_compat_options; behavior_compat_options-------------------------
(1 row)
testdb=# create procedure test_outparam_1(p1 int,p2 out int) istestdb$# begintestdb$# raise notice 'this is test_outparam_1(p1 int,p2 out int)';testdb$# end;testdb$# /CREATE PROCEDUREtestdb=# create procedure test_outparam_1(p1 int,p2 out int,p3 out int) istestdb$# begintestdb$# raise notice 'this is test_outparam_1(p1 int,p2 out int,p3 out int)';testdb$# end;testdb$# /ERROR: function "test_outparam_1" already exists with same argument typestestdb=#
复制代码
2.用 create or replace procedure，会直接覆盖同名 procedure
testdb=# create or replace procedure test_outparam_1(p1 int,p2 out int,p3 out int) istestdb$# begintestdb$# raise notice 'this is test_outparam_1(p1 int,p2 out int,p3 out int)';testdb$# end;testdb$# /CREATE PROCEDUREtestdb=# declaretestdb-# x int;testdb-# begintestdb$# test_outparam_1(1,x);testdb$# end;testdb$# /ERROR: function "test_outparam_1" with 2 parameters doesn't existCONTEXT: SQL statement "CALL test_outparam_1(1,x)"PL/pgSQL function inline_code_block line 4 at SQL statementtestdb=# declaretestdb-# x int;testdb-# y int;testdb-# begintestdb$# test_outparam_1(1,x,y);testdb$# end;testdb$# /NOTICE: this is test_outparam_1(p1 int,p2 out int,p3 out int)ANONYMOUS BLOCK EXECUTEtestdb=#
复制代码
3.创建同名，但 in 参数类型不一致的 procedure,发现也会将之前同名的 procedure 覆盖掉
testdb=# create or replace procedure test_outparam_1(p1 text,p2 out int,p3 out int) istestdb$# begintestdb$# raise notice 'this is test_outparam_1(p1 text,p2 out int,p3 out int)';testdb$# end;testdb$# /CREATE PROCEDUREtestdb=# declaretestdb-# x int;testdb-# y int;testdb-# begintestdb$# test_outparam_1(1,x,y);testdb$# end;testdb$# /NOTICE: this is test_outparam_1(p1 text,p2 out int,p3 out int)ANONYMOUS BLOCK EXECUTEtestdb=# \df test_outparam_1 List of functions Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind--------+-----------------+------------------+-----------------------------------------+--------+------------+------------+--------- public | test_outparam_1 | record | p1 text, OUT p2 integer, OUT p3 integer | normal | f | f | p(1 row)
testdb=#
复制代码
4.打开选项后，上述测试结果不会发生变化
B、package 外的 function(ORACLE 语法风格)
1.关闭选项，创建 in 参数一致但 out 参数不一致的同名 function，报错
testdb=# set behavior_compat_options='';SETtestdb=# create function test_outparam_2(p1 int,p2 out int) return int istestdb$# begintestdb$# raise notice 'this is test_outparam_2(p1 int,p2 out int)';testdb$# return 1;testdb$# end;testdb$# /CREATE FUNCTIONtestdb=# create function test_outparam_2(p1 int,p2 out int,p3 out int) return int istestdb$# begintestdb$# raise notice 'this is test_outparam_2(p1 int,p2 out int,p3 out int)';testdb$# return 1;testdb$# end;testdb$# /ERROR: function "test_outparam_2" already exists with same argument typestestdb=#
复制代码
2.用 create or replace function ,会直接覆盖同名 function
testdb=# create or replace function test_outparam_2(p1 int,p2 out int,p3 out int) return int istestdb$# begintestdb$# raise notice 'this is test_outparam_2(p1 int,p2 out int,p3 out int)';testdb$# return 1;testdb$# end;testdb$# /CREATE FUNCTIONtestdb=# \df test_outparam_2 List of functions Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind--------+-----------------+------------------+--------------------------------------------+--------+------------+------------+--------- public | test_outparam_2 | integer | p1 integer, OUT p2 integer, OUT p3 integer | normal | f | f | f(1 row)
testdb=#
复制代码
3.创建同名，但 in 参数类型不一致的 function,发现也会将之前同名的 function 覆盖掉
testdb=# create or replace function test_outparam_2(p1 text,p2 out int,p3 out int) return int istestdb$# begintestdb$# raise notice 'this is test_outparam_2(p1 text,p2 out int,p3 out int)';testdb$# return 1;testdb$# end;testdb$# /CREATE FUNCTIONtestdb=# \df test_outparam_2 List of functions Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind--------+-----------------+------------------+-----------------------------------------+--------+------------+------------+--------- public | test_outparam_2 | integer | p1 text, OUT p2 integer, OUT p3 integer | normal | f | f | f(1 row)
testdb=#
复制代码
4.打开选项后，上述测试结果不会发生变化
C、package 外的 function(PG 语法风格)
1.关闭选项，创建 in 参数一致但 out 参数不一致的同名 function,报错
testdb=# create function test_outparam_3(p1 int,p2 out int) returns int as $$testdb$# begintestdb$# raise notice 'this is test_outparam_3(p1 int,p2 out int)';testdb$# return 1;testdb$# end;$$language plpgsql;CREATE FUNCTIONtestdb=# create function test_outparam_3(p1 int,p2 out int,p3 out int) returns int as $$testdb$# begintestdb$# raise notice 'this is test_outparam_3(p1 int,p2 out int,p3 out int)';testdb$# return 1;testdb$# end;$$language plpgsql;ERROR: function "test_outparam_3" already exists with same argument typestestdb=#
复制代码
2.用 create or replace function ,会直接覆盖同名 function
testdb=# create or replace function test_outparam_3(p1 int,p2 out int,p3 out int) returns int as $$testdb$# begintestdb$# raise notice 'this is test_outparam_3(p1 int,p2 out int,p3 out int)';testdb$# return 1;testdb$# end;$$language plpgsql;CREATE FUNCTIONtestdb=# \df test_outparam_3 List of functions Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind--------+-----------------+------------------+--------------------------------------------+--------+------------+------------+--------- public | test_outparam_3 | integer | p1 integer, OUT p2 integer, OUT p3 integer | normal | f | f | f(1 row)
testdb=#
复制代码
3.创建同名，但 in 参数类型不一致的 function,此时不会覆盖原有同名函数
testdb=# create or replace function test_outparam_3(p1 text,p2 out int,p3 out int) returns int as $$testdb$# begintestdb$# raise notice 'this is test_outparam_3(p1 text,p2 out int,p3 out int)';testdb$# return 1;testdb$# end;$$language plpgsql;CREATE FUNCTIONtestdb=# \df test_outparam_3 List of functions Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind--------+-----------------+------------------+--------------------------------------------+--------+------------+------------+--------- public | test_outparam_3 | integer | p1 integer, OUT p2 integer, OUT p3 integer | normal | f | f | f public | test_outparam_3 | integer | p1 text, OUT p2 integer, OUT p3 integer | normal | f | f | f(2 rows)
testdb=#
复制代码
4.打开选项后，上述测试结果不会发生变化
D、PACKAGE 内的 procedure
1.关闭选项，入参一致，出参不一致的同名 procedure,创建报错
testdb=# set behavior_compat_options='';SETtestdb=# create package test_outparam_4 istestdb$# procedure a(p1 int,p2 out int);testdb$# procedure a(p1 int,p2 out int,p3 out int);testdb$# end test_outparam_4;testdb$# /ERROR: can not override out param:aLINE 2: procedure a(p1 int,p2 out int);
复制代码
2.打开选项，再次创建，不报错
testdb=# set behavior_compat_options='proc_outparam_override';SETtestdb=# create package test_outparam_4 istestdb$# procedure a(p1 int,p2 out int);testdb$# procedure a(p1 int,p2 out int,p3 out int);testdb$# end test_outparam_4;testdb$# /CREATE PACKAGEtestdb=#
复制代码
3.关闭选项，入参类型不一致，出参一致，创建成功(打开参数时，情况一样)
testdb=# set behavior_compat_options='';SETtestdb=# create or replace package test_outparam_4 istestdb$# procedure a(p1 int,p2 out int);testdb$# procedure a(p1 text,p2 out int);testdb$# end test_outparam_4;testdb$# /CREATE PACKAGEtestdb=#testdb=# create type int_table is table of int;CREATE TYPEtestdb=# create or replace package test_outparam_4 istestdb$# procedure a(p1 int,p2 out int);testdb$# procedure a(p1 int_table,p2 out int);testdb$# end test_outparam_4;testdb$# /CREATE PACKAGEtestdb=#
复制代码
4.特殊情况 1,procedure 的名称和入参的自定义复合类型中的一个属性名一致时，会报错（此处与重载无关）
openGauss=# create type test_ttyy as (a int,b int);CREATE TYPEopenGauss=# create or replace package test_outparam_4 isopenGauss$# procedure a(p1 test_ttyy,p2 out int);openGauss$# end test_outparam_4;openGauss$# /ERROR: "a" is already an attribute of type test_ttyyopenGauss=#
复制代码
5.特殊情况 2，带 inout 的参数，在关闭重载选项时，只会判断 in 的参数个数，不会判断参数的数据类型，一个 inout 参数相当于一个 in 参数和一个 out 参数，下例两个 procedure 都只有一个 in 参数，因此创建报错；但打开重载选项后，可以创建成功，此时 inout 视为一个参数，判断重复的规则变成了 “存储过程名称+总参数个数+按相同顺序的参数 inout 类型+按相同顺序参数数据类型”
testdb=# create package test_out istestdb$# procedure p1(a date,b out text);testdb$# procedure p1(a inout int);testdb$# end test_out;testdb$# /ERROR: can not override out param:p1LINE 2: procedure p1(a date,b out text);testdb=# set behavior_compat_options='proc_outparam_override';SETtestdb=# create or replace package test_out istestdb$# procedure p1(a date,b out text);testdb$# procedure p1(a inout int);testdb$# end test_out;testdb$# /CREATE PACKAGEtestdb=# create or replace package test_out istestdb$# procedure p1(a int,b out int);testdb$# procedure p1(a inout int);testdb$# end test_out;testdb$# /CREATE PACKAGEtestdb=# create or replace package test_out istestdb$# procedure p1(a int,b out int);testdb$# procedure p1(a inout int,b out int);testdb$# end test_out;testdb$# /CREATE PACKAGE
复制代码
E、PACKAGE 内的 function
1.关闭选项，入参一致，出参不一致的同名 function,创建报错
testdb=# set behavior_compat_options='';SETtestdb=# create or replace package test_outparam_5 istestdb$# function f1(p1 int,p2 out int) return int;testdb$# function f1(p1 int,p2 out text) return int;testdb$# end test_outparam_5;testdb$# /ERROR: function declared duplicate: f1
复制代码
2.打开选项，再次创建，报错发生变化
testdb=# set behavior_compat_options='proc_outparam_override';SETtestdb=# create or replace package test_outparam_5 istestdb$# function f1(p1 int,p2 out int) return int;testdb$# function f1(p1 int,p2 out text) return int;testdb$# end test_outparam_5;testdb$# /ERROR: "f1" functions with plpgsql language and out params are not supported Overloaded.DETAIL: N/A.testdb=#
复制代码
3.关闭选项，入参类型不一致，出参一致，创建成功(打开选项时，情况一样)
testdb=# set behavior_compat_options='';SETtestdb=# create or replace package test_outparam_5 istestdb$# function f1(p1 int,p2 out int) return int;testdb$# function f1(p1 text,p2 out int) return int;testdb$# end test_outparam_5;testdb$# /CREATE PACKAGEtestdb=#
复制代码
二、使用
PLSQL block 中使用
1.开启重载选项，调用 package 中的 procedure,重载的 procedure 均可正常获取出参
testdb=# set behavior_compat_options='proc_outparam_override';SETtestdb=# create or replace package test_outparam_6 istestdb$# procedure a(p1 int,p2 out int);testdb$# procedure a(p1 int,p2 out int,p3 out int);testdb$# end test_outparam_6;testdb$# /CREATE PACKAGEtestdb=# create or replace package body test_outparam_6 istestdb$# procedure a(p1 int,p2 out int) istestdb$# begintestdb$# p2:=p1;testdb$# end;testdb$# procedure a(p1 int,p2 out int,p3 out int) istestdb$# begintestdb$# p2:=p1*10;testdb$# p3:=p1*10;testdb$# end;testdb$# end test_outparam_6;testdb$# /CREATE PACKAGE BODYtestdb=# declaretestdb-# x1 int:=1;testdb-# x2 int;testdb-# y1 int:=2;testdb-# y2 int;testdb-# y3 int;testdb-# begintestdb$# test_outparam_6.a(x1,x2);testdb$# test_outparam_6.a(y1,y2,y3);testdb$# raise notice '%,%',x1,x2;testdb$# raise notice '%,%,%',y1,y2,y3;testdb$# end;testdb$# /NOTICE: 1,1NOTICE: 2,20,20ANONYMOUS BLOCK EXECUTEtestdb=#
复制代码
2.关闭选项，调用包内带出参的 procedure (无重载的情况)，可正常获取出参
testdb=# set behavior_compat_options='';SETtestdb=# create or replace package test_outparam_6 istestdb$# procedure a(p1 int,p2 out int);testdb$# end test_outparam_6;testdb$# /CREATE PACKAGEtestdb=# create or replace package body test_outparam_6 istestdb$# procedure a(p1 int,p2 out int) istestdb$# begintestdb$# p2:=p1;testdb$# end;testdb$# end test_outparam_6;testdb$# /CREATE PACKAGE BODYtestdb=# declaretestdb-# x1 int:=1;testdb-# x2 int;testdb-# begintestdb$# test_outparam_6.a(x1,x2);testdb$# raise notice '%,%',x1,x2;testdb$# end;testdb$# /NOTICE: 1,1ANONYMOUS BLOCK EXECUTEtestdb=#
复制代码
3.开启选项，调用包内带出参的函数，出参可正常获取
testdb=# set behavior_compat_options='proc_outparam_override';SETtestdb=# create or replace package test_outparam_8 istestdb$# function a(p1 int,p2 out int) return int;testdb$# end test_outparam_8;testdb$# /CREATE PACKAGEtestdb=# create or replace package body test_outparam_8 istestdb$# function a(p1 int,p2 out int) return int istestdb$# begintestdb$# p2:=p1;testdb$# return p1+p2;testdb$# end;testdb$# end test_outparam_8;testdb$# /CREATE PACKAGE BODYtestdb=# declaretestdb-# x1 int:=1;testdb-# x2 int;testdb-# r int;testdb-# begintestdb$# r:=test_outparam_8.a(x1,x2);testdb$# raise notice '%,%,%',x1,x2,r;testdb$# end;testdb$# /NOTICE: 1,1,2ANONYMOUS BLOCK EXECUTEtestdb=#
复制代码
4.关闭选项，调用包内带出参的函数,无法获得 out 参数的值
testdb=# set behavior_compat_options='';SETtestdb=# create or replace package test_outparam_8 istestdb$# function a(p1 int,p2 out int) return int;testdb$# end test_outparam_8;testdb$# /CREATE PACKAGEtestdb=# create or replace package body test_outparam_8 istestdb$# function a(p1 int,p2 out int) return int istestdb$# begintestdb$# p2:=p1;testdb$# return p1+p2;testdb$# end;testdb$# end test_outparam_8;testdb$# /CREATE PACKAGE BODYtestdb=# declaretestdb-# x1 int:=1;testdb-# x2 int;testdb-# r int;testdb-# begintestdb$# r:=test_outparam_8.a(x1,x2);testdb$# raise notice '%,%,%',x1,x2,r;testdb$# end;testdb$# /NOTICE: 1,<NULL>,2ANONYMOUS BLOCK EXECUTEtestdb=#
复制代码
SQL 中使用
1.关闭重载选项，使用 select 方式调用 package 中的 procedure，传参只能传 in 参数，不能传 out 参数（带默认值的 in 参数可以不传）
testdb=# set behavior_compat_options='';SETtestdb=# create or replace package test_outparam_6 istestdb$# procedure a(p1 int,p2 out int);testdb$# end test_outparam_6;testdb$# /CREATE PACKAGEtestdb=# create or replace package body test_outparam_6 istestdb$# procedure a(p1 int,p2 out int) istestdb$# begintestdb$# p2:=p1;testdb$# end;testdb$# end test_outparam_6;testdb$# /CREATE PACKAGE BODYtestdb=# select test_outparam_6.a(1); a--- 1(1 row)
testdb=# select test_outparam_6.a(1,1);ERROR: function test_outparam_6.a(integer, integer) does not existLINE 1: select test_outparam_6.a(1,1); ^HINT: No function matches the given name and argument types. You might need to add explicit type casts.CONTEXT: referenced column: atestdb=#
复制代码
2.打开重载选项，使用 select 方式调用 package 中的 procedure，必须传满 in 和 out 参数,out 参数只需要传对类型，值无实际意义（带默认值的 in 参数可以不传）
testdb=# set behavior_compat_options='proc_outparam_override';SETtestdb=# create or replace package test_outparam_6 istestdb$# procedure a(p1 int,p2 out int);testdb$# end test_outparam_6;testdb$# /CREATE PACKAGEtestdb=# create or replace package body test_outparam_6 istestdb$# procedure a(p1 int,p2 out int) istestdb$# begintestdb$# p2:=p1;testdb$# end;testdb$# end test_outparam_6;testdb$# /CREATE PACKAGE BODYtestdb=# select test_outparam_6.a(1);ERROR: function test_outparam_6.a(integer) does not existLINE 1: select test_outparam_6.a(1); ^HINT: No function matches the given name and argument types. You might need to add explicit type casts.CONTEXT: referenced column: atestdb=# select test_outparam_6.a(1,1); a--- 1(1 row)
复制代码
3.获取多个 out 参数的方法
testdb=# set behavior_compat_options='';SETtestdb=# create or replace package test_outparam_6 istestdb$# procedure a(p1 int,p2 out int,p3 out int);testdb$# end test_outparam_6;testdb$# /CREATE PACKAGEtestdb=# create or replace package body test_outparam_6 istestdb$# procedure a(p1 int,p2 out int,p3 out int) istestdb$# begintestdb$# p2:=p1;testdb$# p3:=p1*10;testdb$# end;testdb$# end test_outparam_6;testdb$# /CREATE PACKAGE BODYtestdb=# select * from test_outparam_6.a(1); p2 | p3----+---- 1 | 10(1 row)
testdb=# set behavior_compat_options='proc_outparam_override';--打开重载SETtestdb=# select * from test_outparam_6.a(1);ERROR: function test_outparam_6.a(integer) does not existLINE 1: select * from test_outparam_6.a(1); ^HINT: No function matches the given name and argument types. You might need to add explicit type casts.testdb=# select * from test_outparam_6.a(1,2,3); p2 | p3----+---- 1 | 10(1 row)
testdb=#
复制代码
4.关闭重载选项，使用 select 方式调用带出参的 package 内 function
testdb=# set behavior_compat_options='';SETtestdb=# create or replace package test_outparam_8 istestdb$# function a(p1 int,p2 out int) return int;testdb$# end test_outparam_8;testdb$# /CREATE PACKAGEtestdb=# create or replace package body test_outparam_8 istestdb$# function a(p1 int,p2 out int) return int istestdb$# begintestdb$# p2:=p1;testdb$# return p1+p2;testdb$# end;testdb$# end test_outparam_8;testdb$# /CREATE PACKAGE BODYtestdb=# select test_outparam_8.a(1); a--- 2(1 row)
testdb=# select test_outparam_8.a(1,1);ERROR: function test_outparam_8.a(integer, integer) does not existLINE 1: select test_outparam_8.a(1,1); ^HINT: No function matches the given name and argument types. You might need to add explicit type casts.CONTEXT: referenced column: atestdb=#
复制代码
5.打开重载选项，使用 select 方式调用带出参的 package 内 function
testdb=# set behavior_compat_options='proc_outparam_override';SETtestdb=# create or replace package test_outparam_8 istestdb$# function a(p1 int,p2 out int) return int;testdb$# end test_outparam_8;testdb$# /CREATE PACKAGEtestdb=# create or replace package body test_outparam_8 istestdb$# function a(p1 int,p2 out int) return int istestdb$# begintestdb$# p2:=p1;testdb$# return p1+p2;testdb$# end;testdb$# end test_outparam_8;testdb$# /CREATE PACKAGE BODYtestdb=# select test_outparam_8.a(1);ERROR: function test_outparam_8.a(integer) does not existLINE 1: select test_outparam_8.a(1); ^HINT: No function matches the given name and argument types. You might need to add explicit type casts.CONTEXT: referenced column: atestdb=# select test_outparam_8.a(1,1); a--- 2(1 row)
testdb=#
复制代码
总结
对于 openGauss 一、以下两条不会由于重载选项的差异发生行为改变
1.使用 ORACLE 风格语法的 procedure 和 function，创建规则和 ORACLE 一致，同名对象直接覆盖 2.使用 PG 风格的 FUNCTION，支持入参不一致的重载创建；对于入参一致，仅出参有差异的，会直接覆盖（如果 return 类型变化，创建会报错）
二、重载选项会控制 package 中的 procedure 能否创建仅出参不一致的重载，关闭选项时，重载规则为仅入参不匹配；开启选项时，重载规则为任意参数不匹配。
三、当有 inout 参数时，关闭重载选项，视为一个 in 参数和一个 out 参数，并且不再判断参数类型差异，重载必须要求入参个数不一致；开启重载选项时按上一条规则，即任意参数不匹配即可重载。
四、打开重载选项时，PLSQL 中调用 PROCEDURE 和 FUNCTION 方法和 ORACLE 一致，均可正常获取出参的值；关闭重载选项时，调用 FUNCTION 无法获得出参的值。
五、打开重载选项时，使用 SQL 查询 procedure 和 function，需要传满参数，包括所有的 in 和 out 参数；关闭重载选项时，只需要传 in 和 inout 参数。
评论