欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

ATM-简单SQL查询

程序员文章站 2023-11-14 08:43:04
use master go if exists(select * from sysDatabases where name = 'BankDB') drop database BankDB go create database BankDB go use BankDB go --建用户信息表 if ... ......

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'