Oracle получить остаток от деления
Функция TRUNC усекает столбец, выражение или значение к n десятичным разрядам.
Функция TRUNC работает с параметрами, подобно таковым из функции ROUND. Если второй параметр 0 или отсутствует, значение усекается до целого. Если второй параметр 2, значение является усеченным к двум десятичным разрядам. Наоборот, если второй параметр –2, значение является усеченным к двум десятичным разрядам слева. Если второй параметр –1, значение является усеченным к одному десятичному разряду слева.
Как и функция ROUND, функция TRUNC может использоваться с функциями даты.
Использование Функции MOD
Для всех сотрудников с заголовком работы "Торговый представитель" вычислить остаток от зарплаты после того, как оно делится на 5 000.
Функция MOD находит остаток от деления первого параметра на второй параметр. Пример на рисунке вычисляет остаток от зарплаты после деления ее на 5 000 для всех сотрудников, ID задания которых является SA_REP.
Отметьте : функция MOD часто используется, чтобы определить, четное значение или нечетное. Функция MOD является также хеш-функцией Oracle.
Иногда недоумение у начинающих работать с Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server вызывают результаты подобных запросов:
Одни (подозреваю, что это пользователи MySQL или Oracle) ожидают результаты типа
| a | b |
|---|---|
| 0.3333 | 1.6667 |
т.е. округления к ближайшему целому. В то время как SQL Server дает
Чтобы развеять это недоумение, скажу, что операция "/" просто обозначает целочисленное деление (а именно, дает в результате неполное частное ), если операнды являются целыми числами. Т.е. отдельного обозначения для этой операции нет, и используется символ "обычного" деления. Если же вы хотите получить десятичное число, то нужно привести хотя бы один операнд к вещественному типу явно (первый столбец) или неявно (второй столбец):
| a | b |
|---|---|
| 0.333333 | 1.666666 |
Операция получения остатка от деления в SQL Server обозначается "%":
Теперь что касается некоторых других СУБД.
PostgreSQL ведет себя аналогично SQL Server.
В MySQL для получения неполного частного используется специальный оператор DIV :
Остаток от деления можно также получить в стиле а-ля Паскаль:
Хотя будет работать и "общепринятое"
В Oracle вообще нет операции для получения неполного частного, поэтому результат деления
| a | b |
|---|---|
| .333333333333333 | 1.66666666666667 |
потребуется вручную приводить к целому типу с желаемым результатом, например, так:
Для получения остатка от деления в Oracle используется функция MOD :
PL/SQL реализует несколько операторов, используемых при работе с числами. Эти операторы перечислены в табл. 1 в порядке возрастания приоритетов. Операторы с более низким приоритетом выполняются до операторов с более высоким приоритетом.
Числовые операторы
Числовые функции
В PL/SQL реализовано множество функций для работы с числами. Мы уже рассматривали функции преобразования TO_CHAR , TO_NUMBER, TO_BINARY_FLOAT и TO_BINARY_DOUBLE . В нескольких ближайших разделах приведены краткие описания важнейших функций. За подробными описаниями конкретных функций обращайтесь к справочнику Oracle SQL Reference.
Функции округления и усечения
Существуют четыре числовые функции, выполняющие округление и усечение числовых значений: CEIL, FLOOR, ROUND и TRUNC . Выбор нужной функции для конкретной ситуации может вызвать затруднения, поэтому в табл. 1 приводятся их сравнительные описания, а на рис. 5 представлены результаты вызова всех четырех функций с разными значениями.
Рис. 1. Функции округления и усечения
Тригонометрические функции
В PL/SQL поддерживаются все основные тригонометрические функции. При их использовании следует помнить, что углы задаются в радианах, а не в градусах. Преобразование радианов в градусы и наоборот выполняется по следующим формулам:
В PL/SQL нет отдельной функции для получения числа π, однако его можно получить косвенным методом:
Арккосинус числа –1 равен значению π. Конечно, поскольку это число представляет собой бесконечную десятичную дробь, вы всегда будете работать с его приближенным значением. Для получения нужной точности можно округлить результат вызова ACOS (-1) до нужного количества позиций функцией ROUND .
Сводка числовых функций PL/SQL
В этом разделе представлены краткие описания всех встроенных функций PL/SQL. Там, где это возможно, функции перегружаются для разных числовых типов. Например:
- ABS
Функция перегружена для типов BINARY_DOUBLE, BINARY_FLOAT, NUMBER, SIMPLE_INTEGER, SIMPLE_FLOAT, SIMPLE_DOUBLE и PLS_INTEGER , так как операция определения абсолютного значения применима как к вещественным, так и к целочисленным значениям. - BITAND
Функция перегружена для типов PLS_INTEGER и INTEGER (подтип NUMBER ), так как операция AND может применяться только к целочисленным значениям. - CEIL
Функция перегружена для типов BINARY_DOUBLE, BINARY_FLOAT и NUMBER , поскольку функция CEIL не актуальна для целых чисел.
Чтобы узнать, для каких типов перегружена та или иная функция, запросите описание встроенного пакета SYS.STANDARD :
Почти все функции в следующем списке определяются во встроенном пакете SYS . STANDARD . Единственным исключением является функция BIN_TO_NUM . За полной документацией по отдельным функциям обращайтесь к справочнику Oracle SQL Reference.
Возвращает абсолютное значение числа.
Возвращает арккосинус угла n из диапазона [–1; 1]. Возвращаемое функцией значение находится в пределах от 0 до π.
Возвращает арксинус угла n из диапазона [–1; 1]. Возвращаемое функцией значение находится в пределах от −π/2 до −π/2.
Возвращает арктангенс угла n из диапазона (– ∞ ; + ∞ ). Возвращаемое функцией значение находится в пределах от −π/2 до −π/2.
Возвращает арктангенс n/m для чисел n и m из диапазона (– ∞ ; + ∞ ). Возвращаемое функцией значение находится в пределах от −π/2 до −π/2.
BIN_TO_NUM(b1, b2. bn)
Преобразует битовый вектор, представленный значениями от b1 до bn, в число. Каждое из значений вектора должно быть равно либо 0, либо 1. Например, результат вызова BIN_TO_NUM(1,1,0,0) равен 12.
Выполняет поразрядную операцию AND над битами двух положительных целых чисел. Например, вызов BITAND(12,4) дает результат 4, то есть в значении 12 (двоичное 1100) установлен 4-й бит.
Вам будет проще работать с BITAND , если вы ограничитесь положительными целыми числами. Значения типа PLS_INTEGER , особенно удобного в сочетании с BITAND , позволяют хранить значения до 230; таким образом, в вашем распоряжении 30 битов для выполнения поразрядных операций.
Возвращает наименьшее целое число, которое больше либо равно заданному значению. В табл. 1 и рис. 1 функция CEIL сравнивается с другими числовыми функциями округления и усечения.
Возвращает косинус угла n , заданного в радианах. Если угол задается в градусах, то значение следует преобразовать в радианы (см. раздел «Тригонометрические функции»).
Возвращает гиперболический косинус n . Если n — вещественное число, а i — мнимая единица, тогда связь между функциями COS и COSH выражается следующей формулой: COS (i * n) = COSH (n)
Возвращает число e в степени n , где n — аргумент функции. Число e (приблизительно равное 2,71828) является основанием натурального логарифма.
Возвращает наибольшее целое число, которое меньше или равно заданному значению. В табл. 1 и рис. 1 функция FLOOR сравнивается с другими числовыми функциями округления и усечения.
GREATEST(n1, n2. n3)
Возвращает наибольшее число во входном списке; например, результат вызова GREATEST (1,0, -1, 20) равен 20.
Возвращает наименьшее число во входном списке; например, результат вызова LEAST (1, 0, –1, 20) равен −1.
Возвращает остаток от деления n на m . Остаток вычисляется по формуле, эквивалентной n-(m*FLOOR(n/m)) при совпадении знаков n и m или n-(m*CEIL(n/m)) при различающихся знаках. Например, результат вызова MOD(10, 2.8) равен 1.6. Если аргумент m равен 0, возвращается значение n . С помощью функции MOD можно быстро проверить число на четность или нечетность:
Возвращает m , если n является NaN («не числом»); в противном случае возвращается n . Возвращаемое значение относится к числовому типу аргумента, обладающему наибольшим приоритетом в следующем порядке: BINARY_DOUBLE, BINARY_FLOAT или NUMBER .
Возводит n в степень m . Если значение n отрицательно, то аргумент m должен быть целым числом. В следующем примере функция POWER используется для вычисления диапазона допустимых значений переменной типа PLS_INTEGER (от −231 −1 до 231 −1) :
Возвращает «псевдоостаток» от деления n на m. Значение вычисляется по следующей формуле:
Например, результат вызова REMAINDER(10, 2.8) равен −1.2. Сравните с функцией MOD .
Возвращает значение n , округленное до ближайшего целого. Пример:
Возвращает значение n , округленное до m разрядов. Значение m может быть отрицательным: в этом случае функция ROUND отсчитывает позиции округления влево, а не вправо от десятичной запятой. Примеры:
В табл. 1 и рис. 1 функция ROUND сравнивается с другими числовыми функциями округления и усечения.
Возвращает −1, 0 или +1, если значение n меньше нуля, равно нулю или больше нуля соответственно.
Возвращает синус угла n , заданного в радианах. Если угол задается в градусах, значение следует преобразовать в радианы (см. раздел «Тригонометрические функции»).
Возвращает гиперболический синус n . Если n — вещественное число, а i — мнимая единица, тогда связь между функциями SIN и SINH выражается следующей формулой:
Возвращает тангенс угла n , заданного в радианах. Если угол задается в градусах, значение следует преобразовать в радианы (см. раздел «Тригонометрические функции»).
Возвращает гиперболический тангенс n . Если n — вещественное число, а i — мнимая единица, тогда связь между функциями TAN и TANH выражается следующей формулой:
Усекает значение n до целого числа. Например, результат вызова TRUNC(10.51) равен 10.
Усекает значение n до m разрядов. Например, результат вызова TRUNC(10.789, 2) равен 10.78. Значение m может быть отрицательным: в этом случае функция TRUNC отсчитывает позиции усечения влево, а не вправо от десятичной запятой. Так, вызов TRUNC(1264, -2) дает значение 1200.
В табл. 1 и рис. 1 функция CEIL сравнивается с другими числовыми функциями округления и усечения.
PL/SQL реализует несколько операторов, используемых при работе с числами. Эти операторы перечислены в табл. 1 в порядке возрастания приоритетов. Операторы с более низким приоритетом выполняются до операторов с более высоким приоритетом.
Числовые операторы
| Оператор | Операция | Приоритет |
| ** | Возведение в степень | 1 |
| + | Тождество | 2 |
| - | Отрицание | 2 |
| * | Умножение | 3 |
| / | Деление | 3 |
| + | Сложение | 4 |
| – | Вычитание | 4 |
| = | Равно | 5 |
| < | Меньше чем | 5 |
| > | Больше чем | 5 |
| <= | Меньше либо равно | 5 |
| >= | Больше либо равно | 5 |
| <>, !=, |
Числовые функции
В PL/SQL реализовано множество функций для работы с числами. Мы уже рассматривали функции преобразования TO_CHAR , TO_NUMBER, TO_BINARY_FLOAT и TO_BINARY_DOUBLE . В нескольких ближайших разделах приведены краткие описания важнейших функций. За подробными описаниями конкретных функций обращайтесь к справочнику Oracle SQL Reference.
Функции округления и усечения
Существуют четыре числовые функции, выполняющие округление и усечение числовых значений: CEIL, FLOOR, ROUND и TRUNC . Выбор нужной функции для конкретной ситуации может вызвать затруднения, поэтому в табл. 1 приводятся их сравнительные описания, а на рис. 5 представлены результаты вызова всех четырех функций с разными значениями.
| Функция | Описание |
| CEIL | Возвращает наименьшее целое число, большее либо равное заданному значению |
| FLOOR | Возвращает наибольшее целое число, меньшее либо равное заданному значению |
| ROUND | Выполняет округление числа. Положительное значение параметра определяет способ округления цифр, находящихся справа от запятой, а отрицательное — находящихся слева |
| TRUNC | Усекает число до заданного количества десятичных знаков, отбрасывая все цифры, находящиеся справа |
Рис. 1. Функции округления и усечения
Тригонометрические функции
В PL/SQL поддерживаются все основные тригонометрические функции. При их использовании следует помнить, что углы задаются в радианах, а не в градусах. Преобразование радианов в градусы и наоборот выполняется по следующим формулам:
В PL/SQL нет отдельной функции для получения числа π, однако его можно получить косвенным методом:
Арккосинус числа –1 равен значению π. Конечно, поскольку это число представляет собой бесконечную десятичную дробь, вы всегда будете работать с его приближенным значением. Для получения нужной точности можно округлить результат вызова ACOS (-1) до нужного количества позиций функцией ROUND .
Сводка числовых функций PL/SQL
В этом разделе представлены краткие описания всех встроенных функций PL/SQL. Там, где это возможно, функции перегружаются для разных числовых типов. Например:
- ABS
Функция перегружена для типов BINARY_DOUBLE, BINARY_FLOAT, NUMBER, SIMPLE_INTEGER, SIMPLE_FLOAT, SIMPLE_DOUBLE и PLS_INTEGER , так как операция определения абсолютного значения применима как к вещественным, так и к целочисленным значениям. - BITAND
Функция перегружена для типов PLS_INTEGER и INTEGER (подтип NUMBER ), так как операция AND может применяться только к целочисленным значениям. - CEIL
Функция перегружена для типов BINARY_DOUBLE, BINARY_FLOAT и NUMBER , поскольку функция CEIL не актуальна для целых чисел.
Чтобы узнать, для каких типов перегружена та или иная функция, запросите описание встроенного пакета SYS.STANDARD :
Почти все функции в следующем списке определяются во встроенном пакете SYS . STANDARD . Единственным исключением является функция BIN_TO_NUM . За полной документацией по отдельным функциям обращайтесь к справочнику Oracle SQL Reference.
Возвращает абсолютное значение числа.
Возвращает арккосинус угла n из диапазона [–1; 1]. Возвращаемое функцией значение находится в пределах от 0 до π.
Возвращает арксинус угла n из диапазона [–1; 1]. Возвращаемое функцией значение находится в пределах от −π/2 до −π/2.
Возвращает арктангенс угла n из диапазона (– ∞ ; + ∞ ). Возвращаемое функцией значение находится в пределах от −π/2 до −π/2.
Возвращает арктангенс n/m для чисел n и m из диапазона (– ∞ ; + ∞ ). Возвращаемое функцией значение находится в пределах от −π/2 до −π/2.
BIN_TO_NUM(b1, b2. bn)
Преобразует битовый вектор, представленный значениями от b1 до bn, в число. Каждое из значений вектора должно быть равно либо 0, либо 1. Например, результат вызова BIN_TO_NUM(1,1,0,0) равен 12.
Выполняет поразрядную операцию AND над битами двух положительных целых чисел. Например, вызов BITAND(12,4) дает результат 4, то есть в значении 12 (двоичное 1100) установлен 4-й бит.
Вам будет проще работать с BITAND , если вы ограничитесь положительными целыми числами. Значения типа PLS_INTEGER , особенно удобного в сочетании с BITAND , позволяют хранить значения до 230; таким образом, в вашем распоряжении 30 битов для выполнения поразрядных операций.
Возвращает наименьшее целое число, которое больше либо равно заданному значению. В табл. 1 и рис. 1 функция CEIL сравнивается с другими числовыми функциями округления и усечения.
Возвращает косинус угла n , заданного в радианах. Если угол задается в градусах, то значение следует преобразовать в радианы (см. раздел «Тригонометрические функции»).
Возвращает гиперболический косинус n . Если n — вещественное число, а i — мнимая единица, тогда связь между функциями COS и COSH выражается следующей формулой: COS (i * n) = COSH (n)
Возвращает число e в степени n , где n — аргумент функции. Число e (приблизительно равное 2,71828) является основанием натурального логарифма.
Возвращает наибольшее целое число, которое меньше или равно заданному значению. В табл. 1 и рис. 1 функция FLOOR сравнивается с другими числовыми функциями округления и усечения.
GREATEST(n1, n2. n3)
Возвращает наибольшее число во входном списке; например, результат вызова GREATEST (1,0, -1, 20) равен 20.
Возвращает наименьшее число во входном списке; например, результат вызова LEAST (1, 0, –1, 20) равен −1.
Возвращает остаток от деления n на m . Остаток вычисляется по формуле, эквивалентной n-(m*FLOOR(n/m)) при совпадении знаков n и m или n-(m*CEIL(n/m)) при различающихся знаках. Например, результат вызова MOD(10, 2.8) равен 1.6. Если аргумент m равен 0, возвращается значение n . С помощью функции MOD можно быстро проверить число на четность или нечетность:
Возвращает m , если n является NaN («не числом»); в противном случае возвращается n . Возвращаемое значение относится к числовому типу аргумента, обладающему наибольшим приоритетом в следующем порядке: BINARY_DOUBLE, BINARY_FLOAT или NUMBER .
Возводит n в степень m . Если значение n отрицательно, то аргумент m должен быть целым числом. В следующем примере функция POWER используется для вычисления диапазона допустимых значений переменной типа PLS_INTEGER (от −231 −1 до 231 −1) :
Возвращает «псевдоостаток» от деления n на m. Значение вычисляется по следующей формуле:
Например, результат вызова REMAINDER(10, 2.8) равен −1.2. Сравните с функцией MOD .
Возвращает значение n , округленное до ближайшего целого. Пример:
Возвращает значение n , округленное до m разрядов. Значение m может быть отрицательным: в этом случае функция ROUND отсчитывает позиции округления влево, а не вправо от десятичной запятой. Примеры:
В табл. 1 и рис. 1 функция ROUND сравнивается с другими числовыми функциями округления и усечения.
Возвращает −1, 0 или +1, если значение n меньше нуля, равно нулю или больше нуля соответственно.
Возвращает синус угла n , заданного в радианах. Если угол задается в градусах, значение следует преобразовать в радианы (см. раздел «Тригонометрические функции»).
Возвращает гиперболический синус n . Если n — вещественное число, а i — мнимая единица, тогда связь между функциями SIN и SINH выражается следующей формулой:
Возвращает тангенс угла n , заданного в радианах. Если угол задается в градусах, значение следует преобразовать в радианы (см. раздел «Тригонометрические функции»).
Возвращает гиперболический тангенс n . Если n — вещественное число, а i — мнимая единица, тогда связь между функциями TAN и TANH выражается следующей формулой:
Усекает значение n до целого числа. Например, результат вызова TRUNC(10.51) равен 10.
Усекает значение n до m разрядов. Например, результат вызова TRUNC(10.789, 2) равен 10.78. Значение m может быть отрицательным: в этом случае функция TRUNC отсчитывает позиции усечения влево, а не вправо от десятичной запятой. Так, вызов TRUNC(1264, -2) дает значение 1200.
В табл. 1 и рис. 1 функция CEIL сравнивается с другими числовыми функциями округления и усечения.
Для упрощения работы со строками имеется ряд встроенных функций, что значительно облегчает такие операции как преобразование строк к данным других типов, поиск подстроки в строке, определение длины строки и т. д. В данной статье мы рассмотрим самые распространенные функции для работы со строками.
1) Функция определения длины строки LENGTH(строка), возвращает количество символов в строке, включая концевые пробелы.
2) Функции преобразования регистров символов UPPER(строка), LOWER(строка), INITCAP(строка). Для преобразования символов к верхнему регистру используется функция UPPER().
Если необходимо преобразовать символы строки к нижнему регистру используется функция LOWER().
Функция INITCAP преобразовывает каждый первый символ слова к верхнему регистру, а все остальные символы к нижнему при условии, что символ-разделитель между словами пробел.
вернет строку 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 выведет только те записи из таблицы 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().
Конечно для переноса данных необходимо воспользоваться оператором 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 числа.
| Значение параметра | День недели |
|---|---|
| mon | Понедельник |
| tue | Вторник |
| wed | Среда |
| thu | Четверг |
| fri | Пятница |
| sat | Суббота |
| sun | воскресенье |
6) Округление даты ROUND(дата, формат). Второй параметр не обязателен, если его не указывать, то он принимается за ‘DD’, то есть округление будет произведено до ближайшего дня. Примеры
SELECT ROUND(SYSDATE) FROM DUAL вернет дату ‘23.05.2010’
SELECT ROUND(SYSDATE, MONTH) FROM DUAL вернет дату ‘01.06.2010’, округляется до ближайшего первого дня месяца.
| Формат | Единица округления |
|---|---|
| СС, SCC | Век |
| SYYYY, YYYY, YEAR | Год |
| Q | Квартал |
| MM, MONTH | Месяц |
| WW | Тот же день недели, что и первый день года |
| W | Тот же день недели, что и первый день месяца |
| DD, J | День |
| Day, DY | Первый день недели |
| HH, HH12, HH24 | Час |
| MI | Минута |
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(данные, формат). На первый взгляд синтаксис довольно прост, но за счет второго параметра можно очень точно описать в какой формат преобразовать данные. Итак в строку можно преобразовать как дату, так и числовое значение. Рассмотрим вариант преобразования даты к строке. Значения самых распространенных форматов приведены в таблице, более полная информация содержится в технической документации.
| Формат | Описание формата |
|---|---|
| D | День недели |
| DD | День месяца |
| DDD | День года |
| MM | Номер месяца |
| MON | Сокращенное название месяца |
| MONTH | Полное название месяца |
| Q | Квартал |
| YY, YYY, YYYY | Год |
| HH, HH12, HH24 | Час |
| MI | Минут |
| SS | Секунда |
Таблица значений форматов для преобразования числа в строку.
| Формат | Описание формата |
|---|---|
| 99D9 | Указание позиции разделителя десятичной точки. Число девяток соответствует максимальному количеству цифр |
| 999G99 | Указание позиции группового разделителя |
| 99,999 | Возвращает запятую в указанной позиции |
| 99.999 | Возвращает точку в указанной позиции |
| 99V9999 | Возвращает значение умноженное на 10 в степени n, где n число девяток после V. |
| 0999 | Возвращает ведущие нули, а не пробелы |
| 9990 | Возвращает конечные нули, а не пробелы |
| 9.99EEEE | Возвращает число в экспоненциальной форме |
| RM | Возвращает число в римской системе исчисления |
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.
Сразу отмечу, что в случае ошибки все математические функции возвращают NULL. Итак, перейдем к делу.
Знаки числа
Начнем с функции ABS (x), которая возвращает абсолютное значение переданного ей числа x. Пример:
SELECT ABS (-5)
Результат: 5
SELECT ABS (5)
Результат: 5
Знак числа можно определить с помощью функции SIGN (x) . Функция возвращает -1 если x отрицательное число, 1 если положительное и 0 если x является нулем. Пример:
SELECT SIGN (-5)
Результат: -1
SELECT SIGN (0)
Результат: 0
SELECT SIGN (5)
Результат: 1
Округление чисел
Начнем с функции FLOOR (x). Возвращает ближайшее целое число не превышающее x. Пример:
SELECT FLOOR (5.5)
Результат: 5
SELECT FLOOR (5.2)
Результат: 5
SELECT FLOOR (5.7)
Результат: 5
SELECT FLOOR (5)
Результат: 5
SELECT FLOOR (-5.2)
Результат: -6
Как видно, при задании любого положительного дробного числа от 5 до 6 возвращается целое число 5. А при передаче параметра -5.2 вернется -6, так как -6 меньше -5.2.
Обратное действие выполняет функция CEILING (x) . Она возвращает ближайшее целое число, которое превышает переданный параметр x. Пример:
SELECT CEILING (5.5)
Результат: 6
SELECT CEILING (5.2);
Результат: 6
SELECT CEILING (5.7)
Результат: 6
SELECT CEILING (5)
Результат: 5
SELECT CEILING (-5.5)
Результат: -5
Для округления дробного числа до ближайшего целого используется функция ROUND (x, d). Функция может принимать один или два параметра. Первый параметр x — число, которое необходимо округлить. Второй параметр d — целое число, определяющее разряд до которого необходимо округлить x. В случае если передан один параметр x, то происходит просто округление до ближайшего целого. Например:
SELECT ROUND (50.45)
Результат: 50
SELECT ROUND (50.76)
Результат: 51
При значении аргумента, равного середине между двумя целыми числами, результат будет зависеть от конкретной СУБД, где используется язык SQL.
Если в функцию передан второй параметр, то после запятой останется столько знаков сколько указано в параметре. Например, если после запятой необходимо оставить один символ, то в качестве второго параметра указываем цифру 1:
SELECT ROUND (50.76, 1)
Результат: 50,8
Существует возможность округлять число до любого разряда до запятой, для этого просто вторым параметром указываем отрицательное число. Например:
SELECT ROUND (251.55, -1)
Результат: 250
В SQL еще имеется функция, которая не округляет, а отсекает десятичную часть дробного числа. Функция TRUNCATE (x, y) возвращает число x, усеченное до y десятичных знаков:
SELECT TRUNCATE (1.999, 1)
SELECT TRUNCATE (1.999, 0)
Функции выполняющие сложные математические операции
Начнем с самого простого. Функция MOD (x, y) возвращает остаток от деления x на y. Например:
SELECT MOD (10, 3)
Результат: 1
Следующая функция EXP (x), которая возвращает значение e (Число Эйлера) возведенное в степень x. Или научным языком, возвращает экспоненту числа. Пример:
SELECT EXP (3)
Результат: 20.085536923187668
Далее рассмотрим функцию LOG (x), которая возвращает натуральный логарифм числа x. Пример:
SELECT LOG (10)
Результат: 2.302585092994046
Для получения логарифма числа x, для произвольной основы логарифма y можно пользоваться формулой LOG (x)/LOG (y). Например:
SELECT LOG (8)/LOG (2)
Результат: 3
Для получения десятичного логарифма числа x существует функция LOG10 (x). Пример:
SELECT LOG10 (100)
Результат: 2
Для возведения в степень в языке SQL есть целых две функции: POW (x, y) и POWER (x, y). Возвращают число x возведенное в степень y. Пример:
SELECT POW (2, 3)
Результат: 8
SELECT POWER (3, 2)
Результат: 9
А функция SQRT (x) вычисляет квадратный корень числа x. Пример:
SELECT SQRT (16)
Результат: 4
Чтобы использовать в своих вычисления число «пи» в SQL есть функция PI (), которая возвращает значение этого числа:
SELECT PI ()
Результат: 3.141593
Тригонометрические функции в языке SQL
Кратенько пройдемся по тригонометрическим функциям:
Везде x задается в радианах. Примеры:
SELECT COS (PI ())
Результат: -1
SELECT SIN (PI ()/2)
Результат: 1
SELECT TAN (PI ()/4)
Результат: 1
SELECT COT (PI ()/3)
Результат: 0.577350269189626
Функции ACOS (x) и ASIN (x) вычисляют соответственно арккосинус и арксинус числа x, т.е. функции возвращают величину, косинус или синус которой равен x. При этом если значение x не находится в диапазоне от -1 до 1, то функции возвращают NULL. Например:
SELECT ASIN (-1)
Результат: -1.5707963267949
SELECT ACOS (-1)
Результат: 3.14159265358979
SELECT ACOS (1.1)
Результат: NULL
Функция ATAN (x) вычисляет арктангенс числа x, т.е. возвращает величину, тангенс которой равен x. Пример:
SELECT ATAN (3);
Результат: 1.24904577239825
Для преобразования радиан в градусы и обратно используются функции DEGREES (x) и RADIANS (x) соответственно:
SELECT DEGREES (PI ())
Результат: 180
SELECT RADIANS (180)
Результат: 3.14
Случайные числа
Функция RAND (x) генерирует случайное значение в диапазоне от 0 до 1. Если указан аргумент x, то он используется как начальное значение этой величины. Пример:
SELECT RAND ();
Результат:0.472241415009636
SELECT RAND (0.5);
Результат: 0.943597390424144
На этом все. Вроде рассмотрел все часто используемые в SQL математические функции. Возможно вам будет интересно узнать и о функциях обработки строк в SQL.
Читайте также: