北大青鸟4.0 S2 项目实战--MSSQL

主要是实现一个银行自动提款机系统,..不是很难.需要的看看!S2学习的所有SQL技术基本都用上了.
SET NOCOUNT ON
USE master
GO
IF EXISTS (SELECT * FROM sysdatabases WHERE name='bank')
DROP DATABASE bank
GO
CREATE DATABASE bank
GO
USE bank
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='userInfo')
DROP TABLE userInfo
GO
CREATE TABLE userInfo
(
customerID INT IDENTITY(1,1) NOT NULL,                --顾客标号
customerName VARCHAR(10) NOT NULL,                --顾客姓名
PID VARCHAR(18) NOT NULL,                    --身份证号
telephone VARCHAR(13) NOT NULL,                    --电话
address VARCHAR(50) NULL                    --地址
)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='cardInfo')
DROP TABLE cardInfo
GO
CREATE TABLE cardInfo
(
cardID VARCHAR(19) NOT NULL,                --卡号格式 1010 3576 **** ****
curType VARCHAR(10) NOT NULL,                --货币种类
savingType VARCHAR(4) NULL,                --存款类型
openDate DATETIME NOT NULL,                --开户日期
openMoney MONEY NOT NULL,                --开户金额
balance MONEY NOT NULL,                    --余额
pass VARCHAR(20) NOT NULL,                --密码
IsReportLoss BIT NOT NULL,                --是否挂失
customerID INT NOT NULL                    --顾客编号,外键
)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='transInfo')
DROP TABLE transInfo
GO
CREATE TABLE transInfo
(
transDate DATETIME NULL,                --交易时间必须 默认当前
cardID    VARCHAR(19) NOT NULL,                --卡号  外间 可重复索引
transType VARCHAR(4) NOT NULL,                --交易类型 存入 之取
transMoney MONEY NOT NULL,                --交易金额 大于0
remark VARCHAR(100) NULL,                --备注
)
GO
/*建立个表约束*/
ALTER TABLE userInfo ADD CONSTRAINT PK_customerID PRIMARY KEY (customerID)            --住键约束
ALTER TABLE userInfo ADD CONSTRAINT CK_PID CHECK (LEN(PID)=15 OR LEN(PID)=18)            --身份证为15到18人
ALTER TABLE userInfo ADD CONSTRAINT UP_PID UNIQUE (PID)                        --身份证唯一
ALTER TABLE userInfo ADD CONSTRAINT CK_telephone CHECK (LEN(telephone)=11 OR telephone LIKE '____-________')        --11
GO
ALTER TABLE cardInfo ADD CONSTRAINT PK_cardID PRIMARY KEY (cardID)
ALTER TABLE cardInfo ADD CONSTRAINT CK_cardID CHECK (cardID LIKE '1010 3576 ____ ____' AND LEN(cardID)=19)
ALTER TABLE cardInfo ADD CONSTRAINT DE_curType DEFAULT 'RMB' FOR curType
ALTER TABLE cardInfo ADD CONSTRAINT DF_openDate DEFAULT getdate() FOR openDate
ALTER TABLE cardInfo ADD CONSTRAINT CK_openMoney CHECK (openMoney >= 1)
ALTER TABLE cardInfo ADD CONSTRAINT CK_balance CHECK (balance >= 1)
ALTER TABLE cardInfo ADD CONSTRAINT CK_pass CHECK (LEN(pass)>=6)
ALTER TABLE cardInfo ADD CONSTRAINT DF_pass DEFAULT 666666 FOR pass
ALTER TABLE cardInfo ADD CONSTRAINT DF_IsReportLoss DEFAULT 0 FOR IsReportLoss
ALTER TABLE cardInfo ADD CONSTRAINT FK_customerID FOREIGN KEY (customerID) REFERENCES userInfo(customerID)
GO
ALTER TABLE transInfo ADD CONSTRAINT DF_transDate DEFAULT getdate() FOR transDate
ALTER TABLE transInfo ADD CONSTRAINT FK_cardID FOREIGN KEY (cardID) REFERENCES cardInfo(cardID)
ALTER TABLE transInfo ADD CONSTRAINT CK_transType CHECK (transType IN ('存入' ,'支取'))
ALTER TABLE transInfo ADD CONSTRAINT CK_transMoney CHECK (transMoney > 0)
GO
/*插入数据*/
INSERT INTO userInfo (customerName,PID,telephone,address) VALUES ('张三','123456789012345','0010-67898978','北京海淀')
INSERT INTO userInfo (customerName,PID,telephone) VALUES ('李四','321245678912345678','0478-44443333')
INSERT INTO cardInfo (cardID,savingType,openMoney,balance,customerID) VALUES ('1010 3576 1234 5678','活期',1000,1000,1)
INSERT INTO cardInfo (cardID,savingType,openMoney,balance,customerID) VALUES ('1010 3576 1212 1134','定期',1,1,2)
GO
/*张三取900,李四存5000*/
DECLARE @cidz VARCHAR(19),@cidl VARCHAR(19)
/*获取张三和李四的卡号*/
SELECT @cidz=cardID FROM cardInfo WHERE customerID= (SELECT customerID FROM userInfo WHERE customerName='张三')
SELECT @cidl=cardID FROM cardInfo WHERE customerID= (SELECT customerID FROM userInfo WHERE customerName='李四')
/*分别支取和存入*/
INSERT INTO transInfo (cardID,transType,transMoney) VALUES (@cidz,'支取',900)
UPDATE cardInfo SET balance=balance-900 WHERE cardID=@cidz
INSERT INTO transInfo (cardID,transType,transMoney) VALUES (@cidl,'存入',5000)
UPDATE cardInfo SET balance=balance+5000 WHERE cardID=@cidl
GO
/*===============常规业务模拟,修改密码===============*/
/*张三修改密码123456  李四修改密码123456*/
UPDATE cardInfo SET pass='123456' WHERE customerID= (SELECT customerID FROM userInfo WHERE customerName='张三')
UPDATE cardInfo SET pass='123123' WHERE customerID= (SELECT customerID FROM userInfo WHERE customerName='李四')
GO
/*李四卡挂失*/
UPDATE cardInfo SET IsReportLoss=1 WHERE customerID= (SELECT customerID FROM userInfo WHERE customerName='李四')
GO
/*统计银行的资金流通余额和盈利*/
DECLARE @sumin INT,@sumput INT
SELECT @sumin=SUM(transMoney) FROM transInfo WHERE transType='存入'
SELECT @sumput=SUM(transMoney) FROM transInfo WHERE transType='支取'
PRINT '银行流通余额总计为:'+CONVERT(VARCHAR(20),@sumin-@sumput)
PRINT '盈利结算为:'+CONVERT(VARCHAR(20),@sumput*0.008-@sumin*0.003)
GO
/*求本周内开户的银行卡号*/
SELECT cardID FROM cardInfo WHERE openDate > (getdate()-datepart(dw,getdate())+2)
/*查询本月交易金额最高的卡号*/
SELECT cardID,SUM(transMoney) FROM transInfo WHERE transType='支出' GROUP BY cardID
/*查询挂失帐号的客户信息*/
GO
SELECT * FROM userInfo WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1)
/*催款提醒业务*/
GO
SELECT 客户姓名=customerName,联系电话=telephone,帐上余额=balance FROM userInfo INNER JOIN cardInfo
  ON userInfo.customerID=cardInfo.customerID
    WHERE balance<200
