存储过程示例2

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