想请问下,mysql在存储过程中,定义游标,后面跟select语句,跟的表名,能不能是动态的,即,用参数替换,
如果行,那要怎么做
我的样例如下:
create procedure sp_test
declare stop,num integer default 1;
declare tablename varchar(30);
set tablename='user';
begin
declare cur_1 cursor for select count(*) from tablename;
DECLARE CONTINUE HANDLER FOR not FOUND set stop3=0;
open cur_index;
fetch cur_1 into num;
end;
这只是个例子,怎么用参数来替换里面表名
如果行,那要怎么做
我的样例如下:
create procedure sp_test
declare stop,num integer default 1;
declare tablename varchar(30);
set tablename='user';
begin
declare cur_1 cursor for select count(*) from tablename;
DECLARE CONTINUE HANDLER FOR not FOUND set stop3=0;
open cur_index;
fetch cur_1 into num;
end;
这只是个例子,怎么用参数来替换里面表名
MYSQL没有办法直接这样定义动态的游标。 不过也不必如此麻烦,你可以动态的定义一个VIEW。
mysql> CREATE PROCEDURE curdemo(tblName VARCHAR(100))
-> BEGIN
-> DECLARE done INT DEFAULT 0;
-> DECLARE b,c INT;
->
-> DECLARE cur1 CURSOR FOR SELECT id FROM v_wondyfox;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
->
-> drop view if exists v_wondyfox ;
-> set @sql=concat("create view v_wondyfox as select * from ", tblName);
-> PREPARE stmt1 FROM @sql;
-> EXECUTE stmt1 ;
-> DEALLOCATE PREPARE stmt1;
->
-> OPEN cur1;
->
-> REPEAT
-> FETCH cur1 INTO b;
-> select b;
-> UNTIL done END REPEAT;
->
-> CLOSE cur1;
-> END
->
-> //
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> call curdemo('s')//
+------+
| b |
+------+
| 1 |
+------+
1 row in set (0.05 sec)+------+
| b |
+------+
| 2 |
+------+
1 row in set (0.05 sec)+------+
| b |
+------+
| 2 |
+------+
1 row in set (0.05 sec)Query OK, 0 rows affected (0.05 sec)mysql>
我用的就是6楼的办法,但是我又遇到问题了。就是我这一个存储过程需要大概10秒钟的时间,如果遇到这个存储过程同时被调用的情况,临时建的这个view就会冲突了。于是我就想到了随机view名称的方法,结果问题又回到了游标中表名不能动态的问题,呃……我该怎么办啊怎么办……