Error ORA-00933,PL/SQL: SQL Statement ignored , PLS-00103 while running this in oracle, How to resol-CodePudding

I am getting errors while running this code

Error report - 
ORA-06550: line 46, column 104:  
PL/SQL: ORA-00933: SQL command not properly ended  
ORA-06550: line 43, column 11:  
PL/SQL: SQL Statement ignored  
ORA-06550: line 78, column 5:  
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:

I have a table named "STRUCTURE_TABLE" which looks like this

P_DATE    |  SS_CP  SS_OP    SS_HP   SS_LP    DIVS  AD_CP  AD_OP  AD_HP  AD_LP
------------------------------------------------------------------------------
10-08-10  | 27.15   26.5    29.4    26.25     null   0       0      0      0
11-08-10  | 26.9    27.45   27.9    26.275    null   0       0      0      0
12-08-10  | 26.3    26.7    26.7    26        0.98   0       0      0      0
13-08-10  | 26     26.025   26.575  25.75     null   0       0      0      0

And this is the code i am performing on it

declare
v_num integer := 1;
v_SS_CP number(8,5);
v_SS_OP number(8,5);
v_SS_HP number(8,5);
v_SS_LP number(8,5);
v_DIVSS number(8,5);
v_AD_CP number(8,5);
v_AD_OP number(8,5);
v_AD_HP number(8,5);
v_AD_LP number(8,5);
begin
for rec in (select P_DATE, SS_CP, SS_OP, SS_HP, SS_LP, DIVSS ,
                    AD_CP, AD_OP, AD_HP, AD_LP from STRUCTURE_TABLE order by P_DATE)
loop
   if(v_num = 1) then       
       update STRUCTURE_TABLE set AD_CP = SS_CP,
                                 AD_OP = SS_OP,
                                 AD_HP = SS_HP,
                                 AD_LP = SS_LP
       where P_DATE = rec.P_DATE;     
   else
       if (rec.DIVSS is not null) then
          update STRUCTURE_TABLE set 
           AD_CP =
           v_AD_CP * (rec.SS_CP/ v_SS_CP)  
           rec.DIVSS * (v_AD_CP/v_SS_CP),
           
           AD_OP =
           v_AD_OP * (rec.SS_OP/ v_SS_OP)  
           rec.DIVSS * (v_AD_OP/v_SS_OP),
           
           AD_HP =
           v_AD_HP * (rec.SS_HP/ v_SS_HP)  
           rec.DIVSS * (v_AD_HP/v_SS_HP),
           
           AD_LP =
           v_AD_LP * (rec.SS_LP/ v_SS_LP)  
           rec.DIVSS * (v_AD_LP/v_SS_LP)
           
           where P_DATE = rec.P_DATE;
       else
          update STRUCTURE_TABLE set 
          
          AD_CP =
          v_AD_CP * (rec.SS_CP / v_SS_CP) where P_DATE = rec.P_DATE,
          
          AD_OP =
          v_AD_OP * (rec.SS_OP / v_SS_OP) where P_DATE = rec.P_DATE,
          
          AD_HP =
          v_AD_HP * (rec.SS_HP / v_SS_HP) where P_DATE = rec.P_DATE,
          
          AD_LP =
          v_AD_LP * (rec.SS_LP / v_SS_LP) where P_DATE = rec.P_DATE
        end if;
       
    end if;
      
       v_num:= v_num  1;
   
       v_SS_CP := rec.SS_CP;
       v_SS_OP := rec.SS_OP;
       v_SS_HP := rec.SS_HP;
       v_SS_LP := rec.SS_LP;
       
       v_DIVSS := rec.DIVSS;
       
       select AD_CP into v_AD_CP from STRUCTURE_TABLE
       where P_DATE = rec.P_DATE;
       select AD_OP into v_AD_OP from STRUCTURE_TABLE
       where P_DATE = rec.P_DATE;
       select AD_HP into v_AD_HP from STRUCTURE_TABLE
       where P_DATE = rec.P_DATE;
       select AD_LP into v_AD_LP from STRUCTURE_TABLE
       where P_DATE = rec.P_DATE;
     
end loop;
end;

CodePudding user response：

Bunch of misplaced WHERE clauses.

This is OK (as far as that error is concerned; I can't test it without your tables, though).

DECLARE
   v_num    INTEGER := 1;
   v_SS_CP  NUMBER (8, 5);
   v_SS_OP  NUMBER (8, 5);
   v_SS_HP  NUMBER (8, 5);
   v_SS_LP  NUMBER (8, 5);
   v_DIVSS  NUMBER (8, 5);
   v_AD_CP  NUMBER (8, 5);
   v_AD_OP  NUMBER (8, 5);
   v_AD_HP  NUMBER (8, 5);
   v_AD_LP  NUMBER (8, 5);
BEGIN
   FOR rec IN (  SELECT P_DATE,
                        SS_CP,
                        SS_OP,
                        SS_HP,
                        SS_LP,
                        DIVSS,
                        AD_CP,
                        AD_OP,
                        AD_HP,
                        AD_LP
                   FROM STRUCTURE_TABLE
               ORDER BY P_DATE)
   LOOP
      IF (v_num = 1)
      THEN
         UPDATE STRUCTURE_TABLE
            SET AD_CP = SS_CP,
                AD_OP = SS_OP,
                AD_HP = SS_HP,
                AD_LP = SS_LP
          WHERE P_DATE = rec.P_DATE;
      ELSE
         IF (rec.DIVSS IS NOT NULL)
         THEN
            UPDATE STRUCTURE_TABLE
               SET AD_CP =
                        v_AD_CP * (rec.SS_CP / v_SS_CP)
                        rec.DIVSS * (v_AD_CP / v_SS_CP),
                   AD_OP =
                        v_AD_OP * (rec.SS_OP / v_SS_OP)
                        rec.DIVSS * (v_AD_OP / v_SS_OP),
                   AD_HP =
                        v_AD_HP * (rec.SS_HP / v_SS_HP)
                        rec.DIVSS * (v_AD_HP / v_SS_HP),
                   AD_LP =
                        v_AD_LP * (rec.SS_LP / v_SS_LP)
                        rec.DIVSS * (v_AD_LP / v_SS_LP)
             WHERE P_DATE = rec.P_DATE;
         ELSE
            UPDATE STRUCTURE_TABLE
               SET AD_CP = v_AD_CP * (rec.SS_CP / v_SS_CP),
                   AD_OP = v_AD_OP * (rec.SS_OP / v_SS_OP),
                   AD_HP = v_AD_HP * (rec.SS_HP / v_SS_HP),
                   AD_LP = v_AD_LP * (rec.SS_LP / v_SS_LP)
             WHERE P_DATE = rec.P_DATE;
         END IF;
      END IF;

      v_num := v_num   1;

      v_SS_CP := rec.SS_CP;
      v_SS_OP := rec.SS_OP;
      v_SS_HP := rec.SS_HP;
      v_SS_LP := rec.SS_LP;

      v_DIVSS := rec.DIVSS;

      SELECT AD_CP
        INTO v_AD_CP
        FROM STRUCTURE_TABLE
       WHERE P_DATE = rec.P_DATE;

      SELECT AD_OP
        INTO v_AD_OP
        FROM STRUCTURE_TABLE
       WHERE P_DATE = rec.P_DATE;

      SELECT AD_HP
        INTO v_AD_HP
        FROM STRUCTURE_TABLE
       WHERE P_DATE = rec.P_DATE;

      SELECT AD_LP
        INTO v_AD_LP
        FROM STRUCTURE_TABLE
       WHERE P_DATE = rec.P_DATE;
   END LOOP;
END;