Helpful Hints 10 PL/SQL Tips: Simplifying a Complex Language

Helpful Hints 10 PL/SQL Tips: Simplifying a Complex Language

By Steven Feuerstein
PL/SQL Release 2.3 is hitting the streets as you read this issue of Select. PL/SQL has come a long way since its rather simplistic beginnings as a batch-processing language for SQL*Plus scripts in the Oracle Version 6 database. Since PL/SQL Version 2 was first released, for example, Oracle Corporation has added dynamic SQL, RDBMS-based job scheduling, file I/O, and cursor variables. PL/SQL is now a mature, advanced programming language packed with features and capabilities and one of the most significant enabling technologies in the Oracle universe of products.
Since its inception, PL/SQL has become a much more complicated and complex language. There is so much more for all of us to understand and, eventually, master. At the same time, the demands on our expertise and productivity increase steadily. As a result, there has never been a better time to pick up some tips for improving our PL/SQL programming skills.
This article offers nine of my favorite tips for writing PL/SQL code which has fewer bugs, is more easily maintained and takes advantage of some of the most powerful features of PL/SQL. Stepping back from the details, however, these nine suggestions generally express the following good advice:

a. Use structured methodology. You may not be coding in Cobol, but the same principles of design which worked for 3GLs work just as well with PL/SQL. Don�t throw away the baby with the bathwater! Methodologies developed over the past thirty years hold their legitimacy even with the non-procedural environments like Oracle Developer 2000.
b. Use common sense. At the end of many of my presentations on PL/SQL, people will come up to me and say: "But everything you said was just common sense!" I agree wholeheartedly. Certainly PL/SQL breaks some new ground (for many of us) with features like packages, PL/SQL tables, and cursor variables. Most of our time is spent, however, coding the same old IF statements, loops and so forth. We shouldn�t have to be reminded to avoid hard-coding literals, to name identifiers to reflect their purpose, to use consistent indentation, to avoid side-effects in functions.
c. Do it right the first time. I have got to be the worst offender of this advice. I fit the classic hacker profile, impatiently chomping at the bit to get in there and code. Hold yourself back! Do the analysis up front so that you understand the requirements fully. Think through the implementation approach you will take. Uncover the pitfalls before you start coding. And when you start debugging, don�t "try" things. Analyze the problem and craft a solution you can prove will solve the difficulty. You will always end up being more productive if you take the time to do it right the first time around.
d. Make full use of the appropriate, provided features of the programming language. I don�t think there is a single person in this world (including members of the PL/SQL development team) who truly knows about every single feature of the PL/SQL language, especially if you include all of the package-based extensions. Yet the more you know, the more you can use to your advantage in your programs. Make sure you remain current with the ever-flowing releases of PL/SQL. Take the time to familiarize yourself with new features so that you can fully leverage the capabilities of PL/SQL. Since this sort of advice (a) does not fill up an article in Select and (b) is too vague to have an impact on your lives, I will go ahead and provide you with some specifics.

1. Use UPPER-lower case method to make code more readable.
PL/SQL code is made up of many different components: PL/SQL data structures such as PL/SQL tables, bind variables like Oracle Forms items, procedures, functions, loops, declarations, control constructs, etc. All of these elements break down roughly into two types of text: reserved words and application-specific identifiers. The PL/SQL compiler treats these two kinds of text very differently. You can improve the readability of your code greatly by reflecting this difference in the way the text is displayed. I employ the UPPER-lower rule to highlight the distinction: Write all reserved words in UPPERCASE and all application identifiers in lowercase. The following IF statement illustrates this rule:

IF to_number(the_value) > 22 AND num1 BETWEEN lval AND hval THEN newval := 100; ELSIF TO_NUMBER (the_value) < 1 THEN calc_tots (TO_DATE (�12-jan-95�)); END IF;
A consistent mixture of upper- and lowercase words gives a sense of dimension to the code. The eye can more easily cruise over the text and pick the different syntactical elements of each statement. The uppercase words act as signposts directing the activity in the code. You can focus quickly on the lowercase words for the application-specific content.
There are other widely-used standards for use of case in programming, most notably the Visual Basic style of UsingInitCapForEachWord. Whichever you approach you take, make sure it aids in readability -- and use it consistently!
2. Use anchored declarations whenever possible.
You can use the %TYPE and %ROWTYPE declaration attributes to anchor the datatype of one variable to that of a previously-existing variable or data structure. The anchoring data structure can be a column in a database table, the entire table itself, a programmer-defined record or a local PL/SQL variable. In the following example I declare a local variable with the same structure as the company name:

