Oracle PL/SQL Best Practices

Name

DAT-05: Use CONSTANT declarations for variables whose values do not change.

Synopsis

If you know that the value of your variable isn’t going to change, take the time, and make the effort, to declare it as a constant.

Example

The following script runs during business hours (9 A.M. to 6 P.M.) and is used to analyze book checkouts in the MYSTERY category:

DECLARE
   c_date CONSTANT DATE := TRUNC (SYSDATE);
   c_category CONSTANT book.category%TYPE := 
      'MYSTERY';
BEGIN
   checkouts.analyze (c_date, c_category);
   ...
   -- 75 lines later
   FOR rec IN (
      SELECT * FROM book
       WHERE category = c_category)
   LOOP
      ...
   END LOOP;

After writing, testing, and deploying this script, I can be confident that a developer won’t, six months from now, make a change to the c_category structure between the call to checkouts.analyze and the FOR loop.

Benefits

Your code self-documents the usage of this data structure: it should not and cannot change.

A developer can’t later mistakenly change the data structure’s value.