PL/SQL Coding Standards

Revision date: April 19, 2000

Using Case to Aid Readability

Put reserved words in upper case and application specific identifiers in lower case

IF TO_NUMBER(the_value) > 22
    AND Num1 BETWEEN lval AND hval
THEN
    Newval := 100;
ELSIF TO_NUMBER(the_value) < 1
THEN
    Calc_tots (TO_DATE ('12-JAN-98'));
ELSE
    Clear_vals;
END IF;

Formatting Single Statements

• Use at most one statement per line
  
  New_id := 15;
  Calc_total (new_id);
• Use whitespace inside a statement
  
  WHILE (total_sales < maximum_sales AND company_type = 'NEW') LOOP
• Use spaces to make module calls and their parameter lists more understandable but omit space between module name and left paren.
  
  Calc_totals(company_id, LAST_DAY(end_of_year_date), total_type);

Formatting Declarations

• Place one declaration on each line
  
  DECLARE
      Comp_type VARCHAR2 (3);
      Right_now DATE := SYSDATE;
      Month_num INTEGER;
  
  
• Order your declarations consistently
  • By datatype
    
    DECLARE
        Min_value NUMBER;
        Company_id NUMBER:
    
        Company_name VARCHAR2 (30);
        Employee_name VARCHAR2 (60);
    
        Hire_date DATE;
        Termination_date DATE;
    
    
  • In alphabetical order
    
    DECLARE
        Company_id NUMBER;
        Company_name VARCHAR2 (30);
        Employee_name VARCHAR2 (60);
        Hire_date DATE;
        Min_value NUMBER;
        Termination_date DATE;
    
    
  • By logical relationship
    
    DECLARE
        Company_name VARCHAR2 (30);
        Company_id NUMBER:
    
        Employee_name VARCHAR2 (60);
        Hire_date DATE;
        Termination_date DATE;
    
        Min_value NUMBER;
    
    

Formatting Multiline Statements

• Use indentation to offset all continuation lines under the first line
  
  Generate_company_statistics (company_id, last_year_date,
      Rollup_type, total, average, variance,
      budgeted, next_year_plan);
  
  
• Indent module-call continuation lines to align all parameters vertically
  
  Gen_stats (company_id,
          Last_year_date,
          Rollup_type,
          Total,
          Average,
          Variance,
          Budgeted,
          Next_year_plan);
  
  
• OR
  
  Gen_stats (company_id, last_year_date,
          rollup_type, Total, average, variance,
          budgeted, next_year_plan);
  
  
• Align the remainder of the assignment after the assignment operator
  
  Total_sales :=
      Product_sales (company_id) +
      Service_sales (company_id) admin_cutbacks * .5;
  
  
• Make it very obvious that a statement is continued
  
  Q1_sales :=
      Month1_sales +
      Month2_sales +
      Month3_sales;

Formatting SQL Statements

• Right-align the reserved word for the clauses against the DML statement
  
    SELECT last_name, first_name
      FROM employee
     WHERE department_id = 15
       AND hire_date < SYSDATE;
  
  
• Don't skimp on the use of line separators
  
    SELECT last_name,
           C.name,
           MAX (SH.salary) best_salary_ever
      FROM employee E,
           Company C,
           Salary_history SH
     WHERE E.company_id = C.company_id
       AND E.employee_id = SH.employee_id
       AND E.hire_date > ADD_MONTHS (SYSDATE, -60);
  
  
• Use meaningful abbreviations for table and column aliases
  
    SELECT . . select list . .
      FROM employee emp,
           company co,
           history hist,
           bonus,
           Profile prof,
           sales
     WHERE emp.company_id = co.company_id
       AND emp.employee_id = hist.employee_id
       AND co.company_id = sales.company_id
       AND emp.employee_id = bonus.employee_id
       AND co.company_id = prof.company_id;

Formatting IF Statements

This conditional construct comes in three flavors:

The "THEN" can be placed on the same line as the "IF", or it can be used to begin a new line:

More examples:

IF max_sales > 2000 THEN
    Notify_accounting ('over_limit');
    RAISE FORM_TRIGGER_FAILURE;
END IF;

OR:

IF max_sales > 2000
THEN
    Notify_accounting ('over_limit');
RAISE FORM_TRIGGER_FAILURE;
END IF;

In general:

IF outer_condition-1
THEN
    IF inner_condition-1
    THEN
        Execute_statements-1;
    ELSIF inner_condition-2
    THEN
        Execute_statements-2
    END IF;
ELSIF outer_condition-2
THEN
    . . . and so on . . .
END IF;

Formatting Loops

PL/SQL offers the following kinds of loops:

• Infinite or simple loop
• WHILE loop
• Indexed FOR loop (numeric and cursor)

Here are recommendations for formatting your loops:

• The infinite or simple loop
  
  LOOP
      Exectable_statements;
  END LOOP;
  
  
• The WHILE loop:
  
  WHILE condition
  LOOP
      Executable_statements;
  END LOOP;
  
  OR
  
  WHILE condition LOOP
      Executable_statements;
  END LOOP;
• The numeric and cursor FOR loops:
  
  FOR for_index IN low_value . . high_value
  LOOP
      Executable_statements;
  END LOOP;
  
  FOR record_index IN my_cursor
  LOOP
      Executable_statements;
  END LOOP;
  
  OR
  
  FOR for_index IN low_value . . high_value LOOP
      Executable_statements;
  END LOOP;
  
  FOR record_index IN my_cursor LOOP
      Executable_statements;
  END LOOP;

Formatting Exception Handlers

OR

EXCEPTION
    WHEN exception1 THEN
        Executable_statements1;
    WHEN exception2 THEN
        Executable_statements2;
    . . .
    WHEN OTHERS THEN
        Otherwise_code;
END;

Follow these guidelines:

• Indent all the executable statements for that handler in from the WHEN keyword.
• Place a blank line before each WHEN (except for the first).

Formatting PL/SQL Blocks

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

• Header
• Declaration section
• Executable section
• Exception section

For instance:

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;

Formatting Packages

PACKAGE rg_select
IS
    List_name VARCHAR2(60);

PROCEDURE init_list
    (item_name_in IN VARCHAR2,
    fill_action_in IN VARCHAR2 := 'IMMEDIATE');
PROCEDURE delete_list;
PROCEDURE clear_list;

END rg_select;

Restricted Use of Functions

Functions should be used for returning a single value via the RETURN statement.

Functions should not be used to do anything else, such as update global or database information since this generates side effects and reduces the reusability of functions.