Функции конвертирования

В СУБД Oracle используются три простых типа данных:

– строки CHAR, VARCHAR2;

– числа NUMBER;

– даты DATE.

Сервер Oracle может конвертировать данные, имеющие тип VARCHAR2 и CHAR, в данные типов NUMBER и DATE. Он может преобразовать данные, имеющие тип NUMBER или DATE, в данные типов CHAR и VARCHAR2.

Преобразование может осуществляться явным и неявным образом. Неявное преобразование осуществляется при выполнении следующего оператора:

{столбец} тип А = {начение/выражение} тип Б

При выполнении этого оператора значение или выражение в правой части преобразуется к типу, который имеет левая часть.

– WHERE order_date= ′2-04-2017′;

– WHERE order_date= ′26-apr-2017′;

– WHERE order_date= ′26-апр-2017′;

В этих примерах в зависимости от языковых настроек во втором или третьем операторе возникнет ошибка. Если используемый язык – английский, то ошибка возникнет в третьем операторе, Если используемый язык – русский, то ошибка возникнет во втором операторе. Следует иметь в виду, что значение ′26-APR-2017′ имеет тип строки символов.

– WHERE salary = ′4200′;

– WHERE salary = ′4000′+200;

– WHERE salary = ′4.200′;

– WHERE salary = ′$4200′;

В этих примерах первый и второй операторы будут успешно выполнены, при выполнении третьего и четвертого операторов возникнут ошибки, так как эти строки содержат недопустимые символы.

Для того чтобы неявное преобразование было возможно, необходимо, чтобы присваиваемое значение соответствовало формату столбца, которому это значение присваивается.

Хотя неявное преобразование возможно, лучше для этого использовать специальные функции. Чаще всего функции преобразования типов используются для того, чтобы числовые данные и даты отобразить в наиболее удобном (понятном) виде.

Четыре типа преобразования:

– число в строку символов;

– строку символов в число;

– дату в строку символов;

– строку символов в дату.

Преобразование чисел в строку символов

Числа, хранящиеся в базе данных, не имеют форматирования. Это означает, что они не имеют символов валюты, запятых, десятичных знаков и других параметров форматирования. Чтобы добавить форматирование, необходимо преобразовать число в строку символов. Для этого используется функция:

TO_CHAR (Х {маска преобразования})

Для преобразования численного значения в строку можно использовать элементы формата, представленные в таблице 3.6.

Таблица 3.6. Элементы маски преобразования, используемые в функции TO_CHAR

Пример 3.35. Использование функции TO_CHAR

SELECT TO_CHAR (1475.29, ′9999.9′) As ′′9999.9′′,

TO_CHAR (1475.29, ′9999.99′) As ′′9999.99′′,

TO_CHAR (1475.29, ′099999.90′) As ′′099999.99′′,

TO_CHAR (1475.29, ′9,999.99′) As ′′9,999.99′′,

TO_CHAR (1475.29, ′$9,999.99′) As ′′$9,999.99′′

FROM DUAL;

Преобразование строки символов в число

Для преобразования символьного значения в число используется функция TO_NUMBER. Синтаксис:

TO_NUMBER (х, {маска преобразования})

Строка x может содержать цифры и символы, которые соответствуют заданному формату. Параметр {маска преобразования} определяет, как нужно интерпретировать символьное представление числа, может содержать те же элементы, которые были определены для функции TO_CHAR.

Если число символов в строке будет больше числа элементов формата, то возникает ошибка. Примеры преобразований, при которых возникает ошибка:

TO_NUMBER (′1475.29′,′999.99′)

TO_NUMBER (′1475.29′, ′9999.9′)

Если число символов в строке будет меньше числа элементов формата, то возникает ошибка.

Пример 3.36. Использование функции TO_NUMBER

SELECT TO_NUMBER (′1475.29′, ′99999.99′),

TO_NUMBER (′1475.29′, ′9999.999′)

FROM DUAL;

Преобразование строки символов в дату

Для преобразования строки символов в значение, имеющее формат даты, используется функция:

TO_DATE (х, {маска преобразования})

Строка x содержит символьное значение даты. Параметр {маска преобразования} определяет, как нужно интерпретировать символьное представление даты.

Маска может содержать элементы формата, представленные в таблице 3.7.

Таблица 3.7. Элементы маски преобразования, используемые в функции TO_DATE

Пример 3.37. Использование функции TO_DATE

SELECT TO_DATE (′01-SEP-2018′, ′DD-MON-YYYY′)

As ′′01-SEP-2018′′,

TO_DATE (′09/01/18′, ′MM/DD/RR′) As ′′ 09/01/18′′,

TO_DATE (′01092018′, ′DDMMYYYY′) As ′′ 01092018′′

FROM DUAL

Замечание: срока преобразуется в дату, а дата выводится в установленном формате даты. Для ввода и вывода значения времени используется маска HH24:MI: SS, где:

– HH24 – двузначное значение часа в 24-часовом формате;

– MI – двузначное значение минут;

– SS – двузначное значение секунд.

Замечание: введенное значение времени сохраняется, но по умолчанию не отображается. Для отображения времени в значениях, имеющих тип Date, необходимо использовать функцию TO_CHAR.

Пример 3.38. Ввод и вывод значения даты, содержащей время

SELECT TO_CHAR (TO_DATE (′01-SEP-2018, 14:45:51′,

′DD-MON-YYYY HH24:MI: SS′),′DD MONTH YYYY, HH24:MI: SS′)

As Date_Time

FROM DUAL

Использование формата RR

