mysql学习02 -密码登录

1）一个合格的mysql存储过程既要满足既定的要求又要实现一部分的用户自主性

CREATE DEFINER=`root`@`localhost` PROCEDURE `login`(in num0 varchar(20),in pw0 VARCHAR(20))
BEGIN
declare count0 int;
select count(*) into count0 from student where studentnum=num0 and studentPW=pw0;
if count0=1 then
set count0=checkpw(pw0);
else
set count0=0;
end if;
select case count0 when 0 then '用户名或密码不正确'
when 1 then '登陆成功'
when 2 then '密码过于简单'
end
AS count0;

END

2）创建相应的存储过程完成对用户登录功能的实现

CREATE DEFINER=`root`@`localhost` FUNCTION `checkpw`(pw0 varchar(20)) RETURNS int
DETERMINISTIC
BEGIN
declare flag int;
declare ls_i int;
declare ls_str varchar(20) DEFAULT null;
set flag=1;
if(length(pw0)<6 or LOCATE(pw0,'01234567890123456789876543210987654321')>0) then
set flag=2;
else
set ls_i=1;
while(ls_i<length(pw0)) do
if(Locate(MID(pw0,ls_i,1),ls_str)<0) then
set ls_str=concat(ls_str,MID(pw0,ls_i,1));
end if;
set ls_i=ls_i+1;
end while;
if(length(ls_str)<2) then
set flag=2;
end if;
end if;
RETURN flag;
END