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

Question

我试图在 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'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 的 session 值，然后它会再次中断。

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

Answer 1

以下应该工作:

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

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

有趣的是，如果您有一个非常大的字符串，那么简单的 to_number(:x) 将起作用，而此方法将失败。

编辑:由于 sOliver 支持负数。