ШАГ 6 - Функции второй группы

Oracle хранит данные в диапазоне от 1 января 4712 года до нашей эры до 31 декабря 4712 года н.э.
Внутренний числовой формат даты включает:

век
год
месяц
день
часы
минуты
cекунды

Системная дата SYSDATE это псевдостолбец.
Для ее получения удобно использовать таблицу DUAL, принадлежащую системному администратору SYS и доступную всем пользователям по чтению.
DUAL содержит единственный столбец DUMMY и единственную строку со значением «Х».
Таблица DUAL позволяет получить единственное значение, не связанное с таблицами БД, для константы, псевдостолбца или выражения, не содержащего имен столбцов таблиц базы.
Обратите внимание -- это способ преодоления недостатка SQL, который умеет работать только с базой. Сравните запросы:

SELЕСТ SYSDATE FRОМ DUAL;

SELЕСТ SYSDATE FRОМ EMP;

Поскольку даты хранятся в числовом формате, с ними можно производить вычисления, используя операции типа:

дата+число
дата-число -- прибавляет или отнимает указанное количество дней;
дата-дата -- вычисляет количество дней между датами;
дата+число/24 -- прибавляет заданное количество часов, возвращая новую дату.

MONTHS_BETWEEN (дата1|дата2)     -- вычисляет количество месяцев между двумя датами. Результат может быть положительным или отрицательным в зависимости от того, какая дата на каком месте стоит. Если дата1 более поздняя, чем дата2, то результат положительный;

ADD_MONTHS (дата, n)     -- прибавляет к дате n месяцев. N должно быть целым и может быть отрицательным;

NEXT_DAY (дата, ’строка’)     -- возвращает дату, следующую после заданной и соответствующую дню недели, определяемому ’строкой’. ‘Строка’ может быть номером (день недели по порядку) или названием дня недели;

LAST_DAY (дата)     -- возвращает последний день месяца для данной даты;

ROUND (дата)     -- округляет дату до полуночи. Это удобно для сравнения двух дат с разным значением часов/минут/секунд;

ROUND (дата, ‘MONTH’)     -- округляет дату до ближайшего 1-го числа;

ROUND (дата, ‘YEAR’)     -- возвращает ближайшее к дате 1 января;

TRUNK (дата, строка)     -- аналогично ROUND, но обрезает значение, а не округляет его; если в качестве строки стоит MONTH, возвращается 1-е число данного месяца, если YEAR, то 1-е января этого года и т.д.;

Функции конвертирования данных

TO_CHAR (дата, ‘формат_представления’)     -- преобразует дату, представляемую по умолчанию в формате DD-MON-YY, в другой вид, форматом_представления. Форматы представления определены в Приложении 1. Следует помнить, что маска форматирования должна быть взята в одинарные кавычки и может содержать любые форматы, приведенные в Приложении 1. DAY и MONTH в строке результатов автоматически добавляются пробелами справа до 9 символов. Чтобы исключить пробелы справа,добавьте к соответствующей маске префикс FM (fill mode). Задание маски форматирования не обязательно. Если она опущена, то преобразование в символьный формат производится по умолчанию. Для дат - это формат DD-MON-YY. Для чисел формат по умолчанию - это просто число в виде символьной строки. Маски форматирования никак не влияют на внутренний формат представления данных в памяти. Они только влияют на вид, в котором данные выдаются в таблице результатов по команде SELECT;

TO_NUMBER (строка)     -- преобразует символьные строки, содержащие числа, в цифры;

TO_DATE (‘строка[,’формат’])     -- преобразует символьные строки в даты на основании шаблона, заданного в формате. Если формат не задан, то по умолчанию это DD-MON-YY. Часто используется для передачи в Oracle дат в нестандартном формате;

Функции над данными любого типа

DECODE (столбец|выражение, знач_1, рез_1,
знач_2, резт_2, ... рез_по_умолчанию)     -- если значение столбца|выражения совпадает со значением_1, то выдается результат1 , если оно не совпадает ни с одним значением выдается результат_по_умолчанию; обращайте внимание на однотипность данных результатов_1 и результата_по_умолчанию;

