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;
   
9. 
   
10.   OPEN cur1;
    
11.   OPEN cur2;
    
12. 
    
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;
    
25. 
    
26.   CLOSE cur1;
    
27.   CLOSE cur2;
    
28. END;

复制代码

翔子

这里用一个临时cur2做暂存

1. DROP PROCEDURE IF EXISTS get_major_info_for_all_dept;
   
2. 
   
3. DELIMITER //
   
4. -- Create procedure get_major_info_for_all_dept()
   
5. CREATE PROCEDURE get_major_info_for_all_dept()
   
6. 
   
7. BEGIN
   
8. 
   
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' ;
    
14. 
    
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;
    
25. 
    
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';
    
28. 
    
29. -- Open cursor
    
30. OPEN getDept;
    
31. 
    
32. -- loop to extract each major of each department and insert into messages table
    
33. -- until no record left.
    
34. 
    
35. WHILE flag1<>'END' DO
    
36. -- to get deptid
    
37. FETCH getDept INTO dept_id;
    
38. 
    
39. IF flag1 <> 'END' THEN
    
40. 
    
41. OPEN getMajorsFromEachDept;
    
42. 
    
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;
    
54. 
    
55. SET flag1 = flag2;
    
56. END IF;
    
57. END WHILE;
    
58. -- Loop end.
    
59. 
    
60. -- free memory
    
61. CLOSE getDept;
    
62. 
    
63. --- Display all records of Messages Table---------
    
64. SELECT * FROM messages;
    
65. 
    
66. END;
    
67. //
    
68. 
    
69. DELIMITER ;
    

复制代码

翔子

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;
   
7. 
   
8.   OPEN cur1;
   
9.   OPEN cur2;
   
10. 
    
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;
    
23. 
    
24.   CLOSE cur1;
    
25.   CLOSE cur2;
    
26. END
    

复制代码