如何使用非默认 NLS_NUMERIC_CHARACTERS 在 Oracle PL/SQL 中有效地将文本转换为数字?

问题描述

我试图在 PL/SQL 中找到一种高效、通用的方法将字符串转换为数字，其中 NLS_NUMERIC_CHARACTERS 设置的本地设置是不可预测的——我最好不要碰它.输入格式为编程标准123.456789"，但小数点两边的位数未知.

I'm trying to find an efficient, generic way to convert from string to a number in PL/SQL, where the local setting for NLS_NUMERIC_CHARACTERS settings is inpredictable -- and preferable I won't touch it. The input format is the programming standard "123.456789", but with an unknown number of digits on each side of the decimal point.

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 接受第三个参数，但在这种情况下，您也需要指定第二个参数，并且默认"没有格式规范...

to_number accepts a 3rd parameter but in that case you to specify a second parameter too, and there is no format spec for "default"...

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 的方法:

There is another way using 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 的会话值，然后它会再次中断.

which does exactly what I want, but it doesn't seem efficient if you do it many, many times in a query. You cannot cache the value of v_decimal (fetch once and store in a package variable) because it doesn't know if you change your session value for NLS_NUMERIC_CHARACTERS, and then it would break, again.

我是否忽略了什么?还是我太担心了，而 Oracle 这样做的效率比我认为的要高得多?

Am I overlooking something? Or am I worrying too much, and Oracle does this a lot more efficient then I'd give it credit for?

推荐答案

以下应该有效:

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

它将使用高效的translate 构建正确的第二个参数999.999999，因此您不必事先知道有多少位数字.它将适用于所有支持的 Oracle 数字格式(在 10.2.0.3 中显然最多 62 位有效数字).

It will build the correct second argument 999.999999 with the efficient translate so you don't have to know how many digits there are beforehand. It will work with all supported Oracle number format (up to 62 significant digits apparently in 10.2.0.3).

有趣的是，如果你有一个非常大的字符串，简单的 to_number(:x) 会起作用，而这个方法会失败.

Interestingly, if you have a really big string the simple to_number(:x) will work whereas this method will fail.

由于 sOliver 支持负数.

这篇关于如何使用非默认 NLS_NUMERIC_CHARACTERS 在 Oracle PL/SQL 中有效地将文本转换为数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！