这个是我的过程代码,比较乱:)
IS
CURSOR c_s IS
select "ARADMIN"."T385"."C1" ,
"ARADMIN"."T385"."C536871133" ,
"ARADMIN"."T385"."C536871149" ,
"ARADMIN"."T385"."C536871150" ,
"ARADMIN"."T385"."C630000045" ,
"ARADMIN"."T385"."C630000066" ,
"ARADMIN"."T385"."C536871119" ,
"ARADMIN"."T385"."C536870925" ,
to_char(to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')+C536871079 /86400,'yyyy-mm-dd hh24:mi:ss') BEG_TIME,
to_char(to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')+C536870923 /86400,'yyyy-mm-dd ') END_TIME,
cast(to_char(sysdate,'hh24') as int) HH,
cast(to_char(sysdate,'hh24') as int) MM
from "ARADMIN"."T385"
where "ARADMIN"."T385"."C536871119" is not null and "ARADMIN"."T385"."C630000066" is not null
and "ARADMIN"."T385"."C536871133" ='无线'
and to_char(to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')+C536870923 /86400,'yyyy')*10000 +
to_char(to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')+C536870923 /86400,'mm')*100+
to_char(to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')+C536870923 /86400,'dd')
=
to_char(sysdate,'yyyy')*10000+to_char(sysdate,'mm')*100+to_char(sysdate,'dd');
v_s c_s%ROWTYPE;
CARD_NO varchar2(250);
COTAGORY varchar2(250);
KBS number;
NOR_CNT number;
CHECK_CNT number;
SEC_COM varchar2(250);
FINTIME varchar2(250);
USETIME number;
CMT number;
FIR_COM varchar2(20);
HH number;
MM number;
USETIME1 number;
OVER_CNT number;
CNT number;
BEGIN
OPEN c_s;
LOOP
BEGIN
/**************************************/
FETCH c_s INTO v_s;
EXIT WHEN c_s%NOTFOUND;
CARD_NO:=v_s.C536871119 ;
COTAGORY:=v_s.C536871133 ;
KBS:=v_s.C536871149 ;
SEC_COM:=v_s.C630000066 ;
FINTIME:=v_s.END_TIME ;
USETIME:=v_s.C536871119 ;
CMT:=v_s.C536870925 ;
HH:=v_s.HH ;
MM:=v_s.MM ;
NOR_CNT:=v_s.C536871150 ;
CHECK_CNT:=v_s.C630000045 ;
CNT:=v_s.C536871150+v_s.C630000045+v_s.C536871149;
select distinct "ARADMIN"."T424"."C536870945" into FIR_COM
from "ARADMIN"."T424" where "ARADMIN"."T424"."C536870917" =SEC_COM;
if v_s.HH >=0 and v_s.HH <=6 then
USETIME:=v_s.C536871119-(6-1-v_s.HH);
USETIME1:=60-v_s.MM;
end if;
insert into "ARADMIN"."REPORT_GZ_KPI"
(
"ARADMIN"."REPORT_GZ_KPI"."CARD_NO",
"ARADMIN"."REPORT_GZ_KPI"."COTAGORY",
"ARADMIN"."REPORT_GZ_KPI"."KBS",
"ARADMIN"."REPORT_GZ_KPI"."NOR_CNT",
"ARADMIN"."REPORT_GZ_KPI"."CHECK_CNT",
"ARADMIN"."REPORT_GZ_KPI"."SEC_COM",
"ARADMIN"."REPORT_GZ_KPI"."USETIME",
"ARADMIN"."REPORT_GZ_KPI"."CMT",
"ARADMIN"."REPORT_GZ_KPI"."FINTIME",
"ARADMIN"."REPORT_GZ_KPI"."FIR_COM",
"ARADMIN"."REPORT_GZ_KPI"."OVER_CNT",
"ARADMIN"."REPORT_GZ_KPI"."CNT"
)
values
(
CARD_NO,
COTAGORY,
KBS,
NOR_CNT,
CHECK_CNT,
SEC_COM,
USETIME,
CMT,
FINTIME,
FIR_COM,
OVER_CNT,
CNT
);
commit;
END;
END LOOP;
CLOSE c_s;
commit;
END REPORT_DATA;
IS
CURSOR c_s IS
select "ARADMIN"."T385"."C1" ,
"ARADMIN"."T385"."C536871133" ,
"ARADMIN"."T385"."C536871149" ,
"ARADMIN"."T385"."C536871150" ,
"ARADMIN"."T385"."C630000045" ,
"ARADMIN"."T385"."C630000066" ,
"ARADMIN"."T385"."C536871119" ,
"ARADMIN"."T385"."C536870925" ,
to_char(to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')+C536871079 /86400,'yyyy-mm-dd hh24:mi:ss') BEG_TIME,
to_char(to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')+C536870923 /86400,'yyyy-mm-dd ') END_TIME,
cast(to_char(sysdate,'hh24') as int) HH,
cast(to_char(sysdate,'hh24') as int) MM
from "ARADMIN"."T385"
where "ARADMIN"."T385"."C536871119" is not null and "ARADMIN"."T385"."C630000066" is not null
and "ARADMIN"."T385"."C536871133" ='无线'
and to_char(to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')+C536870923 /86400,'yyyy')*10000 +
to_char(to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')+C536870923 /86400,'mm')*100+
to_char(to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')+C536870923 /86400,'dd')
=
to_char(sysdate,'yyyy')*10000+to_char(sysdate,'mm')*100+to_char(sysdate,'dd');
v_s c_s%ROWTYPE;
CARD_NO varchar2(250);
COTAGORY varchar2(250);
KBS number;
NOR_CNT number;
CHECK_CNT number;
SEC_COM varchar2(250);
FINTIME varchar2(250);
USETIME number;
CMT number;
FIR_COM varchar2(20);
HH number;
MM number;
USETIME1 number;
OVER_CNT number;
CNT number;
BEGIN
OPEN c_s;
LOOP
BEGIN
/**************************************/
FETCH c_s INTO v_s;
EXIT WHEN c_s%NOTFOUND;
CARD_NO:=v_s.C536871119 ;
COTAGORY:=v_s.C536871133 ;
KBS:=v_s.C536871149 ;
SEC_COM:=v_s.C630000066 ;
FINTIME:=v_s.END_TIME ;
USETIME:=v_s.C536871119 ;
CMT:=v_s.C536870925 ;
HH:=v_s.HH ;
MM:=v_s.MM ;
NOR_CNT:=v_s.C536871150 ;
CHECK_CNT:=v_s.C630000045 ;
CNT:=v_s.C536871150+v_s.C630000045+v_s.C536871149;
select distinct "ARADMIN"."T424"."C536870945" into FIR_COM
from "ARADMIN"."T424" where "ARADMIN"."T424"."C536870917" =SEC_COM;
if v_s.HH >=0 and v_s.HH <=6 then
USETIME:=v_s.C536871119-(6-1-v_s.HH);
USETIME1:=60-v_s.MM;
end if;
insert into "ARADMIN"."REPORT_GZ_KPI"
(
"ARADMIN"."REPORT_GZ_KPI"."CARD_NO",
"ARADMIN"."REPORT_GZ_KPI"."COTAGORY",
"ARADMIN"."REPORT_GZ_KPI"."KBS",
"ARADMIN"."REPORT_GZ_KPI"."NOR_CNT",
"ARADMIN"."REPORT_GZ_KPI"."CHECK_CNT",
"ARADMIN"."REPORT_GZ_KPI"."SEC_COM",
"ARADMIN"."REPORT_GZ_KPI"."USETIME",
"ARADMIN"."REPORT_GZ_KPI"."CMT",
"ARADMIN"."REPORT_GZ_KPI"."FINTIME",
"ARADMIN"."REPORT_GZ_KPI"."FIR_COM",
"ARADMIN"."REPORT_GZ_KPI"."OVER_CNT",
"ARADMIN"."REPORT_GZ_KPI"."CNT"
)
values
(
CARD_NO,
COTAGORY,
KBS,
NOR_CNT,
CHECK_CNT,
SEC_COM,
USETIME,
CMT,
FINTIME,
FIR_COM,
OVER_CNT,
CNT
);
commit;
END;
END LOOP;
CLOSE c_s;
commit;
END REPORT_DATA;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货