--Test Phone number call P_base_checklogin ('15584463676');--test Logon name call P_base_checklogin ('sch000001')--Test ID number call P_base_checklogin ('080428402304032042')--Test Student mobile phone number call P_base_checklogin ('13019212198') Drop PROCEDURE IF EXISTS p_base_checklogin;create PROCEDURE p_base_checklogin (v_loginname VARCHAR (255)) Label:begin--mobile phone number matching SELECT v_loginname REGEXP"^[1][35678][0-9]{9}$"into @checkResult; if@checkResult =1 ThenSelectP.person_id,p.identity_id,p.person_name into @person_id, @identity_id, @person_name fromT_base_person PwhereP.tel=v_loginname limit1; if@person_id isNotNULL ThenSelectL.login_name,l.login_password into @login_name, @login_password fromT_sys_loginperson Lwhere[Email protected]_id and l.identity_id=@identity_id; Select@login_name asUSER_NAME, @person_id asPERSON_ID, @identity_id asIDENTITY_ID, @person_name asReal_name, @login_password asPASSWORD; LEAVE label; Endif; --Student's mobile phone number matchSelectP.STUDENT_ID,6 asidentity_id into @person_id, @identity_id fromT_base_student asPwhereP.stu_tel=v_loginname limit1; if@person_id isNotNULL ThenSelectL.login_name,l.login_password into @login_name, @login_password fromT_sys_loginperson Lwhere[Email protected]_id and l.identity_id=@identity_id; Select@login_name asUSER_NAME, @person_id asPERSON_ID, @identity_id asIDENTITY_ID, @person_name asReal_name, @login_password asPASSWORD; LEAVE label; Endif; Endif; --Identification number MatchingSelectF_base_check_id_number (v_loginname) into @checkResult; if@checkResult =1 ThenSelectPerson_id,identity_id,person_name into @person_id, @identity_id, @person_name fromT_base_person PwhereP.identity_num=v_loginname limit1; if@person_id isNotNULL ThenSelectL.login_name,l.login_password into @login_name, @login_password fromT_sys_loginperson Lwhere[Email protected]_id and l.identity_id=@identity_id; Select@login_name asUSER_NAME, @person_id asPERSON_ID, @identity_id asIDENTITY_ID, @person_name asReal_name, @login_password asPASSWORD; LEAVE label; Endif; Endif; --Normal Login QuerySelectL.login_name,person_id,identity_id,l.person_name,l.login_password into @login_name, @person_id, @identity_id, @ Person_name, @login_password fromT_sys_loginperson LwhereL.login_name=v_loginname limit1; if@person_id isNotNULL ThenSelect@login_name asUSER_NAME, @person_id asPERSON_ID, @identity_id asIDENTITY_ID, @person_name asReal_name, @login_password asPASSWORD; LEAVE label; Endif; END;
Drop functionifEXISTS F_base_check_id_number; CREATE FUNCTION ' f_base_check_id_number ' (' IDNumber ' CHAR ( -)) RETURNSenum('1','0') LANGUAGE SQL not deterministic NO SQL SQL SECURITY definer COMMENT"'begindeclare status ENUM ('1','0')default '0';D eclare Verify CHAR (1);D eclare Sigma Int;declare remainder int;if length (idnumber)= - ThenSetSigma = cast (substring (idnumber,1,1) asUNSIGNED) *7+cast (substring (idnumber,2,1) asUNSIGNED) *9+cast (substring (idnumber,3,1) asUNSIGNED) *Ten+cast (substring (idnumber,4,1) asUNSIGNED) *5+cast (substring (idnumber,5,1) asUNSIGNED) *8+cast (substring (idnumber,6,1) asUNSIGNED) *4+cast (substring (idnumber,7,1) asUNSIGNED) *2+cast (substring (idnumber,8,1) asUNSIGNED) *1+cast (substring (idnumber,9,1) asUNSIGNED) *6+cast (substring (idnumber,Ten,1) asUNSIGNED) *3+cast (substring (idnumber, One,1) asUNSIGNED) *7+cast (substring (idnumber, A,1) asUNSIGNED) *9+cast (substring (idnumber, -,1) asUNSIGNED) *Ten+cast (substring (idnumber, -,1) asUNSIGNED) *5+cast (substring (idnumber, the,1) asUNSIGNED) *8+cast (substring (idnumber, -,1) asUNSIGNED) *4+cast (substring (idnumber, -,1) asUNSIGNED) *2; Setremainder = MOD (Sigma, One); SetVerify = ( Caseremainder when0Then'1'When1Then'0'When2Then'X'When3Then'9' when4Then'8'When5Then'7'When6Then'6'When7Then'5' when8Then'4'When9Then'3'WhenTenThen'2' Else '/'end); END if;if Right (IDNumber,1) =Verify ThenSetStatus ='1'; END IF; RETURN status; END
Using stored procedures in MySQL to return data information for querying multiple tables