下面所用过程中表 ucr_bc1.tg_cdr04,在1月份时将是tg_cdr01,2月份是tg_cdr02,一直到12,这个过程怎么改造一下,在5月份提取tg_cdr04数据,6月份提取tg_cdr05的数据,以此类推,以满足一年的需要?CREATE OR REPLACE PROCEDURE p_test AUTHID CURRENT_USER AS
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table ty_04';
EXCEPTION
WHEN OTHERS THEN
NULL;
END; EXECUTE IMMEDIATE 'create table ty_04 as
select b.lac, b.ci, b.name,a.msisdn,count(*) count
from ucr_bc1.tg_cdr04@ngbil a, tyjz b
where a.lac1 = b.lac
and a.cell_id1 = b.ci
group by b.lac, b.ci, b.name,a.msisdn'; BEGIN
EXECUTE IMMEDIATE 'drop table ty_04_jg';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
EXECUTE IMMEDIATE 'create table ty_04_jg as
select *
from (select name,
lac,
ci,
msisdn,
count,
row_number() over(partition by msisdn order by count desc) rn
from ty_04)
where rn = 1';
END;
/
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table ty_04';
EXCEPTION
WHEN OTHERS THEN
NULL;
END; EXECUTE IMMEDIATE 'create table ty_04 as
select b.lac, b.ci, b.name,a.msisdn,count(*) count
from ucr_bc1.tg_cdr04@ngbil a, tyjz b
where a.lac1 = b.lac
and a.cell_id1 = b.ci
group by b.lac, b.ci, b.name,a.msisdn'; BEGIN
EXECUTE IMMEDIATE 'drop table ty_04_jg';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
EXECUTE IMMEDIATE 'create table ty_04_jg as
select *
from (select name,
lac,
ci,
msisdn,
count,
row_number() over(partition by msisdn order by count desc) rn
from ty_04)
where rn = 1';
END;
/
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table ty_'||to_char(sysdate,'mm');
EXCEPTION
WHEN OTHERS THEN
NULL;
END; EXECUTE IMMEDIATE 'create table ty_'||to_char(sysdate,'mm')||' as
select b.lac, b.ci, b.name,a.msisdn,count(*) count
from ucr_bc1.tg_cdr'||to_char(sysdate,'mm')||'@ngbil a, tyjz b
where a.lac1 = b.lac
and a.cell_id1 = b.ci
group by b.lac, b.ci, b.name,a.msisdn'; BEGIN
EXECUTE IMMEDIATE 'drop table ty_'||to_char(sysdate,'mm')||'_jg';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
EXECUTE IMMEDIATE 'create table ty_'||to_char(sysdate,'mm')||'_jg as
select *
from (select name,
lac,
ci,
msisdn,
count,
row_number() over(partition by msisdn order by count desc) rn
from ty_'||to_char(sysdate,'mm')||')
where rn = 1';
END;
/
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table ty_'||to_char(sysdate,'mm');
EXCEPTION
WHEN OTHERS THEN
NULL;
END; EXECUTE IMMEDIATE 'create table ty_'||to_char(sysdate,'mm')||' as
select b.lac, b.ci, b.name,a.msisdn,count(*) count
from ucr_bc1.tg_cdr'||to_char(sysdate,'mm')||'@ngbil a, tyjz b
where a.lac1 = b.lac
and a.cell_id1 = b.ci
group by b.lac, b.ci, b.name,a.msisdn'; BEGIN
EXECUTE IMMEDIATE 'drop table ty_'||to_char(sysdate,'mm')||'_jg';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
EXECUTE IMMEDIATE 'create table ty_'||to_char(sysdate,'mm')||'_jg as
select *
from (select name,
lac,
ci,
msisdn,
count,
row_number() over(partition by msisdn order by count desc) rn
from ty_'||to_char(sysdate,'mm')||')
where rn = 1';
END;
/
不就是你想要的提取前一月份的
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table ty_'||to_char(sysdate,'mm');
EXCEPTION
WHEN OTHERS THEN
NULL;
END; EXECUTE IMMEDIATE 'create table ty_'||to_char(sysdate,'mm')||' as
select b.lac, b.ci, b.name,a.msisdn,count(*) count
from ucr_bc1.tg_cdr'||to_char(add_months(sysdate,-1),'mm')||'@ngbil a, tyjz b
where a.lac1 = b.lac
and a.cell_id1 = b.ci
group by b.lac, b.ci, b.name,a.msisdn'; BEGIN
EXECUTE IMMEDIATE 'drop table ty_'||to_char(sysdate,'mm')||'_jg';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
EXECUTE IMMEDIATE 'create table ty_'||to_char(sysdate,'mm')||'_jg as
select *
from (select name,
lac,
ci,
msisdn,
count,
row_number() over(partition by msisdn order by count desc) rn
from ty_'||to_char(sysdate,'mm')||')
where rn = 1';
END;
/
看楼主的意思大约是这样的?