plPL/SQL 函数用于检查列中的数据是否为数字

Question

我有几个表将 varchar 作为列的数据类型，但包含数值， 我为同一个问题写了一个pl/sql函数...但未按预期工作。

create or replace FUNCTION is_column_numberic (table_name IN VARCHAR2, column_name in VARCHAR2)
RETURN NUMBER
AS
BEGIN
EXECUTE IMMEDIATE('SELECT TO_NUMBER(nvl(' ||column_name|| ',0)) from ' ||table_name);
RETURN 1;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;

对于不包含数值的列，该函数应返回 0，但尽管列包含数值，但该函数每次都返回 1。

Answer 1

您必须将 select 中的值返回到绑定变量中，否则不会引发异常：

create or replace FUNCTION is_column_numberic (table_name IN VARCHAR2, column_name in VARCHAR2)
RETURN NUMBER
AS
val NUMBER:
BEGIN
EXECUTE IMMEDIATE('SELECT TO_NUMBER(nvl(' ||column_name|| ',0)) from ' ||table_name) 
into val;
RETURN 1;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;

我还建议修复您的表以使用正确的数据类型。

Answer 2

作为捕获异常的替代方法，在最新版本的 Oracle(自 12c 起)中，您可以使用validate_conversion()函数，该函数返回 1 表示成功，返回 0 表示失败：

create or replace FUNCTION is_column_numberic (table_name IN VARCHAR2, column_name in VARCHAR2)
RETURN NUMBER
AS
l_result NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT MIN(VALIDATE_CONVERSION(' ||column_name|| ' AS NUMBER)) from ' ||table_name
INTO l_result;
RETURN l_result;
END;
/

小提琴

我在它周围添加了MIN()，以便如果列中的任何值无法转换，则总体结果将为 0;如果所有值都可以，则它将为 1。我省略了NVL()因为如果有空值，您就无法真正说出这些值是否是数字，而且它在您的版本中没有任何作用;但是，如果您想将根本没有值的列视为数字或非数字，那么您可以执行NVL(MAX(...), 0)- 但没有数据，答案确实是"未知"，因此只要调用者知道这意味着什么，仅返回 null 可能是合适的。

正如 OldProgrammer 所说，你需要一个INTO子句 - 没有一个子句是你的版本永远不会抛出异常的原因。文档说：

注意：如果dynamic_sql_statement是SELECT语句，并且您同时省略了into_clause和bulk_collect_into_clause，则execute_immediate_statement永远不会执行。

该语句仍被解析，但查询未执行 - 因此它会报告语法错误(并且由于版本中when others，所有行都返回零)，但它不会访问表数据或尝试转换任何值，因此当您运行代码时它不会抱怨非数字值。