ch18.htm

21精通SQL

	using the <TT>START</TT> command or the character <TT>@</TT>. PL/SQL script files
	can also be called within other PL/SQL files, shell scripts, or other programs. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077">Displaying Output to the User</FONT></H3>
<P>Particularly when handling exceptions, you may want to display output to keep
users informed about what is taking place. You can display output to convey information,
and you can display your own customized error messages, which will probably make
more sense to the user than an error number. Perhaps you want the user to contact
the database administrator if an error occurs during processing, rather than to see
the exact message.</P>
<P>PL/SQL does not provide a direct method for displaying output as a part of its
syntax, but it does allow you to call a package that serves this function from within
the block. The package is called <TT>DBMS_OUTPUT</TT>.</P>
<PRE><FONT COLOR="#0066FF">EXCEPTION
  WHEN zero_divide THEN
    DBMS_OUTPUT.put_line('ERROR:  DIVISOR IS ZERO.  SEE YOUR DBA.');
</FONT></PRE>
<H5>ANALYSIS:<TT></TT></H5>
<P><TT>ZERO_DIVIDE</TT> is an Oracle predefined exception. Most of the common errors
that occur during program processing will be predefined as exceptions and are raised
implicitly (which means that you don't have to raise the error in the PROCEDURE section
of the block).</P>
<P>If this exception is encountered during block processing, the user will see:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> @block1</B>

ERROR:  DIVISOR IS ZERO.  SEE YOUR DBA.
PL/SQL procedure successfully completed.
</FONT></PRE>
<P>Doesn't that message look friendly than:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>@block1
begin
*</B>

ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 20
</FONT></PRE>
<H2><FONT COLOR="#000077">Transactional Control in PL/SQL</FONT></H2>
<P>On Day 11, &quot;Controlling Transactions,&quot; we discussed the transactional
control commands <TT>COMMIT</TT>, <TT>ROLLBACK</TT>, and <TT>SAVEPOINT</TT>. These
commands allow the programmer to control when transactions are actually written to
the database, how often, and when they should be undone.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">BEGIN
  DECLARE
    ...
  BEGIN
    statements...
    IF condition THEN
      COMMIT;
    ELSE
      ROLLBACK;
    END IF;
    ...
  EXCEPTION
    ...
  END;
END;
</FONT></PRE>
<P>The good thing about PL/SQL is that you can automate the use of transactional
control commands instead of constantly monitoring large transactions, which can be
very tedious.
<H2><FONT COLOR="#000077">Putting Everything Together</FONT></H2>
<P>So far, you have been introduced to PL/SQL, have become familiar with the supported
data types, and are familiar with the major features of a PL/SQL block. You know
how to declare local variables, constants, and cursors. You have also seen how to
embed SQL in the <TT>PROCEDURE</TT> section, manipulate cursors, and raise exceptions.
When a cursor has been raised, you should have a basic understanding of how to handle
it in the <TT>EXCEPTION</TT> section of the block. Now you are ready to work with
some practical examples and create blocks from <TT>BEGIN</TT> to <TT>END</TT>. By
the end of this section, you should fully understand how the parts of a PL/SQL block
interact with each other.
<H3><FONT COLOR="#000077">Sample Tables and Data</FONT></H3>
<P>We will be using two tables to create PL/SQL blocks. <TT>PAYMENT_TABLE</TT> identifies
a customer, how much he or she has paid, and the total amount due. <TT>PAY_STATUS_TABLE</TT>
does not yet contain any data. Data will be inserted into <TT>PAY_STATUS_TABLE</TT>
according to certain conditions in the <TT>PAYMENT_TABLE</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select *</B>
  2  <B>from payment_table;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF">CUSTOMER  PAYMENT TOTAL_DUE
-------- -------- ---------
ABC         90.50    150.99
AAA         79.00     79.00
BBB        950.00   1000.00
CCC         27.50     27.50
DDD        350.00    500.95
EEE         67.89     67.89
FFF        555.55    455.55
GGG        122.36    122.36
HHH         26.75      0.00
9 rows selected.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>describe pay_status_table</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF"> Name                            Null?    Type
 ------------------------------ --------- ----
 CUST_ID                         NOT NULL CHAR(3)
 STATUS                          NOT NULL VARCHAR2(15)
 AMT_OWED                                 NUMBER(8,2)
 AMT_CREDIT                               NUMBER(8,2)
