 | Chapter 22 Code Design Tips |  |
This section offers tips for designing and using your parameters most effectively.
In the header of the program or directly next to each parameter, you should include comments explaining any assumptions about the parameter and the way it's used in the program. Such information would handle the following situations:
The units of measure for a numeric parameter. If the parameter is speed_in, is the speed in miles-per-hour or kilometers-per-hour?
The valid ranges for incoming parameter values. Remember that all parameters are unconstrained. You cannot, for example, declare a parameter as NUMBER(3). So if a parameter does have a valid range or list of values, explain that.
Invalid or impossible values for the parameters. If the speed_in parameter cannot have a value less than or greater than 200, a statement to that effect can help a person who is fixing a bug in that program or in a program that calls it.
Error status and flag information. If the program returns a status flag, code, or message, you should indicate this at the start of the program so that anyone who reads the program is aware of the expected values.
It can be particularly handy to include these comments in the actual parameter list because that documentation then becomes part of the program's specification. Then, even when developers do not have access to the body of the program, as with packages like Oracle Developer/2000 PL/SQL libraries, they can still see important information about the parameters in the specification.
The following procedure specification illustrates some of these recommendations:
PROCEDURE calc_elapsed_time
(start_time_in IN NUMBER /* Range: 0 through 24 */,
start_loc_in IN VARCHAR2 /* NY, CHI or SF */,
end_loc_in IN VARCHAR2 /* DC, LA, or NO */,
valid_out OUT VARCHAR2 /* VALID or INVALID */);It can be hard to make time for this kind of documentation, but it can make a big difference in how easily your programs can be used by others.
A common parameter for a module is a flag that relies on two-valued logic (TRUE or FALSE) to pass information to the module. The temptation in such a case is to declare the parameter to be type BOOLEAN. With this approach, when you call the program, you will pass either TRUE or FALSE. You will find in these circumstances that while the specification for your program is very readable, the way you call it in other programs will be difficult to follow.
Consider the parameters for the following procedure, which is used to generate reports and optionally print the reports out on the specified queue:
PROCEDURE generate_report
(report_id_in IN NUMBER,
queue_in IN VARCHAR2,
clean_up_log_files_in IN BOOLEAN := TRUE,
print_file_in IN BOOLEAN := TRUE);In order to make generate_report more flexible, the developer has provided two Boolean parameters:
TRUE if the procedure should clean up (remove) any log and listing files created in the process of generating the report; FALSE to keep the files in place, usually for purposes of debugging.
TRUE if the output file of the report should be printed; FALSE to skip the print step.
When one glances over the procedure's specification, the purpose and usage of each parameter seems clear enough. But take a look at how I would call this procedure:
generate_report (report_id, 'PRINT_QUEUE1', TRUE, TRUE);
As you can see, these Boolean parameter values are not very descriptive. Without the context provided by their names, actual Boolean parameters are unable to self-document their effect. A developer (or, most importantly, a maintainer) of the code must go back to the source to understand the impact of a particular value. That completely defeats the information hiding principle of modular programming.
This problem is not restricted to true Booleans, either. You will be just as confused by parameters acting as "pseudo-Boolean" parameters. In the following version of generate_report, the BOOLEAN parameters are replaced by VARCHAR2 parameters, to little advantage.
PROCEDURE generate_report
(report_id_in IN NUMBER, queue_in IN VARCHAR2,
clean_up_log_files_in IN VARCHAR2:= 'Y',
print_file_in IN VARCHAR2 := 'Y');With these VARCHAR2 replacements, calls to generate_report now look like this:
generate_report (new_report_id, temp_queue_name,'Y', 'Y');
While it is true that I no longer have the obscure TRUE or FALSE to deal with in the call interface, it would be hard to argue that "Y" or "N" is any more illuminating. A much better approach replaces Boolean and pseudo-Boolean parameters with character parameters whose acceptable values are descriptions of the action or situation.
PROCEDURE generate_report
(report_id_in IN NUMBER, queue_in IN VARCHAR2,
clean_up_log_files_in IN VARCHAR2:= 'CLEANUP',
print_file_in IN VARCHAR2 := 'PRINT');Now a call to generate_report states clearly its intentions:
generate_report (report_id, queue_name, 'CLEANUP', 'PRINT');
or:
generate_report (report_id, queue_name, 'NO_CLEANUP', 'PRINT');
or:
generate_report (report_id, queue_name, 'NO_CLEANUP', 'NO_PRINT');
As you can see from these examples, I write my procedures to accept an unambiguous affirmative value (CLEANUP) and the most clearly-defined negative form (NO_CLEANUP). One complication to this style is that you need to validate the parameter values; if you were using a Boolean, the strong datatyping in PL/SQL would guarantee that a legal value is passed.
Make sure that all OUT and IN OUT parameters are assigned values in your procedure (you should never have such parameters in a function, after all). Remember that any assignments made to OUT or IN OUT parameters in the body of a program are ignored when an exception is raised in that program. PL/SQL performs its own internal rollback of all incomplete changes. This can result in situations where the program that called the function or procedure with these parameters does not have reliable data with which to work.
The way to make sure that you assign values to your OUT and IN OUT parameters whether the program succeeds or fails is to perform assignments within the exception handlers. Consider the generate_raise procedure:
PROCEDURE generate_raise
(employee_id_in IN employee.employee_id%TYPE,
salary_out OUT employee.salary%TYPE)
IS
sal NUMBER;
days_employed BINARY_INTEGER;
BEGIN
-- Initialize the salary to zero.
salary_out := 0;
SELECT salary, SYSDATE - hire_date
INTO sal, days_employed
FROM employee
WHERE employee_id = employee_id_in;
salary_out := sal * (days_employed/365 + .15);
END;Because the procedure does not have an exception section, any errors arising from the implicit cursor ("SELECT ... FROM employee ...") are not trapped in the program. As a result, generate_raise does not always return a valid amount in the salary_out parameter. By including an exception section, we can trap such errors and assign a value to salary_out, as shown in this example:
PROCEDURE generate_raise
(employee_id_in IN employee.employee_id%TYPE,
salary_out OUT employee.salary%TYPE)
IS
sal NUMBER;
days_employed BINARY_INTEGER;
BEGIN
... as shown above ...
EXCEPTION
/*
|| Handle all exceptions in this single handler:
|| set the salary_out to zero.
*/
WHEN OTHERS THEN
salary_out := 0;
END;Now you can be sure that salary_out always has a value. Of course, it might not make sense to simply set the salary to zero when an error occurs. By doing so, you have placed a special, undocumented meaning on that value of zero. It doesn't just mean a very low salary. It means something went wrong.
It is generally not a good idea to load a variable with double meanings like that. It is hard to document and it makes the procedure difficult for people to use. Even if you changed the assignment in the exception handler to return NULL, you still have blurred the use of the salary_out parameter.
A better approach is to pass back an explicit indication that the program has succeeded or failed; in this way the code that called the program can make an unambiguous check on the status of that procedure's results. This final version of generate_raise adds another OUT parameter whose sole purpose in life is to let me know how everything went inside the black box of the procedure.
PROCEDURE generate_raise
(employee_id_in IN employee.employee_id%TYPE,
salary_out OUT employee.salary%TYPE,
found_emp_out OUT VARCHAR2)
IS
sal NUMBER;
days_employed BINARY_INTEGER;
BEGIN
... as shown above ...
EXCEPTION
/*
|| Now I set the salary to NULL, which is probably a better choice
|| for the error value, but I also set the status parameter to
|| "NOT FOUND" to show that something went wrong..
*/
WHEN OTHERS THEN
salary_out := NULL;
found_emp_out := 'NOT FOUND';
END;Now when I call generate_raise, I follow it immediately with a check of the status parameter and act accordingly (in this case of an Oracle Forms code fragment, I display an alert and then fail out of the trigger).
generate_raise (:employee.employee_id, :employee.salary, employee_around);
IF employee_around = 'NOT FOUND'
THEN
alert_selection := SHOW_ALERT ('al_emp_not_found');
RAISE FORM_TRIGGER_FAILURE;
END IF;
Procedures and functions will often take character values as parameters and use those strings in an IF statement to determine which code to execute. Parameters like action types, status flags, or selection codes often perform this kind of function. When a programmer calls a module with such a conditional parameter, the programmer must pass a value, which the program will recognize and process correctly.
Suppose I wrote a procedure that takes an action code as a parameter and then executes the appropriate code for that action. The routine might look like this:
PROCEDURE exec_action (action_in IN VARCHAR2)
IS
BEGIN
IF action_in = 'DISPLAY_LEDGER'
THEN
... the code to display the ledger.
ELSIF action_in = 'CALC_PROFITS'
THEN
... the code to calculate the profits.
ELSIF ...
END IF;
END;This procedure will work correctly only when the argument for action_in is passed in uppercase. Yet, if you didn't happen to be the author of this program, you would have to be downright lucky to get the format of the value correct.
The exec_action module requires the user of the program -- in this case, another programmer -- to know and remember an aspect of the way the program was implemented. This is a violation of good coding style (make your program a black box). On top of that, the requirement that the parameter be uppercased is simply an avoidable bother. When people sense that they are being expected to follow a rule that is, well, dumb, they will at some level -- consciously or unconsciously -- rebel against that rule. The rebellion in this case will most likely take the form of truly annoying whines and sloppy code.
You show your respect for other people by not wasting their time and their brain cells. In the world of programming, you show this respect by freeing up your users (whether they are end users or other programmers) to concentrate on making important decisions and remembering key rules and information. In other words, you make your code smarter so your users don't have to worry over the details.
If you want to make the exec_action procedure as useful as possible, you will make it as easy and foolproof to use as possible. You will shift the burden of knowledge and effort from the user to the code. Programmers in particular shouldn't have to know or care about the format of the name of the requested action. You work it out for them.
In the version of exec_action that follows, I put some smarts in the code:
Uppercasing the action as I copy it into a local (internal) variable, which I will then use for comparisons
Checking for both full action strings and abbreviations, to save the programmer some typing
Adding an ELSE clause to check for invalid actions
PROCEDURE exec_action (action_in IN VARCHAR2)
IS
/* Uppercased copy of action. The "int" stands for "internal" */
action_int VARCHAR2(30) := UPPER (action_in);
BEGIN
/* Adds support for action abbreviations, too! */
IF action_int IN ('DISPLAY_LEDGER', 'DL')
THEN
... the code to display the ledger.
ELSIF action_int IN ('CALC_PROFITS', 'CP')
THEN
... the code to calculate the profits.
ELSE
MESSAGE (' Invalid action code: ' || action_in);
END IF;
END;Now, no matter how the user enters the action, exec_action can figure out what to do:
exec_action ('DL');
exec_action ('dl');
exec_action ('display_LEDGER');
exec_action ('calc_profits');The ELSE clause also checks for an invalid value. While you, the author, may be sure that you would never enter an incorrect action, a new developer might be a lot less certain about the specific values. If you do not trap and report the mistake, this person would be at a tremendous disadvantage.
Never forget the Golden Rule when it comes to programming: trap mistakes for others as you hope they would trap them for you!
When checking for bad codes, you do have an alternative to performing the check at the end of a sometimes long IF-ELSIF sequence. That final ELSE clause is less than ideal since it can be hard to find in the code and, from a performance standpoint, you have to actually perform all the valid comparison checks before you register the problem. I suggest that you develop a generic code_check procedure that takes the list of valid codes, the code entered by the user, and an optional message text. If the code is not found in the list, code_check forces termination of the calling program.
PROCEDURE exec_action (action_in IN VARCHAR2)
IS
action_int VARCHAR2(30) := UPPER (action_in);
BEGIN
code_check ('|DISPLAY_LEDGE|DL|CALC_PROFITS|CP', action_int,
'action code');
IF action IN ('DISPLAY_LEDGER', 'DL')
THEN -- the code to display the ledger.
ELSIF action IN ('CALC_PROFITS', 'CP')
THEN -- the code to calculate the profits.
ELSIF ...
END IF;
END;Here is the code_check routine in Oracle Forms:
PROCEDURE code_check
(valid_codes_in IN VARCHAR2,
check_code_in IN VARCHAR2,
code_name_in IN VARCHAR2 := 'code',
delimiter_in IN VARCHAR2 := '|')
IS
BEGIN
IF INSTR (valid_codes_in, delimiter_in || check_code_in) = 0
THEN
MESSAGE
(' Invalid ' || code_name_in || ': "' || check_code_in || '"');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;Using code_check, if I execute the following statement with an invalid action code:
exec_action ('dp');the following text will appear on the message line or in an alert box:
Invalid action code: "dp"
Whether or not you go to the trouble of building a separate routine to check for valid codes, as long as you do validate the input to your modules, programmers will make fewer errors and be able to fix those faster. The more work your code does for the programmer, the more productive that programmer will be.
If you are using Oracle Server Version 7.0, you will encounter a number of situations where you have to include a value for every parameter in your module's parameter list. In Version 7.0, any call to a remote procedure must include all arguments, as indicated by the following error:
PLS-424: 'all arguments must be provided in remote procedure call;
no defaults'.You might say to yourself, "No problem. I do not have a distributed database. I am not performing any RPCs (remote procedure calls)." From the standpoint of PL/SQL, however, any of the following scenarios involves a remote procedure call:
A Pro*C program that calls a stored procedure
An Oracle Developer/2000 tool that calls a stored procedure
An honest-to-goodness remote procedure call of the format:
procedure_name@database_link;
One way you can get around this problem is to create a local procedure that supports default values by passing its own default to the stored procedure. Suppose I have a stored procedure as follows:
PROCEDURE calc_profits
(company_id_in IN NUMBER,
profit_type_in IN VARCHAR2 := 'NET');If I try to use this procedure in Oracle Forms, I must include a value for both parameters, even if I use the default:
calc_profits (:company.company_id, 'NET');
Suppose I now create the following local procedure (either in the form itself or in a PL/SQL library):
PROCEDURE of_calc_profits
(company_id_in IN NUMBER,
profit_type_in IN VARCHAR2 := 'NET')
IS
BEGIN
calc_profits (company_id_in, profit_type_in);
END;I can now call this procedure using the default value of NET, since the Oracle Forms version of the procedure always passes a value to the stored procedure version:
of_calc_profits (:company.company_id);
(In Version 7.1 of the Oracle Server, by the way, you will be permitted to use default values in remote procedure calls.)
This situation with Oracle Server Version 7 reinforces one of my suggestions regarding default parameter values: always specify a value for each of the module's parameters, even if a value is the same as the default. You will find yourself less vulnerable to problems down the line.
|  | |
| 22.6 Construct Abstract Data Types (ADTs) |  | 23. Managing Code in the Database |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.