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

Question

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 that part of the function I would like to perform a FOR. I will insert in a table according to the number of existing Lancamentos, using the value of the variable vQtdLocations.

Example: FOR i = 1; i <= vQtdLankings INSERT INTO TB_CR_PERIODO (NUM_PERIODO) VALUES(i)

I know you’re 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 do this for? Or if there is another way to do better I also accept suggestions and help.

Answer 1

I managed to make the 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;