oracle pl-sql programming, 2nd edition chapter 3_ effective coding style.htm

SQL 和 data base 学习资料

    <DD class=FM-ListVariable->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: 
    <P>
    <TABLE border=1>
      <TBODY>
      <TR>
        <TH>
          <P class=CellHeading>SELECT</P></TH>
        <TH>
          <P class=CellHeading>INSERT</P></TH>
        <TH>
          <P class=CellHeading>UPDATE</P></TH>
        <TH>
          <P class=CellHeading>DELETE</P></TH></TR>
      <TR>
        <TD><PRE><CODE class=Code>SELECT</CODE>
<CODE class=Code>&nbsp;</CODE>
<CODE class=Code>  FROM</CODE>
<CODE class=Code>&nbsp;</CODE>
<CODE class=Code> WHERE</CODE>
<CODE class=Code>   AND</CODE>
<CODE class=Code>    OR</CODE>
<CODE class=Code>&nbsp;</CODE>
<CODE class=Code> GROUP BY</CODE>
<CODE class=Code>&nbsp;</CODE>
<CODE class=Code>HAVING</CODE>
<CODE class=Code>   AND</CODE>
<CODE class=Code>    OR</CODE>
<CODE class=Code>&nbsp;</CODE>
<CODE class=Code> ORDER BY</CODE>
</PRE></TD>
        <TD><PRE><CODE class=Code>INSERT INTO</CODE>
<CODE class=Code>     VALUES</CODE>
<CODE class=Code>&nbsp;</CODE>
<CODE class=Code>INSERT INTO</CODE>
<CODE class=Code>     SELECT</CODE>
<CODE class=Code>       FROM</CODE>
<CODE class=Code>      WHERE</CODE>
</PRE></TD>
        <TD><PRE><CODE class=Code>UPDATE</CODE>
<CODE class=Code>   SET</CODE>
<CODE class=Code> WHERE</CODE>
</PRE></TD>
        <TD><PRE><CODE class=Code>DELETE</CODE>
