ch18.htm

Learn SQL in an easy way...

    cursor pay_cursor is
      select name, pay_type, pay_rate, eff_date,
             sysdate, rowid
      from pay_table;
    IndRec pay_cursor%ROWTYPE;
    cOldDate date;
    fNewPay number(8,2);
  BEGIN
    open pay_cursor;
    loop
    fetch pay_cursor into IndRec;
    exit when pay_cursor%NOTFOUND;
    cOldDate := sysdate - 180;
    if (IndRec.pay_type = 'SALARY') then
      fNewPay := IndRec.pay_rate * 1.05;
    elsif (IndRec.pay_type = 'HOURLY') then
      fNewPay := IndRec.pay_rate * 1.04;
    else
      raise UnknownPayType;
    end if;
    if (IndRec.eff_date < cOldDate) then
      update pay_table
      set pay_rate = fNewPay,
          prev_pay = IndRec.pay_rate,
          eff_date = IndRec.sysdate
      where rowid = IndRec.rowid;
      commit;
    end if;
    end loop;
    close pay_cursor;
  EXCEPTION
    when UnknownPayType then
      dbms_output.put_line('=======================');
      dbms_output.put_line('ERROR: Aborting program.');
      dbms_output.put_line('Unknown Pay Type for Name');
    when others then
      dbms_output.put_line('ERROR During Processing.  See the DBA.');
    END;
END;
/</B>
</FONT></PRE>
<P>Are you sure that you want to give four employees a pay raise? (The final <TT>SELECT</TT>
statement has four <TT>Yes</TT> values in the <TT>DUE</TT> column.) Why not...let's
give all four employees a raise. You can apply the appropriate pay increases by executing
the PL/SQL script file, named <TT>block2.sql</TT>:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>@block2</B>

Input truncated to 1 characters
PL/SQL procedure successfully completed.
</FONT></PRE>
<P>You can do a quick select to verify that the changes have been made to the <TT>pay_rate</TT>
of the appropriate individuals:</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.96 20-MAY-97       11.5
KEITH JONES          HOURLY       10.40 20-MAY-97         10
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    36750.00 20-MAY-97      35000
KEVIN TROLLBERG      SALARY    28875.00 20-MAY-97      27500

8 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Four employees received a pay increase. If you compare this output to the output
of the original <TT>SELECT</TT> statement, you can see the changes. The current pay
rate was updated to reflect the pay increase, the original pay rate was inserted
into the previous pay column, and the effective date was updated to today's date.
No action was taken on those individuals who did not qualify for a pay increase.</P>
<P>Wait--you didn't get a chance to see how the defined exception works. You can
test the <TT>EXCEPTION</TT> section by inserting an invalid <TT>PAY_TYPE</TT> into
<TT>PAY_TABLE</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>insert into pay_table values</B>
  2  <B>('JEFF JENNINGS','WEEKLY',71.50,'01-JAN-97',NULL);</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF">1 row created.
</FONT></PRE>
<P>The moment of truth:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> @block2</B>

Input truncated to 1 characters
=======================
ERROR: Aborting program.
Unknown Pay Type for:  JEFF JENNINGS
PL/SQL procedure successfully completed.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>An error message told you that <TT>JEFF JENNINGS</TT> had a <TT>Pay Type</TT>
with a value other than <TT>SALARY</TT> or <TT>HOURLY</TT>. That is, the exception
was handled with an error message.
<H2><FONT COLOR="#000077">Stored Procedures, Packages, and Triggers</FONT></H2>
<P>Using PL/SQL, you can create stored objects to eliminate having to constantly
enter monotonous code. Procedures are simply blocks of code that perform some sort
of specific function. Related procedures can be combined and stored together in an
object called a package. A trigger is a database object that is used with other transactions.
You might have a trigger on a table called <TT>ORDERS</TT> that will insert data
into a <TT>HISTORY</TT> table each time the <TT>ORDERS</TT> table receives data.
The basic syntax of these objects follows.
<H3><FONT COLOR="#000077">Sample Procedure</FONT></H3>
<H5><FONT COLOR="#000000">SYNTAX:</FONT><FONT COLOR="#000077"></FONT></H5>
<PRE><FONT COLOR="#0066FF">PROCEDURE procedure_name IS
  variable1 datatype;
  ...
BEGIN
  statement1;
  ...
EXCEPTION
  when ...