GO
/*7创建所以和试图*/
/*给交易表的卡号cardID字段创建重复索引,以便加速查询,充填因为为70%*/
IF EXISTS (SELECT * FROM sysindexes WHERE name='index_cardID')
DROP INDEX transInfo.index_cardID
GO
CREATE NONCLUSTERED INDEX index_cardID
  ON transInfo(cardID)
GO
/*按指定索引查询 张三 (卡号 10103576 1212 1134)的交易记录*/
SELECT * FROM transInfo (INDEX=index_cardID) WHERE cardID='1010 3576 1212 1134'
/*创建试图方便客户查看*/
GO
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='view_userInfo')
DROP VIEW view_userInfo
GO
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='view_cardInfo')
DROP VIEW view_cardInfo
GO
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='view_transInfo')
DROP VIEW view_transInfo
GO
CREATE VIEW view_userInfo
  AS
    SELECT 客户编号=customerID,开户名=customerName,身份证号=PID,电话号码=telephone,居住地址=address FROM userInfo
GO
CREATE VIEW view_cardInfo
  AS
    SELECT 卡号=cardID,货币种类=curType,存款类型=savingType,开户日期=openMoney,余额=balance,密码=pass,是否挂失=IsReportLoss FROM cardInfo
GO
CREATE VIEW view_transInfo
  AS
    SELECT 交易日期=transDate,交易类型=transType,卡号=cardID,交易金额=transMoney,备注=remark FROM transInfo
