How to use FOR to INSERT within a function in ORACLE?

2

I have the following function:

 FUNCTION PERSISTIR_CR(
    p_AnoExercicio     TB_CONFIGURACAO_EXERCICIO.NUM_EXERCICIO%TYPE
   ,p_CodCR            TB_CESTA_ROTINA.COD_CESTA_ROTINA%TYPE
   ,p_CodUG            TB_CESTA_ROTINA.COD_UG%TYPE
   ,p_PctAtingAcum     TB_CESTA_ROTINA.PCT_ATINGIDO_ACUMULADO%TYPE
   ,p_ValFarolNotaAcum TB_CESTA_ROTINA.VAL_FAROL_NOTA_ACUMULADO%TYPE
   ,p_CodUsuarioResp   TB_CESTA_ROTINA.COD_USUARIO_RESPONSAVEL%TYPE   
  )
  RETURN NUMBER
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    vEXISTE NUMBER;  
    vCodConfigModulo NUMBER;
    vCodCR NUMBER; 
    vQtdLancamentos NUMBER;
  BEGIN

    IF ( (p_AnoExercicio IS NOT NULL) AND (p_AnoExercicio > 0) ) THEN
      vCodConfigModulo := EXERCICIO_PKG.COD_CONFIG_MOD_DO_ANO_EXERC_CR(p_AnoExercicio);
    END IF;

    vEXISTE := 0;

    SELECT COUNT(*) INTO vEXISTE 
    FROM TB_CESTA_ROTINA
    WHERE 
      COD_CESTA_ROTINA = p_CodCR;

      --/*
    -- Garantindo as configuracoes de Modulo  
    IF (vEXISTE = 0) THEN  
      SELECT SEQ_COD_TB_CESTA_ROTINA.nextval INTO vCodCR FROM DUAL;
      INSERT INTO TB_CESTA_ROTINA
      (
        COD_CESTA_ROTINA,
        COD_CONFIGURACAO_MODULO,
        COD_UG,
        PCT_ATINGIDO_ACUMULADO,
        VAL_FAROL_NOTA_ACUMULADO,
        COD_USUARIO_RESPONSAVEL
      )
      VALUES
      (
         vCodCR
        ,vCodConfigModulo
        ,p_CodUG
        ,p_PctAtingAcum        
        ,p_ValFarolNotaAcum
        ,p_CodUsuarioResp
      );
      SELECT TP.QTD_LANCAMENTOS, FROM TB_CONFIGURACAO_EXERCICIO TCE INNER JOIN TB_PERIODO TP ON TP.COD_PERIODO = TCE.COD_PERIODO_PRINCIPAL WHERE TCE.NUM_EXERCICIO = p_AnoExercicio INTO vQtdLancamentos

=== > In this part of the function I would like to hold a FOR. I'm going to insert into a table according to the number of Existing Launches, using the value of the variable vQtdLancamentos.

Example: FOR i = 1; i

I know it's wrong, it's just to illustrate more or less what I want.

Then follow the rest of the normal function.

    ELSE
      vCodCR := p_CodCR;
      UPDATE
        TB_CESTA_ROTINA
      SET
        -- COD_CONFIGURACAO_MODULO  =
        --,
         COD_UG                   = p_CodUG
        ,PCT_ATINGIDO_ACUMULADO   = p_PctAtingAcum
        ,VAL_FAROL_NOTA_ACUMULADO = p_ValFarolNotaAcum
        ,COD_USUARIO_RESPONSAVEL  = p_CodUsuarioResp
      WHERE
        COD_CESTA_ROTINA = vCodCR
        ;
    END IF;

    COMMIT;  
    --*/
    RETURN vCodCR;

    EXCEPTION
      WHEN OTHERS THEN
        ecode := SQLCODE;
        emesg := SQLERRM;
        dbms_output.put_line('CR_PKG.PESISTIR_CR' || ecode || ' - ' || emesg);

    --ROLLBACK;    
    RETURN 0;

  END PERSISTIR_CR;

How to make this for? Or if there is another way to do it better I also accept suggestions and help.

    
asked by anonymous 14.04.2014 / 18:40

1 answer

0

I was able to loop:

  FOR i IN 1..vQtdLancamentos LOOP
    INSERT INTO TB_CR_PERIODO (COD_CR_PERIODO, COD_CESTA_ROTINA, NUM_PERIODO_LANCAMENTO) VALUES (SEQ_COD_TB_CR_PERIODO.nextval, vCodCR, i);
  END LOOP;
    
14.04.2014 / 19:12