Как использовать функцию Oracle TO_NUMBER() с использованием NLS_NUMERIC_CHARACTERS, определенной на сервере базы данных?

Сегодня я обнаружил, что Oracle NLS_NUMERIC_CHARACTERS неоднозначно определяется!

Действительно, с самого начала я думал, что NLS_NUMERIC_CHARACTERS используется только для определения способа отображения десятичного числа.

Но на самом деле, NLS_NUMERIC_CHARACTERS также косвенно используется в TO_NUMBER() Функция для определения формата числа STRING для преобразования.

Моя проблема в том, что я европеец, и для меня десятичный разделитель - это на самом деле запятая (,), а не точка (.).

Когда я отображаю число, я предпочитаю отображать его через запятую. По этой причине NLS_NUMERIC_CHARACTERS установлен в ',.' где первый символ определяет разделитель чисел.

До сих пор у меня нет проблем с этим:-)

Моя проблема в том, что все String Значения поля таблицы, содержащие число, используют символ POINT в качестве десятичного разделителя.

Когда я преобразую любое строковое значение, представляющее число, я могу использовать функцию Oracle TO_NUMBER следующим образом

select TO_NUMBER(VALUE) from TB_PARAMETER;

Это хорошо работает, если десятичный разделитель ТОЧКА (NLS_NUMERIC_CHARACTERS = '.,') но это перестает работать, если десятичный разделитель является COMMA (NLS_NUMERIC_CHARACTERS = ',.').

Я знаю что могу уточнить NLS_NUMERIC_CHARACTERS в TO_NUMBER() функционировать так

select TO_NUMBER(VALUE
                ,'999999999D999999999'
                ,'NLS_NUMERIC_CHARACTERS = ''.,'''
                )
  from TB_PARAMETER;

но это многословно для меня!

Есть ли функция Oracle, которая делает то же самое?

Пример:

select CAST_NUMBER(VALUE) from TB_PARAMETER;

Персонально, я думаю, что тот факт, что Oracle TO_NUMBER() использование функции NLS_NUMERIC_CHARACTERS Параметр сеанса для определения формата десятичной строки для преобразования является ошибкой.

Действительно, в базе данных десятичный разделитель, используемый в строке, всегда фиксирован. Это запятая или точка, но никогда, однажды запятая, а другая точка.

Если мой анализ верен, TO_NUMBER() Функция всегда должна использовать фиксированное значение POINT или COMMA, которые не соответствуют (во всех случаях) NLS_NUMERIC_CHARACTERS, поскольку эти параметры имеют область действия сеанса и определяются клиентским приложением Oracle.

В резюме, когда вы используете TO_NUMBER() функция без указания каких-либо NLS_NUMERIC_CHARACTERS в представлении Oracle ваше представление будет работать корректно до тех пор, пока оно не будет выполнено в сеансе с другим NLS_NUMERIC_CHARACTERS значение!

То же самое, безусловно, существует, когда вы пытаетесь преобразовать значение DATE, сохраненное в виде строки, используя Oracle TO_DATE() функция!

Почему это неоднозначно!

Чтобы ответить на некоторые вопросы в комментариях, я добавил свое длинное объяснение в следующих параграфах.

Изменение NLS_NUMERIC_CHARACTERS на уровне сеанса или на уровне системы не всегда возможно, поскольку NLS_NUMERIC_CHARACTERS имеет 2 различные роли или цели.

  1. исправление десятичного разделителя отображения десятичных чисел
  2. исправление десятичного разделителя, используемого для преобразования строки в функцию TO_NUMBER()

Пример: предположим, что вы создадите представление, которое будет отображать десятичные числа и которое использует TO_NUMBER() для выполнения некоторых вычислений.

