Компьютеры лучше работают с числами в двоичном представлении, тогда как людям удобнее видеть числовые данные в виде строк, состоящих из цифр, запятых и пробелов. 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
:
TO_NUMBER(строка [,формат [,параметры_nls]])
Здесь строка — строка или выражение типа BINARY_DOUBLE
, содержащее представление числа; формат — необязательная маска, которая определяет, как функция TO_NUMBER
должна интерпретировать символьное представление числа, содержащегося в первом параметре; параметры_nls
— необязательная строка со значениями параметров NLS
. Ее можно применять для замены текущих установок параметров NLS
уровня сеанса.
При работе с функциями TO_BINARY_FLOAT
и TO_BINARY_DOUBLE
можно использовать строки INF и –INF для представления положительной и отрицательной бесконечности, а также строку NaN
для представления «не числа». Эти специальные строки не чувствительны к регистру символов.
Использование TO_NUMBER без параметров форматирования
В простейших случаях функция TO_NUMBER
вызывается без строки форматирования. Все следующие преобразования успешно выполняются без дополнительных параметров:
DECLARE
a NUMBER;
b NUMBER;
c NUMBER;
d NUMBER;
e BINARY_FLOAT;
f BINARY_DOUBLE;
g BINARY_DOUBLE;
n1 VARCHAR2(20) := '-123456.78';
n2 VARCHAR2(20) := '+123456.78';
BEGIN
a := TO_NUMBER('123.45');
b := TO_NUMBER(n1);
c := TO_NUMBER(n2);
d := TO_NUMBER('1.25E2');
e := TO_BINARY_FLOAT('123.45');
f := TO_BINARY_DOUBLE('inf');
g := TO_BINARY_DOUBLE('NAN');
END;
В общем случае функция TO_NUMBER
может использоваться без параметров форматирования в следующих случаях:
- когда число представлено только цифрами с единственной десятичной запятой;
- при использовании научной записи — например, 1.25E2;
- перед числом стоит необязательный знак, плюс или минус; при отсутствии знака число считается положительным.
Если символьная строка не соответствует этим критериям или значения должны округляться до заданного количества десятичных знаков, вызывайте функцию TO_NUMBER
с маской форматирования.
Использование TO_NUMBER с маской форматирования
Применение функции TO_NUMBER
с маской форматирования позволяет получить более разнообразные представления чисел в PL/SQL. Например, местоположение разделителей групп и символ денежной единицы могут задаваться следующим образом:
a := TO_NUMBER('$123,456.78','L999G999D99');
Указывать в форматной строке точное количество цифр не обязательно. Функция TO_NUMBER
позволяет задать в строке форматирования больше цифр, чем содержится в преобразуемом значении. Следующая строка кода также выполняется без ошибок:
a := TO_NUMBER('$123,456.78','L999G999G999D99');
Но если справа или слева от десятичной запятой значение содержит больше цифр, чем допускает маска форматирования, произойдет ошибка. Первое из следующих двух преобразований завершится ошибкой, поскольку строка содержит десять цифр слева от десятичной запятой, тогда как маска разрешает только девять. Второе преобразование завершается ошибкой из-за того, что строка содержит слишком много цифр справа от десятичной запятой:
a := TO_NUMBER('$1234,567,890.78','L999G999G999D99');
a := TO_NUMBER('$234,567,890.789','L999G999G999D99');
Элемент форматирования 0 обеспечивает вывод начальных нулей:
a := TO_NUMBER('001,234','000G000');
Элемент PR
распознает угловые скобки как обозначение отрицательного числа:
a := TO_NUMBER('','999D99PR');
Однако не все элементы форматирования предназначены для преобразования строк в числа. Например, элемент RN
, предназначенный для вывода числа римскими цифрами, предназначен только для форматирования выводимой информации. Следующая попытка преобразования вызовет ошибку:
a := TO_NUMBER('cxxiii','rn');
Элемент EEEE
тоже используется только для форматирования вывода. Для обратного преобразования он не нужен, поскольку функция TO_NUMBER
правильно распознает числа в научной записи без дополнительных указаний:
a := TO_NUMBER('1.23456E-24');
Передача функции TO_NUMBER параметров NLS
Действие многих элементов форматирования определяется текущими установками параметров NLS
. Например, элемент G представляет разделитель групп разрядов, но какой именно символ используется в качестве разделителя, зависит от текущего значения параметра NLS_NUMERIC_CHARACTERS
в момент выполнения преобразования. Для просмотра текущих значений параметров можно запросить представление NLS_SESSION_PARAMETERS
:
SQL> SELECT * FROM nls_session_parameters;
PARAMETER VALUE
------------------------- ---------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
Некоторые установки NLS
по умолчанию зависят от других. Если присвоить параметру NLS_TERRITORY
значение AMERICA
, Oracle по умолчанию установит параметр NLS_NUMERIC_CHARACTERS
равным '.,'. Это не помешает явно присвоить параметру NLS_NUMERIC_CHARACTERS
другое значение (например, с использованием команды ALTER SESSION
).
Иногда отдельные параметры NLS
требуется переопределить только на время вызова TO_NUMBER
. В этом случае нужные установки задаются в этом вызове и действуют они исключительно для него. Например, в следующем примере при вызове TO_NUMBER
задаются установки NLS
, соответствующие NLS_TERRITORY
=FRANCE
:
a := TO_NUMBER('F123.456,78','L999G999D99',
'NLS_NUMERIC_CHARACTERS='',.'''
|| ' NLS_CURRENCY=''F'''
|| ' NLS_ISO_CURRENCY=FRANCE');
Строка параметров NLS
получается слишком длинной, поэтому мы разбиваем ее на три строки, объединяемых оператором конкатенации, чтобы пример лучше смотрелся на странице. Обратите внимание на дублирование кавычек. Параметру NLS_NUMERIC_CHARACTERS
требуется присвоить следующее значение:
NLS_NUMERIC_CHARACTERS=',.'
Поскольку это значение вместе с кавычками включается в строку параметров NLS
, каждую кавычку необходимо продублировать. В результате получается следующая строка:
'NLS_NUMERIC_CHARACTERS='',.'''
Функция TO_NUMBER
позволяет задавать только три приведенных в данном примере параметра NLS
. Было бы удобнее, если бы при вызове можно было использовать следующую запись:
a := TO_NUMBER('F123.456,78','L999G999D99','NLS_TERRITORY=FRANCE');
К сожалению, изменение параметра 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
вызывается следующим образом:
TO_CHAR(число [,формат [,параметры_nls]])
Здесь число — это число, которое требуется представить в символьной форме. Оно может относиться к любому из числовых типов PL/SQL: NUMBER, PLS_INTEGER, BINARY_INTEGER, BINARY_FLOAT, BINARY_DOUBLE, SIMPLE_INTEGER, SIMPLE_FLOAT
или SIMPLE_DOUBLE
. Параметр формат содержит необязательную маску форматирования, определяющую способ представления числа в символьной форме; необязательная строка параметры_nls
содержит значения параметров NLS
. Ее можно применить для замещения текущих установок параметров NLS
уровня сеанса.
Если вы хотите, чтобы результат был представлен в национальном наборе символов, используйте вместо TO_CHAR
функцию TO_NCHAR
. При этом помните, что строка форматирования числа должна быть представлена символами национального набора; в противном случае полученная строка будет состоять из символов «#
».
Использование TO_CHAR без маски форматирования
Функция TO_CHAR
, как и TO_NUMBER
, может вызываться без маски форматирования:
DECLARE
b VARCHAR2(30);
BEGIN
b := TO_CHAR(123456789.01);
DBMS_OUTPUT.PUT_LINE(b);
END;
Результат выглядит так:
123456789.01
В отличие от TO_NUMBER
форма TO_CHAR
особой пользы не приносит. Чтобы число лучше читалось, нужно задать как минимум разделитель групп разрядов.
Использование функции TO_CHAR с маской форматирования
При преобразовании числа в символьное представление функция TO_CHAR
используется чаще всего с маской форматирования. Например, с ее помощью можно вывести денежную сумму:
DECLARE
b VARCHAR2(30);
BEGIN
b := TO_CHAR(123456789.01,'L999G999G999D99');
DBMS_OUTPUT.PUT_LINE(b);
END;
В локальном контексте США результат будет выглядеть так:
$123,456,789.01
Элементы форматирования позволяют очень гибко определять формат символьного представления числа. Чтобы лучше понять, как они работают, стоит немного с ними поэкспериментировать. В следующем примере указано, что старшие разряды должны быть заполнены нулями, но при этом элемент форматирования B требует замены нулей пробелами. Данный элемент предшествует цифровым элементам (нулям), но следует за индикатором вывода знака денежной единицы L
:
DECLARE
b VARCHAR2(30);
c VARCHAR2(30);
BEGIN
b := TO_CHAR(123.01,'LB000G000G009D99');
DBMS_OUTPUT.PUT_LINE(b);
c := TO_CHAR(0,'LB000G000G009D99');
DBMS_OUTPUT.PUT_LINE(c);
END;
Результат будет иметь следующий вид:
$000,000,123.01
В примере выводится только одна строка, полученная после первого преобразования. В результате второго преобразования получается нуль, и элемент форматирования B заставляет TO_CHAR
вернуть пустую строку, хотя в маске форматирования указано, что нулевые старшие разряды числа следует оставить. В качестве эксперимента попробуйте выполнить этот пример без элемента B.
Не все комбинации элементов форматирования являются допустимыми. Например, нельзя использовать сочетание LRN
, которое выводит перед числом, записанным римскими цифрами, знак денежной единицы. Oracle не документирует такие нюансы, поэтому о некоторых из них можно узнать только на практике.
Элемент форматирования V
Элемент форматирования V достаточно необычен, чтобы его стоило упомянуть особо. Он позволяет масштабировать значение, а его действие лучше показать на примере (рис. 1).
Рис. 1. Элемент форматирования V
Зачем может понадобиться масштабирование? Рассмотрим простой пример. Стандартная единица сделки на бирже составляет 100 акций, и сообщая о реализованных на бирже акциях обычно говорят о количестве проданных пакетов по 100 акций. Поэтому 123 продажи означает 123 пакета по 100 акций, то есть 12 300 акций.
Следующий пример показывает, как использовать элемент V
для масштабирования значения 123, с учетом того, что на самом деле оно представляет количество сотен:
DECLARE
shares_sold NUMBER := 123;
BEGIN
DBMS_OUTPUT.PUT_LINE(
TO_CHAR(shares_sold,'999G9V99')
);
END;
Результат:
12,300
Заметьте, что в этом примере маска форматирования включает элемент G
, определяющий местоположение разделителя групп (запятой), который может быть задан только слева от элемента V
, что не всегда удобно. Следующая маска форматирования на первый взгляд выглядит вполне разумно:
TO_CHAR(123.45,'9G99V9G999');
Вы ожидаете, что результат будет отформатирован в виде 1,234,500, но элемент G не может располагаться справа от V. Можно использовать маску 9G99V9999 для получения результата 1,234500 или маску 999V9999 для получения 1234500, но оба эти результата выглядят не так, как нам хотелось бы.
Едва ли вы часто будете пользоваться элементом V
, но знать о нем нужно.