Debian Codegraph @

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

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