P_SG_CREATE_SCRIPT_BY_BATCH_CODE

DELIMITER ;;
  CREATE  PROCEDURE "P_SG_CREATE_SCRIPT_BY_BATCH_CODE"(in VAR_BATCH_CODE varchar(20))
BEGIN
DECLARE t_table VARCHAR(300);
DECLARE st_table VARCHAR(300);
DECLARE VAR_TRUNCATE_SQL_SCRIPT VARCHAR(500);
DECLARE VAR_INSERT_I_SQL_SCRIPT VARCHAR(4000);
DECLARE DONE INT DEFAULT(0);
DECLARE cur CURSOR FOR SELECT TDB||'.'||TNAME,TSOURCE||'.'||STNAME,TABLE_BATCH_CODE from BD_DB_I.SG_T_LOADCONFIG_INCR where TABLE_BATCH_CODE = VAR_BATCH_CODE;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
truncate BD_DB_I.SG_T_CREATE_SCRIPT;
OPEN cur;
REPEAT
FETCH cur INTO t_table,st_table,VAR_BATCH_CODE;
IF NOT done THEN
select 'truncate ' ||t_table|| ';' into VAR_TRUNCATE_SQL_SCRIPT;
select 'insert into  ' || t_table || ' select * from '|| st_table ||';' into VAR_INSERT_I_SQL_SCRIPT;
insert into BD_DB_I.SG_T_CREATE_SCRIPT (TAR_TABLE_ENAME,TRUNCATE_SQL_SCRIPT,INSERT_I_SQL_SCRIPT,TABLE_BATCH_CODE) values
(t_table,VAR_TRUNCATE_SQL_SCRIPT,VAR_INSERT_I_SQL_SCRIPT,VAR_BATCH_CODE);
END IF;
UNTIL DONE END REPEAT;
CLOSE cur;

END ;;