|
Chapter 15 Procedures and Functions |
|
Procedures and functions can both use parameters to pass information back and forth between the module and the calling PL/SQL block.
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:
The number of parameters. Too few parameters can limit the reusability of your program. Too many parameters, and no one will want to reuse your program.
The types of parameters. Should you use read-only, write-only, or read-write parameters?
The names of parameters. How should you name your parameters so that their purpose in a module is properly and easily understood?
Default values for parameters. How do you set defaults? When should a parameter be given defaults and when should the programmer be forced to enter a value?
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)
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.
When you define the parameter you also specify the way in which the parameter can be used. There are three different modes of parameters:
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:
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.
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:
An IN OUT parameter cannot have a default value.
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.
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:
The primary key of the company
The status of the orders to be included in the sales calculation
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:
Associate the actual parameter implicitly (by position) with the formal parameter.
Associate an actual parameter explicitly (by name) with the formal parameter.
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:
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.
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 (
:=
), as the following example illustrates:
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:
astrology_reading (1756);
Not exactly what you might have had in mind! You also cannot use a comma
( , )
to indicate a placeholder, as in, "There should be a parameter here so use the default value!" This next call does not even compile:
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.
astrology_reading (birth_time_in => 1756);
|
|
|
|
| 15.5 Functions |
|
15.7 Local Modules |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.