Utilizando cursores no Oracle

Olá, pessoal!

Neste artigo vamos simular a utilização dos diversos tipos de cursores no Oracle, por final vamos comparar a velocidade destes. Utilizaremos a extração de uma tabela com uma massa de dados de 50 mil registros para que possamos fazer um teste considerável.

01. Criar tabela

Crie a estrutura da tabela

create table TB_PESSOA
(
  IDPESSOA       INTEGER not null,
  NOME           VARCHAR2(40) not null,
  DATANASCIMENTO DATE not null,
  ALTURA         NUMBER(5,2),
  PESO           NUMBER(5,2)
)

02. Inserir registros

Vamos inserir 50 mil registros na tabela para criarmos um volume de dados considerável para nossos testes.

declare

v_id_pessoa tb_pessoa.idpessoa%type;
v_nome      tb_pessoa.nome%type;
v_dt_nascimento tb_pessoa.datanascimento%type;
begin

v_nome := 'Joao da Silva';
v_dt_nascimento := to_date('01/01/1900');

  for dd in 1..50000 loop
  
      
     select nvl(max(p.idpessoa),0)+1
       into v_id_pessoa
       from tb_pessoa p;
     
    insert into tb_pessoa(idpessoa,nome,datanascimento)
                   values(v_id_pessoa,v_nome,v_dt_nascimento);
    
  end loop;

end;


commit;

03. Criação dos objetos

Vamos criar as procedures com as seguintes formas de cursores. Vejam que em todas temos as variáveis v_dt_inicio e v_dt_fim, que farão o controle do tempo de processamento da procedure.
3.1. O cursor contido nesta procedure é o mais usual. O cursor de tipo pré-declarado

Procedure PRC_TST_CURSOR

create or replace procedure prc_tst_cursor is

v_dt_inicio timestamp;
v_dt_fim    timestamp;

cursor cr_pessoa is
select *
  from tb_pessoa;

v_pessoa cr_pessoa%rowtype;

begin

v_dt_inicio := systimestamp;

  open cr_pessoa;
   loop
   fetch cr_pessoa into v_pessoa;
   exit when cr_pessoa%notfound;
     null;
   end loop;
  close cr_pessoa;

v_dt_fim := systimestamp;

dbms_output.put_line('Tempo processamento: '||to_char(v_dt_fim - v_dt_inicio,'dd/mm/yyyy hh24:mi:ss.ff'));

end;

3.2. Esta procedure abaixo contém uma forma de cursor bastante utilizada também. O cursor do tipo for é um dos de mais fácil de utilização.

Procedure PRC_TST_FOR

create or replace procedure prc_tst_for is

v_dt_inicio timestamp;
v_dt_fim    timestamp;

begin

v_dt_inicio := systimestamp;

  for dd in (select p.idpessoa
               from tb_pessoa p) loop

     null;

   end loop;

v_dt_fim := systimestamp;

dbms_output.put_line('Tempo processamento: '||to_char(v_dt_fim - v_dt_inicio,'dd/mm/yyyy hh24:mi:ss.ff'));

end;

3.3. Nesta procedure abaixo, temos a extração da informação através de um bulk collection. Forma menos usual, alguns front end não suportam este tipo de cursor, que é o caso do FORMS.

Procedure PRC_TST_BULK_COUNT

create or replace procedure prc_tst_bulk_count is

v_dt_inicio timestamp;
v_dt_fim    timestamp;

type typ_pessoa is table of tb_pessoa%rowtype;
v_tb_pessoa typ_pessoa;

begin

v_dt_inicio := systimestamp;

  select *
    bulk collect into v_tb_pessoa
    from tb_pessoa;

   for dd in 1..v_tb_pessoa.count loop

     null;

   end loop;


v_dt_fim := systimestamp;

dbms_output.put_line('Tempo processamento: '||to_char(v_dt_fim - v_dt_inicio,'dd/mm/yyyy hh24:mi:ss.ff'));

end;

3.4. Nesta procedure abaixo, temos a variação do cursor de bulk collection, onde a diferença está apenas na execução do loop.

Procedure PRC_TST_BULK_FIRST_LAST

create or replace procedure prc_tst_bulk_first_last is

v_dt_inicio timestamp;
v_dt_fim    timestamp;

type typ_pessoa is table of tb_pessoa%rowtype;
v_tb_pessoa typ_pessoa;

begin

v_dt_inicio := systimestamp;

  select *
    bulk collect into v_tb_pessoa
    from tb_pessoa;

   for dd in v_tb_pessoa.first.. v_tb_pessoa.last loop

     null;

   end loop;


v_dt_fim := systimestamp;

dbms_output.put_line('Tempo processamento: '||to_char(v_dt_fim - v_dt_inicio,'dd/mm/yyyy hh24:mi:ss.ff'));

end;

Para a execução das procedures utilizei o PL/SQL Developer, para isso você pode utilizar qualquer programa, SQL*Plus, TOAD, SQL Developer dentre outros.


Chamada da procedure prc_tst_cursor.


Tempo de execução prc_tst_cursor.


Chamada da procedure prc_tst_for.


Tempo de execução prc_tst_for.


Chamada da procedure prc_tst_bulk_count.


Tempo de execução prc_tst_bulk_count.


Chamada da procedure prc_tst_bulk_first_last.


Tempo de execução prc_tst_bulk_first_last.

Procedure	Tempo Execução
PRC_TST_CURSOR	953 Milésimos
PRC_TST_FOR	844 Milésimos
PRC_TST_BULK_COUNT	109 Milésimos
PRC_TST_BULK_FIRST_LAST	109 Milésimos

Conclusões

Velocidade
Com isso, conseguimos provar que a análise do cursor explicito (FETCH) é o mais lento levando 953 Milésimos para sua execução, seguido do cursor implícito (FOR) com 844 Milesimos.
E em primeiro lugar cursores de BULK COLLECT. Independente da forma de extração dos dados de dentro do cursor, da montagem do loop, sendo via COUNT ou via FIRST-LAST, são os mais rápidos ambas as formas levando 109 Milésimospara seu processamento.
Facilidade de Implementação
No ponto de vista de implementação dos cursores, o FOR é os mais simples, bastando ser declarado ao longo do programa. Os cursores de BULK COLLECT vêm segundo lugar, pois necessitam que sejam declarados variáveis baseadas em types, tornando a implementação pouca coisa mais trabalhosa. Já os cursores de FETCH vêm em último lugar. Como devem ser declarados juntamente com as variáveis que receberão os valores, além de que devem abertos ao longo da execução do programa e fechados, sua implementação torna-se mais trabalhosa.
Documentação
Todos os cursores podem ser documentados com facilidade. Por sintaxe o cursor de FETCH deve receber obrigatoriamente um nome em sua declaração, tornando a documentação automática. Os outros cursores também podem ser nomeados, mas não é muito comum de ver esse tipo de coisa.

Conclusão Final

Com base na velocidade, o mais importante, nas facilidades de implementação e documentações. Concluo que em primeiro lugar por conter a execução mais rápida e estar em segundo lugar em facilidade de implementação o cursor de BULK COLLECT é o melhor a ser utilizado para uma massa de dados grande.
Considero também que os outros cursores também são de grande utilização, mas para massas de dados menores.

Fonte: http://imasters.com.br/artigo/12960/oracle/utilizando_cursores_no_oracle/