我想在我的Oracle数据库模式中找到仅包含数字数据但具有非数字类型的所有列。（因此，基本上是带有可能错误选择的数据类型的列候选。）
我有一个查询所有varchar2-columns：
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM user_tab_cols
WHERE DATA_TYPE = 'VARCHAR2';
此外，我有一个查询来检查表myTable和列内的任何非数字数据myColumn：
SELECT 1
FROM myTable
WHERE NOT REGEXP_LIKE(myColumn, '^[[:digit:]]+$');
我喜欢以这样的方式组合两个查询，即第一个查询仅返回not exists第二个查询所在的行。
这里的主要问题是，第一个查询位于数据字典的meta层上，其中TABLE_NAME和COLUMN_NAME作为数据出现，而我identifiers在第二个查询中需要该数据作为（而不是作为数据）。
在伪SQL中，我有这样的想法：
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM user_tab_cols
WHERE DATA_TYPE = 'VARCHAR2'
AND NOT EXISTS
(SELECT 1 from asIdentifier(TABLE_NAME)
WHERE NOT REGEXP_LIKE(asIdentifier(COLUMN_NAME), '^[[:digit:]]+$'));
创建一个函数，如下所示：
create or replace function isNumeric(val in VARCHAR2) return INTEGER AS
res NUMBER;
begin
res := TO_NUMBER(val);
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
然后，您可以像这样使用它：
DECLARE
r integer;
BEGIN
For aCol in (SELECT TABLE_NAME, COLUMN_NAME FROM user_tab_cols WHERE DATA_TYPE = 'VARCHAR2') LOOP
-- What about CHAR and CLOB data types?
execute immediate 'select count(*) from '||aCol.TABLE_NAME||' WHERE isNumeric('||aCol.COLUMN_NAME||') = 0' into r;
if r = 0 then
DBMS_OUTPUT.put_line(aCol.TABLE_NAME ||' '||aCol.COLUMN_NAME ||' contains numeric values only');
end if;
end loop;
end;
注意，此PL / SQL块的性能将很差。希望这只是一次工作。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句