ch18.htm

21精通SQL

<P>The PL/SQL <TT>%ROWCOUNT</TT> attribute maintains a count of rows that the SQL
statements in the particular block have accessed in a cursor.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>DECLARE
  cursor employee_cursor is
    select emp_id, emp_name from employees;
  records_processed := employee_cursor%ROWCOUNT;</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>In this example the variable <TT>records_processed</TT> represents the current
number of rows that the PL/SQL block has accessed in the <TT>employee_cursor</TT>.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Beware of naming conflicts with
	table names when declaring variables. For instance, if you declare a variable that
	has the same name as a table that you are trying to access with the PL/SQL code,
	the local variable will take precedence over the table name. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">The PROCEDURE Section</FONT></H2>
<P>The <TT>PROCEDURE</TT> section is the only mandatory part of a PL/SQL block. This
part of the block calls variables and uses cursors to manipulate data in the database.
The <TT>PROCEDURE</TT> section is the main part of a block, containing conditional
statements and SQL commands.
<H3><FONT COLOR="#000077">BEGIN...END</FONT></H3>
<P>In a block, the <TT>BEGIN</TT> statement denotes the beginning of a procedure.
Similarly, the <TT>END</TT> statement marks the end of a procedure. The following
example shows the basic structure of the <TT>PROCEDURE</TT> section:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">BEGIN
  open a cursor;
  condition1;
    statement1;
  condition2;
    statement2;
  condition3;
    statement3;
.
.
.
  close the cursor;
END
</FONT></PRE>
<H3><FONT COLOR="#000077">Cursor Control Commands</FONT></H3>
<P>Now that you have learned how to define cursors in a PL/SQL block, you need to
know how to access the defined cursors. This section explains the basic cursor control
commands: <TT>DECLARE</TT>, <TT>OPEN</TT>, <TT>FETCH</TT>, and <TT>CLOSE</TT>.
<H4><FONT COLOR="#000077">DECLARE</FONT></H4>
<P>Earlier today you learned how to define a cursor in the <TT>DECLARE</TT> section
of a block. The <TT>DECLARE</TT> statement belongs in the list of cursor control
commands.
<H4><FONT COLOR="#000077">OPEN</FONT></H4>
<P>Now that you have defined your cursor, how do you use it? You cannot use this
book unless you open it. Likewise, you cannot use a cursor until you have opened
it with the <TT>OPEN</TT> command. For example:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">BEGIN
  open employee_cursor;
  statement1;
  statement2;
  .
  .
  .

END
</FONT></PRE>
<H4><FONT COLOR="#000077">FETCH</FONT></H4>
<P><TT>FETCH</TT> populates a variable with values from a cursor. Here are two examples
using <TT>FETCH</TT>: One populates an aggregate variable, and the other populates
individual variables.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>DECLARE
  cursor employee_cursor is
    select emp_id, emp_name from employees;
  employee_record employee_cursor%ROWTYPE;
BEGIN
  open employee_cursor;
  loop
    fetch employee_cursor into employee_record;
  end loop;
  close employee_cursor;
END</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The preceding example fetches the current row of the cursor into the aggregate
variable <TT>employee_record</TT>. It uses a loop to scroll the cursor. Of course,
the block is not actually accomplishing anything.</P>
<PRE><FONT COLOR="#0066FF"><B>DECLARE
  cursor employee_cursor is
    select emp_id, emp_name from employees;
  id_num employees.emp_id%TYPE;
  name employees.emp_name%TYPE;
BEGIN
  open employee_cursor;
  loop
    fetch employee_cursor into id_num, name;
  end loop;
  close employee_cursor;
END
</B></FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This example fetches the current row of the cursor into the variables <TT>id_num</TT>
and <TT>name</TT>, which was defined in the <TT>DECLARE</TT> section.
<H4><FONT COLOR="#000077">CLOSE</FONT></H4>
<P>When you have finished using a cursor in a block, you should close the cursor,
as you normally close a book when you have finished reading it. The command you use
is <TT>CLOSE</TT>.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">BEGIN
  open employee_cursor;
  statement1;
  statement2;
  .
  .
  .
  close employee_cursor;
