Debian Codegraph @

mysql-test/suite/engines/funcs/r/sp_cursor.result

raw
1 DROP TABLE IF EXISTS t1,t2,t3;
2 DROP PROCEDURE IF EXISTS sp1;
3 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
4 CREATE TABLE t2 (i INTEGER NULL);
5 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
6 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
7 INSERT INTO t2 VALUES(1),(2),(3);
8 CREATE PROCEDURE sp1()
9 BEGIN
10 DECLARE done INT DEFAULT 0;
11 DECLARE a CHAR(16);
12 DECLARE b,c INT;
13 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
14 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
15 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
16 OPEN cur1;
17 OPEN cur2;
18 REPEAT
19 FETCH cur1 INTO a, b;
20 FETCH cur2 INTO c;
21 IF NOT done THEN
22 IF b < c THEN
23 INSERT INTO t3 VALUES (a,b);
24 ELSE
25 INSERT INTO t3 VALUES (a,c);
26 END IF;
27 END IF;
28 UNTIL done END REPEAT;
29 CLOSE cur1;
30 CLOSE cur2;
31 END//
32 CALL sp1();
33 SELECT * FROM t1 ORDER BY id;
34 id data
35 1 1
36 2 1
37 3 4
38 4 5
39 SELECT * FROM t2 ORDER BY i;
40 i
41 1
42 2
43 3
44 SELECT * FROM t3 ORDER BY id;
45 id data
46 1 1
47 2 1
48 3 3
49 DROP PROCEDURE sp1;
50 DROP TABLE t1,t2,t3;