Positional vs. Named Parameter Notation v13
You can use either positional or named parameter notation when passing parameters to a function or procedure. If you specify parameters using positional notation, you must list the parameters in the order that they are declared; if you specify parameters with named notation, the order of the parameters is not significant.
To specify parameters using named notation, list the name of each parameter followed by an arrow (=>)
and the parameter value. Named notation is more verbose, but makes your code easier to read and maintain.
A simple example that demonstrates using positional and named parameter notation follows:
CREATE OR REPLACE PROCEDURE emp_info ( p_deptno IN NUMBER, p_empno IN OUT NUMBER, p_ename IN OUT VARCHAR2, ) IS BEGIN dbms_output.put_line('Department Number =' || p_deptno); dbms_output.put_line('Employee Number =' || p_empno); dbms_output.put_line('Employee Name =' || p_ename; END;
To call the procedure using positional notation, pass the following:
emp_info(30, 7455, 'Clark');
To call the procedure using named notation, pass the following:
emp_info(p_ename =>'Clark', p_empno=>7455, p_deptno=>30);
Using named notation can alleviate the need to re-arrange a procedure’s parameter list if the parameter list changes, if the parameters are reordered or if a new optional parameter is added.
In a case where you have a default value for an argument and the argument is not a trailing argument, you must use named notation to call the procedure or function. The following case demonstrates a procedure with two, leading, default arguments.
CREATE OR REPLACE PROCEDURE check_balance ( p_customerID IN NUMBER DEFAULT NULL, p_balance IN NUMBER DEFAULT NULL, p_amount IN NUMBER ) IS DECLARE balance NUMBER; BEGIN IF (p_balance IS NULL AND p_customerID IS NULL) THEN RAISE_APPLICATION_ERROR (-20010, 'Must provide balance or customer'); ELSEIF (p_balance IS NOT NULL AND p_customerID IS NOT NULL) THEN RAISE_APPLICATION_ERROR (-20020,'Must provide balance or customer, not both'); ELSEIF (p_balance IS NULL) THEN balance := getCustomerBalance(p_customerID); ELSE balance := p_balance; END IF; IF (amount > balance) THEN RAISE_APPLICATION_ERROR (-20030, 'Balance insufficient'); END IF; END;
You can only omit non-trailing argument values (when you call this procedure) by using named notation; when using positional notation, only trailing arguments are allowed to default. You can call this procedure with the following arguments:
check_balance(p_customerID => 10, p_amount = 500.00) check_balance(p_balance => 1000.00, p_amount = 500.00)
You can use a combination of positional and named notation (mixed notation) to specify parameters. A simple example that demonstrates using mixed parameter notation follows:
CREATE OR REPLACE PROCEDURE emp_info ( p_deptno IN NUMBER, p_empno IN OUT NUMBER, p_ename IN OUT VARCHAR2, ) IS BEGIN dbms_output.put_line('Department Number =' || p_deptno); dbms_output.put_line('Employee Number =' || p_empno); dbms_output.put_line('Employee Name =' || p_ename; END;
You can call the procedure using mixed notation:
emp_info(30, p_ename =>'Clark', p_empno=>7455);
If you do use mixed notation, remember that named arguments cannot precede positional arguments.