PL / SQL - Cursores

Neste capítulo, discutiremos os cursores em PL / SQL. O Oracle cria uma área de memória, conhecida como área de contexto, para processar uma instrução SQL, que contém todas as informações necessárias para processar a instrução; por exemplo, o número de linhas processadas, etc.

UMA cursoré um ponteiro para esta área de contexto. PL / SQL controla a área de contexto por meio de um cursor. Um cursor contém as linhas (uma ou mais) retornadas por uma instrução SQL. O conjunto de linhas que o cursor mantém é referido como oactive set.

Você pode nomear um cursor para que ele possa ser referido em um programa para buscar e processar as linhas retornadas pela instrução SQL, uma de cada vez. Existem dois tipos de cursores -

Cursores implícitos
Cursores explícitos

Cursores implícitos

Cursores implícitos são criados automaticamente pelo Oracle sempre que uma instrução SQL é executada, quando não há cursor explícito para a instrução. Os programadores não podem controlar os cursores implícitos e as informações nele contidas.

Sempre que uma instrução DML (INSERT, UPDATE e DELETE) é emitida, um cursor implícito é associado a essa instrução. Para operações INSERT, o cursor contém os dados que precisam ser inseridos. Para as operações UPDATE e DELETE, o cursor identifica as linhas que seriam afetadas.

No PL / SQL, você pode se referir ao cursor implícito mais recente como o SQL cursor, que sempre tem atributos como %FOUND, %ISOPEN, %NOTFOUND, e %ROWCOUNT. O cursor SQL tem atributos adicionais,%BULK_ROWCOUNT e %BULK_EXCEPTIONS, projetado para uso com o FORALLdeclaração. A tabela a seguir fornece a descrição dos atributos mais usados -

S.Não	Atributo e descrição
1	%FOUND Retorna TRUE se uma instrução INSERT, UPDATE ou DELETE afetou uma ou mais linhas ou uma instrução SELECT INTO retornou uma ou mais linhas. Caso contrário, retorna FALSE.
2	%NOTFOUND O oposto lógico de% FOUND. Ele retorna TRUE se uma instrução INSERT, UPDATE ou DELETE não afetou nenhuma linha ou uma instrução SELECT INTO não retornou nenhuma linha. Caso contrário, retorna FALSE.
3	%ISOPEN Sempre retorna FALSE para cursores implícitos, porque o Oracle fecha o cursor SQL automaticamente após executar sua instrução SQL associada.
4	%ROWCOUNT Retorna o número de linhas afetadas por uma instrução INSERT, UPDATE ou DELETE, ou retornadas por uma instrução SELECT INTO.

Qualquer atributo do cursor SQL será acessado como sql%attribute_name conforme mostrado abaixo no exemplo.

Exemplo

Estaremos usando a tabela CLIENTES que criamos e usamos nos capítulos anteriores.

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

O programa a seguir atualizará a tabela e aumentará o salário de cada cliente em 500 e usará o SQL%ROWCOUNT atributo para determinar o número de linhas afetadas -

DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE customers 
   SET salary = salary + 500; 
   IF sql%notfound THEN 
      dbms_output.put_line('no customers selected'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected '); 
   END IF;  
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

6 customers selected  

PL/SQL procedure successfully completed.

Se você verificar os registros na tabela de clientes, verá que as linhas foram atualizadas -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2500.00 | 
|  2 | Khilan   |  25 | Delhi     |  2000.00 | 
|  3 | kaushik  |  23 | Kota      |  2500.00 | 
|  4 | Chaitali |  25 | Mumbai    |  7000.00 | 
|  5 | Hardik   |  27 | Bhopal    |  9000.00 | 
|  6 | Komal    |  22 | MP        |  5000.00 | 
+----+----------+-----+-----------+----------+

Cursores explícitos

Cursores explícitos são cursores definidos pelo programador para obter mais controle sobre o context area. Um cursor explícito deve ser definido na seção de declaração do Bloco PL / SQL. Ele é criado em uma instrução SELECT que retorna mais de uma linha.

A sintaxe para criar um cursor explícito é -

CURSOR cursor_name IS select_statement;

Trabalhar com um cursor explícito inclui as seguintes etapas -

Declarando o cursor para inicializar a memória
Abrindo o cursor para alocar a memória
Buscando o cursor para recuperar os dados
Fechando o cursor para liberar a memória alocada

Declarando o Cursor

Declarar o cursor define o cursor com um nome e a instrução SELECT associada. Por exemplo -

CURSOR c_customers IS 
   SELECT id, name, address FROM customers;

Abrindo o Cursor

Abrir o cursor aloca a memória para o cursor e o torna pronto para buscar as linhas retornadas pela instrução SQL nele. Por exemplo, vamos abrir o cursor definido acima da seguinte maneira -

OPEN c_customers;

Buscando o Cursor

Buscar o cursor envolve acessar uma linha por vez. Por exemplo, vamos buscar linhas do cursor aberto acima da seguinte maneira -

FETCH c_customers INTO c_id, c_name, c_addr;

Fechando o Cursor

Fechar o cursor significa liberar a memória alocada. Por exemplo, fecharemos o cursor aberto acima da seguinte maneira -

CLOSE c_customers;

Exemplo

A seguir está um exemplo completo para ilustrar os conceitos de cursores explícitos & minua;

DECLARE 
   c_id customers.id%type; 
   c_name customer.name%type; 
   c_addr customers.address%type; 
   CURSOR c_customers is 
      SELECT id, name, address FROM customers; 
BEGIN 
   OPEN c_customers; 
   LOOP 
   FETCH c_customers into c_id, c_name, c_addr; 
      EXIT WHEN c_customers%notfound; 
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 
   END LOOP; 
   CLOSE c_customers; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

1 Ramesh Ahmedabad  
2 Khilan Delhi  
3 kaushik Kota     
4 Chaitali Mumbai  
5 Hardik Bhopal   
6 Komal MP  
  
PL/SQL procedure successfully completed.