END procedure_name;
</FONT></PRE>
<H3><FONT COLOR="#000077">Sample Package</FONT></H3>
<H5><FONT COLOR="#000000">SYNTAX:</FONT><FONT COLOR="#000077"></FONT></H5>
<PRE><FONT COLOR="#0066FF">CREATE PACKAGE package_name AS
  PROCEDURE procedure1 (global_variable1 datatype, ...);
  PROCEDURE procedure2 (global_variable1 datatype, ...);
END package_name;
CREATE PACKAGE BODY package_name AS
  PROCEDURE procedure1 (global_variable1 datatype, ...) IS
    BEGIN
      statement1;
      ...
    END procedure1;
  PROCEDURE procedure2 (global_variable1 datatype, ...) IS
    BEGIN
      statement1;
      ...
  END procedure2;
END package_name;
</FONT></PRE>
<H3><FONT COLOR="#000077">Sample Trigger</FONT></H3>
<H5><FONT COLOR="#000000">SYNTAX:</FONT><FONT COLOR="#000077"></FONT></H5>
<PRE><FONT COLOR="#0066FF">  CREATE TRIGGER trigger_name
    AFTER UPDATE OF column ON table_name
    FOR EACH ROW
  BEGIN
    statement1;
    ...
  END;
</FONT></PRE>
<P>The following example uses a trigger to insert a row of data into a transaction
table when updating <TT>PAY_TABLE</TT>. The <TT>TRANSACTION</TT> table looks like
this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> describe trans_table</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF"> Name                            Null?    Type
 ------------------------------  -------- ----
 ACTION                                   VARCHAR2(10)
 NAME                                     VARCHAR2(20)
 PREV_PAY                                 NUMBER(8,2)
 CURR_PAY                                 NUMBER(8,2)
 EFF_DATE                                 DATE
</FONT></PRE>
<P>Here's a sample row of data:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select *</B>
  2  <B>from pay_table</B>
  3  <B>where name = 'JEFF JENNINGS';</B>

NAME                 PAY_TYPE  PAY_RATE EFF_DATE   PREV_PAY
-------------------- -------- --------- -------- ----------
JEFF JENNINGS        WEEKLY       71.50 01-JAN-97
</FONT></PRE>
<P>Now, create a trigger:</P>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE TRIGGER pay_trigger</B>
  2    <B>AFTER update on PAY_TABLE</B>
  3   <B> FOR EACH ROW</B>
  4  <B>BEGIN</B>
  5    <B>insert into trans_table values</B>
  6    <B>('PAY CHANGE', :new.name, :old.pay_rate,</B>
  7    <B> :new.pay_rate, :new.eff_date);</B>
  8<B>  END;
</B>  9 <B> /</B>

Trigger created.
</FONT></PRE>
<P>The last step is to perform an update on <TT>PAY_TABLE</TT>, which should cause
the trigger to be executed.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>update pay_table</B>
  2  <B>set pay_rate = 15.50,</B>
  3     <B> eff_date = sysdate</B>
  4 <B> where name = 'JEFF JENNINGS';</B>

1 row updated.

SQL&gt; <B>select *</B>
  2  <B>from pay_table</B>
  3  <B>where name = 'JEFF JENNINGS';</B>

NAME                 PAY_TYPE  PAY_RATE EFF_DATE   PREV_PAY
-------------------- -------- --------- -------- ----------
JEFF JENNINGS        WEEKLY       15.50 20-MAY-97

SQL&gt; <B>select *</B>
  2  <B>from trans_table;</B>

