As we recall, a correlated subquery involves executing the subquery first, and then applying the result to the entire outer query. In this case, the inner query will execute 5,000 times, and the outer query is executed once for each row returned from the inner query. This Cartesian product problem has always been a problem for correlated subqueries.

Now consider the identical query as written in PL/SQL (Listing 4.12).

Listing 4.12 The PL/SQL query.

DECLARE
  CURSOR c1 is
  SELECT account_number,
    check_amount
  FROM TRANSACTION;

    keep_account_number      number;
    keep_check_amount        number;

BEGIN

    OPEN C1;
    LOOP
            fetch c1 into keep_account_number, KEEP check_amount;
    EXIT WHEN C1%NOTFOUND;

    UPDATE GENERAL_LEDGER
    SET account_balance = account_balance - keep_check_amount
    WHERE account_number = keep_account_number;
    END LOOP;
END;

Here we see that each check amount is retrieved in a separate transaction, and fetched into cursor C1. For each check_amount, the balance is applied to the GENERAL_LEDGER row, one at a time.

Using Oracle Stored Procedures And Triggers

Many excellent benefits can be derived from using stored procedures and triggers for all database access code. These enhancements include:

•  Encapsulation—The code resides in the database instead of in external programs. With the proper use of Oracle packages, stored procedures can be logically grouped together into a cohesive framework of SQL statements.

•  Performance—Stored procedures and triggers are cached into the shared pool, making repeated calls to the procedure very fast.

•  Flexibility—By keeping all database access inside Oracle packages, the applications contain no SQL, becoming little more than a set of calls to the stored procedures. As such, the application is insulated from the database and becomes very easy to migrate to another platform or database product.

Certain rules apply in deciding when to use a trigger and when to use a stored procedure. The choice revolves around the nature of the desired SQL, and whether it is specific to a DML event or is global in nature. In general, the validation of input data is ideally suited to an INSERT trigger, especially when it involves accessing another Oracle table. If you are writing your own referential integrity, DELETE triggers are appropriate. Triggers are generally associated with SQL that is closely tied to a single DML event, such as the insertion, deletion, or updating of a row. In practice, SELECT triggers are used only in situations where hand-rolled auditing is used to keep a log of those who are viewing secure rows.

Stored procedures are generally used when an SQL query creates an aggregate object, which accesses rows from many tables to create a single result set. The creation of an invoice, an order form, or a student’s schedule are examples of these types of queries.

One of the problems with utilizing Oracle stored procedures and triggers is keeping track of the SQL once it has been entered into the database. Unlike the object database products, Oracle does not yet provide a mechanism for directly associating a stored procedure with the tables that it touches.

Oracle version 8 promises to make this less of a problem, but client/server programmers must be able to identify and reuse queries that have already been written and tested. To achieve reusability, you can use naming conventions to ensure that all SQL is logically associated with the tables against which it operates. For example, the action of the SQL inserting a row into the customer table could be given a meaningful name, say customer_insert().This way, the data dictionary can be interrogated to identify all existing SQL that touched a table. The use of naming conventions is tricky when a single SQL statement joins many tables, but prudent use of naming conventions can also help ensure that the SQL can be easily located.

Oracle triggers have the ability to call stored procedures, and a trigger may include many SQL statements, thus providing the ability to “nest” SQL statements. Oracle triggers are stored as procedures that can be parameterized and used to simulate object-oriented behavior. For example, assume that we want to perform a behavior called CHECK_TOTAL_INVENTORY whenever an item is added to an order. The trigger definition would be:

CREATE TRIGGER CHECK_TOTAL_INVENTORY
           AFTER  SELECT OF ITEM
FOR EACH ROW

   SELECT COUNT(*) INTO :count
   FROM QUANTITY WHERE ITEM_# = :myitem:

IF :count < item.total THEN
     ......
END IF;

Triggers could also be combined to handle combined events, such as the reordering of an ITEM when the quantity on hand falls below a predefined level:

CREATE TRIGGER REORDER BEFORE UPDATE ON ITEM
    FOR EACH ROW WHEN (new.reorderable = 'Y')
     BEGIN
        IF new.qty_on_hand + old.qty_on_order < new.minimum_qty
        THEN
            INSERT INTO REORDER VALUES (item_nbr, reorder_qty);
            new.qty_on_order := old.qty_on_order + reorder_qty;
        END IF;
    END

Simulating Object-Orientation

The latest movement in the trendy database marketplace is to offer products that use object technology and also offer the popular SQL access language. On one side of this market we see the “pure” ODBMSs developing SQL interfaces to their object architectures, while the relational vendors are creating object layers for their table structures. While neither of these approaches is ideal from the perspective of the MIS manager, great progress has been made in both directions. The time has arrived when programmers who have mastered SQL also need to understand its object-oriented dialects. These products include SQL++ by Objectivity Inc., Object SQL by Ontos, and SQL/X¹ by UniSQL. Many proponents of the relational model state that the ad hoc query capability of SQL is inconsistent with the object oriented principle of encapsulation, but the sheer popularity of these tools has led to many attempts to reconcile SQL with object-oriented databases.

The object/relational hybrids promise to allow users to keep their existing relational technology while gaining object features. Unlike traditional relational databases, these hybrids allow cells within a table to contain multiple values or even another entire table. This “nesting” of tables within tables allows far more flexibility than with traditional database systems, even though it appears to toss away even the most fundamental principles of normalization. Codd’s definition of First Normal Form (1NF) does not allow for repeating data items within a field, much less a nested table.