GPO ( Grupo de Profissionais Oracle )
A maior comunidade Oracle do Brasil !

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 performance deles. 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.

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;

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;

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;

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.

Abraços

Share

You may also like...

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *