mysql学习笔记(进阶一)

----declare---
begin
declare inta int;
set inta = 88;
select inta;
end
----in-----
create procedure p_hello(in p_int int)
begin
set p_int = p_int+1;
select p_int;
end
$$;
delimiter ;

set @p_int = 3;
call p_hello(@p_int)
//@p_int 的值在存储外并不会改变;
----out---
create procedure p_hello(out p_int int)
begin
set p_int = p_int+1;
select p_int;
end
$$;
delimiter ;

set @p_int = 3;
call p_hello(@p_int)
//进入时所赋的值并不生效,不认可,为null
//@p_int 的值在存储外会改变;

----inout---
create procedure p_hello(inout p_int int)
begin
set p_int = p_int+1;
select p_int;
end
$$;
delimiter ;

set @p_int = 3;
call p_hello(@p_int)
//进入的值被认可
//@p_int 的值在存储外不会会改变;

begin 
-----
if age >=18 then select '成年人';
else select '未成年人';
end if
-----
if age >=18 then select '成年人';
elseif age>=60 then select '未成年人';
else select '未成年人';
end if
-----
end 
$$;

begin
declare aaa int;
case v_empno
when 1 then
set aaa ='我是1';
when 2 then
set aaa = '我是2';
when 3 then
set aaa = '我是3';
else
set aaa = 'null';
end case;
end;
$$;

begin
declare i int default 1;
declare result int default 0;
while i<=100 do
set result = result+i;
set i=i+1;
end while;

repeat
内容
until 条件 //退出循环的条件
end repeat;
-------------
begin
declare imin int default 1;
declare imax int default 1;
seelct max(a) into imax from user;
seelct min(a) into imin from user;
repeat
if imin % 2 = 0 then
update.......
endif;
set imin = imin+1;
until imin>imax (没有分号)
endrepeat;
end;

loop名字:loop
内容
if 条件 then
leave loop名字
endif;
end loop;

myloop:loop
if imax %2=1 then
ipdate........
end if;
set imax = imax+1
if imin > imax then
leave loop myloop;//离开循环
end if;
end loop;

存储过程中,前一句sql出错,不影响下一条