处理就诊表PersonID字段异常数据脚本

处理就诊表PersonID字段异常数据脚本：

--第一步：先定位基准记录和待处理异常数据
--用窗口函数快速拿到每组encounterid最小的基准记录的标准姓名，筛选出所有姓名不一致的待处理数据：
-- 提前创建全局临时表（只需执行一次）
CREATE GLOBAL TEMPORARY TABLE APPS.temp_need_handle_encounter (
    encounterid NUMBER,
    old_personid NUMBER,
    new_personid NUMBER,
    current_name VARCHAR2(100),
    standard_name VARCHAR2(100)
) ON COMMIT PRESERVE ROWS;

--select a.* from APPS.temp_base_standard a;
CREATE GLOBAL TEMPORARY TABLE APPS.temp_base_standard (
    encounterid NUMBER,
    personid NUMBER,
    person_name VARCHAR2(100),
    gendcodeid number,
    birthdate date
) ON COMMIT PRESERVE ROWS;

INSERT INTO APPS.temp_base_standard (encounterid, personid, PERSON_NAME)
SELECT MIN(A.ENCOUNTERID) AS ENCOUNTERID, a.personid, b.displayname--, a.gendercodeid, a.birthtime
FROM PRPA.encounter a, ENTITY.person b
WHERE a.personid=b.personid AND a.isdeleted=0 and b.isdeleted=0 
GROUP BY a.personid, b.displayname--, a.gendercodeid, a.birthtime
ORDER BY ENCOUNTERID;
COMMIT;

--select a.* from APPS.temp_base_standard a WHERE a.person_name='陈小菊';
--select a.* from prpa.encounter a where a.personid=3241298;
--select distinct(a.personid, a.PERSON_NAME) from APPS.temp_base_standard a 
MERGE INTO APPS.temp_base_standard t
USING prpa.encounter s
ON (t.ENCOUNTERID = s.ENCOUNTERID)
WHEN MATCHED THEN 
    UPDATE SET t.gendcodeid = s.gendercodeid, t.birthdate = s.birthtime;
COMMIT;

-- 筛选出所有和标准姓名不一致的待处理encounter记录
INSERT INTO APPS.temp_need_handle_encounter(encounterid, old_personid, current_name, standard_name)
SELECT DISTINCT e.encounterid, e.personid AS old_personid, p.DISPLAYNAME AS current_name, e.DISPLAYNAME
FROM PRPA.encounter e
LEFT JOIN ENTITY.PERSON p ON e.personid = p.personid
where p.DISPLAYNAME <> e.DISPLAYNAME; -- 过滤出姓名不一致的异常记录
COMMIT;
--SELECT * FROM APPS.temp_need_handle_encounter
--第二步：插入新的person记录，生成新的自增personId
-- 注意：如果同一条姓名对应多条待处理encounter，需要用DISTINCT去重避免重复插入
DECLARE
    -- 1. 定义变量：存储游标遍历的名称、新生成的自增主键ID
    TYPE detail_record IS RECORD
    ( standard_name APPS.temp_need_handle_encounter.standard_name%type
    --  , current_name APPS.temp_need_handle_encounter.current_name%type
    --  , old_personid APPS.temp_need_handle_encounter.old_personid%type
    );
    TYPE detail_cursor IS REF CURSOR RETURN detail_record;
    v_prodcur detail_cursor;
    v_prodrec detail_record;
    v_old_personid  APPS.temp_need_handle_encounter.old_personid%type;
    v_current_name  APPS.temp_need_handle_encounter.current_name%type;
    v_standard_name  APPS.temp_need_handle_encounter.standard_name%type;
    v_new_person_id  NUMBER; 
    v_gendcodeid  NUMBER; 
    v_birthdate  DATE; 
BEGIN
    -- 2. 打开游标逐行循环处理
    OPEN v_prodcur FOR SELECT DISTINCT standard_name 
        FROM APPS.temp_need_handle_encounter
        -- 可选加过滤条件，比如只处理new_personId为空的未处理数据
        WHERE new_personId IS NULL;
    LOOP
        FETCH v_prodcur INTO v_prodrec ;
        EXIT WHEN v_prodcur%notfound;
        
        v_standard_name:=v_prodrec.standard_name;
        -- 3. 插入person记录，不指定自增主键，自动生成ID，同时用RETURNING捕获刚生成的主键
        select t.old_personid, t.current_name into v_old_personid, v_current_name 
        from (select a.*
              from APPS.temp_need_handle_encounter a
              where a.standard_name = v_standard_name order by a.encounterid) t
        where rownum = 1;
        
        select a.gendcodeid, a.birthdate into v_gendcodeid, v_birthdate
        from APPS.temp_base_standard a
        where a.personid = v_old_personid and a.person_name=v_current_name;
        
        INSERT INTO ENTITY.person (DISPLAYNAME, GENDERCODEID, BIRTHTIME)
        VALUES (v_standard_name, v_gendcodeid, v_birthdate)
        RETURNING PERSONID INTO v_new_person_id; -- 把生成的主键返回给变量v_new_person_id

        -- 4. 用刚拿到的新ID，批量更新临时表里所有同名的记录
        UPDATE APPS.temp_need_handle_encounter
        SET new_personId = v_new_person_id
        WHERE standard_name = v_standard_name;
    END LOOP;
    CLOSE v_prodcur;

    -- 可选：提交事务（如果外层没有统一事务管理）
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- 异常回滚，同时返回Oracle原生错误码和错误描述
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('执行出错，错误码：' || SQLCODE || '，错误信息：' || SQLERRM);
        RAISE; -- 可根据需求选择是否重新抛出异常给上层调用者
END;
/