When you pass a parameter to a function or stored procedure, you can either use positional parameter notation or named parameter notation. If you use positional notation, you must list the parameters in the declared order. If you use named notation, the order of parameters is not important.

If you use named notation, you must list the name of each parameter followed by an arrow (=>) and a parameter value. Your workloads increase if you use named notation, but named notation makes your code easy to read and maintain.

Example

The following example describes how to use positional parameter notation 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;
If you use positional notation to call a stored procedure, pass the following information:
emp_info(30, 7455, 'Clark'); 
If you use named notation to call a stored procedure, pass the following information:
emp_info(p_ename =>'Clark', p_empno=>7455, p_deptno=>30);

If the parameter list is changed, the parameters are reordered, or an optional parameter is added, named notation can reduce the need to rearrange the parameter list of a stored procedure.

If you specify a default value for a parameter and this parameter is not a trailing parameter, you must use named notation to call a stored procedure or function. The following example describes a stored procedure that has two leading default parameters:
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 ignore non-trailing parameter values if you use named notation to call the preceding stored procedure. If positional notation is applied, you can only assign default values to trailing parameters. You can call the preceding stored procedure by specifying parameters as follows:
  • check_balance(p_customerID => 10, p_amount = 500.00)
  • check_balance(p_balance => 1000.00, p_amount = 500.00)
You can specify parameters by using a combination of positional and named notation, which is called mixed notation. The following example describes how to use 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 use mixed notation to call the stored procedure.
emp_info(30, p_ename =>'Clark', p_empno=>7455);

If you use mixed notation to call a stored procedure, named parameters must not precede positional parameters.