[Muca]

Boa Tarde senhores,

Aqui na empresa onde trabalho, utilizamos o Firebird, porem surgiu a necessidade de desenvolver nosso sistema também para o Oracle.
Estou iniciando agora minhas pesquisas no Oracle e ainda desconheço grande parte.

Vamos ao problema:

Nas minhas procedures no Firebird, é bastante utilizado o FOR SELECT, sendo assim que tipo de comando posso utilizar no ORACLE que desempenhe a mesma função de um FOR SELECT??

Utilizando ORACLE 10g Express Edition.

Obrigado

Samuel

[fsitja]

Olá,

Desculpe, mas não conheço o Firebird de perto. O que faz um “for select” do Firebird, o que ele implementa?

Se for algo como um cursor for loop do Oracle, o ideal seria implementar com bulk collect usando arrays no Oracle, pois fica muito mais rápido do que fazer linha-a-linha a operação.

Exemplo de bloco PL/SQL anônimo com bulk collect e cursor fazendo update:


Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as FSITJA

SQL>
SQL> set serveroutput on
SQL> create table t1 (id_t1 number(10) primary key, tipo varchar2(20));

Table created
SQL> insert into t1 (id_t1, tipo) values (1, 'X');

1 row inserted
SQL> insert into t1 (id_t1, tipo) values (2, 'X');

1 row inserted
SQL> insert into t1 (id_t1, tipo) values (3, 'X');

1 row inserted
SQL> insert into t1 (id_t1, tipo) values (4, 'X');

1 row inserted
SQL> insert into t1 (id_t1, tipo) values (5, 'X');

1 row inserted
SQL> commit;

Commit complete
SQL> create or replace type typ_t1 as object (id_t1 number(10),
2 tipo varchar2(20))
3 /

Type created
SQL> create or replace type typ_tab_t1 is table of typ_t1
2 /

Type created
SQL> declare
2 tab_t1 typ_tab_t1;
3 cursor cur_t1 is
4 select typ_t1(id_t1, tipo) from t1;
5 begin
6 open cur_t1;
7 loop
8 fetch cur_t1 bulk collect
9 into tab_t1 limit 2;
10 update t1 set t1.tipo = 'Y'
11 where t1.id_t1 in (select t.id_t1 from table(tab_t1) t);
12 dbms_output.put_line('Update em ' || sql%rowcount || ' linhas.');
13 commit;
14 exit when cur_t1%notfound;
15 end loop;
16 dbms_output.put_line('Total de linhas atualizadas: ' || cur_t1%rowcount);
17 close cur_t1;
18 end;
19 /

Update em 2 linhas.
Update em 2 linhas.
Update em 1 linhas.
Total de linhas atualizadas: 5

PL/SQL procedure successfully completed
SQL> drop type typ_tab_t1;

Type dropped
SQL> drop type typ_t1;

Type dropped
SQL> select *
2 from t1;

  ID_T1 TIPO

---

      1 Y
      2 Y
      3 Y
      4 Y
      5 Y

SQL>


Num código de produção você vai querer subir o LIMIT para algo entre 100 e 1.000 por exemplo. Mais que isso reduz o ganho de performance relativo pois aumenta muito o consumo de memória na PGA.

