The parameters of a module are at least as important as the code that implements the module (the module's body). Sure, you have to make certain that your module fulfills its promise. But the whole point of creating a module is that it can be called, you hope by more than one other module. If the parameter list is confusing or badly designed, it will be very difficult for programmers to make use of the module. The result will be that few people will use that module. And it doesn't much matter how well you implemented a program that no one uses.
Many developers do not give enough attention to a module's set of parameters. Considerations regarding parameters include:
PL/SQL offers many different features to help you design parameters effectively. This section covers all elements of parameter definition. Chapter 22, Code Design Tips , offers a number of tips for designing your parameters for maximum readability and code reusability.
Formal parameters are defined in the parameter list of the program. A parameter definition parallels closely the syntax for declaring variables in the declaration section of a PL/SQL block. There is one important distinction: a parameter declaration must be unconstrained.
A constrained declaration is one that constrains or limits the kind of value that can be assigned to a variable declared with that datatype. An unconstrained declaration is one that does not limit values in this way. The following declaration of the variable company_name constrains the variable to 60 characters:
DECLARE company_name VARCHAR2(60); BEGIN
When you declare a parameter, however, you must leave out the constraining part of the declaration:
PROCEDURE display_company (company_name IN VARCHAR2) IS ...
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)
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.
The mode determines how the program can use and manipulate the value assigned to the formal parameter. You specify the mode of the parameter immediately after the parameter name and before the parameter's datatype and optional default value. The following procedure header uses all three modes of parameters:
PROCEDURE predict_activity (last_date_in IN DATE, task_desc_inout IN OUT VARCHAR2, next_date_out OUT DATE)
The predict_activity procedure takes in two pieces of information: the date of the last activity and a description of the activity. It then returns or sends out two pieces of information: a possibly modified task description and the date of the next activity. Because the task_desc_inout parameter is IN OUT, the program can both read the value of the argument and change the value of that argument.
Let's look at each of these parameter modes in detail.
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.
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:
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.
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:
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.
When we talk about 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 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:
They do not exist outside of the function. You can think of them as place holders 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.
The actual parameters must be evaluated because they can be expressions, as well as pointers, to non-PL/SQL objects such as bind variables in the development tool.
The formal parameter and the actual parameter that corresponds to the formal parameter (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 so you are sure of what is happening in your code. Use a formal conversion function like TO_CHAR or TO_DATE (see Chapter 14, Conversion Functions ), so you know exactly what kind of data you are passing into your modules.
How does PL/SQL know which actual parameter goes with which formal parameter when a program is executed? PL/SQL actually offers you two different ways to make the association:
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.
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);
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:
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.
As you have seen from previous examples, you can provide a default value for IN parameters. If an IN parameter has a default value, you do not need to include that parameter in the call to the program. You must, of course, include an actual parameter for any IN OUT parameters, even if they have default values. A parameter's default value is used by the program only if the call to that program does not include that parameter in the list.
The parameter default value works the same way as a specification of a default value for a declared variable. There are two ways to specify a default value: either with the keyword DEFAULT or with the assignment operator (
PROCEDURE astrology_reading (sign_in IN VARCHAR2 := 'LIBRA', birth_time_in IN NUMBER DEFAULT 800) IS
By using default values, you can call programs using different numbers of actual parameters. The program uses the default value of any unspecified parameters. It also overrides the default values of any parameters in the list that have default values. Here are all the different ways you can ask for your astrology reading using positional notation:
astrology_reading ('SCORPIO', 1756); astrology_reading ('SCORPIO'); astrology_reading;
The first line specifies both parameters explicitly. In the second call to astrology_reading, only the first actual parameter is included, so birth_time_in is set to 8:00 A.M. In the third line, no parameters are specified, so we cannot include the parentheses. Both the default values are used in the body of the procedure.
What if you want to specify a birth time, but not a sign? The following call to astrology_reading compiles properly since PL/SQL will convert NUMBER to VARCHAR2 automatically, but it results in assigning the string "1756" to the sign, and 8:00 A.M. to the birth time:
Not exactly what you might have had in mind! You also cannot use a comma
astrology_reading (,1756); -- Invalid skipped argument
If you wish to leave out leading and defaulted parameters from your program call, you need to switch to named notation. By including the name of the formal parameter, you can list only those parameters to which you need to pass (or retrieve) values. In this (thankfully) last request for a star-based reading of my fate, I have successfully passed in a default of Libra as my sign and an overridden birth time of 5:56 P.M.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.