如何按日期取最新记录并解决Oracle存储过程ORA-01422错误？

错误根源 #

ORA-01422错误是因为你调用的四个分值函数（FN_PTJ_ANNOS_EXP、FN_PTJE_HORAS、FN_PTJE_ZONA、FN_PTJE_RANKING）内部的查询逻辑，在传入NUMRUN后从ANTECEDENTES_PERSONALES表返回了多行数据，但函数需要返回单个数值，导致精确取值操作（比如SELECT ... INTO ...）无法匹配行数要求。

同时原游标用DISTINCT获取的姓名可能不是对应NUMRUN最新日期的记录，不符合你“取每个NUMRUN最新日期记录”的需求。

解决方案 #

1. 修正游标：获取每个NUMRUN的最新日期记录 #

把原游标替换为使用窗口函数ROW_NUMBER()的查询，确保每个NUMRUN只取最新日期的那条记录的姓名：

CREATE OR REPLACE PROCEDURE PR_INSERT_DATOS IS
CURSOR CUR_DATOS IS
    SELECT NUMRUN, PNOMBRE||' '||SNOMBRE||' '||APATERNO||' '||AMATERNO AS NOMBRE
    FROM (
        SELECT 
            NUMRUN,
            PNOMBRE,
            SNOMBRE,
            APATERNO,
            AMATERNO,
            -- 假设日期字段名为FECHA，按日期倒序排序，取每组第一条
            ROW_NUMBER() OVER (PARTITION BY NUMRUN ORDER BY FECHA DESC) AS RN
        FROM ANTECEDENTES_PERSONALES
    )
    WHERE RN = 1
    ORDER BY NUMRUN;
    V_PTJE_ANNOS_EXP NUMBER(8);
    V_PTJE_HORAS_TRAB NUMBER(8);
    V_PTJE_ZONA NUMBER(8);
    V_PTJE_RANKING NUMBER(8);
BEGIN
    EXECUTE IMMEDIATE('TRUNCATE TABLE DETALLE_PUNTAJE_POSTULACION');

    FOR REG_DATOS IN CUR_DATOS LOOP
        V_PTJE_ANNOS_EXP:=FN_PTJ_ANNOS_EXP(REG_DATOS.NUMRUN);
        V_PTJE_HORAS_TRAB:=FN_PTJE_HORAS(REG_DATOS.NUMRUN);
        V_PTJE_ZONA:=FN_PTJE_ZONA(REG_DATOS.NUMRUN);
        V_PTJE_RANKING:=FN_PTJE_RANKING(REG_DATOS.NUMRUN);
        INSERT INTO DETALLE_PUNTAJE_POSTULACION VALUES (REG_DATOS.NUMRUN, REG_DATOS.NOMBRE, V_PTJE_ANNOS_EXP,
        V_PTJE_HORAS_TRAB, V_PTJE_ZONA, V_PTJE_RANKING, 0, 0);
    END LOOP;

END;

注意：将查询中的FECHA替换为你表中实际的日期字段名。

2. 修正分值函数：确保每个NUMRUN只返回单行结果 #

以FN_PTJ_ANNOS_EXP为例，修改函数内部的查询逻辑，只取对应NUMRUN最新日期的记录来计算分值：

CREATE OR REPLACE FUNCTION FN_PTJ_ANNOS_EXP(P_NUMRUN IN VARCHAR2) RETURN NUMBER IS
    V_RESULTADO NUMBER(8);
BEGIN
    -- 替换为你原来的计算逻辑，但确保只取最新日期的记录
    SELECT 
        -- 示例：按入职日期计算工作年限分值
        FLOOR(MONTHS_BETWEEN(SYSDATE, FECHA_INGRESO)/12) * 5
    INTO V_RESULTADO
    FROM (
        SELECT FECHA_INGRESO -- 替换为你需要的字段
        FROM ANTECEDENTES_PERSONALES
        WHERE NUMRUN = P_NUMRUN
        ORDER BY FECHA DESC -- 替换为实际日期字段
        FETCH FIRST 1 ROW ONLY -- 只取最新的一行
    );
    
    RETURN V_RESULTADO;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 0; -- 无记录时返回0或你需要的默认值
    WHEN OTHERS THEN
        RAISE;
END;

对其他三个函数执行同样的修改，确保每个函数内部查询ANTECEDENTES_PERSONALES时，只获取对应NUMRUN的最新日期记录，避免返回多行。

3. 可选优化：避免重复查询 #

如果四个函数都需要查询同一条最新记录，可以考虑在存储过程中先获取该记录的所有必要字段，再传入函数计算，减少重复查询数据库的开销：

CREATE OR REPLACE PROCEDURE PR_INSERT_DATOS IS
    TYPE T_ANTECEDENTE IS RECORD (
        NUMRUN VARCHAR2(20),
        NOMBRE VARCHAR2(100),
        FECHA_INGRESO DATE,
        HORAS_TRAB NUMBER,
        ZONA VARCHAR2(50),
        RANKING NUMBER
        -- 其他需要的字段
    );
    CURSOR CUR_DATOS IS
        SELECT 
            NUMRUN,
            PNOMBRE||' '||SNOMBRE||' '||APATERNO||' '||AMATERNO AS NOMBRE,
            FECHA_INGRESO,
            HORAS_TRAB,
            ZONA,
            RANKING
        FROM (
            SELECT 
                NUMRUN,
                PNOMBRE,
                SNOMBRE,
                APATERNO,
                AMATERNO,
                FECHA_INGRESO,
                HORAS_TRAB,
                ZONA,
                RANKING,
                ROW_NUMBER() OVER (PARTITION BY NUMRUN ORDER BY FECHA DESC) AS RN
            FROM ANTECEDENTES_PERSONALES
        )
        WHERE RN = 1
        ORDER BY NUMRUN;
    V_ANTECEDENTE T_ANTECEDENTE;
    V_PTJE_ANNOS_EXP NUMBER(8);
    V_PTJE_HORAS_TRAB NUMBER(8);
    V_PTJE_ZONA NUMBER(8);
    V_PTJE_RANKING NUMBER(8);
BEGIN
    EXECUTE IMMEDIATE('TRUNCATE TABLE DETALLE_PUNTAJE_POSTULACION');

    FOR V_ANTECEDENTE IN CUR_DATOS LOOP
        -- 直接传入记录字段给函数，避免函数再次查询
        V_PTJE_ANNOS_EXP:=FN_PTJ_ANNOS_EXP(V_ANTECEDENTE.FECHA_INGRESO);
        V_PTJE_HORAS_TRAB:=FN_PTJE_HORAS(V_ANTECEDENTE.HORAS_TRAB);
        V_PTJE_ZONA:=FN_PTJE_ZONA(V_ANTECEDENTE.ZONA);
        V_PTJE_RANKING:=FN_PTJE_RANKING(V_ANTECEDENTE.RANKING);
        INSERT INTO DETALLE_PUNTAJE_POSTULACION VALUES (V_ANTECEDENTE.NUMRUN, V_ANTECEDENTE.NOMBRE, V_PTJE_ANNOS_EXP,
        V_PTJE_HORAS_TRAB, V_PTJE_ZONA, V_PTJE_RANKING, 0, 0);
    END LOOP;

END;

同时修改函数参数为具体字段，减少数据库查询次数，提升性能。

内容的提问来源于stack exchange，提问作者Diego Henriquez Bravo