<legend id='3ekf0'><style id='3ekf0'><dir id='3ekf0'><q id='3ekf0'></q></dir></style></legend>
    <bdo id='3ekf0'></bdo><ul id='3ekf0'></ul>

    1. <tfoot id='3ekf0'></tfoot>
    2. <small id='3ekf0'></small><noframes id='3ekf0'>

      <i id='3ekf0'><tr id='3ekf0'><dt id='3ekf0'><q id='3ekf0'><span id='3ekf0'><b id='3ekf0'><form id='3ekf0'><ins id='3ekf0'></ins><ul id='3ekf0'></ul><sub id='3ekf0'></sub></form><legend id='3ekf0'></legend><bdo id='3ekf0'><pre id='3ekf0'><center id='3ekf0'></center></pre></bdo></b><th id='3ekf0'></th></span></q></dt></tr></i><div id='3ekf0'><tfoot id='3ekf0'></tfoot><dl id='3ekf0'><fieldset id='3ekf0'></fieldset></dl></div>

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

      How to efficiently convert text to number in Oracle PL/SQL with non-default NLS_NUMERIC_CHARACTERS?(如何使用非默认 NLS_NUMERIC_CHARACTERS 在 Oracle PL/SQL 中有效地将文本转换为数字?)

          <small id='aeZbv'></small><noframes id='aeZbv'>

            • <bdo id='aeZbv'></bdo><ul id='aeZbv'></ul>

              <tfoot id='aeZbv'></tfoot>

              <legend id='aeZbv'><style id='aeZbv'><dir id='aeZbv'><q id='aeZbv'></q></dir></style></legend>

            • <i id='aeZbv'><tr id='aeZbv'><dt id='aeZbv'><q id='aeZbv'><span id='aeZbv'><b id='aeZbv'><form id='aeZbv'><ins id='aeZbv'></ins><ul id='aeZbv'></ul><sub id='aeZbv'></sub></form><legend id='aeZbv'></legend><bdo id='aeZbv'><pre id='aeZbv'><center id='aeZbv'></center></pre></bdo></b><th id='aeZbv'></th></span></q></dt></tr></i><div id='aeZbv'><tfoot id='aeZbv'></tfoot><dl id='aeZbv'><fieldset id='aeZbv'></fieldset></dl></div>

                  <tbody id='aeZbv'></tbody>
                本文介绍了如何使用非默认 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 中有效地将文本转换为数字?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                相关文档推荐

                How to redirect the output of DBMS_OUTPUT.PUT_LINE to a file?(如何将 DBMS_OUTPUT.PUT_LINE 的输出重定向到文件?)
                How do I get column datatype in Oracle with PL-SQL with low privileges?(如何使用低权限的 PL-SQL 在 Oracle 中获取列数据类型?)
                Get a list of all functions and procedures in an Oracle database(获取 Oracle 数据库中所有函数和过程的列表)
                Why cannot I create triggers on objects owned by SYS?(为什么我不能在 SYS 拥有的对象上创建触发器?)
                Returning result even for elements in IN list that don#39;t exist in table(即使对于表中不存在的 IN 列表中的元素也返回结果)
                Reset Sequence in oracle 11g(oracle 11g 中的重置序列)

                    <tbody id='G7IvM'></tbody>

                  <tfoot id='G7IvM'></tfoot>
                  <i id='G7IvM'><tr id='G7IvM'><dt id='G7IvM'><q id='G7IvM'><span id='G7IvM'><b id='G7IvM'><form id='G7IvM'><ins id='G7IvM'></ins><ul id='G7IvM'></ul><sub id='G7IvM'></sub></form><legend id='G7IvM'></legend><bdo id='G7IvM'><pre id='G7IvM'><center id='G7IvM'></center></pre></bdo></b><th id='G7IvM'></th></span></q></dt></tr></i><div id='G7IvM'><tfoot id='G7IvM'></tfoot><dl id='G7IvM'><fieldset id='G7IvM'></fieldset></dl></div>
                  • <bdo id='G7IvM'></bdo><ul id='G7IvM'></ul>
                  • <small id='G7IvM'></small><noframes id='G7IvM'>

                  • <legend id='G7IvM'><style id='G7IvM'><dir id='G7IvM'><q id='G7IvM'></q></dir></style></legend>