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