The Oracle RDBMS has the ability to store procedures within
the data dictionary and execute procedures in the RDBMS.
Procedures (Program Units) are written in the PL/SQL
language (Procedural Language), which is proprietary
to Oracle. PL/SQL runs in both the database engine
as well as in many of Oracle's development tools
such as Oracle Developer.
The PL/SQL language has all of the conditional (IF ...THEN)
looping (WHILE), assignment, variable declaration and
other language constructs of a complete programming language.
SQL statements may be freely mixed in with the other programming
statements. The major change to SQL is the syntax of
the SELECT statement. All SELECT statements in PL/SQL must
use the INTO clause to redirect the rows returned by the
SELECT into variables. The syntax of the SELECT statement is:
SELECT <COLUMN1, COLUMN2, . . .>
INTO <VAR1, VAR2, . . .>
FROM <TABLE1, TABLE2, . . .>
WHERE <WHERE CLAUSE>
GROUP BY <COLUMN1, COLUMN2, . . .>
HAVING <HAVING CLAUSE>
ORDER BY <COLUMN1, COLUMN2, . . .>
Variables named in the INTO clause correspond to
the order of columns selected in the SELECT clause.
For example:
DECLARE
empsalary NUMBER;
empdepartment NUMBER;
BEGIN
SELECT employee.salary, employee.dno
INTO empsalary, empdepartment
FROM employee
WHERE employee.lname = 'SMITH';
IF (empdepartment = 1) THEN
UPDATE employee
SET salary = empsalary * 1.03
WHERE employee.lname = 'SMITH';
END IF;
END;
The above PL/SQL block declares two variables and
then executes a SELECT statement returning the salary
in PL/SQL variable empsalary and the department
number in PL/SQL variable empdepartment for
employee SMITH. If the empdepartment is equal
to 1 then an SQL UPDATE statement is executed.
It is possible that a SELECT...INTO statement can
return more than on row or record, or no records at all.
In such situations, the entire SELECT statement will fail
resulting in what is called an EXCEPTION. EXCEPTIONs in
PL/SQL must be handled (taken care of) by some code.
Most all triggers and stored procedures that use SELECT...INTO
have EXCEPTION handling code.
The EXCEPTION code the following syntax:
EXCEPTION
WHEN <EXCEPTION_NAME> THEN
BEGIN
...
END;
WHEN <EXCEPTION_NAME> THEN
BEGIN
...
END;
WHEN OTHERS THEN
BEGIN
...
END;
To continue the above example, the exception code
would appear as follows at the end of the regular
stored procedure code:
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
RAISE_APPLICATION_ERROR(-20610,
'No employee with last name SMITH found');
END;
WHEN TOO_MANY_ROWS THEN
BEGIN
RAISE_APPLICATION_ERROR(-20612,
'More than one employee with last name SMITH found');
END;
There are two main ways of storing PL/SQL code
in the Oracle database: CREATE PROCEDURE
and CREATE TRIGGER. Triggers are procedures that
are executed in response to some event. Events
include the execution of a DML statement on
a table (such as INSERT, UPDATE, DELETE, MODIFY).
Procedures are typically used to implement general
program logic that can be shared across applications,
triggers and utilities. A procedure must be explicitly
called by an application, trigger or program.
It is common practice to store general business
rule checking in procedures. This allows applications
to check data validity before a transaction is
submitted to the database. Triggers can also call
the procedures to check data at the database level.
Since the business rules are coded in a single set
of procedures, maintenance of this code is simplified.
In this section, we will introduce the syntax
for creating triggers and demonstrate the use of a
trigger to enforce a business rule.
Creating a trigger is accomplished with the
CREATE TRIGGER statement. There are numerous
options for a trigger that specify when the trigger
should fire. These options include:
The SQL statement (INSERT, UPDATE, DELETE, SELECT)
that causes the event. An event can include
more than one SQL statement per trigger.
The timing when the trigger code is executed.
Options here include
BEFORE - The trigger code is executed before the
effects of the SQL statement are put into place.
INSTEAD OF - The trigger code is executed
instead of the normal SQL statement.
AFTER - The trigger code is executed after
the normal SQL statement is processed.
Some SQL statements such as UPDATE, DELETE and
SELECT may affect more than one row. Triggers may
be specified to fire once for the SQL statement or
once for each row affected by the SQL statement.
Here is an example trigger called check_age
used to check if
an employee is over the age of 16. This trigger will
be executed in response to the events of INSERT or
DELETE on the employee table. The check_age
trigger code will be
executed BEFORE the affects of the SQL statement
are put into place. Finally, check_age will
execute FOR EACH ROW affected by the SQL statement.
Lines starting with the double minus sign --
are comments and are ignored by the trigger.
CREATE OR REPLACE TRIGGER check_age
BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW
DECLARE
-- Declare two variables.
years_old NUMBER;
error_msg CHAR(180);
BEGIN
-- The variable :new.bdate will be holding the new birth date
-- of the record to be inserted or updated. Subtract from
-- the system date and divide by 365 to get years.
years_old := ( (sysdate - :new.bdate) / 365);
-- Now check to see if the new employee is under age.
-- If so, then show an error.
IF (years_old < 16) THEN
ERROR_MSG := 'DO not HIRE ' || :new.fname || ' ' ||
:NEW.LNAME || '. They ARE only ' ||
TO_CHAR(YEARS_OLD, '99.9') || ' YEARS old.';
-- Signal THE user THERE is A problem WITH this DATA.
-- THIS also ABORTS the AFFECTS of THE SQL STATEMENT
-- FOR the CURRENT row.
RAISE_APPLICATION_ERROR ( -20601, error_msg);
END IF;
END;
After this code has been entered in SQL*Plus,
an additional line will appear as if the SQL
statement should continue. To complete entering
the trigger code, type a forward slash /
and the code will be submitted.
One of three things will happen when a new procedure
or trigger is created:
If there are no syntax errors, the code will be
compiled and the trigger will be stored in the user's
schema. In this case, SQL*Plus will respond with
a message that the trigger was created.
If there are some minor syntax errors within the
code itself (between DECLARE and the last END statement),
the trigger will still be created and stored in the
database, however a message will be returned:
Trigger created with compilation errors.
To view the compilation errors check the
USER_ERRORS view: SELECT * FROM USER_ERRORS
or use the SHOW ERRORS SQL*Plus command.
Finally, if there are syntax errors in the
CREATE OR REPLACE TRIGGER statement itself
(such as if the employee table does not exist or
one of the key words was misspelled), then
the entire statement will be rejected and
the trigger code will not be saved in the
schema.
To see if the trigger compiled correctly, look in
view USER_ERRORS as follows:
SQL> SELECT * FROM user_errors;
no rows selected
If the message no rows selected appears,
then no errors were found in the trigger.
Alternately, use the SQL*Plus command SHOW ERRORS.
To view the trigger code:
SQL> SET LONG 4096
SQL> SET PAGESIZE 90
SQL> SELECT * FROM user_triggers;
Or, in a more compact form:
SQL> SELECT trigger_name, trigger_body
FROM user_triggers
WHERE trigger_name = 'CHECK_AGE';
Once the trigger has been entered without syntax errors, it can be tested.
See what happens when we attempt to insert a new employee record where the
employee's birthdate is less than 16 years ago:
SQL> INSERT INTO employee VALUES ('Joe', 'K', 'Smith', 12332199,
2 '08-JUN-81', '123 Smith St,', 'M', 32000, 888665555, 1);
INSERT INTO employee VALUES ('Joe', 'K', 'Smith', 12332199,
*
ERROR at line 1:
ORA-20601: Do not hire Joe Smith. They are only 15.6 years old.
ORA-06512: at "TUCANO.CHECK_AGE", line 8
ORA-04088: error during execution of trigger 'TUCANO.CHECK_AGE'
The following example implements a simple inventory system.
The Products table holds a list of products with a productid
as the key and a description. The inventory location table
holds a series of locations in the warehouse including an
identifier and the aisle, tier and bin.
Finally, the intersection of these two tables is the
inventory table which takes a locationid and a
productid and gives the quantity of the product
present at the location.
SQL Statements to create and populate tables
The SQL code to create and populate the three tables
is given below:
First step: Create three tables and add constraints
CREATE TABLE inventory_locations (
locationid NUMBER(10) NOT NULL,
aisle NUMBER(10),
tier NUMBER(10),
bin NUMBER(10) );
ALTER TABLE inventory_locations
ADD CONSTRAINT il_pk PRIMARY KEY (locationid);
CREATE TABLE products (
productid VARCHAR(10) NOT NULL,
description VARCHAR(35) );
ALTER TABLE products ADD CONSTRAINT prod_pk
PRIMARY KEY (productid);
CREATE TABLE inventory (
locationid NUMBER(10) NOT NULL,
productid VARCHAR(10) NOT NULL,
quantity NUMBER(10) );
ALTER TABLE inventory ADD CONSTRAINT inventory_pk
PRIMARY KEY (locationid, productid);
Next step: Add some data to the three tables
INSERT INTO inventory_locations VALUES (101, 1, 1, 1);
INSERT INTO inventory_locations VALUES (102, 1, 1, 2);
INSERT INTO inventory_locations VALUES (103, 1, 1, 3);
INSERT INTO inventory_locations VALUES (104, 1, 2, 1);
INSERT INTO inventory_locations VALUES (105, 1, 2, 2);
INSERT INTO inventory_locations VALUES (106, 1, 2, 3);
INSERT INTO inventory_locations VALUES (107, 2, 1, 1);
INSERT INTO inventory_locations VALUES (108, 2, 1, 2);
INSERT INTO products VALUES ('P500', 'HP LaserJet 6L');
INSERT INTO products VALUES ('P510', 'HP DeskJet 855');
INSERT INTO products VALUES ('P520', 'IBM Aptiva');
INSERT INTO products VALUES ('P530', 'Compaq Presario');
INSERT INTO inventory VALUES (101, 'P500', 5);
INSERT INTO inventory VALUES (102, 'P510', 10);
INSERT INTO inventory VALUES (103, 'P500', 10);
INSERT INTO inventory VALUES (104, 'P520', 1);
INSERT INTO inventory VALUES (105, 'P530', 5);
The following query shows the current state of the inventory:
SELECT i.locationid, aisle, tier, bin, i.productid, description, quantity
FROM inventory i, inventory_locations il, products p
WHERE i.locationid = il.locationid
AND i.productid = p.productid;
We can create a view to implement this query:
CREATE VIEW vinventory AS
SELECT i.locationid, aisle, tier, bin, i.productid, description, quantity
FROM inventory i, inventory_locations il, products p
WHERE i.locationid = il.locationid AND i.productid = p.productid;
To see the current state of the inventory, simply
query the view:
SELECT * FROM vinventory;
To see the output from the stored procedures, set the following
options (Note: You must do this each time you log into SQL*Plus).
SET SERVEROUTPUT ON
SET ARRAYSIZE 2
Stored Procedures to Add and Remove items From inventory
Adding a new product to an existing location requires the
following:
Check to see if some quantity of that product is
already in the location. If so, then UPDATE
the quantity already there.
If that product is not currently in the location,
then INSERT a new inventory record with the
locationid, productid and new quantity.
The following Oracle PL/SQL implements the add_to_inventory
procedure:
CREATE OR REPLACE PROCEDURE add_to_inventory (
new_locationid IN NUMBER,
new_productid IN VARCHAR,
new_quantity IN NUMBER)
AS
current_quantity NUMBER;
BEGIN
current_quantity := 0;
-- See if some quantity exists at the current location
-- If not, then raise EXCEPTION and insert a new record
-- If so, then continue on to the UPDATE statement
SELECT quantity
INTO current_quantity
FROM inventory
WHERE inventory.locationid = new_locationid
AND inventory.productid = new_productid;
-- If we get this far, then there must already exist
-- an inventory record with this locationid and productid
-- So update the inventory by adding the new quantity.
IF (current_quantity > 0) THEN
UPDATE inventory
SET quantity = quantity + new_quantity
WHERE inventory.locationid = new_locationid
AND inventory.productid = new_productid;
END IF;
-- If the first SELECT statement above fails to return any
-- records at all, then the NO_DATA_FOUND exception will be
-- signalled. The following code reacts to this exception
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
-- Since an inventory record mathcing the locationid and
-- productid can not be found, we must INSERT a new
-- inventory record.
INSERT INTO inventory
(locationid, productid, quantity)
VALUES (new_locationid, new_productid, new_quantity);
END;
END;
Removing an existing product from inventory requires the
following:
Check to see if the requested quantity of that product is
available in the location. If so, then UPDATE
the quantity.
If the resulting quantity falls to 0, then DELETE the
inventory record.
CREATE OR REPLACE PROCEDURE remove_from_inventory (
current_locationid IN NUMBER,
current_productid IN VARCHAR,
quantity_to_remove IN NUMBER) AS
current_quantity NUMBER;
error_msg CHAR(180);
BEGIN
current_quantity := 0;
-- See if some quantity exists at the current location
-- If not, then raise EXCEPTION and exit the procedure.
-- If so, then continue on to the UPDATE statement
SELECT quantity
INTO current_quantity
FROM inventory
WHERE inventory.locationid = current_locationid
AND inventory.productid = current_productid;
-- If we get this far, then there must already exist
-- an inventory record with this locationid and productid
-- So update the inventory by removing the quantity.
IF (current_quantity - quantity_to_remove > 0) THEN
UPDATE inventory
SET quantity = quantity - quantity_to_remove
WHERE inventory.locationid = current_locationid
AND inventory.productid = current_productid;
END IF;
-- If the quantity to remove is the same as the current
-- quantity in the location, then simply delete the
-- entire record.
IF (current_quantity - quantity_to_remove = 0) THEN
DELETE FROM inventory
WHERE inventory.locationid = current_locationid
AND inventory.productid = current_productid;
END IF;
IF (current_quantity - quantity_to_remove < 0) THEN
ERROR_MSG := 'ERROR: Insufficient QUANTITY in THAT location';
RAISE_APPLICATION_ERROR (-20602, ERROR_MSG);
END IF;
-- IF the FIRST SELECT STATEMENT above FAILS to RETURN any
-- records AT all, THEN the NO_DATA_FOUND exception WILL be
-- signalled. THE following CODE reacts TO this EXCEPTION.
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
ERROR_MSG := 'ERROR: Product ' || CURRENT_PRODUCTID ||
' not FOUND at THIS location';
RAISE_APPLICATION_ERROR (-20603, ERROR_MSG);
END;
END;
Note: When creating stored procedures in SQL*Plus, you
must type a slash character ( / ) on the line after the
last END; of the procedure. This lets SQL*Plus know
to submit the CREATE PROCEDURE statement to the database.
To see any errors from the compilation of the procedure
use the show errors command.
To run the stored procedures, use the EXECUTE command followed
by the name of the procedure and any parameters.
For example, to add 10 units of product P500 to location 106,
execute the following:
EXECUTE add_to_inventory(106, 'P500', 10)
To remove 10 units of procedure P500 from location 106,
execute the following:
EXECUTE remove_from_inventory(106, 'P500', 10)
In this section, we have shown some basic forms of triggers
and stored procedures. For additional information and examples
on the PL/SQL language, please refer to the Oracle PL/SQL
User's Guide and Reference.
