begin
declare v_sql varchar(5000);
declare colname varchar(100);
DECLARE keycolname varchar(100);
DECLARE v_pksql varchar(1000);
DECLARE done INT DEFAULT 0;
DECLARE curl CURSOR FOR SELECT column_name,COLUMN_KEY FROM information_schema.COLUMNS WHERE table_name=p_tbname and TABLE_SCHEMA=p_dbname ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
set v_sql= CONCAT('update ',p_tbname,' a,(select @x:=ifnull(@x,0)+10 as rownum ,');
set v_pksql='';
OPEN curl;
my_loop:LOOP
FETCH curl INTO colname,keycolname;
IF done=1 THEN
LEAVE my_loop;
END IF;
set v_sql=concat(v_sql,colname,',');
IF (keycolname='PRI') THEN
set v_pksql=CONCAT(v_pksql,'a.',colname,'=b.',colname,' and ');
end if;
END LOOP my_loop;
CLOSE curl;
set v_sql=left(v_sql,char_length(v_sql)-1); -- 去除最后逗号
set v_sql=CONCAT(v_sql,' from ',p_tbname,' where ',p_condition,' order by ',p_fieldname,') b set a.',p_fieldname,'=b.rownum where ');
set v_pksql=left(v_pksql,char_length(v_pksql)-1); -- 去除最后逗号
set v_sql=CONCAT(v_sql,v_pksql,' a.',p_fieldname,'<>b.rownum');
select v_sql;
set @v_sql=v_sql;
set @x=0;
prepare stmt from @v_sql; -- 预处理需要执行的动态SQL,其中stmt是一个变量
EXECUTE stmt; -- 执行SQL语句
deallocate prepare stmt; -- 释放掉预处理段
-- select CONCAT(v_sql,v_pksql,' a.',fieldname,'<>b.rownum');
-- SELECT v_pksql;
end
declare v_sql varchar(5000);
declare colname varchar(100);
DECLARE keycolname varchar(100);
DECLARE v_pksql varchar(1000);
DECLARE done INT DEFAULT 0;
DECLARE curl CURSOR FOR SELECT column_name,COLUMN_KEY FROM information_schema.COLUMNS WHERE table_name=p_tbname and TABLE_SCHEMA=p_dbname ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
set v_sql= CONCAT('update ',p_tbname,' a,(select @x:=ifnull(@x,0)+10 as rownum ,');
set v_pksql='';
OPEN curl;
my_loop:LOOP
FETCH curl INTO colname,keycolname;
IF done=1 THEN
LEAVE my_loop;
END IF;
set v_sql=concat(v_sql,colname,',');
IF (keycolname='PRI') THEN
set v_pksql=CONCAT(v_pksql,'a.',colname,'=b.',colname,' and ');
end if;
END LOOP my_loop;
CLOSE curl;
set v_sql=left(v_sql,char_length(v_sql)-1); -- 去除最后逗号
set v_sql=CONCAT(v_sql,' from ',p_tbname,' where ',p_condition,' order by ',p_fieldname,') b set a.',p_fieldname,'=b.rownum where ');
set v_pksql=left(v_pksql,char_length(v_pksql)-1); -- 去除最后逗号
set v_sql=CONCAT(v_sql,v_pksql,' a.',p_fieldname,'<>b.rownum');
select v_sql;
set @v_sql=v_sql;
set @x=0;
prepare stmt from @v_sql; -- 预处理需要执行的动态SQL,其中stmt是一个变量
EXECUTE stmt; -- 执行SQL语句
deallocate prepare stmt; -- 释放掉预处理段
-- select CONCAT(v_sql,v_pksql,' a.',fieldname,'<>b.rownum');
-- SELECT v_pksql;
end