Nesse artigo vamos mergulhar profundamente no mundo dos cursores explícitos em Oracle PL/SQL. Preparem-se para artigo detalhado e cheio de exemplos práticos! Se você quer executar os mesmos exemplos desse artigo, certifique-se de criar os objetos como descrito nesse artigo sobre cursores implicitos. Basta clicar aqui.
O que são Cursores Explícitos?
Diferentemente dos cursores implícitos, que são gerenciados automaticamente pelo PL/SQL, os cursores explícitos são definidos e controlados por você, o programador. Eles são como uma ferramenta personalizada que você cria para manipular conjuntos de dados específicos.
Por que usar Cursores Explícitos?
Cursores explícitos são essenciais quando você precisa:
- Processar múltiplas linhas de uma vez
- Ter controle granular sobre o fluxo de dados
- Otimizar o desempenho em consultas complexas
- Passar parâmetros para sua consulta
O Ciclo de Vida de um Cursor Explícito
Trabalhar com cursores explícitos envolve quatro etapas principais:
- Declarar o cursor
- Abrir o cursor
- Buscar dados do cursor
- Fechar o cursor
Vamos explorar cada etapa em detalhes!
1. Declarando um Cursor
A declaração de um cursor define sua estrutura e a consulta associada. A sintaxe é:
|
1 2 |
CURSOR nome_do_cursor [lista_de_parametros] [RETURN tipo_retorno] IS instrucao_select; |
Exemplos práticos:
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Cursor simples CURSOR c_estudantes IS SELECT * FROM estudantes; -- Cursor com parâmetros CURSOR c_estudantes_por_curso(p_id_curso NUMBER) IS SELECT nome FROM estudantes WHERE id_curso = p_id_curso; -- Cursor com cláusula RETURN CURSOR c_instrutores RETURN instrutor%ROWTYPE IS SELECT * FROM instrutor; |
2. Abrindo o Cursor
Abrir o cursor aloca recursos do banco de dados e prepara o conjunto de resultados. A sintaxe é simples:
|
1 |
OPEN nome_do_cursor [(lista_de_parametros)]; |
Exemplo:
|
1 |
OPEN c_estudantes_por_curso(101); |
3. Buscando Dados do Cursor
A busca de dados é feita com o comando FETCH. Você pode buscar em variáveis individuais ou em uma variável de registro:
|
1 2 3 4 5 |
-- Busca em variáveis individuais FETCH nome_do_cursor INTO v_id, v_nome, v_email; -- Busca em uma variável de registro FETCH nome_do_cursor INTO v_registro_estudante; |
Geralmente, usamos um loop para processar todas as linhas:
|
1 2 3 4 5 |
LOOP FETCH c_estudantes INTO v_estudante; EXIT WHEN c_estudantes%NOTFOUND; -- Processamento da linha END LOOP; |
4. Fechando o Cursor
Fechar o cursor libera os recursos alocados:
|
1 |
CLOSE nome_do_cursor; |
Atributos de Cursores Explícitos
Os cursores explícitos têm atributos importantes que nos ajudam a controlar o fluxo:
- %ISOPEN: Retorna TRUE se o cursor estiver aberto.
- %FOUND: Retorna TRUE se a última busca retornou uma linha.
- %NOTFOUND: Retorna TRUE se a última busca não retornou uma linha.
- %ROWCOUNT: Retorna o número de linhas buscadas até o momento.
Exemplo Completo e Detalhado
Vamos ver um exemplo completo que demonstra todas as etapas e usa os atributos do cursor:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
DECLARE -- Declaração do cursor CURSOR c_instrutores IS SELECT primeiro_nome || ' ' || sobrenome nome_instrutor, data_contratacao, salario FROM instrutor ORDER BY data_contratacao; -- Variáveis para armazenar os dados do cursor v_nome_instrutor VARCHAR2(100); v_data_contratacao DATE; v_salario NUMBER; -- Variável para contar instrutores processados v_contagem NUMBER := 0; BEGIN -- Verificar se o cursor está aberto (sempre será FALSE aqui) IF c_instrutores%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('O cursor já está aberto (isso não deveria acontecer aqui)'); ELSE DBMS_OUTPUT.PUT_LINE('O cursor está fechado, como esperado'); END IF; -- Abrir o cursor OPEN c_instrutores; -- Verificar novamente se o cursor está aberto IF c_instrutores%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('Agora o cursor está aberto'); END IF; -- Buscar e processar os dados LOOP FETCH c_instrutores INTO v_nome_instrutor, v_data_contratacao, v_salario; -- Sair do loop se não houver mais linhas EXIT WHEN c_instrutores%NOTFOUND; -- Incrementar o contador v_contagem := v_contagem + 1; -- Processar os dados DBMS_OUTPUT.PUT_LINE('Instrutor #' || v_contagem || ':'); DBMS_OUTPUT.PUT_LINE(' Nome: ' || v_nome_instrutor); DBMS_OUTPUT.PUT_LINE(' Contratado em: ' || TO_CHAR(v_data_contratacao, 'DD/MM/YYYY')); DBMS_OUTPUT.PUT_LINE(' Salário: $' || TO_CHAR(v_salario, '999,999.00')); DBMS_OUTPUT.PUT_LINE('-----------------------------'); -- Mostrar o número de linhas processadas DBMS_OUTPUT.PUT_LINE('Instrutores processados até agora: ' || c_instrutores%ROWCOUNT); END LOOP; -- Mostrar o total de instrutores processados DBMS_OUTPUT.PUT_LINE('Total de instrutores processados: ' || c_instrutores%ROWCOUNT); -- Fechar o cursor CLOSE c_instrutores; -- Tentar fechar novamente (isso causará um erro) BEGIN CLOSE c_instrutores; EXCEPTION WHEN INVALID_CURSOR THEN DBMS_OUTPUT.PUT_LINE('Erro: Tentativa de fechar um cursor já fechado'); END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Erro inesperado: ' || SQLERRM); -- Garantir que o cursor seja fechado em caso de erro IF c_instrutores%ISOPEN THEN CLOSE c_instrutores; END IF; END; |
Este script demonstra:
- Declaração de um cursor complexo com ordenação.
- Verificação do estado do cursor antes e depois de abri-lo.
- Uso de um loop para processar todas as linhas.
- Utilização dos atributos %NOTFOUND e %ROWCOUNT.
- Tratamento de erros, incluindo a tentativa de fechar um cursor já fechado.
- Garantia de que o cursor seja fechado mesmo em caso de erro.
Dicas Avançadas
- Parâmetros em Cursores: Você pode tornar seus cursores mais flexíveis usando parâmetros:
|
1 2 3 4 5 |
CURSOR c_estudantes_por_curso(p_id_curso NUMBER) IS SELECT nome FROM estudantes WHERE id_curso = p_id_curso; -- Uso: OPEN c_estudantes_por_curso(101); |
- Cursores com FOR LOOP: Uma forma mais concisa de processar cursores:
|
1 2 3 |
FOR rec_instrutor IN c_instrutores LOOP DBMS_OUTPUT.PUT_LINE('Nome: ' || rec_instrutor.nome_instrutor); END LOOP; |
- Cursores Aninhados: Você pode usar um cursor dentro de outro para processar dados hierárquicos:
|
1 2 3 4 5 6 7 8 9 10 |
CURSOR c_departamentos IS SELECT * FROM departamentos; CURSOR c_funcionarios(p_dept_id NUMBER) IS SELECT * FROM funcionarios WHERE id_departamento = p_dept_id; FOR rec_dept IN c_departamentos LOOP DBMS_OUTPUT.PUT_LINE('Departamento: ' || rec_dept.nome); FOR rec_func IN c_funcionarios(rec_dept.id) LOOP DBMS_OUTPUT.PUT_LINE(' Funcionário: ' || rec_func.nome); END LOOP; END LOOP; |
Conclusão
Ufa! Chegamos ao fim desta jornada intensa pelos cursores explícitos em PL/SQL. Vimos que eles são ferramentas poderosas que nos dão controle total sobre como processamos conjuntos de dados.
Lembre-se dos pontos-chave:
- Declare seus cursores com cuidado, considerando parâmetros e cláusulas de retorno.
- Sempre abra o cursor antes de usar e feche-o quando terminar.
- Use os atributos do cursor para controlar o fluxo e obter informações úteis.
- Considere usar FOR LOOP para simplificar o processamento de cursores.
- Trate erros adequadamente, garantindo que os cursores sejam sempre fechados.
