同样的一句SQL语句在pl/sql 代码块中count 没有数据，但是直接用SQl 执行却可以count 得到结果...

pl/sql 代码块：

SELECT count(distinct t2.so_nbr)
INTO v_count2
FROM KFGL_YW_STEP_qd t2
WHERE t2.partition_id_month=to_number(substr(p_month, 5, 2))
AND t2.area_id = cur_area.area_id
AND t2.prod_id in (20201010,20201011,20202011,20203030);

 

sql 直接运行

SELECT count(distinct t2.so_nbr)
FROM KFGL_YW_STEP_qd t2
WHERE t2.partition_id_month=to_number(substr(p_month, 5, 2))
AND t2.area_id = cur_area.area_id
AND t2.prod_id in (20201010,20201011,20202011,20203030);

 

前面的返回结果是零，后面正常返回（条件一致）。

而后将pl/sql 代码快改为动态sql 

EXECUTE IMMEDIATE 'SELECT count(distinct t2.so_nbr) FROM KFGL_YW_STEP_qd t2
WHERE t2.partition_id_month=to_number(substr('||p_month||', 5, 2))
AND t2.area_id = '||cur_area.area_id||'
AND t2.prod_id in (20201010,20201011,20202011,20203030)' INTO v_count2;

正常返回结果。

 

初步怀疑是不是和oracle的统计信息有关。之前改过表结构，rename 过表。

 

转载于:https://www.cnblogs.com/Alex-Zeng/p/3180043.html