Если десятичный разделитель, сохраненный в базе данных, является POINT и если необходимо, чтобы десятичные числа отображались с использованием COMMA в качестве десятичного разделителя, вы можете изменить NLS_NUMERIC_CHARACTERS, чтобы определить десятичный разделитель как POINT. Функция Oracle TO_NUMBER() будет работать корректно, но десятичные числа, отображаемые на экране, будут отображаться с POINT в качестве десятичного разделителя!

Вот почему я говорю, что NLS_NUMERIC_CHARACTERS неоднозначно.

В резюме, в системе баз данных, где NLS_NUMERIC_CHARACTERS может быть '.,' Или ',.' И где десятичное число может быть сохранено в таблице Oracle с использованием хорошо фиксированного формата (пример: десятичный разделитель - POINT), небезопасно использовать TO_NUMBER() без указания правильного NLS_NUMERIC_CHARACTERS.

Ошибка Oracle (или неправильное представление, если вы предпочитаете) состоит в том, чтобы определить параметр (NLS_NUMERIC_CHARACTERS) с 2 различными ролями!

Источник

4 ответа

Для информации я создал следующую функцию

CREATE FUNCTION TO_X_NUMBER(sNumber IN VARCHAR2) 
  RETURN NUMBER as

BEGIN
    RETURN TO_NUMBER
        (sNumber
        ,'99999999999999999999D99999999999999999999'
        ,'NLS_NUMERIC_CHARACTERS=''.,''');
END TO_X_NUMBER;

и синоним

CREATE PUBLIC SYNONYM TO_X_NUMBER FOR GWHDBA.TO_X_NUMBER; 

GRANT EXECUTE ON TO_X_NUMBER TO PUBLIC;

Теперь я могу использовать его из другой схемы, как в следующем примере:

SELECT TO_X_NUMBER(PARAM_VALUE) as STANDARD_DEVIATION
  FROM TB_PARAMETER
  WHERE PARAM_NAME = 'STANDARD_DEVIATION';
Источник

Ну, с моей точки зрения, лучший вариант - это сохранить цифры в NUMBER столбцы типа данных. Как вы будете подавать заявку TO_NUMBER в value = '32.5rzg'?

Кроме этого, я предполагаю, что то, что вы уже обнаружили (применяя NLS_NUMERIC_CHARACTERS) это то, что вы можете сделать, кроме использования REPLACE и преобразовать все точки в запятые во всех этих строковых значениях.

Источник

Функция, которая всегда будет действовать правильно, независимо от настроек NLS клиента и независимо от входной строки,

      declare
  mynum  number;

  FUNCTION TO_NUMBER_FNLS(sNumber in varchar2) return number as
      ret  number;
  BEGIN
      select
           to_number(
             case substr(nls.value,1,1)
               when ',' then replace(sNumber, '.', ',')
               else          replace(sNumber, ',', '.')
             end
           )
           into ret
      from nls_session_parameters nls
      where nls.parameter = 'NLS_NUMERIC_CHARACTERS';

      return ret;
  END;

begin
  mynum := TO_NUMBER_FNLS('100.22');
  DBMS_OUTPUT.PUT_LINE('input with dot: '|| mynum);

  mynum := TO_NUMBER_FNLS('100,22');
  DBMS_OUTPUT.PUT_LINE('input with comma: '|| mynum);
end;
/

https://dbfiddle.uk/dim1wC2W

Источник

Возможно, это немного поздно, но я нашел следующее:

Чтобы улучшить производительность, в качестве подготовки сделайте что-то вроде

      select value into v_nls_numeric_characters
from nls_session_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS';

Затем, если вы знаете, что ваш ввод ЗАПЯТЬЯ означает десятичный символ, а ТОЧКА означает разделитель групп (например, немецкий формат чисел), используйте это:

      to_number(translate(pi_input_text, ',.', v_nls_numeric_characters))

Если вы ожидаете американские цифры (ТОЧКА означает десятичный символ, а ЗАПЯТЬЯ означает разделитель групп, используйте это:

      to_number(translate(pi_input_text, '.,', v_nls_numeric_characters))
Источник
Другие вопросы по тегам