PL/SQL存储过程新增UPDATE语句后报ORA-00933错误求助

错误原因分析 #

1. 直接触发ORA-00933的原因：Oracle的UPDATE语法不允许在WHERE子句后使用ORDER BY子句。你在两个UPDATE语句末尾添加的order by noteid desc属于非法语法，导致SQL命令无法正确解析。
2. 潜在的后续错误：原UPDATE中的子查询未关联目标表的sid，会返回对应表的所有记录拼接结果，若表中有多条数据，会触发ORA-01427（单行子查询返回多行）错误，必须添加关联条件限定为当前sid的记录，同时按noteid排序取最新的一条。

修正后的代码 #

DECLARE 
---values---
   v_sidcount sitetb.sid%type;
   v_sid sitetb.sid%type;
   v_cid sitetb.cid%type := 46;
   v_sptid sitetb.sptid%type := 215;

BEGIN 

---count---
SELECT count(sid) into v_sidcount FROM sitetb
WHERE NOT EXISTS (SELECT sid FROM kam_report_ttn
  WHERE sid = sitetb.sid
) and cid = v_cid and sptid = v_sptid;

---find all data---
SELECT max(sid) into v_sid FROM sitetb
WHERE NOT EXISTS (SELECT sid FROM kam_report_ttn
  WHERE sid = sitetb.sid
) and cid = v_cid and sptid = v_sptid;

---insert into table---
insert into KAM_REPORT_TTN (sid) 
values (v_sid);

---update comments---
-- 修正：移除ORDER BY，子查询关联sid并取最新的note记录
UPDATE KAM_REPORT_TTN t
SET comments = (
    SELECT addeddate || ', ' || responseperson || ': ' || candidatenote || ' '
    FROM candidatetb_extra e
    WHERE e.sid = t.sid
    ORDER BY noteid DESC
    FETCH FIRST 1 ROW ONLY
)
WHERE sid = v_sid;

UPDATE KAM_REPORT_TTN t
SET comments2 = (
    SELECT addeddate || ', ' || responseperson || ': ' || sitenote || ' '
    FROM sitetb_extra e
    WHERE e.sid = t.sid
    ORDER BY noteid DESC
    FETCH FIRST 1 ROW ONLY
)
WHERE sid = v_sid;

   dbms_output.put_line 
   (v_sid || '/' ||v_sidcount); 
END; 
/  

补充说明 #

• FETCH FIRST 1 ROW ONLY用于取排序后的第一条记录（Oracle 12c及以上支持），若使用低版本Oracle，可改用ROW_NUMBER()分析函数实现：

  SELECT addeddate || ', ' || responseperson || ': ' || candidatenote || ' '
  FROM (
      SELECT addeddate, responseperson, candidatenote,
             ROW_NUMBER() OVER(ORDER BY noteid DESC) rn
      FROM candidatetb_extra e
      WHERE e.sid = t.sid
  )
  WHERE rn = 1
  

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