ORACLE的“ORA-30926: 无法在源表中获得一组稳定的行”错误修正范例

MERGE INTO POOR.TEMPALBORDERREQUESTDETAIL t
USING (SELECT A.ENCOUNTERID, A.ENTITYID FROM PRPA.ENCOUNTERPARTICIPATION A WHERE A.ROLECODEID=10 AND A.ISDELETED=0 AND A.ISLAST=1 ORDER BY A.STARTTIME DESC) D
ON (t.ENCOUNTERID = D.ENCOUNTERID)
WHEN MATCHED THEN
  UPDATE SET t.CLINICTEAMID = D.ENTITYID;
这个语句会不会当存在encounterId匹配出大于1条记录时报错？如果会该如何修改？

会报错。这是 Oracle MERGE 的经典限制，错误通常是 “ORA-30926: 无法在源表中获得一组稳定的行”。

改法一：ROW_NUMBER 去重（推荐，逻辑最直观）

MERGE INTO POOR.TEMPALBORDERREQUESTDETAIL t
USING (
  SELECT ENCOUNTERID, ENTITYID
    FROM (
      SELECT A.ENCOUNTERID, A.ENTITYID,
             ROW_NUMBER() OVER (
               PARTITION BY A.ENCOUNTERID
               ORDER BY A.STARTTIME DESC, A.ENTITYID
             ) rn
        FROM PRPA.ENCOUNTERPARTICIPATION A
       WHERE A.ROLECODEID = 10 AND A.ISDELETED = 0 AND A.ISLAST = 1
    )
   WHERE rn = 1
) D
ON (t.ENCOUNTERID = D.ENCOUNTERID)
WHEN MATCHED THEN
  UPDATE SET t.CLINICTEAMID = D.ENTITYID;

改法二：聚合 + KEEP FIRST（少一层嵌套，等价于"取最新一条的 ENTITYID"）

MERGE INTO POOR.TEMPALBORDERREQUESTDETAIL t
USING (
  SELECT A.ENCOUNTERID,
         MAX(A.ENTITYID) KEEP
           (DENSE_RANK FIRST ORDER BY A.STARTTIME DESC) AS ENTITYID
    FROM PRPA.ENCOUNTERPARTICIPATION A
   WHERE A.ROLECODEID = 10 AND A.ISDELETED = 0 AND A.ISLAST = 1
   GROUP BY A.ENCOUNTERID
) D
ON (t.ENCOUNTERID = D.ENCOUNTERID)
WHEN MATCHED THEN
  UPDATE SET t.CLINICTEAMID = D.ENTITYID;

GROUP BY 天然保证一个 ENCOUNTERID 只出一行，从结构上就杜绝了 ORA-30926。

完整存储过程：

CREATE GLOBAL TEMPORARY TABLE POOR.TEMPALBORDERREQUESTDETAIL (
    ORDERREQUESTID NUMBER(10,0)
    , ENCOUNTERID NUMBER(10,0)
    , DISPLAYNAME NVARCHAR2(100)
    , SEQNOTEXT NVARCHAR2(100)
    , GENDERCODEID NUMBER(10,0)
    , BIRTHTIME DATE
    , ADMITDATEON DATE
    , DISCHARGEON DATE
    , ADMITORGANIZATIONID NUMBER(10,0)
    , DISCHARGEORGANIZATIONID NUMBER(10,0)
    , AUTHORID NUMBER(10,0)
    , AUTHORNAME NVARCHAR2(100)
    , ADMINDOSE NUMBER(18,4)
    , FREQUENCYID NUMBER(10,0)
    , FREQUENCYNAME NVARCHAR2(100)
    , ROUTEID NUMBER(10,0)
    , ROUTENAME NVARCHAR2(100)
    , STARTDATE DATE
    , ENDDATE DATE
    , ALBGL NUMBER(18,4)
    , CLINICTEAMID NUMBER(10,0)
    , CLINICTEAMNAME NVARCHAR2(100)
) ON COMMIT PRESERVE ROWS;

/

CREATE OR REPLACE PROCEDURE POOR.GetALBOrderRequestDetail(
    IN_StartDateFrom IN date,
    IN_StartDateTo IN date,
    ref_cursor OUT sys_refcursor
    )
AS
    v_sql varchar2(4000);  
