mysql prepare存储过程

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

近来用了下存储过程,简单的记录总结一下。

使用存储过程,可以对数据库进行更复杂写的逻辑处理。比如说,我要将一个表subscriber的最大id获取到,然后复制给另外一张表usage的auto_increment,就可以这样做。

1. 获取最大id

SET @m = (SELECT MAX(id) + 10000 FROM subscriber);

@m为所要使用的参数

2. 准备赋值AUTO_INCREMENT的sql语句,此处用到了concat函数

SET @s = CONCAT('ALTER TABLE usage AUTO_INCREMENT=', @m);

3. prepare并执行

PREPARE stmt1 FROM @s;

EXECUTE stmt1;

4. 释放资源

DEALLOCATE PREPARE stmt1;

此处第二步也可以通过别的方法进行参数传递,比如:

SET @m = (SELECT min(id) FROM subscriber);
PREPARE stmt1 FROM "select * from subscriber where id=?";
EXECUTE stmt1 using @m;
DEALLOCATE PREPARE stmt1;

但是这种传参方法对于alter table auto_increment不起作用, update语句是可以的,亲测有效。

 

展开阅读全文

存储过程prepare如何写?

08-05

delimiter $$rncreate procedure sCode ()rnbegin rn declare icode varchar(10);rn declare cur_sCode cursor for (select symbol from quote group by symbol);rn declare exit HANDLER for not found close cur_sCode;rn open cur_sCode ;rn repeatrn fetch cur_sCode into icode;rn set @sql = concat('create table ',icode,'( select * from quote where symbol=''',icode,''' order by date desc);');rn PREPARE stmt1 FROM @sql;rn EXECUTE stmt1 ;rn DEALLOCATE PREPARE stmt1;rn until 0 end repeat;rn close cur_sCode;rnend;rn$$rn上面的存储过程可以运行,但是产生问题rn可以运行,产生了新的表名,但是每个表都是空的,修改成rn1.set @sql = concat('create table ',icode,'( select * from quote where symbol='',icode,''order by date desc);');rn可以运行,产生了新的表名,但是每个表都是空的rn2.rndelimiter $$rncreate procedure sCode ()rnbegin rn declare icode varchar(10);rn declare cur_sCode cursor for (select symbol from quote group by symbol);rn declare exit HANDLER for not found close cur_sCode;rn open cur_sCode ;rn repeatrn fetch cur_sCode into icode;rn set @sql = concat('create table ',@myname,'select * from quote where symbol=',@myname,order by date desc');rn set @myname=icode; rn PREPARE stmt1 FROM @sql;rn EXECUTE stmt1 ;rn DEALLOCATE PREPARE stmt1;rn until 0 end repeat;rn close cur_sCode;rnend;rn$$rn编译无法通过rnERROR 1064 (42000): 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 'order by date desc');rn set @myname=icode; rn PREPARE stmt1 FROM @sql;rn EXECUT' at line 9rnmysql> rn请问,究竟应该如何处理呢?rnrn请问,如何处理? 论坛

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 论坛

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