III – Oracle 9i
Coleções
Introdução
• Coleção: formada de objetos do mesmo tipo, cada
um com um índice (posição) único dentro da
coleção
– NESTED TABLE (TABLE)
– VARRAY
• NESTED TABLE x VARRAY
– VARRAYS são densos; NESTED TABLES podem ser
esparsas
– VARRAYS são adequados para pequenas coleções;
NESTED TABLES o são para grandes coleções, ou
coleções com tamanho indefinido
Coleções Locais (Transitórias)
e Coleções Persistentes (de
BD)
• É importante levar em conta que os
tratamentos de coleção local e
coleção persistente são distintos
– Por exemplo, uma coleção local é
diretamente indexável, enquanto uma
coleção de BD não o é
Definindo Coleções
Locais
Nested Table:
TYPE tipo IS TABLE OF tipo_elemento
[NOT NULL]
VARRAY:
TYPE tipo IS VARRAY (tamanho_máximo)
OF tipo_elemento [NOT NULL]
Definindo Coleções
Locais - Exemplos
DECLARE
TYPE Calendario IS VARRAY(366) OF DATE
TYPE Nomes IS TABLE OF VARCHAR2(30)
TYPE Emps IS TABLE OF emp.nome%TYPE
TYPE Item IS RECORD (
codigo
VARCHAR2(20),
quantidade
INTEGER)
TYPE Estoque IS VARRAY(250) OF Item
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
Definindo Coleções de BD
CREATE TYPE ListaCursos AS TABLE OF REF Curso
CREATE TYPE Estudante AS OBJECT (
id_num INTEGER(4),
nome VARCHAR2(25),
endereço VARCHAR2(35),
cursos ListaCursos)
CREATE TABLE Estudante_tab OF Estudante
NESTED TABLE cursos STORE AS cursos_tab
Definindo Coleções de BD
(2)
CREATE TYPE Projeto AS OBJECT(
id_projeto
NUMBER(2),
titulo
VARCHAR2(35),
custo
NUMBER(7,2))
CREATE TYPE ListaProjetos AS VARRAY(50) OF Projeto
CREATE TABLE departamento (
dept_id
NUMBER(2),
nome
VARCHAR2(15),
orcamento
NUMBER(11,2),
projetos
ListaProjetos)
Coleções e Procedures
(Funções)
TYPE Equipe IS TABLE OF Empregado
...
PROCEDURE premia (membros IN Equipe)
DECLARE
TYPE EquipeVenda IS VARRAY(25) OF Vendedor;
FUNCTION melhores_vendedores (n INTEGER) RETURN
EquipeVenda IS ...
Iniciando Coleções
DECLARE
meus_cursos ListaCursos;
BEGIN
meus_cursos := ListaCursos(’Econ 2010’, ’Mat 3401’, ’Geren 3100’,
’Hist 3141’, ’Mark 3312’, ’Ingl 2005’);
...
END;
DECLARE
conjunto_projetos ListaProjetos;
BEGIN
conjunto_projetos :=
ListaProjetos(Projeto(1, ’Gerar Novo Relatório’, 3250),
Projeto(2, ’Conferir Folha Pagamento’, 12350),
Projeto(3, ’Checar Credores’, 1425));
...
END;
Iniciando Coleções (2)
DECLARE
TYPE Clientela IS VARRAY(100) OF Cliente;
vips Clientela := Clientela(); /* coleção vazia <> coleção nula
(ou não iniciada) */
BEGIN
IF vips IS NOT NULL THEN /* verdade */
...
END IF;
END;
Note:
1) Coleção iniciada: vazia, não-vazia
2) Coleção não-iniciada: nula
Iniciando Coleções (3)
BEGIN
INSERT INTO novatos
VALUES (Estudante(5035, ’Janet Alvarez’, ’122 Broad
St’, ’FT’, ListaCursos(’Econ 2010’, ’Acct 3401’, ’Mgmt 3100’,
...)));
Indexando Elementos de
Coleções Locais
DECLARE
TYPE ListaNomes IS TABLE OF VARCHAR2(15);
nomes ListaNomes := ListaNomes(’J Hamil’, ’D Caruso’, ’R
Singh’, ...);
i BINARY_INTEGER;
BEGIN
...
IF nomes(i) = ’J Hamil’ THEN
...
END IF;
END;
Indexando Elementos de
Coleções Locais (2)
DECLARE
TYPE ListaNomes IS TABLE OF VARCHAR2(15);
nomes ListaNomes := ListaNomes(’J Hamil’, ’D Piro’, ’R
Singh’, ...);
i BINARY_INTEGER;
BEGIN
...
verify_nome(nomes(i)); -- call procedure
Indexando Elementos de
Coleções Locais (3)
DECLARE
TYPE Equipe IS VARRAY(20) OF Empregado;
FUNCTION novos_contratados (data_contrato DATE)
RETURN Equipe IS ...
BEGIN
staffer := novos_contratados(’16-OCT-96’)(3);
...
END;
Atribuindo Coleções
DECLARE
TYPE Clientela IS VARRAY(100) OF Cliente;
TYPE Vips IS VARRAY(100) OF Cliente;
grupo1 Clientela := Clientela(...);
grupo2 Clientela := Clientela(...);
grupo3 Clientela; /* Atomicamente nula */
grupo4 Vips := Vips(...);
BEGIN
grupo2 := grupo1;
grupo1 := NULL; /* Atomicamente nula */
grupo3 := grupo2; /* Erro de compilação: por quê? */
Atribuindo Elementos de
Coleções
DECLARE
TYPE NumList IS TABLE OF INTEGER;
nums NumList;
BEGIN
nums(1) := 10;
-- COLLECTION_IS_NULL: ERROR!
nums := NumList(10,20,30);
nums(1) := ASCII(’A’);
nums(2) := 10 * nums(1);
nums(’B’) := 15;
-- VALUE_ERROR
nums(4) := 40;
-- SUBSCRIPT_BEYOND_COUNT
END;
Comparando Coleções
DECLARE
TYPE Clientela IS TABLE OF Cliente;
grupo1 Clientela := Clientela(...);
grupo2 Clientela := Clientela(...);
grupo3 Clientela;
BEGIN
...
IF grupo1 = grupo2 THEN -- erro de compilação
...
END IF;
IF grupo3 IS NULL THEN ...
…
END IF;
END;
Manipulando Coleções - Nested
Tables
SQL> CREATE TYPE Curso AS OBJECT (
2 n_curso NUMBER(4),
3 titulo VARCHAR2(35),
4 creditos NUMBER(1));
SQL> CREATE TYPE ListaCursos AS TABLE OF Curso;
SQL> CREATE TABLE departamento (
2 nome VARCHAR2(20),
3 diretor VARCHAR2(20),
4 secretaria VARCHAR2(20),
5 cursos ListaCursos)
6 NESTED TABLE cursos STORE AS cursos_tab;
Manipulando Coleções Nested Tables (2)
BEGIN
INSERT INTO departamento
VALUES(’Psicologia’, ’Irene Santos’, ’R. 1o. Maio 133’,
ListaCursos(Curso(1000, ’ Psicologia Gerak’, 5),
Curso(2100, ’Psicologia Experimental’, 4),
Curso(4410, ’Psicologia Especial’, 4)));
INSERT INTO departamento
VALUES(’Línguas’, ’Mara Silva’, ’R. 7 de Setembro 205’,
ListaCursos(Curso(1002, ’Escrita Oficial’, 3),
Curso(2020, ’Literatura e Cinema’, 4),
Curso(2418, ’Ficção Científica Moderna’, 3),
Curso(3870, ’Contos’, 5)));
END;
Manipulando Coleções Nested Tables (3)
DECLARE
novos_cursos ListaCursos :=
ListaCursos(Curso(1002, ’Escrita Oficial’, 3),
Curso(2020, ’Literatura e Cinema’, 4),
Curso(4725, Português Instrumental’, 5));
BEGIN
UPDATE departamento
SET cursos = novos_cursos WHERE nome = ’Línguas’;
END;
Manipulando Coleções Nested Tables (4)
DECLARE
psic_cursos ListaCursos;
BEGIN
SELECT d.cursos INTO psic_cursos FROM departamento d
WHERE d.nome = ’Psicologia’;
...
END;
Pergunta: Por que não BULK COLLET INTO?
Manipulando Coleções - VARRAY
SQL> CREATE TYPE Projeto AS OBJECT (
2 project_no
NUMBER(2),
3 titulo
VARCHAR2(35),
4 custo
NUMBER(7,2));
SQL> CREATE TYPE ListaProjetos AS VARRAY(50) OF Projeto;
SQL> CREATE TABLE departamento (
2 dept_id
NUMBER(2),
3 nome
VARCHAR2(15),
4 orcamento NUMBER(11,2),
5 projetos ListaProjetos);
Manipulando Coleções – VARRAY (2)
BEGIN
INSERT INTO departamento
VALUES(50, ’Manutenção’, 925300,
ListaProjetos(Projeto(1, ’Reparar Vazamentos’, 2850),
Projeto(2, ’Lavar Janelas’, 975),
Projeto(3, ’Manutenção Ventiladores’, 1125)));
INSERT INTO departamento
VALUES(60, ’Segurança’, 750400,
ListaProjetos(Projeto(1, ’Transportar Pagamentos’, 9000),
Projeto(2, ’Investigar Roubo Equipamentos’, 2750),
Projeto(3, ’Verificar Saida de Emergência’,1900)));
END;
Manipulando Coleções –
VARRAY (3)
DECLARE
novos_projetos ListaProjetos :=
ListaProjetos(Projeto(1, ’Transportar Pagamentos’,9000),
Projeto(2, ’Analisar Alarmes’, 2750), Projeto(3, ’Verificar
Extintores’,1900)));
BEGIN
UPDATE departamento
SET projetos = novos_projetos WHERE dept_id = 60;
END;
Manipulando Coleções –
VARRAY (4)
DECLARE
meus_projetos ListaProjetos;
BEGIN
SELECT d.projetos INTO meus_projetos FROM
departamento d
WHERE d.dept_id = 30;
...
END;
Manipulando Coleções –
Função TABLE
BEGIN
INSERT INTO
TABLE(SELECT d.cursos FROM departamento d WHERE d.nome
= ’Línguas’)
VALUES(1234, ’Inglês Instrumental’, 4);
END;
DECLARE
ajuste INTEGER DEFAULT 1;
BEGIN
UPDATE TABLE(SELECT cursos FROM departamento
WHERE nome = ’Psicologia’) c
SET c.creditos = c.creditos + ajuste
WHERE c.n_curso IN (2200, 3540);
END;
Manipulando Coleções –
Função TABLE (2)
DECLARE
meu_n_curso
NUMBER(4);
meu_titulo VARCHAR2(35);
BEGIN
SELECT c.n_curso, c.titulo INTO meu_n_curso, meu_titulo
FROM TABLE(SELECT d.cursos FROM departamento d
WHERE d.nome = ’Línguas’) c
WHERE c.n_curso = 1002;
...
END;
Manipulando Coleções –
Função TABLE (3)
BEGIN
DELETE TABLE(SELECT d.cursos FROM departamento d
WHERE d.nome = ’Línguas’) c
WHERE c.creditos = 5;
END;
Manipulando Coleções –
Função TABLE (4)
DECLARE
meu_custo NUMBER(7,2);
meu_titulo VARCHAR2(35);
BEGIN
SELECT p.custo, p.titulo INTO meu_custo, meu_titulo
FROM TABLE(SELECT d.projetos FROM departamento d
WHERE d.dept_id = 50) p
WHERE p.project_no = 4;
...
END;
Manipulando Coleções Locais Varredura
FOR i IN meus_projetos.FIRST..meus_projetos.LAST LOOP
IF meus_projetos(i).project_no = proj_no THEN
IF novo_titulo IS NOT NULL THEN
meus_projetos(i).titulo := novo_titulo;
END IF;
IF novo_custo IS NOT NULL THEN
meus_projetos(i).custo := novo_custo;
END IF;
EXIT;
END IF;
END LOOP;
UPDATE departamento SET projetos = meus_projetos
WHERE dept_no = 03;
END update_project;
Manipulando Coleções Locais
DECLARE
revisado ListaCursos := ListaCursos(Curso(1002, ’Escrita Oficial’, 3),
Curso(2020, ’Literatura e Cinema’, 4),
Curso(3010, ’Gramática Portuguesa’, 3),
Curso(3550, ’Realismo e Naturalismo’, 4));
num_diferentes INTEGER;
BEGIN
SELECT COUNT(*) INTO num_diferentes
FROM TABLE(CAST(revisado AS ListaCursos)) novo,
TABLE(SELECT d.cursos FROM departamento d
WHERE d.nome = ’Línguas’) AS antigo
/* Produto Cartesiano */
WHERE novo.n_curso = antigo.n_curso AND
(novo.titulo != antigo.titulo OR novo.creditos !=
antigo.creditos);
END;
Coleções Multidimensionais VARRAY
declare
type t1 is varray(10) of integer;
type nt1 is varray(10) of t1;
/* VARRAY
multidimensional */
va t1 := t1(2,3,5);
-- Inicia VARRAY multidimensional
nva nt1 := nt1(va, t1(55,6,73), t1(2,4), va);
i integer;
begin
-- Acesso multidimensional
i := nva(2)(3);
-- recupera o valor 73
Coleções Multidimensionais –
VARRAY (2)
-- adiciona novo elemento a nva
nva.extend;
nva(5) := t1(56, 32);
-- substitui um elemento de primeiro nível (uma dimensão)
nva(4) := t1(45,43,67,43345);
-- substitui um elemento único
nva(4)(4) := 1;
-- troca 43345 por 1
-- acrescente um novo elemento ao 4o. Elemento VARRAY
-- e armazena 89 nele.
nva(4).extend;
nva(4)(5) := 89;
end;
Coleções Multidimensionais Nested Tables
declare
type tb1 is table of varchar2(20);
type ntb1 is table of tb1; /* tabela com elementos
também tabelas */
type tv1 is varray(10) of integer;
type ntb2 is table of tv1; /* tabela com elementos
VARRAY */
vtb1 tb1 := tb1('one', 'three');
vntb1 ntb1 := ntb1(vtb1);
vntb2 ntb2 :=ntb2(tv1(3,5), tv1(5,7,3)); /* tabela com
elementos VARRAY */
Coleções Multidimensionais Nested Tables (2)
begin
vntb1.extend;
vntb1(2) := vntb1(1);
-- apaga o primeiro elemento de vntb1
vntb1.delete(1);
/* apaga a primeira cadeia da segunda tabela na nested
table */
vntb1(2).delete(1);
end;
/
Métodos de Coleções
EXISTS
IF cursos.EXISTS(i) THEN cursos(i) := novo_curso;
END IF;
COUNT
IF projetos.COUNT = 25 THEN ...
LIMIT
IF projetos.LIMIT = 25 THEN
Métodos de Coleções (2)
FIRST e LAST
IF cursos.FIRST = cursos.LAST THEN ... /* só um
elemento */
FOR i IN cursos.FIRST..cursos.LAST LOOP ...
PRIOR e NEXT
i:=cursos.FIRST; -- o índice do primeiro elemento
WHILE i IS NOT NULL LOOP
...
i:=cursos.NEXT(i);
END LOOP;
Métodos de Coleções (3)
EXTEND
cursos.EXTEND; -- acrescenta 1 elemento NULL
cursos.EXTEND(5,1); -- acrescenta 5 cópias do elemento 1
cursos.EXTENDS(3); -- acrescenta 3 elementos NULL
TRIM
cursos.TRIM(2); -- remove a posição 2
cursos.TRIM; -- remove a última posição
DELETE
cursos.DELETE(2); -- remove a posição 2
cursos.DELETE(2,6); -- remove as posições de 2 a 6
projetos.DELETE; -- remove toda a coleção
Atenção: EXTEND, TRIM e DELETE ignoram as posições removidas. Por exemplo, se uma coleção
foi criada com 10 elementos, e a posição 10 foi removida por TRIM ou DELETE, EXTEND
acrescenta 1 elemento NULL na posição 11.
As demais funções (COUNT, LAST, …) não ignoram as posições removidas. Para o exemplo
anterior, e imediatamente depois da posição 10 removida, COUNT = 9, LAST = 9, etc.
Exceções
COLLECTION_IS_NULL
Operando em uma coleção atomicamente nula.
NO_DATA_FOUND
O índice se refere a um elemento que foi apagado.
SUBSCRIPT_BEYOND_COUNT
O índice excede o número de elementos na coleção.
SUBSCRIPT_OUTSIDE_LIMIT
O índice está fora do intervalo permitido.
VALUE_ERROR
O índice é nulo ou não conversível para um inteiro.
Multi Nested Types
CREATE OR REPLACE TYPE Programa_objtyp
CREATE OR REPLACE TYPE Aluno_objtyp
CREATE OR REPLACE TYPE Turma_objtyp
CREATE OR REPLACE TYPE Erro_objtyp AS OBJECT(
ErroNo NUMBER,
Tipo NUMBER(2),
Descricao VARCHAR2(20),
VlDescontoNota NUMBER(2,2),
Programa_ref REF Programa_objtyp
)
Multi Nested Types (2)
CREATE OR REPLACE TYPE ErroList_ntabtyp AS TABLE OF
Erro_objtyp
CREATE OR REPLACE TYPE Programa_objtyp AS OBJECT(
ProgramaNo NUMBER,
Nome VARCHAR2(20),
DtcEntrega DATE,
Nota NUMBER(2),
ErroList_ntab ErroList_ntabtyp,
Aluno_ref REF Aluno_objtyp
)
CREATE OR REPLACE TYPE ProgramaList_ntabtyp AS TABLE OF
Programa_objtyp
Multi Nested Types (3)
CREATE OR REPLACE TYPE Pessoa_objtyp AS OBJECT(
Nome VARCHAR2(40),
DtcNascimento DATE
)
CREATE OR REPLACE TYPE Aluno_objtyp UNDER Pessoa_objtyp AS
OBJECT(
Matricula NUMBER,
ProgramaList_ntab ProgramaList_ntabtyp,
Turma_ref REF Turma_objtyp
)
CREATE OR REPLACE TYPE AlunoList_ntabtyp AS TABLE OF
Aluno_objtyp
/*
Multi Nested Types (4)
Turma_objtyp
AlunoList_ntab
ProgramaList_ntab
ErroList_ntab
*/
CREATE OR REPLACE TYPE Turma_objtyp AS OBJECT(
Codigo NUMBER,
Horario DATE,
Vagas NUMBER(2),
Periodo_inicial DATE,
Periodo_final DATE,
AlunoList_ntab AlunoList_ntabtyp
)
CREATE TABLE Turma_objtab OF Turma_objtyp (
PRIMARY KEY(Codigo))
NESTED TABLE AlunoList_ntab STORE AS
TurmaAluno_ntab
((PRIMARY KEY (NESTED_TABLE_ID,
Matricula))
ORGANIZATION INDEX COMPRESS
NESTED TABLE ProgramaList_ntab
STORE AS innerAlunoProg_ntab
((PRIMARY KEY
(NESTED_TABLE_ID, ProgramaNo))
ORGANIZATION INDEX COMPRESS
NESTED TABLE ErroList_ntab
STORE AS innerProgErro_ntab
((PRIMARY KEY
(NESTED_TABLE_ID, ErroNo))
ORGANIZATION INDEX
COMPRESS))
)