Function Variable Number of Arguments plsql

functionsoracleplsql

I am looking to do a function with non fixed number of arguments like decode function.
The syntax for the DECODE function in Oracle/PLSQL is:

DECODE( expression , search , result [, search , result]... [, default] )

I want to do the same [] for example:

SUM(expresion1, expresion2, [expresion]...)

A practical result could be:

SELECT SUM(1,1) FROM dual;
SELECT SUM(1,1,1,1,1,1,1,1,1) FROM dual;
etc

Is that posible in PL/SQL or is this limited to Oracle built-in functions?

Best Answer

DECODE is an SQL function, not PL/SQL. You can not use it in plain PL/SQL, only as part of SQL statements.

There is no nice and easy way for this. There is the method with collections, for example:

create or replace type t_numbers as table of number;
/

create or replace function sum_plsql (p_numbers t_numbers) return number
as
  rv number := 0;
begin
  for n in 1..p_numbers.count
  loop
    rv := rv + p_numbers(n);
  end loop;
  return rv;
end;
/

Then:

select sum_plsql(t_numbers(1, 1, 1, 1, 1, 1, 1, 1, 1)) from dual;

9

There are built-in, non-documented simple list types, so you do not need to define your own types at all circumstances, but relying on non-documented features is generally bad practice, as they may change in newer versions without warning.

If there is a reasonable limit for the number of arguments:

create or replace function sum_plsql2 (p1 number default 0, p2 number default 0, p3 number default 0, p4 number default 0) return number
as
begin
  return p1 + p2 + p3 + p4;
end;
/

select sum_plsql2(1, 1, 1) from dual;

3

Obviously, these are neither tidy nor effective.

Related Question