Oracle 在Select语句中使用动态表名准备提取文件

SELECT TICKET_ID  
,SOURCE_ID          
,SERV_ID            
,to_char(COLLECTED_DATE,'YYYY-MM-DD HH24:MI:SS')    
,to_char(DEALED_DATE,'YYYY-MM-DD HH24:MI:SS')        
FROM BILL.DDI_TICKET_10_101708   --- Need to handle the table name dynamically, Next month 1708 will become 1709.                                                                     
WHERE trunc(DEALED_DATE) =trunc(SYSDATE-1)
复制

create or replace procedure p ( tab_suffix varchar2 ) as
  sql_stmt   varchar2(1000);
  table_name user_tables.table_name%type;
begin

  table_name := dbms_assert.sql_object_name('DDI_TICKET_10_10' || tab_suffix);
  
  sql_stmt := q'|SELECT TICKET_ID  
,SOURCE_ID          
,SERV_ID            
,to_char(COLLECTED_DATE,'YYYY-MM-DD HH24:MI:SS')    
,to_char(DEALED_DATE,'YYYY-MM-DD HH24:MI:SS')        
FROM BILL.|' || table_name || 'WHERE trunc(DEALED_DATE) =trunc(SYSDATE-1)';

  execute immediate sql_stmt;

end p;
/
复制