Unidade 4 1 bdor oracle 11g l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 119

Unidade 4.1: BDOR – Oracle 11g PowerPoint PPT Presentation


  • 103 Views
  • Uploaded on
  • Presentation posted in: General

Unidade 4.1: BDOR – Oracle 11g. Banco de Dados II – 2009.2 Prof. Cláudio de Souza Baptista, Ph.D. UFCG/CEEI/DSC. Oracle OR 11g. O ORACLE 11g oferece diferentes tipos de objetos: Tipos de Objetos ( TADs ) Nested Tables (Tabelas aninhadas) VArrays ( Varying Arrays )

Download Presentation

Unidade 4.1: BDOR – Oracle 11g

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Unidade 4 1 bdor oracle 11g l.jpg

Unidade 4.1: BDOR – Oracle 11g

Banco de Dados II – 2009.2

Prof. Cláudio de Souza Baptista, Ph.D.

UFCG/CEEI/DSC


Oracle or 11g l.jpg

Oracle OR 11g

  • O ORACLE 11g oferece diferentes tipos de objetos:

    • Tipos de Objetos (TADs)

    • NestedTables (Tabelas aninhadas)

    • VArrays (VaryingArrays)

    • LargeObjects (LOBs)

    • References (REF)

    • ObjectView (Visão de Objetos)

  • No entanto, háalgumasdiferenças com o padrã SQL:1999


Oracle or 11g3 l.jpg

Oracle OR 11g

  • Conceitos Básicos

    • Tipo de Objeto

    • Método

    • Evolução de Tipo

    • Herança de Tipo

    • Tabela de Objeto

    • Objeto de Linha e Objeto de Coluna

    • Referência de Objeto

    • Coleção de Objetos


Slide4 l.jpg

Relacional.Objeto-relacional - Um banco de dados, tradicionalmente relacional, estendido para incluir os conceitos OO e estruturas como tipos de dados abstratos, nested tables e varying arrays.Orientado a objetos- Um banco de dados orientado a objetos cujo projeto é, desde o seu início, desenvolvido com análise orientada a objetos.

Oracle OR 11g

O ORACLE 11g fornece um suporte completo para todos os três diferentes tipos de implementação:


Slide5 l.jpg

Exemplo - UML


Slide6 l.jpg

Projeto BD Relacional

Esquema Relacional


Slide7 l.jpg

Esquema

Objeto

Relacional


Slide8 l.jpg

Definindo os Tipos


Tipos de objetos object types l.jpg

Tipos de Objetos (Object Types)

  • Tipo de objeto é um tipo abstrato de dados (TAD), ou seja um StructuredType em SQL:1999

  • TAD é um tipo de dado definido pelo usuário que encapsula propriedades (atributos) e comportamento (métodos)

  • Corresponde ao “Molde” de um objeto

  • Não aloca espaço de armazenamento

  • Não pode armazenar dados


Slide10 l.jpg

Tipos de Objetos

  • Um Tipo de Objeto é um esquema de objeto com 3 componentes:

    • Nome

    • Atributos

    • Métodos

  • Um tipo de objeto pode ser usado para:

    • Definir o domínio de atributos (“column object”) de tabelas

    • Definir o tipo dos atributos de TADs ( “embedded object”)

    • Criar uma tabela de objetos


Tipos de objetos l.jpg

Tipos de Objetos

  • Um tipo de objeto em Oracle possui a seguinte estrutura:


Tipos de objetos12 l.jpg

Tipos de Objetos

  • Exemplo de especificação da interface pública de um objeto

  • Sintaxe resumida:

    CREATE [OR REPLACE] TYPE nome_tipo AS OBJECT (

    [lista de atributos]

    [lista de métodos]

    );


Tipos de objetos13 l.jpg

Tipos de Objetos

Pode ser usado da mesma forma que é usado um tipo primitivo

EX: Para definir o tipo de um atributo de uma tabela

CREATE TABLE tb_contatos (

contato tp_pessoa,

dt_contatoDATE );

CREATE TABLE tb_domicilio (

local tp_ponto,

enderecoVARCHAR2 (80) );

-- Para definir o tipo de um atributo de um TAD

CREATE TYPE tp_contatos AS OBJECT (

contato tp_ pessoa,

dt_contatoDATE );

CREATE TYPE tp_domicilio AS OBJECT (

local tp_ponto,

enderecoVARCHAR2 (80) );


Tipos de objetos14 l.jpg

Tipos de objetos

create type ENDERECO_TYP as object(rua VARCHAR2(50), cidade VARCHAR2(25), estado CHAR(2), cep NUMBER);

create table PESSOAS (nome VARCHAR2(25), endereço ENDERECO_TYP);

ENDEREÇO_TYP é usado para definir o tipo (domínio) da coluna Endereço da tabela PESSOAS


Tipos de objetos15 l.jpg

Tipos de objetos

create type ENDERECO_TYP as object(rua VARCHAR2(50), cidade VARCHAR2(25), estado CHAR(2), cep NUMBER);

ENDEREÇO_TYP é usado para definir o tipo do atributo Endereco do tipo PESSOA_TYP

create type PESSOA_TYP as object(nome VARCHAR2(25), endereco ENDERECO_TYP);

Não é possível ocorrer uma inserção de dados em PESSOA_TYP. Isso porque um tipo de objeto descreve dados, mas não os armazena.


Tipos de objetos16 l.jpg

Tipos de objetos

Para armazenar dados é necessário a criação de uma tabela a partir de um tipo de objeto.

create type PESSOA_TY as object(Nome VARCHAR2(25), CPF NUMBER,

Endereco ENDERECO_TY);

create table PESSOAS of PESSOA_TY (CPF primary key );

A tabela PESSOAS irá armazenar dados com a estrutura do tipo PESSOA_TY


Exemplo l.jpg

Exemplo

CREATE TYPE person_typ AS OBJECT (

idno NUMBER,

first_name VARCHAR2(20),

last_name VARCHAR2(25),

email VARCHAR2(25),

phone VARCHAR2(20),

MAP MEMBER FUNCTION get_idno RETURN NUMBER,

MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ));


Exemplo cont l.jpg

Exemplo (cont.)

CREATE TYPE BODY person_typ AS

MAP MEMBER FUNCTION get_idno RETURN NUMBER IS

BEGIN

RETURN idno;

END;

MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) IS

BEGIN

-- use the PUT_LINE procedure of the DBMS_OUTPUT package to display details

DBMS_OUTPUT.PUT_LINE(TO_CHAR(idno) || ' ' ||first_name || ' ' ||last_name);

DBMS_OUTPUT.PUT_LINE(email || ' ' ||phone);

END;

END;


Inserindo dados l.jpg

