如何将光标内的记录转换为选择语句的列名

Question

我正在尝试计算给定表的每个列的空值数。

我的光标（T_COL_NAME）保存着我的桌子中的列列表。

--select count(*) from sh.costs where promo_id is null  result:78425
DECLARE 
  null_count number;    
  t_col_name ALL_TAB_COLUMNS.COLUMN_NAME%type; 
  CURSOR c_column_name is 
      select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME = 'COSTS';
BEGIN 
    dbms_output.put_line('Null Count');  
    OPEN c_column_name; 
    LOOP 
    FETCH c_column_name into t_col_name; 
      EXIT WHEN c_column_name%notfound; 
    select count(*) into v_count from sh.costs where t_col_name is 
    null;
    dbms_output.put_line(t_col_name || ' '|| null_count); 
    END LOOP; 
    CLOSE c_column_name;   
END;

实际结果：

Null count
PROD_ID 0
TIME_ID 0
PROMO_ID 0
CHANNEL_ID 0
UNIT_COST 0
UNIT_PRICE 0

预期结果：

Null count
PROD_ID 0
TIME_ID 0
PROMO_ID 78425
CHANNEL_ID 0
UNIT_COST 0
UNIT_PRICE 0

问题来自我的SQL查询：

select count(*) into v_count from sh.costs where t_col_name is null;

t_col_name作为字符串而不是列名传递。

当我将查询更改为实际的列名时，我会得到正确的计数，即：

select count(*) into v_count from sh.costs where promo_id is null;
Null count
PROD_ID 78425
TIME_ID 78425
PROMO_ID 78425
CHANNEL_ID 78425
UNIT_COST 78425
UNIT_PRICE 78425

Answer 1

以下是：它是 dynamic sql 您需要的，因为您必须将（表和）列名称传递给 SELECT语句。

SQL> declare
  2    l_cnt number;
  3  begin
  4    for cur_r in (select table_name, column_name
  5                  from user_tab_columns
  6                  where table_name in ('EMP', 'DEPT')
  7                 )
  8    loop
  9      execute immediate 'select count(*) from ' || cur_r.table_name ||
 10                        ' where ' || cur_r.column_name || ' is null'
 11              into l_cnt;
 12      dbms_output.put_line(rpad(cur_r.table_name ||'.'|| cur_r.column_name, 20, ' ')
 13         ||': ' || l_cnt);
 14    end loop;
 15  end;
 16  /
DEPT.LOC            : 0
DEPT.DEPTNO         : 0
DEPT.DNAME          : 0
EMP.EMPNO           : 0
EMP.ENAME           : 0
EMP.JOB             : 0
EMP.MGR             : 1
EMP.HIREDATE        : 0
EMP.SAL             : 0
EMP.COMM            : 9
EMP.DEPTNO          : 0
PL/SQL procedure successfully completed.
SQL>