数码鹭岛论坛

标题: mysql存储过程之cursor [打印本页]
作者: 翔子    时间: 2011-10-26 00:16
标题: mysql存储过程之cursor
  1. CREATE PROCEDURE curdemo()
  2. BEGIN
  3.   DECLARE done INT DEFAULT 0;
  4.   DECLARE a CHAR(16);
  5.   DECLARE b,c INT;
  6.   DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  7.   DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  8.   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  10.   OPEN cur1;
  11.   OPEN cur2;

  13.   read_loop: LOOP
  14.     FETCH cur1 INTO a, b;
  15.     FETCH cur2 INTO c;
  16.     IF done THEN
  17.       LEAVE read_loop;
  18.     END IF;
  19.     IF b < c THEN
  20.       INSERT INTO test.t3 VALUES (a,b);
  21.     ELSE
  22.       INSERT INTO test.t3 VALUES (a,c);
  23.     END IF;
  24.   END LOOP;

  26.   CLOSE cur1;
  27.   CLOSE cur2;
  28. END;
复制代码
作者: 翔子    时间: 2011-10-26 00:18
标题: 嵌套cursor的例子
这里用一个临时cur2做暂存
  1. DROP PROCEDURE IF EXISTS get_major_info_for_all_dept;

  3. DELIMITER //
  4. -- Create procedure get_major_info_for_all_dept()
  5. CREATE PROCEDURE get_major_info_for_all_dept()

  7. BEGIN

  9. DECLARE dept_id VARCHAR(3) ;
  10. DECLARE major_description VARCHAR(50) ;
  11. DECLARE concat_description VARCHAR(60);
  12. DECLARE flag1 VARCHAR(5) DEFAULT 'START' ;
  13. DECLARE flag2 VARCHAR(5) DEFAULT 'START' ;

  15. -- Declare cursor for outside looping purpose to get all departments from the dept table
  16. DECLARE getDept CURSOR FOR
  17. SELECT deptid
  18. FROM dept
  19. ORDER BY deptid;
  20. --Declare cursor for getting each and every major description of each department.
  21. DECLARE getMajorsFromEachDept CURSOR FOR
  22. SELECT description
  23. FROM major
  24. WHERE deptid = dept_id;

  26. -- Record not found means for inner loop no more major record left for that dept_id and for outer loop no more record left for dept-id
  27. DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag1 = 'END';

  29. -- Open cursor
  30. OPEN getDept;

  32. -- loop to extract each major of each department and insert into messages table
  33. -- until no record left.

  35. WHILE flag1<>'END' DO
  36. -- to get deptid
  37. FETCH getDept INTO dept_id;

  39. IF flag1 <> 'END' THEN

  41. OPEN getMajorsFromEachDept;

  43. SET flag2= flag1;
  44. WHILE flag1 <> 'END' DO
  45. FETCH getMajorsFromEachDept INTO major_description;
  46. IF flag1 <> 'END' THEN
  47. SET concat_description = CONCAT(dept_id,'*',major_description);
  48. INSERT INTO messages
  49. VALUES (concat_description);
  50. END IF;
  51. END WHILE;
  52. -- free memory
  53. CLOSE getMajorsFromEachDept;

  55. SET flag1 = flag2;
  56. END IF;
  57. END WHILE;
  58. -- Loop end.

  60. -- free memory
  61. CLOSE getDept;

  63. --- Display all records of Messages Table---------
  64. SELECT * FROM messages;

  66. END;
  67. //

  69. DELIMITER ;
复制代码
作者: 翔子    时间: 2011-10-26 00:19
标题: 另一种cursor方案
  1. CREATE PROCEDURE curdemo()
  2. BEGIN
  3.   DECLARE a CHAR(16);
  4.   DECLARE b,c INT;
  5.   DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  6.   DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;

  8.   OPEN cur1;
  9.   OPEN cur2;

  11.   BEGIN
  12.     DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
  13.     LOOP
  14.       FETCH cur1 INTO a, b;
  15.       FETCH cur2 INTO c;
  16.       IF b < c THEN
  17.         INSERT INTO test.t3 VALUES (a,b);
  18.       ELSE
  19.         INSERT INTO test.t3 VALUES (a,c);
  20.       END IF;
  21.     END LOOP;
  22.   END;

  24.   CLOSE cur1;
  25.   CLOSE cur2;
  26. END
复制代码




欢迎光临 数码鹭岛论坛 (http://www.clore.net/forum/) Powered by Discuz! X3.2