3.4 Formatting PL/SQL Blocks

As I've outlined in Chapter 2 , every PL/SQL program is structured as a block containing up to four sections:

• Header

• Declaration section

• Executable section

• Exception section

The PL/SQL block structure forms the backbone of your code. A consistent formatting style for the block, therefore, is critical. This formatting should make clear these different sections. (See Chapter 15, Procedures and Functions , for more information about the block structure.)

Consider the following function:

FUNCTION
company_name (company_id_in IN company.company_id%TYPE)    RETURN
VARCHAR2 IS cname company.company_id%TYPE; BEGIN
   SELECT name INTO cname FROM company
    WHERE company_id = company_id_in;
   RETURN cname;
EXCEPTION WHEN NO_DATA_FOUND THEN   RETURN NULL; END;

You know that this program is a function because the first word in the program is FUNCTION. Other than that, however, it is very difficult to follow the structure of this program. Where is the declaration section? Where does the executable section begin and end?

Here is that same function after we apply some straightforward formatting rules to it:

FUNCTION company_name (company_id_in IN company.company_id%TYPE)
   RETURN VARCHAR2
IS
   cname company.company_id%TYPE;

BEGIN
   SELECT name INTO cname FROM company
    WHERE company_id = company_id_in;
   RETURN cname;

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;
END;

Now it is easy to see that the header of the function consists of:

FUNCTION company_name (company_id_in IN company.company_id%TYPE)
   RETURN VARCHAR2

The declaration section, which comes after the IS and before the BEGIN, clearly consists of a single declaration of the cname variable. The executable section consists of all the statements after the BEGIN and before the EXCEPTION statement; these are indented in from the BEGIN. Finally, the exception section shows a single specific exception handler and a WHEN OTHERS exception.

Generally, indent the statements for a given section from the reserved words which initiate the section. You can also include a blank line before each section, as I do above, for the executable section (before BEGIN) and the exception section (before EXCEPTION). I usually place the IS keyword on its own line to clearly differentiate between the header of a module and its declaration section.