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)

Large Objects (LOBs)

References (REF)

Object View (Visão de Objetos)
No entanto, há algumas diferenças com o padrã
SQL:1999
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
Oracle OR 11g
O ORACLE 11g fornece um suporte completo para todos os três
diferentes tipos de implementação:
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.
Exemplo - UML
Projeto BD Relacional
Esquema Relacional
Esquema
Objeto
Relacional
Definindo os Tipos
Tipos de Objetos (Object
Types)





Tipo de objeto é um tipo abstrato de dados
(TAD), ou seja um Structured Type 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
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

Um tipo de objeto em Oracle possui a
seguinte estrutura:
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 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_contato DATE );
CREATE TABLE tb_domicilio (
local tp_ponto,
endereco VARCHAR2 (80) );
-- Para definir o tipo de um atributo de um TAD
CREATE TYPE tp_contatos AS OBJECT (
contato tp_ pessoa,
dt_contato DATE );
CREATE TYPE tp_domicilio AS OBJECT (
local tp_ponto,
endereco VARCHAR2 (80) );
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 objetos
create type ENDERECO_TYP as object
(rua
VARCHAR2(50),
cidade VARCHAR2(25),
estado CHAR(2),
cep
NUMBER);
create type PESSOA_TYP as object
(nome VARCHAR2(25),
endereco ENDERECO_TYP);
ENDEREÇO_TYP é usado
para definir o tipo do
atributo Endereco do tipo
PESSOA_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 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
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.)
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
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
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
CREATE TABLE department_mgrs (
dept_no NUMBER PRIMARY KEY,
dept_name CHAR(20),
dept_mgr person_typ,
dept_loc location_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, 'Physical Sciences',
person_typ(65,'Vrinda Mills', '1-800-555-4412'),
location_typ(300, 'Palo Alto'));
Trigger em Objetos Tipados
CREATE TABLE movement (
idno NUMBER,
old_office location_typ,
new_office location_typ );
CREATE TRIGGER trigger1
BEFORE UPDATE OF office_loc ON office_tab
FOR EACH ROW
WHEN (new.office_loc.city = 'Redwood Shores')
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 pode modificar colunas do tipo LOB no corpo de um trigger!
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é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é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
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é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




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
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
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 (que indica se um objeto é igual ou não de outro
baseado na comparação de cada atributo)
ORDER




Implementa o RELATIVE WITH do SQL:1999, retornando negativo,
zero ou positivo
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
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 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; -- qualquer número negativo
ELSIF building_no > l.building_no THEN
RETURN 1; -- qualquer número positivo
ELSE RETURN 0;
END IF;
END;
END;
Evolução de Tipos






Uso do comando ALTER TYPE, permite
modificar, ou evoluir, um tipo objeto:
Adicionar e remover atributos
Adicionar e remover métodos
Modificar um atributo numérico para aumentar
o length, precision, ou scale
Modicar um atributo string para aumentar seu
length
Mudar propriedades FINAL e INSTANTIABLE
do tipo
Evolução de Tipos
EX:
ALTER TYPE person_typ
ADD ATTRIBUTE ( sex VARCHAR2(1) ) CASCADE ;
ALTER TYPE person_typ
DROP MAP MEMBER FUNCTION get_id RETURN
NUMBER;
ALTER TYPE person_typ NOT FINAL CASCADE;
ALTER TYPE person_typ
MODIFY ATTRIBUTE sex VARCHAR2(10) CASCADE ;


OBS.:

CASCADE: propaga a mudança para todos os tipos
dependentes
Herança de Tipos





Suporta herança simples
Há uma diferença do padrão SQL:1999, pois o
Oracle não requer herança explicitamente nas
tabelas, mas apenas nos tipos => modelo mais
simples, embora menos flexí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 Tipos
CREATE TYPE employee_typ UNDER
person_typ(
depto_id NUMBER,
funcao VARCHAR2(30),
salario NUMBER
) NOT FINAL;

OBS.: Por default um tipo de objeto é
FINAL!
Herança de Tipos – Drop Type
Não se pode remover um subtipo antes de
remover suas respectivas instâncias na tabela
que armazena as tuplas daquele subtipo
(substitutability)
 Ex.: Drop Type incorreto
