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;