GO

SELECT * FROM view_userInfo
SELECT * FROM view_cardInfo
SELECT * FROM view_transInfo
/*============创建触发器trig_trans============*/
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='trig_trans')
DROP TRIGGER trig_trans
GO
CREATE TRIGGER trig_trans
  ON transInfo
    FOR INSERT
      AS
    DECLARE @cardid VARCHAR(19),@money MONEY,@type VARCHAR(4)
    SELECT @cardid=cardID,@money=transMoney,@type=transType FROM inserted
    IF @type='存入'
    BEGIN
      UPDATE cardInfo SET balance=balance+@money WHERE cardID=@cardid
      PRINT '交易成功帐号余额'
      SELECT balance FROM cardInfo WHERE cardID=@cardid
    END
      ELSE
    BEGIN
      IF ((SELECT balance FROM cardInfo WHERE cardID=@cardid)-@money)>=1
      BEGIN
        UPDATE cardInfo SET balance=balance-@money WHERE cardID=@cardid
        PRINT '交易成功帐号余额'
        SELECT balance FROM cardInfo WHERE cardID=@cardid
      END
        ELSE
      BEGIN
        RAISERROR ('账户余额不足,交易失败',16,1)
        ROLLBACK TRAN
      END
    END
GO
/*===========测试触发器===============*/
INSERT INTO transInfo VALUES (DEFAULT,'1010 3576 1234 5678','支取',1000,DEFAULT)
GO
INSERT INTO transInfo VALUES (DEFAULT,'1010 3576 1212 1134','存入',200,DEFAULT)
GO
/*========创建存储过程,实现取钱和存钱============*/
IF EXISTS (SELECT * FROM sysobjects WHERE name='proc_takeMoney')
DROP PROC proc_takeMoney
GO
CREATE PROC proc_takeMoney
  @card VARCHAR(19),                --卡号
  @money MONEY,                    --交易金额
  @type VARCHAR(4),                --交易类型
  @pass VARCHAR(20) = NULL            --密码,默认空
    AS
      IF @type='支取'
      BEGIN
    IF ((SELECT pass FROM cardInfo WHERE cardID=@card)=@pass)
      INSERT INTO transInfo VALUES (DEFAULT,@card,@type,@money,DEFAULT)
        ELSE
          PRINT '密码输入错误,交易失败'
      END
      IF @type='存入'
    INSERT INTO transInfo VALUES (DEFAULT,@card,@type,@money,DEFAULT)
GO
DECLARE @acardid VARCHAR(19),@bcardid VARCHAR(19)
SELECT @acardid=cardID FROM cardInfo WHERE customerID=(SELECT customerID FROM userInfo WHERE customerName='张三')
EXEC proc_takeMoney @acardid,300,'支取','123356'
GO
DECLARE @bcardid VARCHAR(19)
SELECT @bcardid=cardID FROM cardInfo WHERE customerID=(SELECT customerID FROM userInfo WHERE customerName='李四')
EXEC proc_takeMoney @bcardid,500,'存入'
GO
/*产生随机卡号proc_randCardID*/
IF EXISTS (SELECT * FROM sysobjects WHERE name='proc_randCardID')
DROP PROC proc_randCardID
GO
CREATE PROC proc_randCardID
@randCardID VARCHAR(19) OUTPUT
  AS
    DECLARE @r numeric(15,8),@rd INT
    SELECT @r=RAND((DATEPART(mm,GETDATE())*10000+DATEPART(ss,GETDATE())*1000)+DATEPART(ms,GETDATE()))
    SELECT @rd=@r*100000000
    SELECT @randCardID='1010 3576 '+ LEFT(@rd,4)+' '+RIGHT(@rd,4)     
GO