DROP TYPE student_typ VALIDATE
Correto seria:
DELETE FROM person_obj_table WHERE p IS OF
(student_typ);
DROP TYPE student_typ VALIDATE;

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 Tipos
CREATE TYPE professor_typ
UNDER person_typ (
dept_id NUMBER,
speciality VARCHAR2(30))
NOT FINAL;
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.)
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 (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 (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 (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 (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-5557765',
100, 'Jennifer Nelson'));
INSERT INTO person_obj_table
VALUES (part_time_student_typ(52, 'Kim Patel', '1-800555-1232', 14, 'PHYSICS', 20));
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




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
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
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
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






Equivalentes às Tabelas Tipadas (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 (object identifier) 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
aliases para as tabelas
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
 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 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 tabelas
SELECT p.CPF
FROM PESSOAS p
WHERE p.nome = ‘Maria Silva';
Resposta:
CPF
-------------------------------------------------------------543875432
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 tabelas
select p.endereco.cidade
from PESSOAS p
where p.nome = ‘Maria Silva';
Resposta:
ENDERECO.CIDADE
------------------------Campina Grande
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
person person_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
 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
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-5551232', 14, 'PHYSICS', 20), '24 Jun 2003' );
Desligando a substitutability
CREATE TYPE office_typ AS OBJECT (
office_id VARCHAR(10),
location location_typ,
occupant person_typ ) NOT FINAL;
CREATE TABLE dept_office (
dept_no NUMBER,
office office_typ)
COLUMN office NOT SUBSTITUTABLE AT ALL LEVELS;
CREATE TABLE dept_office (
dept_no NUMBER,
office office_typ)
COLUMN office NOT SUBSTITUTABLE AT ALL LEVELS;
CREATE TABLE office_tab OF office_typ
COLUMN occupant NOT SUBSTITUTABLE AT ALL LEVELS;
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
update PESSOAS p
Set p.endereco= ENDERECO_TY(‘Rua das Margaridas 22',
‘Patos', ‘PB', 58453230)
where p.nome = ‘Maria Silva';
Delete from PESSOAS p
where p.nome = ‘Maria Silva';
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)





É 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
REF
CREATE TYPE emp_person_typ AS OBJECT (
name VARCHAR2(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';
REF
CREATE OR REPLACE TYPE tp_cliente as OBJECT(
cod_cli VARCHAR (3),
nome_cli VARCHAR (60));
CREATE TABLE tb_cliente OF tp_cliente(
cod_cli PRIMARY KEY,
nome_cli NOT NULL);
CREATE OR REPLACE TYPE tp_dependente as OBJECT(
cod_dep VARCHAR(3),
nm_dep VARCHAR (60),
ref_titular REF tp_cliente);
CREATE TABLE tb_dependente OF tp_dependente(
cod_dep PRIMARY KEY,
nm_dep NOT NULL,
ref_titular SCOPE IS tb_cliente);
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’;
REF
SELECT * FROM tb_dependente D;
COD_DEP NM_DEP REF_TITULAR
----------------------------------------D1
Paulo Q2459QW8RNDGS0D98G765SF
D2
Pedro 5XBGVX3B75XCN490VM0VBX4

REF
SELECT REF(D)
FROM tb_dependente D
WHERE D. nm_dep = ‘Paulo’;
REF(D)
----------------------HRD23K56RNDGS0DUY6TGDE4
REF
SELECT D.ref_titular.cod_cli cod_cliente,
D.ref_titular.nm_cli nm_cliente,
D.nm_dep nm_dependente
FROM tb_dependente D;
COD_CLIENTE NM_CLIENTE
NM_DEPENDENTE
--------------------------------------------------------------C1
Rita
Paulo
C2
Ana
Pedro
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_cli cod_cliente,
D.ref_titular.nm_cli nm_cliente,
D.nm_dep nm_dependente
FROM tb_dependente D;
COD_CLIENTE NM_CLIENTE NM_DEPENDENTE
--------------------------------------------------------------Paulo
C2
Ana
Pedro

OBS.: O objeto Rita não é listado, mas Paulo ainda existe!!!!
Dangling REF
Verificando a validade das referências (Dangling)
SELECT D.ref_titular.cod_cli cod_cliente,
D.ref_titular.nm_cli nm_cliente,
D.nm_dep nm_dependente
FROM tb_dependente D
WHERE D.ref_titular IS DANGLING;

COD_CLIENTE NM_CLIENTE NM_DEPENDENTE
-------------------------------------------------------------Paulo
Dangling REF
Verificando a validade das referências (Dangling)
SELECT D.ref_titular.cod_cli cod_cliente,
D.ref_titular.nm_cli nm_cliente,
D.nm_dep nm_dependente
FROM tb_dependente D
WHERE D.ref_titular IS NOT DANGLING;

COD_CLIENTE NM_CLIENTE NM_DEPENDENTE
---------------------------------------------------------------C2
Ana
Pedro
Dangling != NULL
SELECT D.ref_titular.cod_cli cod_cliente,
D.ref_titular.nm_cli nm_cliente,
D.nm_dep nm_dependente
FROM tb_dependente D
WHERE D.ref_titular IS NOT NULL;
COD_CLIENTE NM_CLIENTE NM_DEPENDENTE
-------------------------------------------------------------Paulo
C2
Ana
Pedro
SCOPED REF
CREATE TABLE contacts_ref (
contact_ref REF person_typ SCOPE IS
person_obj_table,
contact_date DATE );
 Para inserir uma linha na tabela:
INSERT INTO contacts_ref
SELECT REF(p), '26 Jun 2003‘
FROM person_obj_table p
WHERE p.idno = 101;
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



O operador DEREF “desfaz” o REF
Retorna um objeto referenciado por uma
coluna do tipo REF
Desreferenciar um objeto dangling retorna
um objeto null
DEREF
Ex.:
SELECT DEREF(D.ref_titular) deref_titular,
D.nm_dep nm_dependente
FROM tb_dependente D;

DEREF_TITULAR(COD_CLI, NM_CLI)
NM_DEPENDENTE
-----------------------------------------------------------------------TP_CLIENTE('C1', 'Rita')
Paulo
TP_CLIENTE('C2', 'Ana')
Pedro
DEREF
EX. (sem usar DEREF):
SELECT D.ref_titular sem_deref,
D.nm_dep nm_dependente
FROM tb_dependente D;
SEM_DEREF
NM_DEPENDENTE
-----------------------------------------------------------------------P07XZC8V6Z0F97X6VZ965X6VZ4X8VXCVB6Z Paulo
XCVU6CHBD967B436CB74B5X9B2BX2VQ4WFF Pedro

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
EMPREGADO_TY
de
pto
DEPARTAMENTO_TY
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
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
Ex.: Pega subtipo student_t e seus respectivos
subtipos
SELECT VALUE(p)
FROM person_obj_table p
WHERE VALUE(p) IS OF (student_typ);

Ex.: Pega apenas instâncias do subtipo
SELECT TREAT(VALUE(p) AS student_typ)
FROM person_obj_table p
WHERE VALUE(p) IS OF(ONLY student_typ);

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

Oracle dá suporte a:


VARRAY: coleção ordenada de elementos, com
número fixo de elementos, se quiser manipular a
coleção toda de uma vez
Nested tables: quando se precisa rodar
consultas eficientes em coleções, manipular um
número arbitrário de elementos ou executar
várias operações de insert, update, ou delete.
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)
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_list phone_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

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;
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



É 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)
CREATE TYPE people_typ AS TABLE OF
person_typ;
CREATE TABLE people_tab (
group_no NUMBER,
people_column people_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-5551212'),
person_typ(2, 'Diane Smith', NULL)));
Nested Table (exemplo)
CREATE TABLE students (
graduation DATE,
math_majors people_typ,
chem_majors people_typ,
physics_majors people_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

Podemos ter:

Nested table de nested table type

Nested table de varray type
Varray de nested table type
Varray de varray type
Nested table ou varray de um UDT que
tem um atributo que é uma nested table
ou varray type



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),
locations nt_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-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 Rue des Corps-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

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çõ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-5551212'),
PERSON_TYP(2, 'Diane Smith', '1-800-555-1243'))
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çõ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çõ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ções
SELECT *
FROM TABLE(
SELECT d.dept_emps
FROM department_persons d
WHERE d.dept_no = 101);
Obs.: a subquery acima deve retornar sempre
uma coleção
a projeção da subquery deve conter apenas
um item
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çõ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çõ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çõ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



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
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
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),
address address_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
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
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
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



Oracle permite, se não houver ambiguidade,
realizar update, delete e insert em Object Views.
Uma View não poderá ser diretamente
atualizada se contiver joins, set operators,
aggregate functions, GROUP BY ou DISTINCT
clauses. Nestes casos, para poder-se atualizála, deve-se usar o INSTEAD OF Trigger
Uma remoção ou alteração numa superview
será feita nas subviews também a menos que
se use o operador ONLY
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
- Número de empregados um Departamento não pode exceder 10
CREATE TRIGGER emp_instr INSTEAD OF INSERT on emp_view
FOR EACH ROW
DECLARE
dept_var dept_t;
emp_count integer;
BEGIN
-- Enforce the constraint
-- 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 the insert
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
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 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 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_v AS
SELECT ssn, name, address, deptid, major
FROM AllPersons
WHERE typeid = 2;
CREATE VIEW Employee_v OF employee_typ UNDER Person_v AS
SELECT ssn, name, address, empid, mgr
FROM AllPersons
WHERE typeid = 3;
Consultando Object Views
1.
SELECT REF(p), VALUE(p) FROM Person_v
p;
2.
SELECT * FROM Person_v;
3.
SELECT VALUE(p) FROM ONLY(Person_v)
p;