Utilizando cursores

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;

Fonte: http://imasters.com.br/artigo/12960/oracle/utilizando-cursores-no-oracle