Mysql中使用存储过程返回查询多个表的数据信息
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql中使用存储过程返回查询多个表的数据信息,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3993字,纯文字阅读大概需要6分钟。
内容图文
-- 测试手机号 call P_Base_CheckLogin(‘15584463676‘); -- 测试登录名 call P_Base_CheckLogin(‘sch000001‘) -- 测试身份证号 call P_Base_CheckLogin(‘080428402304032042‘) -- 测试学生手机号 call P_Base_CheckLogin(‘13019212198‘) 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 where p.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=@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 as 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=@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 as 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 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=@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 as 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 from t_sys_loginperson l where l.login_name=v_loginName limit 1; 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 as 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 +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
原文:http://www.cnblogs.com/littlehb/p/6962262.html
内容总结
以上是互联网集市为您收集整理的Mysql中使用存储过程返回查询多个表的数据信息全部内容,希望文章能够帮你解决Mysql中使用存储过程返回查询多个表的数据信息所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。