create or replace procedure xue_sw_count(begindata in varchar2,enddata in varchar2)
as
cdate number := 0;
yearmonthday varchar2(200) := '';
table_name varchar2(200) :='t_gw_ue_13_220_';
first number :=1;
begin
cdate := to_number(to_date(enddata,'YYYYMMDD') - to_date(begindata,'YYYYMMDD'));
yearmonthday := begindata;
table_name := table_name || yearmonthday;
-----------------------------------------
while first =1 LOOP
EXECUTE IMMEDIATE 'insert into xue_test(x_calling_num,x_area_name,x_count_num)
select a.calling_number,b.area_name ,count(a.calling_number) "16300"
from '||table_name||' a,t_area b
where a.called_number like ''163%'' and a.calling_area_id = b.area_id
group by a.calling_number,b.area_name';
first := 2;
end loop;
-----------------------------------------
table_name := 't_gw_ue_13_220_';
select to_char(to_date(begindata,'YYYYMMDD') + 1,'YYYYMMDD') INTO yearmonthday FROM dual; while cdate>=0 loop
table_name := table_name || yearmonthday;
EXECUTE IMMEDIATE ' insert into xue_test
select c.calling_number,c.area_name,c.c_count from
(select a.calling_number calling_number,b.area_name area_name,count(a.calling_number) c_count
from '||table_name||' a,t_area b
where a.called_number like ''163%'' and a.calling_area_id = b.area_id
group by a.calling_number,b.area_name) c where c.calling_number not in
(select calling_number from xue_test ) '; cdate := cdate-1;
select to_char(to_date(begindata,'YYYYMMDD') + 1,'YYYYMMDD') INTO yearmonthday FROM dual;
end loop;
commit;
end xue_sw_count;
as
cdate number := 0;
yearmonthday varchar2(200) := '';
table_name varchar2(200) :='t_gw_ue_13_220_';
first number :=1;
begin
cdate := to_number(to_date(enddata,'YYYYMMDD') - to_date(begindata,'YYYYMMDD'));
yearmonthday := begindata;
table_name := table_name || yearmonthday;
-----------------------------------------
while first =1 LOOP
EXECUTE IMMEDIATE 'insert into xue_test(x_calling_num,x_area_name,x_count_num)
select a.calling_number,b.area_name ,count(a.calling_number) "16300"
from '||table_name||' a,t_area b
where a.called_number like ''163%'' and a.calling_area_id = b.area_id
group by a.calling_number,b.area_name';
first := 2;
end loop;
-----------------------------------------
table_name := 't_gw_ue_13_220_';
select to_char(to_date(begindata,'YYYYMMDD') + 1,'YYYYMMDD') INTO yearmonthday FROM dual; while cdate>=0 loop
table_name := table_name || yearmonthday;
EXECUTE IMMEDIATE ' insert into xue_test
select c.calling_number,c.area_name,c.c_count from
(select a.calling_number calling_number,b.area_name area_name,count(a.calling_number) c_count
from '||table_name||' a,t_area b
where a.called_number like ''163%'' and a.calling_area_id = b.area_id
group by a.calling_number,b.area_name) c where c.calling_number not in
(select calling_number from xue_test ) '; cdate := cdate-1;
select to_char(to_date(begindata,'YYYYMMDD') + 1,'YYYYMMDD') INTO yearmonthday FROM dual;
end loop;
commit;
end xue_sw_count;
1.动态sql的概念
2.select into
找几个正确的procedure,好好熟悉下语法啥的。
1.动态sql的概念
2.select into
找几个正确的procedure,好好熟悉下语法啥的。