首页 > 其他分享 >【我和openGauss的故事】带有out参数的存储过程及自定义函数的重载测试

【我和openGauss的故事】带有out参数的存储过程及自定义函数的重载测试

时间:2023-08-07 10:33:52浏览次数:77  
标签:p1 自定义 int testdb test openGauss outparam out

【我和openGauss的故事】带有out参数的存储过程及自定义函数的重载测试

DarkAthena openGauss 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='';
SET
testdb=# show behavior_compat_options;
 behavior_compat_options
-------------------------

(1 row)

testdb=# create procedure test_outparam_1(p1 int,p2 out int) is
testdb$# begin
testdb$# raise notice 'this is test_outparam_1(p1 int,p2 out int)';
testdb$# end;
testdb$# /
CREATE PROCEDURE
testdb=# create procedure test_outparam_1(p1 int,p2 out int,p3 out int) is
testdb$# begin
testdb$# 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 types
testdb=#

2.用create or replace procedure,会直接覆盖同名procedure

testdb=# create or replace procedure test_outparam_1(p1 int,p2 out int,p3 out int) is
testdb$# begin
testdb$# raise notice 'this is test_outparam_1(p1 int,p2 out int,p3 out int)';
testdb$# end;
testdb$# /
CREATE PROCEDURE
testdb=# declare
testdb-# x int;
testdb-# begin
testdb$# test_outparam_1(1,x);
testdb$# end;
testdb$# /
ERROR:  function "test_outparam_1" with 2 parameters doesn't exist
CONTEXT:  SQL statement "CALL test_outparam_1(1,x)"
PL/pgSQL function inline_code_block line 4 at SQL statement
testdb=# declare
testdb-# x int;
testdb-# y int;
testdb-# begin
testdb$# 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 EXECUTE
testdb=#

3.创建同名,但in参数类型不一致的procedure,发现也会将之前同名的procedure覆盖掉

testdb=# create or replace procedure test_outparam_1(p1 text,p2 out int,p3 out int) is
testdb$# begin
testdb$# raise notice 'this is test_outparam_1(p1 text,p2 out int,p3 out int)';
testdb$# end;
testdb$# /
CREATE PROCEDURE
testdb=# declare
testdb-# x int;
testdb-# y int;
testdb-# begin
testdb$# 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 EXECUTE
testdb=# \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='';
SET
testdb=# create function test_outparam_2(p1 int,p2 out int) return int is
testdb$# begin
testdb$# raise notice 'this is test_outparam_2(p1 int,p2 out int)';
testdb$# return 1;
testdb$# end;
testdb$# /
CREATE FUNCTION
testdb=# create function test_outparam_2(p1 int,p2 out int,p3 out int) return int is
testdb$# begin
testdb$# 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 types
testdb=#

2.用create or replace function ,会直接覆盖同名function

testdb=# create or replace function test_outparam_2(p1 int,p2 out int,p3 out int) return int is
testdb$# begin
testdb$# raise notice 'this is test_outparam_2(p1 int,p2 out int,p3 out int)';
testdb$# return 1;
testdb$# end;
testdb$# /
CREATE FUNCTION
testdb=# \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 is
testdb$# begin
testdb$# raise notice 'this is test_outparam_2(p1 text,p2 out int,p3 out int)';
testdb$# return 1;
testdb$# end;
testdb$# /
CREATE FUNCTION
testdb=# \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$# begin
testdb$# raise notice 'this is test_outparam_3(p1 int,p2 out int)';
testdb$# return 1;
testdb$# end;$$language plpgsql;
CREATE FUNCTION
testdb=# create function test_outparam_3(p1 int,p2 out int,p3 out int) returns int as $$
testdb$# begin
testdb$# 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 types
testdb=#

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$# begin
testdb$# raise notice 'this is test_outparam_3(p1 int,p2 out int,p3 out int)';
testdb$# return 1;
testdb$# end;$$language plpgsql;
CREATE FUNCTION
testdb=# \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$# begin
testdb$# raise notice 'this is test_outparam_3(p1 text,p2 out int,p3 out int)';
testdb$# return 1;
testdb$# end;$$language plpgsql;
CREATE FUNCTION
testdb=# \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='';
SET
testdb=# create package test_outparam_4 is
testdb$# 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:a
LINE 2: procedure a(p1 int,p2 out int);

