O'Reilly logo

Oracle PL/SQL Best Practices by Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required