Oracle PL/SQL Best Practices

Name

STYL-06: Self-document using block and loop labels.

Synopsis

While PL/SQL labels (identifiers within double angle brackets, such as <<yearly_analysis>>) are most often associated with GOTOs and are therefore disdained, they can be a big help in improving the readability of code.

Use a label directly in front of loops and nested anonymous blocks:

• To name that portion of code and thereby self-document what it’s doing

• So you can repeat that name with the END statement of that block or loop

This recommendation is especially important when you have multiple nestings of loops (and possibly inconsistent indentation), as in the following:

LOOP
   <body>
   WHILE <condition>
   LOOP
      <while body>
   END LOOP;
END LOOP;

Example

I use labels for a block and two nested loops, and then apply them in the appropriate END statements. I can now easily see which loop and block is ending, no matter how badly my code is indented!

CREATE OR REPLACE PROCEDURE display_book_usage
IS
BEGIN
   <<best_seller_review>>
   DECLARE
      CURSOR yearly_analysis_cur IS SELECT ...;
      CURSOR monthly_analysis_cur IS SELECT ...;
   BEGIN
      <<yearly_analysis>>
      FOR book_rec IN yearly_analysis_cur (2000)
      LOOP
         <<monthly_analysis>>
         FOR month_rec IN
             monthly_analysis_cur (
                yearly_analysis_cur%rowcount)
         LOOP
            ... lots of month-related code ...
         END LOOP monthly_analysis;
         ... lots of year-related code ...
      END LOOP yearly_analysis;
   END best_seller_review;
END display_book_usage;

Benefits

If you use labels, it’s much easier to read your code, especially if it ...