1 / 132

Capítulo 4 : SGBDOR Oracle 11g

Capítulo 4 : SGBDOR Oracle 11g. Banco de Dados II Prof. Carlos Eduardo Pires cesp@dsc.ufcg.edu.br. SGBDOR Oracle 11g. O SGBDOR ORACLE 11g oferece diferentes tipos de objetos: Tipos de Objetos (TADs) Nested Tables (Tabelas Aninhadas) Varying Arrays (Varrays) Large Objects (LOBs)

kipling
Télécharger la présentation

Capítulo 4 : SGBDOR 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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Capítulo 4: SGBDOR Oracle 11g Banco de Dados II Prof. Carlos Eduardo Pires cesp@dsc.ufcg.edu.br

  2. SGBDOR Oracle 11g Capítulo 4: BDOR – SGBD Oracle 11g • O SGBDOR ORACLE 11g oferece diferentes tipos de objetos: • Tipos de Objetos (TADs) • Nested Tables (Tabelas Aninhadas) • Varying Arrays (Varrays) • Large Objects (LOBs) • References (REF) • Object View (Visão de Objetos) • No entanto, há algumas diferenças com o padrão SQL:1999...

  3. SGBDOR Oracle 11g Capítulo 4: BDOR – SGBD Oracle 11g • Conceitos Básicos • Tipo de Objeto • Método • Evolução de Tipo (ALTER TYPE) • Herança de Tipo • Tabela de Objeto • Objeto de Linha e Objeto de Coluna • Referência de Objeto • Coleção de Objetos

  4. Exemplo - UML Esquema Conceitual Capítulo 4: BDOR – SGBD Oracle 11g

  5. Projeto BD Relacional Esquema Relacional Capítulo 4: BDOR – SGBD Oracle 11g

  6. Esquema Objeto-Relacional Capítulo 4: BDOR – SGBD Oracle 11g

  7. Definindo os Tipos 5 7 6 2 4 1 3 Capítulo 4: BDOR – SGBD Oracle 11g

  8. Tipos de Objetos (Object Types) Capítulo 4: BDOR – SGBD Oracle 11g 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 Por default, são definidos como FINAL (herança)

  9. Tipos de Objetos (Object Types) • Um Tipo de Objeto é um esquema de objeto com 3 componentes: • Nome obrigatório • Atributos  obrigatório • Métodos  facultativo (já vem com construtor) • Um Tipo de Objeto pode ser usado para: • Definir o domínio de campos (“column object”) de tabelas normais • Definir o tipo dos atributos de TADs (“embedded object”) • Criar uma tabela de objetos (“object table”) Capítulo 4: BDOR – SGBD Oracle 11g

  10. Tipos de Objetos (Object Types) Capítulo 4: BDOR – SGBD Oracle 11g Um tipo de objeto em Oracle possui a seguinte estrutura:

  11. Tipos de Objetos (Object Types) Capítulo 4: BDOR – SGBD Oracle 11g 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] );

  12. Tipos de Objetos (Object Types) Capítulo 4: BDOR – SGBD Oracle 11g Pode ser usado da mesma forma que é usado um tipo primitivo -- 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) );

  13. Tipos de Objetos CREATE TYPEENDERECO_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 Capítulo 4: BDOR – SGBD Oracle 11g

  14. Tipos de Objetos CREATE TYPEENDERECO_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_TYPAS OBJECT(nome VARCHAR2(25),endereco ENDERECO_TYP); Não é possível ocorrer uma inserção de dados em PESSOA_TYP, porque um tipo de objeto apenas descreve dados, mas não os armazena Capítulo 4: BDOR – SGBD Oracle 11g

  15. 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); CREATE TABLE pessoas2 OF pessoa_ty; As tabelas de objetos PESSOAS e PESSOAS2 irão armazenar dados com a estrutura do tipo PESSOA_TY Capítulo 4: BDOR – SGBD Oracle 11g

  16. Exemplo com Métodos CREATE TYPEperson_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 person_typ)); Atenção: (SELF IN OUT person_type ) é opcional! Capítulo 4: BDOR – SGBD Oracle 11g

  17. Exemplo (cont.) Capítulo 4: BDOR – SGBD Oracle 11g CREATE TYPE BODY person_typ AS MAP MEMBER FUNCTION get_idno RETURN NUMBER IS BEGIN -- método usado para comparação de objetos RETURN idno; END; MEMBER PROCEDURE display_details (SELF IN OUT 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;

  18. Inserindo Dados construtor Capítulo 4: BDOR – SGBD Oracle 11g Exemplo CREATE TABLE contacts ( contact person_typ, contact_date DATE ); INSERT INTO contacts VALUES ( person_typ (65, 'Verna', 'Mills', 'vmills@oracle.com', '1-800-555-4412'), '24-JUN-2003');

  19. Inserindo com NULL INSERT INTO contacts VALUES (person_typ (NULL, NULL, NULL,NULL,NULL), '24 Jun 2003' ); INSERT INTO contacts VALUES (NULL, '25 Jun 2003' ); 1 2 Capítulo 4: BDOR – SGBD Oracle 11g

  20. Inserindo com NULL • Em ambos os casos, o SGBD Oracle aloca espaço em CONTACTS para uma nova linha e atualiza a coluna CONTACT_DATE de acordo com o valor fornecido • Caso 1: SGBD Oracle aloca espaço para um objeto na coluna CONTACT e atualiza cada atributo para NULL • ((null, null, null, null, null),’08-JAN-2009’) • Caso 2: SGBD Oracle atualiza o campo CONTACT para NULL e não aloca espaço para um objeto na coluna CONTACT • (null,’08-JAN-2009’) Capítulo 4: BDOR – SGBD Oracle 11g

  21. Restrições de Integridade (Constraints) nome da constraint dado pelo usuário construtores Capítulo 4: BDOR – SGBD Oracle 11g • São definidas na tabela normal ou de objetos CREATE TABLE department_mgrs ( dept_no NUMBER CONSTRAINT dept_no_pk 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'));

  22. Trigger em Objetos Tipados CREATE TYPE location_typ AS OBJECT( city VARCHAR2(30), building_no NUMBER); coluna tabela CREATE TABLE office_tab ( office_loc location_typ, occupant occupant_typ); CREATE TYPE occupant_typ AS OBJECT( idno NUMBER); Capítulo 4: BDOR – SGBD Oracle 11g CREATE TABLE movement ( idno NUMBER, old_office location_typ, new_office location_typ); CREATE TRIGGER my_trigger 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;

  23. Métodos Capítulo 4: BDOR – SGBD Oracle 11g • São funções ou procedimentosdeclarados 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 • Por default, os métodos são definidos como NOT FINAL (permite que sejam sobrescritos)

  24. Métodos Capítulo 4: BDOR – SGBD Oracle 11g • Um Object Type... • sempre possui um método construtor • pode possuir zero ou mais métodos membro • pode possuir um método map ou um método order, porém não os dois • Exemplo de chamada de método sem parâmetros SELECT c.get_idno() AS idno FROM contacts c;

  25. Métodos Capítulo 4: BDOR – SGBD Oracle 11g • MEMBER • São os métodos mais comuns • Implementam as operações das instâncias do tipo • São chamados através da qualificação de objeto objeto.método() • SELF não precisa ser declarado, mas, se for, deverá ser sempre o primeiro parâmetro

  26. Exemplo: Member Method Capítulo 4: BDOR – SGBD Oracle 11g CREATE TYPE solid_typ AS OBJECT ( len INTEGER, wth INTEGER, hgt INTEGER, MEMBER FUNCTION volume RETURN INTEGER, MEMBER FUNCTION surface RETURN INTEGER, MEMBER PROCEDURE display (SELF IN OUT 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 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;

  27. Métodos Capítulo 4: BDOR – SGBD Oracle 11g • Construtor • Criado implicitamente ao criar um tipo de objeto • Nome é exatamente igual ao nome do tipo • Pode haver mais de um construtor (overloadable) • Exemplo INSERT INTO tb_contatos VALUES ( Person_typ (65, 'Pedro', 'Medeiros', 'pm@hotmail.com', '83-3337-3333'),SYSDATE);

  28. Static Method Capítulo 4: BDOR – SGBD Oracle 11g 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 São chamados nos tipos de dados, não em uma instância particular Chamado da seguinte forma: type_name.method()

  29. Exemplo 01: Static Method CREATE OR REPLACE TYPE alunosbd2_typ AS OBJECT( matricula NUMBER, nome VARCHAR2(20), semestre NUMBER, ano NUMBER, STATIC PROCEDURE atualiza (p_semestre NUMBER)); CREATE TABLE alunosbd2_tab OF alunosbd2_typ; INSERT INTO alunosbd2_tab VALUES (1,'JOSE',20101,null); INSERT INTO alunosbd2_tab VALUES (2,'MARIA',20111,null); INSERT INTO alunosbd2_tab VALUES (3,'PEDRO',20111,null); Capítulo 4: BDOR – SGBD Oracle 11g

  30. Exemplo 01: Static Method CREATE OR REPLACE TYPE BODY alunosbd2_typ AS STATIC PROCEDURE atualiza (p_semestre NUMBER) IS BEGIN UPDATE alunosbd2_tab SET ano = SUBSTR(TO_CHAR(semestre),1,4) WHERE semestre = p_semestre; END; END; BEGIN alunosbd2_typ.atualiza(20111); END; SELECT * FROM alunosbd2_tab; Capítulo 4: BDOR – SGBD Oracle 11g

  31. Exemplo 02: Static Method CREATE TYPE atype AS OBJECT( a1 NUMBER, STATIC PROCEDURE newa (p1 NUMBER, tabname VARCHAR2, schname VARCHAR2)); CREATE TYPE BODY atype AS STATIC PROCEDUREnewa (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; construtor valor, tabela e esquema (usuário do BD) Capítulo 4: BDOR – SGBD Oracle 11g

  32. Métodos para Comparação de Objetos • Motivação DROP TABLE emp_table; DROP TYPE emp_type; CREATE OR REPLACE TYPE end_type AS OBJECT ( rua VARCHAR2(30), bairro VARCHAR2(30)); Capítulo 4: BDOR – SGBD Oracle 11g

  33. Métodos para Comparação de Objetos CREATE OR REPLACE TYPE emp_type AS OBJECT ( nome VARCHAR2(30), endr end_type); CREATE TABLE emp_table OF emp_type; INSERT INTO emp_table VALUES ('JOSE',end_type('RUA DO SOL','CATOLE')); INSERT INTO emp_table VALUES ('MARIA',end_type('RUA DA LUA','CATOLE')); Capítulo 4: BDOR – SGBD Oracle 11g

  34. Métodos para Comparação de Objetos SELECT m.nome FROM emp_table m ORDER BY m.endr; order by m.endr * ERRO na linha 3: ORA-22950: não pode ORDER objetos sem o método MAP ou ORDER Capítulo 4: BDOR – SGBD Oracle 11g

  35. Métodos para Comparação de Objetos Capítulo 4: BDOR – SGBD Oracle 11g • MAP ou ORDER • São “funções” opcionais e servem para comparar objetos • São mutuamente exclusivas! • MAP • Implementa o MAP do SQL:1999, retornando um valor de tipo built-in • Não exige parâmetro de entrada • MAP compara vários objetos (ex.: ORDER BY) • ORDER • Implementa o RELATIVE WITH do SQL:1999, retornando negativo, zero ou positivo • Exige como parâmetro um objeto do mesmo tipo • Compara o objeto corrente (SELF) com o objeto do parâmetro

  36. Métodos para Comparação de Objetos Capítulo 4: BDOR – SGBD Oracle 11g 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; END;

  37. Métodos para Comparação de Objetos Outro Exemplo de MAP CREATE OR REPLACE TYPE end_type AS OBJECT ( rua VARCHAR2(30), bairro VARCHAR2(30), MAP MEMBER FUNCTION compare RETURN VARCHAR2); Ou... ALTER TYPE end_type ADD MAP MEMBER FUNCTION compare RETURN VARCHAR2 CASCADE; CREATE OR REPLACE TYPE BODY end_type AS MAP MEMBER FUNCTION compare RETURN VARCHAR2 IS BEGIN RETURN rua; END; END; Capítulo 4: BDOR – SGBD Oracle 11g

  38. Métodos para Comparação de Objetos CREATE TABLE emp_table (nome VARCHAR2(400), endr end_type); SELECT m.nome FROM emp_table m ORDER BY m.endr; NOME ----------------------------- MARIA JOSE Ou... SELECT m.nome FROM emp_table m ORDER BY m.endr.rua; Não precisaria de MAP! -- Rua da Lua -- Rua do Sol Capítulo 4: BDOR – SGBD Oracle 11g

  39. Métodos para Comparação de Objetos Capítulo 4: BDOR – SGBD Oracle 11g Exemplo de ORDER CREATE TYPE location_typAS 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 self.building_no < l.building_no THEN RETURN -1; -- qualquer número negativo ELSIF self.building_no > l.building_no THEN RETURN 1; -- qualquer número positivo ELSE RETURN 0; END IF; END; END;

  40. Evolução de Tipos Capítulo 4: BDOR – SGBD Oracle 11g • 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 a precisão ou a escala • Modificar um atributo texto para aumentar seu comprimento • Mudar propriedades FINAL e INSTANTIABLE do tipo

  41. Evolução de Tipos Capítulo 4: BDOR – SGBD Oracle 11g • Exemplos ALTER TYPE person_typ ADD ATTRIBUTE (sex CHAR(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 FINAL; -- não poderá ter subtipo • Observação • CASCADE: propaga a mudança para todos os tipos dependentes

  42. Evolução de Tipos – Exemplo 01 Capítulo 4: BDOR – SGBD Oracle 11g CREATE TYPE address_type AS OBJECT (street VARCHAR2(60)); CREATE TABLE customer ( id NUMBER, name VARCHAR2(40), address address_type); ALTER TYPE address_type ADD ATTRIBUTE province VARCHAR2(40); * ERRO na linha 1: ORA-22312: é necessário especificar a opção CASCADE ou INVALIDATE ALTER TYPE address_type ADD ATTRIBUTE province VARCHAR2(40) CASCADE; SQL> DESCRIBE address_type Nome Nulo? Tipo ----------------------------------------- -------- ------------ STREET VARCHAR2(60) PROVINCE VARCHAR2(40)

  43. Evolução de Tipos – Exemplo 02 Capítulo 4: BDOR – SGBD Oracle 11g CREATE TYPE address_type2 AS OBJECT (street VARCHAR2(60), num NUMBER); CREATE OR REPLACE PROCEDURE test_proc01 IS v_address ADDRESS_TYPE2; BEGIN v_address.street := 'test'; END; SELECT status FROM user_objects WHERE object_name = 'TEST_PROC01'; STATUS ------- VALID ALTER TYPE address_type2 DROP ATTRIBUTE num INVALIDATE; SELECT status FROM user_objects WHERE object_name = 'TEST_PROC01'; STATUS ------- INVALID

  44. Herança de Tipos Capítulo 4: BDOR – SGBD Oracle 11g • Suporta herança simples • Há uma diferença do padrão SQL:1999, pois o Oraclenão requer herança explicitamente nas tabelas, mas apenas nos tipos modelo mais simples • 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 • 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

  45. Herança de Tipos Capítulo 4: BDOR – SGBD Oracle 11g CREATE TYPE person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20), FINAL MAP MEMBER FUNCTION get_idno RETURN NUMBER )NOT FINAL; Obs.: Por default, um tipo de objeto é FINAL!

  46. Herança de Tipos Capítulo 4: BDOR – SGBD Oracle 11g CREATE TYPE employee_typ UNDER person_typ( depto_id NUMBER, funcao VARCHAR2(30), salario NUMBER ) NOT FINAL; CREATE TYPE professor_typ UNDERperson_typ ( dept_id NUMBER, speciality VARCHAR2(30) ) NOT FINAL;

  47. Herança de Tipos Capítulo 4: BDOR – SGBD Oracle 11g CREATE TYPE student_typ UNDER person_typ ( registration NUMBER ) NOT FINAL; CREATE TYPE monitor_typ UNDER student_typ ( year NUMBER ) NOT FINAL;

  48. Herança de Tipos SQL> DESCRIBE person_typ; person_typ is NOT FINAL Nome Nulo? Tipo --------------------------------------- -------- ------------ IDNO NUMBER NAME VARCHAR2(30) PHONE VARCHAR2(20) SQL> DESCRIBE employee_typ; employee_typ extends HR.PERSON_TYP employee_typ is NOT FINAL Nome Nulo? Tipo ---------------------------------------- -------- ------------ IDNO NUMBER NAME VARCHAR2(30) PHONE VARCHAR2(20) DEPTO_ID NUMBER FUNCAO VARCHAR2(30) SALARIO NUMBER Capítulo 4: BDOR – SGBD Oracle 11g

  49. Herança de Tipos Não foi necessário usar UNDER • CREATE TABLE person_tab OF person_typ; • CREATE TABLE student_tab OF student_typ; • CREATE TABLE monitor_tab OF monitor_typ; • INSERT INTO person_tab VALUES (person_typ(1,'JOSE','88839098')); • INSERT INTO student_tab VALUES(student_typ(2,'MARIA', '33372298',20000)); • INSERT INTO monitor_tab VALUES(monitor_typ(3, 'EICKMANN','33362288',30000,2010)); Capítulo 4: BDOR – SGBD Oracle 11g

  50. Herança de Tipos SELECT p.* FROM person_tab p; IDNO NAME PHONE ---- ------------------------------ -------------------- 1 JOSE 88839098 SELECT s.* FROM student_tab s; IDNO NAME PHONE REGISTRATION ---- ------------------------------ -------------------- ---------------------- 2 MARIA 33372298 20000 SELECT m.* FROM monitor_tab m; IDNO NAME PHONE REGISTRATION YEAR ---- ------------------- -------------- ---------------------- ------- 3 EICKMANN 33362288 30000 2010 Capítulo 4: BDOR – SGBD Oracle 11g

More Related