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

[夏末冬初]

想请问下各位，我现在有个表前面是以'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) ;

[结贴是美德]

貌似得用动态sql，就是execute immediate

[book523]

使用动态拼装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;

[夏末冬初]

[Quote=引用 2 楼 的回复:]
使用动态拼装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_s……
[/Quote]


试了是可以，不过我想把sum(sms.send_count)的值付给sa_send_cnt 直接执行上面的sql会报错，说是missing keyword

[夏末冬初]

搞定了，多谢各位！

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;

[book523]

简单吧，搞定后有没有一点成就感？我觉得做软件最大的乐趣就是搞定一个问题后的那种喜悦和一点点成就。