Этот формат связан с проблемой 2000 года. Определяет год, если в дате заданы две последние цифры года. Если две последние цифры лежат в диапазоне от 0 до 49, то год принадлежит текущему столетию. Если две последние цифры лежат в диапазоне от 50 до 99, то год принадлежит предыдущему столетию.

TO_DATE (′04-JUL-18′, ′DD-MON-RR′) → 04/JUL/2018

TO_DATE (′04-JUL-75′, ′DD-MON-RR′) → 04/JUL/1975

Более полная информация о правилах использования формата RR приведена в таблице 3.8.

Таблица. 3.8. Правила преобразования года в формате RR

При использовании формата YY первые две цифры всегда соответствуют текущему столетию. Совет: при работе с датами всегда указывайте четыре цифры года.

Пример 3.39. Использование формата RR при вводе двузначного значения года

SELECT TO_CHAR (TO_DATE

(′04-07-18′, ′DD-MM-RR′),′DD-MON-YYYY′) As DAT1,

TO_CHAR (TO_DATE (′04-07-75′, ′DD-MM-RR′),′DD-MON-YYYY′)

As DAT2

FROM DUAL;

Пример 3.40. Использование формата YY при вводе двузначного значения года

SELECT TO_CHAR (TO_DATE (′04-07-18′, ′DD-MM-YY′),

′DD-MON-YYYY′) As DAT1,

TO_CHAR (TO_DATE (′04-07-75′, ′DD-MM-YY′),

′DD-MON-YYYY′) As DAT2

FROM DUAL;

Преобразование даты в строку символов

Это преобразование выполняется для того, чтобы отобразить значение, имеющее тип Date в требуемом виде. Для осуществления этого преобразования используется функция:

TO_CHAR (х, {маска преобразования})

где: x – значение, имеющее тип Date, а строка {маска преобразования}) – маска, которая определяет, как нужно отобразить значение x; может содержать те же элементы, которые были определены для функции TO_DATE.

Пример 3.41. Использование функции TO_CHAR для преобразования значения, имеющего тип Date, в строку символов

SELECT TO_CHAR (SYSDATE, ′ DD/MM/YYYY′) AS RESULT1,

TO_CHAR (SYSDATE, ′ DD MON, YYYY′) AS RESULT2,

TO_CHAR (SYSDATE, ′ DD DAY MONTH, YYYY′) AS RESULT3,

TO_CHAR (SYSDATE, ′ DD – MONTH -YYYY, HH24:MI: SS′)

AS RESULT4

FROM DUAL;

Используя функцию TO_CHAR при работе с данными, имеющими тип Date, можно выделить определенную часть даты: день, месяц, год.

Пример 3.42. Вывести данные о сотрудниках, которые были приняты на работу в 2000 году

SELECT employee_id, first_name, last_name, hire_date, salary

FROM Employees

WHERE TO_CHAR (hire_date, ′YYYY′) = ′2000′;

Работа с неопределенными значениями

Если при вводе новой строки в таблицу столбцу не будет присвоено значение, то этот столбец будет иметь значение NULL – не определено. Это может происходить по двум основным причинам. Первая причина: в момент ввода строки значение столбца неизвестно, в этом случае значение будет присвоено позже. Вторая причина: значение не может быть присвоено исходя из правил предметной области. Для рассматриваемой базы данных вторую причину можно пояснить на примере столбца commission_pct таблицы Employees. Некоторым сотрудникам полагаются комиссионные, столбец commission_pct содержит значение комиссионных. Зарплата таких сотрудников рассчитывается по формуле: Salary * (1 + commission_pct). У сотрудников, которым комиссионные не полагаются, значение столбца commission_pct не может быть определено.

При работе с арифметическими и логическими выражениями следует иметь в виду следующее: арифметическое выражение вернет значение NULL, если один или несколько операндов будут иметь значение NULL; результатом операции сравнения будет NULL, если один или оба операнда будут иметь значение NULL.

Результат логических операций AND и OR приведен в таблицах 3.9 и 3.10 соответственно.

Таблица 3.9. Таблица истинности логической функции AND с учетом значений NULL

Таблица 3.10. Таблица истинности логической функции OR с учетом значений NULL

Для корректной обработки данных, которые могут иметь значения NULL, следует использовать специальные функции.

Функция NVL

Позволяет заменить значение NULL фактическим значением. Синтаксис:

NVL (x,y)

Возвращает x, если x не NUUL, и возвращает y, если x имеет значение NUUL, например: NVL (commission_pct,0).

Рассмотрим примеры использования функции NVL при решении конкретных задач.

Пример 3.43. Вывести данные о сотрудниках, включая размер комиссионных, которые работают в отделах 30 и 80

SELECT employee_id, first_name, last_name, department_id,

salary, NVL (commission_pct,0)

FROM Employees

WHERE department_id IN (30,80)

ORDER BY department_id;

Пример 3.44. Вывести данные о сотрудниках, включая зарплату с учетом комиссионных (полная зарплата), которые работают в отделах 30 и 80, упорядочив их в порядке убывания значений зарплаты с учетом комиссионных

SELECT employee_id, first_name, last_name, department_id,

salary* (1+NVL (commission_pct,0)) AS total_salary

FROM Employees

WHERE department_id IN (30,80)

ORDER BY total_salary DESC;

Псевдонимы столбцов можно использовать в предложении ORDER BY, но нельзя использовать в предложении WHERE.

Пример 3.45. Вывести данные о сотрудниках, включая зарплату с учетом комиссионных, полная зарплата которых больше 15 000, упорядочив их в порядке убывания значений полной зарплаты

SELECT employee_id, first_name, last_name, department_id,

salary* (1+NVL (commission_pct,0)) AS total_salary

FROM Employees

WHERE total_salary> 15000

ORDER BY total_salary DESC;