1

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?

1

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.

0

Closest thing that I know is to overload a function...

create or replace package test_pkg
as
    function variableParams ( a in varchar2, z in integer, y in integer ) return integer;
    function variableParams ( a in varchar2, z in integer, y in integer, x in integer ) return integer; 
end;
/

create or replace package body test_pkg
as
function variableParams
    (   a  in varchar2,
        z  in integer,
        y  in integer
    )
return integer
as  
    result integer;
begin
    if ( action = 'SUM' )
    then
        result := z + y;
    elsif ( action = 'SUBTRACT' )
    then
        result := z - y;
    else
        raise_application_error(-20001,'Invalid action specified');
    end if;

    return result;
end variableParams;
function variableParams
    (   a  in varchar2,
        z  in integer,
        y  in integer,
        x  in integer
    )
return integer
as  
    result integer;
begin
    if ( action = 'SUM' )
    then
        result := z + y + x;
    elsif ( action = 'SUBTRACT' )
    then
        result := z - y - x;
    else
        raise_application_error(-20001,'Invalid action specified');
    end if;

    return result;
end variableParams;
end test_pkg;
/

I like Balazs answer as well. Either way, I hope you aren't trying to allow too many elements because this could get messy. :)

Good luck.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.