用户注册册及确认在线的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
用户注册及跟踪代码（一）
80酷酷网 80kuku.com
