用戶注冊冊及確認在線的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