检查所有表中所有列中的空格或零。

Question

Oracle架构中有多个表，其列的值不正确：

• 值为零的列数，而不是null。
• 值为空格的文本列，而不是null。

我正在清理数据。我最终将在列上创建约束，以防止首先输入错误的数据。但是在我这样做之前，我需要找到存在问题的列并对它们进行调查。

首先，我希望生成一个列的列表，这些列的值不正确。

• 对于架构中的每个表，提供以零或空格作为值的列的列表。

—————————————————————————————————-————————————-———————- table_name column_name数据类型值计数道路宽度编号10000 500人行道描述varchar2 空格 10000人行道const_year编号0 2000

问题：

在Oracle中生成这样的列表需要什么？

(注:我白天是公共工程技术员，不是数据库管理员。)外行人的条件将不胜感激。)

Answer 1

您应该考虑查询基于信息模式的表(如ALL_TAB_COLUMNS或DBA_TAB_COLUMNS)，以获得所有表和所有列的列表：https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/ALL_制表符_COLUMNS.html#GUID-F218205C-7D76-4A83-8691-BFD2AD372B63

https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/DBA_制表符_COLUMNS.html#GUID-91F945AE-5778-45A6-A07E-775A006A9AA1

然后研究如何编写动态SQL以轻松生成查询每个表的每一列所需的代码：https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/dynamic-sql.html#GUID-7E2F596F-9CA3-4DC8-8333-0C117962DB73。

下面是一些关于创建动态SQL时的思想过程的psuedo代码(*注意，这不是Oracle PL/SQL语法，因此只需将其作为如何构建动态SQL的一般示例，并主要关注步骤3)：

-- 1) Create a temp table to hold the results
DROP TABLE IF EXISTS #Results
CREATE TABLE #Results (TableName VARCHAR(100), ColumnName VARCHAR(100), [Value] VARCHAR(100) NULL)

-- 2) Dynamic SQL variable used to build the dynamic SQL string
DECLARE @DynamicSQL AS NVARCHAR(MAX) = ''

-- 3) Build the dynamic SQL string using the DBA_TAB_COLUMNS information schema view. First query gets any row whose column has 0 as a value, second query gets any row whose column has a NULL value
SELECT @DynamicSQL = @DynamicSQL + '
    INSERT INTO #Results 
    SELECT ''' + TABLE_NAME+ ''' AS TableName, ''' + COLUMN_NAME + ''' AS ColumnName, [' + COLUMN_NAME + '] AS ColumnValue 
    FROM [' + TABLE_NAME+ '] 
    WHERE [' + COLUMN_NAME + '] = ''0'';' 
    + '
    INSERT INTO #Results 
    SELECT ''' + TABLE_NAME + ''' AS TableName, ''' + COLUMN_NAME + ''' AS ColumnName, [' + COLUMN_NAME + '] AS ColumnValue 
    FROM [' + TABLE_NAME + '] 
    WHERE [' + COLUMN_NAME + '] IS NULL;'
FROM sys.DBA_TAB_COLUMNS

-- 4) Execute the dynamic SQL string
EXEC sp_ExecuteSQL @DynamicSQL

-- 5) Select the final results, grouping up on TableName, ColumnName, and Value to get the counts of 0 and NULL values per column per table
SELECT R.TableName, R.ColumnName, TC.DATA_TYPE AS DataType, R.[Value], COUNT(1) AS [Count]
FROM #Results AS R
INNER JOIN sys.DBA_TAB_COLUMNS AS TC
    ON R.TableName = TC.TableName
    AND R.ColumnName = R.ColumnName
GROUP BY R.TableName, R.ColumnName, R.[Value]

Answer 2

我想出的是：

--DROP TABLE incorrect_value_results;
--CREATE TABLE incorrect_value_results (id NUMBER, table_name VARCHAR2(30), column_name VARCHAR2(30), val_count NUMBER, value varchar2(30));
TRUNCATE TABLE incorrect_value_results;

DECLARE
   l_count NUMBER;
   l_inv_char_str VARCHAR2(2000);
   TYPE invalid_char_val_rec IS RECORD(
      cnt      NUMBER,
      inv_char VARCHAR2(20)
   );
   TYPE invalid_char_val_tab IS TABLE OF invalid_char_val_rec
      INDEX BY PLS_INTEGER;
   l_inv_char_vals invalid_char_val_tab;
   l_index NUMBER;
BEGIN
   l_index := 0;
   -- Loop through each table in the schema
   FOR i IN (SELECT table_name
               FROM user_tables) 
   LOOP
      -- Loop through each relevant column for this table
      -- Exclude column EVT_FROM
      FOR j IN (SELECT column_name, data_type
                  FROM user_tab_cols
                 WHERE table_name = i.table_name
                   AND column_name  NOT IN ('EVT_FROM','ANGLE','UND','OFFSET')
                   AND table_name   NOT LIKE 'A_%'
                   AND table_name   NOT LIKE 'SDE%'
                   AND table_name   NOT LIKE '%ANNO%'
                   AND table_name   NOT IN ('INCORRECT_VALUE_RESULTS','NUMBERS')
                   AND data_type    IN ('VARCHAR2', 'CHAR', 'NCHAR', 'NVARCHAR2', 'NUMBER')) 
      LOOP
         IF j.data_type IN ('VARCHAR2', 'CHAR', 'NCHAR', 'NVARCHAR2') THEN
            EXECUTE IMMEDIATE 
               'SELECT COUNT(1), '||j.column_name||
                ' FROM '||i.table_name|| 
                ' WHERE UPPER('||j.column_name||') IN('' '', ''  '', ''0'', ''-'', ''NULL'', ''<NULL>'' )
                  GROUP BY '||j.column_name
                 BULK COLLECT INTO l_inv_char_vals;
            
         ELSIF j.data_type = 'NUMBER' THEN
            EXECUTE IMMEDIATE 
               'SELECT COUNT(1), '||j.column_name||
                ' FROM '||i.table_name|| 
                ' WHERE UPPER('||j.column_name||') <= 0
                  GROUP BY '||j.column_name
                 BULK COLLECT INTO l_inv_char_vals;
         END IF;     
         -- If there are results then log them
         l_index := l_index + 1;
         FORALL k IN 1..l_inv_char_vals.COUNT
            INSERT INTO incorrect_value_results (id, table_name, column_name, val_count, value)
            VALUES (l_index,
                    i.table_name, 
                    j.column_name, 
                    l_inv_char_vals(k).cnt,
                    l_inv_char_vals(k).inv_char);
      END LOOP;
   END LOOP;
END;
/
COMMIT;