DELIMITER //
CREATE PROCEDURE paging(
In name varchar(1024), //表名
In fields varchar(1024), //查询字段
In size int, //每页显示的记录数
In nowpage int//当前页
)
Begin
//定义变量
declare begin_row int default 0; //开始光标
declare limit_string char(64); //定义limit语句
//构造语句
Set begin_row=(nowpage-1)*size;//开始光标
Set limit_string=concat('limit',begin_row,',',size);// 赋值limit语句
Set @result=concat('select',fields,'from',name,'',limit_string);//执行结果
//预处理
Prepare result_stmt from @result;//定义
Execute result_stmt; /*执行预处理语句*/
Deallocate prepare result_stmt; /*删除定义*/
End;
//
DELIMITER ;
[SQL]
CREATE PROCEDURE paging(
In name varchar(1024), ;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2还有一个疑问Set limit_string=concat('limit',begin_row,',',size);// 赋值limit语句
Set @result=concat('select',fields,'from',name,'',limit_string);//执行结果
为什么一个要加@一个不要
CREATE PROCEDURE paging(
In name varchar(1024), //表名
In fields varchar(1024), //查询字段
In size int, //每页显示的记录数
In nowpage int//当前页
)
Begin
//定义变量
declare begin_row int default 0; //开始光标
declare limit_string char(64); //定义limit语句
//构造语句
Set begin_row=(nowpage-1)*size;//开始光标
Set limit_string=concat('limit',begin_row,',',size);// 赋值limit语句
Set @result=concat('select',fields,'from',name,'',limit_string);//执行结果
//预处理
Prepare result_stmt from @result;//定义
Execute result_stmt; /*执行预处理语句*/
Deallocate prepare result_stmt; /*删除定义*/
End;
//
DELIMITER ;
[SQL]
CREATE PROCEDURE paging(
In name varchar(1024), ;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2还有一个疑问Set limit_string=concat('limit',begin_row,',',size);// 赋值limit语句
Set @result=concat('select',fields,'from',name,'',limit_string);//执行结果
为什么一个要加@一个不要
call paging(@tablename,@fileds,@size,@nowpage);
[SQL] call paging(@tablename,@fileds,@size,@nowpage);
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1
call paging('data','test',1,3);
[SQL] call paging('data','test',1,3);
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1
Set begin_row=(nowpage-1)*size;//开始光标
Set limit_string=concat('limit',begin_row,',',size);// 赋值limit语句
Set @result=concat('select',fields,'from',name,'',limit_string);//执行结果
select begin_row;
select limit_string;
select @result;