Web based School

- Chapter 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.

Chapter 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);
  toChapter 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 toChapter 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 guide 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 guide 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 Chapter 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 Chapter 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.

ToChapter 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.

Chapter 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 Chapter 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.