Управление сортировкой с помощью параметра

Возник вопрос: можно ли управлять порядком сортировки (asc/desc) при помощи параметра из прикладного приложения? Типа order by x :direction. Ответ - да!, но не совсем так.

Признаюсь сразу: решение не мое, а команды Тома Кайта; то ли Крис, то ли Коннор - но я разобрался и немного переформулировал.

В первом приближении, конструкция вида order by x :direction корректной не является, но, как говорится, мы поняли, что нужно заказчику.

Вначале, как всегда, запишем генератор данных:

select level as x from dual connect by level <= 5
;

1
2
3
4
5

Запишем прямую сортировку:

with t as (
select level as x from dual connect by level <= 5
)
select x from t
order by x asc
;

1
2
3
4
5

Запишем обратную сортировку:

with t as (
select level as x from dual connect by level <= 5
)
select x from t
order by x desc
;

5
4
3
2
1

Запишем обе сортировки вместе:

with t as (
select level as x from dual connect by level <= 5
)
select x from t
order by
x asc,
x desc
;

Понятно, что всегда будет срабатывать первая - а можно ли сделать выключатель? Или переключатель? Да! Пусть значение параметра :direction = 1 означает прямую сортировку (asc), а значение :direction = -1 означает обратную сортировку (desc); тогда:

with t as (
select level as x from dual connect by level <= 5
)
select x from t
order by
case :direction when '1' then x end asc,
case :direction when '-1' then x end desc
;

Дело в том, что при несовпадении оператор case возвращает пустое значение, null, константу - а сортировка по константе означает ее выключение. Соответственно, если :direction = 1, то первый case пропускает x, а второй case возвращает null, т.е. вторая сортировка отключается. И наоборот.

Мы получили механизм управления сортировкой с помощью параметра. И не только направлением: при необходимости мы можем записать гораздо более сложные управляемые правила.