Oracle PL/SQL Best Practices

Name

CTL-10: Use anonymous blocks within IF statements to conserve resources.

Synopsis

One of the nice things about PL/SQL is that you, the developer, can define any set of executable statements as a distinct block, with its own declaration, executable, and exception sections.

If you notice that certain operations and data structures aren’t needed unless a certain condition is satisfied, move all the execution of those operations and the declaration of those data structures inside the conditional statement. The result is that you won’t incur the overhead (CPU or memory) unless it’s absolutely needed.

Example

In the following block, I declare a set of local variables and even initialize l_name with a function that usually takes 10 seconds to execute (min_balance_account). But when I write my block, it turns out that in many situations, those structures are ignored:

DECLARE
   TYPE account_tabtype IS TABLE 
      OF account%ROWTYPE INDEX BY BINARY_INTEGER;
   l_accounts account_tabtype;

   l_name VARCHAR2(2000) := 
      min_balance_account (SYSDATE);
BEGIN 
   IF balance_too_low (1056)
   THEN
      use_collection (l_accounts);
      use_name (l_name);
   ELSE
      -- No use of l_accounts or l_name
      ...
   END IF;
END;

Once I recognize this situation (usually identified through a code walkthrough), I should change it to this:

BEGIN IF balance_too_low (1056) THEN DECLARE TYPE account_tabtype IS TABLE OF account%ROWTYPE INDEX BY BINARY_INTEGER; l_accounts account_tabtype; l_name VARCHAR2(2000) := min_balance_account (SYSDATE); BEGIN use_collection ...