Positional versus named parameter notation v17
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 doesn't matter.
If you 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.
This example uses positional and named parameter notation:
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);
If you used named notation, you don't need to rearrange a procedure’s parameter list if the parameter list changes, the parameters are reordered, or an optional parameter is added.
When an argument has a default value and the argument isn't a trailing argument, you must use named notation to call the procedure or function. This example shows 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 omit nontrailing argument values when you call this procedure only 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, referred to as mixed notation, to specify parameters. This example shows using mixed parameter notation:
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);
When using mixed notation, named arguments can't precede positional arguments.