What`s Code Without Variables? (Page 1 of 4 )
Every programming language deals with variables. There are certain ways to handle variables that can make your life as a programmer much easier. This article explains best practices for handling variables in PL/SQL. It is excerpted from chapter four of the book
Oracle PL/SQL Best Practices, Second Edition, written by Steven Feuerstein (O'Reilly, 2007; ISBN: 0596514107). Copyright © 2007 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.
PL/SQL is a strongly typed language. This means that before you can work with any kind of data structure, you must first declare it. And when you declare it, you specify its type and, optionally, an initial or default value. All declarations of these variables must be made in the declaration section of your anonymous block, procedure, function, or package. I’ve divided the best practices in this chapter into three main categories, described in the following sections:
Best Practices for Declaring Variables and Data Structures
Presents best practices for specifying %TYPE and %
ROWTYPE, declaring SUBTYPEs, and localizing
variable initialization.
Best Practices for Using Variables and Data Structures
Presents best practices for simplifying the
specification of business rules and data structures
and avoiding implicit datatype conversions.
Best Practices for Declaring and Using Package Variables
Presents best practices aimed particularly at the use
of variables in packages.
Best Practices for Declaring Variables and Data Structures Use the best practices described in this section when you declare your variables and data structures.
That column’s never going to change!
Always anchor variables to database datatypes using
%TYPE and %ROWTYPE.
Problem: Lizbeth writes a “quick-and-dirty” program.
Lizbeth hates doing anything the quick-and-dirty way. In fact, she generally hates to be in a hurry at all. But Sunita comes by with an urgent request: “I need you to write a program to scan through all the flimsy excuses in our system, and display the title and description of each excuse. I need it in 30 minutes, but we’re going to run it only once, so you don’t have to worry about following all the usual best practices.”
Gritting her teeth, Lizbeth puts aside her good judgment and quickly familiarizes herself with the structure of the table:
CREATE TABLE flimsy_excuses (
id INTEGER
, title VARCHAR2(50)
, description VARCHAR2(100)
, ... and many more columns ...
);
She then throws together the following program:
PROCEDURE show_excuses
IS
CURSOR quick_cur IS SELECT title, description FROM flimsy_excuses;
l_title VARCHAR2 (50);
l_desc VARCHAR2 (100);
BEGIN
OPEN quick_cur;
LOOP
FETCH quick_cur INTO l_title, l_description;
EXIT WHEN quick_cur%NOTFOUND;
DBMS_OUTPUT.put_line (
'Title/description: ' || l_title || ' - ' || l_desc);
END LOOP;
END show_excuses;
Lizbeth runs some tests—it appears to do the job. She finishes in well under 30 minutes and hands over the code to Sunita, who is delighted and hurries off to do whatever she needs to do with it. Quickly putting show_excuses out of her mind, Lizbeth returns to her real work, and slows way down.
Years go by, and one day Lizbeth gets a call from Support: “We’re getting reports of unhandled VALUE_ERROR exceptions in the reporting module. Can you take a look?” She does take a look and much to her combined horror, dismay, and disgust, she finds that old quick-and-dirty, one-off, never-to-be-used-again show_excuses program integrated directly into the production reporting subsystem.
That’s bad enough, but it seems that it has been working for years. Why would it suddenly be experiencing “technical difficulties?” It takes Lizbeth two very frustrating hours, but she finally figures it out: the DBAs just yesterday put in a number of changes to the base tables so that My Flimsy Excuse could support multiple languages (many of which are much more verbose than English).
In particular, the maximum length of the flimsy_excuses.title column was increased to 1,000 and the description column to 4,000. Once the new data went into the table and the program was run, those quick-and-dirty declarations of l_title and l_description were suddenly wholly inadequate.
Solution: Assume that everything will change and that any program you write could be around for decades.
Lizbeth never should have compromised her programming principles. Everyone’s always in a hurry, but we all know that doing things in a hurry doesn’t really save time—it just shifts where the time is spent.
Lizbeth never should have compromised her programming principles. Everyone’s always in a hurry, but we all know that doing things in a hurry doesn’t really save time—it just where the time is spent.
Furthermore, we always underestimate the staying power of our code. We can’t really imagine that the program we write today will be around for years (heck, we don’t even really believe that our code can continue working year after year without our paying any attention to it!). Yet it does. Applications have incredible staying power. And the shortcuts we take today come back to bite us (or whichever poor fool now must maintain the application) later on.
So we should always write our programs expecting them to last a long, long time—and also expecting everything they depend on and use to change.
In the particular case of the show_excuses program, Lizbeth’s big mistake was getting lazy about declaring the two variables. She hardcoded the maximum length of the variables to the current maximum size of the table’s columns. Instead, she should have declared the variables using the %TYPE attribute, as you see in this rewritten declaration section:
PROCEDURE show_excuses
IS
CURSOR quick_cur IS SELECT title, description FROM flimsy_excuses;
l_title flimsy_excuses.title%TYPE;
l_desc flimsy_excuses.description%TYPE;
Now this program will automatically adapt to changes in the underlying table. It won’t have a choice in the matter. Whenever the data structure against which a declaration is anchored changes, the program containing the anchoring is marked INVALID. Upon recompilation, it automatically uses the new form of the data structure.
These declarations are also “self-documenting”: a
%TYPE declaration tells anyone who reads it what kind of data this variable is supposed to hold.
You can also use the %ROWTYPE attribute to anchor an entire record to a cursor, table, or view. In fact, this kind of declaration makes much more sense for show_excuses. Let’s rewrite the program using
%ROWTYPE:
PROCEDURE show_excuses
IS
CURSOR quick_cur IS SELECT title, description FROM flimsy_excuses;
l_record quick_cur%ROWTYPE;
BEGIN
OPEN quick_cur;
LOOP
FETCH quick_cur INTO l_record;
EXIT WHEN quick_cur%NOTFOUND;
DBMS_OUTPUT.put_line ( 'Title/description: '
|| l_record.title || ' - ' || l_record.description);
END LOOP;
END show_excuses;
Now Lizbeth can declare just a single variable, a record, that has the same structure as the cursor. This code is even more resilient. The lengths of columns can, of course, change without causing the program to raise errors. But Lizbeth can even add more values to the SELECT list of the query, and the record will automatically (after recompilation) have an extra field corresponding to that new element.
Lizbeth could simplify this code even further by using a cursor FOR loop. Since she is iterating through every row, she can avoid the record declaration entirely as follows:
PROCEDURE show_excuses
IS
CURSOR quick_cur IS SELECT title, description FROM flimsy_excuses;
BEGIN
FOR l_record IN quick_cur
LOOP
DBMS_OUTPUT.put_line ( 'Title/description: '
|| l_record.title || ' - ' || l_record.description);
END LOOP;
END show_excuses;
If your variable holds data that is coming from a table or a cursor, use %TYPE or %$ROWTYPE to declare that variable. Aim for a single point of definition for the datatypes that you are using for declarations. And if you can get the Oracle database to do the work for you (implicitly declaring the variable or record), all the better!
Next: There is more to data than columns in a table >>
More Database Articles Articles
More By O'Reilly Media
|
 This article is excerpted from chapter four of the book Oracle PL/SQL Best Practices, Second Edition, written by Steven Feuerstein (O'Reilly, 2007; ISBN: 0596514107). Check it out today at your favorite bookstore. Buy this book now.
|
|