Lá você também criaria o código PL/SQL como uma procedure (a parte do “declare” e “begin” até o “end;”

Criação de type, tabela e tudo mais é só para setup de ambiente, para ilustrar no exemplo.

O que precisar, abuse da documentação da Oracle, lá tem muita informação útil.

http://tahiti.oracle.com

Para você començando é indispensável ver o livro de Oracle Database Concepts:
http://www.oracle.com/pls/db112/to_pdf? … e10713.pdf

Os livros de SQL e PL/SQL são essenciais para referência e tira-dúvidas conforme vai se escrevendo o código:
http://www.oracle.com/pls/db112/to_pdf? … e10472.pdf
http://www.oracle.com/pls/db112/to_pdf? … e10593.pdf

Se pintar alguma dúvida específica pode mandar.

Abraço.

[Muca]

Valeu pela resposta fsitja,

Acredito que o FOR SELECT faça algo como um cursor for loop do Oracle sim, ou seja, faz um consulta, aí pra cada linha que a consula retorna é possivel executar um outro comando. Exemplo no “codigo burro” abaixo:

FOR SELECT ID FROM USUARIO where ID > 100 INTO :var_id
DO
BEGIN
delete from cartao where id_usuario = :var_id;
END

e como voce mensionou, a solução que eu encontrei no Oracle, foi usar
FOR LOOP, mas com uma implementação diferente da que voce postou.

Utilizando o exemplo acima, parece funcionar (não pude testar ainda):

FOR teste_for in (select id from usuario where id > 100)
LOOP
delete from cartao where id_usuario = teste_for.id;
END LOOP;

de qualquer forma vou analisar a solução que tu apresentou.

Obrigado
Abraço.

[fsitja]

A ideia é a mesma. Você pode começar usando a implementação que você postou e conforme for ficando mais confortável passar para o bulk collect com forall, que é a forma “ideal” de se resolver problemas em PL/SQL.

Isso porque o Cursor for loop é uma estrutura já ultrapassada.

[Muca]

ultrapassada!??! …hm entendi…
Vou fazer isso mesmo que você falou… vou começar com o mais simples, até eu resolver todos os problemas envolvendo a migração, e aí vou para a implementação bulk collect e tudo mais.

mas é isso…

Valeu fsitja…

Abraço!!

[fsitja]

Digo “ultrapassada” pois deveria ter sido deprecada já, devido à performance muito ruim que apresenta.

Por exemplo, você jamais vai querer fazer um cursor for loop onde o cursor possui milhões de linhas, ou até mesmo 100 mil ou mais, fazendo updates, selects, inserts ou deletes dentro do loop. É terrível em matéria de performance.

É mais ou menos a seguinte analogia:
Você vai na biblioteca, chega no balcão e pede um livro. A pessoa vai até as prateleiras, procura o livro, pega ele e leva até você. Daí você dá uma olhada no livro, usa ele e faz o que precisa, volta no balcão e pede um outro livro. A moça vai de novo nas prateleiras, procura o livro, acha, pega e retorna para você… você vai para a mesa, trabalha com ele, etc etc…

Repita a operação suficientemente e prepare-se para ser ofendido ou estapeado.

Não seria muito mais fácil levar uma “listinha” com os livros para a pobre da moça, pedir todos de uma vez, ela voltar com os braços cheios de livros e você fazer tudo o que precisa com eles de uma vez só?

É a mesma coisa com o banco de dados. Exceto que ele não te xinga de cobras e lagartos se você abusar da boa vontade dele. 🙄

Fazer com loop envolve:
1 – sair do PL/SQL
2 – ir pro banco de dados e fazer o fetch de uma linha
3 – retornar ao PL/SQL com a linha
4 – processar a transformação ou lógica de negócio
5 – fazer o select/update/insert/delete necessário dentro do loop, que significa sair para o engine do SQL novamente
7 – retornar ao engine do PL/SQL
8 – retornar ao passo 1

Isso tudo para cada uma das milhares, milhões ou bilhões de linhas. Sai muito custoso isso tudo. O simples fato de chavear context do PL/SQL para o SQL, que pode ser imperceptível se realizado meia dúzia de vezes, multiplicado por mil ou por um milhão, vira uma eternidade para o usuário esperando a transação terminar.

E isso não vale só para PL/SQL. Vale para Java, para PHP, .NET enfim, qualquer linguagem que tenha interação com o banco de dados. Por isso a interface das procedures tem que ser cuidadosamente definida e, possivelmente, ter variações que recebam lotes (arrays, collections) para operações com bulk collect.

O PL/SQL pelo menos tem a “esperteza” de usar bind variables nos SQLs, enquanto que nas outras linguagens o desenvolvedor precisa fazer o binding manualmente. Senão a coisa toda piora muito mais e o servidor sofre fazendo parse e plano de acesso para cada comando SQL enviado.

Já o bulk collect trabalha em lotes de linhas, assim como a listinha na biblioteca.

Reduza o overhead. Maximize a utilização de recursos.

Não é só porque funciona que está correto.

[Autor]

Posts