END
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>After a cursor is closed, the result set of the query no longer exists. You must
reopen the cursor to access the associated set of data.
<H3><FONT COLOR="#000077">Conditional Statements</FONT></H3>
<P>Now we are getting to the good stuff--the conditional statements that give you
control over how your SQL statements are processed. The conditional statements in
PL/SQL resemble those in most third-generation languages.
<H4><FONT COLOR="#000077">IF...THEN</FONT></H4>
<P>The <TT>IF...THEN</TT> statement is probably the most familiar conditional statement
to most programmers. The <TT>IF...THEN</TT> statement dictates the performance of
certain actions if certain conditions are met. The structure of an <TT>IF...THEN</TT>
statement is as follows:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">IF condition1 THEN
  statement1;
END IF;
</FONT></PRE>
<P>If you are checking for two conditions, you can write your statement as follows:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">IF condition1 THEN
  statement1;
ELSE
  statement2;
END IF;
</FONT></PRE>
<P>If you are checking for more than two conditions, you can write your statement
as follows:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">IF condition1 THEN
  statement1;
ELSIF condition2 THEN
  statement2;
ELSE
  statement3;
END IF;
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The final example states: If <TT>condition1</TT> is met, then perform <TT>statement1</TT>;
if <TT>condition2</TT> is met, then perform <TT>statement2</TT>; otherwise, perform
<TT>statement3</TT>. <TT>IF...THEN</TT> statements may also be nested within other
statements and/or loops.
<H4><FONT COLOR="#000077">LOOPS</FONT></H4>
<P>Loops in a PL/SQL block allow statements in the block to be processed continuously
for as long as the specified condition exists. There are three types of loops.</P>
<P><TT>LOOP</TT> is an infinite loop, most often used to scroll a cursor. To terminate
this type of loop, you must specify when to exit. For example, in scrolling a cursor
you would exit the loop after the last row in a cursor has been processed:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>BEGIN
open employee_cursor;
LOOP
  FETCH employee_cursor into employee_record;
  EXIT WHEN employee_cursor%NOTFOUND;
  statement1;
  .
  .
  .
END LOOP;
close employee_cursor;
END;</B>
</FONT></PRE>
<P><TT>%NOTFOUND</TT> is a cursor attribute that identifies when no more data is
found in the cursor. The preceding example exits the loop when no more data is found.
If you omit this statement from the loop, then the loop will continue forever.</P>
<P>The <TT>WHILE-LOOP</TT> executes commands while a specified condition is <TT>TRUE</TT>.
When the condition is no longer true, the loop returns control to the next statement.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>DECLARE
  cursor payment_cursor is
    select cust_id, payment, total_due from payment_table;
  cust_id payment_table.cust_id%TYPE;
  payment payment_table.payment%TYPE;
  total_due payment_table.total_due%TYPE;
BEGIN
  open payment_cursor;
  WHILE payment &lt; total_due LOOP
    FETCH payment_cursor into cust_id, payment, total_due;
    EXIT WHEN payment_cursor%NOTFOUND;
    insert into underpay_table
    values (cust_id, 'STILL OWES');
END LOOP;
  close payment_cursor;
END;</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The preceding example uses the <TT>WHILE-LOOP</TT> to scroll the cursor and to
execute the commands within the loop as long as the condition <TT>payment &lt; total_due</TT>
is met.</P>
<P>You can use the <TT>FOR-LOOP</TT> in the previous block to implicitly fetch the
current row of the cursor into the defined variables.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>DECLARE
  cursor payment_cursor is
    select cust_id, payment, total_due from payment_table;
  cust_id payment_table.cust_id%TYPE;
  payment payment_table.payment%TYPE;
  total_due payment_table.total_due%TYPE;
