Oracle运行PL/SQL报ORA-00933/PLS-00103错误该如何解决？

• 核心语法错误出现在DIVSS为空的ELSE分支的UPDATE语句中：SQL的UPDATE语句仅允许在所有字段赋值规则结束后写1次WHERE条件，你在每个字段的赋值表达式后都追加了WHERE子句，还使用逗号分隔，完全不符合语法规范，直接触发ORA-00933: SQL command not properly ended错误。
• 语法结构错位导致后续代码解析异常：上述UPDATE语句的语法错误打乱了PL/SQL的块结构解析逻辑，所以后续解析到LOOP关键字时会抛出PLS-00103错误。
• 隐藏错误：你的表结构中除权字段名为DIVS，但代码中查询的是DIVSS（多了一个S），语法问题修复后会触发字段不存在的报错。

首先修正UPDATE语句的语法错误，将WHERE子句移到所有字段赋值之后，再对齐字段名即可。修复后的完整代码如下：

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_DIVS 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, DIVS,
                    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.DIVS is not null) then
          update STRUCTURE_TABLE set 
           AD_CP = v_AD_CP * (rec.SS_CP/ v_SS_CP) + rec.DIVS * (v_AD_CP/v_SS_CP),
           AD_OP = v_AD_OP * (rec.SS_OP/ v_SS_OP) + rec.DIVS * (v_AD_OP/v_SS_OP),
           AD_HP = v_AD_HP * (rec.SS_HP/ v_SS_HP) + rec.DIVS * (v_AD_HP/v_SS_HP),
           AD_LP = v_AD_LP * (rec.SS_LP/ v_SS_LP) + rec.DIVS * (v_AD_LP/v_SS_LP)
           where P_DATE = rec.P_DATE;
       else
          -- 修正：所有字段赋值后统一加WHERE条件
          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_DIVS := rec.DIVS;
       
    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;
/

你可以将UPDATE语句后四次查询AD字段的逻辑替换为RETURNING子句，减少数据库查询次数提升效率，示例如下：

-- 以首条更新为例
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
returning AD_CP, AD_OP, AD_HP, AD_LP into v_AD_CP, v_AD_OP, v_AD_HP, v_AD_LP;

内容的提问来源于stack exchange，提问作者Amit