7 Replies Latest reply: Apr 22, 2013 6:43 PM by indra budiantho RSS

    Loopless

    indra budiantho
      Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
      PL/SQL Release 10.2.0.5.0 - Production
      CORE     10.2.0.5.0     Production
      TNS for Linux: Version 10.2.0.5.0 - Production
      NLSRTL Version 10.2.0.5.0 - Production

      hi, there! Here is my code, but i do not want to use a loop. Is it possible? tx in advance.
      /* Formatted on 4/23/2013 6:02:12 AM (QP5 v5.139.911.3011) */
      DECLARE
         v_prev_date   DATE := ADD_MONTHS (SYSDATE, -1);
         v_curr_date   DATE := SYSDATE;
      BEGIN
         FOR ctr IN 1 .. 3
         LOOP
            INSERT INTO t
                    SELECT (v_prev_date, v_curr_date) FROM DUAL;
      
            v_prev_date := v_curr_date;
            v_curr_date := ADD_MONTHS (v_curr_date, 1);
         END LOOP;
      
         COMMIT;
      END;
        • 1. Re: Loopless
          rp0428
          Yes - just repeat the code three times
                INSERT INTO t
                        SELECT (v_prev_date, v_curr_date) FROM DUAL;
           
                v_prev_date := v_curr_date;
                v_curr_date := ADD_MONTHS (v_curr_date, 1);
          --
                INSERT INTO t
                        SELECT (v_prev_date, v_curr_date) FROM DUAL;
           
                v_prev_date := v_curr_date;
                v_curr_date := ADD_MONTHS (v_curr_date, 1);
          --
                INSERT INTO t
                        SELECT (v_prev_date, v_curr_date) FROM DUAL;
           
                v_prev_date := v_curr_date;
                v_curr_date := ADD_MONTHS (v_curr_date, 1);
          • 2. Re: Loopless
            onedbguru
            Yes. it is possible... what have you tried?


            @rp... why don't you give the OP an opportunity to learn instead just giving answers.
            Edited by: onedbguru on Apr 22, 2013 4:15 PM

            Edited by: onedbguru on Apr 22, 2013 4:17 PM
            • 3. Re: Loopless
              indra budiantho
              the number of loop 3 is only a sample, it is a variable. The real case is i want to make summary for inventory for some periodes, like jan-feb, feb-mar, and...

              Edited by: indra budiantho on Apr 22, 2013 4:22 PM
              • 4. Re: Loopless
                Frank Kulash
                Hi,

                Here's one way:
                DECLARE
                   v_prev_date   DATE := ADD_MONTHS (SYSDATE, -1);
                   v_curr_date   DATE := SYSDATE;
                BEGIN
                      INSERT INTO t
                              SELECT  ADD_MONTHS (v_prev_date, LEVEL - 1)
                           ,       ADD_MONTHS (v_curr_date, LEVEL - 1) 
                           FROM    dual
                           CONNECT BY     LEVEL <= 3;
                 
                      COMMIT;
                END;
                You don't need PL/SQL for this.
                • 5. Re: Loopless
                  indra budiantho
                  tx, Frank, you are so helpful... that's what i want...
                  • 6. Re: Loopless
                    Frank Kulash
                    Hi,

                    In case you're interested, here's how you can do the same thing without PL/SQL:
                    INSERT INTO t
                    SELECT  ADD_MONTHS (SYSDATE, LEVEL - 2)
                    ,       ADD_MONTHS (SYSDATE, LEVEL - 1) 
                    FROM    dual
                    CONNECT BY     LEVEL <= 3;
                    • 7. Re: Loopless
                      indra budiantho
                      tx very much.