我們想計算表中每列有多少空值。列太多，無法一一進行，因此創建了以下 PLSQL 程序。
在該程序的第一部分，獲取所有列名。這很有效，因為 dbms_output 正確地列出了它們。
其次，查詢在變數“nullscount”中插入空值的計數。這部分不起作用，因為為該變數列印的輸出始終為 0，即使對于我們知道存在空值的列也是如此。
有誰知道如何正確處理第二部分？
非常感謝。
CREATE OR REPLACE PROCEDURE COUNTNULLS AS
nullscount int;
BEGIN
for c in (select column_name from all_tab_columns where table_name = upper('gp'))
loop
select count(*) into nullscount from gp where c.column_name is null;
dbms_output.put_line(c.column_name||' '||nullscount);
end loop;
END COUNTNULLS;
uj5u.com熱心網友回復：
你可以通過這樣的一個查詢來獲得它：這個查詢只掃描一次表：
DBFiddle：https ://dbfiddle.uk/asgrCezT
select *
from xmltable(
'/ROWSET/ROW/*'
passing
dbms_xmlgen.getxmltype(
(
select
'select '
||listagg('count(*)-count("'||column_name||'") as "'||column_name||'"',',')
||' from '||upper('gp')
from user_tab_columns
where table_name = upper('gp')
)
)
columns
column_name varchar2(30) path './name()',
cnt_nulls int path '.'
);
結果：
COLUMN_NAME CNT_NULLS
------------------------------ ----------
A 5
B 4
C 3
此查詢中的動態 sql 使用 (24 chars column name length) 所以它應該可以正常作業，例如平均列名長度 = 10 的 117 列。如果你需要更多，你可以重寫一下，例如：
select *
from xmltable(
'let $cnt := /ROWSET/ROW/CNT
for $r in /ROWSET/ROW/*[name() != "CNT"]
return <R name="{$r/name()}"> {$cnt - $r} </R>'
passing
dbms_xmlgen.getxmltype(
(
select
'select count(*) CNT,'
||listagg('count("'||column_name||'") as "'||column_name||'"',',')
||' from '||upper('gp')
from user_tab_columns
where table_name = upper('gp')
)
)
columns
column_name varchar2(30) path '@name',
cnt_nulls int path '.'
);
uj5u.com熱心網友回復：
create table gp (
id number generated by default on null as identity
constraint gp_pk primary key,
c1 number,
c2 number,
c3 number,
c4 number,
c5 number
)
;
-- add some data with NULLS and numbers
DECLARE
BEGIN
FOR r IN 1 .. 20 LOOP
INSERT INTO gp (c1,c2,c3,c4,c5) VALUES
(CASE WHEN mod(r,2) = 0 THEN NULL ELSE mod(r,2) END
,CASE WHEN mod(r,3) = 0 THEN NULL ELSE mod(r,3) END
,CASE WHEN mod(r,4) = 0 THEN NULL ELSE mod(r,4) END
,CASE WHEN mod(r,5) = 0 THEN NULL ELSE mod(r,5) END
,5);
END LOOP;
END;
/
-- check what is in the table
SELECT * FROM gp;
-- do count of each column
DECLARE
l_colcount NUMBER;
l_statement VARCHAR2(100) := 'SELECT COUNT(*) FROM $TABLE_NAME$ WHERE $COLUMN_NAME$ IS NULL';
BEGIN
FOR r IN (SELECT column_name,table_name FROM user_tab_columns WHERE table_name = 'GP') LOOP
EXECUTE IMMEDIATE REPLACE(REPLACE(l_statement,'$TABLE_NAME$',r.table_name),'$COLUMN_NAME$',r.column_name) INTO l_colcount;
dbms_output.put_line('Table: '||r.table_name||', column'||r.column_name||', COUNT: '||l_colcount);
END LOOP;
END;
/
Table created.
Statement processed.
Result Set 4
ID C1 C2 C3 C4 C5
1 1 1 1 1 5
2 - 2 2 2 5
3 1 - 3 3 5
4 - 1 - 4 5
5 1 2 1 - 5
6 - - 2 1 5
7 1 1 3 2 5
8 - 2 - 3 5
9 1 - 1 4 5
10 - 1 2 - 5
11 1 2 3 1 5
12 - - - 2 5
13 1 1 1 3 5
14 - 2 2 4 5
15 1 - 3 - 5
16 - 1 - 1 5
17 1 2 1 2 5
18 - - 2 3 5
19 1 1 3 4 5
20 - 2 - - 5
20 rows selected.
Statement processed.
Table: GP, columnID, COUNT: 0
Table: GP, columnC1, COUNT: 10
Table: GP, columnC2, COUNT: 6
Table: GP, columnC3, COUNT: 5
Table: GP, columnC4, COUNT: 4
Table: GP, columnC5, COUNT: 0
uj5u.com熱心網友回復：
c.column_name 永遠不會為空，因為它是表“all_tab_columns”的列“column_name”的內容，而不是表 gp 中名稱為 c.column_name 的值的列。您必須使用動態查詢和 EXECUTE IMMEDIATE 來實作您想要的。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/508619.html
標籤：sql甲骨文plsql