2.打开选项,再次创建,不报错

testdb=# set behavior_compat_options='proc_outparam_override';
SET
testdb=# create package test_outparam_4 is
testdb$# 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 PACKAGE
testdb=#

3.关闭选项,入参类型不一致,出参一致,创建成功(打开参数时,情况一样)

testdb=# set behavior_compat_options='';
SET
testdb=# create or replace  package test_outparam_4 is
testdb$# procedure a(p1 int,p2 out int);
testdb$# procedure a(p1 text,p2 out int);
testdb$# end test_outparam_4;
testdb$# /
CREATE PACKAGE
testdb=#
testdb=# create type int_table is table of int;
CREATE TYPE
testdb=# create or replace  package test_outparam_4 is
testdb$# procedure a(p1 int,p2 out int);
testdb$# procedure a(p1 int_table,p2 out int);
testdb$# end test_outparam_4;
testdb$# /
CREATE PACKAGE
testdb=#

4.特殊情况1,procedure的名称和入参的自定义复合类型中的一个属性名一致时,会报错(此处与重载无关)

openGauss=# create type test_ttyy as (a int,b int);
CREATE TYPE
openGauss=# create or replace  package test_outparam_4 is
openGauss$# procedure a(p1 test_ttyy,p2 out int);
openGauss$# end test_outparam_4;
openGauss$# /
ERROR:  "a" is already an attribute of type test_ttyy
openGauss=#

5.特殊情况2,带inout的参数,在关闭重载选项时,只会判断in的参数个数,不会判断参数的数据类型,一个inout参数相当于一个in参数和一个out参数,下例两个procedure都只有一个in参数,因此创建报错;但打开重载选项后,可以创建成功,此时inout视为一个参数,判断重复的规则变成了 “存储过程名称+总参数个数+按相同顺序的参数inout类型+按相同顺序参数数据类型”

testdb=# create package test_out is
testdb$# procedure p1(a date,b out text);
testdb$# procedure p1(a inout int);
testdb$# end test_out;
testdb$# /
ERROR:  can not override out param:p1
LINE 2: procedure p1(a date,b out text);
testdb=# set behavior_compat_options='proc_outparam_override';
SET
testdb=# create or replace package test_out is
testdb$# procedure p1(a date,b out text);
testdb$# procedure p1(a inout int);
testdb$# end test_out;
testdb$# /
CREATE PACKAGE
testdb=# create or replace package test_out is
testdb$# procedure p1(a int,b out int);
testdb$# procedure p1(a inout int);
testdb$# end test_out;
testdb$# /
CREATE PACKAGE
testdb=# create or replace package test_out is
testdb$# 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='';
SET
testdb=# create or replace  package test_outparam_5 is
testdb$# 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';
SET
testdb=# create or replace  package test_outparam_5 is
testdb$# 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='';
SET
testdb=# create or replace  package test_outparam_5 is
testdb$# 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 PACKAGE
testdb=#

二、使用

PLSQL block中使用

1.开启重载选项,调用package中的procedure,重载的procedure均可正常获取出参