NVL (столбец|выражение, результат)     -- если значение столбца|выражения не пусто, оно не меняется, если же оно NULL, выдается результат; помните, что значение любого выражения с операндом NULL есть NULL, а очень часто по смыслу задачи нужно использовать определенное значение; если, например, мы хотим вычислить сумму заработной платы SAL и комиссионных COMM, которые определены не для всех работников, то значение итогового вознаграждения, вычисляемого как SAL+COMM, будет не определено для сотрудников - не получаемых комиссионных; в этом случае правильное выражение SAL+NVL(COMM,0); типы обоих аргументов должны соответствовать друг другу;

GREATEST (столбец1|знач_1, столбец2|знач_2,.....)     -- выбирает максимальное значение из списка; перед началом сравнения все значения аргументов, начиная со второго, конвертируются в тип первого аргумента;

LEAST (столбец1|знач_1, столбец2|знач_2,.....)     -- минимальное значение из списка; перед началом сравнения все значения аргументов, начиная со второго, конвертируются в тип первого аргумента;

VSIZE (столбец|значение)     -- выдает количество байтов, используемых для хранения в памяти столбца/значения;

Упражнения

1. Напечатаем имена всех сотрудников отдела 20 и даты приема их на работу, чтобы дата и месяц приема были прописными буквами.

SELЕСТ DEPTNO, ЕNАМЕ, TO_CHAR(HIREDATE,'fmMonth, DDspth YYYY') HIREDATE FRОМ EMP WHERE DEPTNO = 20;

В окне SQL*Plus Вы должны получить следующие значения:

 DEPTNO ENAME   HIREDATE
 ------ ------  ----------------------------
     20 SMITH   December, SEVENTEENTH 1980
     20 JONES   April, SECOND 1981
     20 SCOTT   April, NINETEENTH 1987
     20 ADAMS   May, TWENTY-THIRD 1987
     20 FORD    December, THIRD 1981

2. Напечатаем имя каждого сотрудника с датой приема на работу и датой назначения оклада. Будем считать, что оклад назначается через год после приема на работу. Расположим данные в порядке возрастания даты назначения оклада.

SELЕСТ ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 12) REVIEW FRОМ EMP ORDER BY ADD_MONTHS (HIREDATE,12);

В окне SQL*Plus Вы должны получить следующие значения:

ENAME    HIREDATE  REVIEW
-------- --------- ---------
SMITH    17-DEC-80 17-DEC-81
ALLEN    20-FEB-81 20-FEB-82
WARD     22-FEB-81 22-FEB-82
JONES    02-APR-81 02-APR-82
.....    ......... .........

3. Напишем запрос для вычисления длительности работы в фирме любого из сотрудников. Используем DEFINE, чтобы избежать многократного повторения одних и тех же функций.

DEFINE TIME = MONTHS_ ВЕТWЕЕN (SYSDАТЕ, НIREDATE);
SELЕСТ ENAME, FLOOR (&TIME/12) || ' YEARS ' || FLOOR (MOD(&TIME,12)) || 'MONTHS' "LEHGTH OF SERVICE"
FRОМ EMP WHERE ENAME = UPPER('&EMPLOYEE_ NAME');

В окне SQL*Plus Вы должны получить следующие значения:

Enter value  for employee_ namе:  king

ЕNАМЕ  LENGTH  ОF  SERVICЕ
-------------------------------------------
KING        4 YEARS 4 MONTHS

4. Сотрудники, принятые на работу с 1 по 15 число любого месяца, начинают получать зарплату с последней пятницы того же месяца. Те, кто принят после 15 числа, первую зарплату получают в последнюю пятницу следующего месяца. Напечатаем список сотрудников, дату их приема на работу и дату первой получки. Рассортируем их по дате приема на работу.

SELЕСТ ENAME, HIREDATE, NEXT_DAY (LAST_DAY (ROUND(HIREDATE,'MONTH'))-7,'FRIDAY') PAYDAY FRОМ EMP ORDER BY HIREDATE;

В окне SQL*Plus Вы должны получить следующие значения:

