sql - SELECT 查询因有效数字的 ORA-01722(无效数字)而失败

Question

我有一个包含配置选项的表。这些值存储为字符串，但可以表示不同类型的数据。 TYPE 列标识值的数据类型。 (STRING、INTEGER、FLOAT 或 BOOLEAN 之一)。

CREATE TABLE CONFIG (
    NAME  VARCHAR2(256) NOT NULL,
    TYPE  VARCHAR2(20)  NOT NULL,
    VALUE VARCHAR2(1024)
)

我正在尝试运行包含数字比较的查询，例如

SELECT NAME, VALUE FROM CONFIG
WHERE TYPE = 'INTEGER' AND to_number(VALUE) > 100

但我的查询失败并显示“ORA-01722:无效数字”。所有值(对于类型为 INTEGER 的记录)都不包含无效字符。为什么我会收到“无效数字”错误以及如何修复我的查询？

Answer 1

SQL 没有定义谓词(WHERE 子句的条件)的计算顺序。这样的查询是否“有效”取决于服务器选择的执行计划。影响计划的查询更改可能会导致此错误在没有明显原因的情况下出现和消失。

最好的解决方案是不要将数字数据存储在字符串 (VARCHAR) 字段中。

假设这不是您需要保护对 to_number 的调用的选项。您可以使用 CASE 子句来做到这一点。

SELECT NAME, VALUE FROM CONFIG
WHERE (
  CASE TYPE WHEN 'INTEGER'
  THEN to_number(VALUE)
  ELSE NULL
  END) > 100