PPT《问题描述》

1010

3576 1234 5678'

, '

活期'

,1000,1000,1)INSERT INTO userInfo(customerName,PID,telephone) VALUES('

李四'

,'

321245678912345678'

,'

0478-44443333'

)INSERT INTO cardInfo(cardID,savingType,openMoney,balance, customerID) VALUES('

1010

3576 1212 1134'

,'

定期'

,1,1,2)SELECT * FROM userInfoSELECT * FROM cardInfoGO 第三阶段标准代码演示-2 张三的卡号取款900元,李四的卡号存款5000元 交易信息表插入交易记录-INSERT INTO transInfo(transType,cardID,transMoney) VALUES('

支取'

,'

1010

3576 1234 5678'

,900)更新银行卡信息表中的现有余额-UPDATE cardInfo SET balance=balance-900 WHERE cardID='

1010

3576 1234 5678'

交易信息表插入交易记录-INSERT INTO transInfo(transType,cardID,transMoney) VALUES('

存入'

,'

1010

3576 1212 1134'

,5000)更新银行卡信息表中的现有余额-UPDATE cardInfo SET balance=balance+5000 WHERE cardID='

1010

3576 1212 1134'

GO 第三阶段标准代码演示-3 修改密码和挂失账号 修改密码-1.张三(卡号为1010

3576 1234 5678)修改银行卡密码为123456--2.李四(卡号为1010

3576 1212 1134)修改银行卡密码为123123update cardInfo set pass='

123456'

WHERE cardID='

1010

3576 1234 5678'

update cardInfo set pass='

123123'

WHERE cardID='

1010

3576 1212 1134'

SELECT * FROM cardInfo/李四的卡号挂失 update cardInfo set IsReportLoss=1 WHERE cardID='

1010

3576 1212 1134'

SELECT * FROM cardInfoGO 第三阶段标准代码演示-4 统计银行的资金流通余额和盈利结算 DECLARE @inMoney moneyDECLARE @outMoney moneyDECLARE @profit moneySELECT @inMoney=sum(transMoney) FROM transInfo WHERE (transType='

存入'

)SELECT @outMoney=sum(transMoney) FROM transInfo WHERE (transType='

支取'

)print '

银行流通余额总计为:'

+ convert(varchar(20)inMoney-@outMoney)+'

RMB'

set @profit=@outMoney*0.008-@inMoney*0.003print '

盈利结算为:'

+ convert(varchar(20),@profit)+'

RMB'

GO 第三阶段标准代码演示-5 其他操作 查询本周开户的卡号,显示该卡相关信息-SELECT * FROM cardInfo WHERE (DATEDIFF(Day,getDate(),openDate)0)begin print '

转账失败!rollback tran end else begin print '

转账成功!'

commit tran endGO --调用上述事务过程转账declare @card1 char(19),@card2 char(19)select @card1=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName='

李四'

select @card2=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInf........