oracle 如何使用 PL/SQL 遍历列

How to loop through columns with PL/SQL

提问by Jaros?aw Drabek

I have searched through and found only this problem: Loop through columns SQLit's similar in some ways, but doesn't concern PL/SQL and Oracle Database, therefore I'm Asking new Question.

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

I have a table with ca. 2000 rows and 600 columns. There are some columns comprised only NULLs in each row. What I want to do is to write a PL/SQL Procedure to remove those columns from the table. So I have met a problem, I wanted to loop through columns in PL/SQL with help of all_tab_columns view. You can see my code below (my table name is PreparedDocumentFeaturesValues):

我有一张桌子。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;

The problem is that statement:

问题是那句话：

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

has character type as a column_name and null_row_count always equals 0.

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

I'm pretty sure, here is somebody who know how can I cope with this problem (by improving the code above, or maybe is there any other way to do such a thing?> Thank you in advance for your help.

我很确定，这里有人知道我该如何解决这个问题（通过改进上面的代码，或者有没有其他方法可以做到这一点？> 在此先感谢您的帮助。

回答by Justin Cave

Since you don't know the column name at compile time, your query would need to use dynamic SQL as well. Something like

由于您在编译时不知道列名，因此您的查询也需要使用动态 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;

You could probably also simplify the logic a bit by just counting the non-NULL rows

您也可以通过仅计算非 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;

This also has the benefit that if you happen to have any indexes on any columns, the queries in the loop could potentially use those. And the query can stop as soon as it finds a single not-NULL value rather than scanning the entire table.

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

回答by RichardJQ

I believe you want

我相信你想要

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

Here's a more complete answer that will be more performant than what you have above.

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

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.