<CODE class=Code>  FROM</CODE>
<CODE class=Code> WHERE</CODE>
</PRE></TD></TR></TBODY></TABLE>
    <P class=ListBullet->Here are some examples of this format in use:</P><CODE 
    class=CodeIndent>SELECT last_name, first_name</CODE> <CODE 
    class=CodeIndent>FROM employee</CODE> <CODE class=CodeIndent>WHERE 
    department_id = 15</CODE> <CODE class=CodeIndent>AND hire_date &lt; 
    SYSDATE;</CODE> <CODE class=CodeIndent>&nbsp;</CODE> <CODE 
    class=CodeIndent>SELECT department_id, SUM (salary) AS total_salary</CODE> 
    <CODE class=CodeIndent>FROM employee</CODE> <CODE class=CodeIndent>GROUP BY 
    department_id</CODE> <CODE class=CodeIndent>ORDER BY total_salary 
    DESC;</CODE> <CODE class=CodeIndent>&nbsp;</CODE> <CODE 
    class=CodeIndent>INSERT INTO employee </CODE><CODE 
    class=CodeIndent>(employee_id, ... )</CODE> <CODE class=CodeIndent>VALUES 
    </CODE><CODE class=CodeIndent>(105 ... );</CODE> <CODE 
    class=CodeIndent>&nbsp;</CODE> <CODE class=CodeIndent>DELETE FROM 
    employee</CODE> <CODE class=CodeIndent>WHERE department_id = 15;</CODE> 
    <CODE class=CodeIndent>&nbsp;</CODE> <CODE class=CodeIndent>UPDATE 
    employee</CODE> <CODE class=CodeIndent>SET hire_date = SYSDATE</CODE> <CODE 
    class=CodeIndent>WHERE hire_date IS NULL</CODE> <CODE class=CodeIndent>AND 
    termination_date IS NULL;</CODE> 
    <P class=ListBullet->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.</P>
    <P class=ListBullet->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).</P>
    <DT class=FM-ListVariableTerm->Don't skimp on the use of line separators. 
    <DD class=FM-ListVariable->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: 
    <P><CODE class=CodeIndent>SELECT last_name,</CODE> <CODE 
    class=CodeIndent>C.name,</CODE> <CODE class=CodeIndent>MAX (SH.salary) 
    best_salary_ever</CODE> <CODE class=CodeIndent>FROM employee E,</CODE> <CODE 
    class=CodeIndent>company C,</CODE> <CODE class=CodeIndent>salary_history 
    SH</CODE> <CODE class=CodeIndent>WHERE E.company_id = C.company_id</CODE> 
    <CODE class=CodeIndent>AND E.employee_id = SH.employee_id</CODE> <CODE 
    class=CodeIndent>AND E.hire_date &gt; ADD_MONTHS (SYSDATE, -60);</CODE> 
    <CODE class=CodeIndent>&nbsp;</CODE> <CODE class=CodeIndent>UPDATE 
    employee</CODE> <CODE class=CodeIndent>SET hire_date = SYSDATE,</CODE> <CODE 
    class=CodeIndent>termination_date = NULL</CODE> <CODE class=CodeIndent>WHERE 
    department_id = 105;</CODE> 
    <P class=Note><B>NOTE:</B> 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.</P></DD></DL>
  <UL>
    <LI class=ListBullet>Use meaningful abbreviations for table and column 
    aliases 
    <P>
    <P class=ListBullet->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? <CODE 
    class=CodeIndent>SELECT ... select list ...</CODE> <CODE 
    class=CodeIndent>FROM employee A, company B, history C, bonus D, 
    </CODE><CODE class=CodeIndent>profile E, sales F</CODE> <CODE 
    class=CodeIndent>WHERE A.company_id = B.company_id</CODE> <CODE 
    class=CodeIndent>AND A.employee_id = C.employee_id</CODE> <CODE 
    class=CodeIndent>AND B.company_id = F.company_id</CODE> <CODE 
    class=CodeIndent>AND A.employee_id = D.employee_id</CODE> <CODE 
    class=CodeIndent>AND B.company_id = E.company_id;</CODE> 
    <P class=ListBullet->With more sensible table aliases (including no tables 
    aliases at all where the table name was short enough already), the 
    relationships are much clearer: </P></LI></UL>
  <P class=CodeIndentKeep>SELECT ... select list ... 
  <P class=CodeIndentKeep>FROM employee EMP, company CO, history HIST, bonus, <PRE><CODE class=CodeIndent>       profile PROF, sales</CODE>
<CODE class=CodeIndent> WHERE EMP.company_id = CO.company_id</CODE>
<CODE class=CodeIndent>   AND EMP.employee_id = HIST.employee_id</CODE>
<CODE class=CodeIndent>   AND CO.company_id = SALES.company_id</CODE>
<CODE class=CodeIndent>   AND EMP.employee_id = BONUS.employee_id</CODE>
<CODE class=CodeIndent>   AND CO.company_id = PROF.company_id;</CODE>
</PRE>
  <H2 class=HeadA><A name=61382></A>Formatting Control Structures</H2>
  <P class=Body>The control structures in your program are the most direct 
  representation of the logic needed to implement your specifications. The 
  format of these control structures, therefore, will have a significant impact 
  on the readability of your code. 
  <P class=Body>Indentation is the most important element of control structure 
  layout. Always keep statements of the same "logical level" at the same 
  indentation level. Let's see what this means for the various control 
  structures of PL/SQL. 
  <H3 class=HeadB>Formatting IF Statements</H3>
  <P class=Body>This conditional construct comes in three flavors: 
  <P>
  <TABLE>
    <TBODY>
    <TR>
      <TD><PRE><CODE class=Code>IF &lt;expression&gt;</CODE>
