Блог

У меня есть хранимая процедура Oracle, которая отвечает за разбивку данных запроса на страницы. Он получает текст для поиска, смещение и номер страницы. Теперь я хочу добавить два новых параметра для сортировки результата запроса перед разбиением его на страницы. Мой вопрос в том, как я могу указать динамический порядок и направление.

На данный момент, я думаю, что я должен использовать предложение CASE для указания столбца, но мне нужно добавить, является ли это ASC или DESC.

 CREATE OR REPLACE PROCEDURE search_city_paged (
        in_search_key     IN                VARCHAR2,
        in_offset         IN                NUMBER,
        in_num_elements   IN                NUMBER,
        in_polling_place  IN                VARCHAR2,
        in_order_by       IN                NUMBER,
        in_order_dir      IN                VARCHAR2,
        out_city          OUT               SYS_REFCURSOR
    ) AS

BEGIN

    OPEN out_citizens FOR 
        SELECT COUNT(*) OVER (),
               cit.city_code,
               cit.city_name,
               cit.city_zip,
               cit.city_cities
        FROM city cit
        WHERE CATSEARCH(cit.FULL_TEXT_SEARCH, l_in_search_key, NULL)>0
        ORDER BY ???
        OFFSET in_offset ROWS FETCH FIRST in_num_elements ROWS ONLY;
END search_city_paged;
  

Если я получу в новых параметрах 1 и ASC, результат будет отсортирован по коду city_code ASC.
Если я получу в новых параметрах 1 и DESC, результат будет отсортирован по city_code DESC.
Если я получу в новых параметрах 2 и ASC, результат будет отсортирован по имени city_name ASC.
Если я получу в новых параметрах 2 и DESC, результат будет отсортирован по имени city_name DESC.

И… таким же образом в каждом столбце я должен разрешить сортировку результатов.

Порядок по двум внешним CASE выражениям, одному ASC one DESC . В первом из них проверьте, хотите ли вы выполнить сортировку ASC , и если нет, пусть выражение возвращает константу or NULL , т.е. порядок не меняется. Для случая, когда вы хотите отсортировать, ASC добавьте внутреннее CASE выражение, которое возвращает столбец для сортировки. Аналогично обработайте DESC случай.

 ...
ORDER BY CASE
           WHEN in_order_dir = 'ASC' THEN
             CASE
               WHEN in_order_by = 1 THEN
                 city_code
               WHEN in_order_by = 2 THEN
                 city_name
               ...
             END
         END ASC,
         CASE
           WHEN in_order_dir = 'DESC' THEN
             CASE
               WHEN in_order_by = 1 THEN
                 city_code
               WHEN in_order_by = 2 THEN
                 city_name
               ...
             END
         END DESC;
  

Возможно, вам придется адаптировать его из-за несовместимости типов между столбцами, я не могу сказать этого из того, что вы опубликовали. Но это должно передавать общую концепцию.

Редактировать:

О проблеме типа:

Одна из возможностей заключается в преобразовании столбцов в совместимые типы данных таким образом, чтобы порядок сохранялся. Например, если у вас есть char_column (типа char ), date_column и integer_column (из date и integer ), вы могли бы сделать

 to_char(date_column, 'YYYYMMDDHH24MISS')
  

для преобразования даты и

 lpad(11, 38, '0')
  

для преобразования целого числа в char .

Самое простое, что нужно сделать, но требующее больше работы (для программиста, с точки зрения производительности, не должно быть никакой существенной разницы вообще), — это еще раз разделить выражения. Т.Е. иметь внешнее CASE значение для каждого типа, ваши столбцы для каждого направления.

Нравится

 ORDER BY CASE
           WHEN in_order_dir = 'ASC' THEN
             CASE
               WHEN in_order_by = 1 THEN
                 char_column1
               WHEN in_order_by = 2 THEN
                 char_column2
               ...
             END
         END ASC,
         CASE
           WHEN in_order_dir = 'ASC' THEN
             CASE
               WHEN in_order_by = 3 THEN
                 date_column1
               WHEN in_order_by = 4 THEN
                 date_column2
               ...
             END
         END ASC,
  

и так далее для всех типов и аналогично для DESC . Помните, что если столбец не предназначен для сортировки после такого, то CASE это даст NULL результат, и сортировка по этому не повлияет на порядок.