testdb=# set behavior_compat_options='proc_outparam_override';
SET
testdb=# create or replace  package test_outparam_6 is
testdb$# 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 PACKAGE
testdb=# create or replace  package body test_outparam_6 is
testdb$# procedure a(p1 int,p2 out int) is
testdb$# begin
testdb$# p2:=p1;
testdb$# end;
testdb$# procedure a(p1 int,p2 out int,p3 out int) is
testdb$# begin
testdb$# p2:=p1*10;
testdb$# p3:=p1*10;
testdb$# end;
testdb$# end test_outparam_6;
testdb$# /
CREATE PACKAGE BODY
testdb=# declare
testdb-# x1 int:=1;
testdb-# x2 int;
testdb-# y1 int:=2;
testdb-# y2 int;
testdb-# y3 int;
testdb-# begin
testdb$# 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,1
NOTICE:  2,20,20
ANONYMOUS BLOCK EXECUTE
testdb=#

2.关闭选项,调用包内带出参的procedure (无重载的情况),可正常获取出参

testdb=# set behavior_compat_options='';
SET
testdb=# create or replace  package test_outparam_6 is
testdb$# procedure a(p1 int,p2 out int);
testdb$# end test_outparam_6;
testdb$# /
CREATE PACKAGE
testdb=# create or replace  package body test_outparam_6 is
testdb$# procedure a(p1 int,p2 out int) is
testdb$# begin
testdb$# p2:=p1;
testdb$# end;
testdb$# end test_outparam_6;
testdb$# /
CREATE PACKAGE BODY
testdb=# declare
testdb-# x1 int:=1;
testdb-# x2 int;
testdb-# begin
testdb$# test_outparam_6.a(x1,x2);
testdb$# raise notice '%,%',x1,x2;
testdb$# end;
testdb$# /
NOTICE:  1,1
ANONYMOUS BLOCK EXECUTE
testdb=#

3.开启选项,调用包内带出参的函数,出参可正常获取

testdb=# set behavior_compat_options='proc_outparam_override';
SET
testdb=# create or replace package test_outparam_8 is
testdb$# function a(p1 int,p2 out int) return int;
testdb$# end test_outparam_8;
testdb$# /
CREATE PACKAGE
testdb=# create or replace package body test_outparam_8 is
testdb$# function a(p1 int,p2 out int) return int is
testdb$# begin
testdb$# p2:=p1;
testdb$# return p1+p2;
testdb$# end;
testdb$# end test_outparam_8;
testdb$# /
CREATE PACKAGE BODY
testdb=# declare
testdb-# x1 int:=1;
testdb-# x2 int;
testdb-# r int;
testdb-# begin
testdb$# r:=test_outparam_8.a(x1,x2);
testdb$# raise notice '%,%,%',x1,x2,r;
testdb$# end;
testdb$# /
NOTICE:  1,1,2
ANONYMOUS BLOCK EXECUTE
testdb=#

4.关闭选项,调用包内带出参的函数,无法获得out参数的值

testdb=# set behavior_compat_options='';
SET
testdb=# create or replace package test_outparam_8 is
testdb$# function a(p1 int,p2 out int) return int;
testdb$# end test_outparam_8;
testdb$# /
CREATE PACKAGE
testdb=# create or replace package body test_outparam_8 is
testdb$# function a(p1 int,p2 out int) return int is
testdb$# begin
testdb$# p2:=p1;
testdb$# return p1+p2;
testdb$# end;
testdb$# end test_outparam_8;
testdb$# /
CREATE PACKAGE BODY
testdb=# declare
testdb-# x1 int:=1;
testdb-# x2 int;
testdb-# r int;
testdb-# begin
testdb$# r:=test_outparam_8.a(x1,x2);
testdb$# raise notice '%,%,%',x1,x2,r;
testdb$# end;
testdb$# /
NOTICE:  1,<NULL>,2
ANONYMOUS BLOCK EXECUTE
testdb=#

SQL中使用

1.关闭重载选项,使用select 方式调用package中的procedure,传参只能传in参数,不能传out参数(带默认值的in参数可以不传)

