存储过程如下:
create or replace procedure pro_mms_user_monthis 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.这个值是我想要的正确结果。
create or replace procedure pro_mms_user_monthis 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.这个值是我想要的正确结果。
解决方案 »
- oracle 如何随机取一条记录
- 动态sql语句报java.sqlException 报无效索引列
- How to migrate R12 from AIX5.3 64位系统 to LinuxAS 464位系统
- oracle存储过程wrapped后如何可以查看它的源码?
- 一个简单问题,关于sql语句
- 怎么样确定身份证15位18位是相同的人
- 用户权限表的设计
- 散分了,请教各位大虾,有关Oracle的简单问题,凡回答者均送分!
- 求教 如何将一个字段查询出的多个结果拼接到一起
- linux7.3安装Oracle10客户端时,系统提示ERROR in writing to directory /tmp/OraInstll.....
- 求一功能实现的SQL
- 求助一查询语句
同样的一个存储过程中的语句,只要没有用 minus 取差集的用变量 “starttime” 等 得到的值都是正确的。搞不懂。求解答。谢谢上面两位的解答,谢谢。