如何使 Oracle 查询排序顺序动态化? [英] How Can I Make Oracle Query Sort Order Dynamic?

问题描述

我在这样的包中有一个 Oracle 过程

I have a Oracle procedure inside a package like this

PROCEDURE getEmployee
(
  pinLanguage               IN    VARCHAR2,
  pinPage                   IN    NUMBER,
  pinPageSize               IN    NUMBER,
  pinSortColumn             IN    VARCHAR2,
  pinSortOrder              IN    VARCHAR2,
  poutEmployeeCursor        OUT   SYS_REFCURSOR
)
AS
BEGIN
    OPEN poutEmployeeCursor FOR
    SELECT * FROM (
      SELECT EMPLOYEE_ID, USERNAME, FULL_NAME, DATE_OF_BIRTH, EMP.GENDER_ID, GEN_TR.GENDER, EMP.WORK_TYPE_ID, WT_TR.WORK_TYPE, SALARY, EMAIL, PROFILE_IMAGE,
      ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID ASC) RN

      FROM EMPLOYEES EMP
      INNER JOIN GENDERS GEN ON EMP.GENDER_ID = GEN.GENDER_ID
      LEFT JOIN GENDERS_MLD GEN_TR ON GEN.GENDER_ID = GEN_TR.GENDER_ID AND GEN_TR.LANGUAGE = pinLanguage
      INNER JOIN WORK_TYPES WT ON EMP.WORK_TYPE_ID = WT.WORK_TYPE_ID
      LEFT JOIN WORK_TYPES_MLD WT_TR ON WT.WORK_TYPE_ID = WT_TR.WORK_TYPE_ID AND WT_TR.LANGUAGE = pinLanguage
    )
    WHERE RN BETWEEN (((pinPage - 1) * pinPageSize) + 1) AND (pinPage * pinPageSize);
END;

我需要使上述查询的排序顺序动态

I need to make the sort order of the above query dynamic

如果我将文本 FullName 传递给 pinSortColumn 参数，它需要对 FULL_NAME 列进行排序

If I pass the text FullName to pinSortColumn parameter, it need to sort FULL_NAME column

如果我将文本 DateOfBirth 传递给 pinSortColumn 参数，它需要对 DATE_OF_BIRTH 列进行排序

If I pass the text DateOfBirth to pinSortColumn parameter, it need to sort DATE_OF_BIRTH column

如果我将文本 Gender 传递给 pinSortColumn 参数，它需要对 GEN_TR.GENDER 列进行排序

If I pass the text Gender to pinSortColumn parameter, it need to sort GEN_TR.GENDER column

我可以将文本 asc 或 desc 传递给 pinSortOrder 参数，并且查询需要相应地进行排序.你能帮我实现这个目标吗?

I can pass the text asc or desc to pinSortOrder parameter and the query need to be sorted accordingly. Can you please help me to achieve this?

推荐答案

asc 和 desc 可以使用单独的 order by 如下:

You can use separate order by for asc and desc as following:

ORDER BY 
CASE pinSortOrder WHEN 'asc' THEN
    CASE pinSortColumn 
      WHEN 'FullName' THEN FULL_NAME 
      WHEN 'DateOfBirth' THEN to_char(DATE_OF_BIRTH,'yyyymmddhh24miss')
      WHEN 'Gender' THEN GEN_TR.GENDER   
    END
END,
CASE pinSortOrder WHEN 'desc' THEN
    CASE pinSortColumn 
      WHEN 'FullName' THEN FULL_NAME 
      WHEN 'DateOfBirth' THEN to_char(DATE_OF_BIRTH,'yyyymmddhh24miss')
      WHEN 'Gender' THEN GEN_TR.GENDER   
    END
END DESC

假设您已将 pinSortColumn 作为 FullName 和 pinSortOrder 作为 asc 传递，那么 order by 子句将是 ORDER BY FULL_NAME, NULL DESC(请注意默认顺序是asc所以我没有写在代码中.查询将以FULL_NAME升序排列)

Let's say you have passed pinSortColumn as FullName and pinSortOrder as asc then order by clause will be ORDER BY FULL_NAME, NULL DESC (please note that default order will be asc so I have not write it in the code. Query will be ordered by FULL_NAME in ascending manner)

现在，如果您已将 pinSortColumn 作为 FullName 和 pinSortOrder 作为 desc 传递，则 order by 子句将是ORDER BY NULL，FULL_NAME DESC.

Now, If you have passed pinSortColumn as FullName and pinSortOrder as desc then order by clause will be ORDER BY NULL, FULL_NAME DESC.

Null 不会影响排序.

Null will not impact ordering.

我希望现在清楚了.

干杯！！

这篇关于如何使 Oracle 查询排序顺序动态化?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！