ACCP7.0优化myschool数据库设计第九章项目

--用例4update cardInfo set pass=123456 where cardID='1010 3576 1234 5678'update cardInfo set pass=123123 where cardID='1010 3576 1212 1134'goupdate cardInfo set IsReportLoss=1 where cardID='1010 3576 1212 1134'godeclare @inmoney moneydeclare @outmoney moneyselect @inmoney=SUM(trademoney) from tradeInfo where tradeType='存入'select @outmoney=SUM(trademoney) from tradeInfo where tradeType='支取'print '银行流通余额总计为:'+convert(varchar(20),@inmoney-@outmoney)+'RMB'print '盈利结算为:'+convert(varchar(20),@outmoney*0.008-@inmoney*0.003)+'RMB'goselect * from cardInfo where DATEDIFF(week,opendate,GETDATE())=0goselect * from tradeInfo where tradeMoney=(select MAX(tradeMoney) from tradeInfo where tradeType='存入' and DATEDIFF(MONTH,tradeDate,GETDATE())<=1)select * from tradeInfo where tradeMoney=(select MAX(tradeMoney) from tradeInfo where tradeType='支取' and DATEDIFF(MONTH,tradeDate,GETDATE())<=1)goselect * from userInfo where customerID in(select customerID from cardInfo where IsReportLoss=1)goselect customername from userInfo inner join cardInfo on cardInfo.customerID=userInfo.customerID where balance<200go--用例5gocreate view vw_userinfo as select customerID as 客户编号,customerName as 开户名, PID as 身份证号,        telephone as 电话号码,address as 居住地址  from userInfo        goSELECT * FROM vw_userInfogocreate VIEW vw_cardInfo  AS     select c.cardID as 卡号,u.customerName as 客户,c.curID as 货币种类, d.savingName as 存款类型,c.openDate as 开户日期,       c.balance as 余额,c.pass 密码,       case c.IsReportLoss when 0 then '挂失'                           when 1 then '正常'       end as 是否挂失    from cardInfo c, deposit d,userinfo u    where c.savingID=d.savingID and c.customerID=u.customerIDgoSELECT * FROM vw_cardInfogocreate VIEW vw_tradeInfo  AS     select tradeDate as 交易日期,tradeType as 交易类型, cardID as 卡号,tradeMoney as 交易金额,      remark as 备注  from tradeInfo goSELECT * FROM vw_tradeInfogo--用例6

