mysql存储过程参数游标_mysql 存储过程，游标参数解决思路

mysql 存储过程，游标参数

create procedure proc_cms(beginindex int,rowsize int)/*传输当前索引和插入条数*/

begin

declare done int default 0;

declare seq int default 0;

declare flag int default 0;

declare seqtitle varchar(2000) default '';

declare cms_size int default 0;/*总记录条数*/

declare sqlsa varchar(2000) default '';

declare cur1 cursor for select t1.seq_id,t1.seg_title from cms_db.tbl_catalog_segment t1 limit beginindex,rowsize;

declare continue handler for SQLSTATE '02000' set done = 1;/*定义错误*/

set @limit_str = concat(' limit ',beginindex,',',rowsize);

select count(*) into cms_size from cms_db.tbl_catalog_segment; /*查询表的总记录*/

open cur1;/*开游标*/

fetch cur1 into seq,seqtitle;

/*循环体*/

while (flag!=100) DO

set flag = flag+1;

insert into nufrontsoft.tbl_temp set nufrontsoft.tbl_temp.seq = seq,nufrontsoft.tbl_temp.seqtitle=seqtitle;

fetch cur1 into seq,seqtitle;

end while;

close cur1;

/*select flag , cms_size; */

end;

我limit加上参数就报语法错误 我也试过其他的方式都不行 希望高手指点下啊

------解决方案--------------------

limit 后不能使用变量，只能使用常数。

------解决方案--------------------

动态执行

set @sql=concat('create table tt as select t1.seq_id,t1.seg_title from cms_db.tbl_catalog_segment t1 limit ',beginindex,',',rowsize);

prepare asql from @sql;

execute asql;

declare cur1 cursor for select * from tt;

....

微信 赏一包辣条吧~

支付宝 赏一听可乐吧~