CODEWITHSUNDEEP
sqloracle-databaseplsql
Nikhil
Nikhil

Reputation: 151

Inserting values in a table in FOR loop statement in pl sql

I want to run the loop from i=1 to i=12 and insert each value of M_1,M_2 and so on till M_12 in M_Maintenance table.I have tried but it is not working.Please help me out. The code which I have tried is:-

BEGIN
   FOR i IN 1 .. 12
   LOOP
      DECLARE
         M_i               NUMBER;
         MONTH_i_COUNT     NUMBER;
         M_i_Maintenance   NUMBER;
         MONTH_i_SUM       NUMBER;
      BEGIN
         SELECT   ROUND (
                     ( ( (SELECT   SUM (MONTH_i_COUNT)
                            FROM   XXBAXY.XXBAXY_BREAKDOWN_TAB
                           WHERE   LOSS_CAT = 'Maintenance Related Losses')
                        + (SELECT   MONTH_i_COUNT
                             FROM   XXBAXY.XXBAXY_BREAKDOWN_TAB
                            WHERE   LOSS_CAT = 'Maintenance Related Losses'
                                    AND description = 'SAFTY SHEET PRO. ')
                        - (SELECT   MONTH_i_COUNT
                             FROM   XXBAXY.XXBAXY_BREAKDOWN_TAB
                            WHERE   LOSS_CAT = 'Maintenance Related Losses'
                                    AND description =
                                          'PREVENTIVE MAINTENANCE '))
                      / (SELECT   MONTH_i_SUM FROM XXBAXY.XXBAXY_ATTR_SUM_TAB))
                     * 100,
                     3
                  )
           INTO   M_i
           FROM   DUAL;
      EXCEPTION
         WHEN ZERO_DIVIDE
         THEN
            M_i := 0;
      END;
      INSERT INTO M_Maintenance (M_i_Maintenance)
        VALUES   (M_i);
   END LOOP;
END;

Upvotes: 0

Views: 3741

Answers (2)

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

Did you mean, that your table XXBAXY.XXBAXY_BREAKDOWN_TAB contains fields MONTH_1_COUNT, MONTH_2_COUNT, MONTH_3_COUNT and so on? If so, first step should be is redesigning this table because of violation of 1NF. It's bad design, a way of immutable pain. If no, it's unclear why you try to use uninitialized local varables in SQL statement without any field references.

Generally it looks that you should avoid a FOR LOOP and PL/SQL and write a single insert/select statement which will produces all rows required.

Elaborating: your source has a common structure of

begin
  for i in MIN..MAX loop
    select something into value 
      from table1 
      where some_condition 
      and id = i;
    insert into table2 values (value);
  end loop;
end;

Almost always better way is do this as

insert into table2
  select something
  from table1
  where some_condition
  and id in (MIN..MAX);

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

Let's first make your query easier to read and better perfoming, if I don't mistake it should be this one:

SELECT ROUND(
    SUM(CASE description
    WHEN 'SAFTY SHEET PRO. ' THEN 2*MONTH_i_COUNT
    WHEN 'PREVENTIVE MAINTENANCE ' THEN -MONTH_i_COUNT
    ELSE MONTH_i_COUNT
    END) / MIN(MONTH_i_SUM) *100, 3)
FROM XXBAXY.XXBAXY_BREAKDOWN_TAB
    CROSS JOIN XXBAXY.XXBAXY_ATTR_SUM_TAB 
WHERE LOSS_CAT = 'Maintenance Related Losses'

In this case your PL/SQL code can look like this (skipping the exception handler):

DECLARE
    m NUMBER;
    sqlstr VARCHAR2(1000);

BEGIN
    FOR i IN 1..12 LOOP           
        sqlstr := 
        'SELECT ROUND( '
        '   SUM(CASE description '
        '   WHEN ''SAFTY SHEET PRO. '' THEN 2*MONTH_'||i||'_COUNT '
        '   WHEN ''PREVENTIVE MAINTENANCE '' THEN -MONTH_'||i||'_COUNT '
        '   ELSE MONTH_'||i||'_COUNT '
        '   END) / MIN(MONTH_'||i||'_SUM) *100, 3) '
        'FROM XXBAXY.XXBAXY_BREAKDOWN_TAB '
        '   CROSS JOIN XXBAXY.XXBAXY_ATTR_SUM_TAB ' 
        'WHERE LOSS_CAT = ''Maintenance Related Losses''';

        EXECUTE IMMEDIATE sqlstr INTO m;
        EXECUTE IMMEDIATE 'INSERT INTO M_Maintenance (M_'||i||'_Maintenance) VALUES (:m)' USING m;
    END LOOP;
END;

Upvotes: 1

Related Questions