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;
….
