Oracle округлить в большую сторону
Функция SQL ROUND(результат_вычислений, n) округляет результат вычислений до n-го знака после запятой. Округление производится по правилам арифметики.
Если n - отрицательное число (−n), то округление происходит до n-го знака перед запятой. Таким образом, с помощью функции ROUND можно получить и целое число как результат округления.
Пример 1. В базе данных фирмы есть таблица Staff, содержащая данные о сотрудниках фирмы. Выведем индивидуальные размеры заработной платы сотрудников:
Результатом выполнения запроса будет следующая таблица:
Name | Salary |
Sanders | 18357.5 |
Pernal | 15430.0 |
Marenghi | 17506.8 |
Doctor | 12322.8 |
Factor | 16228.7 |
Junkers | 16232.8 |
Moonlight | 21500.6 |
Aisen | 19540.7 |
MacGregor | 15790.8 |
Для вычисления среднего размера заработной платы пишем запрос:
Получим следующий результат:
AVG(Salary) |
16990.06662326389 |
Для отчётов результат с таким числом знаков после запятой не годится. Округлим результат до второго знака после запятой с помощью функции ROUND:
Результат будет следующим:
Avg_Salary |
16990.07 |
Пример 2. Теперь округлим результат до первого знака до запятой, применяя в функции ROUND параметр минус единица:
Результат будет следующим:
Avg_Salary |
16990 |
Сместим округление ещё на один знак влево и применим в функции ROUND параметр минус 2:
Результат будет следующим:
Avg_Salary |
17000 |
Функция SQL ROUND может применяться ещё и с третьим необязательными параметром (кроме MySQL). Если этим параметром будет 1, то округление производиться не будет, просто в результате будет оставлено столько знаков после запятой, сколько указано во втором параметре.
Пример 3. Оставить в результате вычислений средней заработной платы два знака после запятой, не производя округления. Применяем функцию ROUND с тремя параметрами:
Результат будет следующим:
Avg_Salary |
16990.06 |
Функция MySQL TRUNCATE
В MySQL аналогом разновидности функции ROUND без округления результата является функция TRUNCATE. Она, как и ROUND в общем случае имеет два параметра: результат вычислений и число знаков после запятой.
Пример 4. Условие то же, что в примере 3, но в MySQL. Применяем функцию TRUNCATE:
Получим результат без округления, как в предыдущем примере:
Avg_Salary |
16990.06 |
Сводка числовых функций 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 сравнивается с другими числовыми функциями округления и усечения.
Функция ROUND используется для округления дробей до нужного знака в дробной части.
См. также функцию truncate, которая усекает числа до нужного знака.
См. также функцию CEILING, которая округляет дроби в большую сторону.
См. также функцию FLOOR, которая округляет дроби в меньшую сторону.
Тригонометрические функции
В PL/SQL поддерживаются все основные тригонометрические функции. При их использовании следует помнить, что углы задаются в радианах, а не в градусах. Преобразование радианов в градусы и наоборот выполняется по следующим формулам:
В PL/SQL нет отдельной функции для получения числа π, однако его можно получить косвенным методом:
Арккосинус числа –1 равен значению π. Конечно, поскольку это число представляет собой бесконечную десятичную дробь, вы всегда будете работать с его приближенным значением. Для получения нужной точности можно округлить результат вызова ACOS (-1) до нужного количества позиций функцией ROUND .
Примеры
Все примеры будут по этой таблице workers, если не сказано иное:
id айди | name имя | age возраст | salary зарплата |
---|---|---|---|
1 | Дима | 23 | 100.129 |
2 | Петя | 24 | 200.391 |
3 | Вася | 25 | 300.934 |
Передача параметров NLS функции TO_CHAR
По аналогии с функцией TO_NUMBER , функция TO_CHAR может получать в третьем параметре строку настроек NLS . Пример:
Таким способом можно задавать три параметра NLS : NLS_NUMERIC_CHARACTERS , NLS_CURRENCY и NLS_ISO_CURRENCY . Пример одновременного использования всех трех параметров приводился ранее, в разделе «Передача функции TO_NUMBER параметров NLS ».
Функция SQL CEILING
Функция SQL CEILING не производит округления. Она просто принимает дробное число и возвращает максимальное целое число, не меньшее принятого. Приведём примеры действия функции с различными принятыми дробными числами.
Функция с аргументом | Возвращаемое значение |
CEILING(0.38) | 1 |
CEILING(1.63) | 2 |
CEILING(−0.38) | 0 |
CEILING(−1.63) | −1 |
В случае функции CEILING некорректно говорить об округлении, поскольку она преобразует числа без учёта правил арифметики.
Неявные преобразования
Преобразования между числами и строками можно выполнить еще одним способом: просто поручите эту работу PL/SQL. Такое преобразование называется неявным, так как оно не определяется явно в программном коде. Пример неявных преобразований:
Как упоминалось в главе 7, неявные преобразования имеют ряд недостатков. Прежде всего, я считаю, что разработчик должен в полной мере контролировать свой код, а при использовании этого метода этот контроль отчасти утрачивается. Всегда лучше знать, когда и какое именно выполняется преобразование, поэтому желательно выполнять его явно. Если полагаться на неявное преобразование, то вы не сможете отследить, где и когда оно выполняется, и код станет менее эффективным. Кроме того, наличие явных преобразований делает код более понятным другим программистам.
Другой недостаток неявных преобразований заключается в том, что они могут нормально работать (по крайней мере на первый взгляд) в простых случаях, но их результат не всегда очевиден. Рассмотрим пример:
Функция CAST
Функция CAST применяется для преобразования чисел в строки, и наоборот. Синтаксис функции выглядит так:
В следующем примере функция CAST сначала используется для преобразования числа типа NUMBER в строку VARCHAR2 , а затем символы этой строки преобразуются в соответствующее числовое значение:
У функции CAST есть один недостаток: она не поддерживает маски форматирования чисел. С другой стороны, эта функция является частью стандарта ISO SQL — в отличие от функций TO_CHAR и TO_NUMBER . Если разработчику важно, чтобы программный код был полностью совместим со стандартом ANSI , используйте для преобразования чисел в строковые значения функцию CAST . В других случаях мы рекомендуем применять функции TO_CHAR и TO_NUMBER .
Поскольку PL/SQL не соответствует стандарту ISO , написать на этом языке полностью совместимый с указанным стандартом код невозможно. Таким образом, функция CAST в программах на PL/SQL становится лишней. Она востребована только в SQL-командах ( SELECT, INSERT и т. д.), если они должны быть совместимы со стандартом ANSI .
Синтаксис
Округление до целого:
Округление до определенного знака в дробной части:
Пример
В данном примере дробная зарплата округляется до целых (0 соответствует целому числу):
Числовые функции
В PL/SQL реализовано множество функций для работы с числами. Мы уже рассматривали функции преобразования TO_CHAR , TO_NUMBER, TO_BINARY_FLOAT и TO_BINARY_DOUBLE . В нескольких ближайших разделах приведены краткие описания важнейших функций. За подробными описаниями конкретных функций обращайтесь к справочнику Oracle SQL Reference.
Пример
В данном примере дробная зарплата округляется до целых при выборке:
SQL запрос выберет следующие строки:
id айди | name имя | age возраст | salary зарплата |
---|---|---|---|
1 | Дима | 23 | 100 |
2 | Петя | 24 | 200 |
3 | Вася | 25 | 301 |
Пример
В данном примере дробная зарплата округляется до двух знаков после точки:
SQL запрос выберет следующие строки:
id айди | name имя | age возраст | salary зарплата |
---|---|---|---|
1 | Дима | 23 | 100.13 |
2 | Петя | 24 | 200.39 |
3 | Вася | 25 | 300.93 |
Остерегайтесь неявных преобразований!
В разделе «Типы BINARY_FLOAT и BINARY_DOUBLE » этой главы я привел код ( binary_performance . sql ), использовавшийся для сравнения производительности BINARY_DOUBLE и NUMBER . В первой версии этого теста циклы для вычисления площади были запрограммированы следующим образом:
Я был потрясен, когда результаты вдруг показали, что вычисления с NUMBER выполняются намного быстрее вычислений с BINARY_DOUBLE . Это было совершенно непостижимо, потому что я «знал», что операции с BINARY_DOUBLE выполняются на аппаратном уровне, а следовательно, просто обязаны работать быстрее операций с NUMBER .
Потом кто-то из сотрудников Oracle Corporation указал мне на мою ошибку: цикл FOR (в приведенном виде) неявно объявляет переменную цикла PLS_INTEGER с именем bd . Область действия нового объявления bd перекрывает блок цикла и замещает мое объявление bd в формате BINARY_DOUBLE . Кроме того, я записал константу в формате 2 (вместо 2d), из-за чего она интерпретировалась как NUMBER . Таким образом, значение bd сначала неявно преобразовывалось в NUMBER , затем возводилось в квадрат, и полученное значение NUMBER неявно снова преобразовывалось в BINARY_DOUBLE для умножения на pi_bd . Неудивительно, что результаты были настолько плохи! Подобные ловушки характерны для неявных преобразований.
Какое значение будет содержать переменная a после выполнения этого кода? Это зависит от того, как PL/SQL вычисляет выражение в правой части оператора присваивания. Если он сначала преобразует строку в число, мы получим следующий результат:
С другой стороны, если PL/SQL сначала преобразует число в строку, то результат будет таким:
И какой из двух результатов будет получен? Вы знаете? Даже если знаете, вряд ли другие программисты сразу догадаются об этом, читая ваш код. В данном случае лучше записать преобразование в явном виде:
Кстати говоря, это выражение соответствует порядку обработки исходного выражения базой данных. Согласитесь, с явно выраженными преобразованиями его намного проще понять с первого взгляда.
PL/SQL реализует несколько операторов, используемых при работе с числами. Эти операторы перечислены в табл. 1 в порядке возрастания приоритетов. Операторы с более низким приоритетом выполняются до операторов с более высоким приоритетом.
Функции округления и усечения
Существуют четыре числовые функции, выполняющие округление и усечение числовых значений: CEIL, FLOOR, ROUND и TRUNC . Выбор нужной функции для конкретной ситуации может вызвать затруднения, поэтому в табл. 1 приводятся их сравнительные описания, а на рис. 5 представлены результаты вызова всех четырех функций с разными значениями.
Функция | Описание |
CEIL | Возвращает наименьшее целое число, большее либо равное заданному значению |
FLOOR | Возвращает наибольшее целое число, меньшее либо равное заданному значению |
ROUND | Выполняет округление числа. Положительное значение параметра определяет способ округления цифр, находящихся справа от запятой, а отрицательное — находящихся слева |
TRUNC | Усекает число до заданного количества десятичных знаков, отбрасывая все цифры, находящиеся справа |
Рис. 1. Функции округления и усечения
Пробелы при преобразовании чисел в символьные строки
В ходе преобразования числа в символьную строку функция TO_CHAR всегда оставляет место для знака «-», даже если число положительное.
Обратите внимание: каждая строка имеет длину семь символов, хотя для положительного числа достаточно и шести. Начальный пробел применяется для выравнивания чисел в столбцах. Но если по какой-то причине вам требуется вывести компактные числа без пробелов, это создаст проблемы.
Если для представления отрицательных чисел используются угловые скобки (в маске задан элемент PR ), положительные числа дополняются одним начальным и одним завершающим пробелом.
Если числа, преобразованные в символьные данные, не должны содержать ни начальных, ни завершающих пробелов, существует несколько решений. Одно из них основано на использовании элемента форматирования TM , определяющего «минимальное» представление числа:
Этот метод удобен, но не позволяет задавать другие элементы форматирования. Так, нельзя задать формат TM999.99, чтобы число выводилось с двумя цифрами в дробной части. Если вам нужны другие элементы форматирования или если элемент TM не поддерживается в вашей версии PL/SQL, можно воспользоваться усечением результата:
Функция LTRIM была использована для удаления начальных пробелов и сохранения двух фиксированных цифр справа от десятичной запятой. Если же знак выводится справа от числа (например, с использованием элемента форматирования MI ), можно воспользоваться функцией RTRIM . Если же при выводе используются элементы, влияющие на вывод с обеих сторон числа (например, PR ), используется функция TRIM .
Функция SQL FLOOR
Функция FLOOR также не производит округления. Её действие противоположно действию функции CEILING. Она принимает дробное число и возвращает максимальное целое число, не большее принятого. Приведём примеры действия функции с различными принятыми дробными числами.
Функция с аргументом | Возвращаемое значение |
FLOOR(0.38) | 0 |
FLOOR(1.63) | 1 |
FLOOR(−0.38) | −1 |
FLOOR(−1.63) | −2 |
Функция FLOOR, как и функция CEILING, преобразует числа без учёта правил арифметики.
В посте рассматриваются функции 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 выполняет операцию модульного деления. Ее синтаксис:
В том случае, когда при преобразовании символьной строки в число слева или справа от десятичной запятой получается больше цифр, чем допускает маска форматирования, в PL/SQL происходит ошибка. Но при преобразовании числа в символьную строку ошибка возникает только при наличии лишних цифр слева от запятой. Если маска форматирования содержит меньше цифр после десятичной запятой, чем требуется для представления числа, число округляется до указанного количества цифр.
Если же дробная часть числа не помещается в маску, происходит округление:
Цифры 5 и больше округляются в большую сторону, так что число 123,4567 округляется до 123,46, а цифры меньше 5 — в меньшую, поэтому 123,4xxx округляется до 123.
Пример
В данном примере дробная зарплата округляется до одного знака после точки:
SQL запрос выберет следующие строки:
id айди | name имя | age возраст | salary зарплата |
---|---|---|---|
1 | Дима | 23 | 100.1 |
2 | Петя | 24 | 200.4 |
3 | Вася | 25 | 300.9 |
Числовые операторы
Оператор | Операция | Приоритет |
** | Возведение в степень | 1 |
+ | Тождество | 2 |
- | Отрицание | 2 |
* | Умножение | 3 |
/ | Деление | 3 |
+ | Сложение | 4 |
– | Вычитание | 4 |
= | Равно | 5 |
Меньше чем | 5 | |
> | Больше чем | 5 |
Меньше либо равно | 5 | |
>= | Больше либо равно | 5 |
<>, !=, ~=, ^= | Не равно | 5 |
IS NULL | Проверка неопределенности | 5 |
BETWEEN | Принадлежность диапазону | 5 |
NOT | Логическое отрицание | 6 |
AND | Конъюнкция | 7 |
OR | Дизъюнкция | 8 |
Функция MySQL TRUNCATE
В MySQL аналогом разновидности функции ROUND без округления результата является функция TRUNCATE. Она, как и ROUND в общем случае имеет два параметра: результат вычислений и число знаков после запятой.
Пример 4. Условие то же, что в примере 3, но в MySQL. Применяем функцию TRUNCATE:
Получим результат без округления, как в предыдущем примере:
Avg_Salary |
16990.06 |
Читайте также: