< Day Day Up > |
This final section on SQL guidelines contains advice for creating understandable and easily maintained SQL. When developing an application, you might be tempted to "let it be if it works." This advice is not good. You should strive for well-documented, structured code. The following miscellaneous guidelines will help you achieve that goal with your SQL statements. Code SQL Statements in Block StyleYou should code all SQL in block style. This standard should apply to all SQL code, whether embedded in a COBOL program, coded as a QMF query, or implemented using another tool. Use the following examples as standard templates for the SELECT , INSERT , UPDATE , and DELETE statements. The following is the SELECT statement: EXEC SQL SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME WORKDEPT, PHONENO, EDLEVEL FROM EMP WHERE BONUS = 0 OR SALARY < 10000 OR (BONUS < 500 AND SALARY > 20000) OR EMPNO IN ('000340', '000300', '000010') ORDER BY EMPNO, LASTNAME END-EXEC. The following is the INSERT statement: EXEC SQL INSERT INTO DEPT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT ) VALUES (:HOSTVAR-DEPTNO, :HOSTVAR-DEPTNAME, :HOSTVAR-MGRNO:NULLVAR-MGRNO, :HOSTVAR-ADMRDEPT ) END-EXEC. The following is the DELETE statement: EXEC SQL DELETE FROM DEPT WHERE DEPTNO = 'E21' END-EXEC. The following is the UPDATE statement: EXEC SQL UPDATE EMP SET JOB = 'MANAGER', EDLEVEL = :HOSTVAR-EDLEVEL, COMM = NULL, SALARY = :HOSTVAR-SALARY:NULLVAR-SALARY, BONUS = 1000 WHERE EMPNO = '000220' END-EXEC. These examples demonstrate the following rules:
These examples are embedded SQL syntax, because this shows more detail for coding in the block style. You can easily convert these examples to interactive SQL by removing the EXEC SQL , END_EXEC , and host variable references. Comment All SQL LiberallyComment ad hoc SQL statements using SQL comment syntax. Comment all embedded SQL statements using the syntax of the host language. Code all comments above the SQL statement. Specify the reason for the SQL and the predicted results.
DB2 V7 introduced SQL Assist, a new feature that can aid application developers in coding SQL statements. Consider using SQL Assist to ensure that your SQL statements are syntactically correct. SQL Assist does not provide SQL performance advice, but it is useful for promoting a standard SQL format within your organization ”such as just discussed in the previous guideline. |
< Day Day Up > |