Oracle PL/SQL Best Practices

Name

DAT-08: Do not overload data structure usage.

Synopsis

This is just one entry of a more general category: “don’t be lazy!” 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.

And here’s a general piece of advice: reliance on a “time-saver” short-cut should raise a red flag. You’re probably doing (or avoiding) something now for which you will pay later.

Example

I have a few different needs for an integer value, so I will declare one and use it throughout:

DECLARE
   ... other declarations 

   intval INTEGER;
BEGIN
   intval := list_of_books.COUNT;

   IF intval > 0
   THEN
      intval := list_of_books(list_of_books.FIRST).page_count;

      analyze_book (intval);
   END IF;

It’s pretty much impossible to look at any usage of intval and understand what is going on. You have to go back to the most recent assignment. Compare that to the following:

DECLARE
   ... other declarations 

   l_book_count INTEGER;
   l_page_count INTEGER;
BEGIN
   l_book_count := list_of_books.COUNT;

   IF l_book_count > 0
   THEN
      l_page_count:= list_of_books(list_of_books.FIRST).page_count;

      analyze_book (l_page_count);
   END IF;

Benefits

It’s a whole lot easier to understand what your code does.

You can make a change to one variable’s usage without worrying about its ripple effect to other ...