create global temporary table t1( first_result date,data float) on commit preserve rows;
create global temporary table t2( first_result date,data float) on commit preserve rows;
create global temporary table t3( first_result date,data float) on commit preserve rows;
CREATE OR REPLACE PACKAGE npmdb.GetFullDataPackge
is
type refcursor is REF CURSOR;
procedure GetFullData(begintime date,endtime date,retunr_list out refcursor);
end GetFullDataPackge;CREATE OR REPLACE PACKAGE BODY npmdb.GetFullDataPackge
is
procedure GetFullData(begintime date,endtime date,retunr_list out refcursor)
as
bsc_count integer;
bts_count integer;
site_count integer;
begin
select count(*) into bsc_count
from tcc_ne_snap
where ne_type = 5200
and confirmed not in (2,5)
and first_result=substr(begintime,1,10)||' 00:00:00';select count(*) into bts_count
from tcc_ne_snap
where ne_type = 5300
and confirmed not in (2,5)
and first_result=substr(begintime,1,10)||' 00:00:00';select count(*) into site_count
from tcc_ne_snap
where ne_type = 5201
and confirmed not in (2,5)
and first_result=substr(begintime,1,10)||' 00:00:00';insert into t1
select first_result,round(100*sfb_divfloat_1(count(*),bsc_count,0,0),2) data from c_tpa_r_bsc_sum
where first_result between begintime and endtime
and ne_type=5200 and sum_level=0
and add_s_ho is not null
group by first_result;insert into t2
select first_result,round(100*sfb_divfloat_1(count(*),bts_count,0,0),2) data
from c_tpa_r_bts_sum
where first_result between begintime and endtime
and ne_type=5300 and sum_level=0
and tch_traffic is not null
group by first_result;insert into t3
select first_result,round(100*sfb_divfloat_1(count(*),site_count,0,0),2) data
from c_tpa_r_site_sum
where first_result between begintime and endtime
and ne_type=5201 and sum_level=0
and avail_tch_nbr is not null
group by first_result;open retunr_list for
select t1.first_result,t1.data,t2.data,t3.data from t1,t2,t3 where t1.first_result =t2.first_result and t1.first_result =t3.first_result;
end;
end;
create global temporary table t2( first_result date,data float) on commit preserve rows;
create global temporary table t3( first_result date,data float) on commit preserve rows;
CREATE OR REPLACE PACKAGE npmdb.GetFullDataPackge
is
type refcursor is REF CURSOR;
procedure GetFullData(begintime date,endtime date,retunr_list out refcursor);
end GetFullDataPackge;CREATE OR REPLACE PACKAGE BODY npmdb.GetFullDataPackge
is
procedure GetFullData(begintime date,endtime date,retunr_list out refcursor)
as
bsc_count integer;
bts_count integer;
site_count integer;
begin
select count(*) into bsc_count
from tcc_ne_snap
where ne_type = 5200
and confirmed not in (2,5)
and first_result=substr(begintime,1,10)||' 00:00:00';select count(*) into bts_count
from tcc_ne_snap
where ne_type = 5300
and confirmed not in (2,5)
and first_result=substr(begintime,1,10)||' 00:00:00';select count(*) into site_count
from tcc_ne_snap
where ne_type = 5201
and confirmed not in (2,5)
and first_result=substr(begintime,1,10)||' 00:00:00';insert into t1
select first_result,round(100*sfb_divfloat_1(count(*),bsc_count,0,0),2) data from c_tpa_r_bsc_sum
where first_result between begintime and endtime
and ne_type=5200 and sum_level=0
and add_s_ho is not null
group by first_result;insert into t2
select first_result,round(100*sfb_divfloat_1(count(*),bts_count,0,0),2) data
from c_tpa_r_bts_sum
where first_result between begintime and endtime
and ne_type=5300 and sum_level=0
and tch_traffic is not null
group by first_result;insert into t3
select first_result,round(100*sfb_divfloat_1(count(*),site_count,0,0),2) data
from c_tpa_r_site_sum
where first_result between begintime and endtime
and ne_type=5201 and sum_level=0
and avail_tch_nbr is not null
group by first_result;open retunr_list for
select t1.first_result,t1.data,t2.data,t3.data from t1,t2,t3 where t1.first_result =t2.first_result and t1.first_result =t3.first_result;
end;
end;
至于有没有数据,你可以先确认对应的查询语句有没有查询出数据,
你可以先建立永久表而不是临时表来测试一下(可以在相关的语句后提交数据,
然后在其它会话中查看数据)
insert into t1
select first_result,round(100*sfb_divfloat_1(count(*),bsc_count,0,0),2) data from c_tpa_r_bsc_sum
where first_result between begintime and endtime
and ne_type=5200 and sum_level=0
and add_s_ho is not null
group by first_result;insert into t2
select first_result,round(100*sfb_divfloat_1(count(*),bts_count,0,0),2) data
from c_tpa_r_bts_sum
where first_result between begintime and endtime
and ne_type=5300 and sum_level=0
and tch_traffic is not null
group by first_result;insert into t3
select first_result,round(100*sfb_divfloat_1(count(*),site_count,0,0),2) data
from c_tpa_r_site_sum
where first_result between begintime and endtime
and ne_type=5201 and sum_level=0
and avail_tch_nbr is not null
group by first_result;
--- 加个commit 还有就是你这些插入语句是否有数据 以及数据类型的问题
select t1.first_result,t1.data,t2.data,t3.data from t1,t2,t3 where t1.first_result =t2.first_result and t1.first_result =t3.first_result; 这句有没有数据?