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

SQL 和 data base 学习资料

  assignments, calls to modules, and declarations. A consistent approach to 
  formatting and grouping such statements will improve the readability of your 
  program as a whole. This section suggests some guidelines.</P>
  <H4 class=HeadC>Use at most one statement per line</H4>
  <P class=Body>As we discussed in Chapter 2, <EM class=ChapterTitle>PL/SQL 
  Language Fundamentals</EM>, PL/SQL uses the semicolon (<KBD 
  class=Command>;</KBD>) as the logical terminator for a statement. As a result 
  you can have more than one statement on a line and you can continue a single 
  executable statement over more than one line. You will sometimes be tempted to 
  place several statements on a single line, particularly if they are very 
  simple. Consider the following line:</P><PRE><CODE class=CodeIndent>new_id := 15; calc_total (new_id); max_dollars := 105 * sales_adj; </CODE>
</PRE>
  <P class=BodyKeep>It is very difficult to pick out the individual statements 
  in this line, in addition to the fact that a procedure is called in the middle 
  of the line. By placing each statement on its own line you mirror the 
  complexity of a program--the simple lines look simple and the complex 
  statements look complex--and reinforce the top-to-bottom logic of the program: 
  </P><PRE><CODE class=CodeIndent>new_id := 15; </CODE>
<CODE class=CodeIndent>calc_total (new_id); </CODE>
<CODE class=CodeIndent>max_dollars := 105 * sales_adj; </CODE>
</PRE>
  <P class=Body>You can scan the left margin (which will move left and right 
  depending on the logic and corresponding indentation) and know that you are 
  reviewing all the lines of code. </P>
  <H4 class=HeadC>Use whitespace inside a statement</H4>
  <P class=BodyKeep>You can use all the indentation and blank lines you want to 
  reveal the logic of a program and still end up with some very dense and 
  unreadable code. It is also important to employ whitespace within a single 
  line to make that one statement more comprehensible. Here are two general 
  rules I employ in my code: </P>
  <UL>
    <LI class=ListBullet>Always include a space between every identifier and 
    separator in a statement. Instead of this: 
    <P><CODE class=CodeIndent>WHILE(total_sales&lt;maximum_sales AND 
    company_type='NEW')LOOP</CODE> 
    <P class=ListBullet->write this:</P><CODE class=CodeIndent>WHILE 
    (total_sales &lt; maximum_sales AND company_type = 'NEW') LOOP</CODE> 
    <LI class=ListBullet>Use spaces to make module calls and their parameter 
    lists more understandable. Instead of this: 
    <P><CODE 
    class=CodeIndent>calc_totals(company_id,LAST_DAY(end_of_year_date),total_type);</CODE> 

    <P class=ListBullet->write this:</P><CODE class=CodeIndent>calc_totals 
    (company_id, LAST_DAY (end_of_year_date), total_type);</CODE> </LI></UL>
  <H3 class=HeadB>Formatting Your Declarations</H3>
  <P class=Body>The declaration section declares the local variables and other 
  structures to be in your PL/SQL block. This section comes right at the top of 
  the block, so it sets the first impression for the rest of the program. If the 
  declaration section has no apparent order and is poorly formatted, it is 
  unlikely that anything else in that program will be easily understood. </P>
  <P class=Body>The declaration section in PL/SQL can contain many different 
  kinds of declarations: simple, scalar variables; complex data structures like 
  records and tables; exceptions; even entire subprograms which exist only in 
  that program.</P>
  <P class=Body>The following sections give some guidelines for creating your 
  declaration statements.</P>
  <H4 class=HeadC>Place one declaration on each line</H4>
  <P class=Body>You will be particularly tempted to "double up" declarations on 
  a single line because, in general, declarations are very short in length. 
  Resist that temptation! Which of the following sets of declarations would you 
  prefer to try to understand at a glance?</P><PRE><CODE class=CodeIndent>DECLARE</CODE>
<CODE class=CodeIndent>   comp_type VARCHAR2(3); right_now DATE := SYSDATE; month_num INTEGER; </CODE>
</PRE>
  <P class=BodyKeep>or:</P><PRE><CODE class=CodeIndent>DECLARE</CODE>
<CODE class=CodeIndent>   comp_type VARCHAR2(3); </CODE>
<CODE class=CodeIndent>   right_now DATE := SYSDATE; </CODE>
<CODE class=CodeIndent>   month_num INTEGER; </CODE>
</PRE>
  <H4 class=HeadC>Ignore alignment for declarations</H4>
  <P class=Body>Many programmers like to align their declarations--for 
  example:</P><PRE><CODE class=CodeIndent>DECLARE</CODE>