my_company company.name%TYPE;
In this second example I declare a record based on a cursor�s structure:

CURSOR company_cur IS SELECT company_id, name, incorp_date FROM company; company_rec company_cur%ROWTYPE;
Anchored types offer the following benefits:
1. Synchronization with database columns.
Many PL/SQL variables @1body copy:"represent" database information inside the program. By using %TYPE, I am guaranteed that the local variable�s data structure matches that in the database. If I instead hard-coded my declaration, the program could get "out of sync" with my data dictionary and generate errors. For example, if the previous declaration had declared my_company as VARCHAR2(30) and then I expanded the column size in the table to 60, my program would be likely to cause VALUE_ERROR exceptions in the future.
2. Normalization of local variables.
You use PL/SQL variables to store calculated values used throughout the application. You can use %TYPE to base all declarations of a common derived value against a single, centralized datatype. If you need to modify that datatype, perhaps to expand the maximum size of a number to reflect higher revenue, you only need to change that single declaration. All anchored declarations will then pick up the new constraint when the programs are recompiled.
3. Leverage fully the built-in functions
PL/SQL offers dozens of built-in functions to help you get your job done with the minimum amount of code and fuss possible. Some of them are straightforward, such as the LENGTH function, which returns the length of the specified string. Others offer subtle variations which will aid you greatly -- but only when you are aware of those variations.
Two of my favorites in this category of hidden talents are INSTR and SUBSTR, both character functions.
SUBSTR returns a sub-portion of a string. Most developers only use these functions to search forward through the strings. By passing a negative starting location, however, SUBSTR will count from the end of the string. The following expression returns the last character in a string:
SUBSTR (my_string, -1, 1)
INSTR returns the position in a string where a sub-string is found. INSTR will actually scan in reverse through the string for the Nth occurrence of a substring. In addition, you can easily use INSTR to count the number of times a substring occurs in a string, using a loop of this nature:

LOOP substring_loc := INSTR (string_in, substring_in, 1, return_value); /* Terminate loop when no more occurrences are found. */ EXIT WHEN substring_loc = 0; /* Found match, so add to total and continue. */ return_value := return_value + 1; END LOOP; RETURN return_value - 1;
4. Get familiar with the new built-in packages
In addition to the many built-in functions provided by PL/SQL, Oracle Corporation also offers many built-in packages. These packages of functions, procedures and data structures greatly expand the scope of the PL/SQL language.
It is no longer sufficient for a developer to be comfortable simply with the basic PL/SQL functions like TO_CHAR and ROUND and so forth. Those functions have now become only the inner-most layer of useful functionality. Oracle Corporation has built upon those functions, and you should do the same thing.
Just to give you a taste of what the built-in packages offer consider the following possibilities: The possibilities and capabilities aren�t quite endless, but they are getting there! With each new release of the Oracle Server, we get new packages with which to improve our own programs.
6. Take advantage of the cursor FOR loop.
The cursor FOR loop is one of my favorite PL/SQL constructs. It leverages fully the tight and effective integration of the procedural aspects of the language with the power of the SQL database language. It reduces the volume of code you need to write to fetch data from a cursor. It greatly lessens the chance of introducing loop errors in your programming -- and loops are one of the more error-prone parts of a program. Does this loop sound too good to be true? Well, it isn�t -- it�s all true!
Suppose I need to update the bills for all pets staying in my pet hotel, the Share-a-Din-Din Inn. The example below contains an anonymous block that uses a cursor, occupancy_cur, to select the room number and pet ID number for all occupants at the Inn. The procedure update_bill adds any new changes to that pet�s room charges.

