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;