请使用 mysql 1.5 或以上version;
测试表 level ;
create
table
test.
level
(name
varchar
(
20
));
再 insert 些数据 ;
代码
/*
初始化
*/
drop procedure if exists useCursor //
/* 建立 存储过程 create */
CREATE PROCEDURE useCursor()
BEGIN
/* 局部变量的定义 declare */
declare tmpName varchar ( 20 ) default '' ;
declare allName varchar ( 255 ) default '' ;
declare cur1 CURSOR FOR SELECT name FROM test. level ;
/* mysql 不知道为什么用异常加入判断 ?
* 此请参考官方文档 20.2.11. 光标 光标
* 这把 游标 异常后 捕捉
* 并设置 循环使用 变量 tmpname 为 null 跳出循环。
*/
declare CONTINUE HANDLER FOR SQLSTATE ' 02000 ' SET tmpname = null ;
/* 开游标 */
OPEN cur1;
/* 游标向下走一步 */
FETCH cur1 INTO tmpName;
/* 循环体 这很明显 把游标查询出的 name 都加起并用 ; 号隔开 */
WHILE ( tmpname is not null ) DO
set tmpName = CONCAT(tmpName ,";") ;
set allName = CONCAT(allName ,tmpName) ;
/* 游标向下走一步 */
FETCH cur1 INTO tmpName;
END WHILE ;
CLOSE cur1;
select allName ;
END ; //
call useCursor() //
drop procedure if exists useCursor //
/* 建立 存储过程 create */
CREATE PROCEDURE useCursor()
BEGIN
/* 局部变量的定义 declare */
declare tmpName varchar ( 20 ) default '' ;
declare allName varchar ( 255 ) default '' ;
declare cur1 CURSOR FOR SELECT name FROM test. level ;
/* mysql 不知道为什么用异常加入判断 ?
* 此请参考官方文档 20.2.11. 光标 光标
* 这把 游标 异常后 捕捉
* 并设置 循环使用 变量 tmpname 为 null 跳出循环。
*/
declare CONTINUE HANDLER FOR SQLSTATE ' 02000 ' SET tmpname = null ;
/* 开游标 */
OPEN cur1;
/* 游标向下走一步 */
FETCH cur1 INTO tmpName;
/* 循环体 这很明显 把游标查询出的 name 都加起并用 ; 号隔开 */
WHILE ( tmpname is not null ) DO
set tmpName = CONCAT(tmpName ,";") ;
set allName = CONCAT(allName ,tmpName) ;
/* 游标向下走一步 */
FETCH cur1 INTO tmpName;
END WHILE ;
CLOSE cur1;
select allName ;
END ; //
call useCursor() //
运行结果:
代码
mysql
>
call useCursor()
//
+ -- ------------------------------------+
| allName |
+ -- ------------------------------------+
| f1;c3;c6;c5;c2;c4;c1;f1;f3;f4;f2;f5; |
+ -- ------------------------------------+
1 row in set ( 0.00 sec)
+ -- ------------------------------------+
| allName |
+ -- ------------------------------------+
| f1;c3;c6;c5;c2;c4;c1;f1;f3;f4;f2;f5; |
+ -- ------------------------------------+
1 row in set ( 0.00 sec)
代码
DELIMITER $$
DROP PROCEDURE IF EXITS cursor_example$$
CREATE PROCEDURE cursor_example()
READS SQL DATA
BEGIN
DECLARE l_employee_id INT ;
DECLARE l_salary NUMERIC( 8 , 2 );
DECLARE l_department_id INT ;
DECLARE done INT DEFAULT 0 ;
DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;
OPEN cur1;
emp_loop: LOOP
FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
IF done = 1 THEN
LEAVE emp_loop;
END IF ;
END LOOP emp_loop;
CLOSE cur1;
END $$
DELIMITER ;
DROP PROCEDURE IF EXITS cursor_example$$
CREATE PROCEDURE cursor_example()
READS SQL DATA
BEGIN
DECLARE l_employee_id INT ;
DECLARE l_salary NUMERIC( 8 , 2 );
DECLARE l_department_id INT ;
DECLARE done INT DEFAULT 0 ;
DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;
OPEN cur1;
emp_loop: LOOP
FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
IF done = 1 THEN
LEAVE emp_loop;
END IF ;
END LOOP emp_loop;
CLOSE cur1;
END $$
DELIMITER ;
代码
/*
创建过程
*/
DELIMITER //
DROP PROCEDURE IF EXISTS test //
CREATE PROCEDURE test()
BEGIN
DECLARE done INT DEFAULT 0 ;
DECLARE a VARCHAR ( 200 ) DEFAULT '' ;
DECLARE c VARCHAR ( 200 ) DEFAULT '' ;
DECLARE mycursor CURSOR FOR SELECT fusername FROM uchome_friend;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;
OPEN mycursor;
REPEAT
FETCH mycursor INTO a;
IF NOT done THEN
SET c = CONCAT(c,a); /* 字符串相加 */
END IF ;
UNTIL done END REPEAT;
CLOSE mycursor;
SELECT c;
END //
DELIMITER ;
DELIMITER //
DROP PROCEDURE IF EXISTS test //
CREATE PROCEDURE test()
BEGIN
DECLARE done INT DEFAULT 0 ;
DECLARE a VARCHAR ( 200 ) DEFAULT '' ;
DECLARE c VARCHAR ( 200 ) DEFAULT '' ;
DECLARE mycursor CURSOR FOR SELECT fusername FROM uchome_friend;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;
OPEN mycursor;
REPEAT
FETCH mycursor INTO a;
IF NOT done THEN
SET c = CONCAT(c,a); /* 字符串相加 */
END IF ;
UNTIL done END REPEAT;
CLOSE mycursor;
SELECT c;
END //
DELIMITER ;