Inserindo dados

  • Ex.:

    CREATE TABLE contacts (

    contact person_typ,

    contact_date DATE );

    INSERT INTO contacts VALUES (

    person_typ (65, 'Verna', 'Mills', [email protected]', '1-800-555-4412'), '24 Jun 2003' );


Inserindo com null l.jpg

Inserindo com NULL

CREATE TABLE contacts (

contact person_typ,

contact_date DATE );

INSERT INTO contacts VALUES (person_typ (NULL, NULL, NULL), '24 Jun 2003' );

INSERT INTO contacts VALUES (NULL, '24 Jun 2003' );


Constraints l.jpg

Constraints

CREATE TABLE department_mgrs (

dept_no NUMBER PRIMARY KEY,

dept_name CHAR(20),

dept_mgrperson_typ,

dept_loclocation_typ,

CONSTRAINT dept_loc_cons1

UNIQUE (dept_loc.building_no, dept_loc.city),

CONSTRAINT dept_loc_cons2 CHECK (dept_loc.city IS NOT NULL) );

INSERT INTO department_mgrs VALUES

( 101, 'PhysicalSciences',

person_typ(65,'Vrinda Mills', '1-800-555-4412'),

location_typ(300, 'Palo Alto'));


Trigger em objetos tipados l.jpg

Trigger em Objetos Tipados

CREATE TABLE movement (

idno NUMBER,

old_officelocation_typ,

new_officelocation_typ );

CREATE TRIGGER trigger1

BEFORE UPDATE OF office_loc ON office_tab

FOR EACH ROW

WHEN (new.office_loc.city = 'RedwoodShores')

BEGIN

IF :new.office_loc.building_no = 600 THEN

INSERT INTO movement (idno, old_office, new_office)

VALUES (:old.occupant.idno, :old.office_loc, :new.office_loc);

END IF;

END;

OBS.: Não se podemodificarcolunas do tipo LOB no corpo de um trigger!


M todos l.jpg

Métodos

  • São funções ou procedimentos que são declarados na definição de um tipo de objeto

  • Exigem o uso de parênteses (mesmo sem parâmetros)

  • O uso de ( ) é para diferenciar o método de um procedimento ou função comum

  • Podem ser

    • MEMBER ou STATIC

    • MAP ou ORDER (para ordenação)

    • Construtor


M todos24 l.jpg

Métodos

  • Um object type sempre possui um construtor, pode possuir zero ou mais métodos membro e pode possuir um método map ou um método order, porém não os dois

  • Ex.: Chamada de método

    SELECT c.contact.get_idno()

    FROM contacts c;


M todos25 l.jpg

Métodos

  • MEMBER

    • São os métodos mais comuns

    • Implementam as operações das instâncias do tipo

    • São invocados através da qualificação de objeto objeto.método()

    • SELF não precisa ser declarado, mas pode ser e deverá ser sempre o primeiro parâmetro


Ex member method l.jpg

Ex. Member method

CREATE TYPE solid_typ AS OBJECT (

len INTEGER,

wth INTEGER,

hgt INTEGER,

MEMBER FUNCTION surface RETURN INTEGER,

MEMBER FUNCTION volume RETURN INTEGER,

MEMBER PROCEDURE display (SELF IN OUT NOCOPY solid_typ) );

CREATE TYPE BODY solid_typ AS

MEMBER FUNCTION volume RETURN INTEGER IS

BEGIN

RETURN len * wth * hgt;

-- RETURN SELF.len * SELF.wth * SELF.hgt; -- equivalent to previous line

END;

MEMBER FUNCTION surface RETURN INTEGER IS

BEGIN -- not necessary to include SELF prefix in following line

RETURN 2 * (len * wth + len * hgt + wth * hgt);

END;

MEMBER PROCEDURE display (SELF IN OUT NOCOPY solid_typ) IS

BEGIN

DBMS_OUTPUT.PUT_LINE('Length: ' ||len || ' - ' || 'Width: ' ||wth || ' - ' || 'Height: ' ||hgt);

DBMS_OUTPUT.PUT_LINE('Volume: ' || volume || ' - ' || 'Surface area: ‘ ||surface);

END;

END;


M todos27 l.jpg

Métodos

  • Construtor

    • Criado implicitamente ao criar um tipo de objeto

    • Deve ser exatamente igual ao nome do tipo

    • Pode haver mais de um construtor

    • EX:

      INSERT INTO tb_contatos VALUES (

      Person_typ(65, ‘Pedro', ‘Medeiros', [email protected]', '83-3337-3333'), ‘12 Mai 2005' );


Static method l.jpg

Static Method

  • São invocados nos tipos de dados, não nas instâncias

  • Usados para operações que são globais ao tipo e não precisam se reportar a uma instância particular

  • Não possui parâmetro SELF

  • Invocado da seguinte forma: type_name.method()


Static method exemplo l.jpg

Static Method - Exemplo

CREATE TYPE atype AS OBJECT(

a1 NUMBER,

STATIC PROCEDURE newa ( p1 NUMBER, tabname VARCHAR2,

schname VARCHAR2));

CREATE TYPE BODY atype AS

STATIC PROCEDURE newa (p1 NUMBER, tabname VARCHAR2, schname VARCHAR2) IS

sqlstmt VARCHAR2(100);

BEGIN

sqlstmt := 'INSERT INTO '||schname||'.'||tabname|| ' VALUES (atype(:1))';

EXECUTE IMMEDIATE sqlstmt USING p1;

END;

END;

CREATE TABLE atab OF atype;

BEGIN

atype.newa(1, 'atab', 'HR');

END;


M todos para compara o de objetos l.jpg

Métodos para Comparação de Objetos

MAP ou ORDER

  • São funções opcionais para comparar objetos

  • São mutuamente exclusivos!

  • Por default o ORACLE implementa a comparação STATE do SQL:1999 (queindica se um objeto é igualounão de outrobaseadonacomparação de cadaatributo)

  • ORDER

    • Implementa o RELATIVE WITH do SQL:1999, retornandonegativo, zero oupositivo

    • exige como parâmetro um obj. do mesmo tipo

    • ORDER compara o obj. corrente com o obj. do parâmetro

  • MAP

    • Implementa MAP do SQL:1999, retornando um valor de tipo built-in

    • não exige parâmetro

    • MAP compara vários objetos (ex: ORDER BY)


M todos para compara o de objetos31 l.jpg

Métodos para Comparação de Objetos

Exemplo de MAP

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;


M todos para compara o de objetos32 l.jpg

Métodos para Comparação de Objetos

Exemplo de ORDER

CREATE TYPE location_typ AS OBJECT (

building_no NUMBER,

city VARCHAR2(40),

ORDER MEMBER FUNCTION match (l location_typ) RETURN INTEGER );

CREATE TYPE BODY location_typ AS

ORDER MEMBER FUNCTION match (l location_typ) RETURN INTEGER IS

BEGIN

IF building_no < l.building_no THEN

RETURN -1; -- qualquernúmeronegativo

ELSIF building_no > l.building_no THEN

RETURN 1; -- qualquernúmeropositivo

ELSERETURN 0;

END IF;

END;

END;


Evolu o de tipos l.jpg

Evolução de Tipos

  • Uso do comandoALTER TYPE, permitemodificar, ouevoluir, um tipoobjeto:

  • Adicionar e remover atributos

  • Adicionar e remover métodos

  • Modificar um atributonuméricoparaaumentar o length, precision, ouscale

  • Modicar um atributo string paraaumentarseu length

  • Mudarpropriedades FINAL e INSTANTIABLE do tipo


Evolu o de tipos34 l.jpg

Evolução de Tipos

  • EX:

    ALTER TYPE person_typ

    ADD ATTRIBUTE ( sexVARCHAR2(1) ) CASCADE ;

    ALTER TYPE person_typ

    DROP MAP MEMBER FUNCTION get_id RETURN NUMBER;

    ALTER TYPE person_typNOT FINAL CASCADE;

    ALTER TYPE person_typ

    MODIFY ATTRIBUTE sexVARCHAR2(10) CASCADE ;

  • OBS.:

    • CASCADE: propaga a mudança para todos os tipos dependentes


Heran a de tipos l.jpg

Herança de Tipos

  • Suportaherança simples

  • Háumadiferença do padrão SQL:1999, pois o Oracle nãorequerherançaexplicitamentenastabelas, masapenasnostipos => modelomais simples, emboramenosflexível

  • Permite criar uma hierarquia de sub-tipos especializados

  • Os tipos derivados (sub-tipos) herdam os atributos e métodos dos tipos ancestrais (super-tipos)

  • Os sub-tipos podem acrescentar novos atributos ou métodos e/ou redefinir os métodos dos super-tipos


Heran a de tipos36 l.jpg

Herança de Tipos

CREATE TYPE employee_typUNDER person_typ(

depto_idNUMBER,

funcaoVARCHAR2(30),

salarioNUMBER

) NOT FINAL;

  • OBS.: Por default um tipo de objeto é FINAL!


Heran a de tipos drop type l.jpg

Herança de Tipos – Drop Type

  • Não se pode remover um subtipo antes de remover suasrespectivasinstânciasnatabelaquearmazena as tuplasdaquelesubtipo (substitutability)

  • Ex.: Drop Type incorreto

    DROP TYPE student_typ VALIDATE

    Corretoseria:

    DELETE FROM person_obj_table WHERE p IS OF (student_typ);

    DROP TYPE student_typ VALIDATE;


Heran a de tipos38 l.jpg

Herança de Tipos

CREATE TYPE person_typ AS OBJECT (

idno NUMBER,

name VARCHAR2(30),

phone VARCHAR2(20),

FINAL MAP MEMBER FUNCTION get_idno RETURN NUMBER)

NOT FINAL;


Heran a de tipos39 l.jpg

Herança de Tipos

CREATE TYPE professor_typ

UNDER person_typ (

dept_id NUMBER,

speciality VARCHAR2(30))

NOT FINAL;


Overriding method l.jpg

Overriding Method

CREATE TYPE person_typ AS OBJECT (

idno NUMBER,

name VARCHAR2(30),

phone VARCHAR2(20),

MAP MEMBER FUNCTION get_idno RETURN NUMBER,

STATIC FUNCTION show_super (person_obj in person_typ) RETURN VARCHAR2,

MEMBER FUNCTION show RETURN VARCHAR2)

NOT FINAL;


Overriding method cont l.jpg

Overriding Method (cont.)

CREATE TYPE BODY person_typ AS

MAP MEMBER FUNCTION get_idno RETURN NUMBER IS

BEGIN

RETURN idno;

END;

-- static function that can be called by subtypes

STATIC FUNCTION show_super (person_obj in person_typ) RETURN VARCHAR2 IS

BEGIN

RETURN 'Id: ' || TO_CHAR(person_obj.idno) || ', Name: ' || person_obj.name;

END;

-- function that can be overriden by subtypes

MEMBER FUNCTION show RETURN VARCHAR2 IS

BEGIN

RETURN person_typ.show_super ( SELF );

END;

END;


Overriding method cont42 l.jpg

Overriding Method (cont.)

CREATE TYPE student_typ UNDER person_typ ( dept_id NUMBER, major VARCHAR2(30),

OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2)

NOT FINAL;

CREATE TYPE BODY student_typ AS

OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2 IS

BEGIN

RETURN person_typ.show_super ( SELF ) || ' -- Major: ' || major ;

END;

END;


Overriding method cont43 l.jpg

Overriding Method (cont.)

CREATE TYPE employee_typ UNDER person_typ (

emp_id NUMBER,

mgr VARCHAR2(30),

OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2);

CREATE TYPE BODY employee_typ AS

OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2 IS

BEGIN

RETURN person_typ.show_super ( SELF ) || ' -- Employee Id: '

|| TO_CHAR(emp_id) || ', Manager: ' || mgr ;

END;

END;


Overriding method cont44 l.jpg

Overriding Method (cont.)

CREATE TYPE part_time_student_typ UNDER student_typ (

number_hours NUMBER,

OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2);

CREATE TYPE BODY part_time_student_typ AS

OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2 IS

BEGIN

RETURN person_typ.show_super ( SELF ) || ' -- Major: ' || major || ', Hours: ' || TO_CHAR(number_hours);

END;

END;


Overriding method cont45 l.jpg

Overriding Method (cont.)

CREATE TABLE person_obj_table OF person_typ;

INSERT INTO person_obj_table

VALUES (person_typ(12, 'Bob Jones', '111-555-1212'));

INSERT INTO person_obj_table

VALUES (student_typ(51, 'Joe Lane', '1-800-555-1312', 12, 'HISTORY'));

INSERT INTO person_obj_table

VALUES (employee_typ(55, 'Jane Smith', '1-800-555-7765',

100, 'Jennifer Nelson'));

INSERT INTO person_obj_table

VALUES (part_time_student_typ(52, 'Kim Patel', '1-800-555-1232', 14, 'PHYSICS', 20));


Overriding method cont46 l.jpg

Overriding Method (cont.)

SELECT p.show() FROM person_obj_table p;

Resultado:

Id: 12, Name: Bob Jones

Id: 51, Name: Joe Lane -- Major: HISTORY

Id: 55, Name: Jane Smith -- Employee Id: 100, Manager: Jennifer Nelson

Id: 52, Name: Kim Patel -- Major: PHYSICS, Hours: 20


Restri es em overriding methods l.jpg

Restrições em Overriding methods

  • Só se pode fazer overriding de métodos declarados NOT FINAL no supertipo

  • Métodos ORDER só podem ser definidos na raiz da hierarquia de tipos, não podendo ser overriden

  • Um método STATIC (MEMBER) num subtipo não poder override um método MEMBER (STATIC) do supertipo

  • Usando PL/SQL não se pode chamar o método do supertipo através da keyword super, mas pode-se invocar um método estático do supertipo


Tabelas no oracle 11g l.jpg

Tabelas no ORACLE 11g

  • Oracle11g suporta 2 tipos de tabelas:

    • Tabela Relacional

    • Tabela de Objetos (Object Table)

      • uma tabela de objetos é um tipo especial de tabela que lida com objetos (“row objects”) e fornece uma visão relacional dos atributos desses objetos.

  • Princípio da Substituição (herança de tipos): uma coluna ou row definidas do tipo t podem conter instâncias de quaisquer de seus subtipos.


Tabela de objetos vs tabela relacional l.jpg

Tabela de objetos vs. Tabela relacional

  • Uma tabela de objetos difere de uma tabela relacional em vários aspectos:

  • Cada linha de uma tabela de objetos possui um identificador de objeto (OID), definido pelo ORACLE quando a linha é inserida na tabela;

  • Um OID é um ponteiro para um objeto “linha” (ROW Object);

  • As linhas ( row objects) de uma tabela de objetos podem ser referenciadas por outros objetos do banco de dados.


Tabela de objetos l.jpg

Tabela de objetos

create type PESSOA_TY as object(Nome VARCHAR2(25), CPF NUMBER,

Endereco ENDERECO_TY);

create table PESSOAS of PESSOA_TY (CPF primary key );

  • A tabela de objetos PESSOAS pode ser vista como:

  • Uma Tabela com uma única coluna:

    • - cada linha é um objeto do tipo PESSOA.

  • Uma Tabela com múltiplas colunas

    • - Uma coluna para cada atributo do tipo PESSOA_TY


Tabelas de objetos l.jpg

Tabelas de Objetos

  • EquivalentesàsTabelasTipadas (Typed Tables) do SQL:1999

  • São tabelas especiais onde cada linha armazena um objeto

  • Provê uma visão relacional desses objetos.

  • As linhas de uma tabela de objetos possuem um OID (objectidentifier) implícito (definido pelo ORACLE)

  • Os objetos de uma tabela de objetos podem ser referenciados (REF) por outros objetos

  • Nos comandos de manipulação de objetos utilizar aliasespara as tabelas


Manipulando tabelas de objetos l.jpg

Manipulando tabelas de objetos

Existem diferenças significativas no modo de utilização de uma tabela de objetos.

Cada linha dentro de uma tabela de objetos possuirá um OID, e essas linhas poderão ser referenciadas como objetos.


Sele o em tabelas l.jpg

Seleção em tabelas

  • Seleção em PESSOAS como uma tabela de múltiplas colunas.

select *

from PESSOAS;

============================================================

NOME CPF ENDERECO(RUA, CIDADE, ESTADO, CEP)

--------------------- ---------------- ----------------------------------------------------------------

Maria Silva 543875432 ENDERECO_TY(‘Rua das Flores 84', ‘Campina Grande', ‘PB', 58102324)


Sele o em tabelas54 l.jpg

Seleção em tabelas

  • Seleção em PESSOAS como uma tabela de uma de uma única coluna.

SELECT VALUE (p)

FROM PESSOAS p

WHERE p.nome = ‘Maria Silva';

Resposta:

VALUE(P)(NOME, CPF, ENDERECO(RUA, CIDADE, ESTADO, CEP))

--------------------------------------------------------------------------------

PESSOA_TY(‘Maria Silva', 543875432, ENDERECO_TY(‘Rua das Flores 84', ‘Campina Grande', ‘PB', 58102324)


Sele o em tabelas55 l.jpg

Seleção em tabelas

SELECT p.CPF

FROM PESSOAS p

WHERE p.nome = ‘Maria Silva';

Resposta:

CPF--------------------------------------------------------------

543875432


Sele o em tabelas56 l.jpg

Seleção em tabelas

SELECT p.endereco

FROM PESSOAS p

WHERE p.nome = ‘Maria Silva';

Resposta:

ENDERECO(RUA, CIDADE, ESTADO, CEP)

-------------------------------------------------------------

ENDERECO_TY('rua das Flores 84', ‘Campina Grande', ‘PB', 58102324)


Sele o em tabelas57 l.jpg

Seleção em tabelas

selectp.endereco.cidade

from PESSOAS p

wherep.nome = ‘Maria Silva';

Resposta:

ENDERECO.CIDADE

-------------------------

Campina Grande


Inser o de dados l.jpg

Inserção de dados

CREATE TABLE person_obj_table OF person_typ;

INSERT INTO person_obj_table VALUES (

person_typ(101, 'John', 'Smith', [email protected]', '1-800-555-1212') );

SELECT VALUE(p) FROM person_obj_table p

WHERE p.last_name = 'Smith';

DECLARE

personperson_typ;

BEGIN -- PL/SQL block for selecting a person and displaying details

SELECT VALUE(p) INTO person FROM person_obj_table p WHERE p.idno= 101;

person.display_details();

END;


Inser o em tabelas de objetos l.jpg

Inserção em tabelas de Objetos

  • Inserção em PESSOAS como uma tabela de uma única coluna.

  • - Usa o método construtor PESSOA_TY que constrói novos objetos do tipo PESSOA_TY.

INSERT INTO PESSOAS VALUES

(PESSOA_TY(‘Maria Silva', 543875432,

ENDERECO_TY(‘Rua das Flores 84', ‘Campina Grande', ‘PB', 58102324))

Métodos construtores para os tipos PESSOA_TY e ENDERECO_TY. O nome do método construtor tem o mesmo nome do tipo.


Inser o usando substitutability l.jpg

Inserção usando substitutability

CREATE TABLE contacts (

contact person_typ,

contact_date DATE );

INSERT INTO contacts

VALUES (person_typ (12, 'Bob Jones', '111-555-1212'), '24 Jun 2003' );

INSERT INTO contacts

VALUES (student_typ(51, 'Joe Lane', '1-800-555-1312', 12, 'HISTORY'), '24 Jun 2003' );

INSERT INTO contacts

VALUES (part_time_student_typ(52, 'Kim Patel', '1-800-555-1232', 14, 'PHYSICS', 20), '24 Jun 2003' );


Desligando a substitutability l.jpg

Desligando a substitutability

CREATE TYPE office_typ AS OBJECT (

office_id VARCHAR(10),

locationlocation_typ,

occupantperson_typ ) NOT FINAL;

CREATE TABLE dept_office (

dept_no NUMBER,

officeoffice_typ)

COLUMN office NOT SUBSTITUTABLE AT ALL LEVELS;

CREATE TABLE dept_office (

dept_no NUMBER,

officeoffice_typ)

COLUMN office NOT SUBSTITUTABLE AT ALL LEVELS;

CREATE TABLE office_tab OF office_typ

COLUMN occupant NOT SUBSTITUTABLE AT ALL LEVELS;


Desligando a substitutability62 l.jpg

Desligando a substitutability

  • Uma alternativa à cláusula: NOT SUBSTITUTABLE AT ALL LEVELS é usar o operador IS OF type, que especifica qual subtipo pode ser instanciado.

    CREATE TABLE office_tab OF office_typ

    COLUMN occupant IS OF (ONLY employee_typ);


Atualiza o e remo o em tabelas de objetos l.jpg

Atualização e Remoção em tabelas de Objetos

update PESSOAS p

Set p.endereco= ENDERECO_TY(‘Rua das Margaridas 22',

‘Patos', ‘PB', 58453230)

wherep.nome = ‘Maria Silva';

Delete from PESSOAS p

wherep.nome = ‘Maria Silva';


Identificadores de objetos l.jpg

Identificadores de objetos

  • Uma tabela de objetos contém uma coluna gerada pelo SGBD contendo o OID do “row object”. O oid de um objeto é único e imutável.

  • Sobre essa coluna de OID é também criado automaticamente um índice para prover acesso eficiente sobre o objeto através do OID. A coluna de OID é equivalente a se ter uma coluna extra de 16 bytes para chave primária.

  • Um OID permite que um “row object” seja referenciado em atributos de outros objetos ou em colunas de tabelas relacionais.

  • Um tipo pré-definido REF é capaz de representar tais referências.


Referenciando objetos ref l.jpg

Referenciando Objetos (REF)

  • É um ponteiro lógico para um “Row Object”

  • Usado para fazer referência

  • É definido a partir do OID do objeto

  • Oferece acesso rápido/direto

  • Não garante integridade referencial, tem que usar referential constraint, neste caso REF pode apontar para qualquer objeto do tipo apontado


Slide66 l.jpg

REF

CREATE TYPE emp_person_typ AS OBJECT (

nameVARCHAR2(30),

manager REF emp_person_typ );

CREATE TABLE emp_person_obj_table OF emp_person_typ;

INSERT INTO emp_person_obj_table VALUES (

emp_person_typ('John Smith', NULL));

INSERT INTO emp_person_obj_table

SELECT emp_person_typ ('Bob Jones', REF(e))

FROM emp_person_obj_table e

WHERE e.name = 'John Smith';


Slide67 l.jpg

REF

CREATE OR REPLACE TYPE tp_cliente as OBJECT(

cod_cliVARCHAR (3),

nome_cliVARCHAR (60));

CREATE TABLE tb_cliente OF tp_cliente(

cod_cliPRIMARY KEY,

nome_cliNOT NULL);

CREATE OR REPLACE TYPE tp_dependente as OBJECT(

cod_depVARCHAR(3),

nm_depVARCHAR (60),

ref_titularREF tp_cliente);

CREATE TABLE tb_dependente OF tp_dependente(

cod_depPRIMARY KEY,

nm_depNOT NULL,

ref_titularSCOPE IS tb_cliente);


Slide68 l.jpg

REF

INSERT INTO tb_cliente VALUES (‘C1’, ‘Rita’);

INSERT INTO tb_cliente VALUES (‘C2’, ‘Ana’);

INSERT INTO tb_dependente

SELECT ‘D1’, ‘Paulo’, REF (C)

FROM tb_cliente C WHERE cod_cli = ‘C1’;

INSERT INTO tb_dependente

SELECT ‘D2’, ‘Pedro’, REF (C)

FROM tb_cliente C WHERE cod_cli = ‘C2’;


Slide69 l.jpg

REF

  • SELECT * FROM tb_dependente D;

    COD_DEP NM_DEP REF_TITULAR

    -----------------------------------------

    D1 Paulo Q2459QW8RNDGS0D98G765SF

    D2 Pedro 5XBGVX3B75XCN490VM0VBX4


Slide70 l.jpg

REF

SELECT REF(D)

FROM tb_dependente D

WHERE D. nm_dep = ‘Paulo’;

REF(D)

-----------------------

HRD23K56RNDGS0DUY6TGDE4


Slide71 l.jpg

REF

SELECT D.ref_titular.cod_clicod_cliente,

D.ref_titular.nm_clinm_cliente,

D.nm_depnm_dependente

FROM tb_dependenteD;

COD_CLIENTE NM_CLIENTE NM_DEPENDENTE

---------------------------------------------------------------

C1 Rita Paulo

C2 Ana Pedro


Dangling ref l.jpg

Dangling REF

  • Verificando a validade das referências (Dangling)

  • EX:

    DELETE FROM tb_cliente WHERE cod_cli = ‘C1’;

  • Remove o objeto Rita

    SELECT D.ref_titular.cod_clicod_cliente,

    D.ref_titular.nm_clinm_cliente,

    D.nm_depnm_dependente

    FROM tb_dependente D;

    COD_CLIENTE NM_CLIENTE NM_DEPENDENTE

    ---------------------------------------------------------------

    Paulo

    C2 Ana Pedro

    OBS.: O objeto Rita não é listado, mas Paulo aindaexiste!!!!


Dangling ref73 l.jpg

Dangling REF

  • Verificando a validade das referências (Dangling)

    SELECT D.ref_titular.cod_clicod_cliente,

    D.ref_titular.nm_clinm_cliente,

    D.nm_depnm_dependente

    FROM tb_dependente D

    WHERE D.ref_titularIS DANGLING;

    COD_CLIENTE NM_CLIENTE NM_DEPENDENTE

    --------------------------------------------------------------

    Paulo


Dangling ref74 l.jpg

Dangling REF

  • Verificando a validade das referências (Dangling)

    SELECT D.ref_titular.cod_clicod_cliente,

    D.ref_titular.nm_clinm_cliente,

    D.nm_depnm_dependente

    FROM tb_dependente D

    WHERE D.ref_titularIS NOT DANGLING;

    COD_CLIENTE NM_CLIENTE NM_DEPENDENTE

    ----------------------------------------------------------------

    C2 Ana Pedro


Dangling null l.jpg

Dangling != NULL

SELECT D.ref_titular.cod_clicod_cliente,

D.ref_titular.nm_clinm_cliente,

D.nm_depnm_dependente

FROM tb_dependente D

WHERE D.ref_titularIS NOT NULL;

COD_CLIENTE NM_CLIENTE NM_DEPENDENTE

--------------------------------------------------------------

Paulo

C2 Ana Pedro


Scoped ref l.jpg

SCOPED REF

CREATE TABLE contacts_ref (

contact_refREF person_typ SCOPE IS person_obj_table,

contact_dateDATE );

  • Para inserirumalinhanatabela:

    INSERT INTO contacts_ref

    SELECT REF(p), '26 Jun 2003‘

    FROM person_obj_table p

    WHERE p.idno = 101;


Integridade referencial em ref l.jpg

Integridade Referencial em REF

  • Utiliza-se uma sintaxe semelhante ao FOREIGN KEY do modelo relacional

  • Ex.:

    FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab)

    No exemplo acima o escopo é dado implicitamente!

  • PRIMARY KEY não pode ser especificado para uma coluna REF


Deref l.jpg

DEREF

  • O operador DEREF “desfaz” o REF

  • Retorna um objeto referenciado por uma coluna do tipo REF

  • Desreferenciar um objeto dangling retorna um objeto null


Deref79 l.jpg

DEREF

  • Ex.:

    SELECT DEREF(D.ref_titular) deref_titular,

    D.nm_depnm_dependente

    FROM tb_dependente D;

    DEREF_TITULAR(COD_CLI, NM_CLI) NM_DEPENDENTE

    ------------------------------------------------------------------------

    TP_CLIENTE('C1', 'Rita') Paulo

    TP_CLIENTE('C2', 'Ana') Pedro


Deref80 l.jpg

DEREF

  • EX. (sem usar DEREF):

    SELECT D.ref_titularsem_deref,

    D.nm_depnm_dependente

    FROM tb_dependente D;

    SEM_DEREF NM_DEPENDENTE

    ------------------------------------------------------------------------

    P07XZC8V6Z0F97X6VZ965X6VZ4X8VXCVB6Z Paulo

    XCVU6CHBD967B436CB74B5X9B2BX2VQ4WFF Pedro


Value l.jpg

VALUE

  • Exibe os dados das instâncias dos objetos

  • Usa o mesmo formato que DEREF

  • EX:

    SELECT VALUE(D) value_titular

    FROM tb_dependente D;

    VALUE_TITULAR (COD_DEP, NM_DEP, REF_TITULAR)

    -----------------------------------------------------

    TP_DEPENDENTE('D1', 'Paulo', Q2459QW8RNDGS0D98G765SF)

    TP_DEPENDENTE('D2', 'Pedro', 5XBGVX3B75XCN490VM0VBX4)


Referenciando objetos l.jpg

Referenciando Objetos

EMPREGADO_TY

DEPARTAMENTO_TY

depto

create type DEPARTAMENTO_TY as object(Nome VARCHAR2(25), ... );

create table DEPARTAMENTOS of DEPARTAMENTO_TY( ... );

Os objetos do tipo DEPARTAMENTO_TY podem ser referenciados em colunas de tabelas relacionais ou em atributos de outros objetos.

create table EMPREGADOS(Nome VARCHAR2(25), CPF NUMBER,

depto REF DEPARTAMENTO_TY);


Tipo ref l.jpg

Tipo REF

create type EMPREGADO_TY as object(Nome VARCHAR2(25), CPF NUMBER,

depto REF DEPARTAMENTO_TY);

  • Um objeto do tipo REF encapsula uma referência para um “row object” de um tipo de objeto especificado;

  • O valor de um objeto do tipo REF é um “ponteiro lógico” para um row object.

  • REFs e coleções de REFs são utilizados na modelagem de associações entre objetos. Por ex. o relacionamento entre uma ordem de compra e um cliente

  • REFs constituem um mecanismo simples para navegar entre objetos. Pode-se utilizar a notação estendida de “pontos” para seguir os ponteiros sem a necessidade de junções explícitas


Selecionando dados de um tipo espec fico l.jpg

Selecionando dados de um tipo específico

  • Ex.: Pegasubtipostudent_t e seusrespectivossubtipos

    SELECT VALUE(p)

    FROM person_obj_table p

    WHERE VALUE(p) IS OF (student_typ);

  • Ex.: Pegaapenasinstâncias do subtipo

    SELECT TREAT(VALUE(p) AS student_typ)

    FROM person_obj_table p

    WHERE VALUE(p) IS OF(ONLY student_typ);


Treat l.jpg

TREAT

  • Função que permite tratar um supertipo como subtipo.

  • Ex.: tratar person como student

    SELECT TREAT(VALUE(p) AS student_typ)

    FROM person_obj_table p;

    Obs.: retorna NULL quando a instância não é do subtipo especificado, por exemplo, a pessoa não é estudante.


Cole es l.jpg

Coleções

  • Oracle dásuporte a:

    • VARRAY: coleçãoordenada de elementos, com númerofixo de elementos, se quisermanipular a coleçãotoda de umavez

    • Nested tables: quando se precisarodarconsultaseficientesemcoleções, manipular um númeroarbitrário de elementosouexecutarváriasoperações de insert, update, ou delete.


Varrays l.jpg

VARRAYS

  • É um conjunto ordenado de elementos

  • Todos os elementos do VARRAY são do mesmo tipo de dados (ou subtipo)

  • Cada elemento tem um índice, que representa sua posição no array, e é usado para acessar um dado elemento

  • Possui número máximo de elementos, chamado de tamanho do array (que pode ser alterado depois)


Varrays88 l.jpg

VARRAYS

  • Ex.1 . :

    CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);

  • Ex 2:

    CREATE TYPE phone_typ AS OBJECT (

    country_code VARCHAR2(2),

    area_code VARCHAR2(3),

    ph_number VARCHAR2(7));

    CREATE TYPE phone_varray_typ AS VARRAY(5) OF phone_typ;

    CREATE TABLE dept_phone_list (

    dept_no NUMBER(5),

    phone_listphone_varray_typ);

    INSERT INTO dept_phone_list VALUES (

    100,

    phone_varray_typ( phone_typ ('01', '650', '5061111'),

    phone_typ ('01', '650', '5062222'),

    phone_typ ('01', '650', '5062525')));


Varray l.jpg

VARRAY

  • Modificando o tamanho de um elemento do varray

    CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);

    ALTER TYPE email_list_arr MODIFY ELEMENT TYPE VARCHAR2(100) CASCADE;

    CREATE TYPE email_list_tab AS TABLE OF VARCHAR2(30);

    ALTER TYPE email_list_tab MODIFY ELEMENT TYPE VARCHAR2(40) CASCADE;


Varray90 l.jpg

VARRAY

  • Modificando o tamanho do Varray

    CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);

    CREATE TYPE email_list_typ AS OBJECT (

    section_no NUMBER,

    emails email_list_arr);

    CREATE TYPE email_varray_typ AS VARRAY(5) OF email_list_typ;

    ALTER TYPE email_varray_typ MODIFY LIMIT 100


Nested table l.jpg

Nested Table

  • É um conjunto não ordenado de elementos, cada um do mesmo tipo de dados

  • Não há número máximo de elementos e a ordem não é preservada

  • Inserção, remoção, seleção e update como em tabelas normais


Nested table exemplo l.jpg

Nested Table (exemplo)

CREATE TYPE people_typ AS TABLE OF person_typ;

CREATE TABLE people_tab (

group_no NUMBER,

people_columnpeople_typ )

NESTED TABLE people_column STORE AS people_column_nt;

INSERT INTO people_tab VALUES (

100,

people_typ( person_typ(1, 'John Smith', '1-800-555-1212'),

person_typ(2, 'Diane Smith', NULL)));


Nested table exemplo93 l.jpg

Nested Table (exemplo)

CREATE TABLE students (

graduation DATE,

math_majorspeople_typ,

chem_majorspeople_typ,

physics_majorspeople_typ)

NESTED TABLE math_majors STORE AS math_majors_nt

NESTED TABLE chem_majors STORE AS chem_majors_nt

NESTED TABLE physics_majors STORE AS physics_majors_nt;

CREATE INDEX math_idno_idx ON math_majors_nt(idno);

CREATE INDEX chem_idno_idx ON chem_majors_nt(idno);

CREATE INDEX physics_idno_idx ON physics_majors_nt(idno);

INSERT INTO students (graduation) VALUES ('01-JUN-03');

UPDATE students

SET math_majors =

people_typ (person_typ(12, 'Bob Jones', '111-555-1212'),

person_typ(31, 'Sarah Chen', '111-555-2212'),

person_typ(45, 'Chris Woods', '111-555-1213')),

chem_majors =

people_typ (person_typ(51, 'Joe Lane', '111-555-1312'),

person_typ(31, 'Sarah Chen', '111-555-2212'),

person_typ(52, 'Kim Patel', '111-555-1232')),

physics_majors =

people_typ (person_typ(12, 'Bob Jones', '111-555-1212'),

person_typ(45, 'Chris Woods', '111-555-1213'))

WHERE graduation = '01-JUN-03';


Cole es multi dimensionais l.jpg

Coleções Multi-dimensionais

  • Podemoster:

    • Nested table de nested table type

    • Nested table de varray type

    • Varray de nested table type

    • Varray de varraytype

    • Nested table ouvarray de um UDT que tem um atributoque é uma nested table ou varraytype


Cole es multi dimensionais95 l.jpg

Coleções Multi-dimensionais

CREATE TYPE location_typ AS OBJECT (

location_id NUMBER(4),

street_address VARCHAR2(40),

postal_code VARCHAR2(12),

city VARCHAR2(30),

state_province VARCHAR2(25));

CREATE TYPE nt_location_typ AS TABLE OF location_typ;

CREATE TYPE country_typ AS OBJECT (

country_id CHAR(2),

country_name VARCHAR2(40),

locationsnt_location_typ);

CREATE TYPE nt_country_typ AS TABLE OF country_typ;

CREATE TABLE region_tab (

region_id NUMBER,

region_name VARCHAR2(25),

countries nt_country_typ)

NESTED TABLE countries STORE AS nt_countries_tab

(NESTED TABLE locations STORE AS nt_locations_tab);


Cole es multi dimensionais96 l.jpg

Coleções Multi-dimensionais

INSERT INTO region_tab

VALUES(1, 'Europe', nt_country_typ(

country_typ( 'IT', 'Italy', nt_location_typ (

location_typ(1000, '1297 Via Cola di Rie','00989','Roma', ''),

location_typ(1100, '93091 Calle della Testa','10934','Venice','') )),

country_typ( 'CH', 'Switzerland', nt_location_typ (

location_typ(2900, '20 RuedesCorps-Saints', '1730', 'Geneva', 'Geneve'),

location_typ(3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE') )),

country_typ( 'UK', 'United Kingdom', nt_location_typ (

location_typ(2400, '8204 Arthur St', '', 'London', 'London'),

location_typ(2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford'),

location_typ(2600, '9702 Chester Road', '09629850293', 'Stretford',

'Manchester') )

)

)

);


Opera es em cole es l.jpg

Operações em coleções

  • Existem duas formas de se realizar uma consulta em colunas com coleções:

    • Uma delas traz as coleções aninhadas dentro das linhas que as contêm

    • A outra forma, desaninha (unnest) as coleções de forma que cada elemento da coleção aparecerá numa linha do resultado


Opera es em cole es98 l.jpg

Operações em coleções

  • Consultas com resultado aninhado:

    SELECT d.dept_emps

    FROM department_persons d;

    DEPT_EMPS(IDNO, NAME, PHONE)

    ------------------------------------------------------------------------------

    PEOPLE_TYP(PERSON_TYP(1, 'John Smith', '1-800-555-1212'),

    PERSON_TYP(2, 'Diane Smith', '1-800-555-1243'))


Opera es em cole es99 l.jpg

Operações em coleções

  • Consultas com resultado desaninhado:

    SELECT e.*

    FROM department_persons d,

    TABLE(d.dept_emps) e;

    IDNO NAME PHONE

    ---------- ------------------------------ ---------------

    1 John Smith 1-800-555-1212

    2 Diane Smith 1-800-555-1243


Opera es em cole es100 l.jpg

Operações em coleções

  • Query que resulta linhas de departamentos que possuem empregados

    SELECT d.dept_no, e.*

    FROM department_persons d, TABLE(d.dept_emps) e;


Opera es em cole es101 l.jpg

Operações em coleções

  • Mesma query anterior mas que mostra também departamentos sem empregados (outer join)

    SELECT d.dept_no, e.*

    FROM department_persons d, TABLE(d.dept_emps) (+) e;


Opera es em cole es102 l.jpg

Operações em coleções

SELECT *

FROM TABLE(

SELECT d.dept_emps

FROM department_persons d

WHERE d.dept_no = 101);

Obs.: a subqueryacimadeveretornarsempre

umacoleção

a projeçãodasubquerydeveconterapenas um item


Consulta em multi cole es l.jpg

Consulta em multi-coleções

Ex11:

SELECT r.region_name, c.country_name, l.location_id

FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l;

Ex2:

SELECT l.location_id, l.city

FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l;


Opera es em cole es104 l.jpg

Operações em coleções

  • Inserção

    INSERT INTO TABLE(SELECT d.dept_emps

    FROM department_persons d

    WHERE d.dept_no = 101)

    VALUES (5, 'Kevin Taylor', '1-800-555-6212');


Opera es em cole es105 l.jpg

Operações em coleções

  • Atualização

    UPDATE TABLE(SELECT d.dept_emps

    FROM department_persons d

    WHERE d.dept_no = 101) e

    SET VALUE(e) = person_typ(5, 'Kevin Taylor', '1-800-555-6233')

    WHERE e.idno = 5;


Opera es em cole es106 l.jpg

Operações em coleções

  • Remoção

    DELETE FROM TABLE(SELECT d.dept_emps

    FROM department_persons d

    WHERE d.dept_no = 101) e

    WHERE e.idno = 5


Object views l.jpg

Object Views

  • São tabelas de objetos (object table) virtuais

  • Cada linha (row) da Object View é um objeto, podendo-se, portanto, invocar seus métodos e acessar seus atributos

  • São úteis no mapeamento de Relacional par a Objetos, pois dá uma “cara” de objetos a uma tabela puramente relacional.


Criando uma object view l.jpg

Criando uma Object View

CREATE TABLE emp_table (

empnum NUMBER (5),

ename VARCHAR2 (20),

salary NUMBER (9,2),

job VARCHAR2 (20));

CREATE TYPE employee_t AS OBJECT (

empno NUMBER (5),

ename VARCHAR2 (20),

salary NUMBER (9,2),

job VARCHAR2 (20));

CREATE VIEW emp_view1 OF employee_t

WITH OBJECT IDENTIFIER (empno) AS

SELECT e.empnum, e.ename, e.salary, e.job

FROM emp_table e

WHERE job = 'Developer';


Criando uma object view ex 2 l.jpg

Criando uma Object View – EX.2

CREATE TABLE dept (

deptno NUMBER PRIMARY KEY,

deptname VARCHAR2(20),

deptstreet VARCHAR2(20),

deptcity VARCHAR2(10),

deptstate CHAR(2),

deptzip VARCHAR2(10));

CREATE TYPE address_t AS OBJECT (

street VARCHAR2(20),

city VARCHAR2(10),

state CHAR(2),

zip VARCHAR2(10));

CREATE TYPE dept_t AS OBJECT (

deptno NUMBER,

deptname VARCHAR2(20),

addressaddress_t );

CREATE VIEW dept_view OF dept_t WITH OBJECT IDENTIFIER (deptno) AS

SELECT d.deptno, d.deptname,

address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip) AS deptaddr

FROM dept d;


Criando uma refer ncia a objetos numa view l.jpg

Criando uma referência a Objetos numa View

CREATE TYPE emp_t AS OBJECT (

empno NUMBER,

ename VARCHAR2(20),

salary NUMBER,

deptref REF dept_t);

CREATE OR REPLACE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(empno)

AS SELECT e.empno, e.empname, e.salary,

MAKE_REF(dept_view, e.deptno)

FROM emp e;


Ex de consulta view anterior l.jpg

Ex. De consulta à view anterior

SELECT e.empno, e.salary, e.deptref.deptno

FROM emp_view e

WHERE e.deptref.address.city = 'San Francisco';


Outra forma de definir a view l.jpg

Outra forma de definir a View

CREATE OR REPLACE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(empno)

AS SELECT e.empno, e.empname, e.salary, REF(d)

FROM emp e, dept_view d

WHERE e.deptno = d.deptno;


Atualizando object views l.jpg

Atualizando Object Views

  • Oracle permite, se nãohouverambiguidade, realizar update, delete e insert em Object Views.

  • Uma View nãopoderá ser diretamenteatualizada se contiver joins, set operators, aggregate functions, GROUP BY ou DISTINCT clauses. Nestescasos, parapoder-se atualizá-la, deve-se usar o INSTEAD OF Trigger

  • Umaremoçãooualteraçãonumasuperviewseráfeitanassubviewstambém a menosque se use o operador ONLY


Atualizando object views114 l.jpg

Atualizando Object Views

  • Ex.: DELETE FROM Person_v; remove de Person_v, Student-V e Employee_v

  • Ex. Usando ONLY:

    UPDATE ONLY(Person_v) SET address = ...


Criando instead of triggers numa view l.jpg

Criando INSTEAD OF Triggers numa View

- Número de empregados um Departamentonãopodeexceder 10

CREATE TRIGGER emp_instr INSTEAD OF INSERT on emp_view

FOR EACH ROW

DECLARE

dept_vardept_t;

emp_countinteger;

BEGIN

-- Enforcetheconstraint

-- First get the department number from the reference

UTL_REF.SELECT_OBJECT(:NEW.deptref, dept_var);

SELECT COUNT(*) INTO emp_count

FROM emp

WHERE deptno = dept_var.deptno;

IF emp_count < 9 THEN

-- Do theinsert

INSERT INTO emp (empno, empname, salary, deptno)

VALUES (:NEW.empno, :NEW.ename, :NEW.salary, dept_var.deptno);

END IF;

END;


Ex de hierarquia de views l.jpg

Ex. De Hierarquia de Views

CREATE TABLE AllPersons

( typeid NUMBER(1),

ssn NUMBER,

name VARCHAR2(30),

address VARCHAR2(100),

deptid NUMBER,

major VARCHAR2(30),

empid NUMBER,

mgr VARCHAR2(30));

  • OBS.: typeid: 1 = person_typ

    2 = student_typ

    3 = employee_typ


Ex de hierarquia de views117 l.jpg

Ex. De Hierarquia de Views

CREATE TYPE person_typ AS OBJECT

( ssn NUMBER,

name VARCHAR2(30),

address VARCHAR2(100)) NOT FINAL;

/

CREATE TYPE student_typ UNDER person_typ

( deptid NUMBER,

major VARCHAR2(30)) NOT FINAL;

/

CREATE TYPE employee_typ UNDER person_typ

( empid NUMBER,

mgr VARCHAR2(30));


Ex de hierarquia de views118 l.jpg

Ex. De Hierarquia de Views

CREATE VIEW Person_v OF person_typ

WITH OBJECT OID(ssn) AS

SELECT ssn, name, address

FROM AllPersons

WHERE typeid = 1;

CREATE VIEW Student_v OF student_typ UNDER Person_vAS

SELECT ssn, name, address, deptid, major

FROM AllPersons

WHERE typeid = 2;

CREATE VIEW Employee_v OF employee_typ UNDER Person_vAS

SELECT ssn, name, address, empid, mgr

FROM AllPersons

WHERE typeid = 3;


Consultando object views l.jpg

Consultando Object Views

  • SELECT REF(p), VALUE(p) FROM Person_v p;

  • SELECT * FROM Person_v;

  • SELECT VALUE(p) FROM ONLY(Person_v) p;


  • Login