Question

oracle存储过程如何动态修改表名关联查询

想请问下各位，我现在有个表前面是以'sms_dx_demo_'开头的，每天的记录会根据每天的日期存放在一张表里，比如今天22号，就会放在sms_dx_demo_22表中，现在我这边有个存储过程，我需要每天跑一次，然后每天这个表名都会动态变化，请问横线表名部分怎么拼装，谢谢！
day varchar2(2); ------日期---- 短信表后缀select to_char(sysdate-1,'dd') into day from dual;

------ 统计xxxx成功量selectsum(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 isnull) ;

Answer 1

用动态sql拼接语句就好了！

VSQL varchar2（2000）
day varchar2(2);
begin
day：= to_char(sysdate-1,'dd')；
VSQL：=' selectsum(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 isnull) ';
execute immdiate VSQL;

end

Answer 2

你前面说的和后面的SQL好像没有什么关联