具有精度和规模的Oracle自定义IsNumber函数 [英] Oracle Custom IsNumber Function with Precision and Scale

问题描述

是否可以编写一个Oracle函数来测试字符串是否符合数字精度和小数位数，同时在运行时提供精度和小数位数，而不使用立即执行?

Is is possible to write an Oracle function that tests to see if a string conforms to a numeric precision and scale while providing precision and scale at run-time, and not using execute immediate?

功能签名将是这样:

FUNCTION IsNumber(pVALUE VARCHAR2, pPRECISION NUMBER, pSCALE NUMBER) RETURN NUMBER

做这样的事是无效的:

DECLARE aNUMBER NUMBER(pPRECISION, pSCALE);

关于如何获得这种效果的任何想法吗?

Any ideas on how to get something like this working?

推荐答案

我不认为有任何简单的内置方式；并且进行动态检查相对容易(请参见下面的示例).但是，作为一种相当复杂的方法，您可以使用根据精度和小数位数构造的格式模型将字符串转换为数字，然后转换回字符串:

I don't think there's any simple built in way; and doing a dynamic check is relatively easy (see example below). But as a rather convoluted approach you could convert the string to a number and back to a string using a format model constructed from your precision and scale:

CREATE OR REPLACE FUNCTION IsNumber(pVALUE VARCHAR2, pPRECISION NUMBER,
  pSCALE NUMBER) RETURN NUMBER
IS
  lFORMAT VARCHAR2(80);
  lNUMBER NUMBER;
  lSTRING NUMBER;

  FUNCTION GetFormat(p NUMBER, s NUMBER) RETURN VARCHAR2 AS
  BEGIN
    RETURN
      CASE WHEN p >= s THEN LPAD('9', p - s, '9') END
        || CASE WHEN s > 0 THEN '.' || CASE WHEN s > p THEN
            LPAD('0', s - p, '0') || RPAD('9', p, '9')
          ELSE RPAD('9', s, '9') END
      END;
  END GetFormat;
BEGIN
  -- sanity-check values; other checks needed (precision <= 38?)
  IF pPRECISION = 0 THEN
    RETURN NULL;
  END IF;

  -- check it's actually a number
  lNUMBER := TO_NUMBER(pVALUE);

  -- get it into the expected format; this will error if the precision is
  -- exceeded, but scale is rounded so doesn't error
  lFORMAT := GetFormat(pPRECISION, pSCALE);
  lSTRING := to_char(lNUMBER, lFORMAT, 'NLS_NUMERIC_CHARACTERS='',.''');

  -- to catch scale rounding, check against a greater scale
  -- note: this means we reject numbers that CAST will allow but round
  lFORMAT := GetFormat(pPRECISION + 1, pSCALE + 1);

  IF lSTRING != to_char(lNUMBER, lFORMAT, 'NLS_NUMERIC_CHARACTERS='',.''') THEN
    RETURN NULL;  -- scale too large
  END IF;
  RETURN lNUMBER;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;  -- not a number, precision too large, etc.
END IsNumber;
/

仅使用一些值进行了测试，但到目前为止似乎可以正常工作

Only tested with a few values but seems to work so far:

with t as (
  select '0.123' as value, 3 as precision, 3 as scale from dual
  union all select '.123', 2, 2 from dual
  union all select '.123', 1, 3 from dual
  union all select '.123', 2, 2 from dual
  union all select '1234', 4, 0 from dual
  union all select '1234', 3, 1 from dual
  union all select '123', 2, 0 from dual
  union all select '.123', 0, 3 from dual
  union all select '-123.3', 4, 1 from dual
  union all select '123456.789', 6, 3 from dual
  union all select '123456.789', 7, 3 from dual
  union all select '101.23253232', 3, 8 from dual
  union all select '101.23253232', 11, 8 from dual
)
select value, precision, scale,
  isNumber(value, precision, scale) isNum,
  isNumber2(value, precision, scale) isNum2
from t;

VALUE         PRECISION      SCALE      ISNUM     ISNUM2
------------ ---------- ---------- ---------- ----------
0.123                 3          3       .123       .123 
.123                  2          2                   .12 
.123                  1          3       .123            
.123                  2          2                   .12 
1234                  4          0       1234       1234 
1234                  3          1                       
123                   2          0                       
.123                  0          3                       
-123.3                4          1     -123.3     -123.3 
123456.789            6          3                       
123456.789            7          3                       
101.23253232          3          8                       
101.23253232         11          8 101.232532 101.232532 

使用WHEN OTHERS并不理想，您可以将其替换为特定的异常处理程序.我以为如果数字无效，您希望它返回null，但是您当然可以返回任何内容，也可以抛出自己的异常.

Using WHEN OTHERS isn't ideal and you could replace that with specific exception handlers. I've assumed you want this to return null if the number isn't valid, but of course you could return anything, or throw your own exception.

isNum2列来自第二个简单得多的函数，它只是动态地进行类型转换-我知道您不想这样做，这只是为了进行比较:

The isNum2 column is from a second, much simpler function, which is just doing the cast dynamically - which I know you don't want to do, this is just for comparison:

CREATE OR REPLACE FUNCTION IsNumber2(pVALUE VARCHAR2, pPRECISION NUMBER,
  pSCALE NUMBER) RETURN NUMBER
IS
  str VARCHAR2(80);
  num NUMBER;
BEGIN
  str := 'SELECT CAST(:v AS NUMBER(' || pPRECISION ||','|| pSCALE ||')) FROM DUAL';
  EXECUTE IMMEDIATE str INTO num USING pVALUE;
  RETURN num;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END IsNumber2;
/

但是请注意，如果指定的比例尺对于该值来说太小，则cast会四舍五入；我可能在这个问题中对符合"的解释过于强烈，因为在这种情况下我犯了错误.如果您希望允许使用'.123', 2, 2之类的东西(给出.12)，则可以从我的IsNumber中删除第二个GetFormat调用和"scale too large"(缩放太大)检查.可能还有其他细微之处我想念或误解了.

But note that cast rounds if the specified scale is too small for the value; I may have interpreted "conforms to" too strongly in the question as I'm erroring in that case. If you want something like '.123', 2, 2 to be allowed (giving .12) then the second GetFormat call and the 'scale too large' check can be removed from my IsNumber. There may be other nuances I've missed or misinterpreted as well.

还值得注意的是，初始to_number()依赖于NLS设置的数据和会话匹配-特别是小数点分隔符；并且不允许使用组分隔符.

Also worth noting that the initial to_number() relies on NLS settings for the data and the session matching - the decimal separator particularly; and it won't allow a group separator.

将传递的数值解构为内部表示并查看它是否与精度和小数位数相比可能更简单...尽管动态路由节省了大量时间和精力.

It might be simpler to deconstruct the passed numeric value into its internal representation and see if that compares with the precision and scale... though the dynamic route saves a lot of time and effort.

这篇关于具有精度和规模的Oracle自定义IsNumber函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！