use test; drop table if exists t1; drop table if exists t2; create table t1 ( id char(16) not null, data int not null ); create table t2 ( s char(16) not null, i int not null, d double not null ); 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; create procedure u() use sptmp; create database sptmp; use test; call u(); select database(); database() test drop database sptmp; drop procedure u; 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; 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; 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; 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; create procedure zip(x char(16), y int) begin declare z int; call zap(y, z); call bar(x, z); end; 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 zap; drop procedure bar; create procedure iotest(x1 char(16), x2 char(16), y int) begin call inc2(x2, y); insert into test.t1 values (x1, y); end; create procedure inc2(x char(16), y int) begin call inc(y); insert into test.t1 values (x, y); end; create procedure inc(inout io int) set io = io + 1; call iotest("io1", "io2", 1); select * from t1; id data io2 2 io1 1 delete from t1; drop procedure iotest; drop procedure inc2; drop procedure inc; create procedure cbv1() begin declare y int; set y = 3; call cbv2(y+1, y); insert into test.t1 values ("cbv1", y); end; 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; id data cbv2 4 cbv1 4711 delete from t1; drop procedure cbv1; drop procedure cbv2; 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; id data a0 2 a0 1 a0 0 delete from t1; drop procedure a0; 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; id data a 2 a 1 a 0 delete from t1; drop procedure a; 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; id data bbb 3 bbb 2 bbb 1 delete from t1; drop procedure b; 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; 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; id data c 3 c 2 c 1 delete from t1; drop procedure c; 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 hmm; call d(3); select * from t1; id data d 3 delete from t1; drop procedure d; 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; id data e 3 e 2 e 1 delete from t1; drop procedure e; 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; id data f 0 f 1 f 2 delete from t1; drop procedure f; 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; id data g 0 g 1 g 2 delete from t1; drop procedure g; 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; id data h0 0 h1 1 h? 17 delete from t1; drop procedure h; 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; id data into 100 into2 102 delete from t1; drop procedure into_test; 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; id data @z into 100 100 into2 102 100 delete from t1; drop procedure into_test2; create procedure into_outfile(x char(16), y int) begin insert into test.t1 values (x, y); select * into outfile "/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; create procedure into_dumpfile(x char(16), y int) begin insert into test.t1 values (x, y); select * into dumpfile "/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; create procedure create_select(x char(16), y int) begin insert into test.t1 values (x, y); create table test.t2 select * from test.t1; insert into test.t2 values (concat(x, "2"), y+2); end; drop procedure create_select; create function e() returns double return 2.7182818284590452354; set @e = e(); select e(), @e; e() @e 2.718281828459 2.718281828459 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 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 create function append(s1 char(8), s2 char(8)) returns char(16) return concat(s1, s2); select append("foo", "bar"); append("foo", "bar") foobar create function fac(n int unsigned) returns bigint unsigned begin declare f bigint unsigned; set f = 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 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.58213176229 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.36598821719 select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2); s i d xxxyyy 12 2.71828182845905 ab 24 1324.36598821719 select * from t2 where d = e(); s i d xxxyyy 12 2.71828182845905 select * from t2; s i d xxxyyy 12 2.71828182845905 ab 24 1324.36598821719 delete from t2; drop function e; drop function inc; drop function mul; drop function append; drop function fun; drop table if exists fac; create table fac (n int unsigned not null primary key, f bigint unsigned); create procedure ifac(n int unsigned) begin declare i int unsigned; set i = 1; if n > 20 then set n = 20; # bigint overflow otherwise end if; while i <= n do begin insert into test.fac values (i, fac(i)); set i = i + 1; end; end while; end; call ifac(20); select * from fac; n f 1 1 2 2 3 6 4 24 5 120 6 720 7 5040 8 40320 9 362880 10 3628800 11 39916800 12 479001600 13 6227020800 14 87178291200 15 1307674368000 16 20922789888000 17 355687428096000 18 6402373705728000 19 121645100408832000 20 2432902008176640000 drop table fac; drop procedure ifac; drop function fac; drop table if exists primes; create table primes ( i int unsigned not null primary key, p bigint unsigned not null ); insert into primes values ( 0, 3), ( 1, 5), ( 2, 7), ( 3, 11), ( 4, 13), ( 5, 17), ( 6, 19), ( 7, 23), ( 8, 29), ( 9, 31), (10, 37), (11, 41), (12, 43), (13, 47), (14, 53), (15, 59), (16, 61), (17, 67), (18, 71), (19, 73), (20, 79), (21, 83), (22, 89), (23, 97), (24, 101), (25, 103), (26, 107), (27, 109), (28, 113), (29, 127), (30, 131), (31, 137), (32, 139), (33, 149), (34, 151), (35, 157), (36, 163), (37, 167), (38, 173), (39, 179), (40, 181), (41, 191), (42, 193), (43, 197), (44, 199); create procedure opp(n bigint unsigned, out pp bool) begin declare r double; declare b, s bigint unsigned; set b = 0, s = 0; set r = sqrt(n); again: loop if s = 45 then set b = b+200, s = 0; else begin declare p bigint unsigned; select t.p into p from test.primes t where t.i = s; if b+p > r then set pp = 1; leave again; end if; if mod(n, b+p) = 0 then set pp = 0; leave again; end if; set s = s+1; end; end if; end loop again; end; create procedure ip(m int unsigned) begin declare p bigint unsigned; declare i int unsigned; set i=45, p=201; while i < m do begin declare pp bool; set pp = 0; call opp(p, pp); if pp then insert into test.primes values (i, p); set i = i+1; end if; set p = p+2; end; end while; end; call ip(200); select * from primes where i=45 or i=100 or i=199; i p 45 211 100 557 199 1229 drop table primes; drop procedure opp; drop procedure ip; drop table t1; drop table t2;