ENAME    HIREDATE  PAYDAY
-------- --------- ---------
SMITH    17-DEC-80 30-JAN-81
ALLEN    20-FEB-81 27-MAR-81
WARD     22-FEB-81 27-MAR-81
JONES    02-APR-81 24-APR-81
.....    ......... .........

MONTHS_BETWEEN (дата1\|дата2)		-- вычисляет количество месяцев между двумя датами. Результат может быть положительным или отрицательным в зависимости от того, какая дата на каком месте стоит. Если дата1 более поздняя, чем дата2, то результат положительный;
ADD_MONTHS (дата, n)		-- прибавляет к дате n месяцев. N должно быть целым и может быть отрицательным;
NEXT_DAY (дата, ’строка’)		-- возвращает дату, следующую после заданной и соответствующую дню недели, определяемому ’строкой’. ‘Строка’ может быть номером (день недели по порядку) или названием дня недели;
LAST_DAY (дата)		-- возвращает последний день месяца для данной даты;
ROUND (дата)		-- округляет дату до полуночи. Это удобно для сравнения двух дат с разным значением часов/минут/секунд;
ROUND (дата, ‘MONTH’)		-- округляет дату до ближайшего 1-го числа;
ROUND (дата, ‘YEAR’)		-- возвращает ближайшее к дате 1 января;
TRUNK (дата, строка)		-- аналогично ROUND, но обрезает значение, а не округляет его; если в качестве строки стоит MONTH, возвращается 1-е число данного месяца, если YEAR, то 1-е января этого года и т.д.;

TO_CHAR (дата, ‘формат_представления’)		-- преобразует дату, представляемую по умолчанию в формате DD-MON-YY, в другой вид, форматом_представления. Форматы представления определены в Приложении 1. Следует помнить, что маска форматирования должна быть взята в одинарные кавычки и может содержать любые форматы, приведенные в Приложении 1. DAY и MONTH в строке результатов автоматически добавляются пробелами справа до 9 символов. Чтобы исключить пробелы справа,добавьте к соответствующей маске префикс FM (fill mode). Задание маски форматирования не обязательно. Если она опущена, то преобразование в символьный формат производится по умолчанию. Для дат - это формат DD-MON-YY. Для чисел формат по умолчанию - это просто число в виде символьной строки. Маски форматирования никак не влияют на внутренний формат представления данных в памяти. Они только влияют на вид, в котором данные выдаются в таблице результатов по команде SELECT;
TO_NUMBER (строка)		-- преобразует символьные строки, содержащие числа, в цифры;
TO_DATE (‘строка[,’формат’])		-- преобразует символьные строки в даты на основании шаблона, заданного в формате. Если формат не задан, то по умолчанию это DD-MON-YY. Часто используется для передачи в Oracle дат в нестандартном формате;

DECODE (столбец\|выражение, знач_1, рез_1, знач_2, резт_2, ... рез_по_умолчанию)		-- если значение столбца\|выражения совпадает со значением_1, то выдается результат1 , если оно не совпадает ни с одним значением выдается результат_по_умолчанию; обращайте внимание на однотипность данных результатов_1 и результата_по_умолчанию;
NVL (столбец\|выражение, результат)		-- если значение столбца\|выражения не пусто, оно не меняется, если же оно NULL, выдается результат; помните, что значение любого выражения с операндом NULL есть NULL, а очень часто по смыслу задачи нужно использовать определенное значение; если, например, мы хотим вычислить сумму заработной платы SAL и комиссионных COMM, которые определены не для всех работников, то значение итогового вознаграждения, вычисляемого как SAL+COMM, будет не определено для сотрудников - не получаемых комиссионных; в этом случае правильное выражение SAL+NVL(COMM,0); типы обоих аргументов должны соответствовать друг другу;
GREATEST (столбец1\|знач_1, столбец2\|знач_2,.....)		-- выбирает максимальное значение из списка; перед началом сравнения все значения аргументов, начиная со второго, конвертируются в тип первого аргумента;
LEAST (столбец1\|знач_1, столбец2\|знач_2,.....)		-- минимальное значение из списка; перед началом сравнения все значения аргументов, начиная со второго, конвертируются в тип первого аргумента;
VSIZE (столбец\|значение)		-- выдает количество байтов, используемых для хранения в памяти столбца/значения;