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