<CODE class=CodeIndent>   company_name      VARCHAR2(30);</CODE>
<CODE class=CodeIndent>   company_id        INTEGER;</CODE>
<CODE class=CodeIndent>&nbsp;</CODE>
<CODE class=CodeIndent>   employee_name     VARCHAR2(60);</CODE>
<CODE class=CodeIndent>   hire_date         DATE;</CODE>
<CODE class=CodeIndent>   termination_date  DATE;</CODE>
<CODE class=CodeIndent>&nbsp;</CODE>
<CODE class=CodeIndent>   min_value         NUMBER;</CODE>
</PRE>
  <P class=Body>I am not convinced of the value of declaration alignment. 
  Although alignment makes it easier to scan down the datatypes, the datatype 
  isn't nearly as important as the identifier, which is already left-justified. 
  A commitment to alignment also raises all kinds of questions that consume a 
  developer's time and thought processes: If you have one long variable name, do 
  you have to move all the other datatypes out to match that datatype 
  declaration? What about when you add a new, very long declaration into an 
  existing section? Do you have to go back and add a tab or two to the existing 
  declarations? </P>
  <P class=Body>The elegance of alignment also breaks down when you include 
  comments above individual declarations, as shown in the following example:</P><PRE><CODE class=CodeIndent>DECLARE</CODE>
