Oracle получить остаток от деления
В посте рассматриваются функции ROUND, TRUNC и MOD относящиеся к функциям по работе с числовыми данными.
Существенным отличием между числовыми и другими функциями является то, что они принимают и возвращают только числовые данные. Oracle предоставляет числовые функции для решения тригонометрических, степенных и логарифмических задач. Ниже рассматриваются три однострочные числовые функции – ROUND, TRUNC и MOD:
Функция ROUND выполняет операцию округления числового значения на основе указанной десятичной точности. Возвращаемое значение округляется в большую или меньшую сторону в зависимости от числового значения значащей цифры в позиции, задаваемой десятичной точностью. Если заданная десятичная точность равна n, то значимая для округления позиция является: (n + 1)-я относительно десятичной запятой (точки) вправо для n>=0 и (0-n)-я относительно десятичной запятой (точки) влево для n
ROUND (числовое значение, (десятичная точность)),
Описания десятичной точности
Десятичная точность | Десятичная позиция |
-4 | десятки тысяч (n*10000) |
-3 | тысячи (n * 1000) |
-2 | сотни (n * 100) |
-1 | десятки (n * 10) |
0 | единицы (n * 1) |
1 | десятых (n ÷ 10) |
2 | сотых (n ÷ 100) |
3 | тысячных (n ÷ 1000) |
4 | десятитысячных (n ÷ 10000) |
Примеры использования функции ROUND:
Если для примера выше опустить значение десятичной точности 0, то получается идентичный результат. При отсутствии десятичной точности, функция ROUND обрабатывает целую часть числа, как это было бы с десятичной точностью равной 0:
Функция TRUNC выполняет операцию усечения числового значения на основе указанной десятичной точности. Числовое усечение отличается от округления тем, что результирующее значение отбрасывает числовые знаки с позиции (n+1)-й вправо от десятичной запятой (точки) указанной десятичной точности n, если десятичная точность положительна или равна нулю. Если заданная десятичная точность n отрицательна, в исходном числе проставляются значащие нули до (0-n)-й десятичной позиции включительно влево от десятичной запятой (точки). Синтаксис функции:
TRUNC (числовое значение, (десятичная точность)).
Примеры использования функции TRUNC:
При отсутствии десятичной точности, функция TRUNC обрабатывает целую часть числа, как это было бы с десятичной точностью равной нулю:
Функция MOD возвращает числовой остаток от операции деления. Функция MOD выполняет операцию модульного деления. Ее синтаксис:
MOD returns the remainder of n2 divided by n1 . Returns n2 if n1 is 0.
This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.
Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion and "Numeric Precedence" for information on numeric precedence
The following example returns the remainder of 11 divided by 4:
This function behaves differently from the classical mathematical modulus function when m is negative. The classical modulus can be expressed using the MOD function with this formula:
The following table illustrates the difference between the MOD function and the classical modulus:
m | n | MOD(m,n) | Classical Modulus |
---|---|---|---|
11 | 4 | 3 | 3 |
11 | -4 | 3 | -1 |
-11 | 4 | -3 | 1 |
-11 | -4 | -3 | -3 |
FLOOR and REMAINDER, which is similar to MOD , but uses ROUND in its formula instead of FLOOR
Читает индекс целиком (все строки) в порядке, представленном индексом. В зависимости от различной системной статистики СУБД может выполнять эту операцию, если нужны все строки в порядке индекса, например, из-за соответствующего предложения ORDER BY. Вместо этого оптимизатор может также использовать операцию Index Fast Full Scan и выполнить дополнительную операцию сортировки.
Hash Group By
Группирует результат, используя хеш-таблицу. Эта операция требует больших объемов памяти для материализации промежуточного набора записей. Вывод не упорядочен каким-либо значимым образом.
Числовые операторы
Оператор | Операция | Приоритет |
** | Возведение в степень | 1 |
+ | Тождество | 2 |
- | Отрицание | 2 |
* | Умножение | 3 |
/ | Деление | 3 |
+ | Сложение | 4 |
– | Вычитание | 4 |
= | Равно | 5 |
Меньше чем | 5 | |
> | Больше чем | 5 |
Меньше либо равно | 5 | |
>= | Больше либо равно | 5 |
<>, !=, ~=, ^= | Не равно | 5 |
IS NULL | Проверка неопределенности | 5 |
BETWEEN | Принадлежность диапазону | 5 |
NOT | Логическое отрицание | 6 |
AND | Конъюнкция | 7 |
OR | Дизъюнкция | 8 |
Merge Join
Соединение слиянием объединяет два отсортированных списка. Обе стороны объединения должны быть предварительно отсортированы.
Sort Aggregate
Вычисляет суммарные итоги с использованием агрегатных функций SUM, COUNT, MIN, MAX, AVG и пр.
Числовые функции
В PL/SQL реализовано множество функций для работы с числами. Мы уже рассматривали функции преобразования TO_CHAR , TO_NUMBER, TO_BINARY_FLOAT и TO_BINARY_DOUBLE . В нескольких ближайших разделах приведены краткие описания важнейших функций. За подробными описаниями конкретных функций обращайтесь к справочнику Oracle SQL Reference.
Load As Select
Прямая загрузка с использованием оператора SELECT в качестве источника.
Table Access By Index ROWID
Извлекает строку из таблицы, используя ROWID, полученный из предыдущего поиска по индексу.
Hash Join
Хеш-соединение загружает записи-кандидаты с одной стороны соединения в хеш-таблицу, которая затем проверяется для каждой строки с другой стороны соединения. Операция используется всегда, когда невозможно применить другие виды соединения: если соединяемые наборы данных достаточно велики и/или наборы данных не упорядочены по столбцам соединения.
Sort Group By Nosort
Агрегирует предварительно отсортированный набор записей в соответствии с предложением GROUP BY. Эта операция не буферизует промежуточный результат.
Sort Order By
Сортирует результат в соответствии с предложением ORDER BY. Эта операция требует больших объемов памяти для материализации промежуточного результата.
Table Access Full
Полное сканирование таблицы. Читает всю таблицу (все строки и столбцы), в порядке, хранящемся на диске. Хотя многоблочные операции чтения значительно повышают скорость сканирования полной таблицы, это все еще одна из самых дорогих операций. Помимо высоких затрат времени ввода-вывода, полное сканирование таблицы должно проверять все строки таблицы, что также занимает значительное количество процессорного времени.
Union-All
Выполняет операцию объединения всех записей между двумя таблицами. Дублирующиеся строки не удаляются.
Intersection
Выполняет операцию пересечения между двумя источниками.
Тригонометрические функции
В PL/SQL поддерживаются все основные тригонометрические функции. При их использовании следует помнить, что углы задаются в радианах, а не в градусах. Преобразование радианов в градусы и наоборот выполняется по следующим формулам:
В PL/SQL нет отдельной функции для получения числа π, однако его можно получить косвенным методом:
Арккосинус числа –1 равен значению π. Конечно, поскольку это число представляет собой бесконечную десятичную дробь, вы всегда будете работать с его приближенным значением. Для получения нужной точности можно округлить результат вызова ACOS (-1) до нужного количества позиций функцией ROUND .
Count Stopkey
Прерывает выполение операций, когда было выбрано нужное количество строк.
Sort Join
Сортирует набор записей в столбце соединения. Используется в сочетании с операцией Merge Join для выполнения сортировки соединением слияния.
Сводка числовых функций 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 сравнивается с другими числовыми функциями округления и усечения.
Выражения в SQL используются для выполнения операций над значениями, извлекаемыми из базы данных или используемыми при поиске в базе данных. Например, в следующем запросе вычисляется процентное соотношение объема и плана продаж для каждого офиса.
А этот запрос возвращает список офисов, объем продаж которых превышает план на $50000 или больше.
В соответствии co стандартом ANSI/ISO в выражениях можно использовать четыре арифметические операции: сложение ( х+Y ), вычитание ( х-Y ), умножение ( x*y ) и деление ( х/Y ). Для формирования сложных выражений можно использовать скобки.
Строго говоря, в приведенном выше выражении скобки не требуются, поскольку в соответствии со стандартом ANSI/ISO умножение и деление имеют более высокий приоритет, чем сложение и вычитание. Однако вы всегда должны использовать скобки, чтобы сделать ваше выражение однозначным. Дело в том, что различные диалекты SQL могут использовать разные правила. Скобки, кроме того, повышают удобочитаемость инструкций и облегчают сопровождение SQL-кода.
В стандарте ANSI/ISO определено также, что преобразование целых чисел в десятичные и десятичных чисел в числа с плавающей точкой должно происходить автоматически. Таким образом, в одном выражении можно использовать числовые данные разных типов.
Во многих реализациях SQL допускается выполнение операций над датами и строками. Стандарт SQL определяет оператор конкатенации строк, записываемый в виде двух вертикальных черточек ( || ), который поддерживается в большинстве реализаций (важным исключением является SQL Server, в которой для этой цели применяется оператор плюс ( + )). Если в двух столбцах с именами FIRST_NAME и LAST_NAME содержатся значения "Jim" и "Jackson", выражение DB2:
вернет строку " Mr./Mrs. Jim Jackson ". Как уже упоминалось, DB2 и многие другие реализации поддерживают также операции сложения и вычитания значений типа date , time и timestamp , когда эти операции имеют смысл. Эта возможность включена и в стандарт SQL.
Index Fast Full Scan
Читает индекс целиком (все строки) в порядке, хранящемся на диске. Эта операция обычно выполняется вместо полного сканирования таблицы, если в индексе доступны все необходимые столбцы. Подобно операции TABLE ACCESS FULL, INDEX FAST FULL SCAN может извлечь выгоду из многоблочных операций чтения.
Функции округления и усечения
Существуют четыре числовые функции, выполняющие округление и усечение числовых значений: CEIL, FLOOR, ROUND и TRUNC . Выбор нужной функции для конкретной ситуации может вызвать затруднения, поэтому в табл. 1 приводятся их сравнительные описания, а на рис. 5 представлены результаты вызова всех четырех функций с разными значениями.
Функция | Описание |
CEIL | Возвращает наименьшее целое число, большее либо равное заданному значению |
FLOOR | Возвращает наибольшее целое число, меньшее либо равное заданному значению |
ROUND | Выполняет округление числа. Положительное значение параметра определяет способ округления цифр, находящихся справа от запятой, а отрицательное — находящихся слева |
TRUNC | Усекает число до заданного количества десятичных знаков, отбрасывая все цифры, находящиеся справа |
Рис. 1. Функции округления и усечения
Nested Loops
Соединение вложенными циклами объединяет две таблицы, выбирая результат из одной таблицы и запрашивая другую таблицу для каждой строки из первой. Встречается очень часто. Выполняет довольно эффективное соединение относительно небольших наборов данных. Соединение вложенными циклами не требует сортировки входных данных.
Filter
Применяет фильтр к набору строк.
Создает промежуточное представление данных.
Hash Unique
Более эффективная реализация алгоритма сортировки и устранения дупликатов с использованием хэш-таблицы. Заменяет операцию Sort Unique в определенных обстоятельствах.
Sort Group By
Сортирует набор записей по столбцам GROUP BY и агрегирует отсортированный результат на втором этапе. Эта операция требует больших объемов памяти для материализации промежуточного результата.
Temp Table Generation/Transformation
Создает/преобразует временную таблицу. Используется в специфичных для Oracle преобразованиях типа Star.
PL/SQL реализует несколько операторов, используемых при работе с числами. Эти операторы перечислены в табл. 1 в порядке возрастания приоритетов. Операторы с более низким приоритетом выполняются до операторов с более высоким приоритетом.
Sort Unique
Сортирует строки и устраняет дупликаты.
Читайте также: