Oracle存储过程分页后才排序问题排查求助

问题描述 #

我编写了Oracle存储过程SP_SRVS_CLIET_MAP_CODE，意图先检索数据并排序，再根据pageNumber和pageSize进行分页。但执行后发现实际是分页完成后才进行排序，附上存储过程代码：

PROCEDURE SP_SRVS_CLIET_MAP_CODE (
  -- Mapping Data Chnaged By Fatma For CR 105726 25-10-2023
  V_Item          IN     VARCHAR2 DEFAULT NULL,
  V_MappingCode   IN     VARCHAR2 DEFAULT NULL,
  V_ClientId      IN     NUMBER DEFAULT NULL,
  V_PageSize      IN     NUMBER,
  V_PageNumber    IN     NUMBER,
  CV_1               OUT SYS_REFCURSOR)


AS
      V_SMIT    VARCHAR2 (4000);
      V_Where   VARCHAR2 (4000);
      V_COUNT   NUMBER;
   BEGIN
      V_Where := ' FROM  SERVICE 
                    WHERE 1=1 ';

  IF V_Item IS NOT NULL
  THEN
     V_Where :=
           V_Where
        || ' AND (upper(SERVICE.LOINCCODE||'' ''||SERVICE.NAME) like upper('''
        || V_Item
        || '%'' ) or SERVICE.LOINCCODE ='''
        || V_Item
        || ''
        || '''or upper(SERVICE.NAME) like upper(''%''
        || V_Item
        || '%''))'; -- modified by mohamed abdrabo for task 109039
  END IF;

  IF V_MappingCode IS NOT NULL
  THEN
     V_Where :=
           V_Where
        || ' AND SERVICE.id in  (select HL7SERVICECODES.SERVICEID  from hl7servicecodes  where HL7SERVICECODES.LOINCCODE =  '''
        || V_MappingCode
        || ''')';
  END IF;

  EXECUTE IMMEDIATE 'select count(1) ' || V_WHERE INTO V_COUNT;

  V_SMIT :=
        ' 
     SELECT '
     || V_COUNT
     || 'TotalCount,ItemId,
            ItemName,
            ItemType,
            ItemLoincCode,
            ClientMappingCode
       FROM (  SELECT ROWNUM ROW_NUM,
                      SERVICE.id ItemId,
                      SERVICE.name ItemName,
                      SERVICE.SERVICETYPEID ItemType,
                      SERVICE.LOINCCODE ItemLoincCode,';

  IF V_ClientId IS NOT NULL
  THEN
     V_SMIT :=
           V_SMIT
        || '(select hl7servicecodes.LOINCCODE  from hl7servicecodes  where HL7SERVICECODES.CLIENTID = '
        || V_ClientId
        || ' and HL7SERVICECODES.SERVICEID  =
                         SERVICE.id ) ClientMappingCode';
  ELSE
     V_SMIT := V_SMIT || 'NULL  LoincCode';
  END IF;


  V_SMIT :=
        V_SMIT
     || V_Where
     || '
              ORDER BY 
             REGEXP_REPLACE(SERVICE.name, ''[^a-zA-Z0-9]'', ''''),


