Syntax, syntax -- is everything just a matter of syntax? You need literacy, a decent memory, and a good reference manual to master the syntax of a computer language like PL/SQL. But how far does that really get you? If you know the correct sequence of keywords to construct a valid procedure, does that mean that you also know how to design that procedure for maximum readability and minimum maintenance? Of course not.
Conquering PL/SQL's syntax is just the first leg of the journey you must take to develop the skills you need to modularize complex and effective applications. This chapter takes another step towards top quality module construction by offering a series of practical, directed tips. Many of these discussions repeat or summarize advice I've given elsewhere in this book, but I've collected it here for easy reference.
These tips cover both how to construct your procedures and functions and how to design your parameters. Once you have written a module and made it available in your development environment, very few people ever look at the code inside the program. Instead, users of your module refer to the module name and parameters, and to the documentation about the module.
The module parameters are far and away the most important part of a module's specification. They determine what information goes into the program and what information can be returned by it (as does the RETURN clause for a function). So this chapter includes a number of tips to help ensure that your parameter lists provide the most accurate information in the clearest manner possible.
A name can make a big difference in the effectiveness of modules and parameters. The tips provided in this section should help you select names and define conventions for names that will improve the readability of your code.
When you place code in a module, you hide it from view and give those executable statements a name (the name of the function or procedure). Information hiding is a good thing when it enhances your understanding of a program by letting you focus on a higher level of abstraction. Information hiding is a bad thing when it obscures your understanding of a program. This usually happens when you do not choose an accurate name for your module.
The name of your module should describe what it does in such a way that the replacement of code with the module call "self-documents" your program -- that is, the name should describe the action performed by the module. In addition, the grammatical structure of the name you use is important.
Calculate_P_and_L Display_Errors Confirm_New_Entries
A function is used like an expression in an executable statement. It returns or represents a value, so the grammar of a function name should be a noun:
Net_Profit Company_Name Number_of_Jobs Earliest_Hire_Date
If I use the wrong grammatical structure for my names, they do not "read" properly. Consider the FOR loop below.
tabrow := 1; FOR company_cur IN company_rec LOOP profit_and_loss (company_cur.company_id); net_profit_table (tabrow) := calculate_net_profit (company_cur.company_id); tabrow := tabrow + 1; END LOOP;
Try reading this code out loud: "For every company in my cursor, profit-and-loss the company and then add the calculate-net-profit to the table." It doesn't sound right and it doesn't look right. If you simply name the procedure "profit_and_loss," you do not explain what you are doing with the profit and loss. And if you include a verb in the function name, you have one verb too many; the assignment operator itself already serves as the "action" for that statement.
I can make a few, minor adjustments in these module names and end up with much more readable code, as shown in this example:
tabrow := 1; FOR company_cur IN company_rec LOOP calculate_p_and_l (company_cur.company_id); net_profit_table (tabrow) := net_profit (company_cur.company_id); tabrow := tabrow + 1; END LOOP;
The module names say it all: "For each company retrieved from the cursor, calculate the profit and loss and store the net profit for that company in the net profit PL/SQL table." Additional, formal documentation seems unnecessary. Can I say the same thing about the next version of the loop?
tabrow := 1; FOR company_cur IN company_rec LOOP calcpl (company_cur.company_id); net_profit_table (tabrow) := np (company_cur.company_id); tabrow := tabrow + 1; END LOOP;
The module names in this code are excessively abbreviated. It is unrealistic to expect someone who didn't actually write the calcpl procedure and the np function to understand what those terms are supposed to mean. You should strike a happy medium between the above obscure identifiers and verbose names like these:
A parameter plays a specific role within a program; its name should indicate this difference. I recommend that in order to further distinguish parameters from other PL/SQL identifiers you include the parameter mode right in the name of the formal parameter.
A parameter has one of three modes: IN, OUT, or IN OUT. An IN parameter passes a value into the module, but its value cannot be modified. An OUT parameter passes a value out of the module, but its value cannot be referenced in the module. An IN OUT parameter can be both referenced and modified in the module.
By incorporating the parameter mode directly into the parameter name, its purpose in the module is self-documenting. Whenever you encounter that parameter in the code, you know that you are looking at a parameter, not a local variable, and you know exactly the ways in which that parameter can be used and/or changed.
In order for the parameter mode to stand out in the formal parameter name, you can include it either as a suffix or as a prefix. As a suffix, the mode is appended to the end of the parameter name, as follows:
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');
The prefix standard would result in a parameter list for the above procedure as follows:
PROCEDURE combine_and_format_names (inout_first_name IN OUT VARCHAR2, inout_last_name IN OUT VARCHAR2, out_full_name OUT VARCHAR2, in_name_format IN VARCHAR2 := 'LAST,FIRST') ;
You could also opt for a minimum of typing by simply using the first letter of each parameter mode:
PROCEDURE combine_and_format_names (io_first_name IN OUT VARCHAR2, io_last_name IN OUT VARCHAR2, o_full_name OUT VARCHAR2, i_name_format IN VARCHAR2 := 'LAST,FIRST') ;
In any of these cases, a quick glance over the code identifies which objects represent parameters in the program. You can also more easily catch logic errors, such as the following case of an illegal update of an IN parameter:
in_name_format := 'FIRST MIDDLE LAST';
A parameter with an "in" suffix or prefix should never have its value changed. A parameter with an "out" suffix or prefix should never be used on the right-hand side of an assignment because its value is indeterminate in the program (until it is given a value).
Given that a package provides a new layer or context over the normal variables and modules, you should take some special care in naming your packages and the elements within them. The name you use for a standalone module, in particular, will change when you move it inside a package. Consider the following example. If I develop a set of standalone procedures and functions to maintain lists of information, I might name them as follows:
PROCEDURE list_create (list_in IN VARCHAR2); PROCEDURE list_get (list_in IN VARCHAR2, position_in IN NUMBER, item_out OUT VARCHAR2);
and so on. I need to use the "list_" prefix in front of each module name so that the name clearly identifies the structure for the action.
Suppose I now move modules like these inside the scope of a package called "list." The specification for the package looks like this:
PACKAGE list IS PROCEDURE list_create (list_in IN VARCHAR2); PROCEDURE list_get (list_in IN VARCHAR2, position_in IN NUMBER, item_out OUT VARCHAR2); END list;
At first glance this looks reasonable enough. The real test, however, comes when you try to use the modules. Let's try it. To create a list using the package procedure, I would execute a statement like this:
Now, that looks silly. The "list" is mentioned in both the package name and the module name. It didn't make much sense to carry over the standalone module names to the package names. A much cleaner naming scheme should produce executable statements that remove the redundancies:
PACKAGE list_pkg IS PROCEDURE create (list_in IN VARCHAR2); PROCEDURE get (list_in IN VARCHAR2, position_in IN NUMBER, item_out OUT VARCHAR2); END list_pkg; list.create ('company_names');
As you define modules in a package, always think of them within the context of the package. If a package is supposed to maintain lists, then the name of the package should reflect that general purpose. The names of the individual modules ought to focus on their particular purpose -- again, within the broader orientation of the package.
You might also consider setting as a standard the use of a _pkg suffix for all package names. With this approach, the list package and calls to the package look like this:
PACKAGE list_pkg IS PROCEDURE create (list_in IN VARCHAR2); PROCEDURE get (list_in IN VARCHAR2, position_in IN NUMBER, item_out OUT VARCHAR2); END list_pkg; list_pkg.create ('company_names');
Personally, I have mixed feelings about this naming convention. It is important and useful to include information about the type of data in the name of the data, and I certainly encourage such a convention with cursors, records, tables, etc. A package, however, is a different sort of animal. It contains all those other language constructs. The way in which the package name is used to qualify the object already tells you what it is. It seems to me that inclusion of the _pkg suffix (or other indicator) reduces the readability of the code.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.