存储过程示例2_cesm segmentation fault-优快云博客

本文介绍了一个用于更新特定数据库表中记录的SQL存储过程。该过程通过连接操作更新了多个表之间的通道ID，并创建了一个临时表来保存更新前的数据。此外，还实现了根据通道别名查找通道ID的逻辑，若未找到则会为通道分配新的ID。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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