用户注册册及确认在线的ASP程序。
1. SQL的表及储存过程
---------------------------------------------
CREATE TABLE [dbo].[userbaseinfo] (
[userid] [varchar] (50) NOT NULL ,
[password] [varchar] (50) NOT NULL ,
[validcodelogin] [char] (50) NOT NULL ,
[userlevel] [char] (1) NULL ,
[logintime] [char] (50) NULL
) ON [PRIMARY]
GO
alter table userbaseinfo
add
constraint PK_userbaseinfo_userid
primary key (userid)
Go
CREATE TABLE [dbo].[userdetailinfo] (
[userid] [varchar] (50) NOT NULL ,
[password] [varchar] (30) NOT NULL ,
[realname] [varchar] (10) NULL ,
[sex] [char] (10) NULL ,
[birthday] [datetime] NULL ,
[idcode] [varchar] (50) NULL ,
[address] [varchar] (300) NULL ,
[email] [varchar] (50) NULL ,
[telephone] [varchar] (50) NULL
) ON [PRIMARY]
GO
alter table userdetailinfo
add
constraint PK_userdetailinfo_userid
primary key (userid)
Go
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
CREATE proc dbo.proc_GetRandom_internal
--取得校验码
@minNum integer,
@maxNum integer,
@RandomNum float output
as
set nocount on
declare @numRange integer
declare @ranSeed integer
declare @curTime datetime
begin
select @numRange=@maxNum-@minNum+1
select @curTime=getdate()
select @ranSeed=datediff(s,'2000-1-1',@curTime)
select @ranSeed=@ranSeed+1
select @RandomNum=rand()*@numRange+@minNum
--print @RandomNum
return
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE proc dbo.proc_GetValidCode_Internal
--取得校验码
@CodeLength integer,
@ValidCode varchar(10) output
as
set nocount on
declare @chrRnd char(1)
declare @chrRndNo integer
begin
select @ValidCode=""
while (@CodeLength>0)
begin
exec proc_GetRandom_internal 1,52,@chrRndNo output
if @chrRndNo>26
begin
select @chrRndNo=@chrRndNo+6
end
select @chrRnd=char(@chrRndNo+64)
select @ValidCode=@ValidCode+@chrRnd
select @CodeLength=@CodeLength-1
end
print @validCode
return
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE proc dbo.proc_UserInfoUpdate
--用户更新个人信息
@ValidCodeLogin varchar(10),
@RealName Varchar(10),
@Sex Varchar(10),
@Birthday datetime,
@IDCode Varchar(50),
@Address Varchar(300),
@eMail Varchar(50),
@Telephone Varchar(50)
as
set nocount on
declare @UserValidFlag int
declare @ValidCodeReg varchar(30)
declare @UserLevel varchar(1)
declare @UserID varchar(30)
begin
exec proc_isUserValidbyCode_internal @ValidCodeLogin,@UserValidFlag output
if @UserValidFlag<0
begin
--select @UserValidFlag as resultID
-- -1 用户尚未登录
-- -2 用户超时
return @UserValidFlag
end
select @UserID=UserID from UserBaseinfo where ValidCodeLogin=@ValidCodeLogin
Update UserDetailInfo
set RealName=@RealName,
Sex=@Sex,
Birthday=@Birthday,
IDCode=@IDCode,
Address=@Address,
eMail=@eMail,
Telephone=@Telephone
where
UserID=@UserID;
if (@RealName="" or @Birthday="" or @Sex="" or @IDCode="" or @Address="" or @eMail="" or @Telephone="")
begin
--select -3 as resultID
return -3 --信息尚未全部填写
end
select 0 as resultID
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc dbo.proc_UserLogOut
--用户退出
@ValidCodeLogin varchar(10)
as
set nocount on
declare @UserValidFlag int
declare @UserLevel varchar(9)
begin
exec proc_isUserValidbyCode_internal @ValidCodeLogin,@UserValidFlag output
if (@UserValidFlag<0)
begin
--select @UserValidFlag as resultID
return @UserValidFlag
-- -1 用户尚未登录
-- -2 用户超时
end
Update UserBaseInfo
set ValidCodeLogin='',
LoginTime='1970-1-1'
where
ValidCodeLogin=@ValidCodeLogin
--select 0 as resultID
return 0
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE proc dbo.proc_UserRegBase
--用户基本资料注册
@UserID Varchar(30),
@Password Varchar(30)
as
set nocount on
declare @UserLevel varchar(9)
--declare @ValidCodeReg varchar(10)
declare @ValidCodeLogin varchar(10)
declare @LoginTime datetime
declare @userExist int
declare @PwdLength int
begin
select @UserLevel="0"
select @PwdLength=4
if (datalength(@Password)<@PwdLength)
begin
select -4 as returnID
return -4 --密码长度不够
end
--exec proc_GetValidCode_internal 10,@ValidCodeReg output --取得用户注册校验码
exec proc_GetValidCode_internal 10,@ValidCodeLogin output --取得用户登录校验码
exec proc_isUserExist_internal @UserID,@userExist output --取得用户存在标志
select @LoginTime=getdate()
print @userExist
if @userExist=0
begin
select -1 as resultID
return -1 --用户已存在
end
--插入用户基本信息表
insert into UserBaseInfo
(UserID,Password,UserLevel,ValidCodeLogin,LoginTime)
Values(@UserID,@Password,@UserLevel,@ValidCodeLogin,@LoginTime)
--插入用户详细信息表
insert into UserDetailInfo
(UserID,Password) Values(@UserID,@Password)
--取得用户注册校验码,登录校验码
select 0 as resultID
select ValidCodeLogin from UserBaseInfo where UserID=@UserID
return 0
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create proc dbo.proc_isUserExist_internal
--判断用户名是否存在
@UserID Varchar(30),
@existFlag int output
as
set nocount on
begin
if not EXISTS(select * from UserBaseInfo where UserID=@UserID)
begin
select @existFlag =-1
return
end
select @existFlag =0
return
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create proc dbo.proc_isUserValidbyCode_internal
--用户身份检验(根据登录校验码)
@ValidCodeLogin varchar(10),
@validFlag int output
as
set nocount on
declare @LoginTime datetime
declare @curTime datetime
declare @diffTime datetime
begin
if not EXISTS(select * from UserBaseInfo where ValidCodeLogin=@ValidCodeLogin)
begin
select @validFlag=-1 --用户尚未登录
return
end
select @LoginTime = (select LoginTime from UserBaseInfo where ValidCodeLogin=@ValidCodeLogin)
select @curTime=getdate()
select @diffTime=datediff(hh,@LoginTime,@curTime)
if @diffTime>=10
begin
select @validFlag=-2 --用户超时
return
end
select @LoginTime=getdate() --取得用户最后登录时间
update UserBaseInfo set LoginTime=@LoginTime where ValidCodeLogin=@ValidCodeLogin
select @validFlag=0
return
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO