月光软件站 - 编程文档 - 数据库

数据库

本类阅读TOP10

·SQL语句导入导出大全
·Power Designer杂记
·SQL Server日期计算
·常用的oracle函数使用说明(一)
·sqlserver2000数据库置疑的解决方法
·MS SQLServer OLEDB分布式事务无法启动的一般解决方案
·SQL to Excel 的应用
·SQL语句导入导出大全
·Error:ORA-01033:ORACLE initialization or shutdown in progress错误解决
·Oracle中password file的作用及说明

→ 分类导航

VC语言	Delphi
VB语言	ASP
Perl	Java
Script	数据库
其他语言	游戏开发
文件格式	网站制作
软件工程	.NET开发

身份证验证

作者：未知来源：月光软件站 加入时间：2005-2-28　月光软件站

alter function dbo.IDTrue--验证十八位身份证是否正确
(
@ID varchar(18)
)
returns bit
as
begin
--验证格式是否正确
declare @RES bit
declare @W table (rn int,val int)
insert into @W(rn,val)
select 1,7
union select 2,9
union select 3,10
union select 4,5
union select 5,8
union select 6,4
union select 7,2
union select 8,1
union select 9,6
union select 10,3
union select 11,7
union select 12,9
union select 13,10
union select 14,5
union select 15,8
union select 16,4
union select 17,2
union select 18,1
declare @A table (rwn int ,val varchar(1))
insert into @A(rwn,val)
select 0,'1'
insert into @A(rwn,val)
select 1,'0'
insert into @A(rwn,val)
select 2,'X'
insert into @A(rwn,val)
select 3,'9'
insert into @A(rwn,val)
select 4,'8'
insert into @A(rwn,val)
select 5,'7'
insert into @A(rwn,val)
select 6,'6'
insert into @A(rwn,val)
select 7,'5'
insert into @A(rwn,val)
select 8,'4'
insert into @A(rwn,val)
select 9,'3'
insert into @A(rwn,val)
select 10,'2'
declare @i int
declare @j int
declare @S int

if len(@ID)=18
begin
select @S=0,@i=1
declare @IDI int
declare @WI int
declare @PII varchar(1)
while    @i<=17
     begin
       select @PII=substring(@ID,@i,1)
       if    @PII like '[0-9]'
        begin
         select @IDI=convert(int,@PII)
         select @WI=val from @W where   rn=@i
         select @j=@IDI*@WI
         select @S=@S+@j
         select @i=@i+1
        end
       else
        begin
              select @RES=0
               return @RES
        end

     end
select @S=@S % 11
select @IDI=convert(int,substring(@ID,18,1))
declare @pi   varchar(1)
select @pi=val from @A where rwn =@S
if   @pi=@IDI
      select @RES=1
else
      select @RES=0
end
else
begin
     select @RES=0
end
return @RES
end
go
declare @pc bit
select @pc=dbo.IDTrue('420324198101031224')
select @pc
go

alter function    SFZ15To18--十五位升十八
(
   @sfz varchar(15)
)
returns varchar(18)
as
begin
declare @W table (rn int,val int)
insert into   @W(rn,val)
select 1,7
union select 2,9
union select 3,10
union select 4,5
union select 5,8
union select 6,4
union select 7,2
union select 8,1
union select 9,6
union select 10,3
union select 11,7
union select 12,9
union select 13,10
union select 14,5
union select 15,8
union select 16,4
union select 17,2
union select 18,1
declare @A table (rwn int ,val varchar(1))
insert into @A(rwn,val)
select 0,'1'
insert into @A(rwn,val)
select 1,'0'
insert into @A(rwn,val)
select 2,'X'
insert into @A(rwn,val)
select 3,'9'
insert into @A(rwn,val)
select 4,'8'
insert into @A(rwn,val)
select 5,'7'
insert into @A(rwn,val)
select 6,'6'
insert into @A(rwn,val)
select 7,'5'
insert into @A(rwn,val)
select 8,'4'
insert into @A(rwn,val)
select 9,'3'
insert into @A(rwn,val)
select 10,'2'
declare @NEWID varchar(18)
select @NEWID=substring(@sfz,1,6)+'19'+substring(@sfz,7,9)
declare @i int
declare @j int
declare @S int
select @S=0,@i=1
declare @IDI int
declare @WI int
declare @PII varchar(1)
while @i<=17
   begin
         select @PII=substring(@NEWID,@i,1)
         if    @PII like '[0-9]'
         begin
          select @IDI=convert(int,@PII)
          select @WI=val from @W where   rn=@i
          select @j=@IDI*@WI
          select @S=@S+@j
          select @i=@i+1
         end
         else
         begin
          return ''
         end
   end
select @S=@S % 11
declare @pi   varchar(1)
select @pi=val from @A where rwn =@S
select @NEWID=@NEWID+@pi
return @NEWID

end
go
select dbo.SFZ15To18('420324810103153')
go
--日期是否正确
alter function   ChkYMD(
@y int,
@m tinyint,
@d tinyint,
@cy int
)
returns bit
as
begin
   declare @res bit
   select @res=1
   if @y<1900 or @y>@cy
     begin
          select @res=0
          return @res
     end
   if @m=1 or @m=3 or @m=5 or @m=7 or @m=8 or @m=10 or @m=12
     begin
         if (@d<1) or (@d>31)
           begin
            select @res=0
            return @res
           end
     end

   if @m=2
     begin
       if ((@y%4)=0) and ((@y % 100)<>0) or ( (@y % 400)=0 )
               begin--闰年
                if (@d<1) or (@d>29)
                   begin
                        select @res=0
                        return @res
                   end
               end else
               begin
                if (@d<1) or (@d>28)
                  begin
                        select @res=0
                        return @res
                   end
               end

     end
   if @m=4 or @m=6 or @m=9 or @m=11
     begin
          if (@d<1) or (@d>30)
                   begin
                        select @res=0
                        return @res
                   end
     end
    return @res

end
go
select dbo.chkymd(1981,1,3,Year(getdate()))
go
/*
y：年，m：月，d：日。在参数都只传入相应的整数
返回值：0 星期一
       1   星期2
       2   星期3
      3 星期4
      4 星期5
      5 星期6
      6 星期7
*/

alter function   GetWeekDay(
@y int,
@m int,
@d int
)
returns tinyint
as
begin
   declare @a tinyint
   select @a=7
   if @m=1 or @m=2
     begin
        select @m=@m+12
        select @y=@y-1
     end
select @a=(@d+2*@m+3*(@m+1)/5+@y+@y/4-@y/100+@y/400)%7;
return @a
end
go
select dbo.getweekday(2004,12,10)

相关文章：

相关软件：