1 DECLARE 2 CURSOR occupancy_cur IS 3 SELECT pet_id, room_number 4 FROM occupancy WHERE occupied_dt = SYSDATE; 5 occupancy_rec occupancy_cur%ROWTYPE; 6 BEGIN 7 OPEN occupancy_cur; 8 LOOP 9 FETCH occupancy_cur INTO occupancy_rec; 10 EXIT WHEN occupancy_cur%NOTFOUND; 11 update_bill 12 (occupancy_rec.pet_id, occupancy_rec.room_number); 13 END LOOP; 14 CLOSE occupancy_cur; 15 END;
This code leaves nothing to the imagination. In addition to defining the cursor (line 2), you must explicitly declare the record for the cursor (line 5), open the cursor (line 7), start up an infinite loop, fetch a row from the cursor set into the record (line 9), check for an end-of-data condition with the cursor attribute (line 10), and finally perform the update. When you are all done, you have to remember to close the cursor (line 14).
If I convert this PL/SQL block to use a cursor FOR loop, then I have:

DECLARE CURSOR occupancy_cur IS SELECT pet_id, room_number FROM occupancy WHERE occupied_dt = SYSDATE; BEGIN FOR occupancy_rec IN occupancy_cur LOOP update_bill (occupancy_rec.pet_id, occupancy_rec.room_number); END LOOP; END;
Here you see the beautiful simplicity of the cursor FOR loop! Gone is the declaration of the record. Gone are the OPEN, FETCH, and CLOSE statements. Gone is need to check the %FOUND attribute. Gone are the worries of getting everything right. Instead, you say to PL/SQL, in effect:: "You and I both know that I want each row and I want to dump that row into a record that matches the cursor. Take care of that for me, will you?" And PL/SQL does take care of it, just the way any modern programming language integrated with SQL should.
7. Enhance scope control with nested blocks.
The general advantage of -- and motivation for -- a nested block is that you create a scope for all the declared objects and executable statements in that block. You can use this scope to improve your control over activity in your program, particularly in the area of exception handling.
In the following procedure, I have placed BEGIN and END keywords around a sequence of DELETE statements. This way, if any DELETE statement fails, I trap the exception, ignore the problem and move on to the next DELETE.

PROCEDURE delete_details IS BEGIN BEGIN DELETE FROM child1 WHERE ...; EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN DELETE FROM child2 WHERE ...; EXCEPTION WHEN OTHERS THEN NULL; END; END;
I can in this way use my nested blocks to allow my PL/SQL program to continue past exceptions.
8. Overload modules to make your software smarter.
Within a package and within the declaration section of a PL/SQL block, you can define more than one module with the same name! The name is, in other words, overloaded. In the following example, I have overloaded the value_ok function in the body of my check package, which is used to validate or check values used in my application:

PACKAGE BODY check IS /* First version takes a DATE parameter. */ FUNCTION value_ok (date_in IN DATE) RETURN BOOLEAN IS BEGIN RETURN date_in <= SYSDATE; END; /* Second version takes a NUMBER parameter. */ FUNCTION value_ok (number_in IN NUMBER) RETURN BOOLEAN IS BEGIN RETURN number_in > 0; END; END;
Now I can put both versions of value_ok to work in my code as follows:

IF check.value_ok (hiredate) AND check.value_ok (salary) THEN ... END IF;
I have found overloading to be extremely useful when I am building a layer of code which will be used by other developers (my PL/SQL toolbox). I use module overloading to hide complexities of the programmatic interface from my users (other programmers). Instead of having to know the six different names of procedures used to, for example, display various kinds of data, a developer can rely on a single module name. In this fashion, overloading transfers the burden of knowledge from the developer to the software.
9. Use local modules to reduce code volume and improve readability.
A local module is a procedure or function which is defined in the declaration section of a PL/SQL block (anonymous or named). This module is considered local because it is only defined within the parent PL/SQL block. It cannot be called by any other PL/SQL blocks defined outside of that enclosing block.
There are two key reasons to create local modules:
-- Reduce the size of the module by stripping it of repetitive code. This is the most common motivation to create a local module. The code reduction leads to higher-quality code since you have fewer lines to test and fewer potential bugs. It takes less effort to maintain the code, since there is simply less to maintain. When you do have to make a change, you make it in one place in the local module and the effects are felt immediately throughout the parent module.
-- Improve the readability of your code. Even if you do not repeat sections of code within a module, you still may want to pull out a set of related statements and package them into a local module to make it easier to follow the logic of the main body of the parent module.
Consider the following example, in which I calculate the net present value for various categories and then format the result for display purposes.

