#

| Chapter 3 Effective Coding Style |

## 3.2 Formatting SQL Statements

Because PL/SQL is an extension to the SQL language, you can place SQL statements directly in your PL/SQL programs. You can also define cursors based on SELECT statements. This section summarizes my suggestions for formatting SQL statements and cursors for maximum readability.

PL/SQL supports the use of four SQL DML (Data Manipulation Language) statements: INSERT, UPDATE, DELETE, and SELECT. Each of these statements is composed of a series of "clauses," as in the WHERE clause and the ORDER BY clause. SQL statements can be very complex, to say the least. Without a consistent approach to indentation and alignment inside these statements, you can end up with a real mess. I have found the following guidelines useful:

Right-align the reserved words for the clauses against the DML statement.

  I recommend that you visually separate the SQL reserved words which identify the separate clauses from the application-specific column and table names. The following table shows how I use right-alignment on the reserved words to create a vertical border between them and the rest of the SQL statement:

  SELECT | INSERT | UPDATE | DELETE
  --- | --- | --- | ---
  >     SELECT
  >       FROM

  >      WHERE
  >        AND
  >         OR

  >      GROUP BY

  >     HAVING
  >        AND
  >         OR

  >      ORDER BY
  >     INSERT INTO
  >          VALUES

  >     INSERT INTO
  >          SELECT
  >            FROM
  >           WHERE
  >     UPDATE
  >        SET
  >      WHERE
  >     DELETE
  >       FROM
  >      WHERE

  Here are some examples of this format in use:

  >     SELECT last_name, first_name
  >       FROM employee
  >      WHERE department_id = 15
  >        AND hire_date < SYSDATE;

  >     SELECT department_id, SUM (salary) AS total_salary
  >       FROM employee
  >      GROUP BY department_id
  >      ORDER BY total_salary DESC;

  >     INSERT INTO employee
  >        (employee_id, ... )
  >      VALUES
  >        (105 ... );

  >     DELETE FROM employee
  >           WHERE department_id = 15;

  >     UPDATE employee
  >        SET hire_date = SYSDATE
  >      WHERE hire_date IS NULL
  >        AND termination_date IS NULL;

  Yes, I realize that the GROUP BY and ORDER BY keywords aren't exactly right-aligned to SELECT, but at least the primary words (GROUP and ORDER) are aligned. Notice that within each of the WHERE and HAVING clauses I right-align the AND and OR Boolean connectors under the WHERE keyword.

  This right alignment makes it very easy for me to identify the different clauses of the SQL statement, particularly with extended SELECTs. You might also consider placing a blank line between clauses of longer SQL statements (this is possible in PL/SQL, but is not acceptable in "native" SQL executed in SQL*Plus).

Don't skimp on the use of line separators.

  Within clauses, such separation makes the SQL statement easier to read. In particular, place each expression of the WHERE clause on its own line, and consider using a separate line for each expression in the select list of a SELECT statement. Place each table in the FROM clause on its own line. Certainly, put each separate assignment in a SET clause of the UPDATE statement on its own line. Here are some illustrations of these guidelines:

  >     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);

  >     UPDATE employee
  >        SET hire_date = SYSDATE,
  >            termination_date = NULL
  >      WHERE department_id = 105;

> NOTE: You can place blank lines inside a sql statement when you are coding that sql from within a pl/sql block. You may not, on the other hand, embed white space in sql statements you are executing from the sql*Plus command line.

Use meaningful abbreviations for table and column aliases

  It drives me crazy when a query has a six-table join and the tables have been assigned aliases A, B, C, D, E, and F. How can you possibly decipher the WHERE clause in the following SELECT?

  >     SELECT ... select list ...
  >       FROM employee A, company B, history C, bonus D,
  >            profile E, sales F
  >      WHERE A.company_id = B.company_id
  >        AND A.employee_id = C.employee_id
  >        AND B.company_id = F.company_id
  >        AND A.employee_id = D.employee_id
  >        AND B.company_id = E.company_id;

  With more sensible table aliases (including no tables aliases at all where the table name was short enough already), the relationships are much clearer:

  >     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;

3.1 Fundamentals of Effective Layout |  | 3.3 Formatting Control Structures

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.