MYSQL中存储过程中写入分页查询----深坑

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/sky920521/article/details/78313072

 create PROCEDURE test1(curruentPage int,pageSize int,tableName VARCHAR(20))
BEGIN
 set @pageindex:=(curruentPage-1)*pageSize;
‘select * from  ’+tableName +‘ limit ’+@pageindex+‘,’+pageSize ;
 END


存储过程test1中我们把传入的表名放入字符串中进行+拼接,

数据库不能正常执行



create PROCEDURE test2(curruentPage int,pageSize int,tableName VARCHAR(20))

BEGIN
set @pageindex:=(curruentPage-1)*pageSize;
SET @str=CONCAT('select * from   ',tableName,'  limit  ',@pageindex,',',pageSize);
PREPARE str from @str;
EXECUTE str;

END


test2中换成concat('str1','str2',...)拼接

预编译

执行

ok!传入当前页数,页面size,以及表名 可以执行分页查询,后台java代码只需要调用存储过程test2

展开阅读全文

mysql存储过程中PREPARE语句的问题

04-11

我在存储过程中利用了PREPARE语句,但是在CALL这个时候报错,请高手们看看是那个地方有问题,创建过程的时候没报错。rn具体代码:rn[code=sql]rn-- 原本是这样的查询语句rnSELECT t1.bankprovincecode,t1.bankprovincename,'2' AS area_level,COUNT(t3.corpid)AS c_corpid rnFROM bankbranchparams AS t1 rnINNER JOIN corpinfo AS t3 ON t1.bankbranchleadercode=t3.bankbranchleadercode rnWHERE t3.applydate <= '20160325' AND t3.version<>'entjxcstd'rnGROUP BY t1.bankprovincecode,t1.bankprovincename;rn-- 创建过程rnDROP PROCEDURE IF EXISTS proc_test;rnCREATE PROCEDURE proc_testrn(v_cname VARCHAR(20),rn v_cname2 VARCHAR(20),rn v_nextlevel VARCHAR(10),rn i_date VARCHAR(20))rnBEGINrnSET @sqlstr1=CONCAT('SELECT t1.',v_cname,'t1.',v_cname2,v_nextlevel,rn'AS area_level,COUNT(t3.corpid)AS c_corpid FROM bankbranchparams AS t1 rnINNER JOIN corpinfo AS t3 ON t1.bankbranchleadercode=t3.bankbranchleadercode rnWHERE t3.applydate <=',i_date, 'AND t3.version<>','entjxcstd',rn'GROUP BY t1.',v_cname,'t1.',v_cname2);rnrnPREPARE stmt1 FROM @sqlstr1;rnEXECUTE stmt1;rnrnEND;rnCALL proc_test('bankprovincecode','bankprovincecodename','2','20160325');rnrn[/code]rn报错:rn[SQL]CALL proc_test('bankprovincecode','bankprovincecodename','2','20160325');rn[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 't3.version<>entjxcstdGROUP BY t1.bankprovincecodet1.bankprovincecodename' at line 3 论坛

没有更多推荐了,返回首页