Tutorial 6

6.1 A Case Study

This tutorial is an abbreviated version of the purchase order database that is explained in more detail in the on-line Oracle documentation.

Exercise:

Each box in the diagram corresponds to an object type. But how many object tables are needed? The relationship between Customer and Phone/Address is "has" whereas the relationship between LineItem and StockItem is "refers to". For which one ("has" or "refers to") is it necessary to create an object table for the dependent data?


6.2 The Customer table

The following code creates the phone and address types and the customer type and table.

      CREATE TYPE PhoneList_vartyp AS VARRAY(10) OF VARCHAR2(20);
      CREATE TYPE Address_objtyp AS OBJECT (
            Street VARCHAR2(200),
            City VARCHAR2(200),
            State CHAR(2),
            Zip VARCHAR2(20)
      );

      CREATE TYPE Customer_objtyp AS OBJECT (
            CustNo NUMBER,
            CustName VARCHAR2(200),
            Address_obj Address_objtyp,
            PhoneList_var PhoneList_vartyp
      ) NOT FINAL;

      CREATE TABLE Customer_objtab OF Customer_objtyp (CustNo PRIMARY KEY)
            OBJECT IDENTIFIER IS PRIMARY KEY ;

      INSERT INTO Customer_objtab
            VALUES (
            1, 'Jean Nance',
            Address_objtyp('2 Avocet Drive', 'Redwood Shores', 'CA', '95054'),
            PhoneList_vartyp('415-555-1212')
            ) ;
      INSERT INTO Customer_objtab
            VALUES (
            2, 'John Nike',
            Address_objtyp('323 College Drive', 'Edison', 'NJ', '08820'),
            PhoneList_vartyp('609-555-1212','201-555-1212')
            ) ;

Exercises:


6.3 The Stock and LineItem Types and Tables

      CREATE TYPE StockItem_objtyp AS OBJECT (
            StockNo NUMBER,
            Price NUMBER,
            TaxRate NUMBER
      );

      CREATE TABLE Stock_objtab OF StockItem_objtyp (StockNo PRIMARY KEY)
            OBJECT IDENTIFIER IS PRIMARY KEY;

      INSERT INTO Stock_objtab VALUES(1004, 6750.00, 2) ;
      INSERT INTO Stock_objtab VALUES(1011, 4500.23, 2) ;
      INSERT INTO Stock_objtab VALUES(1534, 2234.00, 2) ;
      INSERT INTO Stock_objtab VALUES(1535, 3456.23, 2) ;

      CREATE TYPE LineItem_objtyp AS OBJECT (
            LineItemNo NUMBER,
            Stock_ref REF StockItem_objtyp,
            Quantity NUMBER,
            Discount NUMBER
      );

      CREATE TYPE LineItemList_ntabtyp AS TABLE OF LineItem_objtyp;

Exercises:


6.4 The purchaseOrder type and table

      CREATE TYPE PurchaseOrder_objtyp AUTHID CURRENT_USER AS OBJECT (
            PONo NUMBER,
            Cust_ref REF Customer_objtyp,
            OrderDate DATE,
            ShipDate DATE,
            LineItemList_ntab LineItemList_ntabtyp,
            ShipToAddr_obj Address_objtyp,
      MEMBER FUNCTION
            sumLineItems RETURN NUMBER
      );

      CREATE TABLE PurchaseOrder_objtab OF PurchaseOrder_objtyp (
            PRIMARY KEY (PONo),
            FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab)
            OBJECT IDENTIFIER IS PRIMARY KEY
      NESTED TABLE LineItemList_ntab STORE AS PoLine_ntab (
            (PRIMARY KEY(NESTED_TABLE_ID, LineItemNo))
            ORGANIZATION INDEX COMPRESS)
      RETURN AS LOCATOR;

      INSERT INTO PurchaseOrder_objtab
      SELECT 1001, REF(C),
            SYSDATE, '10-MAY-1999',
            LineItemList_ntabtyp(),
            NULL
      FROM Customer_objtab C
      WHERE C.CustNo = 1 ;

Exercises:

      INSERT INTO TABLE (
      SELECT P.LineItemList_ntab
            FROM PurchaseOrder_objtab P
            WHERE P.PONo = 1001
      )
      SELECT 01, REF(S), 12, 0
            FROM Stock_objtab S
            WHERE S.StockNo = 1534 ;


6.5 The PurchaseOrder Type Body

      CREATE OR REPLACE TYPE BODY PurchaseOrder_objtyp AS
            MEMBER FUNCTION sumLineItems RETURN NUMBER IS
            i INTEGER;
            StockVal StockItem_objtyp;
            Total NUMBER := 0;
      BEGIN
            IF (UTL_COLL.IS_LOCATOR(LineItemList_ntab)) -- check for locator
            THEN
            SELECT SUM(L.Quantity * L.Stock_ref.Price) INTO Total
            FROM TABLE(CAST(LineItemList_ntab AS LineItemList_ntabtyp)) L;
            ELSE
            FOR i in 1..SELF.LineItemList_ntab.COUNT LOOP
            UTL_REF.SELECT_OBJECT(LineItemList_ntab(i).Stock_ref,StockVal);
            Total := Total +SELF.LineItemList_ntab(i).Quantity* StockVal.Price;
            END LOOP;
            END IF;
      RETURN Total;
      END;
      END;

Exercises: