Teach Yourself SQL in 21 Days, Second Edition
![Previous chapter](../buttonart/previous.gif) ![Next chapter](../buttonart/next.gif)
- Day 18 -
PL/SQL: An Introduction
Objectives
PL/SQL is the Oracle technology that enables SQL to act like a procedural language.
By the end of today, you should
- Have a basic understanding of PL/SQL
- Understand the features that distinguish PL/SQL from standard SQL
- Have an understanding of the basic elements of a PL/SQL program
- Be able to write a simple PL/SQL program
- Understand how errors are handled in PL/SQL programs
- Be aware of how PL/SQL is used in the real world
Introduction
One way to introduce PL/SQL is to begin by describing standard Structured Query
Language, or SQL. SQL is the language that enables relational database users to communicate
with the database in a straightforward manner. You can use SQL commands to query
the database and modify tables within the database. When you write an SQL statement,
you are telling the database what you want to do, not how to do it. The query optimizer
decides the most efficient way to execute your statement. If you send a series of
SQL statements to the server in standard SQL, the server executes them one at a time
in chronological order.
PL/SQL is Oracle's procedural language; it comprises the standard language of
SQL and a wide array of commands that enable you to control the execution of SQL
statements according to different conditions. PL/SQL can also handle runtime errors.
Options such as loops and IF...THEN statements give PL/SQL the power of
third-generation programming languages. PL/SQL allows you to write interactive, user-friendly
programs that can pass values into variables. You can also use several predefined
packages, one of which can display messages to the user.
Day 18 covers these key features of PL/SQL:
- Programmers can declare variables to be used during statement processing.
- Programmers can use error-handling routines to prevent programs from aborting
unexpectedly.
- Programmers can write interactive programs that accept input from the user.
- Programmers can divide functions into logical blocks of code. Modular programming
techniques support flexibility during the application development.
- SQL statements can be processed simultaneously for better overall performance.
Data Types in PL/SQL
Most data types are obviously similar, but each implementation has unique storage
and internal-processing requirements. When writing PL/SQL blocks, you will be declaring
variables, which must be valid data types. The following subsections briefly describe
the data types available in PL/SQL.
In PL/SQL Oracle provides subtypes of data types. For example, the data type NUMBER
has a subtype called INTEGER. You can use subtypes in your PL/SQL program
to make the data types compatible with data types in other programs, such as a COBOL
program, particularly if you are embedding PL/SQL code in another program. Subtypes
are simply alternative names for Oracle data types and therefore must follow the
rules of their associated data type.
NOTE: As in most implementations of SQL,
case sensitivity is not a factor in the syntax of a statement. PL/SQL allows either
uppercase or lowercase with its commands.
Character String Data Types
Character string data types in PL/SQL, as you might expect, are data types generally
defined as having alpha-numeric values. Examples of character strings are names,
codes, descriptions, and serial numbers that include characters.
CHAR stores fixed-length character strings. The maximum length of CHAR
is 32,767 bytes, although it is hard to imagine a set of fixed-length values in a
table being so long.
SYNTAX:
CHAR ( max_length )
Subtype: CHARACTER
VARCHAR2 stores variable-length character strings. You would normally
user VARCHAR2 instead of CHAR to store variable-length data, such
as an individual's name. The maximum length of VARCHAR2 is also 32,767 bytes.
SYNTAX:
VARCHAR2 ( max_length )
Subtypes: VARCHAR, STRING
LONG also stores variable-length character strings, having a maximum
length of 32,760 bytes. LONG is typically used to store lengthy text such
as remarks, although VARCHAR2 may be used as well.
Numeric Data Types
NUMBER stores any type of number in an Oracle database.
SYNTAX:
NUMBER ( max_length )
You may specify a NUMBER's data precision with the following syntax:
NUMBER (precision, scale)
Subtypes: DEC, DECIMAL, DOUBLE PRECISION, INTEGER,
INT, NUMERIC, REAL, SMALLINT, FLOAT
PLS_INTEGER defines columns that may contained integers with a sign,
such as negative numbers.
Binary Data Types
Binary data types store data that is in a binary format, such as graphics or photographs.
These data types include RAW and LONGRAW.
The DATE Data Type
DATE is the valid Oracle data type in which to store dates. When you
define a column as a DATE, you do not specify a length, as the length of
a DATE field is implied. The format of an Oracle date is, for example, 01-OCT-97.
BOOLEAN
BOOLEAN stores the following values: TRUE, FALSE, and
NULL. Like DATE, BOOLEAN requires no parameters when defining
it as a column's or variable's data type.
ROWID
ROWID is a pseudocolumn that exists in every table in an Oracle database.
The ROWID is stored in binary format and identifies each row in a table.
Indexes use ROWIDs as pointers to data.
The Structure of a PL/SQL Block
PL/SQL is a block-structured language, meaning that PL/SQL programs are divided
and written in logical blocks of code. Within a PL/SQL block of code, processes such
as data manipulation or queries can occur. The following parts of a PL/SQL block
are discussed in this section:
- The DECLARE section contains the definitions of variables and other
objects such as constants and cursors. This section is an optional part of a PL/SQL
block.
- The PROCEDURE section contains conditional commands and SQL statements
and is where the block is controlled. This section is the only mandatory part of
a PL/SQL block.
- The EXCEPTION section tells the PL/SQL block how to handle specified
errors and user-defined exceptions. This section is an optional part of a PL/SQL
block.
NOTE: A block is a logical unit of PL/SQL
code, containing at the least a PROCEDURE section and optionally the DECLARE
and EXCEPTION sections.
Here is the basic structure of a PL/SQL block:
SYNTAX:
BEGIN -- optional, denotes beginning of block
DECLARE -- optional, variable definitions
BEGIN -- mandatory, denotes beginning of procedure section
EXCEPTION -- optional, denotes beginning of exception section
END -- mandatory, denotes ending of procedure section
END -- optional, denotes ending of block
Notice that the only mandatory parts of a PL/SQL block are the second BEGIN
and the first END, which make up the PROCEDURE section. Of course,
you will have statements in between. If you use the first BEGIN, then you
must use the second END, and vice versa.
Comments
What would a program be without comments? Programming languages provide commands
that allow you to place comments within your code, and PL/SQL is no exception. The
comments after each line in the preceding sample block structure describe each command.
The accepted comments in PL/SQL are as follows:
SYNTAX:
-- This is a one-line comment.
/* This is a
multiple-line comment.*/
NOTE: PL/SQL directly supports Data Manipulation
Language (DML) commands and database queries. However, it does not support Data Dictionary
Language (DDL) commands. You can generally use PL/SQL to manipulate the data within
database structure, but not to manipulate those structures.
The DECLARE Section
The DECLARE section of a block of PL/SQL code consists of variables,
constants, cursor definitions, and special data types. As a PL/SQL programmer, you
can declare all types of variables within your blocks of code. However, you must
assign a data type, which must conform to Oracle's rules of that particular data
type, to every variable that you define. Variables must also conform to Oracle's
object naming standards.
Variable Assignment
Variables are values that are subject to change within a PL/SQL block. PL/SQL
variables must be assigned a valid data type upon declaration and can be initialized
if necessary. The following example defines a set of variables in the DECLARE
portion of a block:
DECLARE
owner char(10);
tablename char(30);
bytes number(10);
today date;
ANALYSIS:
The DECLARE portion of a block cannot be executed by itself. The DECLARE
section starts with the DECLARE statement. Then individual variables are
defined on separate lines. Notice that each variable declaration ends with a semicolon.
Variables may also be initialized in the DECLARE section. For example:
DECLARE
customer char(30);
fiscal_year number(2) := '97';
You can use the symbol := to initialize, or assign an initial value,
to variables in the DECLARE section. You must initialize a variable that
is defined as NOT NULL.
DECLARE
customer char(30);
fiscal_year number(2) NOT NULL := '97';
ANALYSIS:
The NOT NULL clause in the definition of fiscal_year resembles
a column definition in a CREATE TABLE statement.
Constant Assignment
Constants are defined the same way that variables are, but constant values are
static; they do not change. In the previous example, fiscal_year is probably
a constant.
NOTE: You must end each variable declaration
with a semicolon.
Cursor Definitions
A cursor is another type of variable in PL/SQL. Usually when you think of a variable,
a single value comes to mind. A cursor is a variable that points to a row of data
from the results of a query. In a multiple-row result set, you need a way to scroll
through each record to analyze the data. A cursor is just that. When the PL/SQL block
looks at the results of a query within the block, it uses a cursor to point to each
returned row. Here is an example of a cursor being defined in a PL/SQL block:
INPUT:
DECLARE
cursor employee_cursor is
select * from employees;
A cursor is similar to a view. With the use of a loop in the PROCEDURE
section, you can scroll a cursor. This technique is covered shortly.
The %TYPE Attribute
%TYPE is a variable attribute that returns the value of a given column
of a table. Instead of hard-coding the data type in your PL/SQL block, you can use
%TYPE to maintain data type consistency within your blocks of code.
INPUT:
DECLARE
cursor employee_cursor is
select emp_id, emp_name from employees;
id_num employees.emp_id%TYPE;
name employees.emp_name%TYPE;
ANALYSIS:
The variable id_num is declared to have the same data type as emp_id
in the EMPLOYEES table. %TYPE declares the variable name
to have the same data type as the column emp_name in the EMPLOYEES
table.
The %ROWTYPE Attribute
Variables are not limited to single values. If you declare a variable that is
associated with a defined cursor, you can use the %ROWTYPE attribute to
declare the data type of that variable to be the same as each column in one entire
row of data from the cursor. In Oracle's lexicon the %ROWTYPE attribute
creates a record variable.
INPUT:
DECLARE
cursor employee_cursor is
select emp_id, emp_name from employees;
employee_record employee_cursor%ROWTYPE;
ANALYSIS:
This example declares a variable called employee_record. The %ROWTYPE
attribute defines this variable as having the same data type as an entire row of
data in the employee_cursor. Variables declared using the %ROWTYPE
attribute are also called aggregate variables.
The %ROWCOUNT Attribute
The PL/SQL %ROWCOUNT attribute maintains a count of rows that the SQL
statements in the particular block have accessed in a cursor.
INPUT:
DECLARE
cursor employee_cursor is
select emp_id, emp_name from employees;
records_processed := employee_cursor%ROWCOUNT;
ANALYSIS:
In this example the variable records_processed represents the current
number of rows that the PL/SQL block has accessed in the employee_cursor.
WARNING: 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.
The PROCEDURE Section
The PROCEDURE 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 PROCEDURE section is the main part of a block, containing conditional
statements and SQL commands.
BEGIN...END
In a block, the BEGIN statement denotes the beginning of a procedure.
Similarly, the END statement marks the end of a procedure. The following
example shows the basic structure of the PROCEDURE section:
SYNTAX:
BEGIN
open a cursor;
condition1;
statement1;
condition2;
statement2;
condition3;
statement3;
.
.
.
close the cursor;
END
Cursor Control Commands
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: DECLARE, OPEN, FETCH, and CLOSE.
DECLARE
Earlier today you learned how to define a cursor in the DECLARE section
of a block. The DECLARE statement belongs in the list of cursor control
commands.
OPEN
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 OPEN command. For example:
SYNTAX:
BEGIN
open employee_cursor;
statement1;
statement2;
.
.
.
END
FETCH
FETCH populates a variable with values from a cursor. Here are two examples
using FETCH: One populates an aggregate variable, and the other populates
individual variables.
INPUT:
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
ANALYSIS:
The preceding example fetches the current row of the cursor into the aggregate
variable employee_record. It uses a loop to scroll the cursor. Of course,
the block is not actually accomplishing anything.
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
ANALYSIS:
This example fetches the current row of the cursor into the variables id_num
and name, which was defined in the DECLARE section.
CLOSE
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 CLOSE.
SYNTAX:
BEGIN
open employee_cursor;
statement1;
statement2;
.
.
.
close employee_cursor;
END
ANALYSIS:
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.
Conditional Statements
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.
IF...THEN
The IF...THEN statement is probably the most familiar conditional statement
to most programmers. The IF...THEN statement dictates the performance of
certain actions if certain conditions are met. The structure of an IF...THEN
statement is as follows:
SYNTAX:
IF condition1 THEN
statement1;
END IF;
If you are checking for two conditions, you can write your statement as follows:
SYNTAX:
IF condition1 THEN
statement1;
ELSE
statement2;
END IF;
If you are checking for more than two conditions, you can write your statement
as follows:
SYNTAX:
IF condition1 THEN
statement1;
ELSIF condition2 THEN
statement2;
ELSE
statement3;
END IF;
ANALYSIS:
The final example states: If condition1 is met, then perform statement1;
if condition2 is met, then perform statement2; otherwise, perform
statement3. IF...THEN statements may also be nested within other
statements and/or loops.
LOOPS
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.
LOOP 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:
INPUT:
BEGIN
open employee_cursor;
LOOP
FETCH employee_cursor into employee_record;
EXIT WHEN employee_cursor%NOTFOUND;
statement1;
.
.
.
END LOOP;
close employee_cursor;
END;
%NOTFOUND 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.
The WHILE-LOOP executes commands while a specified condition is TRUE.
When the condition is no longer true, the loop returns control to the next statement.
INPUT:
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 < 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;
ANALYSIS:
The preceding example uses the WHILE-LOOP to scroll the cursor and to
execute the commands within the loop as long as the condition payment < total_due
is met.
You can use the FOR-LOOP in the previous block to implicitly fetch the
current row of the cursor into the defined variables.
INPUT:
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 < pay_rec.total_due THEN
insert into underpay_table
values (pay_rec.cust_id, 'STILL OWES');
END IF;
END LOOP;
close payment_cursor;
END;
ANALYSIS:
This example uses the FOR-LOOP to scroll the cursor. The FOR-LOOP
is performing an implicit FETCH, which is omitted this time. Also, notice
that the %NOTFOUND attribute has been omitted. This attribute is implied
with the FOR-LOOP; therefore, this and the previous example yield the same
basic results.
The EXCEPTION Section
The EXCEPTION 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 EXCEPTION 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.
Raising Exceptions
Exceptions are raised in a block by using the command RAISE. Exceptions
can be raised explicitly by the programmer, whereas internal database errors are
automatically, or implicitly, raised by the database server.
SYNTAX:
BEGIN
DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
END;
ANALYSIS:
This block shows the fundamentals of explicitly raising an exception. First exception_name
is declared using the EXCEPTION statement. In the PROCEDURE section,
the exception is raised using RAISE if a given condition is met. The RAISE
then references the EXCEPTION section of the block, where the appropriate
action is taken.
Handling Exceptions
The preceding example handled an exception in the EXCEPTION 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.
SYNTAX:
EXCEPTION
WHEN exception1 THEN
statement1;
WHEN exception2 THEN
statement2;
WHEN OTHERS THEN
statement3;
ANALYSIS:
This example shows how the EXCEPTION section might look if you have more
than one exception. This example expects two exceptions (exception1 and
exception2) when running this block. WHEN OTHERS tells statement3
to execute if any other exceptions occur while the block is being processed. WHEN
OTHERS gives you control over any errors that may occur within the block.
Executing a PL/SQL Block
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
(/)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 START command, abbreviated as STA or @.
Your PL/SQL script file might look like this:
SYNTAX:
/* This file is called proc1.sql */
BEGIN
DECLARE
...
BEGIN
...
statements;
...
EXCEPTION
...
END;
END;
/
You execute your PL/SQL script file as follows:
SQL> start proc1 or
SQL> sta proc1 or
SQL> @proc1
NOTE: PL/SQL script files can be executed
using the START command or the character @. PL/SQL script files
can also be called within other PL/SQL files, shell scripts, or other programs.
Displaying Output to the User
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.
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 DBMS_OUTPUT.
EXCEPTION
WHEN zero_divide THEN
DBMS_OUTPUT.put_line('ERROR: DIVISOR IS ZERO. SEE YOUR DBA.');
ANALYSIS:
ZERO_DIVIDE 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).
If this exception is encountered during block processing, the user will see:
INPUT:
SQL> @block1
ERROR: DIVISOR IS ZERO. SEE YOUR DBA.
PL/SQL procedure successfully completed.
Doesn't that message look friendly than:
INPUT/OUTPUT:
SQL> @block1
begin
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 20
Transactional Control in PL/SQL
On Day 11, "Controlling Transactions," we discussed the transactional
control commands COMMIT, ROLLBACK, and SAVEPOINT. These
commands allow the programmer to control when transactions are actually written to
the database, how often, and when they should be undone.
SYNTAX:
BEGIN
DECLARE
...
BEGIN
statements...
IF condition THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
...
EXCEPTION
...
END;
END;
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.
Putting Everything Together
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 PROCEDURE 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 EXCEPTION section of the block. Now you are ready to work with
some practical examples and create blocks from BEGIN to END. By
the end of this section, you should fully understand how the parts of a PL/SQL block
interact with each other.
Sample Tables and Data
We will be using two tables to create PL/SQL blocks. PAYMENT_TABLE identifies
a customer, how much he or she has paid, and the total amount due. PAY_STATUS_TABLE
does not yet contain any data. Data will be inserted into PAY_STATUS_TABLE
according to certain conditions in the PAYMENT_TABLE.
INPUT:
SQL> select *
2 from payment_table;
OUTPUT:
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.
INPUT:
SQL> describe pay_status_table
OUTPUT:
Name Null? Type
------------------------------ --------- ----
CUST_ID NOT NULL CHAR(3)
STATUS NOT NULL VARCHAR2(15)
AMT_OWED NUMBER(8,2)
AMT_CREDIT NUMBER(8,2)
ANALYSIS:
DESCRIBE is an Oracle SQL command that displays the structure of a table
without having to query the data dictionary. DESCRIBE and other Oracle SQL*Plus
commands are covered on Day 20, "SQL*Plus."
A Simple PL/SQL Block
This is how the PL/SQL script (block1.sql) file looks:
INPUT:
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 > fTotalDue ) then
fOverPaid := fPayment - fTotalDue;
insert into pay_status_table (cust_id, status, amt_credit)
values (cCustId, 'Over Paid', fOverPaid);
elsif ( fPayment < 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;
/
ANALYSIS:
The DECLARE section defines six local variables, as well as a cursor
called payment_cursor. The PROCEDURE section starts with the second
BEGIN statement in which the first step is to open the cursor and start
a loop. The FETCH command passes the current values in the cursor into the
variables that were defined in the DECLARE 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
PAY_STATUS_TABLE. The loop terminates, and the cursor closes. The EXCEPTION
section handles errors that may occur during processing.
Now start the PL/SQL script file and see what happens.
INPUT:
SQL> @block1
OUTPUT:
Input truncated to 1 characters
ERROR: amount is Zero. See your supervisor.
PL/SQL procedure successfully completed.
Now that you know that an incorrect amount appears in the total due column, you
can fix the amount and run the script again.
INPUT/OUTPUT:
SQL> update payment_table
2 set total_due = 26.75
3 where cust_id = 'HHH';
1 row updated.
SQL> commit;
Commit complete.
SQL> truncate table pay_status_table;
Table truncated.
NOTE: This example truncates the PAY_STATUS_TABLE
to clear the table's contents; the next run of the statement will repopulate the
table. You may want to add the TRUNCATE TABLE statement to your PL/SQL block.
INPUT/OUTPUT:
SQL> @block1
Input truncated to 1 characters
PL/SQL procedure successfully completed.
Now you can select from the PAY_STATUS_TABLE and see the payment status
of each customer.
INPUT/OUTPUT:
SQL> select *
2 from pay_status_table
3 order by status;
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.
ANALYSIS:
A row was inserted into PAY_STATUS_TABLE for every row of data that is
contained in the PAYMENT_TABLE. If the customer paid more than the amount
due, then the difference was input into the amt_credit column. If the customer
paid less than the amount owed, then an entry was made in the amt_owed column.
If the customer paid in full, then no dollar amount was inserted in either of the
two columns.
Another Program
This example uses a table called PAY_TABLE:
INPUT:
SQL> desc pay_table
OUTPUT:
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)
First take a look at the data:
INPUT:
SQL> select *
2 from pay_table
3 order by pay_type, pay_rate desc;
OUTPUT:
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.
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.
Today is:
INPUT/OUTPUT:
SQL> select sysdate
2 from dual;
SYSDATE
--------
20-MAY-97
Before examining the next PL/SQL block, we will perform a manual select from the
PAY_TABLE that flags individuals who should receive a raise.
INPUT:
SQL> select name, pay_type, pay_rate, eff_date,
2 'YES' due
3 from pay_table
4 where eff_date < sysdate - 180
5 UNION ALL
6 select name, pay_type, pay_rate, eff_date,
7 'No' due
8 from pay_table
9 where eff_date >= sysdate - 180
10 order by 2, 3 desc;
OUTPUT:
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.
The DUE column identifies individuals who should be eligible for a raise.
Here's the PL/SQL script:
INPUT:
set serveroutput on
BEGIN
DECLARE
UnknownPayType exception;
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;
/
Are you sure that you want to give four employees a pay raise? (The final SELECT
statement has four Yes values in the DUE 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 block2.sql:
INPUT/OUTPUT:
SQL> @block2
Input truncated to 1 characters
PL/SQL procedure successfully completed.
You can do a quick select to verify that the changes have been made to the pay_rate
of the appropriate individuals:
INPUT:
SQL> select *
2 from pay_table
3 order by pay_type, pay_rate desc;
OUTPUT:
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.
ANALYSIS:
Four employees received a pay increase. If you compare this output to the output
of the original SELECT 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.
Wait--you didn't get a chance to see how the defined exception works. You can
test the EXCEPTION section by inserting an invalid PAY_TYPE into
PAY_TABLE.
INPUT:
SQL> insert into pay_table values
2 ('JEFF JENNINGS','WEEKLY',71.50,'01-JAN-97',NULL);
OUTPUT:
1 row created.
The moment of truth:
INPUT/OUTPUT:
SQL> @block2
Input truncated to 1 characters
=======================
ERROR: Aborting program.
Unknown Pay Type for: JEFF JENNINGS
PL/SQL procedure successfully completed.
ANALYSIS:
An error message told you that JEFF JENNINGS had a Pay Type
with a value other than SALARY or HOURLY. That is, the exception
was handled with an error message.
Stored Procedures, Packages, and Triggers
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 ORDERS that will insert data
into a HISTORY table each time the ORDERS table receives data.
The basic syntax of these objects follows.
Sample Procedure
SYNTAX:
PROCEDURE procedure_name IS
variable1 datatype;
...
BEGIN
statement1;
...
EXCEPTION
when ...
END procedure_name;
Sample Package
SYNTAX:
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;
Sample Trigger
SYNTAX:
CREATE TRIGGER trigger_name
AFTER UPDATE OF column ON table_name
FOR EACH ROW
BEGIN
statement1;
...
END;
The following example uses a trigger to insert a row of data into a transaction
table when updating PAY_TABLE. The TRANSACTION table looks like
this:
INPUT:
SQL> describe trans_table
OUTPUT:
Name Null? Type
------------------------------ -------- ----
ACTION VARCHAR2(10)
NAME VARCHAR2(20)
PREV_PAY NUMBER(8,2)
CURR_PAY NUMBER(8,2)
EFF_DATE DATE
Here's a sample row of data:
INPUT/OUTPUT:
SQL> select *
2 from pay_table
3 where name = 'JEFF JENNINGS';
NAME PAY_TYPE PAY_RATE EFF_DATE PREV_PAY
-------------------- -------- --------- -------- ----------
JEFF JENNINGS WEEKLY 71.50 01-JAN-97
Now, create a trigger:
SQL> CREATE TRIGGER pay_trigger
2 AFTER update on PAY_TABLE
3 FOR EACH ROW
4 BEGIN
5 insert into trans_table values
6 ('PAY CHANGE', :new.name, :old.pay_rate,
7 :new.pay_rate, :new.eff_date);
8 END;
9 /
Trigger created.
The last step is to perform an update on PAY_TABLE, which should cause
the trigger to be executed.
INPUT/OUTPUT:
SQL> update pay_table
2 set pay_rate = 15.50,
3 eff_date = sysdate
4 where name = 'JEFF JENNINGS';
1 row updated.
SQL> select *
2 from pay_table
3 where name = 'JEFF JENNINGS';
NAME PAY_TYPE PAY_RATE EFF_DATE PREV_PAY
-------------------- -------- --------- -------- ----------
JEFF JENNINGS WEEKLY 15.50 20-MAY-97
SQL> select *
2 from trans_table;
ACTION NAME PREV_PAY CURR_PAY EFF_DATE
---------- -------------------- ---------- ---------- ---------
PAY CHANGE JEFF JENNINGS 71.5 15.5 20-MAY-97
ANALYSIS:
PREV_PAY is null in PAY_TABLE but PREV_PAY appears
in TRANS_TABLE. This approach isn't as confusing as it sounds. PAY_TABLE
does not need an entry for PREV_PAY because the PAY_RATE of 71.50
per hour was obviously an erroneous amount. Rather, we inserted the value for PREV_PAY
in TRANS_TABLE because the update was a transaction, and the purpose of
TRANS_PAY is to keep a record of all transactions against PAY_TABLE.
NOTE: 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.
Summary
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. IF...THEN
statements and loops enable you to search for specific conditions; you can also use
loops to scroll through the contents of a defined cursor.
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.
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.
Q&A
- Q Does Day 18 cover everything I need to know about PL/SQL?
A 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.
Q Can I get by without using PL/SQL?
A 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.
Workshop
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, "Answers to Quizzes and Exercises."
Quiz
- 1. How is a database trigger used?
2. Can related procedures be stored together?
3. True or False: Data Manipulation Language can be used in a PL/SQL statement.
4. True or False: Data Definition Language can be used in a PL/SQL statement.
5. Is text output directly a part of the PL/SQL syntax?
6. List the three major parts of a PL/SQL statement.
7. List the commands that are associated with cursor control.
Exercises
- 1. Declare a variable called HourlyPay in which the maximum accepted
value is 99.99/hour.
2. Define a cursor whose content is all the data in the CUSTOMER_TABLE
where the CITY is INDIANAPOLIS.
3. Define an exception called UnknownCode.
4. Write a statement that will set the AMT in the AMOUNT_TABLE
to 10 if CODE is A, set the AMT to 20
if CODE is B, and raise an exception called UnknownCode
if CODE is neither A nor B. The table has one row.
![Previous chapter](../buttonart/previous.gif) ![Next chapter](../buttonart/next.gif) ![Contents](../buttonart/contents.gif)
© Copyright, Macmillan Computer Publishing. All
rights reserved.
|