如何使用PL/SQL遍历列 [英] How to loop through columns with PL/SQL

问题描述

我已搜索并仅发现以下问题: SQL列遍历 在某些方面相似，但是与PL/SQL和Oracle数据库无关，因此我要提出新问题.

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

我有一个带有ca的表. 2000行和600列.在某些行中，每行仅包含NULL.我想做的是编写一个PL/SQL过程以从表中删除那些列. 因此，我遇到了一个问题，我想在all_tab_columns视图的帮助下遍历PL/SQL中的列.您可以在下面看到我的代码(我的表名称是PreparedDocumentFeaturesValues):

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):

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.

has character type as a column_name and null_row_count always equals 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.

推荐答案

由于您在编译时不知道列名，因此您的查询也将需要使用动态SQL.像

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

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行来简化逻辑

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

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.

这篇关于如何使用PL/SQL遍历列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！