vs oracle带参数更新,Oracle vs PostgreSQL Develop（23） - PL(pg)sql(参数声明)

Oracle和PostgreSQL都提供了内置的编程语言(PL/SQL vs PL/pgSQL)，在输入输出参数的声明上有较大的不同，如输入参数中存在inout/out参数，Oracle的函数可以有返回值，但PG不允许有返回值，只能通过参数返回。

**Oracle**

创建函数

```

TEST-orcl@DESKTOP-V430TU3>CREATE or replace FUNCTION sf_testparameter(p1 varchar2,p2 int,p3 varchar2,p4 in out int)

2 RETURN varchar2

3 is

4 begin

5 p4 := 1;

6 return 'test';

7 end;

8 /

Function created.

```

执行

```

TEST-orcl@DESKTOP-V430TU3>select sf_testparameter(null,null,null,:p4) from dual;

select sf_testparameter(null,null,null,:p4) from dual

*

ERROR at line 1:

ORA-06572: Function SF_TESTPARAMETER has out arguments

```

不能在SQL中执行，但可以在PL/SQL中执行

```

TEST-orcl@DESKTOP-V430TU3>set serveroutput on

TEST-orcl@DESKTOP-V430TU3>declare

2 p4 number;

3 ret varchar2(100);

4 begin

5 ret := sf_testparameter(null,null,null,p4);

6 dbms_output.put_line('p4 = '||p4||',ret='||ret);

7 end;

8 /

p4 = 1,ret=test

```

**PostgreSQL**

创建函数，参数中存在inout类型的参数，返回值必须与该参数类型一样，而且return不能返回实际值。

```

[local:/data/run/pg12]:5120 pg12@testdb=# CREATE or replace FUNCTION sf_testparameter(p1 text,p2 int,p3 text,inout p4 int)

pg12@testdb-# RETURNS text

pg12@testdb-# AS $$

pg12@testdb$# begin

pg12@testdb$# return 'test';

pg12@testdb$# end;

pg12@testdb$# $$ LANGUAGE 'plpgsql';

ERROR: function result type must be integer because of OUT parameters

[local:/data/run/pg12]:5120 pg12@testdb=#

[local:/data/run/pg12]:5120 pg12@testdb=# drop function sf_testparameter;

ERROR: could not find a function named "sf_testparameter"

[local:/data/run/pg12]:5120 pg12@testdb=# CREATE or replace FUNCTION sf_testparameter(p1 text,p2 int,p3 text,inout p4 int)

pg12@testdb-# RETURNS int

pg12@testdb-# AS $$

pg12@testdb$# begin

pg12@testdb$# return 1;

pg12@testdb$# end;

pg12@testdb$# $$ LANGUAGE 'plpgsql';

ERROR: RETURN cannot have a parameter in function with OUT parameters

LINE 5: return 1;

^

[local:/data/run/pg12]:5120 pg12@testdb=#

[local:/data/run/pg12]:5120 pg12@testdb=# CREATE or replace FUNCTION sf_testparameter(p1 text,p2 int,p3 text,inout p4 int)

pg12@testdb-# RETURNS int

pg12@testdb-# AS $$

pg12@testdb$# begin

pg12@testdb$# return;

pg12@testdb$# end;

pg12@testdb$# $$ LANGUAGE 'plpgsql';

CREATE FUNCTION

[local:/data/run/pg12]:5120 pg12@testdb=# select sf_testparameter(null,null,null,null);

sf_testparameter

------------------

(1 row)

[local:/data/run/pg12]:5120 pg12@testdb=#

```

如参数中有多个out参数，则要求返回record类型

```

[local:/data/run/pg12]:5120 pg12@testdb=# drop function sf_testparameter;

ION sf_testparameter(p1 text,p2 int,p3 text,inout p4 int,out p5 text)

RETURNS record

AS $$

begin

return;

end;

$$ LANGUAGE 'plpgsql';

\df sf_testparameter

select sf_testparameter(null,null,null,null,null);

select sf_testparameter(null,null,null,null);

DROP FUNCTION

[local:/data/run/pg12]:5120 pg12@testdb=# CREATE or replace FUNCTION sf_testparameter(p1 text,p2 int,p3 text,inout p4 int,out p5 text)

pg12@testdb-# RETURNS text

pg12@testdb-# AS $$

pg12@testdb$# begin

pg12@testdb$# return 'test';

pg12@testdb$# end;

pg12@testdb$# $$ LANGUAGE 'plpgsql';

ERROR: function result type must be record because of OUT parameters

[local:/data/run/pg12]:5120 pg12@testdb=#

[local:/data/run/pg12]:5120 pg12@testdb=# drop function sf_testparameter;

ERROR: could not find a function named "sf_testparameter"

[local:/data/run/pg12]:5120 pg12@testdb=# CREATE or replace FUNCTION sf_testparameter(p1 text,p2 int,p3 text,inout p4 int,out p5 text)

pg12@testdb-# RETURNS record

pg12@testdb-# AS $$

pg12@testdb$# begin

pg12@testdb$# return;

pg12@testdb$# end;

pg12@testdb$# $$ LANGUAGE 'plpgsql';

CREATE FUNCTION

[local:/data/run/pg12]:5120 pg12@testdb=#

[local:/data/run/pg12]:5120 pg12@testdb=# \df sf_testparameter

List of functions

-[ RECORD 1 ]-------+------------------------------------------------------------

Schema | public

Name | sf_testparameter

Result data type | record

Argument data types | p1 text, p2 integer, p3 text, INOUT p4 integer, OUT p5 text

Type | func

[local:/data/run/pg12]:5120 pg12@testdb=#

[local:/data/run/pg12]:5120 pg12@testdb=# select sf_testparameter(null,null,null,null,null);

ERROR: function sf_testparameter(unknown, unknown, unknown, unknown, unknown) does not exist

LINE 1: select sf_testparameter(null,null,null,null,null);

^

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

[local:/data/run/pg12]:5120 pg12@testdb=# select sf_testparameter(null,null,null,null);

sf_testparameter

------------------

(,)

(1 row)

[local:/data/run/pg12]:5120 pg12@testdb=#

```

不管有多少个out参数，都归为return参数，在调用时不需要作为参数输入，难怪PG要求返回record类型。

```

[local:/data/run/pg12]:5120 pg12@testdb=# drop function sf_testparameter;

DROP FUNCTION

[local:/data/run/pg12]:5120 pg12@testdb=# CREATE or replace FUNCTION sf_testparameter(p1 text,p2 int,p3 text,inout p4 int,out p5 text,out p6 text)

pg12@testdb-# RETURNS record

pg12@testdb-# AS $$

pg12@testdb$# begin

pg12@testdb$# return;

pg12@testdb$# end;

pg12@testdb$# $$ LANGUAGE 'plpgsql';

CREATE FUNCTION

[local:/data/run/pg12]:5120 pg12@testdb=# select sf_testparameter(null,null,null,null);

sf_testparameter

------------------

(,,)

(1 row)

[local:/data/run/pg12]:5120 pg12@testdb=#

[local:/data/run/pg12]:5120 pg12@testdb=#

```

**参考资料**

N/A