Hoje, nosso foco está nas variáveis de cursor no Oracle PL/SQL. Este é um recurso avançado que permite manipular dados de maneira mais flexível e adaptável, essencial para cenários que exigem maior controle em consultas dinâmicas. Vamos explorar o conceito e sua aplicação prática.
O que são Variáveis de Cursor?
Imagine que você tem uma caixa mágica que pode se transformar em diferentes tipos de listas, dependendo do que você precisa no momento. Essa é a essência de uma variável de cursor. Diferente dos cursores que você já conhece, que são fixos a uma consulta específica, as variáveis de cursor podem ser associadas a diferentes consultas em tempo de execução.
Declarando Variáveis de Cursor
Existem duas maneiras principais de declarar variáveis de cursor:
- Usando o tipo predefinido SYS_REFCURSOR:
|
1 |
nome_variavel_cursor SYS_REFCURSOR; |
- Usando um tipo REF CURSOR personalizado:
|
1 2 3 |
TYPE nome_tipo is REF CURSOR [RETURN tipo_retorno]; nome_variavel_cursor nome_tipo; |
Vamos ver alguns exemplos práticos usando o schema HR:
|
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 |
DECLARE -- Variável de cursor baseada no SYS_REFCURSOR predefinido cv_emp1 SYS_REFCURSOR; -- Variável de cursor baseada em um tipo REF CURSOR genérico TYPE emp_cursor_type IS REF CURSOR; cv_emp2 emp_cursor_type; -- Variável de cursor baseada em um tipo REF CURSOR forte TYPE dept_cursor_type IS REF CURSOR RETURN departments%ROWTYPE; cv_dept dept_cursor_type; -- Variáveis para armazenar os resultados v_emp_rec employees%ROWTYPE; v_dept_rec departments%ROWTYPE; BEGIN -- Usando cv_emp1 (SYS_REFCURSOR) OPEN cv_emp1 FOR SELECT * FROM employees WHERE department_id = 30; DBMS_OUTPUT.PUT_LINE('Funcionários do departamento 30 (usando SYS_REFCURSOR):'); LOOP FETCH cv_emp1 INTO v_emp_rec; EXIT WHEN cv_emp1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp_rec.employee_id || ': ' || v_emp_rec.first_name || ' ' || v_emp_rec.last_name); END LOOP; CLOSE cv_emp1; -- Usando cv_emp2 (REF CURSOR genérico) OPEN cv_emp2 FOR SELECT first_name, last_name, salary FROM employees WHERE salary > 10000; DBMS_OUTPUT.PUT_LINE('Funcionários com salário > 10000 (usando REF CURSOR genérico):'); LOOP FETCH cv_emp2 INTO v_emp_rec.first_name, v_emp_rec.last_name, v_emp_rec.salary; EXIT WHEN cv_emp2%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp_rec.first_name || ' ' || v_emp_rec.last_name || ': $' || v_emp_rec.salary); END LOOP; CLOSE cv_emp2; -- Usando cv_dept (REF CURSOR forte) OPEN cv_dept FOR SELECT * FROM departments WHERE location_id = 1700; DBMS_OUTPUT.PUT_LINE('Departamentos na localização 1700 (usando REF CURSOR forte):'); LOOP FETCH cv_dept INTO v_dept_rec; EXIT WHEN cv_dept%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_dept_rec.department_id || ': ' || v_dept_rec.department_name); END LOOP; CLOSE cv_dept; END; / |
Neste Exemplo:
- Usamos
cv_emp1(SYS_REFCURSOR) para buscar funcionários do departamento 30. - Usamos
cv_emp2(REF CURSOR genérico) para buscar funcionários com salário superior a 10000. - Usamos
cv_dept(REF CURSOR forte) para buscar departamentos na localização 1700.
Cada variável de cursor é aberta com uma consulta diferente, demonstrando a flexibilidade das variáveis de cursor. Note que:
- O SYS_REFCURSOR (
cv_emp1) pode ser usado com qualquer consulta. - O REF CURSOR genérico (
cv_emp2) também é flexível, mas aqui demonstramos como ele pode ser usado para selecionar apenas algumas colunas. - O REF CURSOR forte (
cv_dept) só pode ser usado com consultas que retornem exatamente a estrutura dedepartments%ROWTYPE.
Este exemplo demonstra como diferentes tipos de variáveis de cursor podem ser usados em situações variadas, proporcionando flexibilidade e segurança de tipo quando necessário.
Cursores Fracos vs. Fortes
- Cursores Fracos: São como canivetes suíços – flexíveis, mas podem ser perigosos se não usados com cuidado. Exemplo:
cv_emp1ecv_emp2no código acima. - Cursores Fortes: São como ferramentas especializadas – mais seguros, mas menos flexíveis. Exemplo:
cv_deptno código acima.
Mais um Exemplo do Uso de Variáveis de Cursor
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DECLARE TYPE emp_cursor_type IS REF CURSOR; cv_emp emp_cursor_type; v_emp_rec employees%ROWTYPE; BEGIN -- Abrir a variável de cursor OPEN cv_emp FOR SELECT * FROM employees WHERE department_id = 30; -- Buscar e exibir dados LOOP FETCH cv_emp INTO v_emp_rec; EXIT WHEN cv_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp_rec.first_name || ' ' || v_emp_rec.last_name); END LOOP; -- Fechar a variável de cursor CLOSE cv_emp; END; |
Neste exemplo, estamos buscando todos os funcionários do departamento 30.
Cuidados e Boas Práticas
- Sempre feche suas variáveis de cursor após o uso.
- Você pode reabrir uma variável de cursor sem fechá-la, mas é uma boa prática fechá-la antes de reabrir.
- Cuidado com o uso de atributos de cursor após fechar a variável.
Veja um exemplo que ilustra esses pontos:
|
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 |
DECLARE TYPE emp_cursor_type IS REF CURSOR; cv_emp emp_cursor_type; v_emp_rec employees%ROWTYPE; v_count NUMBER; BEGIN -- Primeira consulta OPEN cv_emp FOR SELECT * FROM employees WHERE department_id = 30; -- Processar e contar resultados v_count := 0; LOOP FETCH cv_emp INTO v_emp_rec; EXIT WHEN cv_emp%NOTFOUND; v_count := v_count + 1; END LOOP; DBMS_OUTPUT.PUT_LINE('Funcionários no departamento 30: ' || v_count); CLOSE cv_emp; -- Tentar acessar atributo após fechar (isso causará um erro) -- DBMS_OUTPUT.PUT_LINE('Total processado: ' || cv_emp%ROWCOUNT); -- Reabrir para uma nova consulta OPEN cv_emp FOR SELECT * FROM employees WHERE department_id = 50; -- Processar novamente v_count := 0; LOOP FETCH cv_emp INTO v_emp_rec; EXIT WHEN cv_emp%NOTFOUND; v_count := v_count + 1; END LOOP; DBMS_OUTPUT.PUT_LINE('Funcionários no departamento 50: ' || v_count); CLOSE cv_emp; END; |
Variáveis de Cursor com Parâmetros
As variáveis de cursor podem usar parâmetros, tornando-as ainda mais flexíveis:
|
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 |
DECLARE TYPE emp_cursor_type IS REF CURSOR; cv_emp emp_cursor_type; v_emp_rec employees%ROWTYPE; v_dept_id employees.department_id%TYPE; BEGIN v_dept_id := 30; OPEN cv_emp FOR SELECT * FROM employees WHERE department_id = v_dept_id; -- Processar resultados LOOP FETCH cv_emp INTO v_emp_rec; EXIT WHEN cv_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp_rec.first_name || ' ' || v_emp_rec.last_name); END LOOP; CLOSE cv_emp; -- Mudar o departamento e reabrir v_dept_id := 50; OPEN cv_emp FOR SELECT * FROM employees WHERE department_id = v_dept_id; -- Processar novos resultados LOOP FETCH cv_emp INTO v_emp_rec; EXIT WHEN cv_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp_rec.first_name || ' ' || v_emp_rec.last_name); END LOOP; CLOSE cv_emp; END; |
Conclusão
As variáveis de cursor são ferramentas poderosas no seu arsenal PL/SQL. Elas oferecem flexibilidade para trabalhar com diferentes consultas dinamicamente, permitindo que você crie código mais adaptável e reutilizável.
Lembre-se:
- Use cursores fracos para máxima flexibilidade, mas com cuidado.
- Use cursores fortes quando precisar de segurança de tipo.
- Sempre feche suas variáveis de cursor após o uso.
- Tenha cuidado ao acessar atributos de cursor após fechar a variável.
- Variáveis de cursor podem ser reutilizadas para diferentes consultas.
Pratique bastante! Experimente criar diferentes tipos de variáveis de cursor, use-as com várias tabelas do schema HR, e veja como elas podem tornar seu código mais dinâmico e eficiente.
