实验18-存储过程参考答案

自定义存储过程(一)一、实验目的一、实验目的：1、创建存储过程二、实验内容1、创建一存储过程，实现用户登录验证（该存储过程有待登录框验证用户合法性使用）存储过程名称P_check输入参数@uname--用户名@upassword--密码返回值满足条件的用户信息createproc[dbo].[p_check]@unamechar(10),@upasswordchar(10)asselect*fromaccountwhereusername=@unameandpassword=@upasswordgo--运行该存储过程，看是否正常显示execp_check'jyl','123'2.创建如下存储过程实现用户信息查询a.存储过程p_list:实现’查询all’：将所有用户信息显示存储过程名称P_list输入参数无返回值表createproc[dbo].[p_list]asselect*fromaccountgo--执行execp_listb.存储过程实现‘查询’:查询指定用户名信息存储过程名称P_listinfo输入参数@uname–用户名返回值输入的用户名对应的用户信息)createproc[dbo].[p_list1]@unamevarchar(10)asselect*fromaccountwhereusernamelike'%'+@uname+'%'go--执行execp_list1'lc'--运行该存储过程，看是否正常显示存储过程实践(二)1.修改存储过程p_list1,实现模糊查询如下：输入’jl’，能检索出’jyl’‘alyz’,’lyt’等。备注：关于模糊检索的字符串构造，已经在当前数据库封装成用户自定义函数fnStrProc，createproc[dbo].[p_list1]@unamevarchar(10)asselect*fromaccountwhereusernamelikedbo.fnStrProc(@uname)—调用函数实现对关键字的处理，例如将输入的jl,处理为’%j%l%’，实现更模糊的检索go--执行execp_list1'jl'--创建自定义函数createfunctionfnStrProc(@PartialNamevarchar(20))--接收输入关键字returnsvarchar(50)asbegin--实现思路：将输入关键字张大维-处理为-%张%大%维declare@outPutvarchar(20),@timesint,@lenint--@output用于存储分解完的字符串--@times用于存储循环的次数--@len用于存储检索关键字的长度--@partialName用于存储检索关键字set@len=len(@partialName)set@times=1set@output='%'while@times=@len--判断循环次数是否小于等于字符串长度beginset@outPut=@outPut+substring(@partialname,@times,1)+'%'--截取某一字符与上一次截取进行连接set@times=@times+1endreturn@outputend--调用该函数，查询bbsUsers表用户名字段内容中是否含有关键字’东败’select*frombbsUserswhereunamelikedbo.fnStrProc('东败')2.创建一存储过程，将实现用户信息的录入。（该存储过程有待注册框使用）存储过程名称P_add输入参数自己定义返回值无--创建用户注册表createtableaccount(unamevarchar(20),upsdvarchar(20),usexchar(2),umailvarchar(30),uregistdatedatetimedefaultgetdate(),udesvarchar(255))gocreateprocP_add(@unamechar(10),@passchar(10),@sexchar(2),@mailvarchar(30),@desvarchar(255))asinsertintoaccountvalues(@uname,@pass,@sex,@mail,default,@des)go--执行execp_add'test','test','男','',''--测试该存储过程，看数据能否正常录入。3.创建一存储过程P_Check,对登录的用户进行验证，如果用户输入的用户名和密码匹配，返回1，如果用户名相同但是密码不同，返回2，如果用户名和密码都不匹配，返回3。createprocproc_check@unamevarchar(20),@upsdvarchar(20)asifexists(select*fromaccountwhereuname=@unameandupsd=@upsd)return1elseifexists(select*fromaccountwhereuname=@unameandupsd@upsd)return2elsereturn3go--调用存储过程declare@resultintexec@result=proc_check'jyl','123'select@resultgo4.修改存储过程P_Check,对登录的用户进行验证，如果用户输入的用户名和密码匹配，判断该用户在登陆信息表是否存在，如果存在更新用户的上次登录时间为当前时间，同时读取用户的上次登录时间；如果该用户不存在，插入该用户的本次登录信息；设置上次登录时间为Null，返回1，如果用户名相同但是密码不同，返回2，如果用户名和密码都不匹配，返回3。--创建用户表accountdroptableaccountgocreatetableaccount(unamevarchar(20)primarykey,upsdvarchar(20),usexchar(2),umailvarchar(30),uregistdatedatetimedefaultgetdate(),udesvarchar(255))go--创建登录信息表createtableloginfo(uanmevarchar(20)foreignkeyreferencesaccount(uname),lastlogindatetime)alterprocproc_check@unamechar(10),@upsdchar(10),@loginTimedatetimeoutput--定义输出参数，用于接收上次登录时间asifexists(select*fromaccountwhereuname=@unameandupsd=@upsd)--用户名密码匹配beginifexists(select*fromloginfowhereuname=@uname)--判断该用户在登录信息表中是否存在beginset@loginTime=(selectlastLoginfromloginfowhereuname=@uname)--读取该用户上次登录时间，赋值给输出参数updateloginfosetlastLogin=getdate()whereuname=@uname--更新用户登录时间return1--返回状态，说明登录成功endelse--该用户在登录信息表中不存在begininsertintologinfovalues(@uname,getdate())--插入一登录信息set@loginTime=null--设置上次登录时间为空return1--返回，说明登录成功endendelseifexists(select*fromaccountwhereuname=@unameandupsd@upsd)--用户名匹配，密码不匹配return2--返回else--用户名错误，或者用户名错误，密码错误等情况return3--返回go--带输出参数的调用declare@resultint,@logintimedatetimeexec@result=proc_check'abc','456',@logintimeoutputselect@result返回状态,@logintime上一次登录时间go