为什么同一条sql语句在存储过程中执行和单独执行的结果不一样

存储过程如下：
create or replace procedure pro_mms_user_month

is

  starttime VARCHAR2(14); --定义上个月开始时间
  endtime VARCHAR2(14); --定义上个月结束时间
  starttimelast VARCHAR2(14); --定义上上个月开始时间
  endtimelast VARCHAR2(14); --定义上上个月结束时间
  v_sumnewuser varchar2(100); --包月新增用户数（户）

begin
  --上个月开始时间
  starttime := to_char(last_day(add_months(SYSDATE, -2)) + 1, 'YYYYMMDD');
  --上个月结束时间
  endtime := to_char(last_day(add_months(SYSDATE, -1)), 'YYYYMMDD');

  --上上个月开始时间
  starttimelast := to_char(last_day(add_months(SYSDATE, -3)) + 1, 'YYYYMMDD');
  --上上个月结束时间
  endtimelast := to_char(last_day(add_months(SYSDATE, -2)), 'YYYYMMDD');
 
 --包月新增用户数（户）
select nvl(count(msisdn),0) into v_sumnewuser 
  from (  
  select distinct(a.msisdn)
  from subscribeinfo a, (select d.serviceid from serviceinfo d where d.type=3) b, chargerate c
  where a.serviceid = b.serviceid
  and a.CHARGECLASSIDX = c.chargeclassidx
  and c.chargemode = 0
  and a.state = 1
  and a.spid like '35%'
  and substr(a.ordertime, 0, 8) >= starttime
  and substr(a.ordertime, 0, 8) <= endtime
  minus
  select distinct(a.msisdn)
  from subscribeinfo a, (select d.serviceid from serviceinfo d where d.type=3) b, chargerate c
  where a.serviceid = b.serviceid
  and a.CHARGECLASSIDX = c.chargeclassidx
  and c.chargemode = 0
  and a.state = 1
  and a.spid like '35%'
  and substr(a.ordertime, 0, 8) >= starttimelast
  and substr(a.ordertime, 0, 8) <= endtimelast);
  insert into debuglog (name,value) values('v_sumnewuser',v_sumnewuser);
  commit;
 end;
存储过程插入到 debuglog 表中的数据是 v_sumnewuser 值是 59096
debuglog 表达建表语句是：create table DEBUGLOG
(
  NAME VARCHAR2(40),
  VALUE VARCHAR2(100)
); 

但是单独执行：select nvl(count(msisdn),0) as v_sumnewuser 

  from ( select distinct(a.msisdn)
  from subscribeinfo a, (select d.serviceid from serviceinfo d where d.type=3) b, chargerate c
  where a.serviceid = b.serviceid
  and a.CHARGECLASSIDX = c.chargeclassidx
  and c.chargemode = 0
  and a.state = 1
  and a.spid like '35%'
  and substr(a.ordertime, 0, 8) >= to_char(last_day(add_months(SYSDATE, -2)) + 1, 'YYYYMMDD')
  and substr(a.ordertime, 0, 8) <= to_char(last_day(add_months(SYSDATE, -1)), 'YYYYMMDD')
  minus
  select distinct(a.msisdn)
  from subscribeinfo a, (select d.serviceid from serviceinfo d where d.type=3) b, chargerate c
  where a.serviceid = b.serviceid
  and a.CHARGECLASSIDX = c.chargeclassidx
  and c.chargemode = 0
  and a.state = 1
  and a.spid like '35%'
  and substr(a.ordertime, 0, 8) >= to_char(last_day(add_months(SYSDATE, -3)) + 1, 'YYYYMMDD')
  and substr(a.ordertime, 0, 8) <= to_char(last_day(add_months(SYSDATE, -2)), 'YYYYMMDD'));
得到的 v_sumnewuser 的值是180.这个值是我想要的正确结果。

 

xuexi