Oracle PL/SQL Best Practices

Name

CTL-09: Move static expressions outside of loops and SQL statements.

Synopsis

Whenever you set out to tune your PL/SQL programs, you should first take a look at your loops. Any inefficiency inside a loop’s body will be magnified by the multiple executions of that code.

A common mistake is to put code that is static or unchanging for each iteration of the loop inside the body. When you can identify such situations, extract the static code, assign the outcomes of that code to one or more variables, and then reference those variables inside the loop.

Example

This procedure summarizes book reviews. It’s run every morning at 8 A.M. and takes about 15 minutes to complete:

CREATE OR REPLACE PROCEDURE summarize_reviews (
   summary_title_in IN VARCHAR2,
   isbn_in IN book.isbn%TYPE)
IS
   CURSOR review_cur IS
      SELECT text, 
             TO_CHAR (SYSDATE, 'MM/DD/YYYY') today
        FROM book_review
       WHERE isbn = isbn_in;
BEGIN
   FOR review_rec IN review_cur
   LOOP
      IF LENGTH (review_rec.text) > 100
      THEN
         review_rec.text := 
            SUBSTR (review_rec.text, 1, 100);
      END IF;
      
      review_pkg.summarize (
         UPPER (summary_title_in),
         today,
         UPPER (review_rec.text)
         );
   END LOOP;
END;
/

There are a number of problems with this code:

• Since my job starts and finishes on the same day, I don’t need to select SYSDATE with each row of my query. And unless I really want “today” to be a string expression, or I am ready to absorb the overhead of multiple implicit conversions, I should use TRUNC to get rid of the time element.

• I write over the text field of ...