查询返回值为0 [英] Query Returning value as 0

问题描述

我正在尝试在SQL Developer中执行以下PL/SQL脚本.该循环应返回空计数，但每次返回0时都应以某种方式返回. 将服务器输出设置为

I am trying to execute following PL/SQL script in SQL Developer. The loop should return count of nulls but somehow everytime it is returning 0. set serveroutput on

DECLARE

--v_count number;
v_count_null number;

BEGIN
execute immediate 'select count(*) from SP_MOSAIX' into v_count;

FOR  i in (select column_name from all_tab_COLUMNS where table_name = 'SP_MOSAIX')
LOOP
select count(*) into v_count_null from SP_MOSAIX  where i.column_name IS NULL ;
dbms_output.put_line(v_count_null);

END LOOP;

END;

因此，当我运行此命令时，得到的输出如下:

So when I run this, following output is what i get:

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

但是，如果我手动执行替换column_name的查询，则会得到结果.

But if I manually execute the query subsituting column_name I get the result.

select count(*) into v_count_null from SP_MOSAIX  where i.column_name IS NULL; 

有人可以帮忙吗?

推荐答案

您需要注意一些事情.首先，正如您所暗示的那样，您的COUNT查询是使用i.column_name的值执行的，该值永远不会为NULL.

There are a couple of things going on here that you need to be aware of. Firstly, as you allude to, your COUNT query is executing using the value of i.column_name, which is never NULL.

第二，COUNT(*)返回与WHERE子句条件匹配的行的数量，而与NULL值无关.如果要计算特定列中有多少NOT NULL个值，则必须在该列中明确地显示COUNT个值.

Secondly, COUNT(*) returns the number of rows that match your WHERE clause condition, regardless of NULL values. If you want to count how many NOT NULL values there are in a particular column, you must COUNT values in that column explicitly.

请参见以下示例( SQL提琴):

Oracle 11g R2架构设置:

CREATE TABLE null_col_vals (
  col_without_nulls INTEGER NOT NULL
, col_with_nulls INTEGER
, col_with_mix INTEGER  
)
/

INSERT INTO null_col_vals (col_without_nulls, col_with_nulls, col_with_mix)
VALUES (1, NULL, NULL)
/

INSERT INTO null_col_vals (col_without_nulls, col_with_nulls, col_with_mix)
VALUES (1, NULL, 1)
/

INSERT INTO null_col_vals (col_without_nulls, col_with_nulls, col_with_mix)
VALUES (1, NULL, NULL)
/

查询1 :

SELECT 
  COUNT(col_without_nulls) col_without_nulls
, COUNT(col_with_nulls) col_with_nulls
, COUNT(col_with_mix) col_with_mix
, COUNT(*) all_rows
FROM null_col_vals

结果:

| COL_WITHOUT_NULLS | COL_WITH_NULLS | COL_WITH_MIX | ALL_ROWS |
----------------------------------------------------------------
|                 3 |              0 |            1 |        3 |

如您所见，COUNT(*)总是返回存在的行数，但其他行的结果取决于指定列中是否存在NULL值.

As you can see, COUNT(*) always returns the number of rows present, but the others vary in results depending on whether NULL values are present in the specified column or not.

您将需要使用EXECUTE IMMEDIATE将列名转换为查询的一部分.像这样的事情可能会完成这项工作:

You'll need to use EXECUTE IMMEDIATE to convert your column name into part of the query. Something like this might do the job:

查询2 :

DECLARE

  l_count INTEGER;

BEGIN

  FOR r_col IN (
    SELECT *
    FROM all_tab_columns atc
    WHERE atc.table_name = 'NULL_COL_VALS'
  )
  LOOP

    dbms_output.put_line(r_col.column_name);

    EXECUTE IMMEDIATE 'SELECT COUNT(' || r_col.column_name || ') FROM null_col_vals' 
    INTO l_count;

    dbms_output.put_line(l_count);

  END LOOP;

END;

这篇关于查询返回值为0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！