Ways to create a procedure with optional parameters?

Question

Is that possible to create a procedure with optional parameters? For example, create a procedure that add up all of the parameters.

add(n1, n2, .... nn)

Another procedure like for example I have 11 parameters, it adds up from 1 to 10, and the last parameter can do something else. By googling, it seems like you can use array to do it, but most of the results I got was about another programming languages, not plsql. If apply the theory to plsql I guess I suppose to use varray or nested table?

Answer 1

You need to declare parameter with default value:

procedure add (n1 number, 
               n2 number default 0,
               ...
               nn number default 99) is ...

Using this procedure:

begin
  add(1);
  add(5, 6, 7, ..., 111);
  add(n1 => 111, n5 => 345, nn => 17);
end;

In last case, as you can see, you can pass values for 3 parameters (n1, n5, nn), for others parameters default values will be used.