goOut:BEGIN
#Routine body goes here...
/**
* 更新地方表
* AUTHORS :zxl
* PARAMS:
* i_vcTableName :要更新的地方表的名字
* i_vcArea:要更新的地方表的地点
*/
DECLARE count INT DEFAULT 0;
declare cn varchar(64) default '';
declare cn1 varchar(64) default '';
declare cid varchar(10) default '';
declare eid varchar(10) default '';
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR
select t1.ChannelName from SSC_GETCHANNELLIST_TMPTB t1 where t1.ChannelName not in(select channlename from `SSC_TVIDMAP_V0.01`) and t1.channelid='';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
START TRANSACTION;
SELECT CONCAT('update ',i_vcTableName,' t1 INNER JOIN `SSC_TVIDMAP_V0.01` t2 ON t1.ChannelName=t2.channlename and t1.channelid =\'\' set t1.channelid=t2.channelid;') INTO @sql_text;
PREPARE STMT FROM @sql_text;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
SELECT CONCAT('CREATE TEMPORARY TABLE SSC_GETCHANNELLIST_TMPTB LIKE ',i_vcTableName,';') INTO @sql_text;
PREPARE STMT FROM @sql_text;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
SELECT CONCAT('INSERT INTO SSC_GETCHANNELLIST_TMPTB SELECT * FROM ',i_vcTableName,';') INTO @sql_text;
PREPARE STMT FROM @sql_text;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
open cur1;
SET done = 0;
FETCH cur1 INTO cn;
WHILE done <> 1 DO
#
select done;
select CONCAT('%',',',cn,',','%') into cn1;
select channelid into cid from `SSC_TVIDMAP_V0.01` where channlename=(select channelname from VoiceControlDict where channelalias like cn1);
#select done;
#select cid;
if cid !='' then
SELECT CONCAT('update ',i_vcTableName,' t1 set t1.channelid=',cid,' where t1.ChannelName=\'',cn,'\';') INTO @sql_text;
PREPARE STMT FROM @sql_text;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
else
select max(epgid)+1 into eid from `SSC_TVIDMAP_V0.01` where epgid > 10000 ;
#select eid;
insert into `SSC_TVIDMAP_V0.01`(epgid,channelid,channlename,channleupdatetime,isepg) values(eid,eid,CONCAT(cn,'-',i_vcArea),NOW(),0);
SELECT CONCAT('update ',i_vcTableName,' t1 set t1.channelid=',eid,' where t1.ChannelName=\'',cn,'\';') INTO @sql_text;
PREPARE STMT FROM @sql_text;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
end if;
SET count = count + 1;
SET done = 0;
SET cid ='';
FETCH cur1 INTO cn;
END WHILE;
SELECT count;
CLOSE cur1;
DROP TABLE SSC_GETCHANNELLIST_TMPTB;
COMMIT;
END