1 / 45

EL Modelo Objeto-Relacional Por: ANGELICA URRUTIA

UCM. EL Modelo Objeto-Relacional Por: ANGELICA URRUTIA. GGR/2000. UCM. Contenido Introducción El modelo Objeto-Relacional Creación de tipos de datos Creación de tablas Manipulación de objetos. GGR/2000. UCM. El Modelo Objeto-Relacional

rafiki
Download Presentation

EL Modelo Objeto-Relacional Por: ANGELICA URRUTIA

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. UCM EL Modelo Objeto-Relacional Por: ANGELICA URRUTIA GGR/2000

  2. UCM Contenido • Introducción • El modelo Objeto-Relacional • Creación de tipos de datos • Creación de tablas • Manipulación de objetos GGR/2000

  3. UCM El Modelo Objeto-Relacional • Modelo de Objetos sobre el modelo relacional • Object types (Name, attributes, Methods) Object_Variable.Method() GGR/2000

  4. UCM Purchase Order PONo OrderDate ShipDate getPONo() sumLineItems() El Modelo Objeto-Relacional GGR/2000

  5. UCM Constructor El Modelo Objeto-Relacional purchase_order( 1000376, person ("John Smith","1-800-555-1212"), NULL ) GGR/2000

  6. UCM Object Table: Es un tipo especial de tabla en la cual cada fila representa un objeto El Modelo Objeto-Relacional CREATE TABLE person_table OF person; INSERT INTO person_table VALUES ( "John Smith","1-800-555-1212" ); SELECT VALUE(p) FROM person_table p WHERE p.name = "John Smith"; GGR/2000

  7. UCM El Modelo Objeto-Relacional Row Objects Columns objects GGR/2000

  8. UCM REF Datatype Puntero Lógico a un Row Objects El Modelo Objeto-Relacional CREATE TYPE person AS OBJECT ( name VARCHAR2(30), manager REF person ); DECLARE OrderRef REF to Purchase_order; SELECT REF(po) INTO OrderRef FROM purchase_order_table po WHERE po.id = 1000376; GGR/2000

  9. UCM Collections/VARRAYs El Modelo Objeto-Relacional CREATE TYPE prices AS VARRAY(10) OF NUMBER(12,2); • Conjunto Ordenado de elementos • Todos Los elementos son del mismo tipo • Cada elemento tiene un índice GGR/2000

  10. UCM Collections/Nested Table El Modelo Objeto-Relacional CREATE TYPE lineitem_table AS TABLE OF lineitem; • Conjunto no Ordenado de elementos • Todos Los elementos son del mismo tipo • Tiene una sola columna (built-in, Object Type) NESTED TABLE lineitems STORE AS lineitems_table; GGR/2000

  11. UCM Herencia El Modelo Objeto-Relacional SUBTYPE Empleado IS Persona[(constraint)] [NOT NULL]; GGR/2000

  12. UCM Valores Nulos El Modelo Objeto-Relacional CREATE TYPE person AS OBJECT (name VARCHAR2(30),phone VARCHAR2(20) ); CREATE TABLE contacts (contact person, date DATE ); INSERT INTO contacts VALUES (person (NULL, NULL), ’24 Jun 1997’); INSERT INTO contacts VALUES (NULL,’24 Jun 1997’ ); GGR/2000

  13. UCM Valores por defecto para objetos y coleciones CREATE TYPE person AS OBJECT ( id NUMBER, name VARCHAR2(30), address VARCHAR2(30) ); CREATE TYPE people AS TABLE OF person; people ( person(1, ’John Smith’, ’5 Cherry Lane’), person(2, ’Diane Smith’, NULL) ) CREATE TABLE department ( d_no CHAR(5) PRIMARY KEY, d_name CHAR(20), d_mgr person DEFAULT person(1,’John Doe’,NULL), d_emps people DEFAULT people() ) NESTED TABLE d_emps STORE AS d_emps_tab; El Modelo Objeto-Relacional GGR/2000

  14. UCM Constraint para Object Tables CREATE TYPE location (building_no NUMBER,city VARCHAR2(40) ); CREATE TYPE person (ssno NUMBER,name VARCHAR2(100), address VARCHAR2(100),office location ); CREATE TABLE person_extent OF person (ssno PRIMARY KEY ); CREATE TABLE department ( deptno CHAR(5) PRIMARY KEY, dept_name CHAR(20),dept_mgr person, dept_loc location, CONSTRAINT dept_loc_cons1 UNIQUE (dept_loc.building_no, dept_loc.city), CONSTRAINT dept_loc_cons2 CHECK (dept_loc.city IS NOT NULL) ); El Modelo Objeto-Relacional GGR/2000

  15. UCM Indices para Tablas Objetos y Tablas anidadas CREATE TABLE department deptno CHAR(5) PRIMARY KEY, dept_name CHAR(20), dept_addr address ); CREATE INDEX i_dept_addr1 ON department (dept_addr.city); El Modelo Objeto-Relacional GGR/2000

  16. UCM Triggers para Tablas Objetos CREATE TABLE movement (ssno NUMBER,old_office, location, new_office location ); CREATE TRIGGER trig1 BEFORE UPDATE OF office ON person_extent FOR EACH ROW WHEN new.office.city = ’REDWOOD SHORES’ BEGIN IF :new.office.building_no = 600 THEN INSERT INTO movement (ssno, old_office, new_office) VALUES (:old.ssno, :old.office, :new.office); END IF; END; El Modelo Objeto-Relacional GGR/2000

  17. UCM Example 1 - Tipo definido por el usuario CREATE OR REPLACE TYPE Address AS OBJECT ( Street VARCHAR2(80), City VARCHAR2(80), State CHAR(2), Zip VARCHAR2(10) ); Example 2 - Tabla Anidada CREATE OR REPLACE TYPE AddressList AS TABLE OF Address; El Modelo Objeto-Relacional GGR/2000

  18. UCM Example 3 - User-Defined VARRAY Type CREATE OR REPLACE TYPE PhoneList AS VARRAY(10) OF VARCHAR2(12); Example 4 - User-Defined Object Type with Embedded Object CREATE OR REPLACE TYPE Person AS OBJECT ( FirstName VARCHAR2(10), LastName VARCHAR2(10), CurAddr Address ); El Modelo Objeto-Relacional GGR/2000

  19. UCM Example 5 - User-Defined Object Type with REF CREATE OR REPLACE TYPE Person; // Tipo Incompleto// El Modelo Objeto-Relacional CREATE OR REPLACE TYPE Person AS OBJECT ( FirstName VARCHAR2(10), LastName VARCHAR2(10), CurAddr Address, Mom REF Person, Dad REF Person, PrevAddrs AddressList, PhoneNums PhoneList ); GGR/2000

  20. UCM Example 7 - User-Defined Object Type with Methods CREATE OR REPLACE TYPE Person; CREATE OR REPLACE TYPE Person AS OBJECT ( FirstName VARCHAR2(10), LastName VARCHAR2(10), CurAddr Address, Mom REF Person, Dad REF Person, PrevAddrs AddressList, PhoneNums PhoneList, MEMBER FUNCTION FullName RETURN VARCHAR2, MEMBER PROCEDURE MoveTo(newAddr IN Address), PRAGMA RESTRICT_REFERENCES(FullName, RNDS, WNDS) ); El Modelo Objeto-Relacional GGR/2000

  21. UCM Example 7 (continuación) CREATE OR REPLACE TYPE BODY Person AS MEMBER FUNCTION FullName RETURN VARCHAR2 IS BEGIN RETURN (SELF.FirstName || ' ' || SELF.LastName); END FullName; El Modelo Objeto-Relacional GGR/2000

  22. UCM Example 8 - Creating an Object Table CREATE TABLE People OF Person NESTED TABLE PrevAddrs STORE AS PrevAddrTab; El Modelo Objeto-Relacional Example 9 - Creating a Table with an Object Column CREATE TABLE Corporation ( CorpName VARCHAR2(80), CorpAddr Address, CorpCEO REF Person SCOPE IS People ); GGR/2000

  23. UCM Example 10 - Creating an Object Table with Constraints and Defaults CREATE TABLE People OF Person ( FirstName NOT NULL, LastName NOT NULL, CurAddr DEFAULT Address('500 Oracle Parkway', 'Redwood Shores', 'CA', '94065') ) NESTED TABLE PrevAddrs STORE AS PrevAddrTab; El Modelo Objeto-Relacional GGR/2000

  24. UCM Example 11 - Returning a Nested Table as a Locator El Modelo Objeto-Relacional CREATE TABLE People OF Person ( FirstName NOT NULL, LastName NOT NULL, CurAddr DEFAULT Address('500 Oracle Parkway', 'Redwood Shores', 'CA', '94065') ) NESTED TABLE PrevAddrs STORE AS PrevAddrTab RETURN AS LOCATOR; GGR/2000

  25. UCM Example 12 - Populating the People Table INSERT INTO People VALUES ( Person('Mr.', 'Ellison', Address('1 First Street', 'Orlando', 'FL', '34812'), NULL, NULL, AddressList(Address('2 Second Street', 'Orlando', 'FL', '34812'), Address('3 Third Street', 'Orlando', 'FL', '34812')), PhoneList('407-555-1234', '407-555-4321')) ); El Modelo Objeto-Relacional GGR/2000

  26. UCM Example 12 - Populating the People Table (cont..) El Modelo Objeto-Relacional INSERT INTO People VALUES ( Person('Larry', 'Ellison', Address('1 First Street', 'Orlando', 'FL', '34812'), (SELECT REF(p) FROM People p WHERE p.FirstName = 'Mrs.' AND p.LastName = 'Ellison'), (SELECT REF(p) FROM People p WHERE p.FirstName = 'Mr.' AND p.LastName = 'Ellison'), AddressList(Address('2 Second Street', 'Orlando', 'FL', '34812'), Address('3 Third Street', 'Orlando', 'FL', '34812')), PhoneList('407-555-1234', '407-555-4321')) ); GGR/2000

  27. UCM Example 13 - Populating the Corporation Table INSERT INTO Corporation VALUES ( 'Oracle Corporation', Address('500 Oracle Parkway', 'Redwood Shores', 'CA', '94065'), (SELECT REF(p) FROM People p WHERE p.FirstName = 'Larry' AND p.LastName = 'Ellison') ); El Modelo Objeto-Relacional GGR/2000

  28. UCM Example 14 - Selecting an Embedded Object SELECT c.CorpAddr FROM Corporation c WHERE c.CorpName = 'Oracle Corporation'; El Modelo Objeto-Relacional Example 15 - Selecting an Attribute of an Embedded Object SELECT c.CorpAddr.Street FROM Corporation c WHERE c.CorpName = 'Oracle Corporation'; GGR/2000

  29. UCM Example 16 - Selecting an Object from an Object Table SELECT VALUE(p) FROM People p WHERE p.LastName = 'Ellison'; Example 17 - Selecting an Object Reference from an Object Table SELECT REF(p) FROM People p WHERE p.LastName = 'Ellison'; El Modelo Objeto-Relacional GGR/2000

  30. UCM Example 18 - Selecting a Nested Table Column from an Object Table SELECT p.PrevAddrs FROM People p WHERE p.LastName = 'Ellison'; Example 19 - Selecting a VARRAY Column from an Object Table SELECT p.PhoneNums FROM People p WHERE p.LastName = 'Ellison'; El Modelo Objeto-Relacional GGR/2000

  31. UCM Example 20 - Selecting Objects from a Nested Table SELECT VALUE(a) FROM TABLE(SELECT p.PrevAddrs FROM People p WHERE p.FirstName = 'Larry' AND p.LastName = 'Ellison') a; El Modelo Objeto-Relacional GGR/2000

  32. UCM Example 21 - Collection Unnesting using the TABLE operator SELECT p.FirstName, p.LastName, a.* FROM People p, TABLE(p.PrevAddrs) a WHERE p.LastName = 'Ellison'; p.FirstName p.LastName a.Street a.City a.State a.Zip ------------ ----------- -------------- ---------- --------- -------- Mr. Ellison 2 Second Street Orlando FL 34812 Mr. Ellison 3 Third Street Orlando FL 34812 Mrs. Ellison 2 Second Street Orlando FL 34812 Mrs. Ellison 3 Third Street Orlando FL 34812 Larry Ellison 2 Second Street Orlando FL 34812 Larry Ellison 3 Third Street Orlando FL 34812 El Modelo Objeto-Relacional GGR/2000

  33. UCM Example 22 - Collection Unnesting using an Outer Join SELECT p.FirstName, p.LastName, a.* FROM People p, TABLE(p.PrevAddrs) (+) a WHERE p.Lastname = 'Ellison'; El Modelo Objeto-Relacional Example 23 - Collection Unnesting with a VARRAY SELECT p.FirstName, p.LastName, ph.* FROM People p, TABLE(p.PhoneNums) ph WHERE p.LastName = 'Ellison'; GGR/2000

  34. UCM Example 24 - Selecting Data from a Referenced Object SELECT p.Mom.FirstName FROM People p WHERE p.FirstName = 'Larry' and p.LastName = 'Ellison'; Example 25 - Selecting a Member Function on an Object Table SELECT p.FullName() FROM People p WHERE p.LastName = 'Ellison'; El Modelo Objeto-Relacional GGR/2000

  35. UCM Example 26 - Selecting a Member Function on a Referenced Object SELECT c.CorpCEO.FullName() FROM Corporation c WHERE c.CorpName = 'Oracle Corporation'; El Modelo Objeto-Relacional GGR/2000

  36. UCM Example 27 - Querying a Nested Table Using a Nested Table Locator DECLARE theCEO Person; theAddr Address; BEGIN SELECT VALUE(p) INTO theCEO FROM People p WHERE p.FirstName = 'Larry' AND p.LastName = 'Ellison'; IF (UTL_COLL.IS_LOCATOR(theCEO.PrevAddrs)) THEN SELECT VALUE(a) INTO theAddr FROM TABLE(CAST(theCEO.PrevAddrs AS AddressList)) a WHERE a.Street = '2 Second Street'; ELSE FOR ndx IN 1..theCEO.PrevAddrs.COUNT LOOP IF (theCEO.PrevAddrs(ndx) = '2 Second Street') THEN theAddr := theCEO.PrevAddrs(ndx); END IF; END LOOP; END IF; END; El Modelo Objeto-Relacional GGR/2000

  37. UCM Example 28 - Invoking a Member Procedure on an Object Type DECLARE theCEO Person; newAddr Address := Address('500 Oracle Parkway', 'Redwood Shores', 'CA', '94065'); BEGIN -- retrieve Person object from the database SELECT VALUE(p) INTO theCEO FROM People p WHERE p.FirstName = 'Larry' AND p.LastName = 'Ellison'; -- change the address of the transient Person object theCEO.moveTo(newAddr); -- update the corresponding Person object in the database UPDATE People p SET p = theCEO WHERE p.FirstName = 'Larry' AND p.LastName = 'Ellison'; END; El Modelo Objeto-Relacional GGR/2000

  38. UCM Example 29 - Inserting into a Nested Table Using SQL INSERT INTO TABLE(SELECT p.PrevAddrs FROM People p WHERE p.FirstName = 'Larry' AND p.LastName = 'Ellison') VALUES ( Address('123 First Avenue', 'San Francisco', 'CA', '90909') ); El Modelo Objeto-Relacional GGR/2000

  39. UCM Example 30 - Update a Nested Table Using SQL UPDATE TABLE(SELECT p.PrevAddrs FROM People p WHERE p.FirstName = 'Larry' AND p.LastName = 'Ellison') pa SET pa.Zip = '90910' WHERE pa.City = 'San Francisco' AND pa.State = 'CA'; El Modelo Objeto-Relacional GGR/2000

  40. UCM Example 31 - Querying Referenced Objects using DEREF SELECT DEREF(c.CorpCEO) FROM Corporation c WHERE c.CorpName = 'Oracle Corporation'; El Modelo Objeto-Relacional GGR/2000

  41. UCM Example 30 - Update a Nested Table Using SQL UPDATE TABLE(SELECT p.PrevAddrs FROM People p WHERE p.FirstName = 'Larry' AND p.LastName = 'Ellison') pa SET pa.Zip = '90910' WHERE pa.City = 'San Francisco' AND pa.State = 'CA'; El Modelo Objeto-Relacional GGR/2000

  42. UCM Example 31 – CREATE TYPE Using SQL CREATE TYPE direccion AS OBJECT (n-rut, calle varchar (10)) CREATE TYPE persona AS OBJECT (rut int, dir direccion) Subtypos GGR/2000

  43. UCM Example 32 – CREATE TABLE Using SQL (objeto fila) CREATE TABLE person OF persona INSERT INTO person VALUES (persona (7634559), direccion (1 sur 1350)); Subtypos GGR/2000

  44. UCM Example 32 – CREATE TABLE Using SQL (objeto columna) CREATE TABLE person1 OF (persona, sal INT (6)) CREATE TABLE person1 OF ( per persona, sal INT (6) INSERT INTO person1 VALUES (persona (7634559), direccion (1 sur 1350), 10.000); Subtypos GGR/2000

More Related