Shulou(Shulou.com)05/31 Report--

This article focuses on "what is the difference between the built-in programming languages of Oracle and PostgreSQL". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let the editor take you to learn "what's the difference between Oracle and PostgreSQL's built-in programming languages?"

Both Oracle and PostgreSQL provide a built-in programming language (PL/SQL vs PL/pgSQL), which is quite different in the declaration of input and output parameters. For example, if there is an inout/out parameter in the input parameter, the function of Oracle can have a return value, but PG does not allow a return value, only through the parameter.

Oracle

Create function

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.

Execution

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

Cannot be executed in SQL, but can be executed in PL/SQL

TEST-orcl@DESKTOP-V430TU3 > set serveroutput onTEST-orcl@DESKTOP-V430TU3 > declare 2 p4 number; 3 ret varchar2; 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

PostgreSQL

Create a function with a parameter of type inout, the return value must be the same as the parameter type, and return cannot return the actual value.

[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$# beginpg12@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$# beginpg12@testdb$# return 1 Pg12@testdb$# end;pg12@testdb$# $$LANGUAGE 'plpgsql';ERROR: RETURN cannot have a parameter in function with OUT parametersLINE 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$# beginpg12@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=#

If there is more than one out parameter in the parameter, the record type is required.

[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_testparameterselect 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$# beginpg12@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 recordpg12@testdb-# AS $$pg12@testdb$# beginpg12@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_testparameterList of functions- [RECORD 1]-+-- Schema | | publicName | sf_testparameterResult data type | recordArgument data types | p1 text | P2 integer, p3 text, INOUT p4 integer, OUT p5 textType | 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) does not existLINE 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=#

No matter how many out parameters are classified as return parameters, they do not need to be input as parameters when called, so it is no wonder that PG requires that the record type be returned.

[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 recordpg12@testdb-# AS $$pg12@testdb$# beginpg12@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=# here, I believe you have a better understanding of "what is the difference between Oracle and PostgreSQL's built-in programming languages". You might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.