2
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
7
DROP PROCEDURE IF EXISTS test.p1;
8
DROP PROCEDURE IF EXISTS test.p2;
9
DROP TABLE IF EXISTS test.t2;
10
DROP TABLE IF EXISTS test.t1;
11
DROP TABLE IF EXISTS test.t3;
12
CREATE TABLE IF NOT EXISTS test.t1(id INT, data CHAR(16),PRIMARY KEY(id));
13
CREATE TABLE IF NOT EXISTS test.t2(id2 INT,PRIMARY KEY(id2));
14
CREATE TABLE IF NOT EXISTS test.t3(id3 INT,PRIMARY KEY(id3), c CHAR(16));
15
CREATE PROCEDURE test.p1()
17
DECLARE done INT DEFAULT 0;
20
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1 ORDER BY id;
21
DECLARE cur2 CURSOR FOR SELECT id2 FROM test.t2 ORDER BY id2;
22
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
26
FETCH cur1 INTO spb, spa;
30
INSERT INTO test.t3 VALUES (spb,spa);
32
INSERT INTO test.t3 VALUES (spc,spa);
35
UNTIL done END REPEAT;
39
CREATE PROCEDURE test.p2()
41
INSERT INTO test.t1 VALUES (4,'MySQL'),(20,'ROCKS'),(11,'Texas'),(10,'kyle');
42
INSERT INTO test.t2 VALUES (4),(2),(1),(3);
43
UPDATE test.t1 SET id=id+4 WHERE id=4;
46
< ---- Master selects-- >
47
-------------------------
49
SELECT * FROM test.t1 ORDER BY id;
55
SELECT * FROM test.t2 ORDER BY id2;
62
< ---- Slave selects-- >
63
------------------------
64
SELECT * FROM test.t1 ORDER BY id;
70
SELECT * FROM test.t2 ORDER BY id2;
77
< ---- Master selects-- >
78
-------------------------
80
SELECT * FROM test.t3 ORDER BY id3;
87
< ---- Slave selects-- >
88
------------------------
89
SELECT * FROM test.t3 ORDER BY id3;
95
ALTER PROCEDURE test.p1 MODIFIES SQL DATA;
96
DROP PROCEDURE IF EXISTS test.p1;
97
DROP PROCEDURE IF EXISTS test.p2;
98
DROP TABLE IF EXISTS test.t1;
99
DROP TABLE IF EXISTS test.t2;
100
DROP TABLE IF EXISTS test.t3;