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

SQL 和 data base 学习资料

<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></TR></TBODY></TABLE>
  <P class=Body>Notice that in both versions the executable statements are 
  indented three spaces from the column in which the IF and END IF reserved 
  words are found. The only difference between the two formats is the placement 
  of the THEN reserved word. I prefer the new line format, in which the THEN 
  appears on a line by itself after the IF condition. This format provides more 
  whitespace than the other. I could create the whitespace by using a blank, 
  rather than indenting three spaces, but then the executable statements for the 
  IF clause are made distinct from the condition--and they are logically 
  connected. Let's examine some actual code to get a better sense of the 
  differences. 
  <P class=Body>The following example shows proper IF statement indentation with 
  THEN on the same line: <PRE><CODE class=CodeIndent>IF max_sales &gt; 2000 THEN</CODE>
<CODE class=CodeIndent>   notify_accounting ('over_limit');</CODE>
<CODE class=CodeIndent>   RAISE FORM_TRIGGER_FAILURE;</CODE>
<CODE class=CodeIndent>END IF;</CODE>
</PRE>
  <P class=Body>This code has proper IF statement indentation with THEN on the 
  next line: <PRE><CODE class=CodeIndent>IF max_sales &gt; 2000 </CODE>
<CODE class=CodeIndent>THEN</CODE>
<CODE class=CodeIndent>   notify_accounting ('over_limit');</CODE>
<CODE class=CodeIndent>   RAISE FORM_TRIGGER_FAILURE;</CODE>
<CODE class=CodeIndent>END IF;</CODE>
</PRE>
  <H3 class=HeadB>Formatting Loops</H3>
  <P class=Body>You are going to be writing many loops in your PL/SQL programs, 
  and they will usually surround some of the most complicated code in your 
  application. For this reason, the format you use to structure your loops will 
  make a critical difference in the overall comprehensibility of your programs. 
  <P class=Body>PL/SQL offers the following kinds of loops: 
  <UL>
    <LI class=ListBullet>Infinite or simple loop 
    <P></P>
    <LI class=ListBullet>WHILE loop 
    <P></P>
    <LI class=ListBullet>Indexed FOR loop (numeric and cursor) 
    <P></P></LI></UL>
  <P class=Body>Each loop has a loop boundary (begin and end statements) and a 
  loop body. The loop body should be indented from the boundary (again, I 
  recommend three spaces of indentation). 
  <P class=Body>As with the IF statement, you can either choose to leave the 
  LOOP reserved word at the end of the line containing the WHILE and FOR 
  statements or place it on the next line. I prefer the latter, because then 
  both the LOOP and END LOOP reserved words appear at the same column position 
  (indentation) in the program. 
  <P class=Body>Here are my recommendations for formatting your loops: 
  <UL>
    <LI class=ListBullet>The infinite or simple loop: 
    <P></P></LI></UL>
  <P class=CodeIndentKeep>LOOP 
  <P class=CodeIndentKeep>executable_statements; <PRE><CODE class=CodeIndent>END LOOP;</CODE>
<LI class=ListBullet>The WHILE loop:
<P>
<CODE class=CodeIndent>WHILE condition</CODE>
<CODE class=CodeIndent>LOOP</CODE>
<CODE class=CodeIndent>   executable_statements;</CODE>
<CODE class=CodeIndent>END LOOP;</CODE>
</P><LI class=ListBullet>The numeric and cursor FOR loops:
<P>
<CODE class=CodeIndent>FOR for_index IN low_value .. high_value</CODE>
<CODE class=CodeIndent>LOOP</CODE>
<CODE class=CodeIndent>   executable_statements;</CODE>
<CODE class=CodeIndent>END LOOP;</CODE>
<CODE class=CodeIndent>&nbsp;</CODE>
<CODE class=CodeIndent>FOR record_index IN my_cursor</CODE>
<CODE class=CodeIndent>LOOP</CODE>
<CODE class=CodeIndent>   executable_statements;</CODE>
<CODE class=CodeIndent>END LOOP;</CODE>
</P></LI></PRE>
  <H3 class=HeadB>Formatting Exception Handlers</H3>
  <P class=Body>PL/SQL provides a very powerful facility for dealing with 
  errors. An entirely separate exception section contains one or more "handlers" 
  to trap exceptions and execute code when that exception occurs. Logically, the 
  exception section is structured like a conditional CASE statement (which, by 
  the way, is not supported by PL/SQL). 
  <P class=Body>As you might expect, the format for the exception section should 
  resemble that of an IF statement. Here is a general example of the exception 
  section: <PRE><CODE class=CodeIndent>EXCEPTION</CODE>
