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

This article focuses on "what are the similarities and differences between function overloading Oracle and PG". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the similarities and differences between function overloading Oracle and PG?"

Oracle

Oracle does not support function overloading, a function name can only correspond to one function.

The SQL > drop function func_1; function has been removed. SQL > CREATE function func_1 (p_in NUMBER,p_out1 out number,p_out2 out varchar2) 2 return date 3 as 4 begin 5 return sysdate; 6 end; 7 / function has been created. SQL >-- errorSQL > CREATE function func_1 (p_in1 NUMBER,p_out1 out number,p_in2 in varchar2) 2 return date 3 as 4 begin 5 return sysdate; 6 end 7 / CREATE function func_1 (p_in1 NUMBER,p_out1 out number,p_in2 in varchar2) * Line 1 error: ORA-00955: the name has been used by the existing object SQL > SQL >-- errorSQL > CREATE function func_1 (p_in1 NUMBER,p_out1 out number,p_in2 in varchar2,p_out2 out number) 2 return date 3 as 4 begin 5 return sysdate; 6 end 7 / CREATE function func_1 (p_in1 NUMBER,p_out1 out number,p_in2 in varchar2,p_out2 out number) * an error occurred on line 1: ORA-00955: the name has been used by an existing object SQL >

Although the type of parameters is different or the number of parameters is different, Oracle can replace functions directly.

SQL > CREATE or replace function func_1 (p_in1 NUMBER,p_out1 out number,p_in2 in varchar2) 2 return date 3 as 4 begin 5 return sysdate; 6 end; 7 / function has been created. SQL > CREATE or replace function func_1 (p_in1 NUMBER,p_out1 out number,p_in2 in varchar2,p_out2 out number) 2 return date 3 as 4 begin 5 return sysdate; 6 end; 7 / function has been created. SQL > select object_name from dba_objects where object_name = 'FUNC_1';OBJECT_NAME----FUNC_1SQL >

Function overloading can be achieved through Package.

SQL >-- packageSQL > create or replace package pk_overload 2 as 3 function func_1 (p_in1 NUMBER,p_out1 out number,p_out2 out varchar2) return date; 4 function func_1 (p_in1 NUMBER,p_out1 out number,p_in2 in varchar2) return date; 5 function func_1 (p_in1 NUMBER,p_out1 out number,p_in2 in varchar2,p_out2 out number) return date; 6 end; 7 / package has been created. SQL > SQL > create or replace package body pk_overload 2 as 3 function func_1 (p_in1 NUMBER,p_out1 out number,p_out2 out varchar2) return date 4 is 5 begin 6 null; 7 end; 8 9 function func_1 (p_in1 NUMBER,p_out1 out number,p_in2 in varchar2) return date 10 is 11 begin 12 null; 13 end; 14 15 function func_1 (p_in1 NUMBER,p_out1 out number,p_in2 in varchar2,p_out2 out number) return date 16 is 17 begin 18 null; 19 end 20 21 end; 22 / the package body has been created. SQL >

PostgreSQL

PG can realize function overloading at the function level.

[local:/data/run/pg12]: 5120 pg12@testdb=#-- overload [local:/data/run/pg12]: 5120 pg12@testdb=# CREATE OR REPLACE function func_overload (p_in int,p_out1 out int,p_out2 out text) pg12@testdb-# returns recordpg12@testdb-# aspg12@testdb-# $$pg12@testdb$# declarepg12@testdb$# begin pg12@testdb$# raise notice 'func is: in,out,out';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=# CREATE OR REPLACE function func_overload (p_in1 int,p_out1 out int,p_in2 in text) pg12@testdb-# returns intpg12@testdb-# aspg12@testdb-# $$pg12@testdb$# declarepg12@testdb$# begin pg12@testdb$# raise notice 'func is: in,out,in';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=# CREATE OR REPLACE function func_overload (p_in1 int,p_out1 out int,p_out2 out text,pi_in2 in varchar) pg12@testdb-# returns recordpg12@testdb-# aspg12@testdb-# $$pg12@testdb$# declarepg12@testdb$# begin pg12@testdb$# raise notice 'func is: in,out,out,in';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=# [local:/data/run/pg12]: 5120 pg12@testdb=#--' 1'-- > UNKNOW default match is text [local:/data/run/pg12]: 5120 pg12@testdb=# select func_overload (1memery 1') NOTICE: func is: in,out,in func_overload-(1 row) [local:/data/run/pg12]: 5120 pg12@testdb=# select func_overload; NOTICE: func is: in,out,out,in func_overload-(,) [local:/data/run/pg12]: 5120 pg12@testdb=#

PG will ignore the output parameters, determine whether a function is the same function, and only determine the input parameters; 2. If there is an OUT parameter, the PG function cannot return the result. It can only be returned through the OUT parameter.

At this point, I believe you have a deeper understanding of "what are the similarities and differences between function overloading Oracle and PG". 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.