</FONT></PRE>
<H5>ANALYSIS:<TT></TT></H5>
<P><TT>DESCRIBE</TT> is an Oracle SQL command that displays the structure of a table
without having to query the data dictionary. <TT>DESCRIBE</TT> and other Oracle SQL*Plus
commands are covered on Day 20, &quot;SQL*Plus.&quot;
<H3><FONT COLOR="#000077">A Simple PL/SQL Block</FONT></H3>
<P>This is how the PL/SQL script (<TT>block1.sql</TT>) file looks:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>set serveroutput on
BEGIN
  DECLARE
    AmtZero EXCEPTION;
    cCustId payment_table.cust_id%TYPE;
    fPayment payment_table.payment%TYPE;
    fTotalDue payment_table.total_due%TYPE;
    cursor payment_cursor is
      select cust_id, payment, total_due
      from payment_table;
    fOverPaid number(8,2);
    fUnderPaid number(8,2);
  BEGIN
    open payment_cursor;
    loop
      fetch payment_cursor into
        cCustId, fPayment, fTotalDue;
      exit when payment_cursor%NOTFOUND;
      if ( fTotalDue = 0 ) then
        raise AmtZero;
      end if;
      if ( fPayment &gt; fTotalDue ) then
        fOverPaid := fPayment - fTotalDue;
        insert into pay_status_table (cust_id, status, amt_credit)
        values (cCustId, 'Over Paid', fOverPaid);
      elsif ( fPayment &lt; fTotalDue ) then
        fUnderPaid := fTotalDue - fPayment;
        insert into pay_status_table (cust_id, status, amt_owed)
        values (cCustId, 'Still Owes', fUnderPaid);
      else
        insert into pay_status_table
        values (cCustId, 'Paid in Full', null, null);
      end if;
    end loop;
    close payment_cursor;
  EXCEPTION
    when AmtZero then
    DBMS_OUTPUT.put_line('ERROR: amount is Zero. See your supervisor.');
    when OTHERS then
    DBMS_OUTPUT.put_line('ERROR: unknown error. See the DBA');
  END;
END;
/</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The <TT>DECLARE</TT> section defines six local variables, as well as a cursor
called <TT>payment_cursor</TT>. The <TT>PROCEDURE</TT> section starts with the second
<TT>BEGIN</TT> statement in which the first step is to open the cursor and start
a loop. The <TT>FETCH</TT> command passes the current values in the cursor into the
variables that were defined in the <TT>DECLARE</TT> section. As long as the loop
finds records in the cursor, the statement compares the amount paid by a customer
to the total amount due. Overpayments and underpayments are calculated according
to the amount paid, and we use those calculated amounts to insert values into the
<TT>PAY_STATUS_TABLE</TT>. The loop terminates, and the cursor closes. The <TT>EXCEPTION</TT>
section handles errors that may occur during processing.</P>
<P>Now start the PL/SQL script file and see what happens.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> @block1</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF">Input truncated to 1 characters
ERROR: amount is Zero. See your supervisor.
PL/SQL procedure successfully completed.
</FONT></PRE>
<P>Now that you know that an incorrect amount appears in the total due column, you
can fix the amount and run the script again.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>update payment_table</B>
  2  <B>set total_due = 26.75</B>
  3  <B>where cust_id = 'HHH';</B>

1 row updated.

SQL&gt; <B>commit;</B>

Commit complete.

SQL&gt; <B>truncate table pay_status_table;</B>

Table truncated.
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>This example truncates the <TT>PAY_STATUS_TABLE</TT>
	to clear the table's contents; the next run of the statement will repopulate the
	table. You may want to add the <TT>TRUNCATE TABLE</TT> statement to your PL/SQL block.
	
<HR>
</P>

</BLOCKQUOTE>

<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>@block1</B>

Input truncated to 1 characters
PL/SQL procedure successfully completed.
</FONT></PRE>
<P>Now you can select from the <TT>PAY_STATUS_TABLE</TT> and see the payment status
of each customer.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select *</B>
  2  <B>from pay_status_table</B>
  3  <B>order by status;</B>
</FONT></PRE>

