# # Basic stored PROCEDURE tests # # use test; --disable_warnings drop table if exists t1; drop table if exists t2; --enable_warnings create table t1 ( id char(16) not null, data int not null ); create table t2 ( s char(16), i int, d double ); # Single statement, no params. create procedure foo42() insert into test.t1 values ("foo", 42); call foo42(); select * from t1; delete from t1; drop procedure foo42; # Single statement, two IN params. create procedure bar(x char(16), y int) insert into test.t1 values (x, y); call bar("bar", 666); select * from t1; delete from t1; # Don't drop procedure yet... # Now for multiple statements... delimiter |; # Empty statement create procedure empty() begin end| call empty()| drop procedure empty| # Scope test. This is legal (warnings might be possible in the future, # but for the time being, we just accept it). create procedure scope(a int, b float) begin declare b int; declare c float; begin declare c int; end; end| drop procedure scope| # Two statements. 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| delete from t1| drop procedure two| # Simple test of local variables and SET. 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| delete from t1| drop procedure locset| # In some contexts local variables are not recognized # (and in some, you have to qualify the identifier). 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| delete from t1| drop procedure setcontext| # Set things to null --disable_warnings drop table if exists t3| --enable_warnings create table t3 ( d date, i int, f double, s varchar(32) )| 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| drop table t3| drop procedure nullset| # The peculiar (non-standard) mixture of variables types in SET. 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'| select id,data,@z from t1| delete from t1| drop procedure mixset| # Multiple CALL statements, one with OUT parameter. create procedure zip(x char(16), y int) begin declare z int; call zap(y, z); call bar(x, z); end| # SET local variables and OUT parameter. 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| delete from t1| drop procedure zip| drop procedure bar| # Top-level OUT parameter call zap(7, @zap)| select @zap| drop procedure zap| # "Deep" calls... create procedure c1(x int) call c2("c", x)| create procedure c2(s char(16), x int) call c3(x, s)| create procedure c3(x int, s char(16)) call c4("level", x, s)| 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| delete from t1| drop procedure c1| drop procedure c2| drop procedure c3| drop procedure c4| # INOUT test 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| delete from t1| drop procedure iotest| drop procedure inc2| # Propagating top-level @-vars create procedure incr(inout x int) call inc(x)| # Before select @zap| call incr(@zap)| # After select @zap| drop procedure inc| drop procedure incr| # Call-by-value test # The expected result is: # ("cbv2", 4) # ("cbv1", 4711) create procedure cbv1() begin declare y int default 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| delete from t1| drop procedure cbv1| drop procedure cbv2| # Subselect arguments insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)| create procedure sub1(id char(16), x int) insert into test.t1 values (id, x)| # QQ This doesn't work yet #create procedure sub2(id char(16)) #begin # declare x int; # set x = (select sum(t.x) from test.t2 t); # insert into test.t1 values (id, x); #end| create function sub3(i int) returns int 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))| call sub1("sub1d", (select 1 from (select 1) a))| #call sub2("sub2"); select * from t1| select sub3((select max(i) from t2))| drop procedure sub1| #drop procedure sub2| drop function sub3| delete from t2| # Basic tests of the flow control constructs # Just test on 'x'... 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| delete from t1| drop procedure a0| # The same, but with a more traditional test. 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| delete from t1| drop procedure a| # REPEAT 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| delete from t1| drop procedure b| # Check that repeat isn't parsed the wrong way 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| # We don't actually want to call it. drop procedure b2| # Labelled WHILE with ITERATE (pointless really) 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| delete from t1| drop procedure c| # Labelled WHILE with LEAVE 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| delete from t1| drop procedure d| # LOOP, with simple IF statement 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| delete from t1| drop procedure e| # A full IF statement 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| delete from t1| drop procedure f| # This form of CASE is really just syntactic sugar for IF-ELSEIF-... 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| delete from t1| drop procedure g| # The "simple CASE" 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| delete from t1| drop procedure h| # It's actually possible to LEAVE a BEGIN-END block 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| delete from t1| drop procedure i| # SELECT with one of more result set sent back to the clinet insert into t1 values ("foo", 3), ("bar", 19)| insert into t2 values ("x", 9, 4.1), ("y", -1, 19.2), ("z", 3, 2.2)| create procedure sel1() begin select * from t1; end| call sel1()| drop procedure sel1| create procedure sel2() begin select * from t1; select * from t2; end| call sel2()| drop procedure sel2| delete from t1| delete from t2| # SELECT INTO local variables 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| delete from t1| drop procedure into_test| # SELECT INTO with a mix of local and global variables 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| delete from t1| drop procedure into_test2| # SELECT * INTO ... (bug test) 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)| # Two call needed for bug test call into_test3()| call into_test3()| select * from t2| delete from t1| delete from t2| drop procedure into_test3| # SELECT INTO with no data is a warning ("no data", which we will # not see normally). When not caught, execution proceeds. 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| --disable_warnings drop table if exists t3| --enable_warnings create table t3 ( s char(16), d int)| call into_test4()| select * from t3| insert into t1 values ("i4", 77)| call into_test4()| select * from t3| delete from t1| drop table t3| drop procedure into_test4| # These two (and the two procedures above) caused an assert() to fail in # sql_base.cc:lock_tables() at some point. 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| system rm -f /tmp/spout| call into_outfile("ofile", 1)| system rm -f /tmp/spout| 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| system rm -f /tmp/spdump| call into_dumpfile("dfile", 1)| system rm -f /tmp/spdump| 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.t3 select * from test.t1; insert into test.t3 values (concat(x, "2"), y+2); end| --disable_warnings drop table if exists t3| --enable_warnings call create_select("cs", 90)| select * from t1, t3| --disable_warnings drop table if exists t3| --enable_warnings delete from t1| drop procedure create_select| # A minimal, constant FUNCTION. create function e() returns double return 2.7182818284590452354| set @e = e()| select e(), @e| # A minimal function with one argument create function inc(i int) returns int return i+1| select inc(1), inc(99), inc(-71)| # A minimal function with two arguments create function mul(x int, y int) returns int return x*y| select mul(1,1), mul(3,5), mul(4711, 666)| # A minimal string function create function append(s1 char(8), s2 char(8)) returns char(16) return concat(s1, s2)| select append("foo", "bar")| # A function with flow control 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)| # Nested calls 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)| # Various function calls in differen statements 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))| # These don't work yet. select * from t2 where s = append("a", "b")| select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2)| select * from t2 where d = e()| select * from t2| delete from t2| drop function e| drop function inc| drop function mul| drop function append| drop function fun| # # CONDITIONs and HANDLERs # create procedure hndlr1(val int) begin declare x int default 0; declare foo condition for 1146; 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.t666 values ("hndlr1", val); # Non-existing table if (x) then insert into test.t1 values ("hndlr1", val); # This instead then end if; end| call hndlr1(42)| select * from t1| delete from t1| drop procedure hndlr1| create procedure hndlr2(val int) begin declare x int default 0; begin declare exit handler for sqlstate '42S02' set x = 1; insert into test.t666 values ("hndlr2", val); # Non-existing table end; insert into test.t1 values ("hndlr2", x); end| call hndlr2(42)| select * from t1| delete from t1| drop procedure hndlr2| 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.t666 values ("hndlr3", y); # Non-existing table if x then insert into test.t1 values ("hndlr3", y); end if; end; end if; end| call hndlr3(3)| select * from t1| delete from t1| drop procedure hndlr3| # Variables might be uninitialized when using handlers # (Otherwise the compiler can detect if a variable is not set, but # not in this case.) --disable_warnings drop table if exists t3| --enable_warnings create table t3 ( id char(16), data int )| 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| drop table t3| drop procedure hndlr4| # # Cursors # 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| drop procedure cur1| --disable_warnings drop table if exists t3| --enable_warnings create table t3 ( s char(16), i int )| create procedure cur2() begin declare done int default 0; declare c1 cursor for select id,data from test.t1; declare c2 cursor for select i from test.t2; declare continue handler for sqlstate '02000' set done = 1; open c1; open c2; repeat begin declare a char(16); declare b,c int; fetch c1 into a, b; fetch 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| delete from t1| delete from t2| drop table t3| drop procedure cur2| # The few characteristics we parse create procedure chistics() language sql not deterministic sql security definer comment 'Characteristics procedure test' insert into t1 values ("chistics", 1)| # Call it, just to make sure. call chistics()| select * from t1| delete from t1| alter procedure chistics sql security invoker name chistics2| show create procedure chistics2| drop procedure chistics2| create function chistics() returns int language sql deterministic sql security invoker comment 'Characteristics procedure test' return 42| # Call it, just to make sure. select chistics()| alter function chistics name chistics2 comment 'Characteristics function test'| show create function chistics2| drop function chistics2| # Check mode settings insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)| set @@sql_mode = 'ANSI'| delimiter $| 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$ delimiter |$ set @@sql_mode = ''| set sql_select_limit = 1| call modes(@c1, @c2)| set sql_select_limit = default| select @c1, @c2| delete from t1| drop procedure modes| # Check that dropping a database without routines works. # (Dropping with routines is tested in sp-security.test) # First an empty db. create database sp_db1| drop database sp_db1| # Again, with a table. create database sp_db2| use sp_db2| # Just put something in here... create table t3 ( s char(4), t int )| insert into t3 values ("abcd", 42), ("dcba", 666)| use test| drop database sp_db2| # And yet again, with just a procedure. create database sp_db3| use sp_db3| create procedure dummy(out x int) set x = 42| use test| drop database sp_db3| # Check that it's gone select type,db,name from mysql.proc where db = 'sp_db3'| # # Test cases for old bugs # # # BUG#822 # create procedure bug822(a_id char(16), a_data int) begin declare n int; select count(*) into n from t1 where id = a_id and data = a_data; if n = 0 then insert into t1 (id, data) values (a_id, a_data); end if; end| call bug822('foo', 42)| call bug822('foo', 42)| call bug822('bar', 666)| select * from t1| delete from t1| drop procedure bug822| # # BUG#1495 # create procedure bug1495() begin declare x int; select data into x from t1 order by id limit 1; if x > 10 then insert into t1 values ("less", x-10); else insert into t1 values ("more", x+10); end if; end| insert into t1 values ('foo', 12)| call bug1495()| delete from t1 where id='foo'| insert into t1 values ('bar', 7)| call bug1495()| delete from t1 where id='bar'| select * from t1| delete from t1| drop procedure bug1495| # # BUG#1547 # create procedure bug1547(s char(16)) begin declare x int; select data into x from t1 where s = id limit 1; if x > 10 then insert into t1 values ("less", x-10); else insert into t1 values ("more", x+10); end if; end| insert into t1 values ("foo", 12), ("bar", 7)| call bug1547("foo")| call bug1547("bar")| select * from t1| delete from t1| drop procedure bug1547| # # BUG#1656 # --disable_warnings drop table if exists t70| --enable_warnings create table t70 (s1 int,s2 int)| insert into t70 values (1,2)| create procedure bug1656(out p1 int, out p2 int) select * into p1, p1 from t70| call bug1656(@1, @2)| select @1, @2| drop table t70| drop procedure bug1656| # # BUG#1862 # --disable_warnings drop table if exists t3| --enable_warnings create table t3(a int)| create procedure bug1862() begin insert into t3 values(2); flush tables; end| call bug1862()| # the second call caused a segmentation call bug1862()| select * from t3| drop table t3| drop procedure bug1862| # # BUG#1874 # create procedure bug1874() begin declare x int; declare y double; select max(data) into x from t1; insert into t2 values ("max", x, 0); select min(data) into x from t1; insert into t2 values ("min", x, 0); select sum(data) into x from t1; insert into t2 values ("sum", x, 0); select avg(data) into y from t1; insert into t2 values ("avg", 0, y); end| insert into t1 (data) values (3), (1), (5), (9), (4)| call bug1874()| select * from t2| delete from t1| delete from t2| drop procedure bug1874| # # BUG#2260 # create procedure bug2260() begin declare v1 int; declare c1 cursor for select data from t1; declare continue handler for not found set @x2 = 1; open c1; fetch c1 into v1; set @x2 = 2; close c1; end| call bug2260()| select @x2| drop procedure bug2260| # # BUG#2267 # create procedure bug2267_1() begin show procedure status; end| create procedure bug2267_2() begin show function status; end| create procedure bug2267_3() begin show create procedure bug2267_1; end| create procedure bug2267_4() begin show create function fac; end| --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' call bug2267_1()| --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' call bug2267_2()| call bug2267_3()| call bug2267_4()| drop procedure bug2267_1| drop procedure bug2267_2| drop procedure bug2267_3| drop procedure bug2267_4| # # BUG#2227 # create procedure bug2227(x int) begin declare y float default 2.6; declare z char(16) default "zzz"; select 1.3, x, y, 42, z; end| call bug2227(9)| drop procedure bug2227| # # BUG#2614 # create procedure bug2614() begin drop table if exists t3; create table t3 (id int default '0' not null); insert into t3 select 12; insert into t3 select * from t3; end| --disable_warnings call bug2614()| --enable_warnings call bug2614()| drop table t3| drop procedure bug2614| # # BUG#2674 # create function bug2674 () returns int return @@sort_buffer_size| set @osbs = @@sort_buffer_size| set @@sort_buffer_size = 262000| select bug2674()| drop function bug2674| set @@sort_buffer_size = @osbs| # # BUG#3259 # create procedure bug3259_1 () begin end| create procedure BUG3259_2 () begin end| create procedure Bug3259_3 () begin end| call BUG3259_1()| call BUG3259_1()| call bug3259_2()| call Bug3259_2()| call bug3259_3()| call bUG3259_3()| drop procedure bUg3259_1| drop procedure BuG3259_2| drop procedure BUG3259_3| # # Some "real" examples # # fac --disable_warnings drop table if exists fac| --enable_warnings create table fac (n int unsigned not null primary key, f bigint unsigned)| create procedure ifac(n int unsigned) begin declare i int unsigned default 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| drop table fac| --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' show function status like '%f%'| drop procedure ifac| drop function fac| --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' show function status like '%f%'| # primes --disable_warnings drop table if exists primes| --enable_warnings 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 default 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; 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 default 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| show create procedure opp| --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' show procedure status like '%p%'| # This isn't the fastest way in the world to compute prime numbers, so # don't be too ambitious. ;-) call ip(200)| # We don't want to select the entire table here, just pick a few # examples. select * from primes where i=45 or i=100 or i=199| drop table primes| drop procedure opp| drop procedure ip| --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' show procedure status like '%p%'| # Fibonacci, for recursion test. (Yet Another Numerical series :) --disable_warnings drop table if exists fib| --enable_warnings create table fib ( f bigint unsigned not null )| insert into fib values (1), (1)| # We deliberately do it the awkward way, fetching the last two # values from the table, in order to exercise various statements # and table accesses at each turn. create procedure fib(n int unsigned) begin if n > 0 then begin declare x, y bigint unsigned; declare c cursor for select f from fib order by f desc limit 2; open c; fetch c into y; fetch c into x; close c; insert into fib values (x+y); call fib(n-1); end; end if; end| call fib(20)| select * from fib order by f asc| drop table fib| drop procedure fib| # # Comment & suid # create procedure bar(x char(16), y int) comment "111111111111" sql security invoker insert into test.t1 values (x, y)| --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' show procedure status like 'bar'| alter procedure bar name bar2 comment "2222222222" sql security definer| alter procedure bar2 name bar comment "3333333333"| alter procedure bar| show create procedure bar| --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' show procedure status like 'bar'| drop procedure bar| delimiter ;| drop table t1; drop table t2;