<CODE class=CodeIndent>   WHEN NO_DATA_FOUND</CODE>
<CODE class=CodeIndent>   THEN</CODE>
<CODE class=CodeIndent>      executable_statements1;</CODE>
<CODE class=CodeIndent>&nbsp;</CODE>
<CODE class=CodeIndent>   WHEN DUP_VAL_ON_INDEX</CODE>
<CODE class=CodeIndent>   THEN</CODE>
<CODE class=CodeIndent>      executable_statements1;</CODE>
<CODE class=CodeIndent>&nbsp;</CODE>
</PRE>
  <P class=CodeIndentKeep>... 
  <P class=CodeIndentKeep>WHEN OTHERS 
  <P class=CodeIndentKeep>THEN 
  <P class=CodeIndentKeep>otherwise_code; <PRE><CODE class=CodeIndent>END;</CODE>
</PRE>
  <P class=Body>Instead of an IF or ELSIF keyword, the exception handler uses 
  the word WHEN. In place of a condition (Boolean expression), the WHEN clause 
  lists an exception name followed by a THEN and finally the executable 
  statements for that exception. In place of ELSE, the exception section offers 
  a WHEN OTHERS clause. 
  <P class=Body>Follow these guidelines: 
  <UL>
    <LI class=ListBullet>Indent each WHEN clause in from the EXCEPTION keyword 
    that indicates the start of the exception section, as I've shown above. 
    Place the THEN directly below the WHEN. 
    <P></P>
    <LI class=ListBullet>Indent all the executable statements for that handler 
    in from the THEN keyword. 
    <P></P>
    <LI class=ListBullet>Place a blank line before each WHEN (except for the 
    first). 
    <P></P></LI></UL>
  <H2 class=HeadA><A name=65283></A>Formatting PL/SQL Blocks</H2>
  <P class=Body>As I've outlined in Chapter 2, every PL/SQL program is 
  structured as a block containing up to four sections: 
  <UL>
    <LI class=ListBullet>Header 
    <P></P>
    <LI class=ListBullet>Declaration section 
    <P></P>
    <LI class=ListBullet>Executable section 
    <P></P>
    <LI class=ListBullet>Exception section 
    <P></P></LI></UL>
  <P class=Body>The PL/SQL block structure forms the backbone of your code. A 
  consistent formatting style for the block, therefore, is critical. This 
  formatting should make clear these different sections. (See Chapter 15, <EM 
  class=ChapterTitle>Procedures and Functions</EM>, for more information about 
  the block structure.) 
  <P class=Body>Consider the following function: <PRE><CODE class=CodeIndent>FUNCTION </CODE>
<CODE class=CodeIndent>company_name (company_id_in IN company.company_id%TYPE)    RETURN </CODE>
<CODE class=CodeIndent>VARCHAR2 IS cname company.company_id%TYPE; BEGIN</CODE>
<CODE class=CodeIndent>   SELECT name INTO cname FROM company</CODE>
<CODE class=CodeIndent>    WHERE company_id = company_id_in;</CODE>
<CODE class=CodeIndent>   RETURN cname;</CODE>
<CODE class=CodeIndent>EXCEPTION WHEN NO_DATA_FOUND THEN   RETURN NULL; END;</CODE>
</PRE>
  <P class=Body>You know that this program is a function because the first word 
  in the program is FUNCTION. Other than that, however, it is very difficult to 
  follow the structure of this program. Where is the declaration section? Where 
  does the executable section begin and end? 
  <P class=BodyKeep>Here is that same function after we apply some 
  straightforward formatting rules to it: 
  <P class=CodeIndentKeep>FUNCTION company_name (company_id_in IN 
  company.company_id%TYPE) 
  <P class=CodeIndentKeep>RETURN VARCHAR2 <PRE><CODE class=CodeIndent>IS</CODE>
