Числовые преобразования в PL/SQL на примере: TO_NUMBER, TO_CHAR

Конвертация символьных и числовых типов PL/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

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');

Конвертация символьных и числовых типов PL/SQL

Строка параметров 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).

Элемент форматирования V

Рис. 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, но знать о нем нужно.

 

Вас заинтересует / Intresting for you:

Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 4633 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 14734 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Символьные функции и аргументы...
Символьные функции и аргументы... 18544 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 12232 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Войдите чтобы комментировать

ildergun аватар
ildergun ответил в теме #10052 2 года 9 мес. назад
Отличная документация по функциям TO_NUMBER, TO_CHAR в Oracle. Хорошие примеры запросов. Благодарю!
Myk аватар
Myk ответил в теме #9226 5 года 6 мес. назад
TO_NUMBER и TO_NUMBER примеры хороши, все буквально разжевано!
MaxNiko аватар
MaxNiko ответил в теме #9106 5 года 8 мес. назад
Функция TO_CHAR объяснена абсолютно четко + хорошие примеры.
Gwen аватар
Gwen ответил в теме #9073 5 года 9 мес. назад
Перекидывать переменные из числового формата в символьный и обратно приходится часто при программировании на PL/SQL. Спасибо автору за адекватную статью. Он прояснил несколько важных нюансов, о которых начинающие кодеры знать просто обязаны.