<CODE class=CodeIndent>   company_name      VARCHAR2(30);</CODE>
<CODE class=CodeIndent>   /* Primary key into company table */</CODE>
<CODE class=CodeIndent>   company_id        INTEGER;</CODE>
<CODE class=CodeIndent>&nbsp;</CODE>
<CODE class=CodeIndent>   employee_name     VARCHAR2(60);</CODE>
<CODE class=CodeIndent>   /* Date hired; must be no greater than today's date. */</CODE>
<CODE class=CodeIndent>   hire_date         DATE;</CODE>
<CODE class=CodeIndent>   termination_date  DATE;</CODE>
<CODE class=CodeIndent>&nbsp;</CODE>
<CODE class=CodeIndent>   min_value         NUMBER;</CODE>
</PRE>
  <P class=Body>When the comment text cuts across the vast spaces of the 
  alignment tabs, it just makes the datatype look isolated from its 
  identifier.</P>
  <P class=Body>I believe that you are better off ignoring alignment for 
  declarations.<A class=footnote title=Footnote 
  href="http://www.oreilly.com/catalog/oraclep2/chapter/ch03.html#footnote-1">[1]</A> 
  Keep the elements of the declaration (datatype and default value) close to the 
  identifier.</P>
  <H3 class=HeadB>Formatting Multiline Statements </H3>
  <P class=Body>Because a statement is terminated by a semicolon (<KBD 
  class=Command>;</KBD>) rather than by the physical end of the line, statements 
  can be continued onto additional lines without any specific continuation 
  symbol or operator. This makes it very easy to spread a statement across more 
  than one line, but it can also make it difficult to read across these lines. 
  </P>
  <P class=Body>Here are some examples of multiline statements that are hard to 
  follow:</P><PRE><CODE class=CodeIndent>IF total_sales &lt; maximum_sales AND company_type = 'NEW' AND (override</CODE>
<CODE class=CodeIndent>= 'Y' OR total_company_revenue &lt; planned_revenue (SYSDATE))</CODE>
<CODE class=CodeIndent>THEN</CODE>
<CODE class=CodeIndent>   accept_order;</CODE>
<CODE class=CodeIndent>END IF;</CODE>
<CODE class=CodeIndent>&nbsp;</CODE>
<CODE class=CodeIndent>generate_company_statistics (company_id, last_year_date</CODE>
<CODE class=CodeIndent>, rollup_type, total, average, variance, budgeted, next_year_plan);</CODE>
<CODE class=CodeIndent>&nbsp;</CODE>
<CODE class=CodeIndent>total_sales := product_sales (company_id) + admin_cutbacks * </CODE>
<CODE class=CodeIndent>.5 - overhead - golden_parachutes;</CODE>
</PRE>
  <P class=Body>The format of these continuation lines highlights a key 
  question: How do you best break up a complex expression so the different parts 
  can be read clearly, but still be connected to the statement as a whole? The 
  following guidelines respond to this question and produce much cleaner 
  code.</P>
  <H4 class=HeadC>Use indentation to offset all continuation lines under the 
  first line.</H4>
  <P class=Body>This is the most important guideline. The best way to identify 
  continuation lines is to use indentation to logically subsume those lines 
  under the main or first line of the statement. The following call to 
  generate_company_statistics is obscured because the continuation line butts 
  right up against the left margin with the module name:</P><PRE><CODE class=CodeIndent>generate_company_statistics (company_id, last_year_date,</CODE>
<CODE class=CodeIndent>rollup_type, total, average, variance, budgeted, next_year_plan);</CODE>
</PRE>
  <P class=Body>If I indent the continuation line, the relationship of the 
  second line to the first becomes clear:</P><PRE><CODE class=CodeIndent>generate_company_statistics (company_id, last_year_date,</CODE>
<CODE class=CodeIndent>   rollup_type, total, average, variance, budgeted, next_year_plan);</CODE>
</PRE>
  <P class=Body>This attempt to recode, however, shows that simply adding an 
  indentation isn't always enough. While it is clear that the line starting with 
  rollup_type "belongs" to the previous line, the relationship of the text on 
  the continuation line to that of the first line is unclear. We need more than 
  a simple call to "Indent." There are several possible approaches:</P>
  <DL>
    <DT class=ListVariableTerm>Indent module-call continuation lines to align 
    all parameters vertically. 
    <DD class=ListVariable>You can place a single parameter on each line for 
    maximum clarity, or include more than one parameter on a line--as long as 
    they are properly indented. You can even break up the parameters so that 
    related parameters go together on separate lines. If the name of the 
    procedure is long and results in pushing the alignment column for parameters 
    too far to the right, start the entire parameter list on the next line 
    (indented) and then align all parameters against that second line. Here are 
    some examples illustrating these rules: 
    <P><CODE class=CodeIndent>gen_stats (company_id, last_year_date, 
    rollup_type, </CODE><CODE class=CodeIndent>total, average, variance, 
    budgeted, next_year_plan);</CODE> <CODE class=CodeIndent>&nbsp;</CODE> <CODE 
    class=CodeIndent>gen_stats (company_id, </CODE><CODE 
    class=CodeIndent>last_year_date, </CODE><CODE class=CodeIndent>rollup_type, 
    </CODE><CODE class=CodeIndent>total, </CODE><CODE class=CodeIndent>average, 
    </CODE><CODE class=CodeIndent>variance, </CODE><CODE 
    class=CodeIndent>budgeted, </CODE><CODE 
    class=CodeIndent>next_year_plan);</CODE> <CODE 
    class=CodeIndent>&nbsp;</CODE> <CODE class=CodeIndent>gen_stats </CODE><CODE 
    class=CodeIndent>(company_id, last_year_date, rollup_type, </CODE><CODE 
    class=CodeIndent>total, average, variance, budgeted, next_year_plan);</CODE> 
    </P>
    <DD class=ListVariable>I prefer the third alternative, in which all 
    parameters are moved to the line following the name of the module. You can 
    then place multiple parameters on the same line or place one parameter on 
    each line, but the indentation is always and only the standard three spaces 
    in from the start of the module name. 
    <P></P>
    <DT class=ListVariableTerm>Make it very obvious that a statement is 
    continued. 
    <DD class=ListVariable>If a statement is not going to fit onto a single 
    line, break up the statement so that it is quite obvious, with the most 
    casual glance, that the first line could not possibly finish the statement. 
    The following examples highlight this approach: 
    <P>
    <LI class=FM-ListBullet>The IN statement of a loop clearly needs its range: 
    <P><CODE class=CodeIndent>FOR month_index IN</CODE> <CODE 
    class=CodeIndent>first_month .. last_month</CODE> <CODE 
    class=CodeIndent>LOOP</CODE> <CODE class=CodeIndent>...</CODE> </P>
    <LI class=FM-ListBullet>An assignment could not possibly end with a "+": 
    <P><CODE class=CodeIndent>q1_sales := </CODE><CODE 
    class=CodeIndent>month1_sales +</CODE> <CODE class=CodeIndent>month2_sales 
    +</CODE> <CODE class=CodeIndent>month3_sales;</CODE> </P>
    <LI class=FM-ListBullet>The last comma in the first line of parameters 
    indicates that other parameters follow: 
    <P><CODE class=CodeIndent>generate_company_statistics </CODE><CODE 
    class=CodeIndent>(company_id, last_year_date,</CODE> <CODE 
    class=CodeIndent>rollup_type, total, average, variance, budgeted, 
    next_year_plan);</CODE> </P></LI></DD></DL>
  <H2 class=HeadA><A name=64385></A>Formatting SQL Statements</H2>
  <P class=Body>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.</P>
  <P class=Body>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:</P>
  <DL>
    <DT class=FM-ListVariableTerm->Right-align the reserved words for the 
    clauses against the DML statement.