Database Interaction with PL/SQL, Named Notations, Storing Procedures and Functions

Please note that all the examples in this series have been tested only with Oracle 10g.  I didn’t really test them with all the previous versions of Oracle.  I suggest you refer to the documentation of the respective version you are using, if any of the programs failed to execute.

What is Named Notation?

When we pass parameter values to a PL/SQL program, we are passing parameter values using positional notation. That means the first value goes to the first parameter, second to the second parameter and so on. What If I want the first value to go to the second parameter, the second value to the third parameter, the third value to the first parameter, and so on? This is where the concept of named notation comes in.

Using named notation, you can pass the values of parameters in any order you want regardless of the position.  The following is a sample program:

declare
 procedure dispEmp(p_sal emp.sal%type, p_deptno emp.deptno%type) is
 begin
  for r_emp in (select ename from emp where sal > p_sal and deptno = p_deptno)
  loop
   dbms_output.put_line(r_emp.ename);
  end loop;
 end;
BEGIN
 dispEmp(1000,10);
 dbms_output.put_line('-------------');
 dispEmp(p_sal => 1000, p_deptno => 10);
 dbms_output.put_line('--------------');
 dispEmp(p_deptno => 10, p_sal => 1000);
END;

I hope the procedure ‘dispEmp’ is quite understandable. The only issue on which we need to concentrate is the statements in the main program. The first statement is as follows:

dispEmp(1000,10);

The above statement is straightforward. The 1000 gets into ‘p_sal’ and the 10 gets into ‘p_deptno’.  This is positional notation. Based on the position, the respective parameter name gets chosen.  The next statement is as follows:

dispEmp(p_sal => 1000, p_deptno => 10);

The above statement explicitly says that 1000 has to be assigned to ‘p_sal’ and 10 has to be assigned to ‘p_deptno’.  This is named notation.  In the named notation, we need to specify parameter names (even though it is not that necessary in the above statement).  The next statement is as follows:

dispEmp(p_deptno => 10, p_sal => 1000);

The above statement explicitly says that 10 has to be assigned to ‘p_deptno’ and 1000 has to be assigned to ‘p_sal’. This is also named notation. But it will not give any error and in all three cases, the result would be the same. You can observe that I changed the order according to my requirement.