3- Cursores

Prévia do material em texto

Linguagem Procedural de 
Banco de Dados
PL/SQL
5. Cursores
• Áreas compostas de linhas e colunas em
memória principal;
• Armazena o resultado de uma seleção que
retorna 0 ou mais linhas;
• Levando para dentro do cursor cada linha da
tabela, podemos escrever um programa para
ler e processar o valor de cada linha;
• É possível atualizar ou apagar as linhas na
posição corrente do cursor.
5. Cursores
• Declaração
CURSOR nome_do_cursor (relação_parâmetros)
IS SELECT ... FROM ... WHERE ...
[FOR UPDATE OF colunas]
• Relação de parâmetros
• nome tipo_dado {:= / DEFAULT} valor inicial
– Parâmetros são sempre de entrada
– Não é possível especificar NOT NULL
– Não é possível especificar o tamanho
5. Cursores
• Parâmetros
– Permitem a definição de itens dinâmicos de 
comparação na cláusula WHERE
– Devem ser passados quando o cursor for aberto 
(comandos OPEN ou FOR)
• Claúsula FOR UPDATE
– Utilizada quando dados forem atualizados
– Especifica um bloqueio que será efetuado 
nesse momento e somente será liberado 
quando o cursor for fechado (CLOSE)
5. Cursores
Manipulação de cursores:
• OPEN
– Abre o cursor
• FETCH
–Disponibiliza a linha corrente e posiciona na 
próxima linha do cursor
• CLOSE
– Fecha o cursor
• FOR
– Controla de modo completo o acesso ao cursor, 
substituindo os comandos anteriores
5. Cursores
OPEN:
• Funcionalidade
– Abre o cursor
– Posiciona o ponteiro do cursor para o primeiro 
registro resultante da consulta
• Característica
– O BD não será acessado novamente, pois os 
registros estão em memória principal
• Comando
OPEN nome_do_cursor (relação_parâmetros)
5. Cursores
FETCH:
• Funcionalidade
– Transfere o conteúdo do registro corrente para 
as variáveis correspondentes
– Posiciona o ponteiro do cursor no próximo
registro resultante da consulta
• Comando
FETCH nome_do_cursor 
INTO lista_de_variáveis
- mesmo número 
de variáveis
- mesma seqüência
- tipos de dados 
correspondentes
5. Cursores
CLOSE:
• Funcionalidade
– Fecha o cursor
– Libera a área de memória principal ocupada 
pelo resultado da consulta
• Comando
CLOSE nome_do_cursor
5. Cursores
ATRIBUTOS:
• nome_do_cursor%FOUND
– TRUE: FETCH retorna alguma linha
– Caso contrário, FALSE
– NULL se nenhum FETCH tiver sido executado
• nome_do_cursor%NOTFOUND
– FALSE: FETCH retorna alguma linha
– Caso contrário, TRUE
– NULL se nenhum FETCH tiver sido executado
5. Cursores
ATRIBUTOS:
• nome_do_cursor%ROWCOUNT
– Número de linhas processadas pelo cursor
– 0 se nenhum FETCH tiver sido executado
• nome_do_cursor%ISOPEN
– TRUE: cursor está aberto
– Caso contrário, FALSE
5. Cursores (Estrutura Básica)
DECLARE
CURSOR nome_do_cursor
IS comando SELECT do cursor; 
nome_do_registro nome_do_cursor%RowType;
BEGIN
OPEN nome_do_cursor; 
LOOP
FETCH nome_do_cursor
INTO nome_do_registro;
EXIT WHEN nome_do_cursor%NOTFOUND; 
processos sobre a linha atual do cursor;
END LOOP;
CLOSE nome_do_cursor 
END;
Utilizando um cursor vamos recuperar todos os valores da tabela
Aluno abaixo:
CREATE TABLE ALUNO (
MATRICULA NUMBER(9),
NOME VARCHAR2(30),
SEXO CHAR(1)
);
INSERT INTO aluno VALUES (1001, 'Carlos', 'M');
INSERT INTO aluno VALUES (1002, 'Ana', 'F');
INSERT INTO aluno VALUES (1003, 'Vitória', 'F');
INSERT INTO aluno VALUES (1004, 'Joaquim', 'M');
INSERT INTO aluno VALUES (1005, 'Júlia', 'F’);
INSERT INTO aluno VALUES (1006, null, null);
INSERT INTO aluno VALUES (1007, null, null);
5. Cursores (Exemplo)
5. Cursores (Exemplo)
DECLARE
CURSOR cursor_aluno IS
SELECT matricula, nome, sexo FROM aluno;
variavel_cursor cursor_aluno%Rowtype;
BEGIN
OPEN cursor_aluno;
LOOP
FETCH cursor_aluno INTO variavel_cursor;
EXIT WHEN cursor_aluno%NotFound;
DBMS_OUTPUT.PUT_LINE ('matricula: ' || variavel_cursor. matricula ||
', nome: ' || variavel_cursor.nome ||
', sexo: ' || variavel_cursor.sexo);
END LOOP;
CLOSE cursor_aluno;
END;
/
O programa apagará toda tupla cujo nome e sexo não estejam
preenchidos (null).
5. Cursores (Exemplo)
DECLARE
CURSOR cursor_aluno IS
SELECT matricula FROM aluno
WHERE nome IS NULL AND sexo IS NULL FOR UPDATE;
v_matricula aluno.matricula%type;
BEGIN
OPEN cursor_aluno;
LOOP
FETCH cursor_aluno INTO v_matricula;
EXIT WHEN cursor_aluno%NotFound;
DELETE FROM aluno WHERE matricula = v_matricula;
END LOOP;
CLOSE cursor_aluno;
COMMIT;
END;
/
5. Cursores
Faça um bloco anônimo PL/SQL para atualizar a tabela abaixo,
conforme segue:
• Produtos categoria A deverão ser reajustados em 5%
• Produtos categoria B deverão ser reajustados em 10%
• Produtos categoria C deverão ser reajustados em 15%
CREATE TABLE produto (
codigo NUMBER(4),
categoria CHAR(1),
valor NUMBER(4, 2)
);
INSERT INTO produto VALUES ( 1001, 'A', 7.55 );
INSERT INTO produto VALUES ( 1002, 'B', 9.95 );
INSERT INTO produto VALUES ( 1003, 'C', 3.45 );
5. Cursores
Faça um bloco anônimo PL/SQL para atualizar a tabela abaixo,
conforme segue:
• Produtos categoria A deverão ser reajustados em 5%
• Produtos categoria B deverão ser reajustados em 10%
• Produtos categoria C deverão ser reajustados em 15%
DECLARE
CURSOR cursor_produto IS SELECT * FROM produto;
v_produto produto%rowtype;
BEGIN
FOR v_produto IN cursor_produto LOOP
IF v_produto.categoria = 'A' THEN
UPDATE produto SET valor = valor * 1.05
WHERE codigo = v_produto.codigo;
ELSIF v_produto.categoria = 'B' THEN
UPDATE produto SET valor = valor * 1.10
WHERE codigo = v_produto.codigo;
ELSE
UPDATE produto SET valor = valor * 1.15
WHERE codigo = v_produto.codigo;
END IF;
END LOOP;
END;
/
Trabalho de Casa
Tarefa 1:
Dada a tabela empregado a seguir, faça um bloco anônimo que
insira na tabela maior_salario o Id, o nome completo
(concatenação do prim_nome com o ult_nome), o cargo e o
salario dos empregados com os 10 maiores salários.
Obs.: Se existirem empregados com salários iguais todos
deveram ser listados.
CREATE TABLE maior_salario (
id NUMBER(7) PRIMARY KEY,
nome VARCHAR2(20) ,
cargo VARCHAR2(25),
salario NUMBER(11, 2)
);
CREATE TABLE empregado (
id NUMBER(7),
ult_nome VARCHAR2(20) NOT NULL,
prim_nome VARCHAR2(15),
cargo VARCHAR2(25),
salario NUMBER(11, 2)
);
INSERT INTO empregado VALUES (1, 'Oliveira', 'Gabriela', 'Presidente', 25000);
INSERT INTO empregado VALUES (2, 'Feres', 'Gisele','VP, Operações', 14500);
INSERT INTO empregado VALUES (3, 'Nogueira', 'Wilson','VP, Vendas', 14000);
INSERT INTO empregado VALUES (4, 'Queiroz', 'Marcus', 'VP, Financeiro', 14500);
INSERT INTO empregado VALUES (5, 'Rodrigues', 'Alberto','VP, Administrativo', 15500);
INSERT INTO empregado VALUES (6, 'Ugarte', 'Marlene', 'Gerente de Almoxarifado', 12000);
INSERT INTO empregado VALUES (7, 'Machado', 'Roberto', 'Gerente de Almoxarifado', 12500);
INSERT INTO empregado VALUES (8, 'Bastos', 'Bernardo','Gerente de Almoxarifado', 11000);
INSERT INTO empregado VALUES (9, 'Camargo', 'Antonio','Gerente de Almoxarifado', 13000);
INSERT INTO empregado VALUES (10, 'Hubert', 'Marta','Gerente de Almoxarifado', 13070);
INSERT INTO empregado VALUES (11, 'Margarida', 'Carla','Representante de Vendas', 14000);
INSERT INTO empregado VALUES (12, 'Guimarães', 'Henrique','Representante de Vendas', 14900);
INSERT INTO empregado VALUES (13, 'Sedeghi', 'Yasmin','Representante de Vendas', 15150);
INSERT INTO empregado VALUES (14, 'Laura', 'Maria', 'Representante de Vendas', 15250);
INSERT INTO empregado VALUES (15, 'Dumas', 'Andre','Representante de Vendas', 14500);
INSERT INTO empregado VALUES (16, 'Madeira', 'Elena', 'Almoxarife', 14000);
INSERT INTO empregado VALUES (17, 'Silva', 'George', 'Almoxarife', 9400);
INSERT INTO empregado VALUES (18, 'Nozaki', 'Arnaldo','Almoxarife', 12000);
INSERT INTO empregado VALUES (19, 'Mariano', 'Vitor','Almoxarife', 7950);
INSERT INTO empregado VALUES (20, 'Newman', 'Claudia','Almoxarife', 7500);
INSERT INTO empregado VALUES (21, 'Martins', 'Alexander', 'Almoxarife', 8500);
INSERT INTO empregado VALUES (22, 'Chang', 'Eduardo','Almoxarife', 8000);
INSERT INTO empregado VALUES (23, 'Pires', 'Rita', 'Almoxarife',7950);
INSERT INTO empregado VALUES (24, 'Dantas', 'Beatriz','Almoxarife', 8600);
INSERT INTO empregado VALUES (25, 'Schwartz', 'Silvia','Almoxarife', 11000);
COMMIT;
Trabalho de Casa
Tarefa 2:
A empresa XYZ deseja implementar um sistema de bonificação para seus clientes e 
deseja atribuir notas baseado em seus consumos, seguindo as regras a seguir:
• Consumo inferior a 100 --> nota C
• Consumo entre 100 e 150 --> nota B
• Consumo superior a 150 --> nota A
• Segue a baixo o script de criação da tabela CLIENTES:
Crie um bloco anônimo que seja capaz de atualizar o status de todos os clientes da 
empresa XYZ.
CREATE TABLE cliente(
id NUMBER,
nome VARCHAR2(100),
consumo NUMBER(6,2),
status CHAR(1)
);
INSERT INTO cliente VALUES (100, 'Ana', 210, null);
INSERT INTO cliente VALUES (101, 'Carlos', 80, null);
INSERT INTO cliente VALUES (102, 'Bia', 77, null);
INSERT INTO cliente VALUES (103, 'Mauro', 110, null);
INSERT INTO cliente VALUES (104, 'Zeca', 10, null);