Re: [oracle_br] Agrupamento
Chiappa, reaproveitei a massa de dados que o Emerson Gaudencio criou, mas adaptei a procedure dele... bom, segue novamente, acredito que agora ficará mais fácil de explicar. create table itens_da_nota ( item_nota_id number(8), produto_id number(8), quantidade number(8), retorno number(8) ); create table expedicao ( exp_id number(8), produto_id number(8), quantidade number(8), itens_nota varchar2(1000) ); create unique index EXP_UNICO on EXPEDICAO (EXP_ID, PRODUTO_ID, QUANTIDADE); -- Create sequence create sequence EXPEDICAOID minvalue 1 maxvalue start with 1 increment by 1 nocache; -- inserts insert into itens_da_nota (ITEM_NOTA_ID, PRODUTO_ID, QUANTIDADE, RETORNO) values (1, 10, 100, null); insert into itens_da_nota (ITEM_NOTA_ID, PRODUTO_ID, QUANTIDADE, RETORNO) values (2, 20, 120, null); insert into itens_da_nota (ITEM_NOTA_ID, PRODUTO_ID, QUANTIDADE, RETORNO) values (3, 10, 150, null); insert into itens_da_nota (ITEM_NOTA_ID, PRODUTO_ID, QUANTIDADE, RETORNO) values (4, 20, 300, null); commit; -- trigger pra preencher o ID da expedição: - create or replace trigger t_i_expedicao before insert on expedicao for each row declare id_expedicao number; -- local variables here begin if :new.exp_id is null then SELECT EXPEDICAOID.NEXTVAL INTO id_expedicao FROM DUAL; :new.exp_id := id_expedicao; end if; end t_i_expedicao; - create or replace procedure p_agrupa as total_itens NUMBER; produto_id NUMBER; id_expedicao NUMBER; -- CURSOR c_itens_nota IS --SELECT produto_id, quantidade, ROWID FROM itens_da_nota; BEGIN insert into expedicao (produto_id, quantidade, oel_id) select i.produto_id, sum(i.quantidade), listagg(i.item_nota_id, ',') WITHIN GROUP(ORDER BY i.item_nota_id) from itens_da_nota i group by i.produto_id; end; begin p_agrupa; end; Reparem que usei o LISTAGG pra preencher o ultimo campo da EXPEDICAO. Eu não sei se realmente vou precisar desse campo, mas criei pra ajudar aqui na demonstração. SQL> select * from itens_da_nota; ITEM_NOTA_ID PRODUTO_ID QUANTIDADERETORNO -- -- -- 1 10100 2 20120 3 10150 4 20300 SQL> select * from expedicao; EXP_ID PRODUTO_ID QUANTIDADE OEL_ID -- -- -- 8 10250 1,3 9 20420 2,4 Agora o que falta é: Quero fazer um UPDATE da ITENS_DA_NOTA, no campo RETORNO, fazendo receber o EXP_ID relacionando conforme o campo OEL_ID. Ficaria assim: ITEM_NOTA_ID PRODUTO_ID QUANTIDADERETORNO -- -- -- 1 10100 8 2 20120 9 3 10150 8 4 20300 9 Em 31 de maio de 2014 14:32, Milton Bastos Henriquis Jr. < miltonbas...@gmail.com> escreveu: > Emerson, não funcionou: > > SQL> select * from itens_da_nota; > > ITEM_NOTA_ID PRODUTO_ID QUANTIDADERETORNO > -- -- -- >1 10100 1 >2 20120 1 >3 10150 1 >4 20300 1 > > SQL> select * from expedicao; > > EXP_ID PRODUTO_ID QUANTIDADE > -- -- -- > 1 10250 > 1 20420 > > SQL> > > > > > Em 30 de maio de 2014 19:51, Milton Bastos Henriquis Jr. < > miltonbas...@gmail.com> escreveu: > > Obrigado Emerson! >> >> vou tentar adaptar essa solução aqui pra base e testar na segunda-feira, >> vamos ver! >> >> Valeu mesmo! >> >> >> Em 30 de maio de 2014 19:20, Emerson dos Santos Gaudêncio >> emerson.fen...@gmail.com [oracle_br] >> escreveu: >> >> >>> >>> Miltão, >>> >>> Quebrei um pouco a cachola aqui e pensei numa forma de conseguir >>> resolver isso. Segue meu protótipo espero ajudar: >>> >>> create table itens_da_nota ( >>> item_nota_id number(8), >>> produto_id number(8), >>> quantidade number(8), >>> retorno number(8) >>> ); >>> >>> create table expedicao ( >>> exp_id number(8), >>> produto_id number(8), >>> quantidade number(8) >>> ); >>> >>> create unique index EXP_UNICO on EXPEDICAO (EXP_ID, PRODUTO_ID, >>> QUANTIDADE); >>> >>> -- Create sequence >>> create sequence EXPEDICAOID >>> minvalue 1 >>> maxvalue >>> start with 1 >>> increment by 1 >>> cache 20; >>> >>> -- inserts >>> >>> insert into itens_da_nota (ITEM_NOTA_ID, PRODUTO_ID, QUANTIDADE, RETORNO) >>> values (1, 10, 100, null); >>> insert into itens_da_nota (ITEM_NOTA_ID, PRODUTO_ID, QUANTIDADE, RETORNO) >>> values
Re: [oracle_br] Agrupamento
Emerson, não funcionou: SQL> select * from itens_da_nota; ITEM_NOTA_ID PRODUTO_ID QUANTIDADERETORNO -- -- -- 1 10100 1 2 20120 1 3 10150 1 4 20300 1 SQL> select * from expedicao; EXP_ID PRODUTO_ID QUANTIDADE -- -- -- 1 10250 1 20420 SQL> Em 30 de maio de 2014 19:51, Milton Bastos Henriquis Jr. < miltonbas...@gmail.com> escreveu: > Obrigado Emerson! > > vou tentar adaptar essa solução aqui pra base e testar na segunda-feira, > vamos ver! > > Valeu mesmo! > > > Em 30 de maio de 2014 19:20, Emerson dos Santos Gaudêncio > emerson.fen...@gmail.com [oracle_br] > escreveu: > > >> >> Miltão, >> >> Quebrei um pouco a cachola aqui e pensei numa forma de conseguir resolver >> isso. Segue meu protótipo espero ajudar: >> >> create table itens_da_nota ( >> item_nota_id number(8), >> produto_id number(8), >> quantidade number(8), >> retorno number(8) >> ); >> >> create table expedicao ( >> exp_id number(8), >> produto_id number(8), >> quantidade number(8) >> ); >> >> create unique index EXP_UNICO on EXPEDICAO (EXP_ID, PRODUTO_ID, >> QUANTIDADE); >> >> -- Create sequence >> create sequence EXPEDICAOID >> minvalue 1 >> maxvalue >> start with 1 >> increment by 1 >> cache 20; >> >> -- inserts >> >> insert into itens_da_nota (ITEM_NOTA_ID, PRODUTO_ID, QUANTIDADE, RETORNO) >> values (1, 10, 100, null); >> insert into itens_da_nota (ITEM_NOTA_ID, PRODUTO_ID, QUANTIDADE, RETORNO) >> values (2, 20, 120, null); >> insert into itens_da_nota (ITEM_NOTA_ID, PRODUTO_ID, QUANTIDADE, RETORNO) >> values (3, 10, 150, null); >> insert into itens_da_nota (ITEM_NOTA_ID, PRODUTO_ID, QUANTIDADE, RETORNO) >> values (4, 20, 300, null); >> commit; >> >> -- bloco anonimo de atualização dos registros >> >> DECLARE >>total_itens NUMBER; >>produto_id NUMBER; >>id_expedicao NUMBER; >>CURSOR c_itens_nota IS >> SELECT produto_id, quantidade, ROWID >> FROM itens_da_nota >> ; >> BEGIN >>SELECT EXPEDICAOID.NEXTVAL INTO id_expedicao FROM DUAL; >>FOR rc_itens_nota IN c_itens_nota >>LOOP >> BEGIN >> SELECT SUM(quantidade), produto_id >> INTO total_itens, produto_id >> FROM itens_da_nota >> WHERE produto_id = rc_itens_nota.produto_id >> GROUP BY produto_id; >> INSERT INTO EXPEDICAO >> VALUES >> (id_expedicao, >> produto_id, >> total_itens); >> UPDATE itens_da_nota t >> SETt.retorno = id_expedicao >> WHERE ROWID = rc_itens_nota.rowid; >> EXCEPTION WHEN OTHERS THEN >> UPDATE itens_da_nota t >> SETt.retorno = id_expedicao >> WHERE ROWID = rc_itens_nota.rowid; >> END; >>END LOOP; >>COMMIT; >> END; >> >> >> >> >> >> Em 30 de maio de 2014 16:20, 'Milton Bastos Henriquis Jr.' >> miltonbas...@gmail.com [oracle_br] >> escreveu: >> >> >>> >>> Legal Chiappa, obrigado! >>> >>> Essa questão de retornar múltiplas linhas vc matou a charada, eu não >>> conhecia esse esquema. >>> >>> Agora tenho outro problema... além de retornar esse valor eu precisaria >>> gravá-lo nos registros correspondentes da ORIGEM (ou seja, do SELECT que >>> originou os valores do insert). >>> Não consigo imaginar uma solução pra isso - associar esses novos >>> registros inseridos com os registros da origem desses dados. >>> A dificuldade é justamente por causa do agrupamento. >>> >>> Exemplo (espero que funcione a formatação): >>> >>> >>>item_nota_idproduto_id quantidade retorno 1 10 100 2 20 120 3 >>> 10 150 4 20 300 >>> Faço o insert na EXPEDIÇAO: expedição exp_id produto_id quantidade >>> 51 10 250 52 20 420 Depois atualizo na tabela origem o campo >>> retorno: >>> >>>item_nota_idproduto_id quantidade retorno 1 10 100 51 2 20 120 >>> 52 3 10 150 51 4 20 300 52 >>> >>> >>> >>> >>> >>> Em 30 de maio de 2014 16:52, jlchia...@yahoo.com.br [oracle_br] < >>> oracle_br@yahoogrupos.com.br> escreveu: >>> >>> Miltão, realmente ficou meio confuso, mas ao menos até onde entendi a questão parece ser que vc precisa de uma RETURNING clause retornando múltiplas linhas : a sintaxe para isso é RETURNING BULK COLLECT http://www.java2s.com/Tutorial/Oracle/0440__PL-SQL-Statements/UsingtheRETURNINGBULKCOLLECTclausetoSELECTdirectlyintoaPLSQLarray.htm tem um exemplo para um UPDATE retornando múltiplos valores em múltiplas linhas, imagino que para INSERT deve ser similar E logicamente : a) na hora de processar o array PL/SQL com os valores vc usará FORALL e os construtos de array do PL/SQL e b) sempre que falamos em arrays, vc TEM que ter em mente as limitações de memória do PL/SQL : tenha certeza que o
