mysql带参数游标_mysql 支持带参数的游标吗

mysql 支持带参数的游标吗

关注:98  答案:2  mip版

解决时间 2021-01-16 07:15

提问者心魅

2021-01-15 15:28

mysql 支持带参数的游标吗

最佳答案

二级知识专家縱马獨歸

2021-01-15 15:35

你这种格式的没有，不过这个变量还是可以用下面这种方法实现的。

mysql> delimiter //

mysql>

mysql> CREATE PROCEDURE curdemo(IN V_GROUP INT)

-> BEGIN

-> DECLARE done INT DEFAULT 0;

-> DECLARE a INT;

-> DECLARE b CHAr(16);

->

-> DECLARE cur1 CURSOR FOR SELECt id,name FROM student where classid=V_GROUP;

-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

->

-> OPEN cur1;

->

-> REPEAT

-> FETCH cur1 INTO a, b;

-> select a,b;

-> UNTIL done END REPEAT;

->

-> CLOSE cur1;

-> END

->

->

-> //

Query OK, 0 rows affected (0.20 sec)

mysql>

mysql> delimiter ;

mysql>

mysql> SELECT id,name,classid FROM student ;

+----------+--------+---------+

| id | name | classid |

+----------+--------+---------+

| 91010101 | 丁乙甲 | 10101 |

| 91010102 | 索春龙 | 10101 |

| 91010103 | 刘莉莉 | 10101 |

| 91010104 | 隋鑫 | 10101 |

| 91010105 | 长命 | 10101 |

| 91010201 | 河江山 | 10102 |

| 91010202 | 祝立业 | 10102 |

| 91010203 | 姚桂娇 | 10102 |

| 91010204 | 张冬梅 | 10102 |

| 91020101 | 蒋旭 | 10201 |

| 91020102 | 邱介臣 | 10201 |

| 91020103 | 王晓燕 | 10201 |

| 91020104 | 夏丽佳 | 10201 |

| 91020201 | 熊建业 | 10202 |

| 91020202 | 丛俊江 | 10202 |

| 91020203 | 周静燕 | 10202 |

| 91020204 | 黄爽 | 10202 |

| 91020301 | 魏国兴 | 10203 |

| 91020302 | 蒋洪涛 | 10203 |

| 91020303 | 倪春红 | 10203 |

| 91030101 | 张文天 | 10301 |

| 91030102 | 姜洋 | 10301 |

| 91030103 | 赵聪 | 10301 |

| 91030201 | 王飞 | 10302 |

+----------+--------+---------+

24 rows in set (0.00 sec)

mysql>

mysql> CALL curdemo(10101);

+----------+--------+

| a | b |

+----------+--------+

| 91010101 | 丁乙甲 |

+----------+--------+

1 row in set (0.00 sec)

+----------+--------+

| a | b |

+----------+--------+

| 91010102 | 索春龙 |

+----------+--------+

1 row in set (0.00 sec)

+----------+--------+

| a | b |

+----------+--------+

| 91010103 | 刘莉莉 |

+----------+--------+

1 row in set (0.00 sec)

+----------+------+

| a | b |

+----------+------+

| 91010104 | 隋鑫 |

+----------+------+

1 row in set (0.00 sec)

+----------+------+

| a | b |

+----------+------+

| 91010105 | 长命 |

+----------+------+

1 row in set (0.00 sec)

+----------+------+

| a | b |

+----------+------+

| 91010105 | 长命 |

+----------+------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

全部回答

1楼抚弦思华年

2021-01-15 16:23

可以，参考：

create procedure ora_sp_cur_with_param_mysql()

begin

declare var1 float default 0;

declare val1 float;

declare cur1 cursor for

select col1 from tab1

where col5 = val1;

set val1 = 0;

open cur1;

fetch cur1 into var1;

close cur1;

set val1 = 5;

open cur1;

fetch cur1 into var1;

close cur1;

end;

我要举报

如果感觉以上信息为低俗/不良/侵权的信息，可以点下面链接进行举报，我们会做出相应处理，感谢你的支持！

点此我要举报以上信息！

推荐资讯

大家都在看