Oracle SQL order by case条件

fv2wmkja  于 12个月前  发布在  Oracle
关注(0)|答案(2)|浏览(151)

我有问题,如何写SQL语句与ORDER BY和CASE在参数
输入参数:
p_desc = 0
p_sort_by = 'name'

ORDER BY 
  CASE NVL(p_desc, 0) WHEN 1 THEN NVL(p_sort_by, 1) END DESC,
  CASE NVL(p_desc, 0) WHEN 0 THEN NVL(p_sort_by, 1) END ASC

字符串

qxsslcnc

qxsslcnc1#

ORDER BY子句中使用CASE表达式将所有组合列入白名单。当条件不匹配时,CASE表达式将返回NULL,并且所有行将对未匹配的CASE表达式具有相同的顺序,因此只有匹配的CASE表达式才会对顺序产生影响。

ORDER BY 
  CASE WHEN p_sort_by = 'COLUMN1' AND NVL(p_desc, 0) = 0 THEN column1 END ASC,
  CASE WHEN p_sort_by = 'COLUMN1' AND p_desc = 1         THEN column1 END DESC,
  CASE WHEN p_sort_by = 'COLUMN2' AND NVL(p_desc, 0) = 0 THEN column2 END ASC,
  CASE WHEN p_sort_by = 'COLUMN2' AND p_desc = 1         THEN column2 END DESC,
  CASE WHEN p_sort_by = 'COLUMN3' AND NVL(p_desc, 0) = 0 THEN column3 END ASC,
  CASE WHEN p_sort_by = 'COLUMN3' AND p_desc = 1         THEN column3 END DESC,
  CASE WHEN p_sort_by = 'COLUMN4' AND NVL(p_desc, 0) = 0 THEN column4 END ASC,
  CASE WHEN p_sort_by = 'COLUMN4' AND p_desc = 1         THEN column4 END DESC

字符串

5fjcxozz

5fjcxozz2#

如果它必须是 dynamic 的,也许一个返回ref cursor的函数会有所帮助。
函数编写SELECT语句,并将参数的 * 占位符 * 替换为传递给函数的值。

SQL> create or replace function f_test(p_desc in number, p_sort_by in varchar2)
  2    return sys_refcursor
  3  is
  4    l_str varchar2(500);
  5    rc    sys_refcursor;
  6  begin
  7    l_str := 'select empno, ename, job, sal from emp where deptno = 10 ' ||
  8             'order by ' ||
  9             'case nvl(p_desc, 0) when 1 then nvl(p_sort_by, 1) end desc, ' ||
 10             'case nvl(p_desc, 0) when 0 then nvl(p_sort_by, 1) end asc';
 11
 12    l_str := replace(l_str, 'p_desc', p_desc);
 13    l_str := replace(l_str, 'p_sort_by', p_sort_by);
 14
 15    open rc for l_str;
 16    return rc;
 17  end;
 18  /

Function created.

字符串
让我们尝试一下:按ename升序排序结果:

SQL> select f_test(0, 'ename') from dual;

F_TEST(0,'ENAME')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7782 CLARK      MANAGER         2450
      7839 KING       PRESIDENT       5000
      7934 MILLER     CLERK           1300


ename降序排序:

SQL> select f_test(1, 'ename') from dual;

F_TEST(1,'ENAME')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7934 MILLER     CLERK           1300
      7839 KING       PRESIDENT       5000
      7782 CLARK      MANAGER         2450


job升序排序:

SQL> select f_test(0, 'job') from dual;

F_TEST(0,'JOB')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7934 MILLER     CLERK           1300
      7782 CLARK      MANAGER         2450
      7839 KING       PRESIDENT       5000

相关问题