15.6.1.1 %TYPE and %ROWTYPE

You can use the %TYPE and %ROWTYPE anchoring attributes in the parameter list, even if the %TYPE references a constrained declaration somewhere back along the line, as in a CREATE TABLE statement. The following parameter list will compile just fine:

PROCEDURE fun_and_games (ride_id_in IN rides.ride_id%TYPE)

If you are using PL/SQL Version 2, you can also declare parameters as PL/SQL tables through use of the TABLE type statement, as shown in this example:

PACKAGE pet_hotel
IS
   /* Define a table type and declare a table */
   TYPE breeds_type IS
      TABLE OF breed.breed_name%TYPE INDEX BY BINARY_INTEGER;
   TYPE avail_rooms_type IS
      TABLE OF room.room_number%TYPE INDEX BY BINARY_INTEGER;

   /* Specification of function which takes a table argument */
   FUNCTION room_by_breeds (breed_table_in IN breeds_type)
      RETURN avail_rooms_type;
END;

The %TYPE and %ROWTYPE attributes are allowed for parameters because both result in unconstrained declarations. The actual sizes of the parameters depend on the structures of the tables and columns to which the attributes point. This size is resolved only at compilation time.

15.6.2.1 IN mode

The IN parameter allows you to pass values in to the module, but will not pass anything out of the module and back to the calling PL/SQL block. In other words, for the purposes of the program, its IN parameters function like constants. Just like constants, the value of the formal IN parameter cannot be changed within the program. You cannot assign values to the IN parameter or in any other way modify its value.

IN is the default mode for parameters. If you do not specify a parameter mode, then the parameter is automatically considered an IN parameter. I recommend, however, that you always specify a parameter mode with your parameters. Your intended use of the parameter is then documented explicitly in the code itself.

IN parameters can be given default values in the program header (see Section 15.6.5, "Default Values").

The actual parameter for an IN parameter can be a variable, a named constant, a literal, or an expression. All of the following calls to display_title are valid:

DECLARE
   happy_title CONSTANT VARCHAR2(30)    := 'HAPPY BIRTHDAY';
   changing_title VARCHAR2(30) := 'Happy Anniversary';
   spc VARCHAR2(1) := ' ';
BEGIN
   display_title ('Happy Birthday');             -- a literal
   display_title (happy_title);                  -- a constant

   changing_title := happy_title;

   display_title (changing_title);               -- a variable
   display_title ('Happy' || spc || 'Birthday'); -- an expression
   display_title (INITCAP (happy_title));        -- another expression
END;

What if you want to transfer data out of your program? For that, you will need an OUT or an IN OUT parameter.

15.6.2.2 OUT mode

An OUT parameter is the opposite of the IN parameter, but I suppose you already had that figured out. Use the OUT parameter to pass a value back from the program to the calling PL/SQL block. An OUT parameter is like the return value for a function, but it appears in the parameter list and you can, of course, have as many OUT parameters as you like.

Inside the program, an OUT parameter acts like a variable that has not been initialized. In fact, the OUT parameter has no value at all until the program terminates successfully (without raising an exception, that is). During the execution of the program, any assignments to an OUT parameter are actually made to an internal copy of the OUT parameter. When the program terminates successfully and returns control to the calling block, the value in that local copy is then transferred to the actual OUT parameter. That value is then available in the calling PL/SQL block.

There are several consquences of these rules concerning OUT parameters:

• You cannot assign an OUT parameter's value to another variable or even use it in a re-assignment to itself. An OUT parameter can be found only on the left side of an assignment operation.

• You also cannot provide a default value to an OUT parameter. You can only assign a value to an OUT parameter inside the body of the module.

• Any assignments made to OUT parameters are rolled back when an exception is raised in the program. Because the value for an OUT parameter is not actually assigned until a program completes successfully, any intermediate assignments to OUT parameters are therefore ignored. Unless an exception handler traps the exception and then assigns a value to the OUT parameter, no assignment is made to that parameter. The variable will retain the same value it had before the program was called.

• An OUT actual parameter has to be a variable. It cannot be a constant, literal, or expression, since these formats do not provide a receptacle in which PL/SQL can place the OUTgoing value.

OUT parameters are very restrictive in how they can and should be used. These restrictions place more of a burden on the programmer to understand the parameter modes and their impact. On the other hand, the OUT parameter provides a level of security and a narrowing of functionality which, when appropriate, is invaluable.

15.6.2.3 The IN OUT mode

With an IN OUT parameter, you can pass values into the program and return a value back to the calling program (either the original, unchanged value or a new value set within the program). The IN OUT parameter shares two restrictions with the OUT parameter:

1. An IN OUT parameter cannot have a default value.

2. An IN OUT actual parameter or argument must be a variable. It cannot be a constant, literal, or expression, since these formats do not provide a receptacle in which PL/SQL can place the outgoing value.

Beyond these restrictions, none of the other restrictions apply.

