Since previous few tutorials were about PL/SQL Subroutines such as PL/SQL Functions and Stored Procedures thus it becomes mandatory to talk about their calling notations. Learning the concepts of PL/SQL Subroutines will not be considered as complete until we learn their calling notations as well.
What is Calling Notation for PL/SQL Subroutines?
Calling notation is a way of providing values to the parameters of a subroutine such as PL/SQL function or a stored procedure.
Types of Calling Notations for Subroutines
In Oracle PL/SQL there are 3 types of calling notations. These are:
- Positional Notation
- Named Notation and
- Mixed calling notation
Positional Calling Notations
Positional notation is the most common calling notation which you can see in almost every computer programming language. In positional notation we have to specify the value for each formal parameter in a sequential manner. This means that you have to provide the values for the formal parameters in the same order as they were declared in the procedure or in the function.
In positional notation the datatype and the position of the actual parameter must match with the formal parameter.
Suggested Reading: Actual Parameters vs. Formal Parameters
Example: Positional Notation for calling PL/SQL Subroutines.
CREATE OR REPLACE PROCEDURE emp_sal (dep_id NUMBER, sal_raise NUMBER) IS BEGIN UPDATE employees SET salary = salary * sal_raise WHERE department_id = dep_id; DBMS_OUTPUT.PUT_LINE ('salary updated successfully'); END; /
This is the same example which we did in PL/SQL Tutorial 42 on how to create stored procedure with parameters albeit some minor changes. Now if we use positional calling notation then we have to supply the values to both the parameters of the above procedure in the same manner in which they are declared.
Stored Procedure call using positional notation in Oracle Database
In this simple procedure call, the value 40 is corresponding to the formal parameter dep_id and value 2 is corresponding to the parameter sal_raise.
Named Calling Notations
Named calling notation lets you pass values to the formal parameters using their names. This will in turn let you assign values to only required or say mandatory parameters.
This calling notation is useful when you have a subroutine with parameters where some of those parameters are mandatory and some are optional and you want to pass the values to only the mandatory ones.
In order to assign values to the formal parameters using their names we use association operator. It is a combination of equal to (=) sign and greater than (>) sign. We write the name of the formal parameter to the left hand side of the operator and the value which you want to assign to the right hand side of the operator.
Example of Named Calling Notation for calling a PL/SQL Subroutines
CREATE OR REPLACE FUNCTION add_num (var_1 NUMBER, var_2 NUMBER DEFAULT 0, var_3 NUMBER ) RETURN NUMBER IS BEGIN RETURN var_1 + var_2 + var_3; END; /
The above function has 3 parameters. Among these 3 parameters 2 are mandatory and 1 is optional with a default value 0.
You can call this function using positional notation. But it has a restriction which you have to fulfill and that is that you have to supply values to all the formal parameters in the same order in which they are declared and the datatype of formal and actual parameters must match.
So if you want to omit the optional parameter and want to use their default value or you just forgot the order of the parameter in which they were declared! Then it will be slightly difficult for you to call the above subroutine using positional notation. In such a scenario you can take advantage of Named Calling Notation. This calling notation will provide you the desired flexibility in calling your subroutines.
PL/SQL Function call using Named Calling Notation in Oracle Database
DECLARE var_result NUMBER; BEGIN var_result := add_num(var_3 => 5, var_1 =>2); DBMS_OUTPUT.put_line('Result ->' || var_result); END;
I have explained the PL/SQL Function call in detail in the Video Tutorial on this same topic on my YouTube channel.
Mixed Calling Notation for calling PL/SQL Subroutines
As the name suggests in mixed calling notation you can call subroutines using the combination of named as well as positional calling notations. Mixed calling notation is very helpful where the parameter list is defined with all mandatory parameters first and optional parameters next.
Example of Mixed calling notation for calling PL/SQL subroutines
Here is the anonymous block in which we are calling the same function add_num ( ) which we coded when doing named calling notation.
DECLARE var_result NUMBER; BEGIN var_result := add_num(var_1 => 10, 30 ,var_3 =>19); DBMS_OUTPUT.put_line('Result ->' || var_result); END;
That’s how we use mixed calling notation for calling PL/SQL Subroutines.
Try it yourself
Using the knowledge from the above concepts try solving the following question:
Write a PL/SQL Function with parameters for swapping two numbers and call that function using mixed calling notation.
You can share your code or the snapshot of your code with me on my social media [Twitter // Facebook].
Hope you enjoyed reading. Do make sure to Like and Share this blog. Thanks and have a great day.