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
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;
emp_info(30, 7455, 'Clark');
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.
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;
check_balance(p_customerID => 10, p_amount = 500.00)
check_balance(p_balance => 1000.00, p_amount = 500.00)
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;
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.