ACTION     NAME                   PREV_PAY   CURR_PAY EFF_DATE
---------- -------------------- ---------- ---------- ---------
PAY CHANGE JEFF JENNINGS              71.5       15.5 20-MAY-97
</FONT></PRE>
<H5>ANALYSIS:<TT></TT></H5>
<P><TT>PREV_PAY</TT> is null in <TT>PAY_TABLE</TT> but <TT>PREV_PAY</TT> appears
in <TT>TRANS_TABLE</TT>. This approach isn't as confusing as it sounds. <TT>PAY_TABLE</TT>
does not need an entry for <TT>PREV_PAY</TT> because the <TT>PAY_RATE</TT> of <TT>71.50</TT>
per hour was obviously an erroneous amount. Rather, we inserted the value for <TT>PREV_PAY</TT>
in <TT>TRANS_TABLE</TT> because the update was a transaction, and the purpose of
<TT>TRANS_PAY</TT> is to keep a record of all transactions against <TT>PAY_TABLE</TT>.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>If you are familiar with network
	technologies, you might notice similarities between PL/SQL and Java stored procedures.
	However, some differences should be noted. PL/SQL is an enhancement of standard SQL,
	implementing the commands of a procedural language. Java, which is much more advanced
	than PL/SQL, allows programmers to write more complex programs than are possible
	with PL/SQL. PL/SQL is based on the database-intensive functionality of SQL; Java
	is more appropriate for CPU-intensive programs. Most procedural languages, such as
	PL/SQL, are developed specifically for the appropriate platform. As procedural language
	technology evolves, a higher level of standardization will be enforced across platforms.
	
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>PL/SQL extends the functionality of standard SQL. The basic components of PL/SQL
perform the same types of functions as a third-generation language. The use of local
variables supports dynamic code; that is, values within a block may change from time
to time according to user input, specified conditions, or the contents of a cursor.
PL/SQL uses standard procedural language program control statements. <TT>IF...THEN</TT>
statements and loops enable you to search for specific conditions; you can also use
loops to scroll through the contents of a defined cursor.</P>
<P>Errors that occur during the processing of any program are a major concern. PL/SQL
enables you to use exceptions to control the behavior of a program that encounters
either syntax errors or logical errors. Many exceptions are predefined, such as a
divide-by-zero error. Errors can be raised any time during processing according to
specified conditions and may be handled any way the PL/SQL programmer desires.</P>
<P>Day 18 also introduces some practical uses of PL/SQL. Database objects such as
triggers, stored procedures, and packages can automate many job functions. Today's
examples apply some of the concepts that were covered on previous days.
<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2>

<DL>
	<DD><B>Q Does Day 18 cover everything I need to know about PL/SQL?</B>
	<P><B>A </B>Most definitely not. Today's introduction just scratched the surface
	of one of the greatest concepts of SQL. We have simply tried to highlight some of
	the major features to give you a basic knowledge of PL/SQL.</P>
	<P><B>Q Can I get by without using PL/SQL?</B></P>
	<P><B>A </B>Yes, you can get by, but to achieve the results that you would get with
	PL/SQL, you may have to spend much more time coding in a third-generation language.
	If you do not have Oracle, check your implementation documentation for procedural
	features like those of PL/SQL.
</DL>

<H2><FONT COLOR="#000077">Workshop</FONT></H2>
<P>The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, &quot;Answers to Quizzes and Exercises.&quot;
<H3><FONT COLOR="#000077">Quiz</FONT></H3>

<DL>
	<DD><B>1. </B>How is a database trigger used?
	<P><B>2.</B> Can related procedures be stored together?</P>
	<P><B>3. </B>True or False: Data Manipulation Language can be used in a PL/SQL statement.</P>
	<P><B>4. </B>True or False: Data Definition Language can be used in a PL/SQL statement.</P>
	<P><B>5.</B> Is text output directly a part of the PL/SQL syntax?</P>
	<P><B>6.</B> List the three major parts of a PL/SQL statement.</P>
	<P><B>7. </B>List the commands that are associated with cursor control.
</DL>

<H3><FONT COLOR="#000077">Exercises</FONT></H3>

<DL>
	<DD><B>1. </B>Declare a variable called <TT>HourlyPay</TT> in which the maximum accepted
	value is <TT>99.99</TT>/hour.
	<P><B>2. </B>Define a cursor whose content is all the data in the <TT>CUSTOMER_TABLE</TT>
	where the <TT>CITY</TT> is <TT>INDIANAPOLIS</TT>.</P>
	<P><B>3.</B> Define an exception called <TT>UnknownCode</TT>.</P>
	<P><B>4.</B> Write a statement that will set the <TT>AMT</TT> in the <TT>AMOUNT_TABLE</TT>
	to <TT>10</TT> if <TT>CODE</TT> is <TT>A</TT>, set the <TT>AMT</TT> to <TT>20</TT>
	if <TT>CODE</TT> is <TT>B</TT>, and raise an exception called <TT>UnknownCode</TT>
	if <TT>CODE</TT> is neither <TT>A</TT> nor <TT>B</TT>. The table has one row.
</DL>

<H1></H1>
<CENTER>
<P>
<HR>
<A HREF="../ch17/ch17.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch19/ch19.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A><BR>
<BR>
<BR>
<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>