testdb=# set behavior_compat_options='';
SET
testdb=# create or replace  package test_outparam_6 is
testdb$# procedure a(p1 int,p2 out int);
testdb$# end test_outparam_6;
testdb$# /
CREATE PACKAGE
testdb=# create or replace  package body test_outparam_6 is
testdb$# procedure a(p1 int,p2 out int) is
testdb$# begin
testdb$# p2:=p1;
testdb$# end;
testdb$# end test_outparam_6;
testdb$# /
CREATE PACKAGE BODY
testdb=# 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 exist
LINE 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: a
testdb=#

2.打开重载选项,使用select 方式调用package中的procedure,必须传满in和out参数,out参数只需要传对类型,值无实际意义(带默认值的in参数可以不传)

testdb=# set behavior_compat_options='proc_outparam_override';
SET
testdb=# create or replace  package test_outparam_6 is
testdb$# procedure a(p1 int,p2 out int);
testdb$# end test_outparam_6;
testdb$# /
CREATE PACKAGE
testdb=# create or replace  package body test_outparam_6 is
testdb$# procedure a(p1 int,p2 out int) is
testdb$# begin
testdb$# p2:=p1;
testdb$# end;
testdb$# end test_outparam_6;
testdb$# /
CREATE PACKAGE BODY
testdb=# select test_outparam_6.a(1);
ERROR:  function test_outparam_6.a(integer) does not exist
LINE 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: a
testdb=# select test_outparam_6.a(1,1);
 a
---
 1
(1 row)

3.获取多个out参数的方法

testdb=# set behavior_compat_options='';
SET
testdb=# create or replace  package test_outparam_6 is
testdb$# procedure a(p1 int,p2 out int,p3 out int);
testdb$# end test_outparam_6;
testdb$# /
CREATE PACKAGE
testdb=# create or replace  package body test_outparam_6 is
testdb$# procedure a(p1 int,p2 out int,p3 out int) is
testdb$# begin
testdb$# p2:=p1;
testdb$# p3:=p1*10;
testdb$# end;
testdb$# end test_outparam_6;
testdb$# /
CREATE PACKAGE BODY
testdb=# select * from test_outparam_6.a(1);
 p2 | p3
----+----
  1 | 10
(1 row)

testdb=# set behavior_compat_options='proc_outparam_override';--打开重载
SET
testdb=# select * from test_outparam_6.a(1);
ERROR:  function test_outparam_6.a(integer) does not exist
LINE 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='';
SET
testdb=# create or replace package test_outparam_8 is
testdb$# function a(p1 int,p2 out int) return int;
testdb$# end test_outparam_8;
testdb$# /
CREATE PACKAGE
testdb=# create or replace package body test_outparam_8 is
testdb$# function a(p1 int,p2 out int) return int is
testdb$# begin
testdb$# p2:=p1;
testdb$# return p1+p2;
testdb$# end;
testdb$# end test_outparam_8;
testdb$# /
CREATE PACKAGE BODY
testdb=# 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 exist
LINE 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: a
testdb=#

5.打开重载选项,使用select 方式调用带出参的package内function

testdb=# set behavior_compat_options='proc_outparam_override';
SET
testdb=# create or replace package test_outparam_8 is
testdb$# function a(p1 int,p2 out int) return int;
testdb$# end test_outparam_8;
testdb$# /
CREATE PACKAGE
testdb=# create or replace package body test_outparam_8 is
testdb$# function a(p1 int,p2 out int) return int is
testdb$# begin
testdb$# p2:=p1;
testdb$# return p1+p2;
testdb$# end;
testdb$# end test_outparam_8;
testdb$# /
CREATE PACKAGE BODY
testdb=# select test_outparam_8.a(1);
ERROR:  function test_outparam_8.a(integer) does not exist
LINE 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: a
testdb=# 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参数。

标签:p1,自定义,int,testdb,test,openGauss,outparam,out
From: https://blog.51cto.com/u_16191492/6991273

