Самоучитель. Курс SQL. Базы данных. ORACLE

Илья Леонидович Хохлов, 1985

Систематичный и последовательный курс изучения языка SQL с первых шагов до уровня специалиста с трёхлетним стажем. Весь материал сопровождается практическими примерами и самостоятельными задачами, с вариантами их решений для самоконтроля. Книга написана простым и понятным языком.

7. Преобразование данных

7.1 Функция to_date

Итак, мы поняли, что при написании дат в SQL–запросах в виде текста (то есть дат в кавычках, так как все, что в кавычках — это текст), перед выполнением SQL–запросов, даты из текста будут автоматически распознаны. Текст будет неявно преобразован в дату. Неявно, означает, что без явной нашей команды на преобразование. ORACLE, на основании своего понимания как должна выглядеть дата, делает ее извлечение из текста.

Например, в SQL запросе указана дата вида: '01.03.1980'. На одном компьютере, ORACLE может «понять», что первые две цифры — это номер дня, затем, после точки, идет номер месяца и в конце указан четырехсимвольный номер года. На другом компьютере СУБД распознает дату с предшествующим месяцем, затем с днем. То есть, наоборот.

В первом случае, будут отобраны данные за первое марта, во втором случае — за третье января. Такое использование дат в SQL запросах крайне опасно. Особенно в описанном выше случае — ORACLE «смог» из текста распознать дату, но мы не узнали, что он распознал ее неправильно. СУБД не выдала ошибку. На основе полученных данных мог строиться анализ и приниматься важное решение.

Если бы в запрос передавалась дата '21.01.1980', а Оракл ожидал бы в начале месяц, а потом день, то при выполнении запроса, мы бы получили ошибку о некорректном номере месяца. Получение явной ошибки от Оракла — это лучше, чем ее скрытое наличие. Теперь самое время научиться вписывать даты в запрос правильно.

Для того, чтобы правильно вписать дату в SQL–запрос, необходимо ее явно преобразовать из текста в дату с указанием того, как она должна быть преобразована. Что из написанного день, а что — месяц. Для этого воспользуемся функцией Oracle — to_date.

В функции to_date вначале нужно указать какой текст должен быть преобразован в дату, а затем нужно указать маску (формат) преобразования.

Пример использования функции to_date:

Приведенный выше запрос выбирает из таблицы Persons сотрудников, родившихся в 80–е.

Напишем запрос, выводящий все заказы в платной столовой (расположенной на пятом этаже в нашей компании), сделанные 10 марта 2019 года:

Получаем результат (на скриншоте ниже показаны не все отобранные данные — в целях экономии места):

В колонке PersonID идентификатор сотрудника, сделавшего заказ. В колонке DateOrder дата заказа, и в колонке DishID — идентификатор заказанного блюда.

7.2. Функция to_char

Еще одна функция преобразования Oracle. В отличии от рассмотренной предыдущей функции, функция to_char делает обратное — преобразовывает в текст. Имеет два главный входных параметра: первый параметр — число или дата, которую нужно преобразовать в текст; и второй параметр — формат преобразования (как именно нужно преобразовать в текст). Есть еще и третий необязательный параметр, который мы пока опустим — это языковые параметры.

Сначала рассмотрим преобразование даты в текст. Проиллюстрируем преобразование даты в текст на примере дат рождения сотрудников. Напишем запрос, выводящий дату рождения, например, в формате месяц прописью и год цифрой. На такое способна только функция to_char!

Получим результат:

После ключевого слова SELECT мы указали вывод данных столбца NAME, BIRTHDATE и преобразованного столбца BIRTHDATE с помощью функции to_char. Функция привела выводимые даты к запрашиваемому во втором параметре формату — месяц прописью и год числом. Если в качестве формата преобразование указать буквенное обозначение месяца, написанное в нижнем регистре, то функция to_char выведет результат с именем месяца также в таком же регистре:

Получим:

Если укажем в верхнем регистре, то и получим тоже в верхнем.

Значение месяца мы также можем вывести числом, если укажем это в формате:

Получим:

И теперь придумаем совсем хитрый формат:

Выведем день, через точку, месяц и потом через слэш номер года, состоящий из двух символов:

Как видим, мы можем выводить дату практически в любом удобном (нужном) для нас виде. При составлении формата, можем использовать дополнительные символы: пробел, тире, слеш (косая черта), скобки и т.д. Можем выводить, также, и отдельную информацию о дате, например, день недели:

Результат:

В таблице ниже список основных элементов даты (и времени), которые можно использовать при составлении формата для функции to_char.

Теперь нам ни одна задача не страшна! Но это еще не все! Функция to_char может использоваться для преобразования числа в текст, а не только даты. После преобразования числа в текст, для него уже не будут доступны арифметические операции, но зато смотреться будет красиво.

Преобразование числа в текст делается, в основном, для его форматирования, красивого отображения, например, с пробелами–разделителями групп разрядов. Или можно всегда отображать значение с определенным количеством чисел после запятой. Для целого числа, например, после запятой будут отображаться нули. По мере развития отчетных систем, выводящих данные на печать или в отчеты, надобность форматирования чисел самой СУБД при выдаче данных, потеряла актуальность. Сами отчетные системы умеют отображать числа с любым форматированием и для них главное — само число, то есть сами данные. Тем не менее, для полноты темы, ниже мы рассмотрим использование функции to_char для преобразования числа в текст.

Выведем названия блюд столовой, их цены, а также преобразованные значения цен:

Получим:

Самое первое на что нужно обратить внимание в маске, это на разделитель целой и дробной части — D (англ. delimiter — разделитель). Далее слева и справа от разделителя мы видим цифры «0» и «9». «0» обозначает обязательное число. То есть в ценах блюд столовой до запятой должно быть минимум одно число. Даже если это будет 0. Например, цена 0 рублей 50 копеек. «9» обозначает необязательное число. В нашей маске после разделителя указаны два необязательных числа. При стоимости товара в ровно 20 рублей, после запятой нет чисел. Они бы были если товар стоил 20 рублей 55 копеек, но мы, исходя из того, какие цены у нас есть в столовой, заложили в формат, что после разделителя целой и дробной части, дальше чисел может не быть (в случае целых чисел). И поставили «9» и «9», показывая Ораклу, что нам нужны здесь два знака, но их в значении цены может не быть. Благодаря этому Оракл вывел цену в третьем столбце со знаками после запятой, даже, в случае целых чисел.

Рассмотрим еще один пример. Выведем суммы заработных плат, перечисляемых сотрудникам:

Получаем:

Спереди разделителя указано только одно обязательное число. Остальные все числа не обязательные. Через 3 цифры вставлен разделитель групп разрядов — буква «G». Это визуальный пробел для облегчения понимания размера числа.

7.3. Функция to_number

Функция to_number эквивалент функции to_char при преобразовании числа в текст, но делает обратное — преобразовывает текст в число. Пример:

Получаем: