Use of dynamic SQL in MySQL stored procedures

MySQL stored procedures are not easy to use, generally in the application development process is not recommended. But sometimes in the environment such as data migration, especially involves the complex point of logic processing, requires the coordination of stored procedures, of course, in other languages to write program implementation is also possible, but the efficiency estimate is not directly in the database Operation High.

Sometimes the table where the data is located is indeterminate, and the table name needs to be decided on a temporary basis, which is where dynamic SQL comes in, and the following examples show examples of stored procedures that use dynamic SQL during a data export:

Delimiter//  
CREATE PROCEDURE ' Proc_rt_imp_group ' ()  
BEGIN
DECLARE done INT DEFAULT FALSE;  
declare v_tid,v_ptable int;  
DECLARE V_tablename VARCHAR ();  
DECLARE cur_t CURSOR for select t.tid,t.ptable from Pw_threads_rt t;  
DECLARE CONTINUE HANDLER for does FOUND SET done = TRUE;  
OPEN cur_t;  
Read_loop:loop  
FETCH cur_t into v_tid,v_ptable;  
IF done THEN
LEAVE read_loop;  
End IF;  
If V_ptable=0 then
set v_tablename = ' pw_posts ';  
else
Set v_tablename = concat (' pw_posts ', v_ptable);  
End If;  
Set @updatesql =concat (' INSERT INTO PW_POSTS_RT select * 

from ', V_tablename, ' where tid= ', v_tid);  
PREPARE sqltext from @updatesql;  
Execute sqltext;  
End LOOP;  
Close cur_t;  
end//  
delimiter;