Все функции работы с датами sql oracle. Функции для работы с числами в Oracle. Функции преобразования данных в Oracle
Oracle реализует набор функций для работы со значениями типа даты/времени . Мы не будем подробно рассматривать все функции, но сводка в табл. 1 познакомит Вас с доступными возможностями. Если какие-то функции вас заинтересуют, обращайтесь за подробным описанием к справочнику Oracle SQL Reference.
Избегайте использования традиционных функций Oracle, обрабатывающих значения типа DATE , при работе с новыми типами данных TIMESTAMP . Вместо них следует по возможности использовать новые функции для типов INTERVAL . А DATE -функции должны использоваться только для обработки значений типа DATE .
Многие из приведенных в табл. 1 функций (в том числе ADD_MONTHS) получают значения типа DATE . При использовании таких функций с новыми типами данных TIMESTAMP могут возникнуть проблемы. Хотя любой из этих функций можно передать значение типа TIMESTAMP , Oracle неявно преобразует его к типу DATE , и только тогда функция выполнит свою задачу, например:
DECLARE ts TIMESTAMP WITH TIME ZONE; BEGIN ts:= SYSTIMESTAMP; --Обратите внимание: в значении переменной ts задаются --дробные секунды И часовой пояс. DBMS_OUTPUT.PUT_LINE(ts); --Изменение значения ts одной из встроенных функций. ts:= LAST_DAY(ts); --Дробные секунды ПОТЕРЯНЫ, а часовой пояс заменен --часовым поясом сеанса. DBMS_OUTPUT.PUT_LINE(ts); END;
Результат:
| Имя | Описание |
| ADD_MONTHS | Возвращает значение DATE , полученное в результате увеличения заданного значения DATE на заданное количество месяцев. См. «Сложение и вычитание интервалов» |
| CAST | Выполняет преобразования между типами данных - например, между DATE и различными значениями TIMESTAMP . См. « CAST и EXTRACT » |
| CURRENT_DATE | Возвращает текущую дату и время в часовом поясе сеанса как значение типа DATE |
| CURRENT_TIMESTAMP | Возвращает текущую дату и время в часовом поясе сеанса как значение типа TIMESTAMP WITH TIME ZONE |
| DBTIMEZONE | Возвращает смещение часового пояса базы данных относительно UTC в форме символьной строки (например, "-05:00"). Часовой пояс базы данных используется только при работе со значениями типа TIMESTAMP WITH LOCAL TIME ZONE |
| EXTRACT | Возвращает значение NUMBER или VARCHAR2 , содержащее конкретный элемент даты/времени - час, год или сокращение часового пояса. См. « CAST и EXTRACT » |
| FROM_TZ | Преобразует TIMESTAMP и данные часового пояса в значение типа TIMESTAMP WITH TIME ZONE |
| LAST_DAY | Возвращает последний день месяца для заданного входного значения DATE |
| LOCALTIMESTAMP | Возвращает текущую дату и время как значение типа TIMESTAMP в локальном часовом поясе |
| MONTHS_ BETWEEN | Возвращает значение NUMBER , содержащее количество месяцев между двумя датами. См. «Вычисление интервала между двумя значениями DATE » |
| NEW_TIME | Преобразует значение типа DATE одного часового пояса в аналогичное значение другого пояса. Функция существует для сохранения совместимости со старым кодом; в новых приложениях следует использовать типы TIMESTAMP WITH TIME ZONE или TIMESTAMP WITH LOCAL TIME ZONE |
| NEXT_DAY | Возвращает дату первого дня недели, следующего за указанной датой |
| NUMTODSINTERVAL | Преобразует заданное количество дней, часов, минут или секунд (на ваш выбор) в значение типа INTERVAL DAY TO SECOND |
| NUMTOYMINTERVAL | Преобразует заданное количество годов и месяцев (на ваш выбор) в значение типа INTERVAL YEAR TO MONTH |
| ROUND | Возвращает значение типа DATE , округленное до заданных единиц |
| SESSIONTIMEZONE | Возвращает смещение часового пояса сеанса (относительно UTC) в форме символьной строки |
| SYS_EXTRACT_UTC | Преобразует значение типа TIMESTAMP WITH TIME ZONE в значение TIMESTAMP с той же датой и временем, нормализованное по времени UTC |
| SYSDATE | Возвращает текущую дату и время сервера Oracle как значение типа DATE |
| SYSTIMESTAMP | Возвращает текущую дату и время сервера Oracle как значение типа TIMESTAMP WITH TIME ZONE |
| TO_CHAR | Преобразует значение даты/времени в символьную строку. См. «Преобразование даты и времени» |
| TO_DATE | Преобразует символьную строку в значение типа DATE . См. «Преобразование даты и времени» |
| TO_DSINTERVAL | Преобразует символьную строку в значение типа INTERVAL DAY TO SECOND . См. «Преобразования интервалов» |
| TO_TIMESTAMP | Преобразует символьную строку в значение типа TIMESTAMP . См. «Преобразование даты и времени» |
| TO_TIMESTAMP_TZ | Преобразует символьную строку в значение типа TIMESTAMP WITH TIME ZONE . См. «Преобразование даты и времени» |
| TO_YMINTERVAL | Преобразует символьную строку в значение типа INTERVAL YEAR TO MONTH . См. «Преобразования интервалов» |
| TRUNC | Возвращает значение типа DATE , усеченное до заданных единиц |
| TZ_OFFSET | Возвращает смещение относительно UTC часового пояса, заданного названием или сокращением, в форме VARCHAR2 (например, "-05:00") |
В этом примере переменная ts содержит значение типа TIMESTAMP WITH TIME ZONE . Это значение неявно преобразуется в DATE при передаче LAST_DAY . Поскольку в типе DATE не сохраняются ни дробные части секунд, ни смещение часового пояса, эти части значения ts попросту отбрасываются. Результат LAST_DAY снова присваивается ts , что приводит к выполнению второго неявного преобразования - на этот раз DATE преобразуется в TIMESTAMP WITH TIME ZONE . Второе преобразование получает часовой пояс сеанса, поэтому в смещении часового пояса в итоговом значении мы видим?05:00.
В предыдущей статье мы рассмотрели встроенные функции для работы со строками. В данной статье речь пойдет о функциях работы с датой/временем и функциями предобразования типов для даты. Для хранения даты и времени в Oracle предусмотрен специальный тип DATE. С физической точки зрения это дробное число, целая часть которого хранит количество дней с некоторой базовой даты, а дробная - время. Это позволяет совершать над датами арифметические операции - сложение и вычитание.
Функция SYSDATE
Это одна из самых часто употребляемых функций, она возвращает текущую дату и время по часам сервера.
Функция LAST_DAY(d)
Возвращает последнее число месяца, указанного в дате d.
SELECT SYSDATE d,
LAST_DAY(SYSDATE) d1
Функция MONTHS_BETWEEN(d1, d2)
Функция MONTH_BETWEEN возвращает количество месяцев между двумя датами d1 и d2 с учетом знака как d1-d2, возвращаемое число является дробным.
SELECT MONTHS_BETWEEN("2.09.2006", "2.05.2006") d1,
MONTHS_BETWEEN("12.09.2006", "2.05.2006") d2,
MONTHS_BETWEEN("2.05.2006", "12.09.2006") d3
Рассмотрим типовые примеры - усечение даты до часов, дней, месяца и года. Форматная маска по умолчанию равна «DD»
SELECT SYSDATE d1,
TRUNC(SYSDATE, "HH24") d2,
TRUNC(SYSDATE, "DD") d3,
TRUNC(SYSDATE, "MM") d4,
TRUNC(SYSDATE, "YYYY") d5
Форматные маски, допустимые для функций TRUNC и ROUND
Рассмотрим подробнее форматные маски и особенности их применения.
|
Маска |
Назначение |
Первый день столетия |
|
YEAR, или YYYY, или YY, или Y |
Первый день года |
Первый день квартала |
|
MONTH, или MON, или MM |
Первый день месяца |
Тот же день недели, что и первый день текущего года |
|
Тот же день недели, что и первый день текущего месяца |
|
DAY, или DY, или D |
Первый день недели |
HH, или HH12, или HH24 |
|
Функция TO_DATE(str[,mask [,nls_lang]])
Функция TO_DATE преобразует строку str в дату. Преобразование ведется по маске mask, если она указана. Если маска не указана, то берется маска по умолчанию. В случае указания маски можно указать еще один параметр - язык, используемый при форматировании названий месяцев и дней. В случае ошибки анализа строки str в соответствии с заданной маской возникает исключительная ситуация. Наиболее распространенная ошибка «ORA-01830: шаблон формата даты завершается перед преобразованием всей строки ввода». Кроме того, нередко встречается ошибка «ORA-01821: формат даты не распознан» - она возникает при указании недопустимой форматной маски.
SELECT TO_DATE("12.09.2006") d
Форматные маски, допустимые для функций TO_CHAR в случае форматирования даты
Столетие, причем перед датами до нашей эры ставится знак «минус».
SELECT SYSDATE d1,
TO_CHAR(SYSDATE-1000000, "SCC") d2
26.09.2006 17:14:21 |
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "YYYY") d2
Год, записанный прописью c учетом текущего национального языка.
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "YEAR") d2
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "MM") d2
Название месяца прописью.
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "MONTH") d2
Неделя года.
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "WW") d2
День года.
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "DDD") d2
День недели прописью, при необходимости дополняется до девяти символов пробелами.
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "DAY") d2
Дата юлианского календаря. Является числом дней от 1.01.4712 до нашей эры.
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "J") d2
Час дня по 12-часовой шкале (1-12).
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "HH") d2
Минуты (0-59).
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "MI") d2
Данная форматная маска полезна для измерения временных интервалов в секундах.
Знаки пунктуации. Они выводятся в соответствующие места отформатированной даты.
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "DD.MM.YYYY HH24.MI") d2,
TO_CHAR(SYSDATE, "DD/MM/YYYY HH12.MI PM") d3
Элементы форматной маски, предназначенные для вывода текстовой информации, чувствительны к регистру - регистр управляет регистром форматируемого текста. Рассмотрим управление регистром на примере форматной маски DAY:
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "DAY") d2,
TO_CHAR(SYSDATE, "Day") d3,
TO_CHAR(SYSDATE, "day") d4
26.09.2006 17:47:45 |
Несложно заметить, что если маска записана в верхнем регистре, то и форматируемое текстовое значение возвращается в верхнем регистре. Если в нижнем - форматируемое значение тоже будет в нижнем. И наконец, запись маски с прописной буквы приводит к форматированию формируемого текста таким образом, чтобы он начинался с прописной буквы.
Заключение
В данной статье мы рассмотрели все основные встроенные функции Oracle, предназначенные для работы с датами и выполнения операций преобразования типов с типом «дата». В следующей статье мы рассмотрим функции преобразования и форматирования чисел и перейдем к рассмотрению практических запросов, использующих рассмотренные ранее функции.
В данном разделе речь пойдет о функциях работы с датой/временем и функциями преобразования типов для даты. Для хранения даты и времени в Oracle предусмотрен специальный тип DATE. С физической точки зрения это дробное число, целая часть которого хранит количество дней с некоторой базовой даты, а дробная - время. Это позволяет совершать над датами арифметические операции - сложение и вычитание.
Функция SYSDATE
Это одна из самых часто употребляемых функций, она возвращает текущую дату и время по часам сервера. Пример:
SELECT
SYSDATE
FROM dual
SYSDATE
26.12.2007
16:24:43
Функция ADD_MONTHS(d, x)
Возвращает дату, полученную в результате прибавления к дате d одного или нескольких месяцев. Количество месяцев задается параметров х, причем х может быть отрицательным - в этом случае указанное количество месяцев вычитается из заданной даты.
SELECT SYSDATE d,
ADD_MONTHS(SYSDATE, 3) d1, ADD_MONTHS(SYSDATE,
-3)
d2
FROM dual
|
26.12.2007 16:24:43 |
26.03.2008 16:24:43 |
26.09.2007 16:24:43 |
Функция LAST _DAY(d)
Возвращает последнее число месяца, указанного в дате d. Пример:
SELECT SYSDATE d,
LAST_DAY(SYSDATE) d1
FROM dual
|
26.12.2007 16:24:43 |
31.12.2008 16:24:43 |
Данная функция очень удобна для определения количества дней в заданном месяце, например:
SELECT SYSDATE d,
TO_CHAR(LAST_DAY(SYSDATE), "DD") d1
FROM dual
|
26.12.2007 16:24:43 |
Функция MONTHS_BETWEEN(dl, d2)
Функция MONTH_BETWEEN возвращает количество месяцев между двумя датами dl и d2 с учетом знака как dl-d2, возвращаемое число является дробным.
SELECT MONTHS_BETWEEN("2.09.2006",
"2.05.2006") d1,
M0NTHS_BETWEEN("12.09.2006", "2.05.2006") d2,
M0NTHS_BETWEEN("2.05.2006", "12.09.2006") d3
FROM dual
|
4,32258064516129 |
4,32258064516129 |
Функция TRUN С (d[,mask ])
Производит усечение указанной даты в соответствии с маской. Если маска не указана, то усечение производится до даты (время отбрасывается).
SELECT SYSDATE
d1,
TRUNC
(SYSDATE)
d2
FROM
dual
|
26.09.2006 16:45:26 |
Рассмотрим типовые примеры - усечение даты до часов, дней, месяца и года. Форматная маска по умолчанию равна «DD»
SELECT SYSDATE
dl,
TRUNC(SYSDATE, "HH24") d2,
TRUNC(SYSDATE, "DD") d3,
TRUNC(SYSDATE, "MM") d4,
TRUNC(SYSDATE, "YYYY") d5
FROM dual
|
26.09.2006 16:49:21 |
26.09.2006 16:00:00 |
26.09.2006 01.09.2006 01.01.2006 |
Функция ROUND(d[,mask])
Функция ROUND аналогична TRUNC, но вместо усечения она производит округление. Форматная маска по умолчанию равна «DD». Пример:
SELECT
SYSDATE
d
1,
ROUND(SYSDATE)
d2,
ROUND(SYSDATE,
"
НН
24")
d3,
ROUND
(SYSDATE
, "
DD
")
d4
,
ROUND(SYSDATE,
"MM")
d5
FROM
dual
|
26.09.2006 16:50:50 |
27.09.2006 26.09.2006 17:00:00 |
27.09.2006 01.10.2006 |
Форматные маски, допустимые для функций TRUNC и ROUND
Рассмотрим подробнее форматные маски и особенности их применения:
|
Назначение |
||
|
Первый день столетия |
||
|
YEAR, или YYYY, |
Первый день года |
|
|
или YY, или Y |
||
|
Первый день квартала |
||
|
MONTH, или MON, |
Первый день месяца |
|
|
Тот же день недели, что день текущего года |
||
|
Тот же день недели, что день текущего месяца |
||
|
DAY, или DY, или D |
Первый день недели |
|
|
НН, или НН12, или |
||
Функция TO_DATE(str[,mask [,nls_lang]])
Функция ТО_DATE преобразует строку str в дату. Преобразование ведется по маске mask, если она указана. Если маска не указана, то берется маска по умолчанию. В случае указания маски можно указать еще один параметр - язык, используемый при форматировании названий месяцев и дней. В случае ошибки анализа строки str в соответствии с заданной
маской возникает исключительная ситуация. Наиболее распространенная ошибка «ORA-01830: шаблон формата даты завершается перед преобразованием всей строки ввода». Кроме того, нередко встречается ошибка «ORA-01821: формат даты не распознан» - она возникает при указании недопустимой форматной маски. Пример:
SELECT T0_DATE("12.09.2006")
d
FROM
dual
Функция TO_CHAR(d[,mask])
Преобразует дату d в символьную строку в соответствии с заданной маской. В случае указания недопустимой маски возникает исключительная ситуация «ORA-01821: формат даты не распознан». Пример:
SELECT SYSDATE d1,
TOLCHAR(SYSDATE, "DD.MM.YY HH24:MI") d2
FROM
dual
Как вытащить дату из базы данных в удобном формате d.m.Y. (день.месяц.год) Oracle . Нам поможет функция to_char(). Благодаря ей можно преобразовать время в нужный формат. Посмотрите на пример.
SELECT to_char(current_timestamp, "DD.MM.YYYY") as date_create from DUAL
Формат времени DD.MM.YYYY (день.месяц.год). Вы можете использовать любые символы в качестве разделителей: /, :, -.
Для вывода текущего времени в формате день.месяц.год часы:минуты:секунды используйте параметры DD.MM.YYYY HH24:MI:SS.
SELECT to_char(current_timestamp, "DD.MM.YYYY HH24:MI:SS") as date_create from DUAL /* вывод: 06.10.2017 16:50:52 */
Допустим, нужно вывести начало года? У вас автоматически будет подставляться текущий год. В этом примере применил конкатенацию ||. Более подробную информацию вы найдёте на странице TO_CHAR ФУНКЦИЯ . На той странице находится большой список параметров для преобразования даты в строку.
SELECT "01.01." || to_char(current_timestamp, "YYYY") as date_create from DUAL
Просмотры: 1762, уровень: лёгкий уровень, рейтинг: 0 , дата: 2016-10-26 14:07:32
Для упрощения работы со строками имеется ряд встроенных функций, что значительно облегчает такие операции как преобразование строк к данным других типов, поиск подстроки в строке, определение длины строки и т. д. В данной статье мы рассмотрим самые распространенные функции для работы со строками.
1) Функция определения длины строки LENGTH(строка), возвращает количество символов в строке, включая концевые пробелы.
SELECT LENGTH(‘string ’) FROM DUAL вернет значение 7.
2) Функции преобразования регистров символов UPPER(строка), LOWER(строка), INITCAP(строка). Для преобразования символов к верхнему регистру используется функция UPPER().
SELECT UPPER(‘string’) FROM DUAL вернет STRING.
Если необходимо преобразовать символы строки к нижнему регистру используется функция LOWER().
SELECT LOWER(‘STrinG’) FROM DUAL вернет string.
Функция INITCAP преобразовывает каждый первый символ слова к верхнему регистру, а все остальные символы к нижнему при условии, что символ-разделитель между словами пробел.
SELECT INITCAP(‘string1 string2’) FROM DUAL вернет строку String1 String2.
3) Функции для обрезания начальных и концевых пробелов LTRIM(строка), RTRIM(строка), TRIM(строка). Соответственно первая функция обрезает все начальные пробелы строки, вторая – все концевые, а третья все начальные и концевые.
SELECT LTRIM(‘ str1’) FROM DUAL вернет строку str1,
SELECT RTRIM(‘str2 ’) FROM DUAL вернет строку str2,
SELECT TRIM(‘ str3 ’) FROM DUAL вернет строку str3.
4) Функция замены части строки другой строкой REPLACE(исходная_строка, заменяемая_подстрока, заменяющая_подстрока). Для большей ясности рассмотрим пример, в некотором текстовом поле таблицы хранится число. Причем символ-разделитель между целой и дробной частью в некоторых полях «.», а нам для дальнейшей обработки данных нужно, чтобы он во всех полях должен быть «,». Для этого воспользуемся функцией REPLACE следующим образом. REPLACE(field1, ’.’, ’,’) и все символы «.» в поле field будут заменены на символ «,».
SELECT REPLACE(‘My_string’,’_’,’@’) FROM DUAL вернет строку My@string.
5) Функции преобразования данных к другим типам данных. TO_CHAR(число) преобразует число в текст. TO_NUMBER(строка) преобразует текст в число. TO_DATE(строка, формат_даты) преобразует строку в дату определенного формата.
SELECT TO_CHAR(123) FROM DUAL вернет строку 123,
SELECT TO_NUMBER(‘12345’) FROM DUAL вернет число 12345,
SELECT TO_DATE(’01.01.2010’,’dd.mon.yyyy’) FROM DUAL вернет дату 01.JAN.2010.
6) Функция определения вхождения подстроки в строку INSTR(исходная_строка, подстрока, номер_символа). Даная функция позволяет определять номер символа в исходной строке с которого начинается искомая подстрока (если такая есть). Иначе возвращается 0. Например нам нужно определить все должности в таблице Table1, в наименовании которых встречается подстрока «менеджер». Для этого вполне подойдет следующий оператор
SELECT * FROM TABLE1 WHERE INSTR(POST, ‘менеджер’, 1) > 0.
То есть оператор SELECT выведет только те записи из таблицы TABLE1 где искомая подстрока «менеджер» будет найдена. Причем поиск будет осуществляться с первого символа. Если поиск нужно осуществлять с другой позиции, то номер символа для начала поиска указывается в третьем параметре.
SELECT INSTR(‘Small string’, ‘string’, 1) FROM DUAL вернет значение 7,
SELECT INSTR(‘Small string’, ‘String’, 1) FROM DUAL вернет значение 0.
7) Функция выделения в исходной строке подстроки SUBSTR(исходная_строка, номер_начального_символа, количество_символов). Рассмотрим такой пример, в пользовательской таблице хранится адрес в виде наименование населенного пункта, название улицы, номер дома. Причем мы точно знаем, что для наименования населенного пункта отводится строго 20 символов (если наименовании населенного пункта меньше чем 20 символов, то остальная часть заполняется пробелами), для наименования улицы 30 символов, для номера дома 3 символа. Далее нам необходимо перенести все адреса из нашей таблицы в другую и при этом все 3 компонента адреса должны быть в разных полях. Для выделения компонент адреса применим функцию SUBSTR().
SELECT SUBSTR(TABLE_1.ADDRESS, 1, 20) CITY, SUBSTR(TABLE_1.ADDRESS, 21, 30) STREET, SUBSTR(TABLE_1.ADDRESS, 52, 3) TOWN FROM TABLE_1
Конечно для переноса данных необходимо воспользоваться оператором INSERT, но для понимания работы функции SUBSTR вполне подойдет рассмотренный пример.
SELECT SUBSTR(‘My_string’, 4, 3) FROM DUAL вернет строку str.
Рассмотренные выше функции можно использовать во входных параметрах. Так если нам нужно выделить все символы, после какого-то определенного, то в функцию SUBSTR можно передать номер искомого символа из функции INSTR. Например если нужно перенести все символы из поля таблицы, которые расположены после «,» то можно использовать такую конструкцию
SELECT SUBSTR(My_string, INSTR(My_string, ‘,’, 1), LENGTH(My_string)- INSTR(My_string, ‘,’, 1)+1) FROM DUAL.
Для определения начального символа мы вызываем функцию INSTR(), которая вернет номер символа первого вхождения подстрок «,». Далее мы определяем количество символов до конца строки как разницу длины строки и номера первого вхождения подстроки.
8) Для определения кода символа используется функция ASCII(строка), которая возвращает код 1 символа строки. Например
SELECT ASCII(W) FROM DUAL вернет значение 87.
9) Обратная функция преобразования кода символа в символ CHR(число).
SELECT CHR(87) FROM DUAL вернет символ W.
Функции для работы с числами в Oracle.
В СУБД Oracle имеется ряд функций для работы с числами. К ним относятся функции возведение числа в степень POWER(), округление ROUND() и т. д.
1) Функция ABS(число) возвращает абсолютное значение аргумента.
SELECT ABS(-3) FROM DUAL вернет значение 3.
2) Функция CEIL(число) возвращает наименьшее целое, большее или равное переданному параметру.
SELECT CEIL(4.5) FROM DUAL вернет значение 5.
3) Функция FLOOR(число) возвращает наибольшее целое, меньшее или равное переданному параметру.
SELECT FLOOR(3.8) FROM DUAL вернет значение 3.
4) Функция MOD(число_1, число_2) возвращает остаток от деления первого параметра на второй.
SELECT MOD(5, 3) FROM DUAL вернет значение 2. Примечание. Если второй параметр равен 0, то функция возвращает первый параметр.
5) Функция округления ROUND(число_1, число_2). Округляет первый переданный параметр до количества разрядов, переданного во втором параметре. Если второй параметр не указан, то он принимается равным 0, то есть округление производится до целого значения. Примеры
SELECT ROUND(101.34) FROM DUAL вернет значение 101,
SELECT ROUND(100.1268, 2) FROM DUAL вернет значение 100.13
SELECT ROUND(1234000.3254, -2) FROM DUAL вернет значение 1234000,
SELECT ROUND(-100.122, 2) FROM DUAL вернет значение -100.12.
6) Функция усечения значения TRUNC(число_1, число_2). Возвращает усеченное значение первого параметра до количества десятичных разрядов, указанного во втором параметре. Примеры
SELECT TRUNC(150.58) FROM DUAL вернет значение 150
SELECT TRUNC(235.4587, 2) FROM DUAL вернет значение 235.45
SELECT TRUNC(101.23, -1) FROM DUAL вернет значение 100
7) В СУБД Oracle имеется ряд тригонометрических функций SIN(число), COS(число), TAN(число) и обратные им ACOS(число), ASIN(число), ATAN(число). Они возвращают значение соответствующей названию тригонометрической функции. Для прямых функции параметром является значение угла в радианах, а для обратных – значение функции. Примеры
SELECT COS(0.5) FROM DUAL вернет значение 0.877582561890373
SELECT SIN(0.5) FROM DUAL вернет значение 0.479425538604203
SELECT TAN(0.5) FROM DUAL вернет значение 0.546302489843791
SELECT ACOS(0.5) FROM DUAL вернет значение 1.0471975511966
SELECT ASIN(0.5) FROM DUAL вернет значение 0.523598775598299
SELECT ATAN(0.5) FROM DUAL вернет значение 0.463647609000806
8) Гиперболические функции. SINH(число),
COSH(число), TANH(число). SINH() возвращает гиперболический синус переданного параметра, COSH() возвращает гиперболический косинус переданного параметра, TANH() возвращает гиперболический тангенс переданного параметра. Примеры
SELECT COSH(0.5) FROM DUAL вернет значение 1.12762596520638
SELECT SINH(0.5) FROM DUAL вернет значение 0.521095305493747
SELECT TANH(0.5) FROM DUAL вернет значение 0.46211715726001
9) Функция возведения в степень POWER(число_1, число_2). Примеры
SELECT POWER(10, 2) FROM DUAL вернет значение 100
SELECT POWER(100, -2) FROM DUAL вернет значение 0.0001
10) Логарифмические функции. LN(число) возвращает натуральный логарифм переданного параметра, LOG(число_1, число_2) возвращает логарифм второго переданного параметра по основанию, переданному первом параметре. Причем первый параметр должен быть больше нуля и не равен 1. Примеры
SELECT LN(5) FROM DUAL вернет значение 1.6094379124341
SELECT LOG(10, 3) FROM DUAL вернет значение 0.477121254719662
11) Функция извлечения квадратного корня SQRT(число). Пример
SELECT SQRT(4) FROM DUAL вернет значение 2.
12) Функция возведение числа е в степень EXP(число). Пример
SELECT EXP(2) FROM DUAL вернет значение 7.38905609893065.
Функции для работы с датами в Oracle
На практике очень часто необходимо анализировать данные в виде дат, производить некоторые операции над ними, изменять формат. Все эти операции уже реализованы в виде встроенных функций. Рассмотрим самые основные из них.
1) ADD_MONTHS(дата, количество_месяцев) возвращает дату, отстоящую от даты, переданной в первом параметре на количество месяцев, указанном во втором параметре. Примеры
SELECT ADD_MONTHS(’01-JAN-2010’, 2) FROM DUAL вернет дату ’01.03.2010’
SELECT ADD_MONTHS(’01-JAN-2010’, -3) FROM DUAL вернет дату ’01.10.2009’
SELECT ADD_MONTHS(’30-JAN-2010’, 1) FROM DUAL вернет дату ’28.02.2010’
2) Для определения текущей даты и времени применяется функция SYSDATE. Область применения данной функции намного шире чем может показаться на первый взгляд. В первую очередь это контроль за вводом данных в БД. Во многих таблицах выделяется отдельное поля для сохранения даты последнего внесения изменений. Также очень удобно контролировать некие входные параметры для отчетов, особенно если они не должны быть больше чем текущая дата. Помимо даты данная функция возвращает еще и время с точностью до секунд. Пример
SELECT SYSDATE FROM DUAL вернет дату ‘22.05.2010 14:51:20’
3) Если необходимо определить последний день месяца, то для этого вполне подойдет функции LAST_DAY(дата). Её можно использовать для определения количества дней, оставшихся в месяце.
SELECT LAST_DAY(SYSDATE) – SYSDATE FROM DUAL.
В результате выполнения данного оператора будет выведено количество дней от текущей даты до конца месяца. Пример
SELECT LAST_DAY(’15-FEB-2010’) FROM DUAL вернет дату ’28.02.2010’.
4) Функция для определения количества месяцев между датами MONTHS_BETWEEN(дата_1, дата_2). Примеры
SELECT MONTHS_BETWEEN(’01-JUL-2009’, ’01-JAN-2010’) FROM DUAL вернет значение -6
SELECT MONTHS_BETWEEN(’01-JUL-2009’, ’10-JAN-2010’) FROM DUAL вернет значение -6.29032258064516.
Примечание. Если дни месяцев совпадают, то функция возвращает целое число, в противном случае результат будет дробным, причем количество дней в месяце будет принято 31.
5) Функция NEXT_DAY(дата, день_недели) позволяет определить следующую дату от даты, переданной в первом параметре, которая соответствует дню недели, переданном во втором параметре. Пример
SELECT NEXT_DAY(’01-JUL-2009’, ’mon’) FROM DUAL вернет дату ‘06.07.2009’, то есть следующий понедельник после 1 июля 2009 наступил 6 числа.
6) Округление даты ROUND(дата, формат). Второй параметр не обязателен, если его не указывать, то он принимается за ‘DD’, то есть округление будет произведено до ближайшего дня. Примеры
SELECT ROUND(SYSDATE) FROM DUAL вернет дату ‘23.05.2010’
SELECT ROUND(SYSDATE, MONTH) FROM DUAL вернет дату ‘01.06.2010’, округляется до ближайшего первого дня месяца.
7) Усечение даты. Функция TRUNC(дата, формат). Также как и рассмотренная выше может не иметь второго параметра. В таком случае усечение будет производиться до ближайшего дня. Примеры
SELECT TRUNC(SYSDATE) FROM DUAL вернет дату ’22.05.2010’
SELECT TRUNC(SYSDATE, ‘WW’) FROM DUAL вернет дату ’01.05.2010’
SELECT TRUNC(SYSDATE, ‘Day’) FROM DUAL вернет дату ‘16.05.2010’.
Функции преобразования данных в Oracle
Данный раздел посвящен рассмотрению преобразования данных в различные форматы. На практике довольно распространены ситуации, когда необходимо строковые величины рассматривать как числа и наоборот. Несмотря на небольшое количество функции их возможностей вполне хватает для решения очень сложных прикладных задач.
1) TO_CHAR(данные, формат). На первый взгляд синтаксис довольно прост, но за счет второго параметра можно очень точно описать в какой формат преобразовать данные. Итак в строку можно преобразовать как дату, так и числовое значение. Рассмотрим вариант преобразования даты к строке. Значения самых распространенных форматов приведены в таблице, более полная информация содержится в технической документации.
Таблица значений форматов для преобразования числа в строку.
SELECT TO_CHAR(SYSDATE, ‘D-MONTH-YY’) FROM DUAL вернет строку ‘7-MAY -10’
SELECT TO_CHAR(SYSDATE, ‘DDD-MM-YYYY’) FROM DUAL вернет строку ‘142-05-2010’
SELECT TO_CHAR(SYSDATE, ‘Q-D-MM-YYY’) FROM DUAL вернет строку ‘2-7-05-010’
SELECT TO_CHAR(1050, ‘9.99EEEE) FROM DUAL вернет строку ‘ 1.050E+03’
SELECT TO_CHAR(1400, ‘9999V999’) FROM DUAL вернет строку ‘1400000’
SELECT TO_CHAR(48, ‘RM’) FROM DUAL вернет строку ‘ XLVIII’
2) Функция преобразования строки в дату 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’.
3) Функция преобразования строки в числовое значение 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.