想请问下各位,我现在有个表前面是以'sms_dx_demo_'开头的,每天的记录会根据每天的日期存放在一张表里,比如今天22号,就会放在sms_dx_demo_22表中,现在我这边有个存储过程,我需要每天跑一次,然后每天这个表名都会动态变化,请问横线表名部分怎么拼装,谢谢!
day varchar2(2); ------日期
---- 短信表后缀
select to_char(sysdate-1,'dd') into day from dual; ------ 统计xxxx成功量
select sum(sms.send_count) into sa_send_cnt from core_school sc
inner join sms_mt_send sms on sc.school_id = sms.school_id
inner join 'sms_mt_send_detail'||day detail on sms.mt_send_id = detail.mt_send_id
where sms.data_src in (1,2,3) and (detail.errorcode = '0' or detail.errorcode is null) ;
day varchar2(2); ------日期
---- 短信表后缀
select to_char(sysdate-1,'dd') into day from dual; ------ 统计xxxx成功量
select sum(sms.send_count) into sa_send_cnt from core_school sc
inner join sms_mt_send sms on sc.school_id = sms.school_id
inner join 'sms_mt_send_detail'||day detail on sms.mt_send_id = detail.mt_send_id
where sms.data_src in (1,2,3) and (detail.errorcode = '0' or detail.errorcode is null) ;
sql varchar2(2000);
---- 短信表后缀
select to_char(sysdate-1,'dd') into day from dual;------ 统计xxxx成功量
sql:= 'select sum(sms.send_count) into sa_send_cnt from core_school sc '||
'inner join sms_mt_send sms on sc.school_id = sms.school_id '||
'inner join ||'sms_dx_demo_'||day||' detail on sms.mt_send_id = detail.mt_send_id '||
' where sms.data_src in (1,2,3) and (detail.errorcode = ''0'' or detail.errorcode is null) ';
dbms_output.put_line(sql);
Execute Immediate sql;
declare
p_sql varchar2(5000);
school_id number(10);
sa_send_cnt number(10);
p_day varchar2(2);
begin
select to_char(sysdate-1,'dd') into p_day from dual;
dbms_output.put_line(p_day);
select school_id into school_id from core_school where ec_code = '791910076970';
dbms_output.put_line(school_id);
p_sql := 'select nvl(sum(sms.send_count),0) from core_school sc
inner join sms_mt_send sms on sc.school_id = sms.school_id
inner join sms_mt_send_detail'||p_day||' detail on sms.mt_send_id = detail.mt_send_id
where sms.data_src in (1,2,3) and (detail.errorcode = ''0'' or detail.errorcode is null) and sc.school_id ='||school_id;
dbms_output.put_line(p_sql);
execute immediate p_sql into sa_send_cnt; dbms_output.put_line(sa_send_cnt);
end;