Сегодня мы рассмотрим: Настоящие ценители музыки знают, что для качественного...
Функция 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
Функция 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.