Преобразования NVL для различных типов данных. Функции TO_NUMBER и TO_DATE
При составлении sql-запросов нередко возникает ситуация, когда при выборке данных надо сравнить значения нескольких столбцов и вывести то из них, которое содержит данные (не пустое). Эту задачу отлично решает выражение Coalesce. В данной статье вы найдете полную характеристику выражения Coalesce sql, описание особенностей использования, а также примеры.
Вывод непустых значений с помощью Coalesce. Особенности
Рассмотрим Sql Coalesce особенности использования:
- Допускает указание любого количества аргументов (в отличие от Isnull/Nvl/Nvl2, число аргументов в которых ограничено).
- Может принимать в качестве аргумента подзапросы.
- Возвращает результат, равный первому отличному от Null значению, либо Null, если отличного от Null значения не будет найдено.
- Sql Coalesce может использоваться в предложении Select для выбора непустого значения, а также в Where для уточнения, что набор столбцов с пустыми значениями не допускается (/допускается).
- Данное выражение равнозначно применению выражения Case, проверяющему последовательно каждый аргумент на условие When argument1 is not null then argument1. По сути, Coalesce это «ярлык», созданный для удобства использования, и во многих СУБД оптимизаторы запросов переписывают выражение Coalesce на Case.
- Sql Coalesce функции имеются во всех ведущих реляционных
Синтаксис Coalesce
Каждый, кто хоть раз применял Coalesce при составлении sql-запросов, знает, что синтаксис данного выражения предельно прост. Достаточно в круглых скобках указать аргументы, проверяемые на Null, через запятую. Если допустим, что аргументы имеют имена arg1, arg2, … argN, то синтаксис Coalesce будет иметь следующий вид:
Coalesce(arg1, arg2, … argN).
Подготовим несколько таблиц для изучения механизма работы данного выражения.
Подготовка таблиц
Чтобы лучше понять Coalesce sql описание, создадим в базе данных две таблицы, содержащие информацию по объектам недвижимости.
Первая таблица Area пусть содержит наименования объектов недвижимости и их площадь. Площадь может быть уточненной (area_yt) либо декларированной (area_decl).
id | object_name | area_yt | area_decl |
Объект незавершенного строительства 1 | |||
Помещение 1 | |||
Помещение 2 | |||
Земельный участок 1 | |||
Сооружение 1 | |||
Помещение 3 | |||
Сооружение 2 | |||
Вторая таблица Basic_characteristic пусть содержит информацию по основной характеристике объекта недвижимости - протяженность (Extension), глубина (Depth), площадь (Area), объем (Scope), высота (Height).
id | object_name | Extension | Depth | Area | Scope | Height |
Сооружение 1 | ||||||
Земельный участок 1 | ||||||
Помещение 1 | ||||||
Помещение 2 | ||||||
Помещение 3 |
Рассмотрели синтаксис Coalesce sql, описание, особенности использования и перейдем непосредственно к примерам.
Примеры использования
Синтаксис у выражения Coalesce предельно прост, однако важно не забывать, что результатом выполнения команды станет ПЕРВОЕ непустое найденное значение из списка аргументов. Это замечание имеет очень большое значение, поэтому аргументы в выражении необходимо расставлять в порядке их важности. Проще всего понять принцип по таблице площадей. Составьте запрос, выбирающий наименование объекта недвижимости, а также значение площади:
SELECT Area.id, Area.object_name, coalesce(Area.area_yt, Area.area_decl) |
И получите результат:
По объектам «Здание 1», «Земельный участок 1» и «Сооружение 2» были заполнены оба значения площади, но в приоритете оказалась площадь уточненная, поскольку ее мы указали в списке аргументов первой. Выражение Coalesce нашло первое непустое значение и вывело его, прекратив дальнейший просмотр аргументов. Данное построение запроса - верное, ведь уточненная площадь более определенная, нежели декларированная. Если бы мы указали в качестве первого аргумента площадь декларированную, то при заполненности этого поля таблицы она оказалась бы в приоритете.
Помимо использования в Select, очень часто выражение Coalesce применяется с условием Where. Оно позволяет отсечь из результата те строки, в которых значения перечня полей пустые (или наоборот, включить в результат только те значения, где перечень полей не заполнен). Данная ситуация встречается повсеместно: например, на предприятии при оформлении нового сотрудника в базу данных внесли только базовые сведения о нем, а заполнение подробной информации оставили «на потом». Постепенно «пробелы» всплывают - или перед проверкой, или при направлении сотрудника в отпуск/командировку/больничный.
Выберем из таблицы с основными характеристиками объекты недвижимости, у которых не заполнено ни одно из значений характеристик:
Надеемся, что наше подробное Coalesce sql описание помогло вам понять все особенности использования данного выражения, а также разобраться с важными нюансами.
Функция NVL
Функция NVL, как правило, применяется чаще всего. Функция получает два параметра: NVL(expr1, expr2). Если первый параметр expr1 не равен NULL, то функция возвращает его значение. Если первый параметр NULL, то вместо него функция возвращает значение второго параметра expr2.
Рассмотрим практический пример. Поле COMM в таблице EMP может содержать значения NULL. При выполнении запроса вида:
SELECT EMPNO, ENAME, COMM, NVL(COMM, 0) NVL_COMM
FROM SCOTT.EMP
значение NULL будет заменено на ноль. Обратите внимание на то, что в случае формирования значения при помощи функции ему назначается псевдоним. Результаты запроса будут иметь вид:
| EMPNO | ENAME | COMM | NVL_COMM |
| 7369 | SMITH | 0 | |
| 7499 | ALLEN | 300 | 300 |
| 7521 | WARD | 500 | 500 |
| 7566 | JONES | 0 | |
| 7654 | MARTIN | 1400 | 1400 |
| 7698 | BLAKE | 0 | |
| 7782 | CLARK | 0 | |
| 7839 | KING | 0 | |
| 7844 | TURNER | 0 | 0 |
| 7900 | JAMES | 0 | |
| 7902 | FORD | 0 | |
| 7934 | MILLER | 0 |
Функция CEIL(n)
Функция CEIL возвращает наименьшее целое, большее или равное переданному в качестве параметра числу n. Например:
SELECT CEIL(100) X1, CEIL(-100) X2, CEIL(100.2) X3 , CEIL(-100.2) X4
FROM DUAL
Функция TRUNC(n [,m])
Функция TRUNC возвращает число n, усеченное до m знаков после десятичной точки. Параметр m может не указываться – в этом случае n усекается до целого.
SELECT TRUNC(100.25678) X1, TRUNC(-100.25678) X2, TRUNC(100.99) X3,
TRUNC(100.25678, 2) X4
FROM DUAL
Функция SIGN(n)
Функция SIGN определяет знак числа. Если n положительное, то функция возвращает 1. Если отрицательное возвращается -1. Если равно нулю, то возвращается 0. Например:
SELECT SIGN(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3
FROM DUAL
Интересной особенностью данной функции является возможность передачи m равного нулю при этом не возникает ошибки деления на 0.
Функция POWER(n, m)
Функция POWER возводит число n в степень m. Степень может быть дробной и отрицательной, что существенно расширяет возможности данной функции.
SELECT POWER(10, 2) X1, POWER(100, 1/2) X2,
POWER(1000, 1/3) X3, POWER(1000, -1/3) X4
FROM DUAL
| X1 | X2 | X3 | X4 |
| 100 | 10 | 10 | 0,1 |
В некоторых случаях при вызове данной функции может возникнуть исключительная ситуация. Например:
SELECT POWER(-100, 1/2) X2
FROM DUAL
В данном случае производится попытка вычисления квадратного корня от отрицательного числа, что приведет к возникновению ошибки ORA-01428 «Аргумент вне диапазона».
Функция SQRT(n)
Данная функция возвращает квадратный корень от числа n. Например:
SELECT SQRT(100) X
FROM DUAL
Функции EXP(n) и LN(n)
Функция EXP возводит e в степень n, а функция LN вычисляет натуральный логарифм от n (при этом n должно быть больше нуля). Пример:
SELECT EXP(2) X1, LN(1) X2, LN(EXP(2)) X3
Функция TO_CHAR с числами
Функции преобразования данных к другим типам данных. TO_CHAR(число) преобразует число в текст. TO_NUMBER(строка) преобразует текст в число.
SELECT TO_CHAR (123) FROM DUAL вернет строку 123, SELECT TO_NUMBER (`12345") FROM DUAL вернет число 12345.
Лабораторная работа. Изменение формата выводимых чисел
Изменения формата числовых значений в Oracle SQL, функция TO_CHAR для работы с числовыми значениями.
Задание:
Напишите запрос, который бы выводил информацию о имени, фамилии и зарплате сотрудников из таблицы hr.employees в формате, представленном на рис. 3.4-1:
Рис. 3.4-1
При этом данные должны быть отсортированы таким образом, чтобы первыми выводились строки для сотрудников с наибольшей зарплатой.
Примечание:
Некоторые значения зарплаты на рис. 3.4-1 были изменены, поэтому они могут не совпадать с вашими значениями.
Решение:
SELECT first_name AS «Имя», last_name As «Фамилия», TO_CHAR (SALARY, "L999999999.99") As «Оклад» FROM hr.employees ORDER BY SALARY DESC.
Функции TO_NUMBER и TO_DATE
Функция преобразования строки в дату TO_DATE (строка, формат). Возможные значения форматов уже рассмотрены выше, поэтому приведу несколько примеров использования данной функции. Примеры:
SELECT TO_DATE («01.01.2010", `DD.MM.YYYY") FROM DUAL вернет дату `01.01.2010";
SELECT TO_DATE («01.JAN.2010", `DD.MON.YYYY") FROM DUAL вернет дату `01.01.2009";
SELECT TO_DATE («15-01-10", `DD-MM-YY") FROM DUAL вернет дату `15.01.2010".
Функция преобразования строки в числовое значение TO_NUMBER (строка, формат). Самые распространенные значения форматов перечислены в таблице, поэтому рассмотрим применение данной функции на примерах. Примеры:
SELECT TO_NUMBER (`100") FROM DUAL вернет число 100 SELECT TO_NUMBER (`0010.01", «9999D99") FROM DUAL вернет число 10.01;
SELECT TO_NUMBER ("500,000", "999G999") FROM DUAL вернет число 500000.
Элемент RR в формате даты
Элемент формат даты и времени RR похож на элемент формате YY даты и времени, но это обеспечивает дополнительную гибкость для хранения значений даты и в других столетий. Элемент формата RR даты и времени позволяет хранить даты 20-го века в 21-м веке, указав только две последние цифры года.
Если две последние цифры текущего года являются 00 до 49, то возвращаемый год имеет те же первые две цифры, как в текущем году.
Если две последние цифры текущего года от 50 до 99, то первые 2 цифры возвращенного года являются 1 больше, чем в первые 2 цифр текущего года.
Если две последние цифры текущего года являются 00 до 49, то первые 2 цифры возвращенного года являются 1 меньше первых 2 цифр текущего года.
Если две последние цифры текущего года от 50 до 99, то возвращаемый год имеет те же первые две цифры, как в текущем году.
Функция NVL
Функция NVL, как правило, применяется чаще всего. Функция получает два параметра: NVL (expr1, ехрг2). Если первый параметр expr1 не равен NULL, то функция возвращает его значение. Если первый параметр NULL, то вместо него функция возвращает значение второго параметра ехрг2.
Пример: Выберите NVL (supplier_city, н / а ") от поставщиков:
В заявлении SQL выше вернется н / ", если поле supplier_city содержится нулевое значение. В противном случае он вернет значение supplier_city.
Другой пример использования функции NVL в Oracle / PLSQL является:
выберите supplier_id, NVL (supplier_desc, supplier_name) от поставщиков.
Это SQL заявление будет вернуть supplier_name поле, если supplier_desc содержится нулевое значение. В противном случае он вернет supplier_desc .
Последний пример: используя функцию NVL в Oracle / PLSQL является: выберите NVL (комиссия, 0) от продаж;
Это SQL заявление вернула значение 0, если комиссия поле содержится нулевое значение. В противном случае, было бы вернуть комиссии поле.
Преобразования NVL для различных типов данных
Для преобразования неопределенного значения в фактическое используется функция NVL: NVL (выражение1, выражение 2 ), где:
выражение1- Исходное или вычисленное значение, которое может быть неопределенным.
выражение2 - Значение, которое подставляется вместо неопределенного значения.
Примечание: Функцию NVL можно применять для преобразования любого типа данных, но результат всегда будет того же типа, что и выражение1.
Преобразование NVL для различных типов:
NUMBER - NVL (числовой столбец, 9).
CHAR или VARCHAR2 - NVL (символы|столбец, "Недоступно").
Лабораторная работа. Применение функции NVL
Функция NVL для работы с неопределенными значениями в Oracle SQL.
Задание:
Напишите запрос, который выводит информацию об имени и фамилии сотрудников из таблицы hr.employees., а также ставку комиссии (столбец COMMISSION_PCT) для сотрудника. При этом для тех сотрудников, для которых комиссия не определена, нужно вывести значение 0. Результат выполнения запроса должен быть таким, как представлено на рис. 3.5-1.
Рис. 3.5-1 (показаны значения начиная со строки 51)
Решение:
Код соответствующего запроса может быть таким:
SELECT first_name AS «Имя», last_name As «Фамилия», NVL (COMMISSION_PCT, 0) As «Ставка комиссии» FROM hr.employees.
Функция NVL
Функция NVL, как правило, применяется чаще всего. Функция получает два параметра: NVL(expr1, expr2). Если первый параметр expr1 не равен NULL, то функция возвращает его значение. Если первый параметр NULL, то вместо него функция возвращает значение второго параметра expr2.
Рассмотрим практический пример. Поле COMM в таблице EMP может содержать значения NULL. При выполнении запроса вида:
SELECT EMPNO, ENAME, COMM, NVL(COMM, 0) NVL_COMM
FROM SCOTT.EMP
значение NULL будет заменено на ноль. Обратите внимание на то, что в случае формирования значения при помощи функции ему назначается псевдоним. Результаты запроса будут иметь вид:
| EMPNO | ENAME | COMM | NVL_COMM |
| 7369 | SMITH | 0 | |
| 7499 | ALLEN | 300 | 300 |
| 7521 | WARD | 500 | 500 |
| 7566 | JONES | 0 | |
| 7654 | MARTIN | 1400 | 1400 |
| 7698 | BLAKE | 0 | |
| 7782 | CLARK | 0 | |
| 7839 | KING | 0 | |
| 7844 | TURNER | 0 | 0 |
| 7900 | JAMES | 0 | |
| 7902 | FORD | 0 | |
| 7934 | MILLER | 0 |
Функция CEIL(n)
Функция CEIL возвращает наименьшее целое, большее или равное переданному в качестве параметра числу n. Например:
SELECT CEIL(100) X1, CEIL(-100) X2, CEIL(100.2) X3 , CEIL(-100.2) X4
FROM DUAL
Функция TRUNC(n [,m])
Функция TRUNC возвращает число n, усеченное до m знаков после десятичной точки. Параметр m может не указываться – в этом случае n усекается до целого.
SELECT TRUNC(100.25678) X1, TRUNC(-100.25678) X2, TRUNC(100.99) X3,
TRUNC(100.25678, 2) X4
FROM DUAL
Функция SIGN(n)
Функция SIGN определяет знак числа. Если n положительное, то функция возвращает 1. Если отрицательное возвращается -1. Если равно нулю, то возвращается 0. Например:
SELECT SIGN(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3
FROM DUAL
Интересной особенностью данной функции является возможность передачи m равного нулю при этом не возникает ошибки деления на 0.
Функция POWER(n, m)
Функция POWER возводит число n в степень m. Степень может быть дробной и отрицательной, что существенно расширяет возможности данной функции.
SELECT POWER(10, 2) X1, POWER(100, 1/2) X2,
POWER(1000, 1/3) X3, POWER(1000, -1/3) X4
FROM DUAL
| X1 | X2 | X3 | X4 |
| 100 | 10 | 10 | 0,1 |
В некоторых случаях при вызове данной функции может возникнуть исключительная ситуация. Например:
SELECT POWER(-100, 1/2) X2
FROM DUAL
В данном случае производится попытка вычисления квадратного корня от отрицательного числа, что приведет к возникновению ошибки ORA-01428 «Аргумент вне диапазона».
Функция SQRT(n)
Данная функция возвращает квадратный корень от числа n. Например:
SELECT SQRT(100) X
FROM DUAL
Функции EXP(n) и LN(n)
Функция EXP возводит e в степень n, а функция LN вычисляет натуральный логарифм от n (при этом n должно быть больше нуля). Пример:
SELECT EXP(2) X1, LN(1) X2, LN(EXP(2)) X3
На носу выборы, а это значит, что сегодня мы будем выбирать губернатора. А точнее назначать барьер прохождения кандидатов в следующий тур. Но сначала надо определиться с терминами.
Что такое гарантированный выбор в SQL? Допустим, что в условии запроса к таблице выполняется сравнение какого-либо поля с какой-нибудь переменной. В зависимости от значения этой переменной запрос может вернуть строки из таблицы, а может и не вернуть их вовсе. Если выпадает такое значение переменной, что строки из таблицы не возвращаются, то для этого случая надо специально сгенерировать заранее определенный левый результат. То есть в любом случае общий запрос должен гарантированно что-нибудь да вернуть. Сам термин взят отсюда . Однако задача усложняется тем (а может и наоборот, упрощается), что вместо одной простой ячейки со значением, нам нужно гарантировано вернуть полноценную строку.
Привожу данные центризбиркома. Первый тур голосования закончился с такими результатами
create table election
as
with t (id, name, profession, votes) as (select 1, "Неподкупный Аморал Чисторукович", "прокурор", 9867 from dual union all
select 2, "Эффективный Бюджет Освоилович", "бизнесмен", 8650 from dual union all
select 3, "Правдивый Чтодадут Написайлович", "редактор газеты", 745 from dual union all
select 4, "Благообразная Люцифера Феоктистовна ", "настоятельница", 234 from dual union all
select 5, "Хренсгоры Ктотакой Никтонезнаевич", "учитель сельской школы", 3 from dual)
select * from t;
alter table election add primary key (id);
Начальный запрос, определяющий выход кандидатов в следующий тур, предельно прост:
select * from election where votes > :bound
Предположим, что проходной барьер равен 8000 голосов. Сбиндив это число с:bound, получаем
Но что если проходной барьер 10 000, то есть больше, чем максимально набранное количество голосов? Тогда очевидно, что из вышеозначенных кандидатов в следующий тур не проходит никто. В этом случае устанавливается диктатура и губернатором области автоматически становится кот Полковник. Вот некоторые из способов его назначения:
Способ 1. UNION ALL таблицы с агрегированной собой же
With t as (select * from election where votes > :bound) select id, name, profession, votes from t union all select 0, "Полковник", "кот", null from t having count(*) = 0 order by votes desc
Способ 2
. UNION ALL таблицы с DUAL
with t as (select *
from election
where votes > :bound)
select id, name, profession, votes
from t
union all
select 0, "Полковник", "кот", null
from dual
where not exists (select null from t)
order by votes desc
Способ 3.
LEFT JOIN таблицы с DUAL
select nvl(e.id, 0) id,
nvl2(e.id, e.name, "Полковник") name,
nvl2(e.id, e.profession, "кот") profession,
e.votes
from dual d
left join election e on e.votes >
для случаев, когда в таблице отсутствует уникальное NOT NULL поле
select nvl2(e.rowid, e.id, 0) id,
nvl2(e.rowid, e.name, "Полковник") name,
nvl2(e.rowid, e.profession, "кот") profession,
e.votes
from dual d
left join election e on e.votes > :bound
order by e.votes desc
Способ 4.
Моделька с котом.
select id, name, profession, votes
from election
where votes > :bound
model
dimension by (rownum rn)
measures (id, name, profession, votes)
rules (name = nvl2(id, name, "Полковник"),
profession = nvl2(id, profession, "кот"),
id = nvl(id, 0))
order by votes desc
Ниже гарантированный результат при бинде со слишком большим барьером 10 000
Ясно, что если задать начальную планку 8000, то эти запросы также отработают корректно.
На этом пока всё. Все совпадения имен персонажей с реальными людьми считать случайными.
Решение некоторых других типичных задач SQL можно посмотреть