BEGIN
    DELETE POOR.TEMPALBORDERREQUESTDETAIL;
    COMMIT;
    
    --患者姓名、住院号、性别、年龄、入院日期、出院日期、开具白蛋白情况（单次剂量、给药频次、给药途径、开具日期、结束日期）、
    --开具白蛋白时医生填写的ALB的值，入院科室、出院科室、医嘱医生、医疗组（医护分配选择的医疗组）
    INSERT INTO POOR.TEMPALBORDERREQUESTDETAIL(ORDERREQUESTID, ENCOUNTERID, DISPLAYNAME, SEQNOTEXT, GENDERCODEID, BIRTHTIME, ADMITDATEON, DISCHARGEON, ADMITORGANIZATIONID
    , DISCHARGEORGANIZATIONID, AUTHORID, AUTHORNAME, ADMINDOSE, FREQUENCYID, FREQUENCYNAME, ROUTEID, ROUTENAME
    , STARTDATE, ENDDATE, ALBGL, CLINICTEAMID, CLINICTEAMNAME)
    SELECT A.ORDERREQUESTID, B.ENCOUNTERID, B.DISPLAYNAME, B.SEQNOTEXT, B.GENDERCODEID, B.BIRTHTIME, B.ADMITDATEON, B.DISCHARGEON
    , (CASE WHEN B.STATUSCODEID<3 THEN B.RESPONSIBLEDEPARTMENTID ELSE NULL END) AS ADMITORGANIZATIONID
    , (CASE WHEN B.STATUSCODEID>=3 THEN B.RESPONSIBLEDEPARTMENTID ELSE NULL END) AS DISCHARGEORGANIZATIONID
    , A.AUTHORID, E.NAME AS AUTHORNAME, A.ADMINDOSE, A.FREQUENCYID, C.NAME AS FREQUENCYNAME, A.ROUTEID, D.NAME AS ROUTENAME
    , A.STARTDATE, A.ENDDATE, A.ALBGL, NULL, NULL
    FROM poor.orderrequest a
    INNER JOIN prpa.encounter b ON a.encounterid=b.encounterid
    LEFT OUTER JOIN concept.frequency c ON a.frequencyid=c.frequencyid
    LEFT OUTER JOIN concept.route d ON a.routeid=d.routeid
    LEFT OUTER JOIN role.employee e ON e.employeeid=a.authorid
    WHERE a.isdeleted=0 AND a.statuscodeid NOT IN (0,4,12) AND b.isdeleted=0 AND b.statuscodeid>0 AND b.encounterkindcodeid=1
    AND A.STARTDATE>=TRUNC(IN_StartDateFrom) AND A.STARTDATE<TRUNC(IN_StartDateTo)+1 AND A.ALBGL IS NOT NULL;
    
    MERGE INTO POOR.TEMPALBORDERREQUESTDETAIL t
    USING (
      SELECT ENCOUNTERID, ENTITYID
        FROM (
          SELECT A.ENCOUNTERID, A.ENTITYID,
                 ROW_NUMBER() OVER (
                   PARTITION BY A.ENCOUNTERID
                   ORDER BY A.STARTTIME DESC, A.ENTITYID
                 ) rn
            FROM PRPA.ENCOUNTERPARTICIPATION A
           WHERE A.ROLECODEID = 10 AND A.ISDELETED = 0 AND A.ISLAST = 1
        )
       WHERE rn = 1
    ) D
    ON (t.ENCOUNTERID = D.ENCOUNTERID)
    WHEN MATCHED THEN
      UPDATE SET t.CLINICTEAMID = D.ENTITYID;

    MERGE INTO POOR.TEMPALBORDERREQUESTDETAIL t
    USING ROLE.CLINICTEAM D
    ON (t.CLINICTEAMID = D.CLINICTEAMID)
    WHEN MATCHED THEN
    UPDATE SET t.CLINICTEAMNAME = D.NAME;   
    
    COMMIT;
    
    OPEN ref_cursor FOR SELECT ORDERREQUESTID, ENCOUNTERID, DISPLAYNAME, SEQNOTEXT, GENDERCODEID, BIRTHTIME, ADMITDATEON, DISCHARGEON, ADMITORGANIZATIONID
    , DISCHARGEORGANIZATIONID, AUTHORID, AUTHORNAME, ADMINDOSE, FREQUENCYID, FREQUENCYNAME, ROUTEID, ROUTENAME
    , STARTDATE, ENDDATE, ALBGL, CLINICTEAMID, CLINICTEAMNAME
    FROM POOR.TEMPALBORDERREQUESTDETAIL;
END;