精华内容
下载资源
问答
  • 银行ATM存取款机系统

    2012-12-17 14:31:41
    网上找的觉得挺有用的银行ATM存取款机系统 print '开始转账,请稍后......' print '交易正在进行,请稍后......' update Cardinfo set balance=balance-@money where customerID=(select customerID from UserInfo...
  • 银行ATM存取款机系统主要功能分别有开户,开户又分为:填写开户申请单自动开通账户 存取款,查询余额,转账。这个系统根据银行要求,实现各项银行日常存款,取款和转账业务.
  • 第六章 银行ATM存取款机系统.pptx
  • 酒店管理信息系统银行ATM存取款机系统的数据库设计
  • 银行业务处理 每个用户可以开设多个银行卡账户 每个银行卡账户可以有多笔交易 设计符合第三范式的数据库结构 根据银行业务绘制E-R图 绘制数据库模型图 使用三大范式规范数据库结构 实现“ATM取款机系统”的相关业务
  • 北大青鸟S2优化MySchool数据库设计项目案例ATM机系统
  • 项目名称:银行ATM存取款机系统设计与实现 一、创建数据库............................................................................................................. 3 1.1创建数据库......................

     项目名称:银行ATM存取款机系统设计与实现  

    一、创建数据库............................................................................................................. 3

    1.1创建数据库....................................................................................................... 3

    1.2创建各个数据表及相关的约束....................................................................... 3

    1.3添加外键约束和生成数据库关系图............................................................... 6

    二、创建触发器和插入测试数据................................................................................. 6

    2.1创建级联触发器.............................................................................................. 6

    2.2插入数据表的测试数据.................................................................................. 9

    三、模拟常规业务....................................................................................................... 12

    3.1修改客户密码................................................................................................. 12

    3.2办理银行卡挂失............................................................................................ 12

    3.3统计银行资金流通余额和盈利结算............................................................ 13

    3.4查询本周开户信息........................................................................................ 14

    3.5查询本月单次交易金额最高的卡号和总交易金额最高的卡号................ 14

    3.6查询挂失客户................................................................................................ 15

    3.7催款提醒业务................................................................................................ 15

    四、创建、使用视图................................................................................................... 16

    4.1输出银行客户记录视图VW_userInfo......................................................... 16

    4.2输出银行卡记录视图VW_CardInfo............................................................. 16

    4.3输出银行卡交易记录视图VW_TransInfo................................................... 17

    4.4根据客户登录名查询该客户账户信息VW_OneUserInfo........................... 18

    五、存储过程实现业务处理....................................................................................... 18

    5.1完成存款或取款业务.................................................................................... 18

    5.2产生随机卡号................................................................................................ 21

    5.3完成开户业务................................................................................................ 22

    5.4分页显示查询交易数据................................................................................ 24

    5.5打印客户对账单............................................................................................ 25

    5.6统计未发生交易的账户................................................................................ 27

    5.7统计银行卡交易量和交易额........................................................................ 30

    六、利用事务实现转账............................................................................................... 34

    心得体会:................................................................................................................... 39

    源代码:....................................................................................................................... 40

    创建数据库

    -- 创建数据库BankDB

    -- 数据库文件和日志均保存在 D:\20160107SQL项目练习\银行ATM机器系统\

    -- 文件大小为5MB增长15% 日志大小为3MB增长5MB

    use master

    if exists(select * from sysdatabases where name='BankDB')

        drop database BankDB

    go

    create database BankDB

    on

    (

        name='BankDB_Data',

        filename='D:\20160107SQL\项目练习\银行ATM机器系统\BankDB01_Data.mdf',

        size=5mb,

        filegrowth=15%

    )

    log on

    (

        name='BankDB_Log',

        filename='D:\20160107SQL\项目练习\银行ATM机器系统\BankDB01_Log.ldf',

        size=3mb,

        filegrowth=5mb

    )

    go

    创建各个数据表及相关的约束

    --创建银行业务类型表BankBusinessType

    --判断银行业务类型表是否存在,若存在则删除

    --创建银行业务类型表,包含银行业务类型编号BBTId主键 自动标识符,银行业务类型名称BBTName char(20) not null,银行业务描述BBTComment varchar(100)

    use BankDB

    if exists(select * from sysobjects where name='BankBusinessType')

        drop table BankBusinessType

    go

    create table BankBusinessType

    (

        BBTId int identity(1,1) primary key,

        BBTName char(20) not null,

        BBTComment varchar(100)

    )

    go

    --创建银行客户信息表BankCustomer

    --判断银行卡客户是否存在,若存在则删除

    --创建银行客户信息表,包含客户编号BCID 主键 自动标识符,客户姓名BCName char(20) not null,客户身份证BCICNo,客户联系电话BCTel、

    客户居住地址BCAddr varchar(100)

        --定义身份证号前位必须是数字,后位可以是数字或者X

       --定义联系方式,必须是固定电话号码或者手机号,固定电话号码前位必须是区号,手机号码前面第一位必须是1,第二位必须是[3,5,8,9]

    if exists(select * from sysobjects where name='BankCustomer')

        drop table BankCustomer

    go

    create table BankCustomer

    (

        --客户编号BCID 主键 自动标识符,

        BCID int identity(1,1) primary key,

        --客户姓名BCName char(20) not null

        BCName char(20) not null,

        --客户身份证BCICNo

        BCICNo char(18) not null,

        --客户联系电话BCTel

        BCTel char(12) not null,

        --客户居住地址BCAddr varchar(100)

        BCAddr varchar(100)

    )

    go

    --定义身份证号前位必须是数字,后位可以是数字或者X

    alter table BankCustomer add constraint CK_BCICNo check(BCICNo like

    replicate('[0-9]',17)+replicate('[0-9xX]',1))

    --定义联系方式,必须是固定电话号码或者手机号,固定电话号码前位必须是区号,

    --手机号码前面第一位必须是1,第二位必须是[3,5,8,9]

    alter table BankCustomer add constraint CK_BCTel check(BCTel like

    '1'+replicate('[3589]',1)+replicate('[0-9]',9) or BCTel

    like replicate('[0-9]',3)+'-'+replicate('[0-9]',8) or

    BCTel like replicate('[0-9]',4)+'-'+replicate('[0-9]',7))

    go

    --建立银行卡信息 BankCard

    --判断银行卡是否存在,若存在,则删除银行卡BankCard

    -- 卡号 BCNo char(19) 主键 必须符合16位数字构成,前8位为1010 3576,后位是随机产生且唯一,每4位必须有一个空格

    -- 密码  BCPwd char(6) not null ,开户默认为'888888'

    -- 币种  BCCurrency char(5) not null, 默认为RMB类型

    -- 存款类型 BCBBTId int not null

    -- 开户日期 BCOpenDate datetime not null,默认当日

    -- 开户金额 BCOpenAmount money  not null ,不得小于1

    -- 是否挂失 BCRegLoss char(2),默认为否

    -- 客户编号 BCBCId intnot null,

    -- 当前余额 BCExistBalance money not null

    if exists(select * from sysobjects where name='BankCard')

        drop table BankCard

    go

    create table BankCard

    (

        -- 卡号 BCNo char(19) 主键

        BCNo char(19) primary key,

        -- 密码  BCPwd char(6) not null ,开户默认为'888888'

        BCPwd char(6) not null default('888888'),

        -- 币种  BCCurrency char(5) not null, 默认为RMB类型

        BCCurrency char(5) not null  default('RMB'),

        -- 存款类型 BCBBTId int not null

        BCBBTId int not null,

        -- 开户日期 BCOpenDate datetime notnull,默认当日

        BCOpenDate date not null default(convert(varchar(10),getdate(),120)),

        -- 开户金额 BCOpenAmount money  not null ,不得小于1元

        BCOpenAmountmoney not null check(BCOpenAmount>=1),

        -- 是否挂失 BCRegLoss char(2),默认为否

        BCRegLoss char(2) default('否') check(BCRegLoss='是' or BCRegLoss='否'),

        -- 客户编号 BCBCId int not null,

        BCBCId int not null,

        -- 当前余额 BCExistBalance moneynot null

        BCExistBalancemoney not null

    )

    go

    --卡号必须符合16位数字构成,前8位为1010 3576,

    --后位是随机产生且唯一,每4位必须有一个空格

    alter table BankCard add constraint CK_BCNo check(BCNo like'1010 3576 '+

    replicate('[0-9]',4)+' '+replicate('[0-9]',4))

    go

    --创建交易信息表BankDealInfo

     --判断交易信息BankDealInfo是否存在,若存在则删除

        --交易编号BDNo  为自动增长列 主键

        --卡号 BDBCNo char(19) not null

        --交易日期 BDDealDate Datenot null 默认为当前日期

        --交易金额 BDDealAcount money not null

        --交易类型,有种存入和支取 BDDealType Char(10) not null

        --交易备注 BDDealComment varchar(100)

    if exists(select * from sysobjects where name='BankDealInfo')

        drop table BankDealInfo

    go

    create table BankDealInfo

    (

        --交易编号BDNo  为自动增长列 主键

        BDNo int identity(1,1) primary key,

        --卡号 BDBCNo char(19) not null

        BDBCNo char(19) not null,

        --交易日期 BDDealDate Date notnull 默认为当前日期

        BDDealDate Date not null default(convert(varchar(10),getdate(),120)),

        --交易金额 BDDealAcount money notnull

        BDDealAcountmoney not null,

        --交易类型,有种存入和支取 BDDealTypeChar(10) not null

        BDDealType Char(10) not null check(BDDealType like'存入'

        or BDDealType like'支取'),

        --交易备注 BDDealCommentvarchar(100)

        BDDealCommentvarchar(100)

    )

    Go

     添加外键约束和生成数据库关系图

    --建立表之间的外键约束关系

    alter table BankCard add constraint FK_BCBBTIdforeign key(BCBBTId)

    references BankBusinessType(BBTId)

    alter table BankCard add constraint FK_BCBCId foreign key(BCBCId)

    references BankCustomer(BCID)

    alter table BankDealInfo add constraint FK_BDBCNo foreign key(BDBCNo)

    references BankCard(BCNo)

    go

     创建级联触发器

       2.1.1创建Insert触发器

    --在交易信息表中插入一个触发器tr_InsertdealInfo,修改银行卡的存款余额

    --判断交易记录里是存入还是支取,及时更新银行卡表的存款余额

    if exists(select * from sysobjects where name='tr_InsertdealInfo')

        drop trigger tr_InsertdealInfo 

    go

    create trigger tr_InsertdealInfo on BankDealInfo

    for insert

    as

        declare @BDDealType Char(10),@BDDealAcount money,@BDBCNo char(19)

        select @BDDealType=BDDealType,@BDDealAcount=BDDealAcount,@BDBCNo

        =BDBCNo from inserted

        if(@BDDealType='存入')

           updateBankCard set BCExistBalance+=@BDDealAcount where BCNo=@BDBCNo

        else

        begin

           if(convert(float,(select BCExistBalance from BankCard where

           BCNo=@BDBCNo))<@BDDealAcount)

           begin

               raiserror('本卡的当前余额不足',18,100)   

               rollbacktran

           end

           else

               updateBankCard set BCExistBalance-=@BDDealAcount where BCNo=@BDBCNo

        end

    go

     2.1.2创建Delete触发器

    --在交易信息表中插入一个触发器tr_DeldealInfo,当删除一个交易信息,修改银行卡的存款余额

    if exists(select * from sysobjects where name='tr_DeldealInfo')

        drop trigger tr_DeldealInfo

    go

    create trigger tr_DeldealInfo on BankDealInfo

    instead of delete

    as

        declare @BDDealType Char(10),@BDDealAcount money,@BDBCNo char(19)

        select @BDDealType=BDDealType,@BDDealAcount=BDDealAcount,@BDBCNo

        =BDBCNo from deleted

        if(@BDDealType='存入')

        begin

           if(convert(float,(select BCExistBalance from BankCard where

           BCNo=@BDBCNo))<@BDDealAcount)

           begin

               raiserror('本卡的当前余额不足',18,100)   

               rollbacktran

           end

           else

               updateBankCard set BCExistBalance-=@BDDealAcount where BCNo=@BDBCNo

        end

        else

           updateBankCard set BCExistBalance+=@BDDealAcount where BCNo=@BDBCNo

    go

     2.1.3创建Update触发器

    --在交易信息表中插入一个触发器,修改银行卡的存款余额

    --判断交易记录里是存入还是支取,及时更新银行卡表的存款余额

    if exists(select * from sysobjects where name='tr_updatedealInfo')

        drop trigger tr_updatedealInfo 

    go

    create trigger tr_updatedealInfo on BankDealInfo

    for update

    as

        declare @oldDealType Char(10),@newDealType Char(10),@oldDealAcount

        money,@newDealAcount money,@BDBCNo char(19)

        select @BDBCNo=BDBCNo,@oldDealType=BDDealType,@oldDealAcount=

        BDDealAcountfrom deleted

        select @newDealType=BDDealType,@newDealAcount=BDDealAcount from inserted

        if(@oldDealType=@newDealAcount)

        begin

           if(@oldDealType='存入')

               updateBankCard set BCExistBalance+=@newDealAcount-@oldDealAcount

               whereBCNo=@BDBCNo

           else

           begin

               if(convert(float,(select BCExistBalance from BankCard where

               BCNo=@BDBCNo))<@newDealAcount-@oldDealAcount)

               begin

                  raiserror('本卡的当前余额不足',18,100)   

                  rollbacktran

               end

               else

                  updateBankCard set BCExistBalance-=@newDealAcount-@oldDealAcount

                  whereBCNo=@BDBCNo

           end

        end

        else

        begin

           if(@oldDealType='存入')

           begin

               if(convert(float,(select BCExistBalance from BankCard where

               BCNo=@BDBCNo))<@newDealAcount+@oldDealAcount)

               begin

                  raiserror('本卡的当前余额不足',18,100)   

                  rollbacktran

               end

               else

                  updateBankCard set BCExistBalance-=@newDealAcount+@oldDealAcount

                  whereBCNo=@BDBCNo

           end

           else

           begin        

               updateBankCard set BCExistBalance+=@newDealAcount-@oldDealAcount

               whereBCNo=@BDBCNo

           end

        end

    go

    插入数据表的测试数据

    --插入表的测试数据

    insert into BankBusinessType

    values('活期','无固定存储期,可随时存款,存款金额不限的一种比较灵活的存款'),

    ('定活俩便','事先不约定存期,一次性存入,一次性取出的存款'),

    ('通知','不约定存期,支取时需要提前通知银行,约定支取时间和金额方能支取的存款'),

    ('整存整取1年','整笔存入,到期提取本息'),('整存整取2年','整笔存入,到期提取本息'),

    ('整存整取3年','整笔存入,到期提取本息'),('零存整取1年',

    '事先约定金额,逐月按约定金额存入,到期支付本息'),('零存整取2年',

    '事先约定金额,逐月按约定金额存入,到期支付本息'),('零存整取3年',

    '事先约定金额,逐月按约定金额存入,到期支付本息'),('自助转账',

    '银行ATM存取款机上办理银行卡之间的相互转账')

    select * from BankBusinessType

    go

     

    insert into BankCustomer values('张飞','150203197611154224','13088447030',

    '包头市昆区宝钢五中'),('关羽','15020319761005123X','0472-2315490',

    '包头昆区阿尔丁大街')

    select * from BankCustomer

    go

     

    insert into BankCard values('1010 3576 1234 5678','197611',default,1,

    '2018-05-28',1000,default,1,1000),('1010 3576 1234 5688','197711',

    default,2,'2018-05-28',1500,default,2,1500)

    select * from BankCard

    go

     

    insert into BankDealInfo values('1010 3576 1234 5678','2018-05-29',500,

    '存入','单位1月工资')

    insert into BankDealInfo values('1010 3576 1234 5678','2018-05-29',1500,'存入',

    '单位2月工资')

    insert into BankDealInfo values('1010 3576 1234 5678','2018-05-29',600,'支取',

    '支付宝付款')

    insert into BankDealInfo values('1010 3576 1234 5678','2018-05-29',700,'支取',

    '刷卡消费')

    insert into BankDealInfo values('1010 3576 1234 5688','2018-05-29',3000,'存入',

    '单位1月工资')

    insert into BankDealInfo values('1010 3576 1234 5688','2018-05-29',2800,'存入',

    '单位2月工资')

    insert into BankDealInfo values('1010 3576 1234 5688','2018-05-29',1600,'支取',

    '支付宝付款')

    insert into BankDealInfo values('1010 3576 1234 5688','2018-05-29',900,'支取',

    '刷卡消费')

    select * from BankDealInfo

    select * from BankCard

    go

     修改客户密码

    --修改客户密码

    --根据卡号修改指定2个客户的银行密码,其中第一个客户1010 3576 1234 5678密码修改为123456,第二个客户1010 3576 1234 5688修改为123123.

    update BankCardset BCPwd='123456' where BCNo='1010 3576 1234 5678'

    update BankCardset BCPwd='123123' where BCNo='1010 3576 1234 5688'

    go

     

    -- 按照以下字段进行显示 '卡号', '密码', '货币类型', '存储类型','开户日期', '开户金额','是否挂失', '客户编号','存款金额'

    select BCNo as 卡号,BCPwd as 密码,BCCurrency as 货币类型,BCBBTId as

    存储类型,BCOpenDate as 开户日期,BCOpenAmount as 开户金额,BCRegLoss

    as 是否挂失,BCBCId as 客户编号,BCExistBalance as 存款金额 from BankCard

    go

    办理银行卡挂失

    --办理银行卡挂失

    --卡号为1010 3576 1234 5678的银行卡丢失,申请挂失,要求使用innerjoin语句显示如下图运行结果:

    update BankCardset BCRegLoss='是' where BCNo='1010 3576 1234 5688'

    select BCNo as 卡号,BCPwd as 密码,BCCurrency as 货币类型,BBTName as

    存储类型,BCOpenDate as 开户日期,BCOpenAmount as 开户金额,BCRegLoss

    as 是否挂失,BCBCId as 客户编号,BCExistBalance as 存款金额 from BankCard

    inner join BankBusinessType on BCBBTId=BBTId

    go

    统计银行资金流通余额和盈利结算

    -- 使用存储过程 proc_BanlanceAndProfit,

    -- 统计银行资金流通余额和盈利结算

     -- 1.获取总存入金额和总支取金额

    盈利余额=总存入金额*0.008-总支取金额*0.003

    if exists(select * from sysobjects where name='proc_BanlanceAndProfit')

        drop procedure proc_BanlanceAndProfit

    go

    create procedure proc_BanlanceAndProfit

    as

        declare @inMoney money=0,@outMoney money=0,@i int=1,@BDDealType Char(10),

        @BDDealAcountmoney

        while(@i<=convert(int,(select max(BDNo) from BankDealInfo)))

        begin

           select @BDDealType=BDDealType,@BDDealAcount=BDDealAcount from

           BankDealInfowhere BDNo=@i

           if(@BDDealType='存入')

               set @inMoney+=@BDDealAcount

           else if(@BDDealType='支取')

               set @outMoney+=@BDDealAcount

           set @i+=1

        end

        print '存入总金额:'+ltrim(str(@inMoney))+'RMB,支取总金额:'+

        ltrim(str(@outMoney))+'RMB'

        print '盈利余额为:'+ltrim(str(@inMoney*0.008-@outMoney*0.003))

    go

    exec proc_BanlanceAndProfit

    go

    查询本周开户信息

    --查询本周开户信息

    select BCNo as 卡号,BCPwd as 密码,BCCurrency as 货币类型,BBTName as

    存储类型,BCOpenDate as 开户日期,BCOpenAmount as 开户金额,BCName as

    客户姓名,BCExistBalance as 存款金额,BCRegLoss as 是否挂失,(case when

    BCRegLoss='否' then '正常账户' else '挂失账户' end) as 账户状态 from

    BankCard,BankCustomer,BankBusinessType where BCBBTId=BBTId and BCID=BCBCId

    and datediff(ww,BCOpenDate,getdate())=0

    go

    查询本月单次交易金额最高的卡号和总交易金额最高的卡号

    代码实现:

    --查询

    --1.本月单次交易金额最高的卡号

    select top 1 BDBCNo as 卡号,BCOpenDate as 开户日期,BCOpenAmount as 开户金额

    from BankCard,BankDealInfo,BankCustomer where BDBCNo=BCNo and BCID=BCBCId

    and datediff(mm,BCOpenDate,getdate())=0 order by BDDealAcount desc

    go

     

    --2. 总交易金额最高的卡号

    select top 1 BDBCNo as 卡号,BCOpenDate as 开户日期,BCOpenAmount as 开户金额

    from BankCard,BankDealInfo,BankCustomer where BDBCNo=BCNo and BCID=BCBCId

    group by BDBCNo,BCOpenDate,BCOpenAmount order by sum(BDDealAcount) desc

    go

     查询挂失客户

    --查询挂失客户

    --查询挂失账号的客户信息,分别利用子查询in的方式或者内部连接inner join,查询结果如下图所示:

    select BCName as 客户姓名,BCTel as 客户电话 from BankCustomer where

    BCID in(select BCBCId from BankCard where BCRegLoss='是')

    go

     催款提醒业务

    --催款提醒业务

    --根据某种业务(如代缴电话费、代缴手机费或房贷等)的需要,每个月末,查询出客户账户上余额少于2000元,由银行统一致电催款。

    select BCName as 客户姓名,BCTel as 客户电话,BCExistBalance as 客户余额

    from BankCustomer inner join BankCard on BCID=BCBCId where BCExistBalance<2000

    go

    输出银行客户记录视图VW_userInfo

    为向客户提供友好的用户界面,使用T-SQL语句创建下面几个视图,并使用这些视图输出各表信息。

     --输出银行客户记录视图VW_userInfo

     --显示的列名全为中文,要求先判断该视图是否存在,若存在,则先删除。结果如下图所示:

    if exists(select * from sysobjects where name='VW_userInfo')

        drop view VW_userInfo

    go

    create view VW_userInfo

    as

        select BCID as 客户编号,BCName as 开户名,BCICNo as 身份证号,BCTel

        as 电话号码,BCAddr as 居住地址 from BankCustomer

    go

    select * from VW_userInfo

    go

    输出银行卡记录视图VW_CardInfo

    --(2)输出银行卡记录视图VW_CardInfo

    if exists(select * from sysobjects where name='VW_CardInfo')

        drop view VW_CardInfo

    go

    create view VW_CardInfo

    as

        select BCNo as 卡号,BCName as 开户名,BCCurrency as 货币类型,BBTName as

        存储类型,BCOpenDate as 开户日期,BCExistBalance as 存款金额,BCPwd as 密码

        ,BCRegLoss as 是否挂失 from BankCard,BankBusinessType,BankCustomer

        where BBTId=BCBBTId and BCID=BCBCId

    go

    select * from VW_CardInfo

    go

    输出银行卡交易记录视图VW_TransInfo

    --输出银行卡交易记录视图VW_TransInfo

    --查询该视图,结果如下图所示:

    if exists(select * from sysobjects where name='VW_TransInfo')

        drop view VW_TransInfo

    go

    create view VW_TransInfo

    as

        select BDDealDate as 交易日期,BDDealType as 交易类型,BDBCNo as 交易卡号,

        BDDealAcountas 交易金额,BDDealComment as 备注 from BankDealInfo

    go

    select * from VW_TransInfo

    go

    根据客户登录名查询该客户账户信息VW_OneUserInfo

    --根据客户登录名查询该客户账户信息VW_OneUserInfo

    if exists(select * from sysobjects where name='VW_OneUserInfo')

        drop view VW_OneUserInfo

    go

    create view VW_OneUserInfo

    as

        select BCID as 客户编号,BCName as 开户名,BCICNo as 身份证号,BCTel

        as 电话号码,BCAddr as 居住地址 from BankCustomer where BCName=system_user

    go

    select * from VW_OneUserInfo

    go

    完成存款或取款业务

    描述:

    Ø  根据银行卡号和交易金额实现银行卡的存款和取款业务。

    Ø  每一笔存款,取款业务都要计入银行交易账户,并同时更新客户的存款余额。

    Ø  如果是取款业务,在记账之前,要完成下面两项数据的检查验证工作,如果检查不合格,那么中断取款业务,给出提示信息后退出。

    ²  检查客户输入的密码是否正确。

    ²  账户取款金额是否大于当前存款额加1。

    要求:

    Ø  取款或存款存储过程名为usp_takeMoney。

    Ø  编写一个存储过程完成存款和取款业务,并调用存储过程取钱或者存钱进行测试,测试数据是张飞的卡号支取100元(密码123456),关羽的卡号存入2100元。

    Ø   

    --创建存取款业务的存储过程proc_TakeMoney

    --完成存款或取款业务

    if exists(select * from sysobjects where name='proc_TakeMoney')

        drop procedure proc_TakeMoney

    go

    create procedure proc_TakeMoney @bcno char(19),@saleMoney money,@pwd char(6)=null

    as

        declare @existBanlance money,@errorCount int =0

        begin tran

        select @existBanlance=BCExistBalance from BankCard where BCNo=@bcno

        --不返回受影响的行数

        set nocount on

        print '交易前,卡号'+@bcno+',余额为:'+ltrim(str(@existBanlance))

        print '交易正进行,请稍后...'

        --如果输入参数@pwd为空,则为存款业务,否则为取款业务

        if (@pwd is not null)

        begin 

           --1. 办理取款业务

           --判断指定卡号和密码是否存在,若存在,则可以取款,否则不能办理取款业务

           --判断取款金额是否小于等于存款余额-1,若条件成立,则可以取款,否则不能取款

           if exists(select * from BankCard where BCNo=@bcno and BCPwd=@pwd)

           begin

               if(convert(money,(select BCExistBalance from BankCard where

               BCNo=@bcno and BCPwd=@pwd))-1<=@saleMoney)

                  raiserror('存款余额不足,不能办理取款业务!!',15,1)

               else

               begin

                  set@existBanlance-=@saleMoney

                  insertinto BankDealInfovalues(@bcno,default,@saleMoney,'支取','取款')

                  set@errorCount+=@@error

               end

           end

           else

               raiserror('不能办理取款业务!!',15,1)

        end              

        else

        begin

           --2. 办理存款业务

            --判断事务处理里是否有异常,若没有异常,则提交,若有异常,则回滚

           --判断该交易为何种类型业务,若是存款,则现有余额等于原有余额加上存款金额

           set @existBanlance+=@saleMoney

           insert into BankDealInfo values(@bcno,default,@saleMoney,'存入','存款')

           set @errorCount+=@@error

        end

        if @errorCount<>0

        begin

           rollback tran

           raiserror('支取失败!!',18,2)

        end

        else

           commit tran

        print '交易成功,交易金额为:'+ltrim(str(@saleMoney))

        print '卡号'+@bcno+',余额为:'+ltrim(str(@existBanlance))

    go

    --执行存款存储过程

    exec proc_TakeMoney'1010 3576 1234 5678',2100

    --执行取款存储过程

    exec proc_TakeMoney'1010 3576 1234 5678',100,'123456'

    select * from BankCard

    select * from BankDealInfo

    go

    产生随机卡号

    --使用存储过程 Proc_randCardID   产生随机卡号

    --随机生成后8位卡号  [rand() 随机数 ]

    -- 再把前面的8位补上 '1010 3576'

    -- 最后判断卡号是否存在,如果存在则重新生成卡号的过程

    if exists(select * from sysobjects where name='proc_randCardId')

        drop procedure proc_randCardId

    go

    create procedure proc_randCardId @myCardId1varchar(19) output

    as

        while(1=1)

        begin

           declare @num1 char(4),@num2 char(4)

           set @num1=convert(int,rand()*10000)

           set @num2=convert(int,rand()*10000)

           set @num1=replicate(0,4-len(@num1))+@num1

           set @num2=replicate(0,4-len(@num2))+@num2

           set @myCardId1='1010 3576 '+@num1+' '+@num2

           if(@myCardId1 not in(select BCNo from BankCard))

               break

        end

    go

    declare @myCardId1 varchar(19)

    exec proc_randCardId@myCardId1 output

    print '产生随机卡号为:'+@myCardId1

    go

    完成开户业务

    描述:

    Ø  利用存储过程为客户开设2个银行卡账户,开户时需要提供客户的信息有:开户名、身份证号、电话号码、开户金额、存款类型和地址。客户的信息见表所示:

    Ø  为成功开户的客户提供银行卡,且银行卡号唯一。

    要求:

    Ø  开户的存储过程名为usp_openAccount。

    Ø  使用下面的数据执行该存储过程,进行测试:调用此存储过程开户。

    --完成开户业务

    --创建开户存储过程usp_openAccount,输入参数分别是开户名、身份证号、

    --电话号码、开户金额、存款类型和地址

    if exists(select * from sysobjects where name='usp_openAccount')

        drop procedure usp_openAccount

    go

    create procedure usp_openAccount @BCNamechar(20),@BCICNo char(18),

    @BCTel char(12),@BCOpenAmount money,@BBTName char(20),@BCAddr varchar(100)

    as

        --先判断存款类型是否正确

        if exists(select BBTName from BankBusinessType whereBBTName=@BBTName)

        begin

           declare @errorCount int =0

           begin tran

           --插入一条客户信息记录            

           insert into BankCustomer values(@BCName,@BCICNo,@BCTel,@BCAddr)

           set @errorCount+=@@error

           --得到刚插入的客户信息的编号

           declare @BCID int,@BCNo char(19)

           select @BCID=max(BCID) from BankCustomer

           --插入一条新开银行卡记录

           exec proc_randCardId@BCNo output        

           insert into BankCard values(@BCNo,default,default,(select BBTId from

           BankBusinessTypewhere BBTName=@BBTName),default,@BCOpenAmount,default,

           @BCID,@BCOpenAmount)

           set @errorCount+=@@error

           --判断上述事务操作是否有异常

           --1.如果错误 '尊敬的客户,开户不成功,所有操作均撤销'

           if @errorCount<>0

           begin

               rollbacktran

               raiserror('尊敬的客户,开户不成功,所有操作均撤销!!',18,2)

           end

           -- 2. 如果成功 '尊敬的客户,开户成功,系统为你产生的

           --随机卡号是'+@BCNo

           else

           begin

               print'尊敬的客户,开户成功,系统为你产生的'

               print'随机卡号是'+@BCNo

               committran

           end       

        end

        else

           --错误显示 '尊敬的客户,未能成功开户,存款类型不正确,请重新输入'

           raiserror('尊敬的客户,未能成功开户,存款类型不正确,请重新输入',15,1)

    go

    --执行开户过程

    exec dbo.usp_openAccount'周公旦','150203197510074339','0472-2457890','1200.00',

    '定活俩便','内蒙古包头'

    exec dbo.usp_openAccount'姬昌','150203197610174339','0472-2457890','1300.00',

    '活期','内蒙古包头'

    exec dbo.usp_openAccount'白天磊','150207199301252316','18347261609','10000000',

    '通知','内蒙古包头'

    exec dbo.usp_openAccount'白天帅','150203197510074339','15102274286','1200.00',

    '定活俩便','天津'

    go

     

    --显示开户的客户信息和银行卡信息

    --'客户编号', '开户名', '身份证号', '电话号码', '居住地址',

    --'卡号', '客户', '货币种类', '存款类型', '余额', '密码', '是否挂失'

    select BCID as 客户编号,BCName as 开户名,BCICNo as 身份证号,BCTel

    as 电话号码,BCAddr as 居住地址 from BankCustomer

    go

     

    select BCNo as 卡号,BCName as 客户,BCCurrency as 货币类型,BBTName as

    存款类型,BCExistBalance as 余额,BCPwd as 密码,BCRegLoss as 是否挂失

    from BankCard,BankBusinessType,BankCustomer where BBTId=BCBBTId

    and BCID=BCBCId

    go

    分页显示查询交易数据

    --分页显示查询交易数据

    --根据指定的页数和每页的记录数分页显示交易数据。要求:

    -- 显示第2页 的5条信息

    if exists(select * from sysobjects where name='usp_PagingDisplay')

        drop procedure usp_PagingDisplay

    go

    create procedure usp_PagingDisplay @pageint,@info int

    as

        select top(@page*@info) * from BankDealInfo where BDNo not in(select

        top((@page-1)*@info) BDNo from BankDealInfo)

    go

    exec usp_PagingDisplay2,5

    go

    打印客户对账单

    为某个特定的银行卡号打印指定时间内发生交易的对账单。要求如下:

    Ø  存储过程名称是usp_CheckSheet。

    Ø  分别采用以下两种方式执行存储过程,结果如下图所示。

    ²  如果不指定交易时间段,那么打印指定卡号的所有交易记录,如测试命令:exec  usp_CheckSheet '1010 3576 1234 5688'

     

    ²  如果指定交易时间段,那么打印指定卡号在指定时间内发生的所有交易记录,如测试命令:

    --(5)打印客户对账单

    --创建客户对账单的存储过程usp_CheckSheet

    --判断客户对账单存储过程是否存在,若是存在,则删除

    if exists(select * from sysobjects where name='usp_CheckSheet')

        drop procedure usp_CheckSheet

    go

    create procedure usp_CheckSheet @BCNo char(19),@startTime date=null,

    @endTime date=null

    as

        declare @BCName char(20),@BCCurrency char(5),@BBTName char(20),

        @BCOpenDate date,@i int=0

        select @BCName=BCName,@BCCurrency=BCCurrency,@BBTName=BBTName,

        @BCOpenDate=BCOpenDate from BankCustomer,BankBusinessType,BankCard

        where BCID=BCBCId and BBTId=BCBBTId and BCNo=@BCNo

        print '卡号:'+@BCNo

        print '姓名:'+@BCName

        print '货币:'+@BCCurrency

        print '存款类型:'+@BBTName

        print '开户日期:'+convert(char,@BCOpenDate)

        print '交易日        '+'类型    '+'交易金额    '+'备注'

        --打印客户对账单

        while(@i<=convert(int,(select max(BDNo) from BankDealInfo)))

        begin

           declare @BDDealDate date,@BDDealType char(10),@BDDealAcount money,

           @BDDealCommentvarchar(100)

           if(@startTime is null)

               set @startTime=dateadd(dd,-convert(int,datename(dd,getdate()))+1,

               getdate())

           if(@endTime is null)

               set @endTime=getdate()

           select @BDDealDate=BDDealDate,@BDDealType=BDDealType,@BDDealAcount

           =BDDealAcount,@BDDealComment=BDDealComment from BankDealInfo where

           BDBCNo=@BCNo and datediff(dd,@startTime,BDDealDate)>=0 and datediff

           (dd,@endTime,BDDealDate)<=0 and BDNo=@i

           if(@BDDealDate is not null and @BDDealType is not null and @BDDealAcount

           is not null and @BDDealComment is not null)

               printrtrim(convert(char,@BDDealDate))+'    '+rtrim(ltrim(@BDDealType))

               +'    '+ltrim(convert(char,@BDDealAcount))+'      '+@BDDealComment

           set @BDDealDate=null

           set @BDDealType=null

           set @BDDealAcount=null

           set @BDDealComment=null

           set @i+=1

        end

    go

    --执行打印客户对账单

    exec usp_CheckSheet'1010 3576 1234 5688','2018-05-15','2018-06-15'

    exec usp_CheckSheet'1010 3576 1234 5678'

    go

    统计未发生交易的账户

    查询统计指定时间段内没有发生交易的账户信息。

    要求:

    存储过程名称是usp_getWithoutTrade。

    指定时间段

    如果没有指定起始日期,那么自本月1日开始进行统计,如果没有指定终止日期,那么截止到当日为止。

    要求采用游标技术打印未发生交易的客户信息,参考客户对账单的代码。

    该存储过程的执行结果如下图所示

    --统计未发生交易的账户

    if exists(select * from sysobjects where name='usp_getWithoutTrade')

        drop procedure usp_getWithoutTrade

    go

    create procedure usp_getWithoutTrade @startTimedate=null,@endTime date=null

    as

    begin

        if(@startTime is null)

           set @startTime=dateadd(dd,-convert(int,datename(dd,getdate()))+1,

           getdate())

        if(@endTime is null)

           set @endTime=getdate()

        declare cur_BankCustomer cursorforward_only forselect distinct

        BCID,BCName,BCICNo,BCTel,BCAddr from BankCustomer inner join

        BankCard on BCID=BCBCId where BCNo not in(select BDBCNo

        from BankDealInfo where datediff(dd,@endTime,BDDealDate)<=0

        and datediff(dd,@startTime,BDDealDate)>=0)

        open cur_BankCustomer

        declare @i int=0,@BCExistBalance money,@sumMoney money=0

        print '客户编号   '+'客户姓名   '+'身份证号         '+'    电话号码   '+'    地址'

        while(1=1)

           begin

           declare @BCID int,@BCName char(20),@BCICNo char(18),@BCTel char(12),

           @BCAddr varchar(20)

           fetch next from cur_BankCustomer into@BCID,@BCName,@BCICNo,@BCTel,@BCAddr

           if @@fetch_status<>0

               break

           print convert(char(11),@BCID)+rtrim(@BCName)+'    '+@BCICNo+'    '+@BCTel+

           '    '+@BCAddr

           set @i+=1

           select @BCExistBalance=BCExistBalance from BankCard where BCBCId=@BCID

           set @sumMoney+=@BCExistBalance

        end

        close cur_BankCustomer

        deallocate cur_BankCustomer

        print '统计为发生交易的人数:'+ltrim(str(@i))+'人,客户总余额为:'+

        ltrim(str(@sumMoney))+'元'

    end

    go 

    exec usp_getWithoutTrade'2018-05-28','2018-05-28'

    go

    统计银行卡交易量和交易额

    统计指定时间段内某地区客户在银行卡交易量和交易额,如果不指定地区,则查询所有客户的交易量和交易额。

    要求:

    Ø  存储过程名称是usp_getTradeInfo。

    Ø  指定时间段和客户所在区域

    如果没有指定起始日期,那么自当年1月1日开始统计,如果没有指定终止日期,那么以当日作为截止日。

    如果没有指定地点(根据客户所在地址查询),如北京,那么统计全部客户的交易量和交易额。

    --(7)统计银行卡交易量和交易额

    --统计指定时间段内某地区客户在银行卡交易量和交易额,如果不指定地区,则查询所有客户的交易量和交易额。

    if exists(select * from sysobjects where name='usp_getTradeInfo')

        drop procedure usp_getTradeInfo

    go

    create procedure usp_getTradeInfo @startTimedate=null,@endTime date=null,

    @addr varchar(100)=null

    as

        declare @inCount int=0,@outCount int=0,@inMoney money=0,@outMoney money=0,

        @sumInMoney money=0,@sumOutMoney money=0,@i int=1

        while(@i<=convert(int,(select max(BDNo) from BankDealInfo)))

        begin

           if(@startTime is null)            

               set @startTime=dateadd(mm,-convert(int,datename(mm,getdate()))

               +1,dateadd(dd,-convert(int,datename(dd,getdate()))+1,getdate()))

           if(@endTime is null)

               set @endTime=getdate()

           if(@addr is null)

           begin

               select@inCount=count(*) from BankDealInfo where BDDealType='存入'

               and datediff(dd,@startTime,BDDealDate)>=0 and datediff(dd,@endTime,

               BDDealDate)<=0

               select@inMoney=BDDealAcount from BankDealInfo where BDDealType

               ='存入' and BDNo=@i and datediff(dd,@startTime,BDDealDate)>=0

               and datediff(dd,@endTime,BDDealDate)<=0

               set @sumInMoney+=@inMoney

               select@outCount=count(*) from BankDealInfo where BDDealType='支取'

               and datediff(dd,@startTime,BDDealDate)>=0 and datediff(dd,@endTime,

               BDDealDate)<=0

               select@outMoney=BDDealAcount from BankDealInfo where BDDealType

               ='支取' and BDNo=@i and datediff(dd,@startTime,BDDealDate)>=0

               and datediff(dd,@endTime,BDDealDate)<=0

               set @sumOutMoney+=@outMoney

           end

           else

           begin

               select@inCount=count(*) from BankDealInfo where BDDealType='存入'

               and datediff(dd,@startTime,BDDealDate)>=0 and datediff(dd,@endTime,

               BDDealDate)<=0 and BDBCNo in(select BCNo from BankCard where

               BCBCIdin(select BCID from BankCustomer where BCAddr=@addr))

               select@inMoney=BDDealAcount from BankDealInfo where BDDealType

               ='存入' and BDNo=@i and datediff(dd,@startTime,BDDealDate)>=0

               and datediff(dd,@endTime,BDDealDate)<=0 and BDBCNo in(select BCNo

               from BankCard where BCBCId in(select BCID from BankCustomer

               whereBCAddr=@addr))

               set @sumInMoney+=@inMoney

               select@outCount=count(*) from BankDealInfo where BDDealType='支取'

               and datediff(dd,@startTime,BDDealDate)>=0 and datediff(dd,@endTime,

               BDDealDate)<=0 and BDBCNo in(select BCNo from BankCard where

               BCBCIdin(select BCID from BankCustomer where BCAddr=@addr))

               select@outMoney=BDDealAcount from BankDealInfo where BDDealType

               ='支取' and BDNo=@i and datediff(dd,@startTime,BDDealDate)>=0

               and datediff(dd,@endTime,BDDealDate)<=0 and BDBCNo in(select BCNo

               from BankCard where BCBCId in(select BCID from BankCustomer

               whereBCAddr=@addr))

               set @sumOutMoney+=@outMoney

           end

           set @i+=1

        end

        print '---------------------------------------------------------------'

        print '---------------------------------------------------------------'

        print '统计银行卡交易量和交易额'

        print '起始日期:'+convert(char(21),@startTime)+'截止日期:'+convert(char,@endTime)

        print '存入笔数:'+ltrim(str(@inCount))+'                    存入金额:'

        +ltrim(str(@sumInMoney))

        print '支出笔数:'+ltrim(str(@outCount))+'                    支出金额:'

        +ltrim(str(@sumOutMoney))

        print '交易总笔数:'+ltrim(str(@inCount+@outCount))

        print '结余金额:'+ltrim(str(@sumInMoney-@sumOutMoney))

        print '---------------------------------------------------------------'

        print '---------------------------------------------------------------'  

    go

    exec usp_getTradeInfo

    exec usp_getTradeInfo@addr='包头市昆区宝钢五中'

    exec usp_getTradeInfo'2018-05-1','2018-05-28'

    exec usp_getTradeInfo'2018-05-1','2018-12-30','包头市昆区宝钢五中'

    exec usp_getTradeInfo@addr='内蒙古包头'

    go

    利用事务实现转账

    使用存储过程和事务实现转账业务,操作步骤如下所示:

    (1) 从某一个账户支取一定金额的存款。

    (2) 将支取金额存入到另一个指定的账户中。

    (3) 分别打印此笔业务的转出账单和转入账单。

       要求:

    (1)  存储过程名称是usp_transfer。

    (2) 要求使用事务机制实现转账业务。

    if exists(select * from sysobjects where name='usp_transfer')

        drop procedure usp_transfer

    go

    create procedure usp_transfer @outBCNochar(19),@inBCNo char(19),@saleMoney money

    as

        declare @errorCount int =0,@outBCExistBalance money,@inBCExistBalance money

        begin tran

        --不返回受影响的行数

        set nocount on

        insert into BankDealInfo values(@outBCNo,default,@saleMoney,'支取','通过存储')

        set @errorCount+=@@error

        insert into BankDealInfo values(@inBCNo,default,@saleMoney,'存入','通过存储')

        set @errorCount+=@@error

        if @errorCount<>0

        begin

           rollback tran

           raiserror('转账失败!!',18,2)

        end

        else

        begin

           commit tran

           print '开始转账,请稍后...'

           print '交易正进行,请稍后...'

           print '交易成功,交易金额:'+ltrim(str(@saleMoney))

           select @outBCExistBalance=BCExistBalance from BankCard where

           BCNo=@outBCNo

           print '卡号:'+@outBCNo+'    余额:'+ltrim(str(@outBCExistBalance))

           select @inBCExistBalance=BCExistBalance from BankCard where

           BCNo=@inBCNo

           print '卡号:'+@inBCNo+'    余额:'+ltrim(str(@inBCExistBalance))

           declare @outBCName char(20),@outBCCurrency char(5),@outBBTName

           char(20),@outBCOpenDate date

           select @outBCName=BCName from BankCustomer where BCID=(select BCBCId from

           BankCard where BCNo=@outBCNo)

           select @outBCCurrency=BCCurrency,@outBCOpenDate=BCOpenDate from

           BankCard where BCNo=@outBCNo

           select @outBBTName=BBTName from BankBusinessType whereBBTId=(select

           BCBBTId from BankCard where BCNo=@outBCNo)

           print '打印出转出对账单'

           print '---------------------------------------------------------------'  

           print '卡号:'+@outBCNo

           print '货币类型:'+@outBCCurrency

           print '存款类型:'+@outBBTName

           print '开户日期:'+convert(char,@outBCOpenDate)

           print '交易日   '+'   类型   '+'交易金额   '+'备注'

           print convert(char(10),getdate(),120)+支取   '+ltrim(str(@saleMoney))+

           '        通过存储'

           declare @inBCName char(20),@inBCCurrency char(5),@inBBTName

           char(20),@inBCOpenDate date

           select @inBCName=BCName from BankCustomer where BCID=(select BCBCId from

           BankCard where BCNo=@inBCNo)

           select @inBCCurrency=BCCurrency,@inBCOpenDate=BCOpenDate from

           BankCard where BCNo=@inBCNo

           select @inBBTName=BBTName from BankBusinessType whereBBTId=(select

           BCBBTId from BankCard where BCNo=@inBCNo)

           print '打印出转入对账单'

           print '---------------------------------------------------------------'  

           print '卡号:'+@inBCNo

           print '货币类型:'+@inBCCurrency

           print '存款类型:'+@inBBTName

           print '开户日期:'+convert(char,@inBCOpenDate)

           print '交易日   '+'   类型   '+'交易金额   '+'备注'

           print convert(char(10),getdate(),120)+存入   '+ltrim(str(@saleMoney))+

           '        通过存储'

           print '---------------------------------------------------------------'  

        end

    go

    exec usp_transfer'1010 3576 1234 5678','1010 3576 1234 5688',100

    go

    展开全文
  • CREATE TABLE if not exists cardInfo( cardID VARCHAR(19) PRIMARY KEY NOT NULL COMMENT'银行卡卡号', curID VARCHAR(10) NOT NULL default 'RMB' COMMENT'币种', savingID INT(2) NOT NULL COMMENT'存款类型'...

    首先,我们需要知道项目的需求有哪些?

    用例1:数据库设计

    绘制E-R图

    绘制数据库模型图

    数据库模型图我们可以创建好表和约束之后,点击对象

    就会出现你已经创建好的表,选中需要绘制模型图的表

    然后右下角有三个图案,数据库默认是第一个,我们可以选择第三个

    可以去看看这三个图案有什么不同,这样比我们一个一个去添加字段设置约束的效率要高很多,一举两得,希望能帮助到大家!

    用例2:创建数据库及登录用户并授权

    #创建bankDB数据库,如果存在先删除在创建
    DROP DATABASE IF EXISTS bankDB;
    CREATE DATABASE if not exists bankDB;
    
    #创建普通用户bankMaster并授权
    grant all on bankdb
    to bankMaster@127.0.0.1 identifed by '1234';
    

    登录用户的权限书本上有提到,可以去看看它的解析,这就不多做讲述了。

    用例3:创建表、约束

    #使用bankDB数据库
    use bankDB;
    
    
    #客户信息表
    DROP TABLE IF EXISTS userInfo;
    CREATE TABLE if not exists userInfo(
    	customerID INT(8) PRIMARY KEY AUTO_INCREMENT COMMENT'客户编号',
    	customerName VARCHAR(10) NOT NULL COMMENT'客户姓名',
    	PID CHAR(18) NOT NULL COMMENT '身份证号',
    	telephone CHAR(11) NOT NULL COMMENT '手机号码',
    	address VARCHAR(50) comment '地址'
    )ENGINE=INNODB COMMENT='客户表',
    CHARSET=utf8;
    
    
    #银行卡信息表
    DROP TABLE IF EXISTS cardInfo;
    CREATE TABLE if not exists cardInfo(
    	cardID VARCHAR(19) PRIMARY KEY NOT NULL COMMENT'银行卡卡号',            
    	curID VARCHAR(10) NOT NULL default 'RMB' COMMENT'币种',
    	savingID INT(2) NOT NULL COMMENT'存款类型',
    	openDate TIMESTAMP NOT NULL COMMENT'开户日期',
    	openMoney DECIMAL NOT NULL COMMENT '开户金额',
    	balance DECIMAL NOT NULL COMMENT '账户余额', 
    	`password` VARCHAR(6) NOT NULL default '888888' COMMENT'密码',
    	IsReportLoss BIT NOT NULL default 0 COMMENT '是否挂失',
    	customerID INT(8) NOT NULL COMMENT'客户编号'
    )ENGINE=INNODB COMMENT='银行卡信息表',
    CHARSET=utf8;
    
    alter table cardinfo modify openMoney decimal(9,2);
    alter table cardinfo modify balance decimal(9,2);
    
    #创建外键
    alter table cardinfo
    add constraint foreign key(customerID) references userinfo(customerID);
    alter table cardinfo
    add constraint foreign key(savingID) references deposit(savingID);
    
    
    #交易信息表
    DROP TABLE IF EXISTS tradeInfo;
    CREATE TABLE if not exists tradeInfo(
    	transDate TIMESTAMP NOT NULL COMMENT'交易日期',
    	cardID char(19) NOT NULL NULL COMMENT'卡号',
    	transType char(10) NOT NULL COMMENT'交易类型',
    	transMoney DECIMAL NOT NULL COMMENT'交易金额',
    	remark TEXT COMMENT'交易备注'
    )ENGINE=INNODB COMMENT='交易信息表',
    CHARSET=utf8;
    
    #创建外键
    alter table tradeinfo
    add constraint foreign key(cardID) references cardinfo(cardID);
    
    
    #存款类型表
    DROP TABLE IF EXISTS deposit;
    CREATE TABLE if not exists deposit(
    	savingID INT(2) PRIMARY KEY COMMENT'存款类型',
    	savingName VARCHAR(20) NOT NULL COMMENT'存款类型名称',
    	curID VARCHAR(10) NOT NULL default 'RMB' COMMENT'存款币种'
    )ENGINE=INNODB COMMENT='存款类型表',
    CHARSET=utf8
    

    用例4:插入测试数据库

    #添加数据
    
    #银行客户表数据
    INSERT INTO userInfo(customerID,customerName,PID,telephone,address)
    VALUES(1,'丁六','567891321242345618','13645667783','北京西城区'),
    			(2,'王五','56789132124234567X','13642345112','河北石家庄市'),
    			(3,'张三','567891321242345789','13567893246','北京海定区'),
    			(4,'丁一','123451321242345618','13056434411','河南新乡'),
    			(5,'李四','678911321242345618','13318877954','山东济南市');
    
    
    #银行卡信息数据
    INSERT INTO cardInfo(cardID,savingID,openDate,openmoney,balance,`password`,IsReportLoss,customerID)
    VALUES('1010357612121004',1,NOW(),1,1001,'888888',0,1),
    			('1010357612121130',1,NOW(),1,1,'888888',0,2),
    			('1010357612125678',3,NOW(),1000,6100,'123456',0,3),
    			('1010357612128284',3,NOW(),1000,1001,'888888',0,4),
    			('1010357612121134',1,NOW(),1,1501,'123123',1,5);
    
    
    #交易信息表数据
    INSERT INTO tradeInfo(transDate,cardID,transType,transMoney,remark)
    VALUES(NOW(),'1010357612125678','支取',900,NOW()),
    			(NOW(),'1010357612121130','存入',300,NOW()),
    			(NOW(),'1010357612121004','存入',1000,NOW()),
    			(NOW(),'1010357612121130','存入',1900,NOW()),
    			(NOW(),'1010357612121134','存入',5000,NOW()),
    			(NOW(),'1010357612121134','存入',500,NOW()),
    			(NOW(),'1010357612121134','支取',2000,NOW()),
    			(NOW(),'1010357612125678','存入',2000,NOW()),
    			(NOW(),'1010357612121134','支取',2000,NOW()),
    			(NOW(),'1010357612125678','存入',2000,NOW()),
    			(NOW(),'1010357612125678','存入',2000,NOW());
    
    
    #存款类型表数据
    INSERT INTO deposit(savingID,savingName)
    VALUES(1,"定期一年"),(2,"定期二年"),(3,'活期');
    

    用例5:模拟常规业务

    #1.修改客户密码
    update cardinfo set `password`='123456' where cardID='1010357612125678';
    update cardinfo set `password`='123123' where cardID='1010357612121134';
    
    #2.办理银行卡挂失
    update cardinfo set IsReportLoss=1 where cardID='1010357612121134';
    select c.cardID 卡号,c.curID 货币,d.savingName 储蓄种类,c.openDate 开户日期,c.openMoney 开户金额,c.balance 余额,
    			 c.`password` 密码,c.IsReportLoss 是否挂失,u.customerName 客户姓名
    from cardinfo c
    inner join userinfo u
    on c.customerID=u.customerID
    inner join deposit d
    on c.savingID=d.savingID;
    
    #3.统计银行总存入金额和总总支取金额
    select transType 资金流向,sum(transMoney) 总金额 from tradeInfo
    where transType='支取' or transType='存入'
    group by transType;
    
    #4.查询本周开户信息
    select c.cardID 卡号,u.customerName 姓名,c.curID 货币,c.savingID 存款类型,c.openDate 开户日期,c.openMoney 开户金额,
    			 c.balance 开户余额,c.IsReportLoss 账户状态
    from cardinfo c
    inner join userinfo u
    on c.customerID=u.customerID
    where week(c.openDate)=week(NOW());
    
    #5.查询本月交易金额最高的卡号
    select distinct cardID
    from tradeinfo 
    where  transMoney=(select MAX(transMoney) from tradeinfo where transType='支出' or transType='存入' 
    									 and MONTH(transDate)=MONTH(NOW()));
    
    #6.查询挂失客户
    select u.customerName 客户姓名,u.telephone 联系电话,c.cardID 卡号,c.IsReportLoss 是否挂失
    from userinfo u
    inner join cardinfo c
    on u.customerID=c.customerID
    where c.IsReportLoss=b'1';
    
    
    #7.催款提醒业务
    select u.customerName 客户姓名,u.telephone 联系电话,c.balance 存款余额
    from userinfo u
    inner join cardinfo c
    on u.customerID=c.customerID
    where c.balance<200;

    用例6:创建、使用客户友好信息视图

    #创建用户视图
    DROP VIEW IF EXISTS view_userInfo;
    create view view_userInfo
    as
    select customerID 客户编号,customerName 客户姓名,PID 身份证号,telephone 手机号码,address 地址
    from userinfo u;
    
    #创建银行卡视图
    DROP VIEW IF EXISTS view_cardinfo;
    create view view_cardinfo
    as
    select cardID 卡号,customerName 客户名,c.curID 货币种类,savingName 存款类型,openDate 开户日期,balance 余额, 
    			`password` 密码,IsReportLoss 是否挂失
    from cardinfo c
    inner join userinfo u
    on c.customerID=u.customerID
    inner join deposit d
    on c.curID=d.curID;
    
    
    #创建交易信息视图
    DROP VIEW IF EXISTS view_transInfo;
    create view view_transInfo
    as
    SELECT transDate 交易日期,transType 交易类型,cardID 卡号, transMoney 交易金额, remark 备注
    FROM tradeinfo;

    用例7:使用事务完成转账

    #使用事务完成转账
    #从卡号为“1010357612121134”的账户中转出300元给卡号为“1010357612345678”的账户,即李四转账300元给张三。
    #先关闭自动提交状态
    set autocommit=0;
    
    #开启事务
    begin;
    update cardinfo set balance=balance-300 where cardID='1010357612121134';
    update cardinfo set balance=balance+300 where cardID='1010357612125678';
    
    #提交事务
    commit;
    
    #遇到问题回滚数据到原来的状态
    rollback;
    
    #恢复自动提交
    set autocommit=1;
    

    可以根据书本上的提示完成项目……

     

    展开全文
  • 数据库期末作业之银行ATM存取款机系统

    千次阅读 多人点赞 2019-05-07 11:22:16
    --2)完成取款或存款业务 alter proc sav_get @v_cardnumber char(19), @v_transtype char(4), @v_money money, @v_pass char(6), @remark char(100) as begin tran if exists(select *from cardinfo where cardID=@v...
    --一、建库、建表、建约束
    --1、使用SQL创建表
    --客户信息表userinfo
    --字段名称	说明	备注
    --customerID	顾客编号	自动编号(标识列),从1开始,主键	
    --用序列sequence实现,用其属性:nextval
    --customerName	开户名	必填	
    --PID	身份证号	必填,智能是18位或15位,唯一约束	check约束len()函数
    --telephone	联系电话	必填,11位手机号	check约束,’[0-9]’
    --address	居住地址		
    create table userinfo
    (
    customerID int identity(1,1),
    customerName char(10),
    PID char(18) ,
    telephone char(11),
    address char(30)
    )
    --银行卡信息表cardinfo
    --字段名称	说明
    --cardID	卡号	必填,主键,
    --银行的卡号规则和电话号码一样,一般前8位代表特殊含义,如某综合某支行等,
    --假定该行要求其营业厅的卡号格式为10103576**** ***开始,
    --每4位号码后有空格,卡号一般是随机产生。
    --curType	货币种类	必填,默认为RMB
    --savingTate	存款类型	活期/定活两便/定期
    --openDate	开户日期	必填,默认为系统当前日期
    --openMoney	开户金额	必填,不低于1元
    --balance	余额	必填,不低于1元,否则将销户
    --pass	密码	必填,6位数字,开户时默认为6个“6”
    --IsReportloss	是否挂失	必填,是/否值,默认为“否”
    --customerID	顾客编号	外键,必填,表示该卡对应的顾客编号,一位顾客允许办理多张卡号
    create table cardinfo
    (
    cardID char(19) not null,
    curType char(10),
    savingTate char(10) ,
    openDate datetime,
    openMoney money,
    balance money,
    pass char(6),
    IsReportloss char(2) ,
    customerID int
    )
    --交易信息表transinfo
    --字段名称	说明
    --transDate	交易日期	必填,默认为系统当前日期
    --cardID	卡号	必填,外键
    --transType	交易类型	必填,只能是存入/支取
    --transMoney	交易金额	必填,大于0
    --remark	备注	可选,其他说明
    create table transinfo
    (
    transDate datetime,
    cardID char(19),
    transType char(4),
    transMoney money,
    remark varchar(100)
    )
    --2、使用SQL语言在每个表上添加约束
    --主键约束、外键约束、CHECK约束、默认约束、非空约束
    --①
    --客户信息表userinfo
    
    --customerID	顾客编号	自动编号(标识列),从1开始,主键	
    alter table userinfo
    add constraint PK_userinfor primary key(customerID)
    
    --customerName	开户名	必填	
    alter table userinfo
    add constraint CK_cn check(customerName is not null)
    
    --PID	身份证号	必填,智能是18位或15位,唯一约束	check约束len()函数
    
    alter table userinfo
    add constraint CK_PID check(len(PID)=18 or len(PID)=15 )
    alter table userinfo
    add constraint CK_pn check(PID is not null)
    alter table userinfo
    add constraint UK_pid unique(pid)
    
    --telephone	联系电话	必填,11位手机号	check约束,’[0-9]’
    
    alter table userinfo
    add constraint CK_tele check(telephone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
    alter table userinfo
    add constraint CK_tn check(telephone is not null)
    
    --②
    --银行卡信息表cardinfo
    
    --cardID	卡号	必填,主键,
    --卡号格式为10103576**** ***开始
    alter table cardinfo
    ADD	constraint CK_Nca check(cardID is not null)
    alter table cardinfo
    ADD	constraint PK_ca primary key(cardID),
    	constraint CK_car check(cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]')
    --curType	货币种类	必填,默认为RMB
    
    alter table cardinfo
    add constraint DF_ca default 'RMB' for curType,
    	constraint CK_Ncur check(curType is not null)
    	
    --savingTate	存款类型	活期/定活两便/定期
    
    alter table cardinfo
    add constraint CK_st check(savingTate in ('活期','定活两便','定期'))
    
    --openDate	开户日期	必填,默认为系统当前日期
    
    alter table cardinfo
    add constraint DF_openDate default getdate() for openDate,
    	constraint cK_nod check(openDate is not null)
    	
    --openMoney	开户金额	必填,不低于1元
    
    alter table cardinfo
    add constraint CK_nopenM check(openMoney is not null),
    	constraint CK_openM check(openMoney>=1)
    	
    --balance	余额	必填,不低于1元,否则将销户
    
    alter table cardinfo
    add constraint CK_nbalance check(balance is not null),
    	constraint CK_balance check(balance>=1)
    	
    --pass	密码	必填,6位数字,开户时默认为6个“6”
    
    alter table cardinfo
    add constraint CK_npass check(pass is not null),
    	constraint DF_PASS default '666666' for pass,
    	constraint CK_PASS check(pass like '[0-9][0-9][0-9][0-9][0-9][0-9]')
    --IsReportloss	是否挂失	必填,是/否值,默认为“否”
    
    alter table cardinfo
    add constraint CK_nIsReportloss check(IsReportloss is not null),
    	constraint DF_IsReportloss default '否' for IsReportloss,
    	constraint CK_IsReportloss check(IsReportloss in('是','否'))
    	
    --customerID	顾客编号	外键,必填,表示该卡对应的顾客编号,一位顾客允许办理多张卡号
    
    alter table cardinfo
    add constraint FK_customerID foreign key(customerID) references userinfo(customerID),
    	constraint CK_ncustomerID check(customerID is not null)
    	
    --③
    --交易信息表transinfo
    
    --transDate	交易日期	必填,默认为系统当前日期
    --cardID	卡号	必填,外键
    --transType	交易类型	必填,只能是存入/支取
    --transMoney	交易金额	必填,大于0
    --remark	备注	可选,其他说明
    alter table transinfo
    add constraint CK_Nrd check(transDate is not null),
    	constraint DF_td default getdate() for transDate,
    	
    	constraint FK_cardID foreign key (cardID) references cardinfo(cardID),
    	constraint CK_NcardID check(cardID is not null),
    	
    	constraint CK_NtransType check(transType is not null),
    	constraint DF_transType CHECK(transType in('存入','支取')),
    	
    	constraint CK_NtransMoney check(transMoney is not null),
    	constraint DF_transMoney CHECK(transMoney>0)	
    --二、插入测试数据
    --使用SQL语言向每个表中插入至少3条记录
    
    --1.向userinfo表中插入3条记录
    insert into userinfo(customerName,PID,telephone,address) values
    			('李白','370404066601021111','15000000000','山东济南'),
    			('李大白','370404066601022222','15000000111','山东济宁'),
    			('李小白','370404066601023333','15000000222','山东泰安')
    --2.向cardinfo表中插入3条记录
    insert into cardinfo(cardID,savingTate,openMoney,balance,pass,customerID) values
    			('1010 3576 0000 0000','活期',10,2,'000000',1),
    			('1010 3576 0000 0001','活期',100,20,'000001',2),
    			('1010 3576 0000 0010','活期',1000,200,'000010',3)
    --3.向transinfo表中插入3条记录
    insert into transinfo(cardID,transType,transMoney) values
    ('1010 3576 0000 0000','存入',100),
    ('1010 3576 0000 0001','存入',100),
    ('1010 3576 0000 0010','支取',100)
    --三、模拟常规业务
    --1)修改客户密码
    --修改卡号为‘1010 3576 0000 0000’的密码为‘000011’
    update cardinfo set pass='000011' where cardID='1010 3576 0000 0000'
    --2)办理银行卡挂失
    --挂失卡号为‘1010 3576 0000 0000’的卡
    update cardinfo set IsReportloss='是' where cardID='1010 3576 0000 0000'
    --3)统计银行资金流通余额和盈利结算
    --银行资金流通余额=总存入金额-总支取金额
    --通过调用存储过程计算流通余额
    create proc pro_sum
    as
    begin
    declare @v_sum money,@v_inmoney money,@v_omoney money
    select @v_inmoney=(select SUM(transMoney) from transinfo where transType='存入')
    select @v_omoney=(select SUM(transMoney) from transinfo where transType='支取')
    select @v_sum=@v_inmoney-@v_omoney
    select '流通余额'=@v_sum
    end
    go
    
    exec pro_sum
    --盈利结算=总支取金额 * 0.008 – 总存入金额 * 0.003
    --通过调用存储过程计算盈利结算
    create proc profit
    as
    begin
    declare @v_pro money,@v_inmoney money,@v_omoney money
    select @v_inmoney=(select SUM(transMoney) from transinfo where transType='存入')
    select @v_omoney=(select SUM(transMoney) from transinfo where transType='支取')
    select @v_pro=@v_omoney*0.008 -@v_inmoney * 0.003
    select '盈利结算'=@v_pro
    end
    go
    exec profit
    --4)查询本周开户的卡号,显示该卡相关信息
    select * from cardinfo where (DATEDIFF(DAY,GETDATE(),openDate)<DATEPART(WEEKDAY,openDate))
    --5)查询本月交易金额最高的卡号
    select distinct cardID from transinfo where transMoney=(select MAX(transMoney) from transinfo)
    --6)查询挂失账号的客户信息
    select * from userinfo where customerID in(select customerID from cardinfo where IsReportloss='是')
    --四、利用视图实现数据查询
    --1)为客户提供以下3个视图供其查询该客户数据
    --2)提供友好界面,要求各列名称为中文描述
    --3)调用创建的视图获得查询结果
    --客户基本信息:vw_userInfo
    create view vw_userInfo 
    as select customerID 客户号,customerName 客户名,PID 身份证号,telephone 电话号码,address 地址 from userinfo
    
    select *from vw_userInfo
    --银行卡信息:vw_cardInfo
    create view vw_cardInfo
    as select cardid 银行卡号,curType 货币类型,savingTate 存款类型,openDate 开户日期,openMoney 开户金额,
    balance 余额,pass 密码,IsReportloss 是否挂失,customerID 客户号 from cardinfo
    
    select *from vw_cardInfo
    --银行卡交易信息:vw_transInfo
    create view vw_transInfo
    as select transDate 交易日期,cardID 银行卡号,transType 交易类型,transMoney 交易金额,remark 备注 from transinfo
    
    select *from vw_transInfo
    
    --五、用存储过程实现业务处理
    --1)完成开户业务
    
    --创建存储过程通过随机数产生卡号
    create proc randomcardid
    @cardid char(19)output
    as
    select @cardid='1010 3576 '+substring(convert(char(10),RAND()*10000),0,5)+' '+substring(convert(char(10),RAND()*10000),0,5)
    go
    
    --创建开户存储过程
    create proc openaccount
    @curtype char(10),
    @savingTate char(10) ,
    @openMoney money,
    @customerID int
    as
    begin
    begin tran
    if(@openMoney<1)
    	begin
    		print'开户金额小于一元!'
    		rollback tran
    	end
    else
    	begin
    		declare @cardid char(19) 
    		exec  randomcardid @cardid output
    		insert into cardinfo values(@cardid,@curtype,@savingTate,default,@openMoney,@openMoney,default,DEFAULT,@customerID )
    		print'开户成功!'
    		select *from cardinfo where cardID=@cardid
    		commit tran
    	end
    end
    go
    
    --test:开户金额>=1
    exec openaccount 'DOLLAR','活期',2,'1'
    
    --test:开户金额<1
    exec openaccount 'DOLLAR','活期',0.1,'1'
    --开户金额小于一元!
    
    --2)完成取款或存款业务
    alter proc sav_get
    @v_cardnumber char(19),
    @v_transtype char(4),
    @v_money money,
    @v_pass char(6),
    @remark char(100)
    as
    begin tran
    if exists(select *from cardinfo where cardID=@v_cardnumber)
    	begin
    		if(@v_pass!=(select pass from cardinfo where cardID=@v_cardnumber))
    		begin
    			raiserror('密码输入错误!',16,1)
    			rollback tran
    		end
    
    		if(@v_transtype='存入')
    		begin
    			update cardinfo set balance=balance+@v_money where cardID=@v_cardnumber
    			print'存入成功!'
    		end
    		else if(@v_transtype='支取' )
    		begin
    			update cardinfo set balance=balance-@v_money where cardID=@v_cardnumber
    			if((select balance from cardinfo where cardID=@v_cardnumber)<0)
    			begin
    				print'余额不足!'
    				rollback tran
    			end
    		end
    		insert into transinfo values(GETDATE(),@v_cardnumber,@v_transtype,@v_money,@remark)
    	end
    commit tran
    go
    select *from cardinfo
    exec sav_get'1010 3576 0000 0000','存入',10,'000011',null
    --3)根据卡号打印对账单
    create procedure show
    @cardnumber char(19)
    as
    begin
    if exists(select*from transinfo where cardID=@cardnumber)
    	select *from transinfo where cardID=@cardnumber
    else
    	print'卡号不存在!'
    end
    go
    
    exec show '1010 3576 0000 0000'
    --4)查询、统计指定时间段内没有发生交易的账户信息
    create proc noexchange
    @starttime datetime,
    @endtime datetime
    as
    begin
    if (@starttime<@endtime)
    	select *from cardinfo 
    	where cardID not in(select cardID from transinfo where transDate>=@starttime and transDate<=@endtime)
    else 
    	print'开始时间应早于结束时间!'
    end 
    go
    exec noexchange'2018-12-18','2018-12-19'

     

    展开全文
  • # 银行卡信息 CREATE TABLE `Account`( `card_number` VARCHAR(16) COMMENT '卡号' NOT NULL PRIMARY KEY, `NameId` INT COMMENT '顾客编号' NOT NULL, FOREIGN KEY(`NameId`) REFERENCES client(`NameId`), `...
    CREATE DATABASE `db_bank`;
    
    # 客户信息
    CREATE TABLE `client`(
    `NameId` INT COMMENT '顾客编号' auto_increment PRIMARY KEY NOT NULL,
    `Name` VARCHAR(10) COMMENT '开户名称' NOT NULL,
    `ID_number` VARCHAR(18) COMMENT '身份证号',
    `Phone` VARCHAR(11) COMMENT '联系电话',
    `Address` VARCHAR(20) COMMENT '居住地址'
    );
    # 银行卡信息
    CREATE TABLE `Account`(
    `card_number` VARCHAR(16) COMMENT '卡号' NOT NULL PRIMARY KEY,
    `NameId` INT COMMENT '顾客编号' NOT NULL,
    FOREIGN KEY(`NameId`) REFERENCES client(`NameId`),
    `password` VARCHAR(20) COMMENT '密码',
    `currency_ID` INT COMMENT '货币种类编号'NOT NULL,
    FOREIGN KEY(`currency_ID`) REFERENCES currency_type(`currency_ID`),
    `deposit_type` VARCHAR(20) COMMENT '存款类型' NOT NULL,
    `open_date` TIMESTAMP COMMENT '开户日期' NOT NULL,
    `initial_deposit` DECIMAL COMMENT '开户金额',
    `balance` DECIMAL COMMENT '余额',
    `report` int(1) COMMENT '是否挂失' DEFAULT 0
    );
    # 货币类型
    CREATE TABLE `currency_type`(
    `currency_ID` INT COMMENT '货币种类号' NOT NULL PRIMARY KEY,
    `currency_name` VARCHAR(20) COMMENT '货币种类名称' 
    );
    # 交易信息
    CREATE TABLE `deal_message`(
    `id_number` VARCHAR(16) COMMENT '卡号' NOT NULL,
    FOREIGN KEY(`id_number`) REFERENCES account(`card_number`),
    `deal_data` TIMESTAMP COMMENT '交易日期' NOT NULL,
    `deal_money` DECIMAL COMMENT '交易金额',
    `note` VARCHAR(50) COMMENT '备注'
    )
    INSERT into client VALUES(1,'张三','189642198407089634','10080','记挂市');
    INSERT into client VALUES(2,'李四','152364197702341234','10040','大航州');
    INSERT into client VALUES(3,'王五','987654200010012233','10010','大俗州');
    INSERT into client VALUES(4,'赵六','123456200104045656','119','这将');
    INSERT into client VALUES(5,'郑七','146529199405012648','120','伤嗨');
    
    INSERT INTO currency_type VALUES(1,'美元');
    INSERT INTO currency_type VALUES(2,'人民币');
    INSERT INTO currency_type VALUES(3,'英镑');
    INSERT INTO currency_type VALUES(4,'日元');
    INSERT INTO currency_type VALUES(5,'意大利里拉');
    INSERT INTO currency_type VALUES(6,'瑞士法郎');
    INSERT INTO currency_type VALUES(7,'荷兰盾');
    INSERT INTO currency_type VALUES(8,'比利时法郎');
    
    INSERT into Account VALUES('1562384923486512',1,'123456',2,'定期存款','2021-01-05',3000,300,0);
    INSERT into Account VALUES('1562348953152463',3,'888888',4,'活期存款','2020-10-01',300000,26000,0);
    INSERT into Account VALUES('1562315624895655',2,'471825',1,'活期存款','2009-02-25',2000,1500,1);
    INSERT into Account VALUES('1562314562548759',5,'123456',7,'定期存款','2021-02-05',300.5,200.6,1);
    INSERT into Account VALUES('1562345865977555',4,'123456',6,'活期存款','2019-08-05',400,350,0);
    
    INSERT into deal_message VALUES('1562384923486512','2021-03-08',200,'');
    INSERT into deal_message VALUES('1562348953152463','2020-02-12',200,'casdca');
    INSERT into deal_message VALUES('1562315624895655','2019-01-18',200,'vzxcvzcdad');
    INSERT into deal_message VALUES('1562345865977555','2018-03-03',200,'vsdfvadcasd');
    INSERT into deal_message VALUES('1562314562548759','2020-01-07',200,'vdssdcsadcadcda');
    

    代码作业省略 作业自己写

    展开全文
  • 报告和T-SQL源文件
  • 第九章:银行ATM存取款机系统

    千次阅读 2017-02-24 08:24:25
    INSERT INTO deposit (savingName,descrip) VALUES ('本取息五年','按月支取利息') SELECT * FROM DEPOSIT INSERT INTO userInfo(customerName,PID,telephone,address ) VALUES('张三','123456789012345','010-...
  • --创建存取款业务的存储过程 create procedure proc_OpenAccount @CustName char(20),@CustID char(18),@CustTelephone varchar(20),@OpenMoney money,@BusName varchar(20),@CustAddress ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,566
精华内容 626
关键字:

银行atm存取款机系统