1 / 35

Object-Relational Oracle 11g Tutorial

Object-Relational Oracle 11g Tutorial. Νίκος Πελέκης Εργαστήριο Πληροφοριακών Συστημάτων , Τμήμα Πληροφορικής, Πανεπιστήμιο Πειραιώς http://infolab.cs.unipi.gr/. Outline of seminar. Part III Introduction to Oracle Objects Basic Components of Oracle Objects

caron
Download Presentation

Object-Relational Oracle 11g Tutorial

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Object-Relational Oracle 11gTutorial Νίκος Πελέκης Εργαστήριο Πληροφοριακών Συστημάτων, Τμήμα Πληροφορικής, Πανεπιστήμιο Πειραιώς http://infolab.cs.unipi.gr/

  2. Outline of seminar • Part III • Introduction to Oracle Objects • Basic Components of Oracle Objects • Support for Collection Datatypes • Object Support in Oracle Programming Environments • Applying an Object Model to Relational Data • A Sample Application Using Object-Relational Features

  3. A Sample Application Using Object-Relational Features The examples in this seminar illustrate the most important aspects of defining, using, and evolving user-defined types. Working with user-defined types is creating methods that perform operations on objects. Case study: An application that manages customer purchase orders. First a purely relational version is shown, and then an equivalent, object-relational version. Both versions provide for the same basic kinds of entities—customers, purchase orders, line items, and so on. But the object-relational version creates user-defined types for these entities and manages data for particular customers and purchase orders. 3

  4. E-RDiagram for Purchase Order Application 4

  5. Purchase Order Application • The figure shows the entity relationships among the following four relational entities or tables: • Customer • Purchase Order • Line Item of a purchase order • Stock Item • A customer places zero or more purchase orders, but a purchase order has at most one customer. • A purchase order can contain many line items, but a given line item occurs on exactly one purchase order. • Each line item refers to exactly one stock item; a stock item can occur in zero or many different line items. 5

  6. First Create your USER CREATE USER testuserXXX IDENTIFIED BY testuserXXX QUOTA UNLIMITED ON USERS ACCOUNT UNLOCK; GRANT CREATE PROCEDURE TO testuserXXX; GRANT CREATE SEQUENCE TO testuserXXX; GRANT CREATE SESSION TO testuserXXX; GRANT CREATE SYNONYM TO testuserXXX; GRANT CREATE TABLE TO testuserXXX; GRANT CREATE TYPE TO testuserXXX; GRANT CREATE JOB TO testuserXXX; GRANT CREATE VIEW TO testuserXXX; GRANT SELECT ANY DICTIONARY TO testuserXXX; GRANT CONNECT TO testuserXXX; 6

  7. Creating Tables Under the Relational Model CREATE TABLE Customer_reltab ( CustNo NUMBER NOT NULL, CustName VARCHAR2(200) NOT NULL, Street VARCHAR2(200) NOT NULL, City VARCHAR2(200) NOT NULL, State CHAR(2) NOT NULL, Zip VARCHAR2(20) NOT NULL, Phone1 VARCHAR2(20), Phone2 VARCHAR2(20), Phone3 VARCHAR2(20), PRIMARY KEY (CustNo)); 7

  8. Creating Tables Under the Relational Model CREATE TABLE PurchaseOrder_reltab ( PONo NUMBER, /* purchase order no */ Custno NUMBER references Customer_reltab, /* Foreign KEY referencingcustomer */ OrderDate DATE, /* date of order */ ShipDate DATE, /* date to be shipped */ ToStreet VARCHAR2(200), /* shipto address */ ToCity VARCHAR2(200), ToState CHAR(2), ToZip VARCHAR2(20), PRIMARY KEY(PONo)); 8

  9. Creating Tables Under the Relational Model CREATE TABLE Stock_reltab ( StockNo NUMBER PRIMARY KEY, Price NUMBER, TaxRate NUMBER); CREATE TABLE LineItems_reltab ( LineItemNo NUMBER, PONo NUMBER REFERENCES PurchaseOrder_reltab, StockNo NUMBER REFERENCES Stock_reltab, Quantity NUMBER, Discount NUMBER, PRIMARY KEY (PONo, LineItemNo)); 9

  10. Inserting Values Under the Relational Model INSERT INTO Stock_reltab VALUES(1004, 6750.00, 2); INSERT INTO Stock_reltab VALUES(1011, 4500.23, 2); INSERT INTO Stock_reltab VALUES(1534, 2234.00, 2); INSERT INTO Stock_reltab VALUES(1535, 3456.23, 2); INSERT INTO Customer_reltab VALUES (1, 'Jean Nance', '2 Avocet Drive', 'Redwood Shores', 'CA', '95054', '415-555-1212', NULL, NULL); INSERT INTO Customer_reltab VALUES (2, 'John Nike', '323 College Drive', 'Edison', 'NJ', '08820', '609-555-1212', '201-555-1212', NULL); INSERT INTO PurchaseOrder_reltab VALUES (1001, 1, SYSDATE, to_date('1997/05/10', 'yyyy/mm/dd'), NULL, NULL, NULL, NULL); INSERT INTO PurchaseOrder_reltab VALUES (2001, 2, SYSDATE, to_date('1997/05/20', 'yyyy/mm/dd'), '55 Madison Ave', 'Madison', 'WI', '53715'); INSERT INTO LineItems_reltab VALUES(01, 1001, 1534, 12, 0); INSERT INTO LineItems_reltab VALUES(02, 1001, 1535, 10, 10); INSERT INTO LineItems_reltab VALUES(01, 2001, 1004, 1, 0); INSERT INTO LineItems_reltab VALUES(02, 2001, 1011, 2, 1); 10

  11. Implementing the Schema using the Object-Relational Model 11

  12. Implementing the Schema on the Object-Relational Model In the previous diagram, a customer's phone number attribute is a collection that can contain zero to ten phone numbers. A customer's address is modelled as a single Address object, which contains the attributes Street, City, State, and Zip. The Address object associated with a customer is also used by any Purchase Order objects associated with that customer. In a Purchase Order, the customer's address is used as the shipping address. A Purchase Order contains as an attribute a collection of one or more Line Items. Each Line Item refers to one Stock Item. 12

  13. Defining Types CREATE TYPE StockItem_objtyp AS OBJECT ( StockNo NUMBER, Price NUMBER, TaxRate NUMBER ); CREATE TYPE LineItem_objtyp; CREATE OR REPLACE TYPE LineItem_objtyp AS OBJECT ( LineItemNo NUMBER, Stock_ref REF StockItem_objtyp, Quantity NUMBER, Discount NUMBER ); 13

  14. Defining Types 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, ORDER MEMBER FUNCTION compareCustOrders(x IN Customer_objtyp) RETURN INTEGER ) NOT FINAL; 14

  15. Defining Types CREATE TYPE LineItemList_ntabtyp AS TABLE OF LineItem_objtyp; 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, MAP MEMBER FUNCTION getPONo RETURN NUMBER, MEMBER FUNCTION sumLineItems RETURN NUMBER ); 15

  16. Method Definitions CREATE OR REPLACE TYPE BODY PurchaseOrder_objtyp AS MAP MEMBER FUNCTION getPONo RETURN NUMBER is BEGIN RETURN PONo; END; MEMBER FUNCTION sumLineItems RETURN NUMBER is i INTEGER; StockVal StockItem_objtyp; Total NUMBER := 0; BEGIN 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; RETURN Total; END; END; 16

  17. Method Definitions CREATE OR REPLACE TYPE BODY Customer_objtyp AS ORDER MEMBER FUNCTION compareCustOrders (x IN Customer_objtyp) RETURN INTEGER IS BEGIN RETURN CustNo - x.CustNo; END; END; 17

  18. Creating Object Tables CREATE TABLE Customer_objtab OF Customer_objtyp (CustNo PRIMARY KEY) OBJECT IDENTIFIER IS PRIMARY KEY ; 18

  19. Creating Object Tables CREATE TABLE Stock_objtab OF StockItem_objtyp (StockNo PRIMARY KEY) OBJECT IDENTIFIER IS PRIMARY KEY ; CREATE TABLE Customer_objtab OF Customer_objtyp (CustNo PRIMARY KEY) OBJECT IDENTIFIER IS PRIMARY KEY ; CREATE TABLE PurchaseOrder_objtab OF PurchaseOrder_objtyp ( /* Line 1 */ PRIMARY KEY (PONo), /* Line 2 */ FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab) /* Line 3 */ OBJECT IDENTIFIER IS PRIMARY KEY /* Line 4 */ NESTED TABLE LineItemList_ntab STORE AS PoLine_ntab ( /* Line 5 */ (PRIMARY KEY(NESTED_TABLE_ID, LineItemNo)) /* Line 6 */ ORGANIZATION INDEX COMPRESS) /* Line 7 */ RETURN AS LOCATOR /* Line 8 */; ALTER TABLE PoLine_ntab ADD (SCOPE FOR (Stock_ref) IS stock_objtab) ; 19

  20. Replacing the PurchaseOrder_objtyp Type Body CREATE OR REPLACE TYPE BODY PurchaseOrder_objtyp AS MAP MEMBER FUNCTION getPONo RETURN NUMBER is BEGIN RETURN PONo; END; MEMBER FUNCTION sumLineItems RETURN NUMBER IS i INTEGER; StockVal StockItem_objtyp; Total NUMBER := 0; BEGIN IF (UTL_COLL.IS_LOCATOR(LineItemList_ntab)) 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; 20

  21. Inserting Values 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) ; 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') ) ; INSERT INTO PurchaseOrder_objtab SELECT 1001, REF(C), SYSDATE, SYSDATE, LineItemList_ntabtyp(), NULL FROM Customer_objtab C WHERE C.CustNo = 1 ; 21

  22. Inserting Values in LineItemList_ntab 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 ; 22

  23. Inserting Values in PurchaseOrder_objtab and LineItemList_ntab INSERT INTO PurchaseOrder_objtab SELECT 2001, REF(C), SYSDATE, SYSDATE, LineItemList_ntabtyp(), Address_objtyp('55 Madison Ave','Madison','WI','53715') FROM Customer_objtab C WHERE C.CustNo = 2 ; INSERT INTO TABLE ( SELECT P.LineItemList_ntab FROM PurchaseOrder_objtab P WHERE P.PONo = 1001 ) SELECT 02, REF(S), 10, 10 FROM Stock_objtab S WHERE S.StockNo = 1535 ; 23

  24. Inserting Values in PurchaseOrder_objtab and LineItemList_ntab INSERT INTO TABLE ( SELECT P.LineItemList_ntab FROM PurchaseOrder_objtab P WHERE P.PONo = 2001 ) SELECT 10, REF(S), 1, 0 FROM Stock_objtab S WHERE S.StockNo = 1004 ; INSERT INTO TABLE ( SELECT P.LineItemList_ntab FROM PurchaseOrder_objtab P WHERE P.PONo = 2001 ) VALUES(11, ( SELECT REF(S) FROM Stock_objtab S WHERE S.StockNo = 1011), 2, 1) ; 24

  25. Query Purchase Orders SELECT p.PONo FROM PurchaseOrder_objtab p ORDER BY VALUE(p) ; 25

  26. Query Customer and Line Item Data for Purchase Order 1001 ??? 26

  27. Query Customer and Line Item Data for Purchase Order 1001 SELECT DEREF(p.Cust_ref), p.ShipToAddr_obj, p.PONo, p.OrderDate, LineItemList_ntab FROM PurchaseOrder_objtab p WHERE p.PONo = 1001 ; 27

  28. Query Total Value of Each Purchase Order ??? 28

  29. Query Total Value of Each Purchase Order SELECT p.PONo, p.sumLineItems() FROM PurchaseOrder_objtab p ; 29

  30. Query Purchase Order and Line Item Data for Stock Item 1004 ??? 30

  31. Query Purchase Order and Line Item Data for Stock Item 1004 SELECT po.PONo, po.Cust_ref.CustNo, CURSOR ( SELECT * FROM TABLE (po.LineItemList_ntab) L WHERE L.Stock_ref.StockNo = 1004 ) FROM PurchaseOrder_objtab po ; OR SELECT po.PONo, po.Cust_ref.CustNo, L.* FROM PurchaseOrder_objtab po, TABLE (po.LineItemList_ntab) L WHERE L.Stock_ref.StockNo = 1004 ; 31

  32. Query Average Discount across all Line Items of all Purchase Orders ??? 32

  33. Query Average Discount across all Line Items of all Purchase Orders SELECT AVG(L.DISCOUNT) FROM PurchaseOrder_objtab po, TABLE (po.LineItemList_ntab) L ; 33

  34. Υλικό • Tutorials at UNIPI: http://infolab.cs.unipi.gr/courses/db/labs/ • Tutorials at OTN: • http://www.oracle.com/technology/obe/start/index.html • http://www.oracle.com/technology/obe/11gr1_db/otn_all_db11gr1.html • http://www.oracle.com/technology/obe/hol08/otn_all_hol08.html • SQL Developer: http://www.oracle.com/technology/obe/hol08/sqldev_mngdb/sqldev_mngdb/sqldev_mngdb_otn.htm • EM at UNIPI: https://kittie:1158/em • SID= ORCL • Server name=KITTIE • User “DMUSERX” password=userX • Oracle download: http://www.oracle.com/technology/software/products/database/index.html • Video tutorials: http://www.vtc.com/products/Oracle-Database-Administration-Fundamentals-I-tutorials.htm# 34

  35. Επικοινωνία University of Piraeus, Information Systems Laboratory 80-82 Zeas Str., 18534 Piraeus, Greece3rd Floor Phone: (+30) 210 4142428 http://infolab.cs.unipi.gr/ Νίκος Πελέκης npelekis@unipi.gr 35

More Related