Debian Codegraph @

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

raw
1 --disable_ps2_protocol
2 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
3 CREATE TABLE t2 (i INTEGER NULL);
4 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
5 CREATE TABLE t4 (i INTEGER NULL);
6 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
7 INSERT INTO t2 VALUES(1),(2),(3);
8 DELIMITER //;
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 DELIMITER ;//
35 SELECT sf1();
36 SELECT * FROM t1 ORDER BY id;
37 SELECT * FROM t2 ORDER BY i;
38 SELECT * FROM t3 ORDER BY id;
39 INSERT INTO t4 VALUES(sf1());
40 SELECT * FROM t1 ORDER BY id;
41 SELECT * FROM t2 ORDER BY i;
42 SELECT * FROM t3 ORDER BY id;
43 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
44 SELECT * FROM t1 ORDER BY id;
45 SELECT * FROM t2 ORDER BY i;
46 SELECT * FROM t3 ORDER BY id;
47 DELETE FROM t4 WHERE i = sf1() + 1;
48 SELECT * FROM t1 ORDER BY id;
49 SELECT * FROM t2 ORDER BY i;
50 SELECT * FROM t3 ORDER BY id;
51 DROP FUNCTION sf1;
52 DROP TABLE t1,t2,t3,t4;
53