下面所用过程中表 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;
/
解决方案 »
- oracle,查询的时候遇到的诡异问题
- 字符截取拼接
- 如何把台式机上oracle 10g的数据库“mysjk”复制到我新买的笔记本上(都windows)
- 研究生招生涉及到那些部门(实体)???
- 请教关于group by分组查询的问题
- 我修改了计算机名称后,oracle8.1.6数据库登录不进去了
- 关于oracle数据库中视图的问题,能否对建立的视图(虚表)中某条记录进行删除和修改操作吗?各位大侠指点一下啊,不能的话有没有什么解决
- 再PL/SQL中怎样自定义数据类型?既我想定义一个结构类型,还有,怎样访问这个结构?
- 急!求讲PL/SQL基本的电子书
- 各位大神 有什么好办法可以做两个相同数据库的部分数据的导入导出?
- oracle结果显示
- 求高手指点存储过程ORA-01460: 转换请求无法实现或不合理怎么办
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;
/
看楼主的意思大约是这样的?