Debian Codegraph @

mysql-test/suite/engines/funcs/t/sf_cursor.test

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