MYSQL - 存储过程学习笔记
1、ID注册
存储过程代码：
DELIMITER $$;
DROP PROCEDURE IF EXISTS `zebra`.`AddUser`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddUser`(in Uid varchar(50), in Upwd varchar(50), in Uquest varchar(50), in Uanswer varchar(50), out RegReturnValue int)
BEGIN
DECLARE ChkID varchar(50);
select LOGINID from login where LOGINID = Uid into ChkID;
if (ChkID <> '') then
set RegReturnValue = 10;
else
insert into login (LOGINID, `PASSWORD`, QUESTION1, ANSWER1)VALUES(Uid, Upwd, Uquest, Uanswer);
set RegReturnValue = 20;
end if;
END$$
DELIMITER ;$$
使用：
procedure TForm1.btn1Click(Sender: TObject);
var
ErrorCode:Integer;
begin
try
mystrdprc1.StoredProcName := 'AddUser';
mystrdprc1.Params.ParamByName('Uid').Value := 'eboy';
mystrdprc1.Params.ParamByName('Upwd').Value := 'eewwee';
mystrdprc1.Params.ParamByName('Uquest').Value := 'MeiPaiHao';
mystrdprc1.Params.ParamByName('Uanswer').Value := '140140';
mystrdprc1.ExecProc;
ErrorCode := mystrdprc1.Params.ParamByName('RegReturnValue').Value;
case ErrorCode of
10: ShowMessage('ID已存在。');
20: ShowMessage('注册成功。');
end;
mystrdprc1.Close;
except
end;
end;
2、密码修改
存储过程代码：
DELIMITER $$;
DROP PROCEDURE IF EXISTS `zebra`.`EditPwd`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `EditPwd`(in Uid varchar(50), in OldPwd varchar(50), in NewPwd varchar(50), out EditPwdReturnValue int)
BEGIN
DECLARE ChkID varchar(50);
DECLARE ChkPwd varchar(50);
select LOGINID from login where LOGINID = Uid into ChkID;
if (ChkID = Uid) then
select `PASSWORD` from login where LOGINID = Uid into ChkPwd;
if (ChkPwd = OldPwd) then
update login set `PASSWORD` = NewPwd where LOGINID = Uid;
set EditPwdReturnValue = 30; #?????????
else
set EditPwdReturnValue = 20; #??????????
end if;
else
set EditPwdReturnValue = 10; #ID??????
end if;
END$$
DELIMITER ;$$
使用：
procedure TForm1.btn2Click(Sender: TObject);
var
ErrorCode:Integer;
ad:string;
begin
try
mystrdprc1.StoredProcName := 'EditPwd';
mystrdprc1.Params.ParamByName('Uid').Value := 'eboy';
mystrdprc1.Params.ParamByName('OldPwd').Value := '12333';
mystrdprc1.Params.ParamByName('NewPwd').Value := '1233d3';
mystrdprc1.ExecProc;
ErrorCode := mystrdprc1.Params.ParamByName('EditPwdReturnValue').Value;
case ErrorCode of
10: ShowMessage('ID不存在。');
20: ShowMessage('旧密码错误。');
30: ShowMessage('修改密码成功。');
end;
mystrdprc1.Close;
except
end;
end;
1、Codeforces Round#402(Div.1)掉分记+题解
2、萌新看Android
3、EF DbModelBuilder
4、LeetCode--058--最后一个单词的长度
5、VIM 解决中文乱码