PROCEDURE display_values (projected_sales_in IN NUMBER, year_in IN INTEGER) IS BEGIN DBMS_OUTPUT.PUT_LINE (�Total Sales: � || TO_CHAR((net_present_value (�total_sales�, year_in) / projected_sales_in * 100),�999.99�)); DBMS_OUTPUT.PUT_LINE (�Gross Profit: � || TO_CHAR((net_present_value (�gross_profit�, year_in) / projected_sales_in * 100),�999.99�)); DBMS_OUTPUT.PUT_LINE (�Employee Labor: � || TO_CHAR((net_present_value (�labor_costs�, year_in) / projected_sales_in * 100),�999.99�)); END;
I suppose that when you work in a Windows environment, it isn�t necessarily such a big deal to write code like that shown above. You cut and paste, cut and paste....who knows? You might even use Microsoft Recorder or some other utility to automate the process. However you manage it, though, you still end up with lots of repetitions of the same fragment of code. You still end up with code that is hard to maintain.
Since I have exposed the way I perform the calculation (and formatting), I must upgrade each distinct calculation whenever a change is required (different display format, different formula, etc.). If, on the other hand, I hide the calculation behind the interface of a callable module, then the calculation is coded only once.With the help of a local module, the display_values procedure is transformed as shown below.