<CODE class=Code>END IF;</CODE>
</PRE></TD>
      <TD><PRE><CODE class=Code>IF &lt;expression&gt;</CODE>
<CODE class=Code>ELSE</CODE>
<CODE class=Code>END IF;</CODE>
</PRE></TD>
      <TD><PRE><CODE class=Code>IF &lt;expression&gt;</CODE>
<CODE class=Code>ELSIF &lt;expression&gt;</CODE>
<CODE class=Code>ELSE</CODE>
<CODE class=Code>END IF; </CODE>
</PRE></TD></TR></TBODY></TABLE>
  <P class=Body>In general, the IF statement is composed of clauses in which 
  there is a Boolean expression or condition and a section of code executed when 
  that condition evaluates to TRUE. 
  <P class=Body>So if you want to use indentation to reveal the logical 
  structure of the simplest form of the IF statement (IF-END IF), I suggest one 
  of these two styles: 
  <P>
  <TABLE border=1>
    <TBODY>
    <TR>
      <TH>
        <P class=CellHeading>New Line for THEN </P></TH>
      <TH>
        <P class=CellHeading>Same Line for THEN </P></TH></TR>
    <TR>
      <TD><PRE><CODE class=Code>IF &lt;expression&gt;</CODE>
<CODE class=Code>THEN</CODE>
<CODE class=Code>   executable_statements;</CODE>
<CODE class=Code>END IF;</CODE>
</PRE></TD>
      <TD><PRE><CODE class=Code>IF &lt;expression&gt; THEN</CODE>
<CODE class=Code>   executable_statements</CODE>
<CODE class=Code>END IF;</CODE>
</PRE></TD></TR>
    <TR>
      <TD><PRE><CODE class=Code>IF &lt;expression&gt;</CODE>
<CODE class=Code>THEN</CODE>
<CODE class=Code>   executable_statements;</CODE>
<CODE class=Code>ELSE</CODE>
<CODE class=Code>   else_executable_statements;</CODE>
<CODE class=Code>END IF;</CODE>
</PRE></TD>
      <TD><PRE><CODE class=Code>IF &lt;expression&gt; THEN</CODE>
<CODE class=Code>   executable_statements</CODE>
<CODE class=Code>ELSE</CODE>
<CODE class=Code>   else_executable_statements;</CODE>
<CODE class=Code>END IF;</CODE>
</PRE></TD></TR>
    <TR>
      <TD><PRE><CODE class=Code>IF &lt;expression1&gt;1</CODE>
<CODE class=Code>THEN</CODE>
<CODE class=Code>   executable_statements1;</CODE>
<CODE class=Code>&nbsp;</CODE>
<CODE class=Code>ELSIF &lt;expression2&gt;</CODE>
<CODE class=Code>THEN</CODE>
<CODE class=Code>   executable_statements2;</CODE>
<CODE class=Code>...</CODE>
<CODE class=Code>&nbsp;</CODE>
<CODE class=Code>ELSIF &lt;expressionN&gt;</CODE>
<CODE class=Code>THEN</CODE>
<CODE class=Code>   executable_statementsN;</CODE>
<CODE class=Code>&nbsp;</CODE>
<CODE class=Code>ELSE</CODE>
<CODE class=Code>   else_executable_statements;</CODE>
<CODE class=Code>END IF;</CODE>
</PRE></TD>
      <TD><PRE><CODE class=Code>IF &lt;expression1&gt; THEN</CODE>
<CODE class=Code>   executable_statements1;</CODE>
<CODE class=Code>&nbsp;</CODE>
<CODE class=Code>ELSIF &lt;expression2&gt; THEN</CODE>
<CODE class=Code>   executable_statements2;</CODE>
<CODE class=Code>...</CODE>
<CODE class=Code>&nbsp;</CODE>
<CODE class=Code>ELSIF &lt;expressionN&gt; THEN</CODE>
<CODE class=Code>   executable_statementsN;</CODE>