<PRE><FONT COLOR="#0066FF">CUSTOMER STATUS          AMT_OWED  AMT_CREDIT
-------- -------------- --------- -----------
FFF      Over Paid                    100.00
AAA      Paid in Full
CCC      Paid in Full
EEE      Paid in Full
GGG      Paid in Full
HHH      Paid in Full
ABC      Still Owes         60.49
DDD      Still Owes        150.95
BBB      Still Owes         50.00

9 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>A row was inserted into <TT>PAY_STATUS_TABLE</TT> for every row of data that is
contained in the <TT>PAYMENT_TABLE</TT>. If the customer paid more than the amount
due, then the difference was input into the <TT>amt_credit</TT> column. If the customer
paid less than the amount owed, then an entry was made in the <TT>amt_owed</TT> column.
If the customer paid in full, then no dollar amount was inserted in either of the
two columns.
<H3><FONT COLOR="#000077">Another Program</FONT></H3>
<P>This example uses a table called <TT>PAY_TABLE</TT>:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>desc pay_table</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF"> Name                            Null?    Type
 ------------------------------  -------- ----
 NAME                            NOT NULL VARCHAR2(20)
 PAY_TYPE                        NOT NULL VARCHAR2(8)
 PAY_RATE                        NOT NULL NUMBER(8,2)
 EFF_DATE                        NOT NULL DATE
 PREV_PAY                                 NUMBER(8,2)
</FONT></PRE>
<P>First take a look at the data:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select *</B>
  2  <B>from pay_table</B>
  3  <B>order by pay_type, pay_rate desc;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF">NAME                 PAY_TYPE  PAY_RATE  EFF_DATE  PREV_PAY
-------------------- -------- --------- --------- ---------
SANDRA SAMUELS       HOURLY       12.50 01-JAN-97
ROBERT BOBAY         HOURLY       11.50 15-MAY-96
KEITH JONES          HOURLY       10.00 31-OCT-96
SUSAN WILLIAMS       HOURLY        9.75 01-MAY-97
CHRISSY ZOES         SALARY    50000.00 01-JAN-97
CLODE EVANS          SALARY    42150.00 01-MAR-97
JOHN SMITH           SALARY    35000.00 15-JUN-96
KEVIN TROLLBERG      SALARY    27500.00 15-JUN-96
8 rows selected.
</FONT></PRE>
<P>Situation: Sales are up. Any individual who has not had a pay increase for six
months (180 days) will receive a raise effective today. All eligible hourly employees
will receive a 4 percent increase, and eligible salary employees will receive a 5
percent increase.</P>
<P>Today is:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select sysdate</B>
  2  <B>from dual;</B>

SYSDATE
--------
20-MAY-97
</FONT></PRE>
<P>Before examining the next PL/SQL block, we will perform a manual select from the
<TT>PAY_TABLE</TT> that flags individuals who should receive a raise.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select name, pay_type, pay_rate, eff_date,</B>
  2         <B>'YES' due</B>
  3 <B> from pay_table</B>
  4  <B>where eff_date &lt; sysdate - 180</B>
  5  <B>UNION ALL</B>
  6  <B>select name, pay_type, pay_rate, eff_date,</B>
  7       <B>  'No' due</B>
  8  <B>from pay_table</B>
  9  <B>where eff_date &gt;= sysdate - 180</B>
 10  <B>order by 2, 3 desc;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF">NAME                 PAY_TYPE  PAY_RATE EFF_DATE  DUE
-------------------- --------- -------- --------- ---
SANDRA SAMUELS       HOURLY       12.50 01-JAN-97 No
ROBERT BOBAY         HOURLY       11.50 15-MAY-96 YES
KEITH JONES          HOURLY       10.00 31-OCT-96 YES
SUSAN WILLIAMS       HOURLY        9.75 01-MAY-97 No
CHRISSY ZOES         SALARY    50000.00 01-JAN-97 No
CLODE EVANS          SALARY    42150.00 01-MAR-97 No
JOHN SMITH           SALARY    35000.00 15-JUN-96 YES
KEVIN TROLLBERG      SALARY    27500.00 15-JUN-96 YES
8 rows selected.
</FONT></PRE>
<P>The <TT>DUE</TT> column identifies individuals who should be eligible for a raise.
Here's the PL/SQL script:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>set serveroutput on
BEGIN
  DECLARE
    UnknownPayType exception;