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