PROCEDURE display_values (projected_sales_in IN NUMBER, year_in IN INTEGER) IS /*----------------------- Local Module ----------------------*/ PROCEDURE display_npv (column_in IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE (INITCAP (REPLACE (column_in, �_�, � �)) || �: � || TO_CHAR((net_present_value (column_in, year_in) / projected_sales_in * 100), �999.99�)); END; BEGIN /* Perform direct, readable assignments using the function. */ display_npv (�total_cost�); display_npv (�gross_profit�); display_npv (�employee_labor�); END;
I have found that few developers are aware of the ability to create local modules. I have also found that these modules-within-a-module play an important role in allowing me to write well-structured, even elegant programs.
These days it seems that whenever I write a program with more than 20 lines, and with any complexity whatsoever, I end up creating several local modules. It helps me see my way through to a solution much more easily. I can conceptualize my code at a higher level of abstraction by assigning a name to a whole sequence of statements. I can perform top-down design and step-wise refinement of my requirements. Finally, by modularizing my code even within a single program, I make it very easy to later extract a local module and make it a truly independent, reusable procedure or function if the need arises.
Take a look at any of your more complex programs and I guarantee you will quickly identify segments of the code which would serve you better bundled into a local module.
10. Construct abstract data types with PL/SQL packages.
The term "abstract data type" is about as dry and technical-sounding as you can get. Yet the concept of an abstract data type, or ADT, is something we apply -- or should apply -- in every single one of our application efforts, sometimes without even realizing that we are doing it. An abstract data type is a collection of information and operations which act on that information. An abstract data type can represent computer objects, such as lists, records, stacks, arrays, and tables; but an ADT can also represent real-world objects, such as a company or a product or the set of assembly line operations at a factory.
The power behind the ADT lies in the first word of its name: abstract. When you create an ADT, you work with objects as opposed to variables, columns, and other computer-science items. You perform an abstraction from the implementation details to the "thing in itself" and work on a higher level.
PL/SQL offers several different constructs with which to build and manage ADTs, most importantly the package. The most general description of an ADT, in fact, sounds just like the description of a package: a collection of data and operations on that data.
Packages are among the least-understood and most under-utilized features of PL/SQL. That is a shame, because the package structure is also one of the most useful constructs for building well-designed PL/SQL-based applications. Packages provide a structure in which you can organize your modules and other PL/SQL elements. They encourage proper structured programming techniques in an environment which often befuddles the implementation of structured programming.
In the space of this article, I can only provide a brief example of an ADT (a "progress box") and offer some guidelines.
The development team of a financials application found that their Oracle Forms 4.0 application would not change the cursor from an arrow to an hourglass when PL/SQL programs were running. With a wait-time of between three and five minutes for some operations, they were concerned that users would get impatient, click irresponsibly with their mouse and possibly cause damage. Their solution was to construct a "progress box" which would keep the user informed of the program�s progress.
The progress box consisted of a window named "progress_window", a canvas-view named "cv_progress" and the following three items, all defined in a block named "b_progress":
process_name The description of the program currently executing.
percent_done A textual description of the percentage of the job completed.
thermometer A graphical representation of the percentage of the job completed.
A programmer discovered that by setting the visual attributes (specifically the font) of the thermometer in the item, the letter "n" would appear as a solid box (blue!) on the screen. Two letters would appear as two blocks and so on. Make the item ten characters long and you have a graphical representation of percentage completion in 10% increments.
The first attempt at implementing the progress box resulted in widespread repetition of the sequences of statements used to manage the different elements of the progress box. By treating the progress box as an object with rules governing its use and appearance, on the other hand, I was able to greatly reduce the volume of code required. The resulting statements are also much easier to understand and maintain.
Note: The package I wrote to create an abstract data type can only be defined in an Oracle database.
Developer 2000 PL/SQL library or within a form itself, since it relies on Oracle Forms
built-ins, such as SHOW_VIEW and SYNCHRONIZE.
The following examples show the "before" (dispersed) and "after" (packaged) of the progress box code.
1. Initialize the thermometer with a title, empty thermometer and 0% completed.
Before:

b_progress.process_name := �Updating Profit and Loss data...�; :b_progress.percent_done := �0 % Complete.�; :b_progress.thermometer := NULL; SHOW_VIEW (�cv_progress�); Synchronize;
After:

progress.bar (0, 0, �INIT�, �Updating Profit and Loss data...�);
2. Set the thermometer to 20% completion.
Before:

:b_progress.percent_done := �20 % Complete.�; :b_progress.thermometer := �nn�; SHOW_VIEW (�cv_progress�); SYNCHRONIZE;
After:

progress.bar (20, 2);
3. Hide the progress box when the program completed and control was returned back to the user.
Before:

HIDE_VIEW (�cv_progress�); SET_WINDOW_PROPERTY (�progress_window�, VISIBLE, PROPERTY_OFF); SYNCHRONIZE;
After:

progress.hide;
Here are some guidelines you should follow when designing an ADT:
1. Maintain a consistent level of abstraction. This is probably the most important aspect of your ADT implementation. All the modules you build to represent your abstract data structure should operate with the same level of data.
2. Provide a comprehensive interface to the abstract data type. Make sure that the user (a programmer, in this case) can perform all necessary operations on the ADT without having to go around the interface you build to the ADT. Hide all the implementational details of your ADT behind calls to procedures and modules -- without exception.
3. Use the package structure, the most natural repository for ADT code. The ADT represents a thing by presenting a layer of code which allows you to perform operations on that thing as a whole, rather than its individual components. The package joins related objects together and so corresponds closely to the ADT. The package clearly distinguishes between the public and private parts of the code. The public objects make up the interface to the ADT. The private objects contain and hide the implementational details for the ADT.
Nine, Nineteen, Ninety Tips
This article should provide you with lots of ideas on how to improve your code. Of course, it is just a tip (no pun intended) of the iceberg of features and functionality in the PL/SQL language. Oracle PL/SQL Programming, published by O�Reilly and Associates, explores all of the tips in this article, plus many, many more, in great detail. If you have any questions, you can reach me at my Compuserve address: 72053,441.
About the Author
Steven Feuerstein is the author of O�Reilly and Associates latest Oracle book," Oracle PL/SQL Programming." He is Director of the Oracle Practice at SSC, a Chicago-based systems management consulting firm. Steven has been building Oracle-based applications for the last eight years, publishes regular columns on programming in Oracle in the Oracle Developer newsletter from Pinnacle Publishing and Oracle Integrator from Oracle Corporation, and develops utilities for Oracle developers.
Phone: 708 575 3194
Fax: 708 586 8378
Compuserve 72053,441.

This is a copy of an article published @ http://www.ioug.org/