在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

字符串

如果它必须是 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

型