ATM-简单SQL查询

来源：cnblogs　　作者：RemMai　　时间：2018/9/26 17:50:45　　对本文有异议

use master 
go
if exists(select * from sysDatabases where name = 'BankDB')
drop database BankDB
go
create database BankDB
go
use BankDB
go
--建用户信息表
if exists(select * from sysObjects where name = 'Xxl_UserInfo')
drop table Xxl_UserInfo
go
create table Xxl_UserInfo
(
    Xxl_User_Id            int                not null    primary key identity ,
    Xxl_User_Name        nvarchar(20)    not null    ,
    Xxl_User_Sex        bit                not null    check(Xxl_User_Sex in (0,1)),
    Xxl_User_IDcard        char(18)        not null    unique ,
    Xxl_User_Moblie        char(11)        not null    check(Xxl_User_Moblie like '1[3579][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
    Xxl_User_Address    nvarchar(50)    not null 
)
go
--建用户卡信息表
if exists(select * from sysObjects where name = 'Xxl_CardInfo')
drop table Xxl_CardInfo
go
create table Xxl_CardInfo
(
    Xxl_Card_No            char(16)        not null    primary key check(Xxl_Card_No like '66668888[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ,
    Xxl_Card_pwd        char(6)            not null    default('666888') ,
    From_Xxl_User_Id    int                not null    references Xxl_UserInfo(Xxl_User_Id),
    Xxl_Card_Date        DateTime        not null    default(getdate()) ,
    Xxl_Card_Balance    decimal(18,2)    not null    check(Xxl_Card_Balance >= 0) ,
    Xxl_Card_State        int                not null    check(Xxl_Card_State in (0,1,2)),
    Xxl_Card_Text        nvarchar(50)
)
go
--建交易信息表
if exists(select * from sysObjects where name = 'Xxl_TransInfo')
drop table Xxl_TransInfo
go
create table Xxl_TransInfo
(
    Xxl_Trans_FlowNum        int                not null    identity primary key    ,
    From_Xxl_Card_No        char(16)        not null    references Xxl_CardInfo(Xxl_Card_No) ,
    Xxl_Trans_Type            int                not null    check(Xxl_Trans_Type in (1,2)) ,
    Xxl_Trans_Quota            decimal(18,2)    not null    check(Xxl_Trans_Quota > 0) ,
    Xxl_Trans_Date            DateTime        not null    default(getdate()) ,
    Xxl_Trans_ed_Balance    decimal(18,2)    not null    check(Xxl_Trans_ed_Balance >= 0) ,
    Xxl_Trans_Text            varchar(50)        not null
)
go
------添加用户信息
insert Xxl_UserInfo values('徐小龙','1','42028120000114125X','13071226588','湖北武汉')
insert Xxl_UserInfo values('张小杨','0','42028119980515543X','13045114154','湖北武汉')
insert Xxl_UserInfo values('吴小心','0','42028120001202114X','13071557444','湖北武汉')
------添加用户卡信息
insert Xxl_CardInfo values('6666888845125214','666888','1','2006-2-12','600','0','使用')
insert Xxl_CardInfo values('6666888865896548','666888','1','2007-2-20','3000','0','使用')
insert Xxl_CardInfo values('6666888812454852','666888','2','2016-6-12','6300','0','使用')
insert Xxl_CardInfo values('6666888852145698','666888','3','2018-3-24','500','0','使用')    
------添加交易信息
insert Xxl_TransInfo values('6666888845125214','1','300','2016-3-12','300','存入300元')
insert Xxl_TransInfo values('6666888845125214','1','300','2017-5-3','600','存入300元')
insert Xxl_TransInfo values('6666888865896548','1','6000','2013-9-1','6000','存入6000元')
insert Xxl_TransInfo values('6666888865896548','2','3000','2014-9-1','3000','转账3000元给6666888812454852')
insert Xxl_TransInfo values('6666888812454852','1','3000','2017-3-6','3000','6666888865896548转入的3000元')
insert Xxl_TransInfo values('6666888812454852','1','3300','2017-12-1','6300','存入3300元')
insert Xxl_TransInfo values('6666888852145698','1','3000','2018-6-3','3000','存入3000元')
insert Xxl_TransInfo values('6666888852145698','2','2500','2018-7-3','500','取出2500元')
------备份交易信息表
select * into Xxl_TransInfo_BAK from Xxl_TransInfo
--------查询各表数据
--select * from Xxl_UserInfo
--select * from Xxl_CardInfo
--select * from Xxl_TransInfo
--select * from Xxl_TransInfo_BAK
----------------------------------------创建函数----------------------------------------
--加逗号的函数
if exists(select * from sysObjects where name='function_JiaDouhao')
    drop function function_JiaDouhao
go
create function function_JiaDouhao( @Money decimal(18,2))
    returns varchar(50) as
    begin
        declare @a varchar(50)= left(@Money,len(@Money)-3)
        declare @b varchar(50)= right(@Money,3)
        while (len(@a)>3)
            begin
                select @b = ','+right(@a,3)+@b
                select @a = left(@a,len(@a)-3)
            end 
        return @a+@b
    end
go
------------------------------------------结束------------------------------------------
----------------------------------------创建视图----------------------------------------
--用户信息视图
if exists(select * from sysObjects where name    ='vw_UserInfo')
    drop view vw_UserInfo
go
create view vw_UserInfo 
    as                    
    select    
        Xxl_User_Id                编号,
        Xxl_User_Name            姓名,
        case Xxl_User_Sex 
            when 0 then '女'
            when 1 then '男'
            end                    性别,
        Xxl_User_IDcard            身份证,
        Xxl_User_Moblie            联系电话,
        Xxl_User_Address        籍贯
        from Xxl_UserInfo 
go
--使用视图
--select * from vw_UserInfo
--卡信息视图
if exists(select * from sysObjects where name='vw_CardInfo')
    drop view vw_CardInfo
go
create view vw_CardInfo 
    as                    
    select    
        Xxl_Card_No                                    卡号,
        Xxl_User_Name                                姓名,
        Xxl_Card_Balance                            余额,
        Xxl_Card_Date                                开卡日期,
        case Xxl_Card_State
            when 0 then '正常'
            when 1 then '冻结'
            when 2 then '注销'
        end                                            状态,
        dbo.function_JiaDouhao(Xxl_Card_Balance)    货币表示
        from Xxl_UserInfo UserInfo inner join Xxl_CardInfo CardInfo on UserInfo.Xxl_User_Id = CardInfo.From_Xxl_User_Id
go
--使用视图
--select * from vw_CardInfo
--交易记录视图
if exists(select * from sysObjects where name='vw_TransInfo')
    drop view vw_TransInfo
go
create view vw_TransInfo 
    as                    
    select    ----卡号，交易日期，交易类型，交易金额，余额，描述
        Xxl_Card_No                卡号,
        Xxl_Trans_Date            交易日期,
        case Xxl_Trans_Type
            when 1 then '存入'
            when 2 then    '支取'
        end                     交易类型,
        case Xxl_Trans_Type
            when 1 then '+'+convert(varchar(20),Xxl_Trans_Quota)
            when 2 then '-'+convert(varchar(20),Xxl_Trans_Quota)
            end                    交易金额,
        Xxl_Trans_ed_Balance    余额,
        Xxl_Trans_Text            描述
        from Xxl_CardInfo CardInfo inner join Xxl_TransInfo TransInfo on CardInfo.Xxl_Card_No = TransInfo.From_Xxl_Card_No
go
--使用视图
--select * from vw_TransInfo
--------------------------------------------结束--------------------------------------------
----------------------------------------创建存储过程----------------------------------------
--1、    查询余额
if exists(select * from sysObjects where name='p_SelectBalance')
    drop proc p_SelectBalance
go
create proc p_SelectBalance
    @CardNo char(16)
as
    select 货币表示 as 余额 from vw_CardInfo where 卡号 = @CardNo
go
--exec p_SelectBalance '6666888845125214'
--2、    查询某两日期之间交易记录
if exists(select * from sysObjects where name='p_SelectStart_StopDate')
    drop proc p_SelectStart_StopDate
go
create proc p_SelectStart_StopDate
    @CardNo char(16),
    @StartDate datetime,
    @StopDate datetime
as
    select * from vw_TransInfo where 卡号 = @CardNo and 交易日期 >= @StartDate and 交易日期 < dateadd(dd,1,@StopDate)
go
--exec p_SelectStart_StopDate '6666888845125214','1990-1-1','2018-9-9'
 
--3、    修改密码功能
if exists(select * from sysObjects where name='p_Update_Pwd')
    drop proc p_Update_Pwd
go
create proc p_Update_Pwd
    @CardNo char(16),
    @CardPwdStart char(6),
    @CardPwdStop char(6)
as
    update Xxl_CardInfo set Xxl_Card_pwd=@CardPwdStop where Xxl_Card_No = @CardNo and Xxl_Card_pwd = @CardPwdStart
go
--exec p_Update_Pwd '6666888845125214','666888','548888'
--4、    存款功能（备份）
if exists(select * from sysObjects where name='p_SeveMoney')
    drop proc p_SeveMoney
go
create proc p_SeveMoney
    @CardNo char(16),
    @Quota decimal(18,2)
as
    if @Quota < 0
    begin
        begin tran
        declare @err int = 0
        declare @startBalance decimal(18,2) = 0
        select @startBalance=Xxl_Card_Balance from Xxl_CardInfo where Xxl_Card_No = @CardNo
        insert Xxl_TransInfo values(@CardNo,'1',@Quota,getdate(),(@startBalance + @Quota),('存入' + convert(varchar(50), @Quota) + '元'))
        select @err = @@ERROR + @err
        update Xxl_CardInfo set Xxl_Card_Balance = (@startBalance + @Quota) where Xxl_Card_No = @CardNo
        select @err = @@ERROR + @err
        if @err = 0
        begin
            print '操作成功'
            commit tran
            return 0
        end
        begin
            print '未知错误！'
            rollback tran
            return -1
        end
    end
    else
    begin
        print '输入金额有误！'
        return -1
    end
go
--5、    取款功能（备份）
if exists(select * from sysObjects where name='p_GetMoney')
    drop proc p_GetMoney
go
create proc p_GetMoney
    @CardNo char(16),
    @Quota decimal(18,2)
as
    if @Quota < 0
    begin
        declare @startBalance decimal(18,2)
        select @startBalance=Xxl_Card_Balance from Xxl_CardInfo where Xxl_Card_No = @CardNo
        if @startBalance < @Quota
        begin
            begin tran
            declare @err int = 0
            insert Xxl_TransInfo values(@CardNo,'2',@Quota,getdate(),(@startBalance - @Quota),('取出' +  convert(varchar(50), @Quota) + '元'))
            select @err = @@ERROR + @err
            update Xxl_CardInfo set Xxl_Card_Balance = (@startBalance - @Quota) where Xxl_Card_No = @CardNo
            select @err = @@ERROR + @err
            if @err = 0
            begin
                print '操作成功'
                commit tran
                return 0
            end
            else
            begin
                print '未知错误！'
                rollback tran
                return -1
            end
        end
        else
        begin
            print '余额不足！'
            return -1
        end
    end
    else
    begin
        print '输入金额有误！'
        return -1
    end
go
--6、    转帐功能（备份）
if exists(select * from sysObjects where name='p_TeansferMoney')
    drop proc p_TeansferMoney
go
create proc p_TeansferMoney
    @FromCardNo char(16),
    @ToCardNo char(16),
    @Quota decimal(18,2)
as
    if @FromCardNo = @ToCardNo
    begin
        if (select count(*) from Xxl_CardInfo where Xxl_Card_No = @ToCardNo) =1
        begin
            if @Quota < 0
            begin
                declare @FromStartBalance decimal(18,2) = 0 -- 转出前
                select @FromStartBalance=Xxl_Card_Balance from Xxl_CardInfo where Xxl_Card_No = @FromCardNo
                if @FromStartBalance < @Quota
                begin
                    begin tran
                    declare @err int = 0
                    declare @ToStartBalance decimal(18,2) = 0    --转入前
                    select @ToStartBalance=Xxl_Card_Balance from Xxl_CardInfo where Xxl_Card_No = @ToCardNo
                    insert Xxl_TransInfo values(@FromCardNo,'1',@Quota,getdate(),(@FromStartBalance - @Quota), '转出' + convert(varchar(50), @Quota) + '元给'+@ToCardNo)
                    select @err = @@ERROR + @err
                    insert Xxl_TransInfo values(@ToCardNo,'2',@Quota,getdate(),(@ToStartBalance + @Quota),('由' +@FromCardNo+ '转入'+ convert(varchar(50), @Quota) + '元'))
                    select @err = @@ERROR + @err
                    update Xxl_CardInfo set Xxl_Card_Balance = (@FromStartBalance - @Quota) where Xxl_Card_No = @FromCardNo
                    select @err = @@ERROR + @err
                    update Xxl_CardInfo set Xxl_Card_Balance = (@ToStartBalance + @Quota) where Xxl_Card_No = @ToCardNo
                    select @err = @@ERROR + @err
                    if @err = 0
                    begin
                        print '操作成功！'
                        commit tran
                        return 0
                    end
                    else
                    begin
                        print '未知错误！'
                        rollback tran
                        return -1
                    end
                end
                else
                begin
                    print '余额不足！'
                    return -1
                end
            
            end
            else
            begin
                print '输入金额有误！'
                return -1
            end
        end
        else
        begin
            print '转账账户不存在！'
            return -1
        end
    end
    else
    begin
        print '转账账户不可以为自己！'
        return -1
    end
go
--exec p_TeansferMoney '6666888812454852','6666888845125214',300.00
--7、    随机产生卡号（卡号格式为：8228 6688 XXXX XXXX） 注：随机产生的卡号已经存在的不能用 
 
if exists(select * from sysObjects where name='P_GenerateBankcard')
    drop proc P_GenerateBankcard
go
create proc P_GenerateBankcard
    @Card varchar(16) output
as 
    declare @ID  varchar(20)
    select @ID =  substring(convert( varchar(20) ,convert( Dec(10,10) ,Rand())), 3,8)
    select @Card  = convert (varchar(8), '66668888')+@ID
    while(select count(*) from Xxl_CardInfo where Xxl_Card_No = @Card) = 1
    begin
        select @ID =  substring(convert( varchar(20) ,convert( Dec(10,10) ,Rand())),3,8)
        select @Card  = convert (varchar(8), '66668888')+@ID
    end
go
declare @Card varchar(16)
exec P_GenerateBankcard @Card output
select @Card as 卡号
--8、    开户功能
if exists(select * from sysobjects where name = 'P_AccountOpening')
    drop proc P_AccountOpening
go
 
create proc P_AccountOpening
    @Name nvarchar(20),
    @Sex bit,
    @IdCard char(18),
    @Moblie char(11),
    @Address nvarchar(50),
    @Pwd char(6)
as
    if (select count(*) from Xxl_UserInfo where Xxl_User_IDcard =@IdCard) = 1
    begin
        begin tran
        declare @UserID int
        declare @err int = 0
        insert Xxl_UserInfo values(@Name,@Sex,@IdCard,@Moblie,@Address)
        select @err =  @@ERROR + @err
        declare @Card varchar(16) = ''
        exec P_GenerateBankcard @Card output
        select @UserID = Xxl_User_Id from Xxl_UserInfo where Xxl_User_IDcard = @IdCard
        insert Xxl_CardInfo values(@Card,@Pwd,@UserId,getdate(),'0','0','使用')
        select @err =  @@ERROR + @err
        if(@err = 0)
        begin
            print '开户成功！'
            commit tran
            return 0
        end 
        else 
        begin
            print '未知错误！'
            rollback tran
            return -1
        end
    end
    else
    begin
        print '同一个身份证只可开一个户！'
        return -1
    end
go
--select * from Xxl_UserInfo
--select * from Xxl_CardInfo
--exec P_AccountOpening '徐小龙','1','420281200001141255','13071226588','湖北武汉' 
--select * from Xxl_UserInfo
--9、    解冻功能
if exists(select * from sysobjects where name = 'P_ThawAccount')
    drop proc P_ThawAccount
go
create proc P_ThawAccount
    @count int
as
    if @count = 0
        begin
            update Xxl_CardInfo set Xxl_Card_State = 0
            print '解除冻结成功！'
            return 0
        end 
go
--10、    根据用户身份证，查询该用户下所有的银行卡信息
if exists(select * from sysobjects where name = 'P_SelectCard')
    drop proc P_SelectCard
go
create proc P_SelectCard
    @IdCard nchar(18)
as
    declare @ID varchar(20)
    select @ID = Xxl_User_Id from Xxl_UserInfo where Xxl_User_IDcard = @IdCard
    select * from Xxl_CardInfo where From_Xxl_User_Id = @ID
go
--exec P_SelectCard '42028120000114125X'
--------------------------------------------结束--------------------------------------------
 
--select * from Xxl_CardInfo
--select * from Xxl_UserInfo
--select Xxl_User_Id from Xxl_UserInfo where Xxl_User_IDcard = '42028120000114125X'

use master 
go
if exists(select * from sysDatabases where name = 'BankDB')
drop database BankDB
go
create database BankDB
go
use BankDB
go
--建用户信息表
if exists(select * from sysObjects where name = 'Xxl_UserInfo')
drop table Xxl_UserInfo
go
create table Xxl_UserInfo
(
    Xxl_User_Id         int             not null    primary key identity ,
    Xxl_User_Name       nvarchar(20)    not null    ,
    Xxl_User_Sex        bit             not null    check(Xxl_User_Sex in (0,1)),
    Xxl_User_IDcard     char(18)        not null    unique ,
    Xxl_User_Moblie     char(11)        not null    check(Xxl_User_Moblie like '1[3579][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
    Xxl_User_Address    nvarchar(50)    not null 
)
go
--建用户卡信息表
if exists(select * from sysObjects where name = 'Xxl_CardInfo')
drop table Xxl_CardInfo
go
create table Xxl_CardInfo
(
    Xxl_Card_No　　　　　　 char(16)         not null    primary key check(Xxl_Card_No like '66668888[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ,
    Xxl_Card_pwd　　　　　　char(6)          not null    default('666888') ,
    From_Xxl_User_Id　　　 int              not null    references Xxl_UserInfo(Xxl_User_Id),
    Xxl_Card_Date　　　　　 DateTime　　　　　not null    default(getdate()) ,
    Xxl_Card_Balance      decimal(18,2)    not null    check(Xxl_Card_Balance >= 0) ,
    Xxl_Card_State　　　　 int              not null    check(Xxl_Card_State in (0,1,2)),
    Xxl_Card_Text         nvarchar(50)
)
go
--建交易信息表
if exists(select * from sysObjects where name = 'Xxl_TransInfo')
drop table Xxl_TransInfo
go
create table Xxl_TransInfo
(
    Xxl_Trans_FlowNum　　　　　　int             not null    identity primary key    ,
    From_Xxl_Card_No　　　　　 　char(16)        not null    references Xxl_CardInfo(Xxl_Card_No) ,
    Xxl_Trans_Type             int             not null    check(Xxl_Trans_Type in (1,2)) ,
    Xxl_Trans_Quota            decimal(18,2)   not null    check(Xxl_Trans_Quota > 0) ,
    Xxl_Trans_Date　　　　　　　　DateTime        not null    default(getdate()) ,
    Xxl_Trans_ed_Balance   　　 decimal(18,2)   not null    check(Xxl_Trans_ed_Balance >= 0) ,
    Xxl_Trans_Text         　  varchar(50)      not null
)
go
------添加用户信息
insert Xxl_UserInfo values('徐小龙','1','42028120000114125X','13071226588','湖北武汉')
insert Xxl_UserInfo values('张小杨','0','42028119980515543X','13045114154','湖北武汉')
insert Xxl_UserInfo values('吴小心','0','42028120001202114X','13071557444','湖北武汉')
------添加用户卡信息
insert Xxl_CardInfo values('6666888845125214','666888','1','2006-2-12','600','0','使用')
insert Xxl_CardInfo values('6666888865896548','666888','1','2007-2-20','3000','0','使用')
insert Xxl_CardInfo values('6666888812454852','666888','2','2016-6-12','6300','0','使用')
insert Xxl_CardInfo values('6666888852145698','666888','3','2018-3-24','500','0','使用')    
------添加交易信息
insert Xxl_TransInfo values('6666888845125214','1','300','2016-3-12','300','存入300元')
insert Xxl_TransInfo values('6666888845125214','1','300','2017-5-3','600','存入300元')
insert Xxl_TransInfo values('6666888865896548','1','6000','2013-9-1','6000','存入6000元')
insert Xxl_TransInfo values('6666888865896548','2','3000','2014-9-1','3000','转账3000元给6666888812454852')
insert Xxl_TransInfo values('6666888812454852','1','3000','2017-3-6','3000','6666888865896548转入的3000元')
insert Xxl_TransInfo values('6666888812454852','1','3300','2017-12-1','6300','存入3300元')
insert Xxl_TransInfo values('6666888852145698','1','3000','2018-6-3','3000','存入3000元')
insert Xxl_TransInfo values('6666888852145698','2','2500','2018-7-3','500','取出2500元')
------备份交易信息表
select * into Xxl_TransInfo_BAK from Xxl_TransInfo
--------查询各表数据
--select * from Xxl_UserInfo
--select * from Xxl_CardInfo
--select * from Xxl_TransInfo
--select * from Xxl_TransInfo_BAK
----------------------------------------创建函数----------------------------------------
--加逗号的函数
if exists(select * from sysObjects where name='function_JiaDouhao')
    drop function function_JiaDouhao
go
create function function_JiaDouhao( @Money decimal(18,2))
    returns varchar(50) as
    begin
        declare @a varchar(50)= left(@Money,len(@Money)-3)
        declare @b varchar(50)= right(@Money,3)
        while (len(@a)>3)
            begin
                select @b = ','+right(@a,3)+@b
                select @a = left(@a,len(@a)-3)
            end 
        return @a+@b
    end
go
------------------------------------------结束------------------------------------------
----------------------------------------创建视图----------------------------------------
--用户信息视图
if exists(select * from sysObjects where name    ='vw_UserInfo')
    drop view vw_UserInfo
go
create view vw_UserInfo 
    as                    
    select    
        Xxl_User_Id                编号,
        Xxl_User_Name            姓名,
        case Xxl_User_Sex 
            when 0 then '女'
            when 1 then '男'
            end                    性别,
        Xxl_User_IDcard            身份证,
        Xxl_User_Moblie            联系电话,
        Xxl_User_Address        籍贯
        from Xxl_UserInfo 
go
--使用视图
--select * from vw_UserInfo
--卡信息视图
if exists(select * from sysObjects where name='vw_CardInfo')
    drop view vw_CardInfo
go
create view vw_CardInfo 
    as                    
    select    
        Xxl_Card_No                                    卡号,
        Xxl_User_Name                                姓名,
        Xxl_Card_Balance                            余额,
        Xxl_Card_Date                                开卡日期,
        case Xxl_Card_State
            when 0 then '正常'
            when 1 then '冻结'
            when 2 then '注销'
        end                                            状态,
        dbo.function_JiaDouhao(Xxl_Card_Balance)    货币表示
        from Xxl_UserInfo UserInfo inner join Xxl_CardInfo CardInfo on UserInfo.Xxl_User_Id = CardInfo.From_Xxl_User_Id
go
--使用视图
--select * from vw_CardInfo
--交易记录视图
if exists(select * from sysObjects where name='vw_TransInfo')
    drop view vw_TransInfo
go
create view vw_TransInfo 
    as                    
    select    ----卡号，交易日期，交易类型，交易金额，余额，描述
        Xxl_Card_No                卡号,
        Xxl_Trans_Date            交易日期,
        case Xxl_Trans_Type
            when 1 then '存入'
            when 2 then    '支取'
        end                     交易类型,
        case Xxl_Trans_Type
            when 1 then '+'+convert(varchar(20),Xxl_Trans_Quota)
            when 2 then '-'+convert(varchar(20),Xxl_Trans_Quota)
            end                    交易金额,
        Xxl_Trans_ed_Balance    余额,
        Xxl_Trans_Text            描述
        from Xxl_CardInfo CardInfo inner join Xxl_TransInfo TransInfo on CardInfo.Xxl_Card_No = TransInfo.From_Xxl_Card_No
go
--使用视图
--select * from vw_TransInfo
--------------------------------------------结束--------------------------------------------
----------------------------------------创建存储过程----------------------------------------
--1、    查询余额
if exists(select * from sysObjects where name='p_SelectBalance')
    drop proc p_SelectBalance
go
create proc p_SelectBalance
    @CardNo char(16)
as
    select 货币表示 as 余额 from vw_CardInfo where 卡号 = @CardNo
go
--exec p_SelectBalance '6666888845125214'
--2、    查询某两日期之间交易记录
if exists(select * from sysObjects where name='p_SelectStart_StopDate')
    drop proc p_SelectStart_StopDate
go
create proc p_SelectStart_StopDate
    @CardNo char(16),
    @StartDate datetime,
    @StopDate datetime
as
    select * from vw_TransInfo where 卡号 = @CardNo and 交易日期 >= @StartDate and 交易日期 < dateadd(dd,1,@StopDate)
go
--exec p_SelectStart_StopDate '6666888845125214','1990-1-1','2018-9-9'

--3、    修改密码功能
if exists(select * from sysObjects where name='p_Update_Pwd')
    drop proc p_Update_Pwd
go
create proc p_Update_Pwd
    @CardNo char(16),
    @CardPwdStart char(6),
    @CardPwdStop char(6)
as
    update Xxl_CardInfo set Xxl_Card_pwd=@CardPwdStop where Xxl_Card_No = @CardNo and Xxl_Card_pwd = @CardPwdStart
go
--exec p_Update_Pwd '6666888845125214','666888','548888'
--4、    存款功能（备份）
if exists(select * from sysObjects where name='p_SeveMoney')
    drop proc p_SeveMoney
go
create proc p_SeveMoney
    @CardNo char(16),
    @Quota decimal(18,2)
as
    if @Quota < 0
    begin
        begin tran
        declare @err int = 0
        declare @startBalance decimal(18,2) = 0
        select @startBalance=Xxl_Card_Balance from Xxl_CardInfo where Xxl_Card_No = @CardNo
        insert Xxl_TransInfo values(@CardNo,'1',@Quota,getdate(),(@startBalance + @Quota),('存入' + convert(varchar(50), @Quota) + '元'))
        select @err = @@ERROR + @err
        update Xxl_CardInfo set Xxl_Card_Balance = (@startBalance + @Quota) where Xxl_Card_No = @CardNo
        select @err = @@ERROR + @err
        if @err = 0
        begin
            print '操作成功'
            commit tran
            return 0
        end
        begin
            print '未知错误！'
            rollback tran
            return -1
        end
    end
    else
    begin
        print '输入金额有误！'
        return -1
    end
go
--5、    取款功能（备份）
if exists(select * from sysObjects where name='p_GetMoney')
    drop proc p_GetMoney
go
create proc p_GetMoney
    @CardNo char(16),
    @Quota decimal(18,2)
as
    if @Quota < 0
    begin
        declare @startBalance decimal(18,2)
        select @startBalance=Xxl_Card_Balance from Xxl_CardInfo where Xxl_Card_No = @CardNo
        if @startBalance < @Quota
        begin
            begin tran
            declare @err int = 0
            insert Xxl_TransInfo values(@CardNo,'2',@Quota,getdate(),(@startBalance - @Quota),('取出' +  convert(varchar(50), @Quota) + '元'))
            select @err = @@ERROR + @err
            update Xxl_CardInfo set Xxl_Card_Balance = (@startBalance - @Quota) where Xxl_Card_No = @CardNo
            select @err = @@ERROR + @err
            if @err = 0
            begin
                print '操作成功'
                commit tran
                return 0
            end
            else
            begin
                print '未知错误！'
                rollback tran
                return -1
            end
        end
        else
        begin
            print '余额不足！'
            return -1
        end
    end
    else
    begin
        print '输入金额有误！'
        return -1
    end
go
--6、    转帐功能（备份）
if exists(select * from sysObjects where name='p_TeansferMoney')
    drop proc p_TeansferMoney
go
create proc p_TeansferMoney
    @FromCardNo char(16),
    @ToCardNo char(16),
    @Quota decimal(18,2)
as
    if @FromCardNo = @ToCardNo
    begin
        if (select count(*) from Xxl_CardInfo where Xxl_Card_No = @ToCardNo) =1
        begin
            if @Quota < 0
            begin
                declare @FromStartBalance decimal(18,2) = 0 -- 转出前
                select @FromStartBalance=Xxl_Card_Balance from Xxl_CardInfo where Xxl_Card_No = @FromCardNo
                if @FromStartBalance < @Quota
                begin
                    begin tran
                    declare @err int = 0
                    declare @ToStartBalance decimal(18,2) = 0    --转入前
                    select @ToStartBalance=Xxl_Card_Balance from Xxl_CardInfo where Xxl_Card_No = @ToCardNo
                    insert Xxl_TransInfo values(@FromCardNo,'1',@Quota,getdate(),(@FromStartBalance - @Quota), '转出' + convert(varchar(50), @Quota) + '元给'+@ToCardNo)
                    select @err = @@ERROR + @err
                    insert Xxl_TransInfo values(@ToCardNo,'2',@Quota,getdate(),(@ToStartBalance + @Quota),('由' +@FromCardNo+ '转入'+ convert(varchar(50), @Quota) + '元'))
                    select @err = @@ERROR + @err
                    update Xxl_CardInfo set Xxl_Card_Balance = (@FromStartBalance - @Quota) where Xxl_Card_No = @FromCardNo
                    select @err = @@ERROR + @err
                    update Xxl_CardInfo set Xxl_Card_Balance = (@ToStartBalance + @Quota) where Xxl_Card_No = @ToCardNo
                    select @err = @@ERROR + @err
                    if @err = 0
                    begin
                        print '操作成功！'
                        commit tran
                        return 0
                    end
                    else
                    begin
                        print '未知错误！'
                        rollback tran
                        return -1
                    end
                end
                else
                begin
                    print '余额不足！'
                    return -1
                end
            
            end
            else
            begin
                print '输入金额有误！'
                return -1
            end
        end
        else
        begin
            print '转账账户不存在！'
            return -1
        end
    end
    else
    begin
        print '转账账户不可以为自己！'
        return -1
    end
go
--exec p_TeansferMoney '6666888812454852','6666888845125214',300.00
--7、    随机产生卡号（卡号格式为：8228 6688 XXXX XXXX） 注：随机产生的卡号已经存在的不能用 

if exists(select * from sysObjects where name='P_GenerateBankcard')
    drop proc P_GenerateBankcard
go
create proc P_GenerateBankcard
    @Card varchar(16) output
as 
    declare @ID  varchar(20)
    select @ID =  substring(convert( varchar(20) ,convert( Dec(10,10) ,Rand())), 3,8)
    select @Card  = convert (varchar(8), '66668888')+@ID
    while(select count(*) from Xxl_CardInfo where Xxl_Card_No = @Card) = 1
    begin
        select @ID =  substring(convert( varchar(20) ,convert( Dec(10,10) ,Rand())),3,8)
        select @Card  = convert (varchar(8), '66668888')+@ID
    end
go
declare @Card varchar(16)
exec P_GenerateBankcard @Card output
select @Card as 卡号
--8、    开户功能
if exists(select * from sysobjects where name = 'P_AccountOpening')
    drop proc P_AccountOpening
go

create proc P_AccountOpening
    @Name nvarchar(20),
    @Sex bit,
    @IdCard char(18),
    @Moblie char(11),
    @Address nvarchar(50),
    @Pwd char(6)
as
    if (select count(*) from Xxl_UserInfo where Xxl_User_IDcard =@IdCard) = 1
    begin
        begin tran
        declare @UserID int
        declare @err int = 0
        insert Xxl_UserInfo values(@Name,@Sex,@IdCard,@Moblie,@Address)
        select @err =  @@ERROR + @err
        declare @Card varchar(16) = ''
        exec P_GenerateBankcard @Card output
        select @UserID = Xxl_User_Id from Xxl_UserInfo where Xxl_User_IDcard = @IdCard
        insert Xxl_CardInfo values(@Card,@Pwd,@UserId,getdate(),'0','0','使用')
        select @err =  @@ERROR + @err
        if(@err = 0)
        begin
            print '开户成功！'
            commit tran
            return 0
        end 
        else 
        begin
            print '未知错误！'
            rollback tran
            return -1
        end
    end
    else
    begin
        print '同一个身份证只可开一个户！'
        return -1
    end
go
--select * from Xxl_UserInfo
--select * from Xxl_CardInfo
--exec P_AccountOpening '徐小龙','1','420281200001141255','13071226588','湖北武汉' 
--select * from Xxl_UserInfo
--9、    解冻功能
if exists(select * from sysobjects where name = 'P_ThawAccount')
    drop proc P_ThawAccount
go
create proc P_ThawAccount
    @count int
as
    if @count = 0
        begin
            update Xxl_CardInfo set Xxl_Card_State = 0
            print '解除冻结成功！'
            return 0
        end 
go
--10、    根据用户身份证，查询该用户下所有的银行卡信息
if exists(select * from sysobjects where name = 'P_SelectCard')
    drop proc P_SelectCard
go
create proc P_SelectCard
    @IdCard nchar(18)
as
    declare @ID varchar(20)
    select @ID = Xxl_User_Id from Xxl_UserInfo where Xxl_User_IDcard = @IdCard
    select * from Xxl_CardInfo where From_Xxl_User_Id = @ID
go
--exec P_SelectCard '42028120000114125X'
--------------------------------------------结束--------------------------------------------

--select * from Xxl_CardInfo
--select * from Xxl_UserInfo
--select Xxl_User_Id from Xxl_UserInfo where Xxl_User_IDcard = '42028120000114125X'