3.2 Formatting SQL Statements

Chapter 3
Effective Coding Style

 

---

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;