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