Debian Codegraph @

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

raw
1 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2 CREATE TABLE t2 (i INTEGER NULL);
3 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
4 CREATE TABLE t4 (i INTEGER NULL);
5 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
6 INSERT INTO t2 VALUES(1),(2),(3);
7 CREATE FUNCTION sf1() RETURNS INTEGER
8 BEGIN
9 DECLARE done INT DEFAULT 0;
10 DECLARE a CHAR(16);
11 DECLARE b,c INT;
12 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
13 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
14 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
15 OPEN cur1;
16 OPEN cur2;
17 REPEAT
18 FETCH cur1 INTO a, b;
19 FETCH cur2 INTO c;
20 IF NOT done THEN
21 IF b < c THEN
22 INSERT INTO t3 VALUES (a,b);
23 ELSE
24 INSERT INTO t3 VALUES (a,c);
25 END IF;
26 END IF;
27 UNTIL done END REPEAT;
28 CLOSE cur1;
29 CLOSE cur2;
30 RETURN 0;
31 END//
32 SELECT sf1();
33 sf1()
34 0
35 SELECT * FROM t1 ORDER BY id;
36 id data
37 1 1
38 2 1
39 3 4
40 4 5
41 SELECT * FROM t2 ORDER BY i;
42 i
43 1
44 2
45 3
46 SELECT * FROM t3 ORDER BY id;
47 id data
48 1 1
49 2 1
50 3 3
51 INSERT INTO t4 VALUES(sf1());
52 SELECT * FROM t1 ORDER BY id;
53 id data
54 1 1
55 2 1
56 3 4
57 4 5
58 SELECT * FROM t2 ORDER BY i;
59 i
60 1
61 2
62 3
63 SELECT * FROM t3 ORDER BY id;
64 id data
65 1 1
66 1 1
67 2 1
68 2 1
69 3 3
70 3 3
71 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
72 SELECT * FROM t1 ORDER BY id;
73 id data
74 1 1
75 2 1
76 3 4
77 4 5
78 SELECT * FROM t2 ORDER BY i;
79 i
80 1
81 2
82 3
83 SELECT * FROM t3 ORDER BY id;
84 id data
85 1 1
86 1 1
87 1 1
88 1 1
89 2 1
90 2 1
91 2 1
92 2 1
93 3 3
94 3 3
95 3 3
96 3 3
97 DELETE FROM t4 WHERE i = sf1() + 1;
98 SELECT * FROM t1 ORDER BY id;
99 id data
100 1 1
101 2 1
102 3 4
103 4 5
104 SELECT * FROM t2 ORDER BY i;
105 i
106 1
107 2
108 3
109 SELECT * FROM t3 ORDER BY id;
110 id data
111 1 1
112 1 1
113 1 1
114 1 1
115 1 1
116 2 1
117 2 1
118 2 1
119 2 1
120 2 1
121 3 3
122 3 3
123 3 3
124 3 3
125 3 3
126 DROP FUNCTION sf1;
127 DROP TABLE t1,t2,t3,t4;