Oracle存储过程：获取符合条件的子表最大JOB_PROGRESS_ID及主表字段

需求说明 #

编写Oracle存储过程，实现以下逻辑：

• 从子表TBL_FIBER_INV_JOB_PROGRESS中，为每个JOB_ID筛选出满足UMS_GROUP_ASS_BY_NAME = 'CMM'且UMS_GROUP_ASS_TO_NAME IS NULL条件的最大JOB_PROGRESS_ID
• 关联主表TBL_FIBER_INV_JOBS，返回主表的JOB_ID、SPAN_ID、LINK_ID三个字段

表结构 #

主表 TBL_FIBER_INV_JOBS #

TBL_FIBER_INV_JOBS (主表)
Name                      Null?    Type           
------------------------- -------- -------------- 
JOB_ID                    NOT NULL NUMBER         
SPAN_ID                            NVARCHAR2(100) 
LINK_ID                            NVARCHAR2(100) 
CREATED_BY                         NVARCHAR2(200) 
CREATED_DATE              NOT NULL DATE           
MAINTENANCEZONECODE                NVARCHAR2(50)  
MAINTENANCEZONENAME                NVARCHAR2(100) 
MAINT_ZONE_NE_SPAN_LENGTH          NUMBER(10,4)   
SPAN_TYPE                          NVARCHAR2(20)  
JOB_FLAG                           NUMBER         
MISSING_ABD_LENGTH                 NUMBER(38,10)  
REOFFERFLAG                        VARCHAR2(10)   

子表 TBL_FIBER_INV_JOB_PROGRESS #

TBL_FIBER_INV_JOB_PROGRESS (子表)
Name                   Null?    Type           
---------------------- -------- -------------- 
JOB_PROGRESS_ID        NOT NULL NUMBER         
JOB_ID                          NUMBER         
STATUS_ID                       NUMBER 
APPROVED_BY                     NVARCHAR2(200) 
APPROVED_DATE                   DATE           
REJECTED_BY                     NVARCHAR2(200) 
REJECTED_DATE                   DATE           
APPROV_REJECT_REMARK            NVARCHAR2(255) 
DELAY_REASON                    NVARCHAR2(255) 
ISABDMISSING                    NUMBER         
HOTO_OFFERED_LENGTH             NUMBER         
LIT_OFFERED_LENGTH              NUMBER         
HOTO_ACTUAL_LENGTH              NUMBER         
LIT_ACTUAL_LENGTH               NUMBER         
ABD_COMPLETED_LENGTH            NUMBER         
NE_SPAN_LENGTH                  NUMBER(10,4)   
CREATED_BY                      NVARCHAR2(200) 
CREATED_DATE           NOT NULL DATE           
MODIFIED_BY                     NVARCHAR2(200) 
MODIFIED_DATE                   DATE           
UMS_GROUP_ASS_BY_ID             NUMBER         
UMS_GROUP_ASS_BY_NAME           NVARCHAR2(200) 
UMS_GROUP_ASS_TO_ID             NUMBER         
UMS_GROUP_ASS_TO_NAME           NVARCHAR2(200) 
UMS_GROUP_ASS_TO_DATE           DATE           
JOB_PROGRESS_FLAG      NOT NULL NUMBER 

用户尝试的查询（结果不符合预期） #

-- 子表查询：按hoto_actual_length分组逻辑错误，未关联主表
select max(job_progress_id),hoto_actual_length 
from tbl_fiber_inv_job_progress 
where job_id = 86753 and ums_group_ass_by_name='CMM' and ums_group_ass_to_name is null   
group by hoto_actual_length;

-- 主表查询：单JOB_ID下分组无意义，未关联子表
select max(job_id), span_id, job_flag,nvl(missing_abd_length,0) missing_abd_length, 
       maint_zone_ne_span_length, maintenancezonecode   
from tbl_fiber_inv_jobs 
where job_id = 86753 and job_flag = 0 and span_type <> 'FTTX'  
group by span_id, job_flag,missing_abd_length, maint_zone_ne_span_length,maintenancezonecode;

正确解决方案 #

核心查询逻辑 #

先通过子查询获取符合条件的每个JOB_ID对应的最大JOB_PROGRESS_ID，再关联主表提取所需字段：

SELECT 
    MJ.JOB_ID,
    MJ.SPAN_ID,
    MJ.LINK_ID,
    MP.MAX_JOB_PROGRESS_ID
FROM TBL_FIBER_INV_JOBS MJ
INNER JOIN (
    -- 子查询：按JOB_ID分组，取符合条件的最大进度ID
    SELECT JOB_ID, MAX(JOB_PROGRESS_ID) AS MAX_JOB_PROGRESS_ID
    FROM TBL_FIBER_INV_JOB_PROGRESS
    WHERE UMS_GROUP_ASS_BY_NAME = 'CMM' 
      AND UMS_GROUP_ASS_TO_NAME IS NULL
    GROUP BY JOB_ID
) MP ON MJ.JOB_ID = MP.JOB_ID
-- 可根据需求添加主表过滤条件，比如用户原查询中的JOB_FLAG和SPAN_TYPE限制
WHERE MJ.JOB_FLAG = 0 
  AND MJ.SPAN_TYPE <> 'FTTX';

封装为Oracle存储过程 #

将上述逻辑封装为存储过程，通过游标返回结果集：

CREATE OR REPLACE PROCEDURE GET_JOB_MAX_PROGRESS
(
    p_result_cursor OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_result_cursor FOR
        SELECT 
            MJ.JOB_ID,
            MJ.SPAN_ID,
            MJ.LINK_ID,
            MP.MAX_JOB_PROGRESS_ID
        FROM TBL_FIBER_INV_JOBS MJ
        INNER JOIN (
            SELECT JOB_ID, MAX(JOB_PROGRESS_ID) AS MAX_JOB_PROGRESS_ID
            FROM TBL_FIBER_INV_JOB_PROGRESS
            WHERE UMS_GROUP_ASS_BY_NAME = 'CMM' 
              AND UMS_GROUP_ASS_TO_NAME IS NULL
            GROUP BY JOB_ID
        ) MP ON MJ.JOB_ID = MP.JOB_ID
        WHERE MJ.JOB_FLAG = 0 
          AND MJ.SPAN_TYPE <> 'FTTX';
END GET_JOB_MAX_PROGRESS;
/

存储过程调用示例 #

DECLARE
    v_cursor SYS_REFCURSOR;
    v_job_id NUMBER;
    v_span_id NVARCHAR2(100);
    v_link_id NVARCHAR2(100);
    v_max_progress_id NUMBER;
BEGIN
    GET_JOB_MAX_PROGRESS(v_cursor);
    LOOP
        FETCH v_cursor INTO v_job_id, v_span_id, v_link_id, v_max_progress_id;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('JOB_ID: ' || v_job_id || ', SPAN_ID: ' || v_span_id || ', LINK_ID: ' || v_link_id || ', 最大进度ID: ' || v_max_progress_id);
    END LOOP;
    CLOSE v_cursor;
END;
/

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