Problem
You have a PL/SQL procedure in your Oracle database that accepts a large number of parameters. When calling the procedure, you would rather not worry that the positioning of the parameters is correct.
Solution
Rather than trying to pass all the parameters to the procedure in the correct order, you can pass them by name. The code in this solution calls a procedure that accepts six parameters, and it passes the parameters by name rather than in order.
Procedure Declaration:
PROCEDURE process_emp_paycheck(EMP_ID IN NUMBER,
PAY_CODE IN NUMBER,
SICK_USED IN NUMBER,
VACATION_USED IN NUMBER,
FEDERAL_TAX IN NUMBER,
STATE_TAX IN NUMBER);
Procedure Execution:
EXEC process_emp_paycheck(EMP_ID=>10,
PAY_CODE=>10,
VACATION_USED=>8.0,
SICK_USED=>8.0,
STATE_TAX=>.06,
FEDERAL_TAX=>.08);As you can see, by passing the parameters by name, they do not need to follow the same positional ordering as they do within the declaration of the procedure.
How It Works
To pass a parameter by name, you list the parameter name followed by an arrow (consisting of an equal sign and a greater-than symbol) pointing to the value you are passing. The following pseudocode depicts this technique:
procedure_name(parameter=>value);Although it can be more verbose to use named parameters, passing parameters by name can be very handy when there are several parameters to pass because you do not need to worry about passing them in the correct order. It is also helpful because it increases readability.
Both procedures and functions can accept positional and named parameters. Neither notation is superior to the other, so which one you choose to use is completely dependant upon the procedure or function that is currently being called. However, named parameters are a safe choice if trying to maintain consistency with procedure calls throughout your application or your organization.
Although not recommended, you can use both positional and named notation when passing parameters within the same call. When doing so, you need to place the parameters that you want to pass using positional notation first, followed by the parameters that you want to pass using named notation. The following execution illustrates using both positional and named notation while passing parameters to the PROCESS_EMP_PAYCHECK procedure:
EXEC process_emp_paycheck(198, 10, 0,
SICK_USED=>4.0,
STATE_TAX=>.05,
FEDERAL_TAX=> .04);This particular call passed both of the first parameters by position, those being EMP_ID and PAY_CODE. The last three parameters are passed by named notation.