You can use the IN OUT parameter in both sides of an assignment, because it functions like an initialized, rather than uninitialized, variable. PL/SQL does not lose the value of an IN OUT parameter when it begins execution of the program. Instead, it uses that value as necessary within the program.

The combine_and_format_names procedure shown here combines the first and last names into a full name in the format specified ("LAST, FIRST" or "FIRST LAST"). It uses all three different modes of parameters: IN, IN OUT, and OUT.

PROCEDURE combine_and_format_names
   (first_name_inout IN OUT VARCHAR2,
    last_name_inout IN OUT VARCHAR2,
    full_name_out OUT VARCHAR2,
    name_format_in IN VARCHAR2 := 'LAST, FIRST')
IS
BEGIN
   /* Upper-case the first and last names. */
   first_name_inout := UPPER (first_name_inout);
   last_name_inout := UPPER (last_name_inout);

   /* Combine the names as directed by the name format string. */
   IF name_format_in = 'LAST, FIRST'
   THEN
      full_name_out := last_name_inout || ', ' || first_name_inout;

   ELSIF name_format_in = 'FIRST LAST'
   THEN
      full_name_out := first_name_inout || ' ' || last_name_inout;
   END IF;
END;

The first name and last name parameters must be IN OUT. I need the incoming names for the combine action, and I will uppercase the first and last names for future use in the program (thereby enforcing the application standard of all uppercase for names of people and things).

The full_name_out is just an OUT parameter because I create the full name from its parts. If the actual parameter used to receive the full name has a value going into the procedure, I certainly don't want to use it! Finally, the name_format_in parameter is a mere IN parameter since it is used to determine how to format the full name, but is not changed or changeable in any way.

Each parameter mode has its own characteristics and purpose. You should choose carefully which mode to apply to each of your parameters so that the parameter is used properly within the module.

15.6.4.1 Positional notation

In every example you've seen so far, I have employed positional notation in order to guide PL/SQL through the parameters. With positional notation, PL/SQL relies on the relative positions of the parameters to make the correspondence. PL/SQL associates the nth actual parameter in the call to a program with the nth formal parameter in the program's header.

With the tot_sales example shown below, PL/SQL associates the first actual parameter, :order.company_id, with the first formal parameter, company_id_in. It then associates the second actual parameter, N, with the second format parameter, status_in:

new_sales := tot_sales (:order.company_id, 'N');

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

Now you know the name for the way compilers pass values through parameters to modules. Positional notation, shown graphically in Figure 15.11, is certainly the most obvious method.

Figure 15.11: Matching actual with formal parameters (positional notation)

15.6.4.2 Named notation

With named notation, you explicitly associate the formal parameter (the name of the parameter) with the actual parameter (the value of the parameter), right in the call to the program, using the combination symbol =>.

The general syntax for named notation is:

formal_parameter_name => argument_value

Because you provide explicitly the name of the formal parameter, PL/SQL no longer needs to rely on the order of the parameters to make the association from actual to formal. So, if you use named notation, you do not need to list the parameters in your call to the program in the same order as the formal parameters are listed in the header. I can call tot_sales for new orders in either of these two ways:

new_sales :=
   tot_sales (company_id_in => :order.company_id, status_in =>'N');

new_sales :=
   tot_sales (status_in =>'N', company_id_in => :order.company_id);

You can also mix named and positional notation in the same program call:

:order.new_sales := tot_sales (:order.company_id, status_in =>'N');

If you do mix notation, however, you must list all of your positional parameters before any named notation parameters, as shown in the preceding example. Positional notation has to have a starting point from which to keep track of positions, and the only starting point is the first parameter. If you place named notation parameters in front of positional notation, PL/SQL loses its place. Both of the calls to tot_sales, shown below, fail. The first statement fails because the named notation comes first. The second fails because positional notation is used, but the parameters are in the wrong order. PL/SQL will try to convert `N' to a NUMBER (for company_id):

:order.new_sales := tot_sales (company_id_in => :order.company_id, 'N');

:order.new_sales := tot_sales ('N', company_id_in => :order.company_id);

15.6.4.3 Benefits of named notation

Now you are aware of different ways to notate the order and association of parameters. One obvious question that you might ask is why you would ever use named notation. Here are some possibilities:

• Named notation is self-documenting. When you use named notation, the call to the program clearly describes the formal parameter to which the actual parameter is assigned. The names of formal parameters can and should be designed so that their use/purpose is self-explanatory. In a way, the descriptive aspect of named notation is another form of program documentation. If you are not familiar with all the modules called by an application, the listing of the formal parameters helps reinforce your understanding of a particular program call. In some development environments, the standard for parameter notation is named notation for just this reason.

• Named notation gives you complete flexibility over parameter specification. You can list the parameters in any order you want. You can also include only the parameters you want or need in the parameter list. Complex applications may at times require procedures with literally dozens of parameters. Any parameter with a default value can be left out of the call to the procedure. By using named notation, the developer can use the procedure, passing only the values needed for that usage.

Remember that whether you use named or positional notation, the actual module (both header and body) remains unchanged. The only difference is in the way the module is called.