Как эффективно преобразовать текст в число в 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 принимает 3-й параметр, но в этом случае вы также указываете второй параметр, и не существует спецификации формата для «по умолчанию» ...

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's 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 делает это намного эффективнее, чем я бы того заслуживал?

 mik18 мая 2017 г., 17:07
Еще более сложный пример - конвертировать'12.34E5', Я не нашел формат, который принимает это, но однопараметрическийto_number не имеет проблем с разбором.

Ответы на вопрос(3)

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

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.

 sOliver02 окт. 2015 г., 07:27
Может быть полезно завершить аргумент 'fmt'to_number функционировать следующим образом:translate(:x, '0123456789.,-+', '9999999999DG'), Это останавливает ошибку ORA-01481 на отрицательных числах.

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

Немного поздно, но сегодня я заметил специальные маски формата «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' без начальных или конечных пробелов и десятичной точки, несмотря на мою среду, содержащуюNLS_LANG=GERMAN_GERMANY.WE8MSWIN1252, что обычно вызывает десятичную запятую.

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

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

Ваш ответ на вопрос