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 9999999999999999999999999999
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 QUANTIDADE RETORNO
------------ ---------- ---------- ----------
1 10 100
2 20 120
3 10 150
4 20 300
SQL> select * from expedicao;
EXP_ID PRODUTO_ID QUANTIDADE OEL_ID
---------- ---------- ----------
8 10 250 1,3
9 20 420 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 QUANTIDADE RETORNO
------------ ---------- ---------- ----------
1 10 100 8
2 20 120 9
3 10 150 8
4 20 300 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 QUANTIDADE RETORNO
> ------------ ---------- ---------- ----------
> 1 10 100 1
> 2 20 120 1
> 3 10 150 1
> 4 20 300 1
>
> SQL> select * from expedicao;
>
> EXP_ID PRODUTO_ID QUANTIDADE
> ---------- ---------- ----------
> 1 10 250
> 1 20 420
>
> 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] <oracle_br@yahoogrupos.com.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 9999999999999999999999999999
>>> 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
>>> SET t.retorno = id_expedicao
>>> WHERE ROWID = rc_itens_nota.rowid;
>>> EXCEPTION WHEN OTHERS THEN
>>> UPDATE itens_da_nota t
>>> SET t.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] <oracle_br@yahoogrupos.com.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_id produto_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_id produto_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 retorno vai ser no máximo na
>>>>> casa das CENTENAS, não muito mais que isso, sob pena de esgotar PGA e/ou
>>>>> os
>>>>> limites de variáveis do PL/SQL, okdoc ?
>>>>>
>>>>> []s
>>>>>
>>>>> Chiappa
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
