Insert a select with more than one result

Navigation
1

I need to run the following command in an ORACLE DB: Elaborate a select that returns more than one result and the result of this select need to use to make an insert in several lines.

Ex: My select returned 2 results: A and B, these results should be entered in 2 lines: 

A
B

How can I do this? Here is an elaborate code: 

DECLARE 
cursor insere is 
select campo_valor_tarefa.ds_valor from campo_valor_tarefa
inner join fluxo
on campo_valor_tarefa.cd_fluxo = fluxo.cd_fluxo
where campo_valor_tarefa.cd_tarefa = 2 and campo_valor_tarefa.cd_campo = 10 and fluxo.cd_processo = 180 and campo_valor_tarefa.cd_fluxo <344;

linha insere%rowtype;
BEGIN

OPEN insere;
loop
FETCH insere into linha;
exit when insere%notfound;

UPDATE campo_valor_tarefa set ds_valor = (
SELECT ds_valor FROM CAMPO_VALOR_TAREFA WHERE cd_campo = 10 AND cd_tarefa=2 AND cd_fluxo = 341
)
where cd_campo = 126 and cd_tarefa = 6 and cd_fluxo = 344;
end loop;
close insere;
end;

If I run select at the beginning of the code it returns 2 results and at the time of executing insert / update it inserts only 1 result on all lines.

    
asked by anonymous 19.02.2016 / 20:26

1 answer

1

You need to do this using a loop, that for each "line" an insert is made, see this example that does a select and for each line makes a separate insert: 

create or replace procedure P_EXEMPLO  is
      Cursor CONSULTA IS
        select COLUNA1, COLUNA2 from TABELA order by 1, 2;
        LINHA CONSULTA %ROWTYPE;
      begin
        DBMS_OUTPUT.ENABLE(1000000);
        OPEN   CONSULTA;
        LOOP
        FETCH  CONSULTA  INTO   LINHA;
        EXIT   WHEN   CONSULTA%NOTFOUND;

          BEGIN 
              DBMS_OUTPUT.PUT_LINE('Incluindo novos registros');
              INSERT INTO TABLE2 VALUES(LINHA.COLUNA1, LINHA.COLUNA2);
          END;

        END LOOP; 
        COMMIT;
        CLOSE  CONSULTA;
      end P_EXEMPLO;
    
19.02.2016 / 20:38
$ _POST without closing modal bootstrap Generate PDF report with jasper reports and spring