Exercício 1
===========
-- criação dos tipos e das tabelas.
CREATE TYPE PROFESSOR_TYPE AS OBJECT (
NOME
VARCHAR2(15),
DISCIPLINA VARCHAR2(15)
);
/
CREATE TYPE NT_PROF_T AS TABLE OF PROFESSOR_TYPE;
/
CREATE TYPE ALUNO_TYPE AS OBJECT(
MATRICULA
NUMBER,
PROFESSORES
NT_PROF_T
);
/
CREATE TYPE NT_ALUNO_T AS TABLE OF ALUNO_TYPE;
/
CREATE TYPE TURMA_TYPE AS OBJECT (
CODIGO
VARCHAR2(3),
SALA
VARCHAR2(3),
ALUNOS
NT_ALUNO_T
);
/
CREATE TABLE TURMA_TAB OF TURMA_TYPE (
CODIGO PRIMARY KEY)
NESTED TABLE ALUNOS STORE AS ALUNOS_T
(NESTED TABLE PROFESSORES STORE AS PROF_T);
-- inserções
INSERT INTO TURMA_TAB VALUES
('I5A', 'S9',
NT_ALUNO_T(
ALUNO_TYPE(
210141500,
NT_PROF_T(
PROFESSOR_TYPE('Fernando','GDI'),
PROFESSOR_TYPE('Hermano','PD')
)
),
ALUNO_TYPE(
210141750,
NT_PROF_T(
PROFESSOR_TYPE('Silvio','HFC'),
PROFESSOR_TYPE('Hermano','PLP')
)
)
)
);
INSERT INTO TABLE( SELECT A.PROFESSORES
FROM TABLE( SELECT T.ALUNOS
FROM TURMA_TAB T
WHERE T.CODIGO = 'I5A') A
WHERE A.MATRICULA = 210141750)
VALUES ('Ruy','IT');
--atualizaçoes
UPDATE TABLE( SELECT A.PROFESSORES
FROM TABLE( SELECT T.ALUNOS
FROM TURMA_TAB T
WHERE T.CODIGO = 'I5A') A
WHERE A.MATRICULA = 210141500) P
SET VALUE(P)= PROFESSOR_TYPE('Jacques','PD')
WHERE P.DISCIPLINA='PD';
-- Consulta
SELECT P.NOME, P.DISCIPLINA
FROM TURMA_TAB T, TABLE(T.ALUNOS) A, TABLE(A.PROFESSORES) P;
Exercício 2
-Elimine tabelas e tipos criados
(drop)
--Referencias Cruzadas
--Criação de Tipos
CREATE TYPE EMPREGADO_TYPE AS OBJECT(
ID
NUMBER,
NOME
VARCHAR2(20),
ENDERECO
VARCHAR2(30)
);
/
CREATE TYPE EMPREGADO_NESTED AS TABLE OF REF EMPREGADO_TYPE;
/
CREATE TYPE PROJETO_TYPE AS OBJECT(
COD
NUMBER,
TITULO VARCHAR2(20),
GRUPO_T EMPREGADO_NESTED
);
/
CREATE TYPE PROJETO_NESTED AS TABLE OF REF PROJETO_TYPE;
/
ALTER TYPE EMPREGADO_TYPE ADD ATTRIBUTE(PROJETOS_T PROJETO_NESTED) CASCADE;
CREATE TABLE PROJETO_TABELA OF PROJETO_TYPE(
COD PRIMARY KEY
)
NESTED TABLE GRUPO_T STORE AS Tab_Empregados;
CREATE TABLE EMPREGADO_TABELA OF EMPREGADO_TYPE(
ID PRIMARY KEY
)
NESTED TABLE PROJETOS_T STORE AS Tab_Projetos;
-- Armazena empregados
INSERT INTO EMPREGADO_TABELA VALUES
(1, 'Julio Marques', 'Rua Amelia', PROJETO_NESTED());
INSERT INTO EMPREGADO_TABELA VALUES
(2, 'Marcus Nogueira', 'Rua Bruno Veloso', PROJETO_NESTED());
INSERT INTO EMPREGADO_TABELA VALUES
(3, 'Vanessa Costa', 'Av Caxanga', PROJETO_NESTED());
-- Armazena projetos
INSERT INTO PROJETO_TABELA VALUES
(1, 'Distributed XML', EMPREGADO_NESTED());
INSERT INTO PROJETO_TABELA VALUES
(2, 'JDBC 3', EMPREGADO_NESTED());
INSERT
REF(p)
INSERT
REF(p)
INTO
from
INTO
from
THE(Select PROJETOS_T from EMPREGADO_TABELA e WHERE e.id = 1) select
PROJETO_TABELA p where p.cod = 1;
THE(Select PROJETOS_T from EMPREGADO_TABELA e WHERE e.id = 2) select
PROJETO_TABELA p where p.cod = 1;
INSERT
REF(e)
INSERT
REF(e)
INTO
from
INTO
from
THE(Select GRUPO_T
EMPREGADO_TABELA e
THE(Select GRUPO_T
EMPREGADO_TABELA e
from PROJETO_TABELA p WHERE p.cod = 1) select
where e.id = 1;
from PROJETO_TABELA p WHERE p.cod = 1) select
where e.id = 2;
-- Consultas
SELECT DEREF(ss.COLUMN_VALUE)
FROM THE(SELECT GRUPO_T FROM PROJETO_TABELA p WHERE p.cod = 1) ss;
SELECT DEREF(ss.COLUMN_VALUE)
FROM THE(SELECT PROJETOS_T FROM EMPREGADO_TABELA E WHERE E.ID = 1) ss;
SELECT DEREF(VALUE(p)).titulo
FROM THE (SELECT PROJETOS_T FROM EMPREGADO_TABELA e WHERE e.nome = 'Marcus
Nogueira') p;
Exercício 3
-Elimine tabelas e tipos criados
--herança
--Criação de tipos
(drop)
CREATE TYPE T_PESSOA AS OBJECT(
NOME VARCHAR2(30),
TELEFONE VARCHAR2(20),
DATA_NASCIMENTO DATE
) NOT FINAL;
/
CREATE TYPE T_ALUNO UNDER T_PESSOA(
MATRICULA Integer
);
/
--Criação de tabela
CREATE TABLE ALU_TABLE OF T_ALUNO(
MATRICULA PRIMARY KEY
);
--Inserção de dados
INSERT INTO ALU_TABLE VALUES(T_ALUNO('Maria
Bonita','21268430',to_date('20/01/1980','dd/mm/yyyy'), 45));
--Consulta
SELECT p.NOME,p.DATA_NASCIMENTO FROM ALU_TABLE p WHERE p.MATRICULA = 45;
Exercício 4
-Elimine tabelas e tipos criados
(drop)
--Member methods e nested table
--Criação de tipos
CREATE TYPE EMPREGADO_TP AS OBJECT (
MATRICULA INTEGER,
NOME VARCHAR2(40),
SALARIO NUMBER(5)
);
/
CREATE TYPE EMP_NESTED AS TABLE OF EMPREGADO_TP;
/
CREATE TYPE DEPARTAMENTO_TP AS OBJECT (
MATRICULA INTEGER,
NOME VARCHAR2(40),
EMPREGADOS EMP_NESTED,
MEMBER FUNCTION total_empregados RETURN INTEGER,
MEMBER FUNCTION total_salarios RETURN NUMBER,
MEMBER FUNCTION numeroEmpregadosGanhamMais (numero IN INTEGER) RETURN
INTEGER
);
/
CREATE TABLE DEPARTAMENTO_TAB OF DEPARTAMENTO_TP (
MATRICULA PRIMARY KEY)
NESTED TABLE EMPREGADOS STORE AS EMP_T;
/
CREATE OR REPLACE TYPE BODY DEPARTAMENTO_TP AS
MEMBER FUNCTION TOTAL_EMPREGADOS RETURN INTEGER IS
BEGIN
RETURN SELF.EMPREGADOS.COUNT( );
END;
MEMBER FUNCTION TOTAL_SALARIOS RETURN NUMBER IS
CONTADOR INTEGER;
TOTALSALARIO NUMBER := 0;
BEGIN
FOR CONTADOR IN 1..SELF.EMPREGADOS.COUNT( ) LOOP
TOTALSALARIO := TOTALSALARIO + SELF.EMPREGADOS(CONTADOR).SALARIO;
END LOOP;
RETURN TOTALSALARIO;
END;
MEMBER FUNCTION NUMEROEMPREGADOSGANHAMMAIS (NUMERO IN INTEGER) RETURN INTEGER
IS
CONTADOR INTEGER;
TOTALEMPREGADOS NUMBER := 0;
BEGIN
FOR CONTADOR IN 1..SELF.EMPREGADOS.COUNT( ) LOOP
IF SELF.EMPREGADOS(CONTADOR).SALARIO > NUMERO THEN
TOTALEMPREGADOS := TOTALEMPREGADOS + 1;
END IF;
END LOOP;
RETURN TOTALEMPREGADOS;
END;
END;
/
-- INSERÇOES
INSERT INTO departamento_tab VALUES (departamento_tp(1, 'Depto Compras',
emp_nested(
empregado_tp(1, 'Marcelo',1000)
)
)
);
INSERT INTO THE(SELECT d.empregados FROM departamento_tab d WHERE d.nome =
'Depto Compras')
VALUES (empregado_tp(2,'Joca',2000));
INSERT INTO THE(SELECT d.empregados FROM departamento_tab d WHERE d.nome =
'Depto Compras')
VALUES (empregado_tp(3,'Ana Maria',1200.75));
INSERT INTO THE(SELECT d.empregados FROM departamento_tab d WHERE d.nome =
'Depto Compras')
VALUES (empregado_tp(4,'Antônio André',900));
INSERT INTO departamento_tab VALUES (departamento_tp(2, 'Depto Finanças',
emp_nested(
empregado_tp(1, 'Mônica',2100)
)
)
);
INSERT INTO THE(SELECT d.empregados FROM departamento_tab d WHERE d.nome =
'Depto Finanças')
VALUES (empregado_tp(2,'Silvério',2030));
INSERT INTO THE(SELECT d.empregados FROM departamento_tab d WHERE d.nome =
'Depto Finanças')
VALUES (empregado_tp(3,'Ronaldo',1200));
--Consultas
-- LISTAR TODOS OS DADOS DOS FUNCIONÁRIOS DO DEPARTAMENTO DE COMPRAS (USANDO
THE)
SELECT * FROM THE(SELECT d.empregados FROM departamento_tab d WHERE d.nome =
'Depto Compras');
-- LISTAR TODOS OS DADOS DOS FUNCIONÁRIOS DO DEPARTAMENTO DE COMPRAS (USANDO
TABLE)
SELECT e.* FROM departamento_tab d, TABLE(d.empregados) e
WHERE d.nome = 'Depto Compras';
-- LISTAR TODOS OS DADOS DOS FUNCIONÁRIOS DO DEPARTAMENTO DE FINANÇAS (USANDO
TABLE)
SELECT e.* FROM departamento_tab d, TABLE(d.empregados) e
WHERE d.nome = 'Depto Finanças';
-Listar nome, total de empregados e quantos empregados ganham mais de R$
1.500,00
SELECT d.nome, d.total_empregados(), d.numeroEmpregadosGanhamMais(1500) FROM
departamento_tab d
WHERE d.nome = 'Depto Compras'
SELECT d.total_salarios() FROM departamento_tab d
WHERE d.matricula = 1;
SELECT d.total_salarios() from departamento_tab d
where d.nome = 'Depto Finanças';
SELECT d.nome, d.total_salarios() from departamento_tab d;
SELECT d.nome, d.numeroEmpregadosGanhamMais(1000) from departamento_tab d;