Помимо функций утилит SQL, встроенная библиотека функций Oracle содержит функции преобразования типов. Возможны сценарии, в которых запрос ожидает ввод данных определенного типа, но получает его в другом типе данных. В таких случаях Oracle неявно пытается преобразовать неожиданное значение в совместимый тип данных, который может быть заменен на месте, и непрерывность приложения не нарушается. Преобразование типов может быть неявно выполнено Oracle или явно программистом.
Неявное преобразование типов данных работает на основе матрицы, которая демонстрирует поддержку Oracle для внутреннего приведения типов. Помимо этих правил, Oracle предлагает функции преобразования типов, которые можно использовать в запросах для явного преобразования и форматирования. На самом деле, рекомендуется выполнять явное преобразование, а не полагаться на программный интеллект. Несмотря на то, что неявное преобразование работает хорошо, но для устранения перекосных шансов, где плохие входные данные может быть сложно ввести внутренне.
Неявное преобразование типов данных
Значение VARCHAR2 или CHAR может быть неявно преобразовано в значение типа NUMBER или DATE с помощью Oracle. Точно так же значение типа NUMBER или DATA может быть автоматически преобразовано в символьные данные сервером Oracle. Обратите внимание, что недопустимое взаимопревращение происходит только тогда, когда символ представляет действительное число или значение типа даты соответственно.
Например, рассмотрите следующие запросы SELECT. Оба запроса дадут один и тот же результат, потому что Oracle внутренне обрабатывает 15000 и «15000» как одинаковые.
Query-1
SELECT employee_id,first_name,salary FROM employees WHERE salary > 15000;
Запрос-2
SELECT employee_id,first_name,salary FROM employees WHERE salary > '15000';
Явное преобразование типов данных
Функции преобразования SQL — это функции с одной строкой, которые могут указывать значения столбцов, литералы или выражения. TO_CHAR, TO_NUMBER и TO_DATE — это три функции, которые выполняют перекрестную модификацию типов данных.
Функция TO_CHAR
Функция TO_CHAR используется для ввода числового значения или даты в тип символа с помощью модели формата (необязательно).
Синтаксис
TO_CHAR(number1, [format], [nls_parameter])
Для преобразования цифр в символы параметры nls могут использоваться для указания десятичных символов, разделителя групп, модели национальной валюты или модели международной валюты. Это необязательная спецификация — если она недоступна, будут использоваться настройки nls уровня сеанса. Для преобразования даты в символ параметр nls можно использовать для указания названий дней и месяцев, если это применимо.
Даты могут быть отформатированы в нескольких форматах после преобразования в типы символов с помощью функции TO_CHAR. Функция TO_CHAR используется для отображения дат Oracle 11g в определенном формате. Форматные модели чувствительны к регистру и должны быть заключены в одинарные кавычки.
Рассмотрим приведенный ниже запрос SELECT. Формат запроса в столбцах HIRE_DATE и SALARY таблицы EMPLOYEES с использованием функции TO_CHAR.
SELECT first_name, TO_CHAR (hire_date, 'MONTH DD, YYYY') HIRE_DATE, TO_CHAR (salary, '$99999.99') Salary FROM employees WHERE rownum < 5; FIRST_NAME HIRE_DATE SALARY -------------------- ------------------ ---------- Steven JUNE 17, 2003 $24000.00 Neena SEPTEMBER 21, 2005 $17000.00 Lex JANUARY 13, 2001 $17000.00 Alexander JANUARY 03, 2006 $9000.00
Первый TO_CHAR используется для преобразования даты приема в формат даты МЕСЯЦ ДД, ГГГГ, т. Е. Месяц, прописанный с пробелами, за которым следует двузначный день месяца, а затем четырехзначный год. Если вы предпочитаете отображать название месяца в смешанном регистре (то есть «декабрь»), просто используйте этот регистр в аргументе формата: («Месяц ДД, ГГГГ»).
Вторая функция TO_CHAR на рисунке 10-39 используется для форматирования SALARY для отображения знака валюты и двух десятичных позиций.
Oracle предлагает полный набор форматных моделей. В таблице ниже приведен список моделей формата, которые можно использовать для ввода даты и числовых значений в виде символов с использованием TO_CHAR.
| Модель формата | Описание |
|---|---|
| , (Запятая) | Возвращает запятую в указанной позиции. Вы можете указать несколько запятых в модели числового формата. Ограничения: Запятая не может начинать модель числового формата. Запятая не может появляться справа от десятичного знака или точки в модели числового формата. |
| . (Период) | Возвращает десятичную точку, которая представляет собой точку (.) В указанной позиции. Ограничение: в модели числового формата можно указать только один период |
| $ | Возвращает значение с ведущим знаком доллара |
| 0 | Возвращает ведущие нули. Возвращает завершающие нули. |
| 9 | Возвращает значение с указанным количеством цифр с начальным пробелом, если положительное, или с начальным минусом, если отрицательное. Начальные нули являются пустыми, за исключением нулевого значения, которое возвращает ноль для целой части числа с фиксированной точкой. |
| В | Возвращает пробелы для целой части числа с фиксированной запятой, когда целая часть равна нулю (независимо от «0» в модели формата). |
| С | Возвращает в указанной позиции символ валюты ISO (текущее значение параметра NLS_ISO_CURRENCY). |
| D | Возвращает в указанной позиции десятичный символ, который является текущим значением параметра NLS_NUMERIC_CHARACTER. По умолчанию используется точка (.). Ограничение: Вы можете указать только один десятичный символ в модели числового формата. |
| EEE | Возвращает значение, используемое в научной нотации. |
| FM | Возвращает значение без начальных или конечных пробелов. |
| г | Возвращает в указанную позицию разделитель группы (текущее значение параметра NLS_NUMERIC_CHARACTER). Вы можете указать несколько разделителей групп в модели числового формата. Ограничение: разделитель группы не может отображаться справа от десятичного знака или точки в модели числового формата |
| L | Возвращает в указанной позиции символ местной валюты (текущее значение параметра NLS_CURRENCY). |
| Мичиган | Возвращает отрицательное значение со знаком минус (-). Возвращает положительное значение с завершающим пробелом. Ограничение: Элемент формата MI может появляться только в последней позиции модели числового формата. |
| PR | Возвращает отрицательное значение в. Это может появиться только в конце модели числового формата. |
| RN, гт | Возвращает значение в виде римских цифр в верхнем регистре. Возвращает значение в виде римских цифр в нижнем регистре. Значение может быть целым числом от 1 до 3999. |
| S | Возвращает отрицательное значение с начальным или конечным знаком минус (-). Возвращает положительное значение с начальным или конечным знаком плюс (+). Ограничение: элемент формата S может появляться только в первой или последней позиции модели числового формата. |
| TM | «Текстовый минимум». Возвращает (в десятичном формате) наименьшее возможное количество символов. Этот элемент не чувствителен к регистру. |
| U | Возвращает в указанной позиции символ евро (или другой) с двумя валютами (текущее значение параметра NLS_DUAL_CURRENCY). |
| В | Возвращает значение, умноженное на 10n (и, если необходимо, округлить его), где n — это число 9 после «V». |
| Икс | Возвращает шестнадцатеричное значение указанного количества цифр. |
Функция TO_NUMBER
Функция TO_NUMBER преобразует символьное значение в числовой тип данных. Если преобразуемая строка содержит нечисловые символы, функция возвращает ошибку.
Синтаксис
TO_NUMBER (string1, [format], [nls_parameter])
В приведенной ниже таблице показан список моделей форматов, которые можно использовать для ввода значений символов в виде числа с использованием TO_NUMBER.
| Модель формата | Описание |
|---|---|
| CC | век |
| SCC | До н.э. с префиксом — |
| YYYY | Год с 4 номерами |
| SYYY | Год до нашей эры с префиксом — |
| IYYY | ISO год с 4 номерами |
| YY | Год с 2 числами |
| RR | Год с 2 номерами с совместимостью Y2k |
| ГОД | Год в символах |
| SYEAR | Год в символах, до н.э. с префиксом — |
| До нашей эры | Индикатор BC / AD |
| Q | Квартал в цифрах (1,2,3,4) |
| М.М. | Месяц года 01, 02 … 12 |
| МЕСЯЦ | Месяц в символах (т.е. январь) |
| пН | Янв, Фев |
| WW | Номер недели (т.е. 1) |
| W | Номер недели месяца (т.е. 5) |
| IW | Номер недели в году в стандарте ISO. |
| DDD | День года в цифрах (т.е. 365) |
| DD | День месяца в цифрах (т.е. 28) |
| D | День недели в цифрах (т.е. 7) |
| ДЕНЬ | День недели в символах (т.е. понедельник) |
| FMDAY | День недели в символах (т.е. понедельник) |
| DY | День недели в кратком описании персонажа (т.е. СОЛНЦЕ) |
| J | Юлианский день (количество дней с 1 января 4713 г. до н.э., где 1 января 4713 г. до н.э. 1 в Oracle) |
| HH, H12 | Часовой номер дня (1-12) |
| HH24 | Часовой номер дня с пометкой 24 часа (0-23) |
| ДО ПОЛУДНЯ ПОСЛЕ ПОЛУДНЯ | До или после полудня |
| МИСС | Количество минут и секунд (то есть 59), |
| SSSSS | Количество секунд в этом дне. |
| DS | Краткий формат даты. Зависит от NLS-настроек. Используйте только с меткой времени. |
| DL | Длинный формат даты. Зависит от NLS-настроек. Используйте только с меткой времени. |
| Е | Сокращенное название эпохи. Действительно только для календарей: Японский Империал, Официальный РПЦ, Тайский Будда. |
| EE | Полное название эпохи |
| FF | Дробные секунды. Используйте с отметкой времени. |
| FF1..FF9 | Дробные секунды. Используйте с отметкой времени. Цифра управляет количеством десятичных цифр, используемых в течение доли секунды. |
| FM | Режим заполнения: подавляет пропуски в выводе из преобразования |
| FX | Точный формат: требуется точное сопоставление образца между данными и моделью формата. |
| IYY ИЛИ IY ИЛИ Я | Последние 3,2,1 цифры стандартного года ISO. Только вывод |
| RM | Римская цифра представления месяца (I .. XII) |
| RR | Последние 2 цифры года. |
| RRRR | Последние 2 цифры года при использовании для вывода. Принимает четырехзначные годы при использовании для ввода. |
| SP | Записанный формат. Может появиться конец числового элемента. Результат всегда на английском. Например месяц 10 в формате MMSP возвращает «десять» |
| SPTH | Записанный и порядковый формат; 1 результаты в первую очередь. |
| TH | Преобразует число в его порядковый формат. Например, 1 становится 1-м. |
| TS | Короткий формат времени. Зависит от NLS-настроек. Используйте только с меткой времени. |
| TZD | Сокращенное название часового пояса. то есть PST. |
| TZH, ПЗМ | Часовой пояс смещение часов / минут. |
| TZR | Часовой пояс региона |
| Икс | Локальный корень персонажа. В Америке это период (.) |
Приведенные ниже запросы SELECT принимают числа в качестве ввода символов и распечатывают их в соответствии со спецификатором формата.
SELECT TO_NUMBER('121.23', '9G999D99') FROM DUAL TO_NUMBER('121.23','9G999D99') ------------------------------ 121.23 SELECT TO_NUMBER('1210.73', '9999.99') FROM DUAL; TO_NUMBER('1210.73','9999.99') ------------------------------ 1210.73
Функция TO_DATE
Функция принимает символьные значения в качестве входных данных и возвращает форматированную дату, эквивалентную той же самой. Функция TO_DATE позволяет пользователям вводить дату в любом формате, а затем преобразует запись в формат по умолчанию, используемый Oracle 11g.
Синтаксис:
TO_DATE( string1, [ format_mask ], [ nls_language ] )
Аргумент format_mask состоит из серии элементов, представляющих, как именно должны выглядеть данные, и должны вводиться в одинарных кавычках.
| Модель формата | Описание |
|---|---|
| ГОД | Год прописан |
| YYYY | 4-значный год |
| YYY, YY, Y | Последние 3, 2 или 1 цифра (и) года. |
| IYY, IY, я | Последние 3, 2 или 1 цифра (ы) года ISO. |
| IYYY | 4-значный год в соответствии со стандартом ISO |
| RRRR | Принимает год с 2 цифрами и возвращает год с 4 цифрами. |
| Q | Четверть года (1, 2, 3, 4; JAN-MAR = 1). |
| М.М. | Месяц (01-12; ЯНВ = 01). |
| пН | Сокращенное название месяца. |
| МЕСЯЦ | Название месяца, дополненное пробелами, длиной до 9 символов. |
| RM | Римская цифра месяц (I-XII; JAN = I). |
| WW | Неделя года (1-53), где неделя 1 начинается в первый день года и продолжается до седьмого дня года. |
| W | Неделя месяца (1-5), где неделя 1 начинается в первый день месяца и заканчивается седьмым. |
| IW | Неделя года (1-52 или 1-53) в соответствии со стандартом ISO. |
| D | День недели (1-7). |
| ДЕНЬ | Название дня |
| DD | День месяца (1-31). |
| DDD | День года (1-366). |
| DY | Сокращенное название дня. |
| J | Юлианский день; количество дней с 1 января 4712 г. до н. |
| HH12 | Час дня (1-12). |
| HH24 | Час дня (0-23). |
| МИСС | Минута (0-59). |
| SSSSS | Секунды после полуночи (0-86399). |
| FF | Дробные секунды. Используйте значение от 1 до 9 после FF, чтобы указать количество цифр в долях секунды. Например, «FF4». |
| ДО ПОЛУДНЯ ПОСЛЕ ПОЛУДНЯ | Индикатор меридиана |
| AD, BC | Индикатор AD, BC |
| TZD | Информация о летнем времени. Например, «PST» |
| TZH, ПЗМ, TZR | Часовой пояс час / минута / регион. |
В следующем примере строка символов преобразуется в дату:
SELECT TO_DATE('January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American') FROM DUAL; TO_DATE(' --------- 15-JAN-89
Общие функции
Общие функции используются для обработки значений NULL в базе данных. Целью общих функций обработки NULL является замена значений NULL альтернативными значениями. Мы кратко рассмотрим эти функции ниже.
NVL
Функция NVL заменяет альтернативное значение значением NULL.
Синтаксис:
NVL( Arg1, replace_with )
В синтаксисе оба параметра являются обязательными. Обратите внимание, что функция NVL работает со всеми типами данных. А также, что тип данных исходной строки и замены должен быть в совместимом состоянии, то есть либо одинаковым, либо неявно конвертируемым в Oracle.
Если arg1 является символьным значением, то oracle преобразует замещающую строку в тип данных, совместимый с arg1, перед их сравнением и возвращает VARCHAR2 в наборе символов expr1. Если arg1 является числовым, то Oracle определяет аргумент с наибольшим числовым приоритетом, неявно преобразует другой аргумент в этот тип данных и возвращает этот тип данных.
Оператор SELECT ниже будет отображать ‘n / a’, если сотрудник еще не назначен на какую-либо работу, т.е. JOB_ID равен NULL. В противном случае будет отображаться фактическое значение JOB_ID.
SELECT first_name, NVL(JOB_ID, 'n/a') FROM employees;
NVL2
В качестве усовершенствования по сравнению с NVL, Oracle представила функцию для замены значения не только для значений столбцов NULL, но и для столбцов NOT NULL. Функция NVL2 может использоваться для замены альтернативного значения на NULL, а не на значение NULL.
Синтаксис:
NVL2( string1, value_if_NOT_null, value_if_null )
Оператор SELECT ниже будет отображать «Bench», если JOB_CODE для сотрудника равен NULL. Для определенного ненулевого значения JOB CODE будет отображаться постоянное значение Job Assigned.
SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench') FROM employees;
NULLIF
Функция NULLIF сравнивает два аргумента expr1 и expr2. Если expr1 и expr2 равны, он возвращает NULL; иначе он возвращает expr1. В отличие от другой нулевой функции обработки, первый аргумент не может быть NULL.
Синтаксис:
NULLIF (expr1, expr2)
Обратите внимание, что первый аргумент может быть выражением, которое оценивается как NULL, но не может быть литералом NULL. Оба параметра являются обязательными для выполнения функции.
Приведенный ниже запрос возвращает NULL, поскольку оба входных значения 12 равны.
SELECT NULLIF (12, 12) FROM DUAL;
Аналогичным образом, приведенный ниже запрос возвращает ‘SUN’, поскольку обе строки не равны.
SELECT NULLIF ('SUN', 'MOON') FROM DUAL;
COALESCE
Функция COALESCE, более общая форма NVL, возвращает первое ненулевое выражение в списке аргументов. Требуется минимум два обязательных параметра, но максимальные аргументы не имеют ограничений
Синтаксис:
COALESCE (expr1, expr2, ... expr_n )
Рассмотрим приведенный ниже запрос SELECT. Он выбирает первое ненулевое значение, введенное в поля адреса для сотрудника.
SELECT COALESCE (address1, address2, address3) Address FROM employees;
Интересно, что работа функции COALESCE аналогична конструкции IF..ELSIF..ENDIF. Запрос выше может быть переписан как —
IF address1 is not null THEN result := address1; ELSIF address2 is not null THEN result := address2; ELSIF address3 is not null THEN result := address3; ELSE result := null; END IF;
Условные функции
Oracle предоставляет условные функции DECODE и CASE для наложения условий даже в операторе SQL.
Функция ДЕКОД
Функция является SQL-эквивалентностью условного процедурного оператора IF..THEN..ELSE. DECODE работает со значениями / столбцами / выражениями всех типов данных.
Синтаксис:
DECODE (expression, search, result [, search, result]... [, default])
Функция DECODE сравнивает выражение с каждым поисковым значением по порядку. Если между выражением и аргументом поиска существует равенство, он возвращает соответствующий результат. В случае отсутствия совпадения возвращается значение по умолчанию, если оно определено, иначе NULL. В случае любого несовпадения совместимости типов, oracle внутренне делает возможное неявное преобразование для возврата результатов.
По сути, Oracle считает, что при работе с функцией DECODE два нуля эквивалентны.
SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') FROM DUAL; DECOD ----- EQUAL
Если выражение равно нулю, Oracle возвращает результат первого поиска, который также равен нулю. Максимальное количество компонентов в функции DECODE составляет 255.
SELECT first_name, salary, DECODE (hire_date, sysdate,'NEW JOINEE','EMPLOYEE') FROM employees;
CASE выражение
Выражения CASE работают по той же концепции, что и DECODE, но отличаются синтаксисом и использованием.
Синтаксис:
CASE [ expression ] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE result END
Поиск Oracle начинается слева и двигается вправо, пока не найдет истинное условие, а затем возвращает выражение результата, связанное с ним. Если не найдено ни одного условия истинного, а предложение ELSE существует, Oracle возвращает результат, определенный с помощью else. В противном случае Oracle возвращает ноль.
Максимальное количество аргументов в выражении CASE — 255. Все выражения учитываются в этом пределе, включая начальное выражение простого выражения CASE и необязательное выражение ELSE. Каждая пара WHEN … THEN считается двумя аргументами. Чтобы избежать превышения этого предела, вы можете вкладывать выражения CASE так, чтобы само return_expr было выражением CASE.