delimiter // drop procedure if exists getData;//create procedure getData() begin declare num,i,nid int default 1; declare strtype varchar(50); select count(*) into num from tjmain; while i < num do select id into nid from tjmain limit i,1 ; select nid; set i = i + 1 ; end while;
end 这个存储过程的查询语句limit后边不能用变量代替么?
不能,用CONCAT连接字符串,用PREPARE EXECUTE动态执行SQL
set @gs=1; SET @v_sql = CONCAT( ' select * from sTUDENT limit ',@gs); PREPARE stmt2 FROM @v_sql; EXECUTE stmt2;
没有理解是什么意思,举例说明你的要求
drop procedure if exists getData;//create procedure getData()
begin
declare num,i,nid int default 1;
declare strtype varchar(50);
select count(*) into num from tjmain;
while i < num do
select id into nid from tjmain limit i,1 ;
select nid;
set i = i + 1 ;
end while;
end
这个存储过程的查询语句limit后边不能用变量代替么?
SET @v_sql = CONCAT( ' select * from sTUDENT limit ',@gs);
PREPARE stmt2 FROM @v_sql;
EXECUTE stmt2;