測試表 level
代碼如下:
create table test.level (name varchar(20));
初始化
代碼如下:
drop procedure if exists useCursor //
CREATE PROCEDURE useCursor() BEGIN
declare tmpName varchar(20) default '' ; declare allName varchar(255) default '' ; declare cur1 CURSOR FOR SELECT name FROM test.level ;
並設置 循環使用 變量 tmpname 為 null 跳出循環。
代碼如下:
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;
開游標 代碼如下:
OPEN cur1;
游標向下走一步 代碼如下:
FETCH cur1 INTO tmpName;
循環體 這很明顯 把MySQL 游標查詢出的 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()//
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 ;
repeat循環游標: 代碼如下:
/*創建過程*/ 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 ;