Best Practices for PL/SQL Variables (Page 1 of 5 )
In the second part of a two-part series, we wrap up our discussion of best practices for handling PL/SQL variables. 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). Copyright © 2007 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.
Go ahead and splurge: declare distinct variables for different usages
Don’t overload data structure usage.
Problem: World weariness infects Lizbeth’s code.
Lizbeth is a good citizen of the world. She read The Nation each week (which is, by the way, the oldest weekly newspaper published in the United States), votes in every election, and contacts her Congressperson about any number of issues. She is, in short, well informed and intelligent, and consequently tends to get rather depressed about the state of affairs in the world.
Usually, she puts that aside when she comes to work (in fact, she looks to her world of programming as a refuge). Today, however, she just feels tired of it all, and still, she must work on a “scan and analyze” that takes a list of excuses and perhaps performs analysis on them. She needs to get the number of excuses in the list, get the length of each title, and so on. So many integer variables, so little time! With a big sigh, she writes the following code:
PROCEDURE scan_and_analyze (
excuses_in IN excuses_tp.excuses_tc -- a collection type
)
IS
intval PLS_INTEGER;
BEGIN
intval := excuses_in.COUNT;
IF intval > 0
THEN
FOR indx IN 1 .. excuses_in.COUNT
LOOP
intval := LENGTH (excuses_in (indx).title);
analyze_excuse_usage (intval);
END LOOP;
END IF;
END;
Sure, the code will compile. But who would want to maintain code that looks like this?
Solution: Don’t let your weariness show in your code—and don’t recycle!
This is just one entry of a more general category: “Don’t be lazy (in the wrong way)!”
The problem with Lizbeth’s code is that it’s pretty much impossible to look at any use of the intval variable and understand what is going on. You have to go back to the most recent assignment to make sense of the code. Compare that to the following:
PROCEDURE scan_and_analyze (
excuses_in IN excuses_tp.excuses_tc -- a collection type
)
IS
l_excuse_count PLS_INTEGER;
l_title_length PLS_INTEGER;
BEGIN
l_excuse_count := excuses_in.COUNT;
IF l_excuse_count > 0
THEN
FOR indx IN 1 .. excuses_in.COUNT
LOOP
l_page_length := LENGTH (excuses_in (indx).title);
analyze_excuse_usage (l_page_length);
END LOOP;
END IF;
END;
When you declare a variable, you should give it a name that accurately reflects its purpose in a program. If you then use that variable in more than one way (“recycling”), you create confusion and, very possibly, introduce bugs.
The solution is to declare and manipulate separate data structures for each distinct requirement. With this approach, you can also make a change to one
variable’s usage without worrying about its ripple effect to other areas of your code.
Here is a final, general piece of advice: reliance on a “time-saver” shortcut should raise a red flag. You’re probably doing (or avoiding) something now for which you will pay later.
Next: Didn’t your parents teach you to clean up after yourself? >>
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.
|
|
/ 2 
