CREATE PROCEDURE `proc_updatehouse`()
BEGIN
#Routine body goes here...
declare eof int ;
declare projname varchar(200) charset utf8 default '' ;
declare smap varchar(300) charset utf8 default '' ;
declare dusage varchar(200) charset utf8 default '' ;
declare seladdr varchar(300) charset utf8 default '' ;
declare projaddr varchar(300) charset utf8 default '' ;
declare cur cursor for select a.title,
case
when b.项目地址 is null then ''
else b.项目地址 end 项目地址
,case
when c.DESIGNUSAGE is null then ''
else c.DESIGNUSAGE end DESIGNUSAGE
,func_getprojaddr(a.title)
,b.`售楼部地址`
from xhcms_newhouse_6 a
left join zx_info b on a.title=b.`项目名称`
left join zx_projquery c on a.title=c.PNAME ;
declare continue handler for not found set eof=1 ;
start TRANSACTION ;
set eof=0 ;
open cur ;
fetch cur into projname, smap,dusage,projaddr,seladdr ;
while eof != 1 DO
update xhcms_newhouse_6 k set k.map=smap,k.address=projaddr,k.sell_address=seladdr,k.buildtype=dusage
where k.title=projname ;
fetch cur into projname, smap,dusage,projaddr,seladdr ;
end while ;
close cur ;
COMMIT ;
END
还是要注意varchar的字符集