create procedure usp_takeMoney   @card char(19),  @m money,  @type char(4),  @inputPass char(6)='' AS   print '交易正进行,请稍后......'   if (@type='支取')      if ((SELECT pass FROM cardInfo WHERE cardID=@card)<>@inputPass )         begin           raiserror ('密码错误!',16,1)           return -1         end    DECLARE @mytradeType char(4),@outMoney MONEY,@myCardID char(19)    SELECT @mytradeType=tradeType,@outMoney=tradeMoney ,@myCardID=cardID FROM tradeInfo where cardID=@card    DECLARE @mybalance money    SELECT @mybalance=balance FROM cardInfo WHERE cardID=@card    if (@type='支取')        if (@mybalance>=@m+1)           update cardInfo set balance=balance-@m WHERE cardID=@myCardID       else          begin            raiserror ('交易失败！余额不足！',16,1)                        print '卡号'+@card+'  余额：'+convert(varchar(20),@mybalance)               return -2          end    else         update cardInfo set balance=balance+@m WHERE cardID=@card    print '交易成功！交易金额：'+convert(varchar(20),@m)    SELECT @mybalance=balance FROM cardInfo WHERE cardID=@card    print '卡号'+@card+'  余额：'+convert(varchar(20),@mybalance) INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES(@type,@card,@m)     RETURN 0GOdeclare @card char(19)select @card=cardID from cardInfo Inner Join userInfo ON    cardInfo.customerID=userInfo.customerID where customerName='张三'EXEC usp_takeMoney @card,10 ,'支取','123456' GOselect * from cardInfo Inner Join userInfo ON    cardInfo.customerID=userInfo.customerID where customerName='张三'declare @card char(19)select @card=cardID from cardInfo Inner Join userInfo ON    cardInfo.customerID=userInfo.customerID where customerName='李四'EXEC usp_takeMoney @card,500 ,'存入'select * from vw_cardInfoselect * from vw_tradeInfoGOif exists (select * from sysobjects where name = 'usp_randCardID')drop proc usp_randCardIDgocreate procedure usp_randCardID @randCardID char(19) OUTPUT  AS    DECLARE @r numeric(15,8)     DECLARE @tempStr  char(10)    SELECT  @r=RAND((DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )                  + DATEPART(ms, GETDATE()) )    set @tempStr=convert(char(10),@r)     set @randCardID='1010 3576 '+SUBSTRING(@tempStr,3,4)+' '+SUBSTRING(@tempStr,7,4)  --组合为规定格式的卡号GO--测试产生随机卡号DECLARE @mycardID char(19) EXECUTE usp_randCardID @mycardID OUTPUTprint '产生的随机卡号为：'+@mycardIDGOif exists (select * from sysobjects where name = 'usp_openAccount')drop proc usp_openAccountGOcreate procedure usp_openAccount @customerName char(8),@PID char(18),@telephone char(13)     ,@openMoney money,@savingName char(8),@address varchar(50)='' AS   DECLARE @mycardID char(19),@cur_customerID int, @savingID int    --调用产生随机卡号的存储过程获得随机卡号   EXECUTE usp_randCardID @mycardID OUTPUT   while  exists(SELECT * FROM cardInfo WHERE cardID=@mycardID)       EXECUTE usp_randCardID @mycardID OUTPUT   print '尊敬的客户,开户成功!系统为您产生的随机卡号为:'+@mycardID   print '开户日期'+convert(char(10),getdate(),111)+'  开户金额:'+convert(varchar(20),@openMoney)   IF not exists(select * from userInfo where PID=@PID)       INSERT INTO userInfo(customerName,PID,telephone,address )          VALUES(@customerName,@PID,@telephone,@address) SELECT @savingID = savingID FROM deposit WHERE savingName =@savingNameif @savingID is NULL BEGIN  RAISERROR('存款类型不正确,请重新输入!',16,1)  RETURN -1 END    select @cur_customerID=customerID from userInfo where PID=@PID    INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)         VALUES(@mycardID,@savingID,@openMoney,@openMoney,@cur_customerID)GO--调用存储过程重新开户EXEC usp_openAccount '王五','334456889012678','2222-63598978',1000,'活期','河南新乡' EXEC usp_openAccount '赵二','213445678912342222','0760-44446666',1,'定期' select * from vw_userInfoselect * from vw_cardInfoGOif exists (select * from sysobjects where name = 'usp_CheckSheet')drop proc usp_CheckSheetGOCREATE PROCEDURE usp_CheckSheet  @cardID varchar(19),  @date1 datetime=NULL,  @date2 datetime=NULLASDECLARE @custName varchar(20)DECLARE @curName varchar(20)DECLARE @savingName varchar(20)DECLARE @openDate datetimeSELECT @cardID=c.cardID, @curName=c.curID, @custName=u.customerName,   @savingName=d.savingName , @openDate=c.openDate FROM cardInfo c, userInfo u, deposit dWHERE c.customerID=u.customerID and c.savingID = d.savingID and cardID = @cardID --and u.customerName = user_name()PRINT '卡号：' + @cardIDPRINT '姓名:' + @custNamePRINT '货币:' + @curNamePRINT '存款类型：' + @savingNamePRINT '开户日期：' + CAST(DATEPART(yyyy,@openDate) AS VARCHAR(4))+'年' + CAST(DATEPART(mm,@openDate) AS VARCHAR(2))+'月' + CAST(DATEPART(dd,@openDate) AS VARCHAR(2))+'日'PRINT ' 'print '--------------------------------------------------------------------'    IF @date1 IS NULL AND  @date2 IS NULL       BEGINSELECT tradeDate 交易日, tradeType 类型, tradeMoney 交易金额, remark 备注FROM tradeInfoWHERE cardID='1010 3576 1212 1134'--@cardIDORDER BY tradeDateRETURN      END    ELSE IF @date2 IS NULL       SET @date2 = getdate()      SELECT tradeDate 交易日, tradeType 类型, tradeMoney 交易金额, remark 备注FROM tradeInfoWHERE cardID=@cardID AND tradeDate BETWEEN @date1 AND @date2ORDER BY tradeDateGO--测试打印对帐单EXEC usp_CheckSheet '1010 3576 1212 1134'EXEC usp_CheckSheet '1010 3576 1212 1134','2009-11-2','2009-11-30'if exists (select * from sysobjects where name = 'usp_pagingDisplay')DROP PROCEDURE usp_pagingDisplayGOCREATE PROCEDURE usp_pagingDisplay  @records int = 10,  @page int = 1AS  SET NOCOUNT ON  DECLARE @rec1 int  SET @rec1 = @records  DECLARE @rec2 int  SET @rec2 = (@page - 1) * @records  DECLARE @statement nvarchar(200)  SET @statement='SELECT TOP ' + CAST(@rec1 AS varchar(10)) + ' tradeDate 交易日期,tradeType 交易类型,cardID 卡号,trademoney 交易金额 FROM tradeInfo WHERE cardID not in (SELECT TOP '+ CAST(@rec2 AS varchar(10)) + ' cardID FROM tradeInfo)'   EXEC SP_EXECUTESQL @statement,N'@rec1 int,@rec2 int',@rec1,@rec2GO--EXEC usp_pagingDisplay 2,2if exists (select * from sysobjects where name = 'usp_getWithoutTrade')drop proc usp_getWithoutTradeGOcreate procedure usp_getWithoutTrade  @Num int output,  @Amount decimal(18,2) output,  @date1 datetime = NULL,  @date2 datetime = NULLAS  IF @date1 IS NULL  BEGINdeclare @dateStr varchar(50)set @dateStr = convert(varchar(4),DATEPART(YY,GETDATE())) + '-'+convert(varchar(2),DATEPART(mm,GETDATE())) + '-1 00:00:00.000' set @date1 = convert(datetime, @datestr,101)  END  IF @date2 IS NULLSET @date2 = getdate()  SELECT distinct u.customerID 客户号,u.customerName 客户姓名,u.PID 身份证号,u.telephone 电话,address 地址   FROM userInfo u  JOIN cardInfo c ON u.customerID = c.customerID  WHERE c.cardID NOT IN (SELECT cardID FROM tradeInfo WHERE tradeDate Between @date1 and @date2)  SELECT @Num=COUNT(customerID), @Amount=SUM(balance)  FROM cardInfo  WHERE cardID NOT IN (SELECT cardID FROM tradeInfo WHERE tradeDate Between @date1 and @date2)GODECLARE @NUM intDECLARE @Amount decimal(18,2)DECLARE @date1 datetimeDECLARE @date2 datetimeSET @date1 = '2009-1-1'SET @date2 = getdate()EXEC usp_getWithoutTrade @NUM OUTPUT, @Amount OUTPUT--, @date1, @date2PRINT '统计未发生交易的客户'PRINT '---------------------------------------'PRINT '客户人数：' + CAST(@NUM AS varchar(10)) + '  客户总余额：' + CAST(@Amount AS varchar(20))if exists (select * from sysobjects where name = 'usp_getTradeInfo')drop proc usp_getTradeInfoGOcreate procedure usp_getTradeInfo  @Num1 int output,  @Amount1 decimal(18,2) output,  @Num2 int output,  @Amount2 decimal(18,2) output,  @date1 datetime,  @date2 datetime = NULL,  @address varchar(20) = NULLAS  -- 初始化变量  SET @Num1 = 0  SET @Amount1 = 0  SET @Num2 = 0  SET @Amount2 = 0  IF @date2 IS NULLSET @date2 = getdate()  IF @address IS NULL   BEGIN  SELECT @Num1=COUNT(tradeMoney), @Amount1=SUM(tradeMoney)  FROM tradeInfo  WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='存入'  SELECT @Num2=COUNT(tradeMoney), @Amount2=SUM(tradeMoney)  FROM tradeInfo  WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='支取'    END  ELSE   BEGIN  SELECT @Num1=COUNT(tradeMoney), @Amount1=SUM(tradeMoney)  FROM tradeInfo JOIN cardInfo ON tradeInfo.cardID = cardInfo.cardID           JOIN userInfo ON cardInfo.customerID = userInfo.customerID  WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='存入'        AND address Like '%'+@address+'%'  SELECT @Num2=COUNT(tradeMoney), @Amount2=SUM(tradeMoney)  FROM tradeInfo JOIN cardInfo ON tradeInfo.cardID = cardInfo.cardID           JOIN userInfo ON cardInfo.customerID = userInfo.customerID  WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='支取'        AND address Like '%'+@address+'%'    ENDGODECLARE @CNT1 intDECLARE @Total1 decimal(18,2)DECLARE @CNT2 intDECLARE @Total2 decimal(18,2)DECLARE @date1 datetimeDECLARE @date2 datetimeSET @date1 = '2009-1-1'SET @date2 = getdate()EXEC usp_getTradeInfo @CNT1 OUTPUT, @Total1 OUTPUT, @CNT2 OUTPUT, @Total2 OUTPUT, @date1, @date2--, '北京'PRINT '统计银行卡交易量和交易额'PRINT ''PRINT '起始日期：' + CONVERT(varchar(10),@date1,102) +  '  截止日期：' + CONVERT(varchar(10),@date2,102)PRINT '-----------------------------------------------------------'PRINT '存入笔数：' + CAST(@CNT1 AS varchar(20)) + '  存入金额：' + CAST(@Total1 AS varchar(20))PRINT '支取笔数：' + CAST(@CNT2 AS varchar(20)) + '  支取金额：' + CAST(@Total2 AS varchar(20))PRINT '-----------------------------------------------------------'PRINT '发生笔数：' + CAST(@CNT1+@CNT2 AS varchar(20)) + '  结余金额：' + CAST(@Total1-@Total2 AS varchar(20))