/
sp.result
8235 lines (8128 loc) · 192 KB
/
sp.result
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
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
use test;
drop table if exists t1,t2,t3,t4;
drop view if exists v1;
drop procedure if exists p1;
drop procedure if exists p2;
drop function if exists f1;
drop function if exists f2;
create table t1 (
id char(16) not null default '',
data int not null
);
create table t2 (
s char(16),
i int,
d double
);
drop procedure if exists foo42;
create procedure foo42()
insert into test.t1 values ("foo", 42);
call foo42();
select * from t1;
id data
foo 42
delete from t1;
drop procedure foo42;
drop procedure if exists bar;
create procedure bar(x char(16), y int)
insert into test.t1 values (x, y);
call bar("bar", 666);
select * from t1;
id data
bar 666
delete from t1;
drop procedure if exists empty|
create procedure empty()
begin
end|
call empty()|
drop procedure empty|
drop procedure if exists scope|
create procedure scope(a int, b float)
begin
declare b int;
declare c float;
begin
declare c int;
end;
end|
drop procedure scope|
drop procedure if exists two|
create procedure two(x1 char(16), x2 char(16), y int)
begin
insert into test.t1 values (x1, y);
insert into test.t1 values (x2, y);
end|
call two("one", "two", 3)|
select * from t1|
id data
one 3
two 3
delete from t1|
drop procedure two|
drop procedure if exists locset|
create procedure locset(x char(16), y int)
begin
declare z1, z2 int;
set z1 = y;
set z2 = z1+2;
insert into test.t1 values (x, z2);
end|
call locset("locset", 19)|
select * from t1|
id data
locset 21
delete from t1|
drop procedure locset|
drop procedure if exists setcontext|
create procedure setcontext()
begin
declare data int default 2;
insert into t1 (id, data) values ("foo", 1);
replace t1 set data = data, id = "bar";
update t1 set id = "kaka", data = 3 where t1.data = data;
end|
call setcontext()|
select * from t1 order by data|
id data
foo 1
kaka 3
delete from t1|
drop procedure setcontext|
create table t3 ( d date, i int, f double, s varchar(32) )|
drop procedure if exists nullset|
create procedure nullset()
begin
declare ld date;
declare li int;
declare lf double;
declare ls varchar(32);
set ld = null, li = null, lf = null, ls = null;
insert into t3 values (ld, li, lf, ls);
insert into t3 (i, f, s) values ((ld is null), 1, "ld is null"),
((li is null), 1, "li is null"),
((li = 0), null, "li = 0"),
((lf is null), 1, "lf is null"),
((lf = 0), null, "lf = 0"),
((ls is null), 1, "ls is null");
end|
call nullset()|
select * from t3|
d i f s
NULL NULL NULL NULL
NULL 1 1 ld is null
NULL 1 1 li is null
NULL NULL NULL li = 0
NULL 1 1 lf is null
NULL NULL NULL lf = 0
NULL 1 1 ls is null
drop table t3|
drop procedure nullset|
drop procedure if exists mixset|
create procedure mixset(x char(16), y int)
begin
declare z int;
set @z = y, z = 666, max_join_size = 100;
insert into test.t1 values (x, z);
end|
call mixset("mixset", 19)|
show variables like 'max_join_size'|
Variable_name Value
max_join_size 100
select id,data,@z from t1|
id data @z
mixset 666 19
delete from t1|
drop procedure mixset|
drop procedure if exists zip|
create procedure zip(x char(16), y int)
begin
declare z int;
call zap(y, z);
call bar(x, z);
end|
drop procedure if exists zap|
create procedure zap(x int, out y int)
begin
declare z int;
set z = x+1, y = z;
end|
call zip("zip", 99)|
select * from t1|
id data
zip 100
delete from t1|
drop procedure zip|
drop procedure bar|
call zap(7, @zap)|
select @zap|
@zap
8
drop procedure zap|
drop procedure if exists c1|
create procedure c1(x int)
call c2("c", x)|
drop procedure if exists c2|
create procedure c2(s char(16), x int)
call c3(x, s)|
drop procedure if exists c3|
create procedure c3(x int, s char(16))
call c4("level", x, s)|
drop procedure if exists c4|
create procedure c4(l char(8), x int, s char(16))
insert into t1 values (concat(l,s), x)|
call c1(42)|
select * from t1|
id data
levelc 42
delete from t1|
drop procedure c1|
drop procedure c2|
drop procedure c3|
drop procedure c4|
drop procedure if exists iotest|
create procedure iotest(x1 char(16), x2 char(16), y int)
begin
call inc2(x2, y);
insert into test.t1 values (x1, y);
end|
drop procedure if exists inc2|
create procedure inc2(x char(16), y int)
begin
call inc(y);
insert into test.t1 values (x, y);
end|
drop procedure if exists inc|
create procedure inc(inout io int)
set io = io + 1|
call iotest("io1", "io2", 1)|
select * from t1 order by data desc|
id data
io2 2
io1 1
delete from t1|
drop procedure iotest|
drop procedure inc2|
drop procedure if exists incr|
create procedure incr(inout x int)
call inc(x)|
select @zap|
@zap
8
call incr(@zap)|
select @zap|
@zap
9
drop procedure inc|
drop procedure incr|
drop procedure if exists cbv1|
create procedure cbv1()
begin
declare y int default 3;
call cbv2(y+1, y);
insert into test.t1 values ("cbv1", y);
end|
drop procedure if exists cbv2|
create procedure cbv2(y1 int, inout y2 int)
begin
set y2 = 4711;
insert into test.t1 values ("cbv2", y1);
end|
call cbv1()|
select * from t1 order by data|
id data
cbv2 4
cbv1 4711
delete from t1|
drop procedure cbv1|
drop procedure cbv2|
insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)|
drop procedure if exists sub1|
create procedure sub1(id char(16), x int)
insert into test.t1 values (id, x)|
drop procedure if exists sub2|
create procedure sub2(id char(16))
begin
declare x int;
set x = (select sum(t.i) from test.t2 t);
insert into test.t1 values (id, x);
end|
drop procedure if exists sub3|
create function sub3(i int) returns int deterministic
return i+1|
call sub1("sub1a", (select 7))|
call sub1("sub1b", (select max(i) from t2))|
call sub1("sub1c", (select i,d from t2 limit 1))|
ERROR 21000: Operand should contain 1 column(s)
call sub1("sub1d", (select 1 from (select 1) a))|
call sub2("sub2")|
select * from t1 order by id|
id data
sub1a 7
sub1b 3
sub1d 1
sub2 6
select sub3((select max(i) from t2))|
sub3((select max(i) from t2))
4
drop procedure sub1|
drop procedure sub2|
drop function sub3|
delete from t1|
delete from t2|
drop procedure if exists a0|
create procedure a0(x int)
while x do
set x = x-1;
insert into test.t1 values ("a0", x);
end while|
call a0(3)|
select * from t1 order by data desc|
id data
a0 2
a0 1
a0 0
delete from t1|
drop procedure a0|
drop procedure if exists a|
create procedure a(x int)
while x > 0 do
set x = x-1;
insert into test.t1 values ("a", x);
end while|
call a(3)|
select * from t1 order by data desc|
id data
a 2
a 1
a 0
delete from t1|
drop procedure a|
drop procedure if exists b|
create procedure b(x int)
repeat
insert into test.t1 values (repeat("b",3), x);
set x = x-1;
until x = 0 end repeat|
call b(3)|
select * from t1 order by data desc|
id data
bbb 3
bbb 2
bbb 1
delete from t1|
drop procedure b|
drop procedure if exists b2|
create procedure b2(x int)
repeat(select 1 into outfile 'b2');
insert into test.t1 values (repeat("b2",3), x);
set x = x-1;
until x = 0 end repeat|
drop procedure b2|
drop procedure if exists c|
create procedure c(x int)
hmm: while x > 0 do
insert into test.t1 values ("c", x);
set x = x-1;
iterate hmm;
insert into test.t1 values ("x", x);
end while hmm|
call c(3)|
select * from t1 order by data desc|
id data
c 3
c 2
c 1
delete from t1|
drop procedure c|
drop procedure if exists d|
create procedure d(x int)
hmm: while x > 0 do
insert into test.t1 values ("d", x);
set x = x-1;
leave hmm;
insert into test.t1 values ("x", x);
end while|
call d(3)|
select * from t1|
id data
d 3
delete from t1|
drop procedure d|
drop procedure if exists e|
create procedure e(x int)
foo: loop
if x = 0 then
leave foo;
end if;
insert into test.t1 values ("e", x);
set x = x-1;
end loop foo|
call e(3)|
select * from t1 order by data desc|
id data
e 3
e 2
e 1
delete from t1|
drop procedure e|
drop procedure if exists f|
create procedure f(x int)
if x < 0 then
insert into test.t1 values ("f", 0);
elseif x = 0 then
insert into test.t1 values ("f", 1);
else
insert into test.t1 values ("f", 2);
end if|
call f(-2)|
call f(0)|
call f(4)|
select * from t1 order by data|
id data
f 0
f 1
f 2
delete from t1|
drop procedure f|
drop procedure if exists g|
create procedure g(x int)
case
when x < 0 then
insert into test.t1 values ("g", 0);
when x = 0 then
insert into test.t1 values ("g", 1);
else
insert into test.t1 values ("g", 2);
end case|
call g(-42)|
call g(0)|
call g(1)|
select * from t1 order by data|
id data
g 0
g 1
g 2
delete from t1|
drop procedure g|
drop procedure if exists h|
create procedure h(x int)
case x
when 0 then
insert into test.t1 values ("h0", x);
when 1 then
insert into test.t1 values ("h1", x);
else
insert into test.t1 values ("h?", x);
end case|
call h(0)|
call h(1)|
call h(17)|
select * from t1 order by data|
id data
h0 0
h1 1
h? 17
delete from t1|
drop procedure h|
drop procedure if exists i|
create procedure i(x int)
foo:
begin
if x = 0 then
leave foo;
end if;
insert into test.t1 values ("i", x);
end foo|
call i(0)|
call i(3)|
select * from t1|
id data
i 3
delete from t1|
drop procedure i|
insert into t1 values ("foo", 3), ("bar", 19)|
insert into t2 values ("x", 9, 4.1), ("y", -1, 19.2), ("z", 3, 2.2)|
drop procedure if exists sel1|
create procedure sel1()
begin
select * from t1 order by data;
end|
call sel1()|
id data
foo 3
bar 19
drop procedure sel1|
drop procedure if exists sel2|
create procedure sel2()
begin
select * from t1 order by data;
select * from t2 order by s;
end|
call sel2()|
id data
foo 3
bar 19
s i d
x 9 4.1
y -1 19.2
z 3 2.2
drop procedure sel2|
delete from t1|
delete from t2|
drop procedure if exists into_test|
create procedure into_test(x char(16), y int)
begin
insert into test.t1 values (x, y);
select id,data into x,y from test.t1 limit 1;
insert into test.t1 values (concat(x, "2"), y+2);
end|
call into_test("into", 100)|
select * from t1 order by data|
id data
into 100
into2 102
delete from t1|
drop procedure into_test|
drop procedure if exists into_tes2|
create procedure into_test2(x char(16), y int)
begin
insert into test.t1 values (x, y);
select id,data into x,@z from test.t1 limit 1;
insert into test.t1 values (concat(x, "2"), y+2);
end|
call into_test2("into", 100)|
select id,data,@z from t1 order by data|
id data @z
into 100 100
into2 102 100
delete from t1|
drop procedure into_test2|
drop procedure if exists into_test3|
create procedure into_test3()
begin
declare x char(16);
declare y int;
select * into x,y from test.t1 limit 1;
insert into test.t2 values (x, y, 0.0);
end|
insert into t1 values ("into3", 19)|
call into_test3()|
call into_test3()|
select * from t2|
s i d
into3 19 0
into3 19 0
delete from t1|
delete from t2|
drop procedure into_test3|
drop procedure if exists into_test4|
create procedure into_test4()
begin
declare x int;
select data into x from test.t1 limit 1;
insert into test.t3 values ("into4", x);
end|
delete from t1|
create table t3 ( s char(16), d int)|
call into_test4()|
select * from t3|
s d
into4 NULL
insert into t1 values ("i4", 77)|
call into_test4()|
select * from t3|
s d
into4 NULL
into4 77
delete from t1|
drop table t3|
drop procedure into_test4|
drop procedure if exists into_outfile|
create procedure into_outfile(x char(16), y int)
begin
insert into test.t1 values (x, y);
select * into outfile "MYSQLTEST_VARDIR/tmp/spout" from test.t1;
insert into test.t1 values (concat(x, "2"), y+2);
end|
call into_outfile("ofile", 1)|
delete from t1|
drop procedure into_outfile|
drop procedure if exists into_dumpfile|
create procedure into_dumpfile(x char(16), y int)
begin
insert into test.t1 values (x, y);
select * into dumpfile "MYSQLTEST_VARDIR/tmp/spdump" from test.t1 limit 1;
insert into test.t1 values (concat(x, "2"), y+2);
end|
call into_dumpfile("dfile", 1)|
delete from t1|
drop procedure into_dumpfile|
drop procedure if exists create_select|
create procedure create_select(x char(16), y int)
begin
insert into test.t1 values (x, y);
create temporary table test.t3 select * from test.t1;
insert into test.t3 values (concat(x, "2"), y+2);
end|
call create_select("cs", 90)|
select * from t1, t3|
id data id data
cs 90 cs 90
cs 90 cs2 92
drop table t3|
delete from t1|
drop procedure create_select|
drop function if exists e|
create function e() returns double
return 2.7182818284590452354|
set @e = e()|
select e(), @e|
e() @e
2.718281828459045 2.718281828459045
drop function if exists inc|
create function inc(i int) returns int
return i+1|
select inc(1), inc(99), inc(-71)|
inc(1) inc(99) inc(-71)
2 100 -70
drop function if exists mul|
create function mul(x int, y int) returns int
return x*y|
select mul(1,1), mul(3,5), mul(4711, 666)|
mul(1,1) mul(3,5) mul(4711, 666)
1 15 3137526
drop function if exists append|
create function append(s1 char(8), s2 char(8)) returns char(16)
return concat(s1, s2)|
select append("foo", "bar")|
append("foo", "bar")
foobar
drop function if exists fac|
create function fac(n int unsigned) returns bigint unsigned
begin
declare f bigint unsigned default 1;
while n > 1 do
set f = f * n;
set n = n - 1;
end while;
return f;
end|
select fac(1), fac(2), fac(5), fac(10)|
fac(1) fac(2) fac(5) fac(10)
1 2 120 3628800
drop function if exists fun|
create function fun(d double, i int, u int unsigned) returns double
return mul(inc(i), fac(u)) / e()|
select fun(2.3, 3, 5)|
fun(2.3, 3, 5)
176.58213176229233
insert into t2 values (append("xxx", "yyy"), mul(4,3), e())|
insert into t2 values (append("a", "b"), mul(2,mul(3,4)), fun(1.7, 4, 6))|
select * from t2 where s = append("a", "b")|
s i d
ab 24 1324.3659882171924
select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2) order by i|
s i d
xxxyyy 12 2.718281828459045
ab 24 1324.3659882171924
select * from t2 where d = e()|
s i d
xxxyyy 12 2.718281828459045
select * from t2 order by i|
s i d
xxxyyy 12 2.718281828459045
ab 24 1324.3659882171924
delete from t2|
drop function e|
drop function inc|
drop function mul|
drop function append|
drop function fun|
drop procedure if exists hndlr1|
create procedure hndlr1(val int)
begin
declare x int default 0;
declare foo condition for 1136;
declare bar condition for sqlstate '42S98'; # Just for testing syntax
declare zip condition for sqlstate value '42S99'; # Just for testing syntax
declare continue handler for foo set x = 1;
insert into test.t1 values ("hndlr1", val, 2); # Too many values
if (x) then
insert into test.t1 values ("hndlr1", val); # This instead then
end if;
end|
call hndlr1(42)|
select * from t1|
id data
hndlr1 42
delete from t1|
drop procedure hndlr1|
drop procedure if exists hndlr2|
create procedure hndlr2(val int)
begin
declare x int default 0;
begin
declare exit handler for sqlstate '21S01' set x = 1;
insert into test.t1 values ("hndlr2", val, 2); # Too many values
end;
insert into test.t1 values ("hndlr2", x);
end|
call hndlr2(42)|
select * from t1|
id data
hndlr2 1
delete from t1|
drop procedure hndlr2|
drop procedure if exists hndlr3|
create procedure hndlr3(val int)
begin
declare x int default 0;
declare continue handler for sqlexception # Any error
begin
declare z int;
set z = 2 * val;
set x = 1;
end;
if val < 10 then
begin
declare y int;
set y = val + 10;
insert into test.t1 values ("hndlr3", y, 2); # Too many values
if x then
insert into test.t1 values ("hndlr3", y);
end if;
end;
end if;
end|
call hndlr3(3)|
select * from t1|
id data
hndlr3 13
delete from t1|
drop procedure hndlr3|
create table t3 ( id char(16), data int )|
drop procedure if exists hndlr4|
create procedure hndlr4()
begin
declare x int default 0;
declare val int; # No default
declare continue handler for sqlstate '02000' set x=1;
select data into val from test.t3 where id='z' limit 1; # No hits
insert into test.t3 values ('z', val);
end|
call hndlr4()|
select * from t3|
id data
z NULL
drop table t3|
drop procedure hndlr4|
drop procedure if exists cur1|
create procedure cur1()
begin
declare a char(16);
declare b int;
declare c double;
declare done int default 0;
declare c cursor for select * from test.t2;
declare continue handler for sqlstate '02000' set done = 1;
open c;
repeat
fetch c into a, b, c;
if not done then
insert into test.t1 values (a, b+c);
end if;
until done end repeat;
close c;
end|
insert into t2 values ("foo", 42, -1.9), ("bar", 3, 12.1), ("zap", 666, -3.14)|
call cur1()|
select * from t1|
id data
foo 40
bar 15
zap 663
drop procedure cur1|
create table t3 ( s char(16), i int )|
drop procedure if exists cur2|
create procedure cur2()
begin
declare done int default 0;
declare c1 cursor for select id,data from test.t1 order by id,data;
declare c2 cursor for select i from test.t2 order by i;
declare continue handler for sqlstate '02000' set done = 1;
open c1;
open c2;
repeat
begin
declare a char(16);
declare b,c int;
fetch from c1 into a, b;
fetch next from c2 into c;
if not done then
if b < c then
insert into test.t3 values (a, b);
else
insert into test.t3 values (a, c);
end if;
end if;
end;
until done end repeat;
close c1;
close c2;
end|
call cur2()|
select * from t3 order by i,s|
s i
bar 3
foo 40
zap 663
delete from t1|
delete from t2|
drop table t3|
drop procedure cur2|
drop procedure if exists chistics|
create procedure chistics()
language sql
modifies sql data
not deterministic
sql security definer
comment 'Characteristics procedure test'
insert into t1 values ("chistics", 1)|
show create procedure chistics|
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
chistics STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`()
MODIFIES SQL DATA
COMMENT 'Characteristics procedure test'
insert into t1 values ("chistics", 1) latin1 latin1_swedish_ci latin1_swedish_ci
call chistics()|
select * from t1|
id data
chistics 1
delete from t1|
alter procedure chistics sql security invoker|
show create procedure chistics|
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
chistics STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`()
MODIFIES SQL DATA
SQL SECURITY INVOKER
COMMENT 'Characteristics procedure test'
insert into t1 values ("chistics", 1) latin1 latin1_swedish_ci latin1_swedish_ci
drop procedure chistics|
drop function if exists chistics|
create function chistics() returns int
language sql
deterministic
sql security invoker
comment 'Characteristics procedure test'
return 42|
show create function chistics|
Function sql_mode Create Function character_set_client collation_connection Database Collation
chistics STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11)
DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'Characteristics procedure test'
return 42 latin1 latin1_swedish_ci latin1_swedish_ci
select chistics()|
chistics()
42
alter function chistics
no sql
comment 'Characteristics function test'|
show create function chistics|
Function sql_mode Create Function character_set_client collation_connection Database Collation
chistics STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11)
NO SQL
DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'Characteristics function test'
return 42 latin1 latin1_swedish_ci latin1_swedish_ci
drop function chistics|
insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)|
set @@sql_mode = 'ANSI'|
drop procedure if exists modes$
create procedure modes(out c1 int, out c2 int)
begin
declare done int default 0;
declare x int;
declare c cursor for select data from t1;
declare continue handler for sqlstate '02000' set done = 1;
select 1 || 2 into c1;
set c2 = 0;
open c;
repeat
fetch c into x;
if not done then
set c2 = c2 + 1;
end if;
until done end repeat;
close c;
end$
set @@sql_mode = ''|
set sql_select_limit = 1|
call modes(@c1, @c2)|
set sql_select_limit = default|
select @c1, @c2|
@c1 @c2
12 3
delete from t1|
drop procedure modes|
create database sp_db1|
drop database sp_db1|
create database sp_db2|
use sp_db2|
create table t3 ( s char(4), t int )|
insert into t3 values ("abcd", 42), ("dcba", 666)|
use test|
drop database sp_db2|
create database sp_db3|
use sp_db3|
drop procedure if exists dummy|
create procedure dummy(out x int)
set x = 42|
use test|
drop database sp_db3|
select type,db,name from mysql.proc where db = 'sp_db3'|
type db name
drop procedure if exists rc|
create procedure rc()
begin
delete from t1;
insert into t1 values ("a", 1), ("b", 2), ("c", 3);
end|
call rc()|
select row_count()|
row_count()
3
update t1 set data=42 where id = "b";
select row_count()|
row_count()
1
delete from t1|
select row_count()|
row_count()
3
delete from t1|
select row_count()|
row_count()
0
select * from t1|
id data
select row_count()|
row_count()
-1
drop procedure rc|
drop function if exists f0|
drop function if exists f1|
drop function if exists f2|
drop function if exists f3|
drop function if exists f4|
drop function if exists f5|
drop function if exists f6|
drop function if exists f7|
drop function if exists f8|
drop function if exists f9|
drop function if exists f10|
drop function if exists f11|
drop function if exists f12_1|
drop function if exists f12_2|
drop view if exists v0|
drop view if exists v1|
drop view if exists v2|
delete from t1|
delete from t2|
insert into t1 values ("a", 1), ("b", 2) |
insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |
create function f1() returns int
return (select sum(data) from t1)|
select f1()|
f1()
3
select id, f1() from t1 order by id|
id f1()
a 3
b 3
create function f2() returns int
return (select data from t1 where data <= (select sum(data) from t1) order by data limit 1)|
select f2()|
f2()
1
select id, f2() from t1 order by id|
id f2()
a 1
b 1
create function f3() returns int
begin
declare n int;
declare m int;
set n:= (select min(data) from t1);
set m:= (select max(data) from t1);
return n < m;
end|
select f3()|
f3()
1
select id, f3() from t1 order by id|
id f3()
a 1
b 1
select f1(), f3()|
f1() f3()
3 1
select id, f1(), f3() from t1 order by id|
id f1() f3()
a 3 1
b 3 1
create function f4() returns double
return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")|
select f4()|
f4()
2
select s, f4() from t2 order by s|
s f4()
a 2
b 2
c 2
create function f5(i int) returns int
begin
if i <= 0 then
return 0;
elseif i = 1 then
return (select count(*) from t1 where data = i);
else
return (select count(*) + f5( i - 1) from t1 where data = i);
end if;
end|
select f5(1)|
f5(1)
1
select f5(2)|
ERROR HY000: Recursive stored functions and triggers are not allowed