TO_NUMBER(REGEXP_SUBSTR(SERVICE.name, ''\\d*'')),
  SERVICE.name)
          WHERE ROW_NUM BETWEEN ( ( '
         || V_PAGENUMBER
         || ' - 1) * '
         || V_PAGESIZE
         || ' ) + 1
                            AND ('
         || V_PAGENUMBER
         || ' * '
         || V_PAGESIZE
         || ')';



              INSERT
                INTO TT_SREACH_RESULT_STMT_LOG (SQL_STMT_LOB,
                                                GENERATE_DATE,
                                                PROCEDURE_NAME)
              VALUES (V_SMIT, SYSDATE, 'INTEGRATION_MAPPING_PKG');
  
              COMMIT;

  OPEN CV_1 FOR V_SMIT;
END SP_SRVS_CLIET_MAP_CODE;

问题分析 #

核心错误在于ROWNUM的生成时机：原代码先对未排序的查询结果生成ROW_NUM（基于ROWNUM），之后才执行ORDER BY。Oracle中ROWNUM是查询结果返回时逐行分配的，此时ROW_NUM对应排序前的原始行顺序，分页过滤的是未排序的数据，最后再对分页后的子集排序，就出现了“分页后再排序”的现象。

要实现“先排序再分页”，必须先完成全量数据的排序，再在排序后的结果集上生成行号，最后进行分页过滤。

修复后的代码 #

PROCEDURE SP_SRVS_CLIET_MAP_CODE (
  -- Mapping Data Chnaged By Fatma For CR 105726 25-10-2023
  V_Item          IN     VARCHAR2 DEFAULT NULL,
  V_MappingCode   IN     VARCHAR2 DEFAULT NULL,
  V_ClientId      IN     NUMBER DEFAULT NULL,
  V_PageSize      IN     NUMBER,
  V_PageNumber    IN     NUMBER,
  CV_1               OUT SYS_REFCURSOR)
AS
      V_SMIT    VARCHAR2 (4000);
      V_Where   VARCHAR2 (4000);
      V_COUNT   NUMBER;
   BEGIN
      V_Where := ' FROM  SERVICE 
                    WHERE 1=1 ';

  IF V_Item IS NOT NULL
  THEN
     V_Where :=
           V_Where
        || ' AND (upper(SERVICE.LOINCCODE||'' ''||SERVICE.NAME) like upper('''
        || V_Item
        || '%'' ) or SERVICE.LOINCCODE ='''
        || V_Item
        || ''' or upper(SERVICE.NAME) like upper(''%''
        || V_Item
        || '%''))'; -- modified by mohamed abdrabo for task 109039
  END IF;

  IF V_MappingCode IS NOT NULL
  THEN
     V_Where :=
           V_Where
        || ' AND SERVICE.id in  (select HL7SERVICECODES.SERVICEID  from hl7servicecodes  where HL7SERVICECODES.LOINCCODE =  '''
        || V_MappingCode
        || ''')';
  END IF;

  EXECUTE IMMEDIATE 'select count(1) ' || V_WHERE INTO V_COUNT;

  V_SMIT :=
        ' 
     SELECT '
     || V_COUNT
     || ' TotalCount,ItemId,
            ItemName,
            ItemType,
            ItemLoincCode,
            ClientMappingCode
       FROM (  
              SELECT ROWNUM ROW_NUM, t.*
              FROM (
                      SELECT SERVICE.id ItemId,
                             SERVICE.name ItemName,
                             SERVICE.SERVICETYPEID ItemType,
                             SERVICE.LOINCCODE ItemLoincCode,';

  IF V_ClientId IS NOT NULL
  THEN
     V_SMIT :=
           V_SMIT
        || '(select hl7servicecodes.LOINCCODE  from hl7servicecodes  where HL7SERVICECODES.CLIENTID = '
        || V_ClientId
        || ' and HL7SERVICECODES.SERVICEID  =
                         SERVICE.id ) ClientMappingCode';
  ELSE
     V_SMIT := V_SMIT || 'NULL ClientMappingCode'; -- 修正别名不匹配问题
  END IF;


  V_SMIT :=
        V_SMIT
     || V_Where
     || '
              ORDER BY 
             REGEXP_REPLACE(SERVICE.name, ''[^a-zA-Z0-9]'', ''''),
             TO_NUMBER(REGEXP_SUBSTR(SERVICE.name, ''\\d*'')),
             SERVICE.name
            ) t
          )
          WHERE ROW_NUM BETWEEN ( ( '
         || V_PAGENUMBER
         || ' - 1) * '
         || V_PAGESIZE
         || ' ) + 1
                            AND ('
         || V_PAGENUMBER
         || ' * '
         || V_PAGESIZE
         || ')';



              INSERT
                INTO TT_SREACH_RESULT_STMT_LOG (SQL_STMT_LOB,
                                                GENERATE_DATE,
                                                PROCEDURE_NAME)
              VALUES (V_SMIT, SYSDATE, 'INTEGRATION_MAPPING_PKG');
  
              COMMIT;

  OPEN CV_1 FOR V_SMIT;
END SP_SRVS_CLIET_MAP_CODE;

关键修改点 #

• 新增内层嵌套查询t，先完成全量数据的排序，确保结果是有序的
• 在排序后的结果集t之上生成ROW_NUM，此时行号对应排序后的正确顺序
• 修正了ELSE分支的字段别名错误（原代码写的NULL LoincCode与外层ClientMappingCode不匹配，会导致字段映射错误）

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