Как эффективно конвертировать текст в число в Oracle PL/SQL с нестандартными NLS_NUMERIC_CHARACTERS?

Я пытаюсь найти эффективный, общий способ преобразования из строки в число в PL/SQL, где локальная настройка для параметров NLS_NUMERIC_CHARACTERS непредсказуема - и, предпочтительнее, я ее не коснусь. Формат ввода - это стандарт программирования "123.456789", но с неизвестным числом цифр на каждой стороне десятичной точки.

select to_number('123.456789') from dual;
  -- only works if nls_numeric_characters is '.,'

select to_number('123.456789', '99999.9999999999') from dual;
  -- only works if the number of digits in the format is large enough
  -- but I don't want to guess...

to_number принимает третий параметр, но в этом случае вы также должны указать второй параметр, и нет спецификации формата для "по умолчанию"...

select to_number('123.456789', null, 'nls_numeric_characters=''.,''') from dual;
  -- returns null

select to_number('123.456789', '99999D9999999999', 'nls_numeric_characters=''.,''') from dual;
  -- "works" with the same caveat as (2), so it rather pointless...

Существует другой способ использования PL/SQL:

CREATE OR REPLACE
FUNCTION STRING2NUMBER (p_string varchar2) RETURN NUMBER
IS
  v_decimal char;
BEGIN
  SELECT substr(VALUE, 1, 1)
  INTO v_decimal
  FROM NLS_SESSION_PARAMETERS
  WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
  return to_number(replace(p_string, '.', v_decimal));
END;
/

select string2number('123.456789') from dual;

который делает именно то, что я хочу, но он не кажется эффективным, если вы делаете это много раз в запросе. Вы не можете кэшировать значение v_decimal (выборка один раз и сохранение в переменной пакета), потому что он не знает, измените ли вы значение сеанса для NLS_NUMERIC_CHARACTERS, а затем снова сломаетесь.

Я что-то пропускаю? Или я слишком беспокоюсь, и Oracle делает это намного эффективнее, чем я бы дал ему кредит?

Ответ 1

Следующее должно работать:

SELECT to_number(:x, 
                 translate(:x, '012345678-+', '999999999SS'), 
                 'nls_numeric_characters=''.,''') 
  FROM dual;

Он построит правильный второй аргумент 999.999999 с эффективным translate, поэтому вам не нужно знать, сколько цифр есть заблаговременно. Он будет работать со всеми поддерживаемыми форматами Oracle (до 62 значащих цифр, по-видимому, в 10.2.0.3).

Интересно, что если у вас действительно большая строка, простой to_number(:x) будет работать, тогда как этот метод не удастся.

Изменить: поддержка отрицательных чисел благодаря sOliver.

Ответ 2

Если вы выполняете большую работу за сеанс, возможно, вам придется использовать ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,' в начале вашей задачи.

Конечно, если в одном сеансе выполняется много другого кода, вы можете получить фанковые результаты:-) Однако мы можем использовать этот метод в наших процедурах загрузки данных, поскольку у нас есть выделенные программы с их собственными пулами соединений для загрузки данных.

Ответ 3

Извините, позже я заметил, что ваш вопрос был наоборот. Тем не менее следует отметить, что в противоположном направлении есть простое решение:

Немного поздно, но сегодня я заметил специальные маски формата "TM9" и "TME", которые описываются как "модель формата минимального номера текста возвращает (в десятичном формате) наименьшее количество символов". на https://docs.oracle.com/cloud/latest/db112/SQLRF/sql_elements004.htm#SQLRF00210.

Кажется, что TM9 был изобретен только для решения этой конкретной проблемы:

select to_char(1234.5678, 'TM9', 'NLS_NUMERIC_CHARACTERS=''.,''') from dual;

Результат '1234.5678' без начальных или конечных пробелов и десятичной POINT, несмотря на мою среду, содержащую NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252, которая обычно вызывает десятичный COMMA.

Ответ 4

select to_number(replace(:X,'.',to_char(0,'fmd'))) from dual;

кстати

select to_number(replace('1.2345e-6','.',to_char(0,'fmd'))) from dual;

и если вы хотите более строгий

select to_number(translate(:X,to_char(0,'fmd')||'.','.'||to_char(0,'fmd'))) from dual;