Programando com cursores PL/SQL

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (2)  (0)

Os livros disponíveis no mercado voltados para programação Oracle, inclusive a documententação oficial, são abrangentes e neles contém uma grande quantidade de informações, que num primeiro momento podem dificultar o aprendizado da linguagem PL/SQL. Neste artigo, sem dúvida, temos apenas o essencial para a iniciação a programação de cursores PL/SQL, para que esta experiência seja rápida e direta.

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.

 

Abrindo um Cursor (OPEN)

 
        O comando OPEN abre o cursor, executa a consulta associada a ele e gera o conjunto ativo, que consiste de todas as linhas que atendem os critérios de pesquisa da consulta associada ao cursor. Para gerenciar o conjunto ativo existe um ponteiro que registra qual linha está passível do comando FETCH. Após o OPEN o FETCH atuará sobre a primeira linha do conjunto ativo.

 

 

OPEN nome_cursor[(var1,var2,...)];

 

Figura 2 – Abrindo um cursor

 

 Extraindo dados do Cursor (FETCH)

 
          Extair os dados do cursor é o evento onde os dados da linha atual do conjunto ativo são copiados para variáveis ou registros e a cada FETCH realizado, o ponteiro passará a apontar para a linha seguinte do conjunto ativo.

 

 

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

 
Quando anexados ao nome do cursor, esses atributos retornam informações úteis sobre a execução de uma instrução de manipulação de dados.

 

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.

 

LOOP Simples X Cursor

 
 

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

 

LOOP While X Cursor

 
 

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.

 

LOOP For Implícitos

 
 

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

 

Cursores Implícitos

 
 

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

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?