UCM EL Modelo ObjetoRelacional Por ANGELICA URRUTIA GGR2000
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 • Modelo de Objetos sobre el modelo relacional – Object types (Name, attributes, Methods) Object_Variable. Method() GGR/2000
UCM El Modelo Objeto-Relacional Purchase Order PONo Order. Date Ship. Date get. PONo() sum. Line. Items() GGR/2000
UCM El Modelo Objeto-Relacional Constructor purchase_order( 1000376, person ("John Smith", "1 -800 -555 -1212"), NULL ) GGR/2000
UCM El Modelo Objeto-Relacional Object Table: Es un tipo especial de tabla en la cual cada fila representa un objeto 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
UCM El Modelo Objeto-Relacional Row Objects Columns objects GGR/2000
El Modelo Objeto-Relacional UCM REF Datatype Puntero Lógico a un Row Objects CREATE TYPE person AS OBJECT ( name VARCHAR 2(30), manager REF person ); DECLARE Order. Ref REF to Purchase_order; SELECT REF(po) INTO Order. Ref FROM purchase_order_table po WHERE po. id = 1000376; GGR/2000
UCM El Modelo Objeto-Relacional Collections/VARRAYs 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
UCM El Modelo Objeto-Relacional Collections/Nested Table 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
UCM El Modelo Objeto-Relacional Herencia SUBTYPE Empleado IS Persona[(constraint)] [NOT NULL]; GGR/2000
UCM El Modelo Objeto-Relacional Valores Nulos CREATE TYPE person AS OBJECT (name VARCHAR 2(30), phone VARCHAR 2(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
UCM El Modelo Objeto-Relacional Valores por defecto para objetos y coleciones CREATE TYPE person AS OBJECT ( id NUMBER, name VARCHAR 2(30), address VARCHAR 2(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; GGR/2000
UCM El Modelo Objeto-Relacional Constraint para Object Tables CREATE TYPE location (building_no NUMBER, city VARCHAR 2(40) ); CREATE TYPE person (ssno NUMBER, name VARCHAR 2(100), address VARCHAR 2(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_cons 1 UNIQUE (dept_loc. building_no, dept_loc. city), CONSTRAINT dept_loc_cons 2 CHECK (dept_loc. city IS NOT NULL) ); GGR/2000
UCM El Modelo Objeto-Relacional Indices para Tablas Objetos y Tablas anidadas CREATE TABLE department deptno CHAR(5) PRIMARY KEY, dept_name CHAR(20), dept_address ); CREATE INDEX i_dept_addr 1 ON department (dept_addr. city); GGR/2000
UCM El Modelo Objeto-Relacional Triggers para Tablas Objetos CREATE TABLE movement (ssno NUMBER, old_office, location, new_office location ); CREATE TRIGGER trig 1 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; GGR/2000
UCM El Modelo Objeto-Relacional Example 1 - Tipo definido por el usuario CREATE OR REPLACE TYPE Address AS OBJECT ( Street VARCHAR 2(80), City VARCHAR 2(80), State CHAR(2), Zip VARCHAR 2(10) ); Example 2 - Tabla Anidada CREATE OR REPLACE TYPE Address. List AS TABLE OF Address; GGR/2000
UCM El Modelo Objeto-Relacional Example 3 - User-Defined VARRAY Type CREATE OR REPLACE TYPE Phone. List AS VARRAY(10) OF VARCHAR 2(12); Example 4 - User-Defined Object Type with Embedded Object CREATE OR REPLACE TYPE Person AS OBJECT ( First. Name VARCHAR 2(10), Last. Name VARCHAR 2(10), Cur. Address ); GGR/2000
UCM El Modelo Objeto-Relacional Example 5 - User-Defined Object Type with REF CREATE OR REPLACE TYPE Person; // Tipo Incompleto// CREATE OR REPLACE TYPE Person AS OBJECT ( First. Name VARCHAR 2(10), Last. Name VARCHAR 2(10), Cur. Address, Mom REF Person, Dad REF Person, Prev. Addrs Address. List, Phone. Nums Phone. List ); GGR/2000
UCM El Modelo Objeto-Relacional Example 7 - User-Defined Object Type with Methods CREATE OR REPLACE TYPE Person; CREATE OR REPLACE TYPE Person AS OBJECT ( First. Name VARCHAR 2(10), Last. Name VARCHAR 2(10), Cur. Address, Mom REF Person, Dad REF Person, Prev. Addrs Address. List, Phone. Nums Phone. List, MEMBER FUNCTION Full. Name RETURN VARCHAR 2, MEMBER PROCEDURE Move. To(new. Addr IN Address), PRAGMA RESTRICT_REFERENCES(Full. Name, RNDS, WNDS) ); GGR/2000
UCM El Modelo Objeto-Relacional Example 7 (continuación) CREATE OR REPLACE TYPE BODY Person AS MEMBER FUNCTION Full. Name RETURN VARCHAR 2 IS BEGIN RETURN (SELF. First. Name || ' ' || SELF. Last. Name); END Full. Name; GGR/2000
UCM El Modelo Objeto-Relacional Example 8 - Creating an Object Table CREATE TABLE People OF Person NESTED TABLE Prev. Addrs STORE AS Prev. Addr. Tab; Example 9 - Creating a Table with an Object Column CREATE TABLE Corporation ( Corp. Name VARCHAR 2(80), Corp. Address, Corp. CEO REF Person SCOPE IS People ); GGR/2000
UCM El Modelo Objeto-Relacional Example 10 - Creating an Object Table with Constraints and Defaults CREATE TABLE People OF Person ( First. Name NOT NULL, Last. Name NOT NULL, Cur. Addr DEFAULT Address('500 Oracle Parkway', 'Redwood Shores', 'CA', '94065') ) NESTED TABLE Prev. Addrs STORE AS Prev. Addr. Tab; GGR/2000
UCM El Modelo Objeto-Relacional Example 11 - Returning a Nested Table as a Locator CREATE TABLE People OF Person ( First. Name NOT NULL, Last. Name NOT NULL, Cur. Addr DEFAULT Address('500 Oracle Parkway', 'Redwood Shores', 'CA', '94065') ) NESTED TABLE Prev. Addrs STORE AS Prev. Addr. Tab RETURN AS LOCATOR; GGR/2000
UCM El Modelo Objeto-Relacional Example 12 - Populating the People Table INSERT INTO People VALUES ( Person('Mr. ', 'Ellison', Address('1 First Street', 'Orlando', 'FL', '34812'), NULL, Address. List(Address('2 Second Street', 'Orlando', 'FL', '34812'), Address('3 Third Street', 'Orlando', 'FL', '34812')), Phone. List('407 -555 -1234', '407 -555 -4321')) ); GGR/2000
UCM El Modelo Objeto-Relacional Example 12 - Populating the People Table (cont. . ) INSERT INTO People VALUES ( Person('Larry', 'Ellison', Address('1 First Street', 'Orlando', 'FL', '34812'), (SELECT REF(p) FROM People p WHERE p. First. Name = 'Mrs. ' AND p. Last. Name = 'Ellison'), (SELECT REF(p) FROM People p WHERE p. First. Name = 'Mr. ' AND p. Last. Name = 'Ellison'), Address. List(Address('2 Second Street', 'Orlando', 'FL', '34812'), Address('3 Third Street', 'Orlando', 'FL', '34812')), Phone. List('407 -555 -1234', '407 -555 -4321')) ); GGR/2000
UCM El Modelo Objeto-Relacional 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. First. Name = 'Larry' AND p. Last. Name = 'Ellison') ); GGR/2000
UCM El Modelo Objeto-Relacional Example 14 - Selecting an Embedded Object SELECT c. Corp. Addr FROM Corporation c WHERE c. Corp. Name = 'Oracle Corporation'; Example 15 - Selecting an Attribute of an Embedded Object SELECT c. Corp. Addr. Street FROM Corporation c WHERE c. Corp. Name = 'Oracle Corporation'; GGR/2000
UCM El Modelo Objeto-Relacional Example 16 - Selecting an Object from an Object Table SELECT VALUE(p) FROM People p WHERE p. Last. Name = 'Ellison'; Example 17 - Selecting an Object Reference from an Object Table SELECT REF(p) FROM People p WHERE p. Last. Name = 'Ellison'; GGR/2000
UCM El Modelo Objeto-Relacional Example 18 - Selecting a Nested Table Column from an Object Table SELECT p. Prev. Addrs FROM People p WHERE p. Last. Name = 'Ellison'; Example 19 - Selecting a VARRAY Column from an Object Table SELECT p. Phone. Nums FROM People p WHERE p. Last. Name = 'Ellison'; GGR/2000
UCM El Modelo Objeto-Relacional Example 20 - Selecting Objects from a Nested Table SELECT VALUE(a) FROM TABLE(SELECT p. Prev. Addrs FROM People p WHERE p. First. Name = 'Larry' AND p. Last. Name = 'Ellison') a; GGR/2000
UCM El Modelo Objeto-Relacional Example 21 - Collection Unnesting using the TABLE operator SELECT p. First. Name, p. Last. Name, a. * FROM People p, TABLE(p. Prev. Addrs) a WHERE p. Last. Name = 'Ellison'; p. First. Name p. Last. Name a. Street a. City a. State --------------- -------Mr. Ellison 2 Second Street Orlando FL Mr. Ellison 3 Third Street Orlando FL Mrs. Ellison 2 Second Street Orlando FL Mrs. Ellison 3 Third Street Orlando FL Larry Ellison 2 Second Street Orlando FL Larry Ellison 3 Third Street Orlando FL GGR/2000 a. Zip 34812 34812
UCM El Modelo Objeto-Relacional Example 22 - Collection Unnesting using an Outer Join SELECT p. First. Name, p. Last. Name, a. * FROM People p, TABLE(p. Prev. Addrs) (+) a WHERE p. Lastname = 'Ellison'; Example 23 - Collection Unnesting with a VARRAY SELECT p. First. Name, p. Last. Name, ph. * FROM People p, TABLE(p. Phone. Nums) ph WHERE p. Last. Name = 'Ellison'; GGR/2000
UCM El Modelo Objeto-Relacional Example 24 - Selecting Data from a Referenced Object SELECT p. Mom. First. Name FROM People p WHERE p. First. Name = 'Larry' and p. Last. Name = 'Ellison'; Example 25 - Selecting a Member Function on an Object Table SELECT p. Full. Name() FROM People p WHERE p. Last. Name = 'Ellison'; GGR/2000
UCM El Modelo Objeto-Relacional Example 26 - Selecting a Member Function on a Referenced Object SELECT c. Corp. CEO. Full. Name() FROM Corporation c WHERE c. Corp. Name = 'Oracle Corporation'; GGR/2000
UCM El Modelo Objeto-Relacional Example 27 - Querying a Nested Table Using a Nested Table Locator DECLARE the. CEO Person; the. Address; BEGIN SELECT VALUE(p) INTO the. CEO FROM People p WHERE p. First. Name = 'Larry' AND p. Last. Name = 'Ellison'; IF (UTL_COLL. IS_LOCATOR(the. CEO. Prev. Addrs)) THEN SELECT VALUE(a) INTO the. Addr FROM TABLE(CAST(the. CEO. Prev. Addrs AS Address. List)) a WHERE a. Street = '2 Second Street'; ELSE FOR ndx IN 1. . the. CEO. Prev. Addrs. COUNT LOOP IF (the. CEO. Prev. Addrs(ndx) = '2 Second Street') THEN the. Addr : = the. CEO. Prev. Addrs(ndx); END IF; END LOOP; END IF; END; GGR/2000
UCM El Modelo Objeto-Relacional Example 28 - Invoking a Member Procedure on an Object Type DECLARE the. CEO Person; new. Address : = Address('500 Oracle Parkway', 'Redwood Shores', 'CA', '94065'); BEGIN -- retrieve Person object from the database SELECT VALUE(p) INTO the. CEO FROM People p WHERE p. First. Name = 'Larry' AND p. Last. Name = 'Ellison'; -- change the address of the transient Person object the. CEO. move. To(new. Addr); -- update the corresponding Person object in the database UPDATE People p SET p = the. CEO WHERE p. First. Name = 'Larry' AND p. Last. Name = 'Ellison'; END; GGR/2000
UCM El Modelo Objeto-Relacional Example 29 - Inserting into a Nested Table Using SQL INSERT INTO TABLE(SELECT p. Prev. Addrs FROM People p WHERE p. First. Name = 'Larry' AND p. Last. Name = 'Ellison') VALUES ( Address('123 First Avenue', 'San Francisco', 'CA', '90909') ); GGR/2000
UCM El Modelo Objeto-Relacional Example 30 - Update a Nested Table Using SQL UPDATE TABLE(SELECT p. Prev. Addrs FROM People p WHERE p. First. Name = 'Larry' AND p. Last. Name = 'Ellison') pa SET pa. Zip = '90910' WHERE pa. City = 'San Francisco' AND pa. State = 'CA'; GGR/2000
UCM El Modelo Objeto-Relacional Example 31 - Querying Referenced Objects using DEREF SELECT DEREF(c. Corp. CEO) FROM Corporation c WHERE c. Corp. Name = 'Oracle Corporation'; GGR/2000
UCM El Modelo Objeto-Relacional Example 30 - Update a Nested Table Using SQL UPDATE TABLE(SELECT p. Prev. Addrs FROM People p WHERE p. First. Name = 'Larry' AND p. Last. Name = 'Ellison') pa SET pa. Zip = '90910' WHERE pa. City = 'San Francisco' AND pa. State = 'CA'; GGR/2000
Subtypos Example 31 – CREATE TYPE Using SQL CREATE TYPE direccion AS OBJECT (n-rut, calle varchar (10)) CREATE TYPE persona AS OBJECT (rut int, direccion) GGR/2000 UCM
Subtypos Example 32 – CREATE TABLE Using SQL (objeto fila) CREATE TABLE person OF persona INSERT INTO person VALUES (persona (7634559), direccion (1 sur 1350)); GGR/2000 UCM
Subtypos UCM Example 32 – CREATE TABLE Using SQL (objeto columna) CREATE TABLE person 1 OF (persona, sal INT (6)) CREATE TABLE person 1 OF ( persona, sal INT (6) INSERT INTO person 1 VALUES (persona (7634559), direccion (1 sur 1350), 10. 000); GGR/2000
- Slides: 45