Nvl синтаксис. Операторы ветвления в команде SELECT. Лабораторная работа. Применение функции NVL

Функция 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 будет заменено на ноль. Обратите внимание на то, что в случае формирования значения при помощи функции ему назначается псевдоним. Результаты запроса будут иметь вид:

Функция 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

В некоторых случаях при вызове данной функции может возникнуть исключительная ситуация. Например:

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

Функция 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 будет заменено на ноль. Обратите внимание на то, что в случае формирования значения при помощи функции ему назначается псевдоним. Результаты запроса будут иметь вид:

Функция 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

В некоторых случаях при вызове данной функции может возникнуть исключительная ситуация. Например:

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