oracle存储过程怎么动态修改表名关联查询

oracle存储过程如何动态修改表名关联查询
想请问下各位，我现在有个表前面是以'sms_dx_demo_'开头的，每天的记录会根据每天的日期存放在一张表里，比如今天22号，就会放在sms_dx_demo_22表中，现在我这边有个存储过程，我需要每天跑一次，然后每天这个表名都会动态变化，请问横线表名部分怎么拼装，谢谢！

SQL code

       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  [u]'sms_mt_send_detail'||day[/u] 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，就是execute immediate
[解决办法]
使用动态拼装sql方法

day varchar2(2);
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;

查看更多 下一篇