ATM-简单SQL查询

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'