Oracle PL/SQL中col_nm变量在两种代码版本中行为差异原因咨询

你编写的两个PL/SQL块，核心差异在于静态SQL与动态SQL的标识符解析逻辑不同，导致第一个版本所有计数为0，第二个版本能得到正确结果。

版本一（静态SQL，计数全为0） #

declare
  rec_count number;
  col_nm sys.all_tab_columns.column_name%type;
  
begin
  for col in (select column_name
              from sys.all_tab_columns
              where table_name = 'WEID_DIR_PERSON_00_MV'
              order by column_name)
    loop
      col_nm := col.column_name;
      select count(*) into rec_count
      from csuban.weid_dir_person_00_mv a
      where a.account_type = 'P'
        and a.active_flag_eiddata = 'Y'
        and a.admin_acct_flag <> 'Y'
        and col_nm <> (select col_nm
                       from csuban.midp_weid_dir_person_mv b 
                       where b.account_type = 'P'
                         and b.active_flag_eiddata = 'Y'
                         and b.admin_acct_flag <> 'Y'
                         and a.csu_id = b.csu_id);
      dbms_output.put_line(col_nm||': '||rec_count);
    end loop;
end;

版本二（动态SQL，结果正确） #

declare
  rec_count number;
  col_nm sys.all_tab_columns.column_name%type;
  sqlstring varchar(4000);
  
begin
  for col in (select column_name
              from sys.all_tab_columns
              where table_name = 'WEID_DIR_PERSON_00_MV'
              order by column_name)
    loop
      col_nm := col.column_name;
      sqlstring := 'select count(*) 
                    from csuban.weid_dir_person_00_mv a
                    where a.account_type = ''P''
                      and a.active_flag_eiddata = ''Y''
                      and a.admin_acct_flag <> ''Y''
                      and '||col_nm||' <> (select '||col_nm||'
                                             from csuban.midp_weid_dir_person_mv b 
                                             where b.account_type = ''P''
                                               and b.active_flag_eiddata = ''Y''
                                               and b.admin_acct_flag <> ''Y''
                                               and a.csu_id = b.csu_id)';
      EXECUTE IMMEDIATE (sqlstring) into rec_count;
      dbms_output.put_line(col_nm||': '||rec_count);     
    end loop;
end;

核心原因解析 #

版本一（静态SQL）的问题 #

静态SQL在PL/SQL编译阶段就会完成标识符的绑定与解析，遵循优先匹配表列名，再匹配PL/SQL变量的规则：

1. col_nm变量存储的是目标表的列名字符串（比如'CSU_ID'）。
2. 在静态SQL的条件col_nm <> (select col_nm ...)中，Oracle会先检查查询涉及的表（a和b）是否存在名为col_nm的列——显然你的两个MV都没有这个列，因此Oracle会将col_nm解析为PL/SQL变量的值。
3. 最终条件等价于：'CSU_ID' <> 'CSU_ID'（循环到哪个列名，两边就是相同的字符串），这个条件永远不成立，因此count(*)的结果必然是0。

版本二（动态SQL）的正确逻辑 #

动态SQL通过EXECUTE IMMEDIATE执行拼接后的SQL字符串，本质是将col_nm变量的值（列名字符串）替换进SQL语句：

1. 当col_nm的值为'CSU_ID'时，拼接后的SQL语句会变成：

   select count(*) 
   from csuban.weid_dir_person_00_mv a
   where a.account_type = 'P'
     and a.active_flag_eiddata = 'Y'
     and a.admin_acct_flag <> 'Y'
     and a.CSU_ID <> (select b.CSU_ID
                      from csuban.midp_weid_dir_person_mv b 
                      where b.account_type = 'P'
                        and b.active_flag_eiddata = 'Y'
                        and b.admin_acct_flag <> 'Y'
                        and a.csu_id = b.csu_id)
   

2. 此时执行的是比较两个表中对应行的CSU_ID列值是否不相等的逻辑，完全符合预期，因此能得到正确的计数结果。

内容的提问来源于stack exchange，提问作者alx