Actual and Formal Parameters

We need to distinguish between two different kinds of parameters: actual and formal parameters. The formal parameters are the names that are declared in the parameter list of the header of a module. The actual parameters are the values or expressions placed in the parameter list of the actual call to the module.

Let's examine the differences between actual and formal parameters using the example of tot_sales. Here, again, is the tot_sales header:

FUNCTION tot_sales (company_id_in IN company.company_id%TYPE, status_in IN order.status_code%TYPE := NULL)RETURN std_types.dollar_amount;

The formal parameters of tot_sales are:

company_id_in

The primary key of the company

status_in

The status of the orders to be included in the sales calculation

These formal parameters do not exist outside of the function. You can think of them as placeholders for real or actual parameter values that are passed into the function when it is used in a program.

When you use tot_sales in your code, the formal parameters disappear. In their place you list the actual parameters or variables whose values will be passed to tot_sales. In the following example, the company_id variable contains the primary key pointing to a company record. In the first three calls to tot_sales, a different, hardcoded status is passed to the function. The last call to tot_sales does not specify a status; in this case, the function assigns the default value (provided in the function header) to the status_in parameter:

new_sales := tot_sales (company_id, 'N');paid_sales := tot_sales (company_id, 'P');shipped_sales := tot_sales (company_id, 'S');all_sales := tot_sales (company_id);

When tot_sales is called, all the actual parameters are evaluated. The results of the evaluations are then assigned to the formal parameters inside the function to which they correspond (note that this is true only for IN and IN OUT parameters; parameters of OUT mode are not copied in).

The formal parameter and the actual parameter that corresponds to it (when called) must be of the same or compatible datatypes. PL/SQL will perform datatype conversions for you in many situations. Generally, however, you are better off avoiding all implicit datatype conversions. Use a formal conversion function like TO_CHAR or TO_DATE (see Chapter 10), so that you know exactly what kind of data you are passing into your modules.

16.4.3 Matching Actual and Formal Parameters in PL/SQL

How does PL/SQL know which actual parameter goes with which formal parameter when a program is executed? PL/SQL offers two ways to make the association:

Positional notation

Associate the actual parameter implicitly (by position) with the formal parameter.

Named notation

Associate the actual parameter explicitly (by name) with the formal parameter.