Lab: Introdução a Cursores no Oracle Database com PL/SQL
Sergio BenderObjetivo
Este laboratório introdutório ensina os conceitos básicos de cursores no Oracle Database utilizando PL/SQL, voltado para iniciantes que não têm experiência com PL/SQL. Você aprenderá:
O que são cursores e sua importância no processamento de consultas SQL.
Diferenças entre cursores explícitos e implícitos.
Como declarar, abrir, buscar e fechar cursores explícitos em PL/SQL.
Boas práticas para gerenciamento de cursores com foco em performance tuning.
Pré-requisitos
Acesso a um banco de dados Oracle (versão 12c ou superior recomendada).
Um usuário com permissões para criar tabelas e executar blocos PL/SQL (ex.: usuário com privilégios de
CREATE TABLEeEXECUTE).Ferramenta para executar comandos SQL e PL/SQL, como SQL*Plus, SQL Developer ou Oracle Enterprise Manager.
(Opcional) Configure o parâmetro
OPEN_CURSORSpara pelo menos 300:SHOW PARAMETER open_cursors; ALTER SYSTEM SET open_cursors = 300 SCOPE=BOTH;
Configuração Inicial
Vamos criar uma tabela simples para usar nos exemplos de cursores. A tabela armazenará dados de funcionários.
Crie a tabela
employees_lab:CREATE TABLE employees_lab ( employee_id NUMBER PRIMARY KEY, first_name VARCHAR2(50), last_name VARCHAR2(50), department_id NUMBER, salary NUMBER(10, 2) );Insira alguns dados de teste:
INSERT INTO employees_lab VALUES (1, 'John', 'Doe', 10, 5000); INSERT INTO employees_lab VALUES (2, 'Jane', 'Smith', 20, 6000); INSERT INTO employees_lab VALUES (3, 'Alice', 'Johnson', 10, 5500); INSERT INTO employees_lab VALUES (4, 'Bob', 'Brown', 30, 7000); COMMIT;Verifique os dados:
SELECT * FROM employees_lab;Saída esperada:
EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_ID SALARY ----------- ---------- --------- ------------- ------ 1 John Doe 10 5000 2 Jane Smith 20 6000 3 Alice Johnson 10 5500 4 Bob Brown 30 7000
Passos Práticos
Passo 1: Entendendo Cursores Implícitos
Cursores implícitos são gerenciados automaticamente pelo Oracle quando você executa uma consulta SQL. O Oracle abre, processa e fecha o cursor sem intervenção do usuário.
Exemplo de Cursor Implícito:
Vamos executar uma consulta simples que usa um cursor implícito internamente.BEGIN FOR rec IN (SELECT first_name, last_name FROM employees_lab WHERE department_id = 10) LOOP DBMS_OUTPUT.PUT_LINE('Nome: ' || rec.first_name || ' ' || rec.last_name); END LOOP; END; /Explicação:
O Oracle cria um cursor implícito para a consulta
SELECT first_name, last_name FROM employees_lab WHERE department_id = 10.O loop
FORitera automaticamente sobre os resultados.O cursor é fechado automaticamente ao final do loop.
Saída esperada:
Nome: John Doe Nome: Alice Johnson
Verificação:
Execute o bloco acima no SQLPlus ou SQL Developer. Certifique-se de que
DBMS_OUTPUTestá habilitado (no SQLPlus, useSET SERVEROUTPUT ON).Observe que você não precisou gerenciar o cursor manualmente.
Passo 2: Trabalhando com Cursores Explícitos
Cursores explícitos são declarados e gerenciados manualmente em PL/SQL, dando mais controle sobre o processamento dos dados. Vamos criar um cursor explícito para listar funcionários com salário acima de 5500.
Declare e Use um Cursor Explícito:
DECLARE CURSOR emp_cursor IS SELECT first_name, last_name, salary FROM employees_lab WHERE salary > 5500; v_first_name employees_lab.first_name%TYPE; v_last_name employees_lab.last_name%TYPE; v_salary employees_lab.salary%TYPE; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO v_first_name, v_last_name, v_salary; EXIT WHEN emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Funcionário: ' || v_first_name || ' ' || v_last_name || ', Salário: ' || v_salary); END LOOP; CLOSE emp_cursor; EXCEPTION WHEN OTHERS THEN IF emp_cursor%ISOPEN THEN CLOSE emp_cursor; END IF; RAISE; END; /Explicação:
CURSOR emp_cursor IS ...: Declara o cursor com a consulta desejada.OPEN emp_cursor: Abre o cursor para processar os dados.FETCH ... INTO: Recupera cada linha do resultado nas variáveis.EXIT WHEN emp_cursor%NOTFOUND: Sai do loop quando não há mais linhas.CLOSE emp_cursor: Fecha o cursor manualmente (essencial para liberar recursos).O bloco
EXCEPTIONgarante que o cursor seja fechado em caso de erro.
Saída esperada:
Funcionário: Jane Smith, Salário: 6000 Funcionário: Bob Brown, Salário: 7000
Verificação:
Execute o bloco PL/SQL e confirme a saída.
Note a importância de fechar o cursor para evitar atingir o limite de
OPEN_CURSORS.
Passo 3: Cursores com Parâmetros
Cursores explícitos podem aceitar parâmetros para tornar as consultas dinâmicas. Vamos criar um cursor que filtra funcionários por departamento.
Cursor com Parâmetro:
DECLARE CURSOR dept_cursor (p_dept_id NUMBER) IS SELECT first_name, last_name FROM employees_lab WHERE department_id = p_dept_id; v_first_name employees_lab.first_name%TYPE; v_last_name employees_lab.last_name%TYPE; BEGIN OPEN dept_cursor(10); LOOP FETCH dept_cursor INTO v_first_name, v_last_name; EXIT WHEN dept_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Departamento 10 - Nome: ' || v_first_name || ' ' || v_last_name); END LOOP; CLOSE dept_cursor; END; /Explicação:
CURSOR dept_cursor (p_dept_id NUMBER): Define um parâmetro para o cursor.OPEN dept_cursor(10): Passa o valor 10 para filtrar o departamento.
Saída esperada:
Departamento 10 - Nome: John Doe Departamento 10 - Nome: Alice Johnson
Verificação:
Execute o bloco e confirme os resultados.
Tente alterar o parâmetro (ex.:
OPEN dept_cursor(20)) e execute novamente para ver os funcionários do departamento 20.
Passo 4: Monitorando o Uso de Cursores (Performance Tuning)
Cursores mal gerenciados podem impactar a performance. Vamos verificar quantos cursores estão abertos pela sua sessão.
Consulta para Monitorar Cursores Abertos:
SELECT s.sid, s.username, st.value as open_cursors FROM v$sesstat st JOIN v$statname sn ON st.statistic# = sn.statistic# JOIN v$session s ON st.sid = s.sid WHERE sn.name = 'opened cursors current' AND s.sid = (SELECT sid FROM v$mystat WHERE rownum = 1);Explicação:
v$sesstatev$statname: Mostram estatísticas da sessão atual.v$mystat: Identifica o SID da sua sessão.
Saída esperada:
SID USERNAME OPEN_CURSORS --- -------- ------------ 123 SCOTT 5O número de cursores abertos será baixo, já que estamos fechando os cursores corretamente.
Simulação de Problema:
Execute o bloco abaixo sem fechar o cursor para simular um vazamento:
DECLARE CURSOR emp_cursor IS SELECT first_name FROM employees_lab; v_first_name employees_lab.first_name%TYPE; BEGIN OPEN emp_cursor; FETCH emp_cursor INTO v_first_name; -- Não fechamos o cursor de propósito END; /Execute a consulta de monitoramento novamente e observe o aumento no número de cursores abertos.
Correção: Modifique o bloco para incluir
CLOSE emp_cursore reexecute.
Boas Práticas de Performance Tuning
Feche Sempre os Cursores Explícitos: Evite vazamentos de cursores que podem levar a
ORA-01000: maximum open cursors exceeded.Use Variáveis de Ligação: Em consultas dinâmicas, use variáveis para promover reutilização de cursores:
SELECT first_name FROM employees_lab WHERE employee_id = :emp_id;Monitore o Uso de Cursores: Use relatórios AWR para verificar "Cursor Cache Hit Ratio" e "Parse Calls".
Ajuste Parâmetros:
Aumente
SESSION_CACHED_CURSORS(ex.: 100) para reduzir soft parsing:ALTER SYSTEM SET session_cached_cursors = 100 SCOPE=BOTH;
Limpeza
Para evitar acumular objetos no banco, remova a tabela criada:
DROP TABLE employees_lab PURGE;
Conclusão
Neste lab, você aprendeu os fundamentos de cursores no Oracle Database:
Cursores implícitos são gerenciados automaticamente e úteis para loops simples.
Cursores explícitos oferecem mais controle, mas exigem gerenciamento manual.
O monitoramento e o fechamento adequado de cursores são essenciais para a performance.
Próximos Passos:
Experimente criar cursores para outras tabelas do seu banco.
Explore o uso de cursores com
BULK COLLECTpara processar grandes volumes de dados de forma mais eficiente.Consulte a documentação oficial do Oracle sobre PL/SQL para aprofundar seus conhecimentos.
Subscribe to my newsletter
Read articles from Sergio Bender directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by