查询返回值为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屋!