BEGIN
  open payment_cursor;
  FOR pay_rec IN payment_cursor LOOP
    IF pay_rec.payment &lt; pay_rec.total_due THEN
      insert into underpay_table
      values (pay_rec.cust_id, 'STILL OWES');
    END IF;
  END LOOP;
  close payment_cursor;
END;</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This example uses the <TT>FOR-LOOP</TT> to scroll the cursor. The <TT>FOR-LOOP</TT>
is performing an implicit <TT>FETCH</TT>, which is omitted this time. Also, notice
that the <TT>%NOTFOUND</TT> attribute has been omitted. This attribute is implied
with the <TT>FOR-LOOP</TT>; therefore, this and the previous example yield the same
basic results.
<H2><FONT COLOR="#000077">The EXCEPTION Section</FONT></H2>
<P>The <TT>EXCEPTION</TT> section is an optional part of any PL/SQL block. If this
section is omitted and errors are encountered, the block will be terminated. Some
errors that are encountered may not justify the immediate termination of a block,
so the <TT>EXCEPTION</TT> section can be used to handle specified errors or user-defined
exceptions in an orderly manner. Exceptions can be user-defined, although many exceptions
are predefined by Oracle.
<H3><FONT COLOR="#000077">Raising Exceptions</FONT></H3>
<P>Exceptions are raised in a block by using the command <TT>RAISE</TT>. Exceptions
can be raised explicitly by the programmer, whereas internal database errors are
automatically, or implicitly, raised by the database server.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">BEGIN
  DECLARE
    exception_name EXCEPTION;
  BEGIN
    IF condition THEN
      RAISE exception_name;
    END IF;
  EXCEPTION
    WHEN exception_name THEN
      statement;
  END;
END;
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This block shows the fundamentals of explicitly raising an exception. First <TT>exception_name</TT>
is declared using the <TT>EXCEPTION </TT>statement. In the <TT>PROCEDURE</TT> section,
the exception is raised using <TT>RAISE</TT> if a given condition is met. The <TT>RAISE</TT>
then references the <TT>EXCEPTION</TT> section of the block, where the appropriate
action is taken.
<H3><FONT COLOR="#000077">Handling Exceptions</FONT></H3>
<P>The preceding example handled an exception in the <TT>EXCEPTION</TT> section of
the block. Errors are easily handled in PL/SQL, and by using exceptions, the PL/SQL
block can continue to run with errors or terminate gracefully.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">EXCEPTION
  WHEN exception1 THEN
    statement1;
  WHEN exception2 THEN
    statement2;
  WHEN OTHERS THEN
    statement3;
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This example shows how the <TT>EXCEPTION</TT> section might look if you have more
than one exception. This example expects two exceptions (<TT>exception1</TT> and
<TT>exception2</TT>) when running this block. <TT>WHEN OTHERS</TT> tells <TT>statement3</TT>
to execute if any other exceptions occur while the block is being processed. <TT>WHEN
OTHERS</TT> gives you control over any errors that may occur within the block.
<H3><FONT COLOR="#000077">Executing a PL/SQL Block</FONT></H3>
<P>PL/SQL statements are normally created using a host editor and are executed like
normal SQL script files. PL/SQL uses semicolons to terminate each statement in a
block--from variable assignments to data manipulation commands. The forward slash
(<TT>/</TT>)is mainly associated with SQL script files, but PL/SQL also uses the
forward slash to terminate a block in a script file. The easiest way to start a PL/SQL
block is by issuing the <TT>START</TT> command, abbreviated as <TT>STA</TT> or <TT>@</TT>.</P>
<P>Your PL/SQL script file might look like this:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">/* This file is called proc1.sql */
BEGIN
  DECLARE
    ...
  BEGIN
    ...
    statements;
    ...
  EXCEPTION
    ...
  END;
END;
/
</FONT></PRE>
<P>You execute your PL/SQL script file as follows:</P>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>start proc1 </B>   or
SQL&gt; <B>sta proc1      </B>or
SQL&gt;<B> @proc1</B>
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>PL/SQL script files can be executed