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

This article mainly explains "what is the difference between Oracle and PostgreSQL". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the difference between Oracle and PostgreSQL".

Oracle

SQL > CREATE OR REPLACE function func_out (pi_in NUMBER,pi_out1 out number,pi_out2 out varchar2) 2 return date 3 as 4 v_date date; 5 begin 6 v_date: = sysdate; 7 pi_out1: = pi_in; 8 pi_out2: = pi_in; 9 return vdate10 end; 11 / function has been created. SQL > SQL > set serveroutput onSQL > declare 2 v_date date; 3 v_out1 number; 4 v_out2 varchar2; 5 begin 6 v_date: = func_out; 7 dbms_output.put_line ('v_date =' | v_date | |'; v_out1 ='| | v_out1 |'; v_out2 ='| v_out2); 8 end; 9 / v_date = 14-2 February-20 setter1 = 1 V_out2 = 1PL/SQL process completed successfully. SQL >

The output parameters are number and varchar2, respectively, and the function returns the date type.

PostgreSQL

[local:/data/run/pg12]: 5120 pg12@testdb=# CREATE OR REPLACE function func_out (pi_in int,pi_out1 out int,pi_out2 out text) pg12@testdb-# returns datepg12@testdb-# aspg12@testdb-# $$pg12@testdb$# declarepg12@testdb$# v_date date;pg12@testdb$# begin pg12@testdb$# v_date: = current_date (); pg12@testdb$# pi_out1: = pi_in;pg12@testdb$# pi_out2: = to_char (pi_in) Pg12@testdb$# return vault; 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=#

Prompt that the result type must be record

[local:/data/run/pg12]: 5120 pg12@testdb=# CREATE OR REPLACE function func_out (pi_in int,pi_out1 out int,pi_out2 out text) pg12@testdb-# returns recordpg12@testdb-# aspg12@testdb-# $$pg12@testdb$# declarepg12@testdb$# v_date date;pg12@testdb$# begin pg12@testdb$# v_date: = current_date;pg12@testdb$# pi_out1: = pi_in;pg12@testdb$# pi_out2: = to_char (pi_in); pg12@testdb$# return null Pg12@testdb$# end;pg12@testdb$# $$LANGUAGE 'plpgsql';ERROR: RETURN cannot have a parameter in function with OUT parametersLINE 11: return null; ^ [local:/data/run/pg12]: 5120 pg12@testdb=#

When changed to record, a null value is returned, indicating that the return value is not allowed if there is an OUT parameter.

[local:/data/run/pg12]: 5120 pg12@testdb=# CREATE OR REPLACE function func_out (pi_in int,pi_out1 out int,pi_out2 out text) pg12@testdb-# returns recordpg12@testdb-# aspg12@testdb-# $$pg12@testdb$# declarepg12@testdb$# v_date date;pg12@testdb$# begin pg12@testdb$# v_date: = current_date;pg12@testdb$# pi_out1: = pi_in;pg12@testdb$# pi_out2: = pi_in;pg12@testdb$# return; pg12@testdb$# end Pg12@testdb$# $$LANGUAGE 'plpgsql';CREATE FUNCTION [local:/data/run/pg12]: 5120 pg12@testdb=#

Created successfully, try to call

[local:/data/run/pg12]: 5120 pg12@testdb=# dopg12@testdb-# $$pg12@testdb$# declarepg12@testdb$# v_ret record;pg12@testdb$# v_out1 int;pg12@testdb$# v_out2 text;pg12@testdb$# beginpg12@testdb$# v_ret: = func_out (1 pg12@testdb$# raise notice 'ret is:% Dobbs endpg12@testdb$# # endpg12@testdb$# $$pg12@testdb-#) ERROR: function func_out (integer, integer, text) does not existLINE 1: SELECT func_out. You might need to add explicit type casts.QUERY: SELECT func_out CONTEXT: PL/pgSQL function inline_code_block line 7 at assignment [local:/data/run/pg12]: 5120 pg12@testdb=#

Prompt that there is no corresponding function, because PG ignores the OUT parameter, removes the out parameter and calls it again

[local:/data/run/pg12]: 5120 pg12@testdb=# dopg12@testdb-# $$pg12@testdb$# declarepg12@testdb$# v_ret record;pg12@testdb$# v_out1 int;pg12@testdb$# v_out2 text;pg12@testdb$# beginpg12@testdb$# v_ret: = func_out (1); pg12@testdb$# raise notice 'ret is:%', endpg12@testdb$# $$ NOTICE: ret is: (1) DO [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=#

Record is returned, the first value is 1, and the second value is 1

1.PG ignores the output parameters, determines whether a function is the same function, and only determines the input parameters

two。 If there is an OUT parameter, the PG function cannot return the result, only through the OUT parameter.

Thank you for your reading, the above is the content of "what is the difference between Oracle and PostgreSQL". After the study of this article, I believe you have a deeper understanding of the difference between Oracle and PostgreSQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.