<CODE class=CodeIndent>   cname company.company_id%TYPE;</CODE>
<CODE class=CodeIndent>&nbsp;</CODE>
<CODE class=CodeIndent>BEGIN</CODE>
<CODE class=CodeIndent>   SELECT name INTO cname FROM company</CODE>
<CODE class=CodeIndent>    WHERE company_id = company_id_in;</CODE>
<CODE class=CodeIndent>   RETURN cname;</CODE>
<CODE class=CodeIndent>&nbsp;</CODE>
<CODE class=CodeIndent>EXCEPTION</CODE>
<CODE class=CodeIndent>   WHEN NO_DATA_FOUND </CODE>
<CODE class=CodeIndent>   THEN</CODE>
<CODE class=CodeIndent>      RETURN NULL;</CODE>
<CODE class=CodeIndent>END;</CODE>
</PRE>
  <P class=Body>Now it is easy to see that the header of the function consists 
  of: <PRE><CODE class=CodeIndent>FUNCTION company_name (company_id_in IN company.company_id%TYPE) </CODE>
<CODE class=CodeIndent>   RETURN VARCHAR2</CODE>
</PRE>
  <P class=Body>The declaration section, which comes after the IS and before the 
  BEGIN, clearly consists of a single declaration of the cname variable. The 
  executable section consists of all the statements after the BEGIN and before 
  the EXCEPTION statement; these are indented in from the BEGIN. Finally, the 
  exception section shows a single specific exception handler and a WHEN OTHERS 
  exception. 
  <P class=Body>Generally, indent the statements for a given section from the 
  reserved words which initiate the section. You can also include a blank line 
  before each section, as I do above, for the executable section (before BEGIN) 
  and the exception section (before EXCEPTION). I usually place the IS keyword 
  on its own line to clearly differentiate between the header of a module and 
  its declaration section. 
  <H2 class=HeadA><A name=37208></A>Formatting Packages</H2>
  <P class=Body>A package is a collection of related objects, including 
  variables, TYPE statements (to define structures for records, tables, and 
  cursors), exceptions, and modules. We have already covered structuring all the 
  different objects which make up a package. Now, let's take a look at how to 
  structure the package itself. 
  <P class=Body>A package has both a specification and a body. The package 
  specification contains the declarations or definitions of all those objects 
  that are visible outside of the package--the public objects. This means that 
  the objects can be accessed by any account that has been granted EXECUTE 
  authority on the package. The package body contains the implementation of all 
  cursors and modules defined in the specification, and the additional 
  declaration and implementation of all other package objects. If an object, 
  such as a string variable, is declared in the body and not in the package, 
  then any module in the package can reference that variable, but no program 
  outside of the package can see it. That variable is invisible or private to 
  the package. 
  <P class=Body>The first point to make about the package structure is that all 
  objects declared in the specification exist within the context of the package 
  and so should be indented from the PACKAGE statement itself, as shown below: <PRE><CODE class=CodeIndent>PACKAGE rg_select</CODE>
<CODE class=CodeIndent>IS</CODE>
<CODE class=CodeIndent>   list_name VARCHAR2(60);</CODE>
<CODE class=CodeIndent>&nbsp;</CODE>
<CODE class=CodeIndent>   PROCEDURE init_list </CODE>
<CODE class=CodeIndent>      (item_name_in IN VARCHAR2, </CODE>
<CODE class=CodeIndent>       fill_action_in IN VARCHAR2 := 'IMMEDIATE');</CODE>
<CODE class=CodeIndent>   PROCEDURE delete_list;</CODE>
<CODE class=CodeIndent>   PROCEDURE clear_list;</CODE>
<CODE class=CodeIndent>&nbsp;</CODE>
<CODE class=CodeIndent>END rg_select;</CODE>
</PRE>
  <P class=Body>The same is true for the package body. I suggest that you always 
  include a label for the END statement in a package so that you can easily 
  connect up that END with the end of the package as a whole. I place the IS 
  keyword on a new line to set off the first declaration in the package from the 
  name of the package. You could always use a blank line. Notice that I use 
  blank lines in rg_select to segregate different modules which are related by 
  function. I think that logical grouping is always preferable to an arbitrary 
  grouping such as alphabetical order. 
  <P class=Body>The other important element in formatting a package is the order 
  in which objects are listed in the package. I generally list objects in the 
  order of complexity of their structure, as follows: 
  <UL>