Debian Codegraph @

mysql-test/suite/engines/funcs/r/rpl_row_sp005.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection master;
4 DROP PROCEDURE IF EXISTS test.p1;
5 DROP PROCEDURE IF EXISTS test.p2;
6 DROP TABLE IF EXISTS test.t2;
7 DROP TABLE IF EXISTS test.t1;
8 DROP TABLE IF EXISTS test.t3;
9 CREATE TABLE IF NOT EXISTS test.t1(id INT, data CHAR(16),PRIMARY KEY(id));
10 CREATE TABLE IF NOT EXISTS test.t2(id2 INT,PRIMARY KEY(id2));
11 CREATE TABLE IF NOT EXISTS test.t3(id3 INT,PRIMARY KEY(id3), c CHAR(16));
12 CREATE PROCEDURE test.p1()
13 BEGIN
14 DECLARE done INT DEFAULT 0;
15 DECLARE spa CHAR(16);
16 DECLARE spb,spc INT;
17 DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1 ORDER BY id;
18 DECLARE cur2 CURSOR FOR SELECT id2 FROM test.t2 ORDER BY id2;
19 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
20 OPEN cur1;
21 OPEN cur2;
22 REPEAT
23 FETCH cur1 INTO spb, spa;
24 FETCH cur2 INTO spc;
25 IF NOT done THEN
26 IF spb < spc THEN
27 INSERT INTO test.t3 VALUES (spb,spa);
28 ELSE
29 INSERT INTO test.t3 VALUES (spc,spa);
30 END IF;
31 END IF;
32 UNTIL done END REPEAT;
33 CLOSE cur1;
34 CLOSE cur2;
35 END|
36 CREATE PROCEDURE test.p2()
37 BEGIN
38 INSERT INTO test.t1 VALUES (4,'MySQL'),(20,'ROCKS'),(11,'Texas'),(10,'kyle');
39 INSERT INTO test.t2 VALUES (4),(2),(1),(3);
40 UPDATE test.t1 SET id=id+4 WHERE id=4;
41 END|
42 CALL test.p2();
43 SELECT * FROM test.t1 ORDER BY id;
44 id data
45 8 MySQL
46 10 kyle
47 11 Texas
48 20 ROCKS
49 SELECT * FROM test.t2 ORDER BY id2;
50 id2
51 1
52 2
53 3
54 4
55 connection slave;
56 SELECT * FROM test.t1 ORDER BY id;
57 id data
58 8 MySQL
59 10 kyle
60 11 Texas
61 20 ROCKS
62 SELECT * FROM test.t2 ORDER BY id2;
63 id2
64 1
65 2
66 3
67 4
68 connection master;
69 CALL test.p1();
70 SELECT * FROM test.t3 ORDER BY id3;
71 id3 c
72 1 MySQL
73 2 kyle
74 3 Texas
75 4 ROCKS
76 connection slave;
77 SELECT * FROM test.t3 ORDER BY id3;
78 id3 c
79 1 MySQL
80 2 kyle
81 3 Texas
82 4 ROCKS
83 connection master;
84 ALTER PROCEDURE test.p1 MODIFIES SQL DATA;
85 connection master;
86 DROP PROCEDURE IF EXISTS test.p1;
87 DROP PROCEDURE IF EXISTS test.p2;
88 DROP TABLE IF EXISTS test.t1;
89 DROP TABLE IF EXISTS test.t2;
90 DROP TABLE IF EXISTS test.t3;
91 connection slave;
92 include/rpl_end.inc