PL/SQL数值校验函数失效排查：判断varchar列是否全为数值异常

原函数的动态SELECT语句仅读取表的第一行数据，只要第一行内容能成功转换为数值，函数就直接返回1，完全不会检查后续行的内容。哪怕表中存在大量非数值数据，只要第一行符合要求，函数就会返回1，这就是不符合预期的核心原因。

方法一：通过正则表达式统计无效行数 #

这种方法直接统计列中不符合数值格式的行数，逻辑清晰且易于调整：

create or replace FUNCTION is_column_numeric (table_name IN VARCHAR2, column_name IN VARCHAR2)
   RETURN NUMBER
AS
   v_invalid_count NUMBER;
BEGIN
   EXECUTE IMMEDIATE(
      'SELECT COUNT(*) 
       FROM ' || table_name || ' 
       WHERE NOT REGEXP_LIKE(' || column_name || ', ''^-?\d*\.?\d+$'') 
         AND ' || column_name || ' IS NOT NULL'
   ) INTO v_invalid_count;
   
   RETURN CASE WHEN v_invalid_count > 0 THEN 0 ELSE 1 END;
EXCEPTION
   WHEN OTHERS THEN
      RETURN 0;
END;

说明 #

• 正则表达式^-?\d*\.?\d+$匹配正负整数、小数，可根据实际需求调整（比如是否允许科学计数法）。
• 排除了空值（原函数用NVL(col,0)将空值视为有效数值，这里保持一致逻辑）。

方法二：强制扫描所有行触发转换异常 #

这种方法和原函数逻辑更接近，通过MAX函数强制遍历所有行，只要有一行转换失败就返回0：

create or replace FUNCTION is_column_numeric (table_name IN VARCHAR2, column_name IN VARCHAR2)
   RETURN NUMBER
AS
   v_dummy NUMBER;
BEGIN
   EXECUTE IMMEDIATE(
      'SELECT MAX(TO_NUMBER(nvl(' || column_name || ', 0))) 
       FROM ' || table_name
   ) INTO v_dummy;
   
   RETURN 1;
EXCEPTION
   WHEN OTHERS THEN
      RETURN 0;
END;

说明 #

• MAX函数需要遍历所有行才能计算结果，因此任何一行无法转换为数值时，TO_NUMBER会抛出异常，函数进入异常块返回0。

• 原函数名拼写错误：is_column_numberic应为is_column_numeric，建议修正避免混淆。
• 动态SQL存在SQL注入风险，如果table_name和column_name来自不可信输入，需添加校验逻辑，比如使用DBMS_ASSERT.QUALIFIED_SQL_NAME(table_name)验证对象名合法性。

内容的提问来源于stack exchange，提问作者Tushar V