1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
|
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
drop procedure if exists p1;
drop procedure if exists p2;
drop function if exists f1;
drop table if exists t1,t2;
drop view if exists v1;
create table t1 (a int);
SET GLOBAL log_bin_trust_function_creators = 1;
create procedure p1()
begin
declare spv int default 0;
while spv < 5 do
insert into t1 values(spv+1);
set spv=spv+1;
end while;
end//
call p1();
SELECT * FROM t1 ORDER BY a;
a
1
2
3
4
5
SELECT * FROM t1 ORDER BY a;
a
1
2
3
4
5
create procedure p2()
begin
declare a int default 4;
create table t2 as select a;
end//
call p2();
SELECT * FROM t2 ORDER BY a;
a
4
SELECT * FROM t2 ORDER BY a;
a
4
drop procedure p1;
drop procedure p2;
drop table t2;
create function f1(x int) returns int
begin
insert into t1 values(x);
return x+1;
end//
create procedure p1(a int, b int)
begin
declare v int default f1(5);
if (f1(6)) then
select 'yes';
end if;
set v = f1(7);
while f1(8) < 1 do
select 'this cant be';
end while;
end//
call p1(f1(1), f1(2));
yes
yes
SELECT * FROM t1 ORDER BY a;
a
1
1
2
2
3
4
5
5
6
7
8
create table t2(a int);
insert into t2 values (10),(11);
SELECT a,f1(a) FROM t2 ORDER BY a;
a f1(a)
10 11
11 12
insert into t2 select f1(3);
SELECT 'master:',a FROM t1 ORDER BY a;
master: a
master: 1
master: 1
master: 2
master: 2
master: 3
master: 3
master: 4
master: 5
master: 5
master: 6
master: 7
master: 8
master: 10
master: 11
SELECT 'slave:',a FROM t1 ORDER BY a;
slave: a
slave: 1
slave: 1
slave: 2
slave: 2
slave: 3
slave: 3
slave: 4
slave: 5
slave: 5
slave: 6
slave: 7
slave: 8
slave: 10
slave: 11
drop procedure p1;
delete from t1;
delete from t2;
delete from t1;
insert into t2 values(1),(2);
create view v1 as select f1(a) as f from t2;
select * from v1 order by f;
f
2
3
SELECT 'master:',a FROM t1 ORDER BY a;
master: a
master: 1
master: 2
SELECT 'slave:',a FROM t1 ORDER BY a;
slave: a
slave: 1
slave: 2
drop view v1;
delete from t1;
prepare s1 from 'select f1(?)';
set @xx=123;
execute s1 using @xx;
f1(?)
124
SELECT 'master:',a FROM t1 ORDER BY a;
master: a
master: 123
SELECT 'slave:',a FROM t1 ORDER BY a;
slave: a
slave: 123
delete from t1;
create procedure p1(spv int)
begin
declare c cursor for select f1(spv) from t2;
while (spv > 2) do
open c;
fetch c into spv;
close c;
set spv= spv - 10;
end while;
end//
call p1(15);
SELECT 'master:',a FROM t1 ORDER BY a;
master: a
master: 6
master: 6
master: 15
master: 15
SELECT 'slave:',a FROM t1 ORDER BY a;
slave: a
slave: 6
slave: 6
slave: 15
slave: 15
drop procedure p1;
drop function f1;
drop table t1,t2;
create table t1 (a int);
create procedure p1()
begin
insert into t1 values(@x);
set @x=@x+1;
insert into t1 values(@x);
if (f2()) then
insert into t1 values(1243);
end if;
end//
create function f2() returns int
begin
insert into t1 values(@z);
set @z=@z+1;
insert into t1 values(@z);
return 0;
end//
create function f1() returns int
begin
insert into t1 values(@y);
call p1();
return 0;
end//
set @x=10;
set @y=20;
set @z=100;
select f1();
f1()
0
set @x=30;
call p1();
SELECT 'master', a FROM t1 ORDER BY a;
master a
master 10
master 11
master 20
master 30
master 31
master 100
master 101
master 101
master 102
SELECT 'slave', a FROM t1 ORDER BY a;
slave a
slave 10
slave 11
slave 20
slave 30
slave 31
slave 100
slave 101
slave 101
slave 102
drop table t1;
drop function f1;
drop function f2;
drop procedure p1;
SET GLOBAL log_bin_trust_function_creators = 0;
|
