Mysql-stored procedure learning notes

1. ID registration

Stored Procedure Code:

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; $

Usage:
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 ('uancer'). Value: = '20140901 ';
Mystrdprc1.execproc;
Errorcode: = mystrdprc1.params. parambyname ('regreturnvalue'). value;
Case errorcode
10: showmessage ('Id already exists. ');
20: showmessage ('registration successful. ');
End;
Mystrdprc1.close;
Except
End;
End;

2. Password Modification

Stored Procedure Code:

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; $

Usage:

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: = '20140901 ';
Mystrdprc1.params. parambyname ('newpwd'). Value: = '1233d3 ';
Mystrdprc1.execproc;
Errorcode: = mystrdprc1.params. parambyname ('editpwdreturnvalue'). value;
Case errorcode
10: showmessage ('Id does not exist. ');
20: showmessage ('old password is incorrect. ');
30: showmessage ('password modified successfully. ');
End;
Mystrdprc1.close;
Except
End;
End;