1. Introdução
Este artigo encerra a série a respeito da utilização de vetores no Oracle, apresentando a maneira pela qual um vetor pode ser armazenado como uma coluna de tabela.
2. Armazenando um Vetor em Tabelas Oracle
Além de conter colunas de tipos básicos, como CHAR, NUMBER ou DATE, uma tabela Oracle também pode ser composta por colunas que representam tipos definidos pelo usuário no banco de dados. É possível, por exemplo, armazenar um objeto ou um vetor (ou mesmo um vetor de objetos!) numa tabela. Este artigo demonstra a maneira pela qual vetores (tipo VARRAY) podem (i) ser definidos como colunas de tabelas, (ii) manipulados através de comandos DML e (iii) ter o seu conteúdo recuperado através da instrução SELECT.
2.1 Definição de uma Coluna de Tabela com o tipo Vetor
Para facilitar a compreensão dos conceitos apresentados neste artigo, considere o seguinte exemplo: uma tabela T_ALUNO que armazena a matrícula, o nome e as notas obtidas em cinco exames realizados por alunos de um curso de PL/SQL. Neste exemplo, as notas dos cinco exames serão armazenadas numa coluna do tipo vetor.
O primeiro passo para a definição da tabela T_ALUNO consiste na criação, no banco de dados, do tipo responsável por armazenar as notas dos exames. Este tipo será um VARRAY com 5 posições para dados NUMBER. O comando a seguir ilustra a sintaxe utilizada para a execução desta tarefa (OBS: consulte o primeiro e segundo artigos da série para conhecer as características do tipo VARRAY) :
CREATE OR REPLACE TYPE tVETOR IS VARRAY(5) OF NUMBER(4,2);
Agora que o tipo “tVETOR” foi criado, torna-se possível criar a tabela T_ALUNO normalmente, com o uso do comando CREATE TABLE.
CREATE TABLE T_ALUNO (
COD_MATRICULA CHAR(6) PRIMARY KEY,
NOM_ALUNO VARCHAR2(50) NOT NULL,
VET_NOTAS tVETOR
)
/
2.2 Inserção de Dados na Coluna do tipo Vetor
O exemplo a seguir ilustra a maneira pela qual os dados de um aluno e as suas cinco notas podem ser inseridos em T_ALUNO com o uso de um comando INSERT simples.
INSERT INTO T_ALUNO VALUES(‘M00001’,‘JOÃO DA SILVA’, tVetor(7.5, 9.8, 7.0, 8.2, 10.0));
COMMIT;
Neste exemplo, foram inseridos os dados do aluno de matrícula “M00001”, chamado João da Silva. Este aluno obteve nota 7.5 no primeiro exame, 9.8 no segundo exame e 7.0, 8.2 e 10.0, no terceiro, quarto e quinto exames, respectivamente. Veja que para inserir as notas na coluna “VET_NOTAS” foi preciso utilizar a forma padrão para popular vetores (apresentada no primeiro artigo desta série), que consiste na referência ao construtor padrão do tipo do vetor (neste caso, “tVETOR”).
Os exemplos a seguir ilustram a inserção dos dados e das notas de outros 3 alunos. Veja que o aluno de matrícula “M00003” não fez a segunda prova (por isso foi inserido o valor NULL para a nota desta prova).
INSERT INTO T_ALUNO VALUES(‘M00002’,‘AMANDA SOUTO’, tVetor(3.5, 7.2, 6.0, 4.2, 1.5));
INSERT INTO T_ALUNO VALUES(‘M00003’,‘JOSE CORREIA’, tVetor(5.5, NULL, 5.5, 6.5, 7.2));
INSERT INTO T_ALUNO VALUES(‘M00004’,‘REGINA MARIA’, tVetor(9.5, 10.0, 10.0, 9.0, 9.0));
COMMIT;
2.3 Recuperação dos Dados Armazenados na Coluna do tipo Vetor
Felizmente, os dados armazenados numa coluna do tipo vetor podem ser recuperados através de uma instrução SQL. No entanto, é preciso utilizar um operador especial, chamado TABLE. Este operador requer, como operando, uma subquery que é a responsável por retornar os dados armazenados no vetor. O exemplo a seguir ilustra a forma pela qual podemos recuperar a matrícula, o nome e a nota de todos os alunos contidos em T_ALUNO (os resultados são ordenados por matrícula e nota):
SQL> SELECT A.COD_MATRICULA, A.NOM_ALUNO, N.*
FROM t_ALUNO A,
TABLE(SELECT VET_NOTAS FROM T_ALUNO
WHERE COD_MATRICULA=A.COD_MATRICULA) N
ORDER BY 1,3;
COD_MATR NOM_ALUNO COLUMN_VALUE
-------- --------- -------------
M00001 JOÃO DA SILVA 7
M00001 JOÃO DA SILVA 7,5
M00001 JOÃO DA SILVA 8,2
M00001 JOÃO DA SILVA 9,8
M00001 JOÃO DA SILVA 10
M00002 AMANDA SOUTO 1,5
M00002 AMANDA SOUTO 3,5
M00002 AMANDA SOUTO 4,2
M00002 AMANDA SOUTO 6
M00002 AMANDA SOUTO 7,2
M00003 JOSE CORREIA 5,5
M00003 JOSE CORREIA 5,5
M00003 JOSE CORREIA 6,5
M00003 JOSE CORREIA 7,2
M00003 JOSE CORREIA
M00004 REGINA MARIA 9
M00004 REGINA MARIA 9
M00004 REGINA MARIA 9,5
M00004 REGINA MARIA 10
M00004 REGINA MARIA 10
Sempre que for desejado recuperar os dados armazenados numa coluna do tipo vetor através de uma consulta SQL, será preciso utilizar o operador TABLE junto com uma subconsulta. Apesar de não ser muito flexível, o operador TABLE tem a vantagem de ser muito simples de usar, podendo ser aplicado em muitos casos do dia-a-dia. No entanto, ocorrerão muitas outras situações onde o desenvolvedor precisará realizar operações mais complexas com o vetor. Neste caso, será preciso construir um subprograma PL/SQL (uma função ou procedure) para tratar adequadamente o conteúdo do VARRAY.
3. Comentários Finais
Este artigo encerrou a série a respeito da utilização de vetores (tipo VARRAY) no Oracle. Os dois primeiros artigos da série apresentaram as características dos vetores Oracle e a maneira pela qual eles podem ser manipulados através de programas PL/SQL. Neste artigo final, foi demonstrado um exemplo em que um vetor foi usado como coluna numa tabela. Para obter maiores informações sobre o assunto, o leitor deve consultar o capítulo que aborda as Coleções (Collections), que faz parte do manual de PL/SQL do SGBD Oracle.
Eduardo Corrêa Gonçalves
Instituto Brasileiro de Geografia e Estatística – IBGE
E-mail: eduardo.correa@ibge.gov.br
Postagens
0 comentários:
Postar um comentário