Implementación en Oracle de SQL99

Implementación en Oracle de SQL99

SQL99 CREATE DISTINCT TYPE us_dollar AS DECIMAL(9,2) CREATE DISTINCT TYPE canadian_dollar AS DECIMAL(9,2) Oracle CREATE or REPLACE TYPE us_dollar as object(price NUMBER(9,2)); CREATE or REPLACE TYPE canadian_dollar as object(price NUMBER(9,2)); Tipos de datos primitivos definidos por el usuario

Large Objects • LOB tiene dos subtipos • Binary Large Object, BLOB, almacena objetos multimedia: audio, imagen, mapas, video, fotos. • Character Large Object, CLOB, almacena texto • LOB locator, aputandor a un LOB. CREATE TABLE documental ( titulo VARCHAR2(200), id_doc INTEGER, resumen CLOB (32K), texto_doc CLOB (20M), video_film BLOB (2K));

Oracle Large Objects • No se especifican límites para los Large Objects. CREATE TABLE documental ( titulo VARCHAR2(200), id_doc INTEGER, resumen CLOB, texto_doc CLOB, video_film BLOB);

Oracle Large Objets • Lob locator/Bfile CREATE TYPE Voiced_typ AS OBJECT ( Originator VARCHAR2(30), Script CLOB, Actor VARCHAR2(30), Take NUMBER, Recording BFILE ); Create table voiced of voiced_typ; Update voiced Where … Set recording=LOBFILE (‘/u01/app…’);

TADS y sus Operaciones create or replace type DataStream as object ( id integer, name varchar2(20), createdOn date, data clob, MEMBER FUNCTION DataStreamMin return pls_integer, MEMBER FUNCTION DataStreamMax return pls_integer)

TADS y sus Operaciones CREATE OR REPLACE TYPE BODY DataStream IS MEMBER FUNCTION DataStreamMin return pls_integer is a pls_integer := DS_Package.ds_findmin(data); begin return a; end; MEMBER FUNCTION DataStreamMax return pls_integer is b pls_integer := DS_Package.ds_findmax(data); begin return b; end; end;

Constructores • purchase_order(1000376,person ("John Smith","1-800-555-1212"),NULL ) • INSERT INTO person_table VALUES ("John Smith","1-800-555-1212" ); INSERT INTO purchase_order( SELECT 1000376, VALUE(p), null FROM person_table pWHERE p.name = "John Smith“);

CREATE TYPE person_t AS OBJECT (name VARCHAR2(100), ssn NUMBER) NOT FINAL; / CREATE TYPE employee_t UNDER person_t (department_id NUMBER, salary NUMBER) NOT FINAL; / CREATE TYPE part_time_emp_t UNDER employee_t (num_hrs NUMBER); / Subtipos y Herencia

Consultando objetos SELECT VALUE(p) FROM persons p; VALUE(P)(NAME, SSN) ---------------------------------------- PERSON_T('Bob', 1234) EMPLOYEE_T('Joe', 32456, 12, 100000) PART_TIME_EMP_T('Tim', 5678, 13, 1000, 20)

Final Not Final create type Name as(firstnamevarchar(20),lastname varchar(20))final create type Address as (street varchar(20),city varchar(20),zipcode varchar(20)) not final • Nota: final y not final indican cuando se pueden crear subtipos.

Funciones de Correspondencias • Los Map methods producen un valor simple de un tipo definido por el usuario que puede ser compuesto, para ser usado en funciones de comparación y ordenamientos. • Por ejemplo, si se define un objeto del tipo RECTANGLE, el método de correspondencia AREA puede multiplicar sus atributos HEIGHT y WIDTH y retornar una respuesta. • Oracle puede comparar dos rectángulos por medio de la comparación de sus áreas.

Funciones de Correspondencia CREATE TYPE rectangle_typ AS OBJECT ( len NUMBER, wid NUMBER, MAP MEMBER FUNCTION area RETURN NUMBER); / CREATE TYPE BODY rectangle_typ AS MAP MEMBER FUNCTION area RETURN NUMBER IS BEGIN RETURN len * wid; END area; END; /

Column type o Row Type? SQL> CREATE TYPE person_t AS OBJECT (name VARCHAR2(100), ssn NUMBER) NOT FINAL; / Type created. SQL> create table prueba(p person_t); Table created. SQL> create table prueba2 of person_t; Table created.

Referencias CREATE TYPE cust_address_typ_new AS OBJECT ( street_address VARCHAR2(40) , postal_code VARCHAR2(10) , city VARCHAR2(30) , state_province VARCHAR2(10) , country_id CHAR(2) ); / CREATE TABLE address_table OF cust_address_typ_new; CREATE TABLE customer_addresses ( add_id NUMBER, address REF cust_address_typ_new SCOPE IS address_table);

Referencias más restricción de integridad referencial CREATE TABLE customer_addresses ( add_id NUMBER, address REF cust_address_typ REFERENCES address_table);

Insertando referencias INSERT INTO address_table VALUES ('1 First', 'G45 EU8', 'Paris', 'CA', 'US'); INSERT INTO customer_addresses SELECT 999, REF(a) FROM address_table a;

Consultando Referencias SELECT address FROM customer_addresses; ADDRESS ---------------------------------------------- 000022020876B2245DBE325C5FE03400400B40DCB176B2245DBE305C5FE03400400B40DCB1 SELECT DEREF(address) FROM customer_addresses; DEREF(ADDRESS)(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID) ----------------------------------------------- CUST_ADDRESS_TYP('1 First', 'G45 EU8', 'Paris','CA', 'US')

Colecciones • Set • Multiset • Varray

Multiset/Array SQL99 create type Publisher as ( namevarchar(20),branch varchar(20) create type Book as( title varchar(20),author-array varchar(20) array [10], pub-date date,publisher Publisher, keyword-set varchar(20) multiset ) create table books ofBook

Nested Table/Varray Oracle create type publisher_t as object( name varchar(20), branch varchar(20)); CREATE TYPE keyword_tAS TABLE OF varchar(20); CREATE TYPE authorarray_tas varray (10) of varchar(20); create type Book as object( title varchar(20), authorarray authorarray_t, pubdate date, publisher publisher_t, keywordset keyword_t);

Colecciones de Múltiples niveles • Multilevel collection types are collection types whose elements are themselves directly or indirectly another collection type. Possible multilevel collection types are: • Nested table of nested table type • Nested table of varray type • Varray of nested table type • Varray of varray type • Nested table or varray of a user-defined type that has an attribute that is a nested table or varray type

Implementación en Oracle de SQL99