简述:
这里主要研究一下,在Mysql中游标的使用,此外关注到游标使用过程中循环的调用写法
在testDB下有一个张person表,其中的成员如下图所示,
这里设计一个procedure,在这个函数中,使用到了Mysql中的游标,用于指向每一行的addr这一属性,然后把所有的addr属性合在一起,用分号分隔。
原先person表中的数据如下,
- select name,addr from person;
下面是整个过程的实现SQL代码,
实现写法1:
- drop procedure if exists useCursor ;
- delimiter //
- CREATE PROCEDURE useCursor()
- BEGIN
- DECLARE oneAddr varchar(8) default '';
- DECLARE allAddr varchar(8) default '';
- DECLARE curl CURSOR FOR SELECT addr FROM test.person;
- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET oneAddr = null;
- OPEN curl;
- FETCH curl INTO oneAddr;
- WHILE(oneAddr is not null) DO
- set oneAddr = CONCAT(oneAddr, ';');
- set allAddr = CONCAT(allAddr, oneAddr);
- FETCH curl into oneAddr;
- END WHILE;
- CLOSE curl;
- SELECT allAddr;
- END;//
- call useCursor();
实现写法2:
- drop procedure if exists useCursor;
- delimiter //
- CREATE PROCEDURE useCursor()
- BEGIN
- DECLARE oneAddr varchar(8) default '';
- DECLARE allAddr varchar(8) default '';
- DECLARE done INT DEFAULT 0;
- DECLARE curl CURSOR FOR SELECT addr FROM test.person;
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
- OPEN curl;
- REPEAT
- FETCH curl INTO oneAddr;
- IF NOT done THEN
- set oneAddr = CONCAT(oneAddr, ';');
- set allAddr = CONCAT(allAddr, oneAddr);
- END IF;
- UNTIL done END REPEAT;
- CLOSE curl;
- select allAddr;
- END;//
- call useCursor();
实现写法3:
- drop procedure if exists useCursor;
- delimiter //
- CREATE PROCEDURE useCursor()
- BEGIN
- DECLARE oneAddr varchar(8) default '';
- DECLARE allAddr varchar(8) default '';
- DECLARE done bool DEFAULT false;
- DECLARE curl CURSOR FOR SELECT addr FROM test.person;
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
- OPEN curl;
- personLoop: LOOP
- FETCH curl INTO oneAddr;
- IF done THEN
- LEAVE personLoop;
- ELSE
- set oneAddr = CONCAT(oneAddr, ';');
- set allAddr = CONCAT(allAddr, oneAddr);
- END IF;
- END LOOP personLoop;
- CLOSE curl;
- select allAddr;
- END;//
- call useCursor();
结果输出: