Cursores
Em alguns casos necessitamos de espaços de armazenamento mais complexos que as variáveis, como uma matriz de informação resultada de uma consulta SQL, neste case se faz necessário o uso de cursores.
Os cursores em PL/SQL podem ser explícitos e implícitos. O PL/SQL declara um cursor implicitamente para toda instrução DML (UPDATE, INSERT, DELETE, SELECT...INTO), incluindo consultas que retornam apenas uma linha. As consultas que retornam mais de uma linha deverão ser declaradas explicitamente.
Cursores explícitos são indicados quando é necessário um controle no processamento do mesmo.
Declarando um Cursor (DECLARE)
Quando declaramos um cursor é associado a ele um nome e a consulta SQL que será processada por este cursor. Assim como as variáveis, os cursores devem ser declarados na seção DECLARE.
O escopo de validade dos cursores é o mesmo de uma variável. Cursores declarados em um bloco externo são acessíveis apenas neste bloco e em qualquer sub-bloco contido neste, porém cursores declarados no sub-bloco não são acessíveis pelo bloco externo.
CURSOR nome_cursor
[(parametro1 tipo
,parametro2 tipo
,...,
,parametroN tipo)]
IS Instrução_SQL;
Figura 1 – Declarando um cursor
Os cursores podem ser definidos com parâmetros e para cada parâmetro devem ser escolhidos um nome e um tipo de dado.
OPEN nome_cursor[(var1,var2,...)];
Figura 2 – Abrindo um cursor
Extraindo dados do Cursor (FETCH)
FETCH nome_cursor INTO [var1,var2,...|record_name];
Figura 3 – Fetch Cursor
Diretrizes
· Inclua o mesmo número de variáveis na cláusula INTO da instrução FETCH do que as colunas na instrução SELECT e certifique-se que os tipos de dados são compatíveis
· Faça a correspondência de cada variável para coincidir com as posições das colunas
· Registros podem ser utilizados. O tipo %ROWTYPE pode ser associado ao cursor ou diretamente a uma tabela. Os campos do cursor devem ser idênticos aos campos do registro usado em quantidade e tipo
O comando CLOSE desativa o cursor e libera o conjunto ativo. Esta etapa permite que o cursor seja reaberto, se necessário, para gerar um outro conjunto ativo.
CLOSE nome_cursor;
Figura 4 – Fechando um cursor
1 DECLARE
2 V_empno NUMBER;
3 V_ename VARCHAR2(100);
4 CURSOR cEmplyee IS
5 SELECT employee_id,first_name
6 FROM EMPLOYEES;
7 rEmployee cEmplyee%ROWTYPE;
8 CURSOR cEmplyeeJob
9 (p_job varchar)
10 IS
11 SELECT first_name
12 FROM EMPLOYEES
13 WHERE Job_id = p_job;
14 BEGIN
15 OPEN cEmplyee;
16 FETCH cEmplyee INTO V_empno, V_ename;
17 DBMS_OUTPUT.PUT_LINE(V_ename);
18 FETCH cEmplyee INTO V_empno, V_ename;
19 DBMS_OUTPUT.PUT_LINE(V_ename);
20 FETCH cEmplyee INTO V_empno, V_ename;
21 DBMS_OUTPUT.PUT_LINE(V_ename);
22 FETCH cEmplyee INTO rEmployee ;
23 DBMS_OUTPUT.PUT_LINE(rEmployee.first_name);
24 CLOSE cEmplyee;
25
26 OPEN cEmplyeeJob('SALESMAN');
27 FETCH cEmplyeeJob INTO V_ename;
28 DBMS_OUTPUT.PUT_LINE(V_ename);
29 FETCH cEmplyeeJob INTO V_ename;
30 DBMS_OUTPUT.PUT_LINE(V_ename);
31 CLOSE cEmplyeeJob;
32
33 OPEN cEmplyeeJob('MANAGER');
34 FETCH cEmplyeeJob INTO V_ename;
35 DBMS_OUTPUT.PUT_LINE(V_ename);
36 FETCH cEmplyeeJob INTO V_ename;
37 DBMS_OUTPUT.PUT_LINE(V_ename);
38 CLOSE cEmplyeeJob;
39 END;
40 /
Exemplo 1 – Cursores
Obs: No exemplo acima, as linhas 8 até 13 mostram a declaração de um cursor com parâmetro e nas linhas 15, 26 e 33 mostra o “open” do cursor. A linha 7 mostra um registro recebendo a estrutura de linha de um cursor (isso poderia ser feito a uma tabela) e as linhas 22 e 23 mostram o fetch para o registro e o uso do valor do registro
Atributos do Cursor Explícito
|
Atributo |
Tipo |
Descrição |
|
%ISOPEN |
Booleano |
Será avaliado para TRUE se o cursor estiver aberto |
|
%NOTFOUND |
Booleano |
Será avaliado para TRUE se a extração mais recente não retornar linha. |
|
%FOUND |
Booleano |
Será avaliado para TRUE se a extração mais recente retornar linha. |
|
%ROWCOUNT |
Numerico |
Será avaliado para o número total de linhas retornadas até o momento. |
Neste primeiro estilo de loop de busca, a sintaxe de loop simples é utilizada para processamento do cursor. Atributos explícitos de cursor são utilizados para controlar o número de vezes que o loop é executado.
1 DECLARE
2 CURSOR cEmpregados IS
3 SELECT first_name FROM employees;
4 aName employees.first_name%TYPE;
5 BEGIN
6 OPEN cEmpregados;
7 LOOP
8 FETCH cEmpregados INTO aName;
9 EXIT WHEN cEmpregados%NOTFOUND;
10 DBMS_OUTPUT.PUT_LINE(aName);
11 END LOOP;
12 CLOSE cEmpregados;
13 END;
14 /
Exemplo 2 – Cursores Loop Simples
O mesmo Exemplo 2 poderia ser escrito utilizando a sintaxe WHILE..LOOP, da seguinte maneira.
1 DECLARE
2 CURSOR cCidades IS
3 SELECT * FROM locations;
4 rCity locations%ROWTYPE;
5 BEGIN
6 OPEN cCidades;
7 FETCH cCidades INTO rCity;
8 WHILE cCidades%FOUND LOOP
9 DBMS_OUTPUT.PUT_LINE(rCity.city||' - '||rCity.state_province);
10 FETCH cCidades INTO rCity;
11 END LOOP;
12 CLOSE cCidades;
13 END;
14 /
Exemplo 3 – Cursores Loop While
1.8 LOOP For X Cursor
Os dois exemplos de LOOP’s descritos anteriormente requerem um processamento explícito de cursor por meio de instruções OPEN, FETCH, CLOSE ver (11.8.1). A programação PL/SQL fornece um tipo de LOOP mais eficiente, que trata implicitamente o processamento de cursor.
1 DECLARE
2 CURSOR cCargos IS
3 SELECT job_title, job_id
4 FROM jobs;
5 BEGIN
6 FOR rCargo IN cCargos LOOP
7 DBMS_OUTPUT.PUT_LINE(rCargo.job_id||' - '||rCargo.job_title);
8 END LOOP;
9 END;
10 /
Exemplo 4 – Cursor FOR
Observações
· O registro rCargo não é declarado, sua declaração é executada implicitamente, recebe o tipo cCargos%ROWTYPE e o seu escopo é apenas o LOOP.
· O cursor cCargos é processado implicitamente, sendo desnecessário os comandos OPEN, FETCH, CLOSE.
Além do registro, o próprio cursor pode ser implicitamente declarado. A consulta SQL geradora do conjunto ativo é apresentada em de parênteses dentro da própria instrução FOR, e neste caso, tanto o registro com o cursor são implicitamente declarados.
1 BEGIN
2 FOR rDepartamento IN (SELECT d.department_id, d.department_name
3 FROM departments d) LOOP
4 DBMS_OUTPUT.PUT_LINE(rDepartamento.department_name);
5 END LOOP;
6 END;
7 /
Exemplo 5 – Cursor FOR Implícito
Existem os cursor implícitos que são criados para processar as instruções INSERT, UPDATE, DELETE, SELECT...INTO e são manipulados a revelia do programador. Neste caso apenas o atributo %ROWCOUTN é interessante para a instrução UPDATE. O cursor implícito é representado pela palavra reservada SQL.
1 BEGIN
2 UPDATE jobs
3 SET MAX_SALARY = MAX_SALARY+100
4 WHERE MAX_SALARY < 9000;
5
6 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' linhas salarios foram atualizadas');
7 END;
8 /
Exemplo 6 – Cursor Implícito
