Расширенные возможности сортировки Oracle
выходят далеко за рамки простейшей сортировки A-Z
, которая реализуется секцией ORDER BY
. Сложности, встречающиеся в международных наборах символов, не решаются простой алфавитной сортировкой. Скажем, в китайском языке существует около 70 000 символов (хотя не все они встречаются при повседневном использовании). Такое разнообразие явно не укладывается в простую схему сортировки.
О порядке сортировки строк часто забывают в ходе глобализации, пока продукт не доберется до группы тестирования. Упорядочение имен работников, городов или клиентов — задача намного более сложная, чем простые формулировки «A предшествует B». Необходимо учесть следующие факторы:
- В некоторых европейских символах встречаются диакритические элементы, изменяющие смысл базовой буквы. Скажем, буква «a» отличается от «а». Какая из них должна стоять на первом месте в порядке
ORDER BY
? - Каждый локальный контекст может иметь собственные правила сортировки, поэтому многоязыковое приложение должно поддерживать разные правила сортировки в зависимости от текста. Даже регионы с одинаковыми алфавитами могут иметь разные правила сортировки.
Oracle
поддерживает три вида сортировки: двоичную, одноязычную и многоязычную. Консорциум Юникода публикует свой алгоритм сортировки, так что мы можем сравнить вывод наших запросов для этих трех типов сортировки с ожидаемыми результатами, приведенными на сайте Юникода.
Двоичная сортировка
Двоичная сортировка основана на кодировке символов. Она работает очень быстро и особенно удобна при работе с данными, которые хранятся в верхнем регистре. Двоичная сортировка чаще всего применяется для ASCII
-текста и английского алфавита, но даже в этом случае возможны некоторые нежелательные результаты. Скажем, в ASCII
буквы верхнего регистра располагаются до их представлений в нижнем регистре. Следующий пример из схемы g11n
демонстрирует результаты двоичной сортировки названий городов в Германии:
SELECT city
FROM store_location
WHERE country <> 'JP'
ORDER BY city;
Отсортированный список результатов выглядит так:
CITY
------------------------------------
Abdêra
Asselfingen
Astert
Auufer
Außernzell
Aßlar
Boßdorf
Bösleben
Bötersen
Cremlingen
Creuzburg
Creußen
Oberahr
Zudar
Zühlen
Ängelholm
...lsen
Обратите внимание на порядок следования городов в списке: Angelholm
следует после Zuhlen
. Коды символов сортируются по возрастанию; так формируется порядок A-Z
в приведенном листинге. Аномалии возникают из-за символов, отсутствующих в английском алфавите.
Одноязычная сортировка
Средства одноязычной сортировки Oracle
пригодятся при работе со многими европейскими языками. Вместо базовых кодов в схеме кодировки символов, как при двоичной сортировке, позиция символа при одноязычной сортировке определяется двумя значениями. С каждым символом связывается основное значение, соответствующее базовому символу, и дополнительное значение, определяемое регистром и различиями в диакритических элементах. При несовпадении основных значений порядок сортировки определяется однозначно. Если основные значения совпадают, используется дополнительное значение. Таким образом обеспечивается правильный порядок следования символа «о» по отношению к «о».
Чтобы увидеть, как выбор этого типа сортировки влияет на упорядочение «нестандартных» символов, вернемся к предыдущему примеру и включим для текущего сеанса одноязычную сортировку для немецкого языка:
ALTER SESSION SET NLS_SORT = german;
Получив подтверждение об изменении настроек сеанса, выполним следующий запрос:
SELECT city
FROM store_location
WHERE country <> 'JP'
ORDER BY city;
Обратите внимание на изменение порядка названий городов:
CITY
------------------------------------
Abdêra
Ängelholm
Aßlar
Asselfingen
Astert
Außernzell
Auufer
Boßdorf
Bösleben
Bötersen
Cremlingen
Creußen
Creuzburg
Oberahr
...lsen
Zudar
Zühlen
Гораздо лучше! Порядок следования символов, не входящих в английский алфавит, теперь соответствует правилам немецкого языка. Кстати говоря, если вы не хотите (или не можете) изменять сеансовые настройки NLS
, используйте функцию NLSSORT
и параметр NLS_SORT
в составе запроса:
FUNCTION city_order_by_func (v_order_by IN VARCHAR2)
RETURN sys_refcursor
IS
v_city sys_refcursor;
BEGIN
OPEN v_city
FOR
SELECT city
FROM store_location
ORDER BY NLSSORT (city, 'NLS_SORT=' || v_order_by);
RETURN v_city;
END city_order_by_func;
Функция NLSSORT
и параметр NLS_SORT
предоставляют простые средства для изменения результатов ORDER
BY
. Приведенная функция, используемая в последующих примерах, получает параметр NLS_SORT
во входных данных. В табл. 1 перечислены некоторые значения параметра NLS_SORT
, доступные в Oraclellg
.
Таблица 1. Значения параметра NLS_SORT
для одноязычной сортировки
arabic | xcatalan | japanese |
arabic_abj_sort | german | polish |
arabic_match | xgerman | punctuation |
arabic_abj_match | german_din | xpunctuation |
azerbaijani | xgerman_din | romanian |
xazerbaijani | hungarian | russian |
bengali | xhungarian | spanish |
bulgarian | icelandic | xspanish |
canadian french | indonesian | west_european |
catalan | italian | xwest_european |
Некоторые значения в этом списке начинаются с префикса х: это расширенные режимы сортировки для особых случаев в языке. В приведенном примере с городами некоторые названия содержат символ В. В немецком языке при сортировке этот символ может интерпретироваться как последовательность «ss
». Ранее мы использовали сортировку со значением NLS_SORT
= german
. Давайте посмотрим, какой результат будет получен в режиме xgerman
:
VARIABLE v_city_order REFCURSOR
CALL city_order_by_func('xgerman') INTO :v_city_order;
PRINT v_city_order
Результат:
CITY
------------------------------------
...
Abdêra
Ängelholm
Asselfingen
Aßlar
Astert
Außernzell
Auufer
...
В режиме xgerman слово ABlar
переходит с третьего места в списке на четвертое.
Многоязычная сортировка
Как нетрудно догадаться, одноязычная сортировка обладает серьезным недостатком: она работает только с одним языком, заданным параметром NLS_SORT
. Oracle
также предоставляет многоязычные средства сортировки, позволяющие работать с несколькими локальными контекстами.
Многоязычная сортировка, базирующаяся на стандарте ISO
14651, поддерживает более 1,1 миллиона символов. Oracle
поддерживает не только символы, определяемые в стандарте Юникода 4.0, но и некоторые дополнительные символы.
В отличие от двухэтапной одноязычной сортировки, многоязычная сортировка определяет порядок символов в три этапа:
- На первом уровне отделяются базовые символы.
- На втором уровне базовые символы отделяются от диакритических элементов, модифицирующих базовые символы.
- На третьем уровне происходит разделение символов по регистру.
Функция NLSSORT
и параметр NLS_SORT
используются и при многоязычной сортировке, но при этом используются другие значения. Режим GENERIC_M
хорошо работает в большинстве западных языков. В табл. 2 перечислены значения параметра NLS_SORT
, доступные для многоязычной сортировки.
Таблица 2. Значения параметра NLS_SORT
для многоязычной сортировки
generic_m | |||
canadian_m | japanese_m | schinese_pinyin_m | tchinese_radical_m |
danish_m | korean_m | schinese_radical_m | tchinese_stroke_m |
french_m | schinese_stroke_m | spanish_m | thai_m |
Чтобы продемонстрировать режим многоязычной сортировки, мы изменим вызов функции так, чтобы в нем использовалось значение generic_m
:
VARIABLE v_city_order REFCURSOR
CALL city_order_by_func('generic_m') INTO :v_city_order;
PRINT v_city_order
Упорядоченный список городов выглядит так:
CITY
------------------------------------
Abdêra
Ängelholm
Asselfingen
Aßlar
Astert
..
Zudar
Zühlen
尼崎市
旭川市
足立区
青森市