Stored Procedures and Triggers

Stored Procedures and Triggers

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.

Trigger Syntax

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.

Trigger Example

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'

Stored Procedure Example

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.

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity. Information used on this site is at your own risk.
The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.