Mysql中使⽤存储过程返回查询多个表的数据信息
--测试⼿机号
call P_Base_CheckLogin('155********');
--测试登录名
call P_Base_CheckLogin('sch000001')
--测试⾝份证号
call P_Base_CheckLogin('080428402304032042')
--测试学⽣⼿机号
call P_Base_CheckLogin('130********')
drop PROCEDURE IF EXISTS P_Base_CheckLogin;
create procedure P_Base_CheckLogin(v_loginName VARCHAR(255))
label:
BEGIN
--⼿机号匹配
SELECT v_loginName REGEXP "^[1][35678][0-9]{9}$" into @checkResult;
if @checkResult=1 then
select p.person_id,p.identity_id,p.person_name into @person_id,@identity_id,@person_name from t_base_person p l=v_loginName limit if @person_id is not null THEN
select l.login_name,l.login_password into @login_name,@login_password from t_sys_loginperson l where l.person_id=@person_id and l.IDENTITY_ID select @login_name as USER_NAME,@person_id as PERSON_ID,@identity_id as IDENTITY_ID ,@person_name as REAL_NAME,@login_password LEAVE label;
end if;
--学⽣的⼿机号匹配
select p.student_id,6as identity_id into @person_id,@identity_id from t_base_student as p where p.STU_TEL=v_loginName limit 1;
if @person_id is not null THEN
select l.login_name,l.login_password into @login_name,@login_password from t_sys_loginperson l where l.person_id=@person_id and l.IDENTITY_ID select @login_name as USER_NAME,@person_id as PERSON_ID,@identity_id as IDENTITY_ID ,@person_name as REAL_NAME,@login_password LEAVE label;
end if;
end if;
--⾝份证号匹配
select f_base_check_id_number(v_loginName) into @checkResult;
if @checkResult=1 then
select person_id,identity_id,person_name into @person_id,@identity_id,@person_name from t_base_person p where p.IDENTITY_NUM=v_loginName l if @person_id is not null THEN
select l.login_name,l.login_password into @login_name,@login_password from t_sys_loginperson l where l.person_id=@person_id and l.IDENTITY_ID select @login_name as USER_NAME,@person_id as PERSON_ID,@identity_id as IDENTITY_ID ,@person_name as REAL_NAME,@login_password LEAVE label;
end if;
end if;
--正常登录名查询
select l.login_name,person_id,identity_id,l.person_name,l.login_password into @login_name,@person_id,@identity_id,@person_name,@login_password if @person_id is not null THEN
select @login_name as USER_NAME,@person_id as PERSON_ID,@identity_id as IDENTITY_ID ,@person_name as REAL_NAME,@login_password
LEAVE label;
end if;
END;
drop function if EXISTS f_base_check_id_number;
CREATE FUNCTION `f_base_check_id_number`(`idnumber` CHAR(18))
RETURNS enum('1','0')
LANGUAGE SQL
NOT DETERMINISTIC
NO SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE status ENUM('1','0') default'0';
DECLARE verify CHAR(1);
DECLARE sigma INT;
DECLARE remainder INT;
IF length(idnumber) = 18 THEN
set sigma = cast(substring(idnumber,1,1) as UNSIGNED) * 7
mysql存储过程使用+cast(substring(idnumber,2,1) as UNSIGNED) * 9
+cast(substring(idnumber,3,1) as UNSIGNED) * 10
+cast(substring(idnumber,4,1) as UNSIGNED) * 5
+cast(substring(idnumber,5,1) as UNSIGNED) * 8
+cast(substring(idnumber,6,1) as UNSIGNED) * 4
+cast(substring(idnumber,7,1) as UNSIGNED) * 2
+cast(substring(idnumber,8,1) as UNSIGNED) * 1
+cast(substring(idnumber,9,1) as UNSIGNED) * 6
+cast(substring(idnumber,10,1) as UNSIGNED) * 3
+cast(substring(idnumber,11,1) as UNSIGNED) * 7
+cast(substring(idnumber,12,1) as UNSIGNED) * 9
+cast(substring(idnumber,13,1) as UNSIGNED) * 10
+cast(substring(idnumber,14,1) as UNSIGNED) * 5
+cast(substring(idnumber,15,1) as UNSIGNED) * 8
+cast(substring(idnumber,16,1) as UNSIGNED) * 4
+cast(substring(idnumber,17,1) as UNSIGNED) * 2;
set remainder = MOD(sigma,11);
set verify = (case remainder
when 0 then '1' when 1 then '0' when 2 then 'X' when 3 then '9'
when 4 then '8' when 5 then '7' when 6 then '6' when 7 then '5'
when 8 then '4' when 9 then '3' when 10 then '2'else'/' end
);
END IF;
IF right(idnumber,1) = verify THEN
set status = '1';
END IF;
RETURN status;
END
SELECT PERSON_ID,IDENTITY_ID,PERSON_NAME as REAL_NAME,LOGIN_NAME as USER_NAME FROM
(
select p.person_id,p.identity_id,p.person_l as inputname,l.login_name from t_base_person p join t_sys_loginperson l on p.person_id= l.person_id and p. union
select p.person_id,p.identity_id,p.person_name,p.IDENTITY_NUM as inputname,l.login_name from t_base_person p join t_sys_loginperson l on p.person_id= l.pe union
select l.person_id,l.identity_id,l.person_name,s.STU_TEL as inputname,l.login_name from t_base_student s join t_sys_loginperson l on s.student_id = l.person_id a union
select person_id,identity_id,l.person_name,l.login_name as inputname,l.login_name from t_sys_loginperson l
) t WHERE t.inputname = '155********'
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论