oracle - PlSql Translate函数问题

Question

我创建了一个游标来选择实际数据，并循环输出原始值和转换为数字后的值。应用程序偶尔会抛出无效数字错误。下面是我的测试(不包括 select 语句)代码和输出。

LOOP
  FETCH myCursor into v_answer;
  EXIT WHEN myCursor%notfound;

  DBMS_OUTPUT.PUT_LINE('Raw answer: ' || v_answer );

  v_instr := INSTR(v_answer, '.',1 , 2) ;
  v_number := TO_NUMBER(REPLACE(TRANSLATE (CASE v_instr 
                                             WHEN 0 THEN UPPER(v_answer)
                                             ELSE 0 
                                           END,'ABCDEFGHIJKLMNOPQURSTWVXYZ+<>:',' '), ' ',''));

   DBMS_output.put_line('As number: ' || v_number);

这是输出:

Raw answer: 4
As number: 4
Raw answer: 3
As number: 3
Raw answer: 1.00
As number: 1
Raw answer: <3

我收到:

PL/SQL: numeric or value error: character to number conversion error

...当原始答案为“<3”时。

请注意，应用程序使用的实际代码如下所示:

AND TO_NUMBER(REPLACE(TRANSLATE ( decode( INSTR(hra_ans.answer_text, '.',1 , 2), 0 , UPPER(hra_ans.answer_text) , 0),'ABCDEFGHIJKLMNOPQURSTWVXYZ+<>:',' '), ' ','')) 

and 是动态 sql 字符串中 where 子句的一部分。我已经用 case 语句替换了 decode 语句，因为我得到了一个函数或伪列 'DECODE' may be used inside a SQL statement only 错误。

最后，我的问题是:

1. 为什么翻译函数没有替换小于号和
2. ORA-1722 和 ORA-06502 错误之间有什么区别(通俗地说)？

编辑:我注意到当我将 case 语句更改为:

CASE v_instr 
                                                 WHEN 0 THEN UPPER(v_answer)
                                                 ELSE '0'

,

我不再收到 06502 错误。通过查看我发布的原始代码行，对于可能导致无效数字错误的原因是否有任何建议(假设不考虑要翻译的字符串中不存在任何字符)？或者，是否有更好的方法来完成原始开发人员试图做的事情？

这里是变量声明:

v_answer varchar2(2000);
v_number number;
v_instr number;

Answer 1

首先，TRANSLATE 没有替换“<”符号，因为它没有机会。 CASE 语句在一种情况下评估为数字，在另一种情况下评估为字符。如果您的 CASE 输出是一致的，我相信您的错误就会消失:

  v_number := TO_NUMBER(REPLACE(TRANSLATE (CASE TO_CHAR(v_instr) 
                                             WHEN '0' THEN UPPER(v_answer)
                                             ELSE '0' 
                                           END,'ABCDEFGHIJKLMNOPQURSTWVXYZ+<>:',' '), ' ',''));

来自 this post on asktom.com :

ORA-1722 is Invalid number. We've attempted to either explicity or implicitly convert a character string to a number and it is failing.

This can happen for a number of reasons. It generally happens in SQL only (during a query) not in plsql (plsql throws a different exception for this error).

编辑:

您对 regexp_replace 的使用看起来不错，但如果我对此进行编码，我会使用 CASE 而不是 DECODE，因为我认为它更易于阅读:

v_number := CASE WHEN INSTR(v_answer, '.',1 , 2) = 0 THEN -- has 0 or 1 period
                     TO_NUMER(REGEXP_REPLACE(v_answer,'[^0-9.]',''))
                 ELSE 0  -- has more than one period
            END;