DECLARE @randcardID VARCHAR(19)
EXEC proc_randCardID @randcardID OUTPUT
PRINT '随机卡号为:'+convert(varchar(19),@randcardID)
GO
/*开户存储过程proc_openAccount*/
IF EXISTS (SELECT * FROM sysobjects WHERE name='proc_openAccount')
DROP PROC proc_openAccount
GO
CREATE PROC proc_openAccount
@uname VARCHAR(10),            --开户姓名
@upid VARCHAR(18),            --身份证
@telephone VARCHAR(13),            --电话
@address VARCHAR(50) = NULL,        --地址
@money MONEY,                --开户金额
@type VARCHAR(4)            --开户类型
  AS
    DECLARE @ucid INT,@randcardid VARCHAR(19),@error INT
    SET @error=0
    IF EXISTS (SELECT * FROM userInfo WHERE PID=@upid)
    BEGIN
      RAISERROR ('此账户已经开户,请不要重复开户',16,1)
      RETURN
    END
    INSERT INTO userInfo VALUES (@uname,@upid,@telephone,@address)
    SET @error=@error+@@error
    SELECT @ucid=customerID FROM userInfo WHERE PID=@upid
    WHILE (1=1)
      BEGIN
    EXEC proc_randcardID @randcardid OUTPUT
        SET @error=@error+@@error
        IF NOT EXISTS (SELECT * FROM cardInfo WHERE cardID=@randcardid)
    BREAK
      END
    INSERT INTO cardInfo VALUES (@randcardid,DEFAULT,@type,DEFAULT,@money,@money,DEFAULT,DEFAULT,@ucid)
    SET @error=@error+@@error
    IF @error>0
    BEGIN
      PRINT '发生错误,开户失败'
      ROLLBACK TRANSACTION
    END
    PRINT '尊敬的客户,开户成功!系统为您产生的随机卡号为'+convert(varchar(19),@randcardid)
GO
/*===测试开户存储过程==========*/
EXEC proc_openAccount '王五','334456889012678','2222-63598978','河南新乡',1000,'活期'
EXEC proc_openAccount '赵二','213445678912342222','0760-44446666','河南新乡',1,'定期'
/*====================创建转账================*/
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='proc_transfer')
DROP PROC proc_transfer
GO
CREATE PROC proc_transfer
@cardout VARCHAR(19),                        --转出卡号
@cardin VARCHAR(19),                        --转入卡号
@money MONEY                            --金额
  AS
    BEGIN TRAN
      DECLARE @sumerror INT
      SET @sumerror=0
      INSERT INTO transInfo VALUES (DEFAULT,@cardout,'支取',@money,DEFAULT)
      SET @sumerror=@sumerror+@@error
      INSERT INTO transInfo VALUES (DEFAULT,@cardin,'存入',@money,DEFAULT)
      SET @sumerror=@sumerror+@@error
      IF @sumerror>0
      BEGIN
    PRINT '发生错误,转账失败,事务回滚'
        ROLLBACK TRAN
      END
    ELSE
      BEGIN
    COMMIT TRAN
    PRINT '提交成功'
      END
    SELECT @money=balance FROM cardInfo WHERE cardID=@cardout
    PRINT '卡号:'+convert(varchar(19),@cardout)+'    余额:'+convert(varchar(19),@money)
    SELECT @money=balance FROM cardInfo WHERE cardID=@cardin
    PRINT '卡号:'+convert(varchar(19),@cardin)+'    余额:'+convert(varchar(19),@money)
GO
/*测试转账事务*/
DECLARE @cardout VARCHAR(19),@cardin VARCHAR(19)
SELECT @cardout=cardID FROM cardInfo
  WHERE customerID=(SELECT customerID FROM userInfo WHERE customerName='李四')
SELECT @cardin=cardID FROM cardInfo
  WHERE customerID=(SELECT customerID FROM userInfo WHERE customerName='张三')
EXEC proc_transfer @cardout,@cardin,2000

SELECT * FROM view_cardInfo
SELECT * FROM view_transInfo

/*创建登录帐号*/
EXEC sp_addlogin 'sysAdmin','1234'           
EXEC sp_grantdbaccess 'sysAdmin','sysAdminDBUser'   
GRANT SELECT,INSERT,UPDATE,DELETE ON transInfo TO sysAdminDBUser