Максим Чалышев - Oracle SQL. 100 шагов от новичка до профессионала

А также знакомые нам со школы функции:

Sqrt — квадратный корень;

MOD — остаток от деления;

trunc — округление до целого;

sIN — синус;

cos — косинус

(на самом деле этих функций больше, мы рассматриваем основные).

Все математические операции выполняются только для числовых значений, числовых колонок с типами NUMBER или производными от NUMBER (INt, float) — более подробную информацию можно посмотреть в документации к СУБД. То есть мы можем использовать в математических выражениях значения соответствующих колонок при выводе на экран и в фильтре WHERE.

Запомним, что математические операции используются также в критериях отбора строк WHERE.

Посмотрим, как это делается.

Вывести из таблицы MAN имя, фамилию и возраст (FIRSTNAME, LASTNAME, YEAROLD) человека, разделенный на 10.

Рисунок 41. Запрос к MAN: результат математического вычисления

В колонке с псевдонимом y10 выводится результат выражения YEAROLD/10.

Вывести из таблицы MAN имя, фамилию и возраст человека (FIRSTNAME, LASTNAME, YEAROLD), умноженный на sIN (1), округлить до целого.

Рисунок 42. Запрос к MAN: результат вычисления sIN

Вывести из таблицы CITY записи (*), где квадратный корень от количества населения города больше 1000 (PEOPLES).

Рисунок 43. Запрос к CITY: квадратный корень больше 1000

Пример демонстрирует использование математического выражения sqrt(CITY.PEOPLES) при фильтрации строк в WHERE.

Вывести из таблицы CITY название города (CITYNAME), квадратный корень от количества населения (PEOPLES), где значение кода города (CITYCODE) делится нацело на 3.

Рисунок 44. Запрос к CITY с математическим выражением

Вывести из таблицы CITY название города (CITYNAME), код города, разделенный на 3 (CITYCODE), где значение населения (PEOPLES), разделенное на 100, не больше 1 000 000.

Рисунок 45. Запрос к CITY: математическое выражение WHERE и SELECTLISt

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

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

Зачем использовать псевдонимы для колонок, которые рассчитываются как математические выражения?

Для удобного, понятного отображения названия колонки.

Где найти полный перечень математических функций, поддерживаемых СУБД?

В документации по вашей СУБД.

1. Вывести из таблицы MAN имя, фамилию и квадратный корень 10.

2. Вывести из таблицы MAN имя, фамилию и возраст человека, умноженный на cos (5).

3. Вывести из таблицы CITY записи (*), где популяция делится без остатка на 10 000.

4. Вывести из таблицы CITY название города, квадратный корень от популяции, умноженный на 10, где значение кода города делится нацело на 3.

Продолжением данного шага является следующий шаг, посвященный таблице DUAL.

В SQL ORACLE диалекта есть специальная таблица DUAL. Если в других СУБД существуют альтернативные варианты синтаксиса, то для SQL ORACLE необходимо использовать таблицу DUAL.

Данная таблица выполняет следующие задачи:

• делает удобным вывод в запросе «результаты вычислений математического выражения»;

• помогает при вычислении функции, которая возвращает одно значение.

При этом для результата вычисления данных выражений нам не требуется участие колонок из каких-либо таблиц в нашей базе данных.

Разберем на примерах:

Вычислить значение математического выражения 1000* (123—11) /100 с помощью SELECT.

Рисунок 46. Вычисление выражения

Вычислить значение функции sIN (10) /100 с помощью SELECT.

Рисунок 47. Вычисление выражения, таблица DUAL

Вычислить значение текущей даты с помощью SELECT.

— 21.11.1999 11:22

Вычислить значение sIN (1) *cos (1) с помощью SELECT.

Рисунок 48. Вычисление выражения и таблица sIN (1) * cos (1)

При обращении к таблице DUALзапрос возвращает всего одну строку, при попытке добавить запись в таблицу DUALмогут произойти непредсказуемые последствия, не рекомендую это делать.

Вы говорили, что в других СУБД, например MS SQL, можно обойтись без таблицы DUAL?

Да в MS SQL, например, будет работать такой запрос: SELECT sIN (1), здесь нет FROM, ORACLE в этом случае более соответствует стандарту SQL ANSI 92.

1. Вычислить значение площади квадрата со стороной 5 с помощью SELECT и DUAL.

2. Вычислить значение площади круга с радиусом 7 помощью SELECT и DUAL.

3. Вычислить значение выражения 100*sIN (1) *cos (3) с помощью SELECT и DUAL.

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

Для работы с типом NULL в языке SQL используется специальный синтаксис.

Синтаксис

Выполняется проверка на наличие пустых значений в колонке1 таблицы TABLE1.

Синтаксис

Проверка на отсутствие пустых значений в колонке1 таблицы TABLE1.

Примеры для изучения

Вывести из таблицы AUTO все строки, где значения поля PHONENUM пустые.

Рисунок 49. Запрос к AUTO с незаполненным номером телефона

Вывести из таблицы AUTO все строки, где значения поля PHONENUM не пустые.

Рисунок 50. Запрос к AUTO, где PHONENUM заполнено

Так же в ORACLE SQL применяется специальная встроенная функция NVL, которая преобразует пустое значение в другое, заданное значение.

Синтаксис

Примеры использования NVL:

Вывести на экран все строки из AUTO, а в поле PHONENUM вместо пустого значения выводим 0.

Рисунок 51. Использование NVL

Вывести из таблицы AUTO все строки, где значения поля PHONENUM не пустые, используя NVL.

Рисунок 52. Запрос: использование NVL

Для работы с пустыми значениями в СУБД ORACLE и других СУБД необходимо использовать конструкцию IS NOT NULLи IS NULLдля проверки пустых значений или использовать NVL.

Запрос вида

почти всегда приведет к некорректному результату.

Есть ли возможность в языке SQL, если в поле находится пустое значение, вывести один результат, а если не пустое, то другой?

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