相关文章

  • 【我和openGauss的故事】使用Ora2Pg迁移oracle数据到openGauss
    【我和openGauss的故事】使用Ora2Pg迁移oracle数据到openGaussDemonCharm[openGauss](javascript:void(0);)2023-08-0418:01发表于四川前言本博客介绍了使用Ora2Pg迁移oracle数据到openGauss1、下载及安装Ora2Pg1.1、下载说明:PerlDBD:http://search.CPAN.org#只需在搜索......
  • 【我和openGauss的故事】为集群实现VIP
    【我和openGauss的故事】为集群增加VIPlqkittenopenGauss2023-08-0418:01发表于四川openGauss发布以来,原生支持一主多备,RTO<10S,高可用性能大大增强。自openGauss3.0开始,更新了集群管理套件CM,易用性也得到了提高。但对于客户端来说,数据库端的切换,需要手工完成。openGauss增加VI......
  • MTK 2.9.2 迁移Oracle 11g 至 openGauss 5.0.0操作指南
    MTK2.9.2迁移Oracle11g至openGauss5.0.0操作指南尚雷openGauss2023-08-0418:01发表于四川前言:最近在进行一些去O的验证测试,之前测试过MTK迁移Oracle到MogDB,正好测试下Oracle到openGauss的迁移,于是做了如下测试,并整理记录成文,还望对此熟悉的朋友多多指正。一、简介MTK全......
  • 参数ref、out浅析
    out和ref是C#语言中传递参数的关键字,用于在方法调用中修改参数值。它们分别用于输出参数和引用参数。但都可以用于优化代码性能和减少内存分配。out:只出不进,可用于在方法调用中设置输出参数。输出参数是在方法调用过程中分配并返回给调用方的参数。输出参数必须在函数体内初......
  • Zig 自定义字符类型
    Zig自定义字符类型在Zig​中整数型有无符号Unsigned​有符号Signed​在默认情况下生命的整数型变量都是有符号的类型。长度有符号类型无符号类型8位​i8​​u8​16位​i16​​u16​32位​i32​​u32​64位​i64​​u64​128位​i128......
  • vue-router addRoute将子路由添加到指定路由下
    router.addRoute()可以向vue路由中动态的添加路由信息,但,路由存在多层级关系,例如,最开始的路由是这样的:{path:'/',name:'layout',component:resolve=>require(['../layout'],resolve),meta:{title:'',handleStatus:......
  • traceroute nslookup dig用法
    traceroutenslookupdig用法1,traceroute路由追踪格式:tracerouteIP地址[root@localhost~]#traceroute192.168.1.200tracerouteto192.168.1.200(192.168.1.200),30hopsmax,60bytepackets1 192.168.1.200(192.168.1.200) 0.417ms!X 0.293ms!X 0.178......
  • route用法
    route用法查看或设置主机中路由表信息1,查看路由表信息routeroute[-n]-n:将路由记录中的地址信息显示为数字形式#路由表主要构成:#Destination:目标网络ID,表示可以到达的目标网络ID,0.0.0.0/0表示所有未知网络,又称为默认路由,优先级最低#Genmask:目标网络对应的netmas......
  • FreeSWITCH添加自定义endpoint之媒体交互
    操作系统:CentOS7.6_x64FreeSWITCH版本:1.10.9 之前写过FreeSWITCH添加自定义endpoint的文章:https://www.cnblogs.com/MikeZhang/p/fsAddEndpoint20230528.html今天记录下endpoint媒体交互的过程并提供示例代码及相关资源下载,本文涉及示例代码和资源可从如下渠道获取:关......
  • The server quit without updating PID file
    root@ubuntu:/usr/local/mysql#./bin/mysql.serverrestartShuttingdownMySQL..*StartingMySQL......*TheserverquitwithoutupdatingPIDfile(/usr/local/mysql-5.7.21-linux-glibc2.12-x86_64/data/ubuntu.pid). [mysqld] prompt=(\u@\h)[\d]>\  ......