如何使用 PL/SQL 循环遍历列

Question

我查了一下，只发现这个问题： 循环遍历列 SQL 它在某些方面很相似，但不涉及 PL/SQL 和 Oracle 数据库，因此我提出新问题。

我有一张桌子。 2000 行和 600 列。有些列每行中仅包含 NULL。我想要做的是编写一个 PL/SQL 过程来从表中删除这些列。 所以我遇到了一个问题，我想在 all_tab_columns 视图的帮助下循环遍历 PL/SQL 中的列。您可以在下面看到我的代码（我的表名称是PreparedDocumentFeaturesValues）：

PROCEDURE dropNullColumns AS
   l_query VARCHAR2(10000);
   all_row_count NUMBER;
   null_row_count NUMBER;
BEGIN
   SELECT count(*) 
   INTO all_row_count 
   FROM PreparedDocumentFeaturesValues;

   FOR columnItem IN (SELECT column_name 
                      FROM all_tab_columns 
                      WHERE TABLE_NAME = UPPER('PreparedDocumentFeaturesValues'))
   LOOP
      SELECT count(*) 
      INTO null_row_count 
      FROM PreparedDocumentFeaturesValues 
      WHERE columnItem.column_name IS NULL;

      IF all_row_count=null_row_count THEN 
         l_query := 'ALTER TABLE PreparedDocumentFeaturesValues DROP COLUMN ' || columnItem.column_name;
         EXECUTE IMMEDIATE l_query;
      END IF;
   END LOOP;
END;

问题是该语句：

SELECT count(*) 
INTO null_row_count 
FROM PreparedDocumentFeaturesValues 
WHERE columnItem.column_name IS NULL;

具有字符类型作为column_name并且null_row_count始终等于0。

我很确定，有人知道我如何应对这个问题（通过改进上面的代码，或者也许还有其他方法可以做到这一点？> 预先感谢您的帮助。

Answer 1

我相信您想要

execute immediate 'SELECT count(*) FROM PreparedDocumentFeaturesValues WHERE '|| columnItem.column_name||' IS NULL' into null_row_count;

这是一个更完整的答案，它比您上面的答案更高效。

DVLP SQL>create table foo as select * from dba_objects where rownum < 10;

Table created.

DVLP SQL>update foo set status = null;

9 rows updated.

DVLP SQL>
DVLP SQL>declare
  2    tab_name constant varchar2(32) := 'foo';
  3    not_null number;
  4  begin
  5      for x in (select column_name from all_tab_columns where table_name = upper(tab_name)) loop
  6        dbms_output.put('Checking '||tab_name||'.'||x.column_name);
  7        begin
  8          execute immediate 'select 1 from (select 1 from '||tab_name||
  9            ' where '||x.column_name||' is not null) where rownum = 1' into not_null;
 10          dbms_output.put_line('.');
 11        exception when NO_DATA_FOUND then
 12          dbms_output.put_line('...all null.');
 13        end;
 14      end loop;
 15  end;
 16  /
Checking foo.OWNER.
Checking foo.OBJECT_NAME.
Checking foo.SUBOBJECT_NAME...all null.
Checking foo.OBJECT_ID.
Checking foo.DATA_OBJECT_ID.
Checking foo.OBJECT_TYPE.
Checking foo.CREATED.
Checking foo.LAST_DDL_TIME.
Checking foo.TIMESTAMP.
Checking foo.STATUS...all null.
Checking foo.TEMPORARY.
Checking foo.GENERATED.
Checking foo.SECONDARY.
Checking foo.NAMESPACE.
Checking foo.EDITION_NAME...all null.

Answer 2

由于您在编译时不知道列名称，因此您的查询也需要使用动态 SQL。 这还有一个

PROCEDURE dropNullColumns AS
   l_query VARCHAR2(10000);
   all_row_count NUMBER;
   null_row_count NUMBER;
BEGIN
   SELECT count(*) 
     INTO all_row_count 
     FROM PreparedDocumentFeaturesValues;
   FOR columnItem IN (SELECT column_name 
                        FROM all_tab_columns 
                       WHERE TABLE_NAME = UPPER('PreparedDocumentFeaturesValues'))
   LOOP
      l_query := 'SELECT COUNT(*) ' ||
                 '  FROM PreparedDocumentFeaturesValues ' ||
                 ' WHERE ' || columnItem.column_name || ' IS NULL';
      EXECUTE IMMEDIATE l_query
         INTO null_row_count;

      IF all_row_count=null_row_count THEN 
         l_query := 'ALTER TABLE PreparedDocumentFeaturesValues DROP COLUMN ' || columnItem.column_name;
         EXECUTE IMMEDIATE l_query;
      END IF;
   END LOOP;
END;

您可能还可以通过仅计算非 NULL 行来稍微简化逻辑，

PROCEDURE dropNullColumns AS
   l_query VARCHAR2(10000);
   not_null_row_count NUMBER;
BEGIN
   FOR columnItem IN (SELECT column_name 
                        FROM all_tab_columns 
                       WHERE TABLE_NAME = UPPER('PreparedDocumentFeaturesValues'))
   LOOP
      l_query := 'SELECT 1 from (SELECT COUNT(*) ' ||
                 '  FROM PreparedDocumentFeaturesValues ' ||
                 ' WHERE ' || columnItem.column_name || ' IS NOT NULL ' ||
                 '   ) WHERE rownum < 2';
      EXECUTE IMMEDIATE l_query
         INTO not_null_row_count;

      IF not_null_row_count=0 THEN 
         l_query := 'ALTER TABLE PreparedDocumentFeaturesValues DROP COLUMN ' || columnItem.column_name;
         EXECUTE IMMEDIATE l_query;
      END IF;
   END LOOP;
END;

好处是，如果您碰巧在任何列上有任何索引，则循环中的查询可能会使用这些索引。并且查询可以在找到单个非 NULL 值时立即停止，而不是扫描整个表。