oracle - 如何使用 PL/SQL 遍历列

Question

我已经搜索并只发现了这个问题:
Loop through columns SQL
它在某些方面很相似，但不涉及 PL/SQL 和 Oracle 数据库，因此我在问新问题。

我有一张 table 。 2000 行和 600 列。有一些列在每行中仅包含 NULL。我想要做的是编写一个 PL/SQL 过程来从表中删除这些列。
所以我遇到了一个问题，我想在 all_tab_columns View 的帮助下遍历 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.