Oracle получить дробную часть числа
Обновлено: 06.07.2024
Для преобразования числа в строку с заданным форматом используют функцию TO_CHAR :
Формат числа задается кодами форматирования
| Код | Описание |
|---|---|
| 9 | позиция цифры (может отсутствовать, если цифра незначащая) |
| 0 | позиция цифры (присутствует всегда, даже если цифра незначащая) |
| . (точка) | десятичная точка |
| , (запятая) | разделитель групп (тысяч) |
| PR | отрицательное значение в угловых скобках |
| S | знак, добавляемый к числу (с учётом локали) |
| L | символ денежной единицы (с учётом локали) |
| D | разделитель целой и дробной части числа (с учётом локали) |
| G | разделитель групп (с учётом локали) |
| MI | знак минус в заданной позиции (если число < 0) |
| PL | знак плюс в заданной позиции (если число > 0) |
| SG | знак плюс или минус в заданной позиции |
| RN | число римскими цифрами (в диапазоне от 1 до 3999) |
| TH или th | окончание порядкового числительного |
| V | сдвиг на заданное количество цифр (см. замечания) |
| EEEE | экспоненциальная запись числа |
Из этого многообразия обычно используют только
| Код | Описание |
|---|---|
| 9 | позиция цифры (может отсутствовать, если цифра незначащая) |
| 0 | позиция цифры (присутствует всегда, даже если цифра незначащая) |
| D | разделитель целой и дробной части числа (с учётом локали) |
| G | разделитель групп (с учётом локали) |
Пройдемся по ним.
9 - позиция цифры. Может отсутствовать, если цифра незначащая.
Если на позиции 9-ки в числе есть значащая цифра, то она отображается, если нет, то не отображается.
На примерах будет понятнее.
Если дробную часть не указали в формате, то она не отображается:
Дробная часть в формате начинается после точки . или символа D :
Мы указали одну 9-ку после запятой, поэтому в отформатированном числе видем после запятой только десятки.
Интересный момент: при форматировании числа цифры, выходящие за формат, не отбрасываются. Число сначала округляется до указанной в формате точности:
Можно было ожидать, что в результате получим 12.45. Но так как на позиции тысячных стоит 7, то после округления получаем 12.46
0 - позиция цифры. Отображается всегда.
Если в формате числа встретился 0, то цифра всегда отображается:
D - разделитель целой и дробной части
Разделитель целой и дробной части нужен для указания, сколько знаков после запятой нужно отобразить:
G - разделитель групп
На месте G в формате числа вставляется символ разделителя групп. Обычно его используют для отделения тысяч, миллионов, миллиардов.
Но ни что не мешает использовать его в произвольном месте:
Если число не входит в формат
Для упрощения работы со строками имеется ряд встроенных функций, что значительно облегчает такие операции как преобразование строк к данным других типов, поиск подстроки в строке, определение длины строки и т. д. В данной статье мы рассмотрим самые распространенные функции для работы со строками.
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.
Компьютеры лучше работают с числами в двоичном представлении, тогда как людям удобнее видеть числовые данные в виде строк, состоящих из цифр, запятых и пробелов. PL/SQL позволяет преобразовывать числа в строки, и наоборот. Обычно такие преобразования выполняются функциями TO_CHAR и TO_NUMBER .
При работе с двоичными вещественными типами IEEE-754 используйте функции TO_BINARY_FLOAT и TO_BINARY_DOUBLE . Чтобы упростить последующее изложение, в тексте будет упоминаться только функция TO_NUMBER . Помните, что все неуточненные ссылки на TO_NUMBER в равной степени относятся к функциям TO_BINARY_FLOAT и TO_BINARY_DOUBLE .
Функция TO_NUMBER
Функция TO_NUMBER преобразует строки фиксированной и переменной длины, а также вещественные типы IEEE-754 к типу NUMBER с использованием необязательной маски форматирования. Используйте эту функцию, когда потребуется преобразовать строковое представление числа в соответствующее числовое значение. Синтаксис вызова TO_NUMBER :
Здесь строка — строка или выражение типа BINARY_DOUBLE , содержащее представление числа; формат — необязательная маска, которая определяет, как функция TO_NUMBER должна интерпретировать символьное представление числа, содержащегося в первом параметре; параметры_nls — необязательная строка со значениями параметров NLS . Ее можно применять для замены текущих установок параметров NLS уровня сеанса.
При работе с функциями TO_BINARY_FLOAT и TO_BINARY_DOUBLE можно использовать строки INF и –INF для представления положительной и отрицательной бесконечности, а также строку NaN для представления «не числа». Эти специальные строки не чувствительны к регистру символов.
Использование TO_NUMBER без параметров форматирования
В простейших случаях функция TO_NUMBER вызывается без строки форматирования. Все следующие преобразования успешно выполняются без дополнительных параметров:
В общем случае функция TO_NUMBER может использоваться без параметров форматирования в следующих случаях:
- когда число представлено только цифрами с единственной десятичной запятой;
- при использовании научной записи — например, 1.25E2;
- перед числом стоит необязательный знак, плюс или минус; при отсутствии знака число считается положительным.
Если символьная строка не соответствует этим критериям или значения должны округляться до заданного количества десятичных знаков, вызывайте функцию TO_NUMBER с маской форматирования.
Использование TO_NUMBER с маской форматирования
Применение функции TO_NUMBER с маской форматирования позволяет получить более разнообразные представления чисел в PL/SQL. Например, местоположение разделителей групп и символ денежной единицы могут задаваться следующим образом:
Указывать в форматной строке точное количество цифр не обязательно. Функция TO_NUMBER позволяет задать в строке форматирования больше цифр, чем содержится в преобразуемом значении. Следующая строка кода также выполняется без ошибок:
Но если справа или слева от десятичной запятой значение содержит больше цифр, чем допускает маска форматирования, произойдет ошибка. Первое из следующих двух преобразований завершится ошибкой, поскольку строка содержит десять цифр слева от десятичной запятой, тогда как маска разрешает только девять. Второе преобразование завершается ошибкой из-за того, что строка содержит слишком много цифр справа от десятичной запятой:
Элемент форматирования 0 обеспечивает вывод начальных нулей:
Элемент PR распознает угловые скобки как обозначение отрицательного числа:
Однако не все элементы форматирования предназначены для преобразования строк в числа. Например, элемент RN , предназначенный для вывода числа римскими цифрами, предназначен только для форматирования выводимой информации. Следующая попытка преобразования вызовет ошибку:
Элемент EEEE тоже используется только для форматирования вывода. Для обратного преобразования он не нужен, поскольку функция TO_NUMBER правильно распознает числа в научной записи без дополнительных указаний:
Передача функции TO_NUMBER параметров NLS
Действие многих элементов форматирования определяется текущими установками параметров NLS . Например, элемент G представляет разделитель групп разрядов, но какой именно символ используется в качестве разделителя, зависит от текущего значения параметра NLS_NUMERIC_CHARACTERS в момент выполнения преобразования. Для просмотра текущих значений параметров можно запросить представление NLS_SESSION_PARAMETERS :
Некоторые установки NLS по умолчанию зависят от других. Если присвоить параметру NLS_TERRITORY значение AMERICA , Oracle по умолчанию установит параметр NLS_NUMERIC_CHARACTERS равным '.,'. Это не помешает явно присвоить параметру NLS_NUMERIC_CHARACTERS другое значение (например, с использованием команды ALTER SESSION ).
Иногда отдельные параметры NLS требуется переопределить только на время вызова TO_NUMBER . В этом случае нужные установки задаются в этом вызове и действуют они исключительно для него. Например, в следующем примере при вызове TO_NUMBER задаются установки NLS , соответствующие NLS_TERRITORY = FRANCE :
Строка параметров NLS получается слишком длинной, поэтому мы разбиваем ее на три строки, объединяемых оператором конкатенации, чтобы пример лучше смотрелся на странице. Обратите внимание на дублирование кавычек. Параметру NLS_NUMERIC_CHARACTERS требуется присвоить следующее значение:
Поскольку это значение вместе с кавычками включается в строку параметров NLS , каждую кавычку необходимо продублировать. В результате получается следующая строка:
Функция TO_NUMBER позволяет задавать только три приведенных в данном примере параметра NLS . Было бы удобнее, если бы при вызове можно было использовать следующую запись:
К сожалению, изменение параметра NLS_TERRITORY при вызове TO_NUMBER не поддерживается. Функция поддерживает только NLS_NUMERIC_CHARACTERS , NLS_CURRENCY и NLS_ISO_CURRENCY .
За подробной информацией о настройке параметров NLS обращайтесь к руководству «Oracle’s Globalization Support Guide», которое является частью документации Oracle11g.
Передавать TO_NUMBER третий аргумент не рекомендуется — лучше полагаться на настройки сеанса, определяющие, как PL/SQL интерпретирует элементы маски форматирования (такие, как L, G и D). Вместо того чтобы жестко кодировать информацию в программах, нужно дать пользователю возможность задавать их на уровне сеанса.
Функция TO_CHAR
Функция TO_CHAR выполняет задачу, обратную функции TO_NUMBER : она преобразует число в его символьное представление. Используя необязательную маску форматирования, можно подробно указать, каким должно быть представление. Функция TO_CHAR вызывается следующим образом:
Здесь число — это число, которое требуется представить в символьной форме. Оно может относиться к любому из числовых типов PL/SQL: NUMBER, PLS_INTEGER, BINARY_INTEGER, BINARY_FLOAT, BINARY_DOUBLE, SIMPLE_INTEGER, SIMPLE_FLOAT или SIMPLE_DOUBLE . Параметр формат содержит необязательную маску форматирования, определяющую способ представления числа в символьной форме; необязательная строка параметры_nls содержит значения параметров NLS . Ее можно применить для замещения текущих установок параметров NLS уровня сеанса.
Использование TO_CHAR без маски форматирования
Функция TO_CHAR , как и TO_NUMBER , может вызываться без маски форматирования:
Результат выглядит так:
В отличие от TO_NUMBER форма TO_CHAR особой пользы не приносит. Чтобы число лучше читалось, нужно задать как минимум разделитель групп разрядов.
Использование функции TO_CHAR с маской форматирования
При преобразовании числа в символьное представление функция TO_CHAR используется чаще всего с маской форматирования. Например, с ее помощью можно вывести денежную сумму:
В локальном контексте США результат будет выглядеть так:
Элементы форматирования позволяют очень гибко определять формат символьного представления числа. Чтобы лучше понять, как они работают, стоит немного с ними поэкспериментировать. В следующем примере указано, что старшие разряды должны быть заполнены нулями, но при этом элемент форматирования B требует замены нулей пробелами. Данный элемент предшествует цифровым элементам (нулям), но следует за индикатором вывода знака денежной единицы L :
Результат будет иметь следующий вид:
В примере выводится только одна строка, полученная после первого преобразования. В результате второго преобразования получается нуль, и элемент форматирования B заставляет TO_CHAR вернуть пустую строку, хотя в маске форматирования указано, что нулевые старшие разряды числа следует оставить. В качестве эксперимента попробуйте выполнить этот пример без элемента B.
Не все комбинации элементов форматирования являются допустимыми. Например, нельзя использовать сочетание LRN , которое выводит перед числом, записанным римскими цифрами, знак денежной единицы. Oracle не документирует такие нюансы, поэтому о некоторых из них можно узнать только на практике.
Элемент форматирования V
Элемент форматирования V достаточно необычен, чтобы его стоило упомянуть особо. Он позволяет масштабировать значение, а его действие лучше показать на примере (рис. 1).
Рис. 1. Элемент форматирования V
Зачем может понадобиться масштабирование? Рассмотрим простой пример. Стандартная единица сделки на бирже составляет 100 акций, и сообщая о реализованных на бирже акциях обычно говорят о количестве проданных пакетов по 100 акций. Поэтому 123 продажи означает 123 пакета по 100 акций, то есть 12 300 акций.
Следующий пример показывает, как использовать элемент V для масштабирования значения 123, с учетом того, что на самом деле оно представляет количество сотен:
Заметьте, что в этом примере маска форматирования включает элемент G , определяющий местоположение разделителя групп (запятой), который может быть задан только слева от элемента V , что не всегда удобно. Следующая маска форматирования на первый взгляд выглядит вполне разумно:
Вы ожидаете, что результат будет отформатирован в виде 1,234,500, но элемент G не может располагаться справа от V. Можно использовать маску 9G99V9999 для получения результата 1,234500 или маску 999V9999 для получения 1234500, но оба эти результата выглядят не так, как нам хотелось бы.
Читайте также: