银行ATM取款机

syziy

论坛管理员

Rank: 27

UID: 1
帖子: 8252
精华: 2
威望: 12767
XB: 9395
经验: 14463 经验
宣传币: 134 GB
阅读权限: 255
在线时间: 714 小时
最后登录: 2008-12-5

热心天使勋章劳动模范勋章管理员勋章

发短消息
加为好友
当前在线

1^# 大中小发表于 2008-8-27 14:10 只看该作者

银行ATM取款机

/*--------统计银行的资金流通余额和盈利结算------------------------------*/
--统计说明:存款代表资金流入,取款代表资金.假定存款利率为千分之3,贷款利率为千分之81 @- t! }+ U4 j6 n- w
DECLARE @inMoney money
DECLARE @outMoney money
DECLARE @profit money9 M  q9 E1 V! a0 A8 E8 i
SELECT * FROM transInfo
SELECT @inMoney=sum(transMoney) FROM transInfo WHERE (transType='存入')  w3 C6 L5 D$ U1 @
SELECT @outMoney=sum(transMoney) FROM transInfo WHERE (transType='支取')' ]0 T( c- O% H2 `0 r' o
print '银行流通余额总计为:'+ convert(varchar(20),@inMoney-@outMoney)+'RMB'/ i; c$ u$ v0 c6 |0 B$ ^% x0 _: d
set @profit=@outMoney*0.008-@inMoney*0.0039 A0 ~3 c  K/ u" c. m
print '盈利结算为:'+ convert(varchar(20),@profit)+'RMB'* l) l' B& y6 z8 {% _
GO- g3 |# P! |* q" Q# E$ y
/*--------查询本周开户的卡号,显示该卡相关信息-----------------*/
SELECT * FROM cardInfo WHERE (DATEDIFF(Day,getDate(),openDate)<DATEPART(weekday,openDate))
- c; F& `) G! G7 Y$ K. q0 m
/*$$$$$$$$$$$$$索引和视图$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/- H3 b9 {7 o! W2 A  k' r2 k
--1.创建索引：给交易表的卡号cardID字段创建重复索引, W5 d3 B1 b, P* A, J3 L+ z
create NONCLUSTERED INDEX index_cardID ON transInfo(cardID)WITH FILLFACTOR=702 Q% ~. S4 \) b
--2.按指定索引查询张三（卡号为1010 3576 1212 1134）的交易记录
GO$ Y/ K2 v9 u0 f- a- }# s0 d
SELECT * FROM transInfo (INDEX=index_cardID) WHERE cardID='1010 3576 1234 5678'1 O3 Z$ o8 N6 S- q! \0 D
GO
--3.创建视图：为了向客户显示信息友好,查询各表要求字段全为中文字段名。% ^# X! J+ i2 v, T3 k- j
create VIEW view_userInfo --用户信息表视图
AS
select customerID as 客户编号,customerName as 开户名, PID as 身份证号,0 a* M, D/ a& J8 q% O; z
      telephone as 电话号码,address as 居住地址 from userInfo
GO3 o3 S3 T; s/ s5 c
2 B0 Q- {1 i9 q2 p: p- p3 c
1 O: b/ ?' p8 f$ z& x  o6 w
/*$$$$$$$$$$$$$触发器$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/
--改进上述的存款或取款语句，当存钱或取钱（如500元）时候，会往交易信息表transInfo中添加一条交易记录，同时会自动更新用户信息表：userInfo中的现有金额的变化（如增加/减少500元）4 t" I4 j! D4 J9 n$ @- }# D
--drop trigger trig_trans9 W/ G. j  R& a: \8 R3 D
CREATE TRIGGER trig_trans ON transInfo FOR INSERT
AS
DECLARE @myTransType char(4),@outMoney MONEY,@myCardID char(19)
SELECT @myTransType=transType,@outMoney=transMoney ,@myCardID=cardID FROM inserted+ P, Z; [0 ]; Q; h* x
DECLARE @mybalance money) H8 O" L4 p6 {4 t
SELECT @mybalance=balance FROM cardInfo WHERE cardID=@myCardID
if (@myTransType='支取') 8 b6 g" Y- s1 v% {# H+ V, J0 l
   if (@mybalance>=@outMoney+1)//余额至少为1元，否则认为销户
         update cardInfo set balance=balance-@outMoney WHERE cardID=@myCardID+ z+ D$ a$ M3 q! A
   else
      begin
         raiserror ('交易失败！余额不足！',16,1); T) R  s7 O, |% m% k
         rollback tran2 ~7 W; m( }( v* j- `( O
         print '卡号'+@myCardID+' 余额：'+convert(varchar(20),@mybalance) % q- ]3 S) r/ x$ O$ J6 f
      end% ^0 J0 }- l' h
else
      update cardInfo set balance=balance+@outMoney WHERE cardID=@myCardID' r: P! g  P% i
print '交易成功！交易金额：'+convert(varchar(20),@outMoney)3 m+ k! j, r6 p/ t9 L  p# g
SELECT @mybalance=balance FROM cardInfo WHERE cardID=@myCardID; ^1 K& {% ~% B9 j
print '卡号'+@myCardID+' 余额：'+convert(varchar(20),@mybalance)
GO
--测试触发器：张三的卡号支取1000，李四的卡号存入200
--现实中的取款机依靠读卡器读出张三的卡号,这里根据张三的名字查出卡号来模拟
declare @card char(19)& O$ ?0 w2 T0 s$ s. x- g6 B2 ^3 H  Z
select @card=cardID from cardInfo Inner Join userInfo ON
cardInfo.customerID=userInfo.customerID where customerName='张三'" s, Y* ]( x* p/ L3 c
INSERT INTO transInfo(transType,cardID,transMoney) VALUES('支取',@card,1000)
GO
declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo ON . Y4 S6 ~2 H/ e8 |' d# X3 e
cardInfo.customerID=userInfo.customerID where customerName='李四'
INSERT INTO transInfo(transType,cardID,transMoney) VALUES('存入',@card,200)- L* b, I# }- c" `2 K; f' Q. }2 q
GO

/*$$$$$$$$$$$$$$$$$$$$$$存储过程$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/
--1.取钱或存钱的存储过程9 U5 y% h* q6 C1 ^- A4 a0 U
--drop proc proc_takeMoney: P; d' i2 q' y* }
create procedure proc_takeMoney : d. P. a+ Z" s8 c4 T8 z
@card char(19),' j' @7 q; r; C2 i. e* E
@m money,
@type char(4),
@inputPass char(6)=' '0 X, w; G& a/ O. o9 ^: X
AS) z. @  C4 S. p" S9 B! a' q+ R
print '交易正进行,请稍后......'! e) g1 t" b& i+ k* M  m( H
if (@type='支取')
   if ((SELECT pass FROM cardInfo WHERE cardID=@card)<>@inputPass )- Z7 b7 p5 _9 B( Z! Q2 h: G/ E
      begin0 K+ }' o7 N' `/ h
         raiserror ('密码错误!',16,1)
         return % J- G5 u6 ^' ?1 t7 ^7 i
      end
INSERT INTO transInfo(transType,cardID,transMoney) VALUES(@type,@card,@m)
GO
$ f/ ~- O0 J3 p& u/ F
--2.调用存储过程取钱或存钱张三取300，李四存500
--现实中的取款机依靠读卡器读出张三的卡号,这里根据张三的名字查出考号来模拟
declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo ON
cardInfo.customerID=userInfo.customerID where customerName='张三'
EXEC proc_takeMoney @card,300 ,'支取','123456'   \+ I, G, n3 ~% q5 H
GO
declare @card char(19)7 B4 a& r" W; w: w( q3 I8 Z
select @card=cardID from cardInfo Inner Join userInfo ON & p! q' J$ K  @- ]9 s& k- ~
cardInfo.customerID=userInfo.customerID where customerName='李四'
EXEC proc_takeMoney @card,500 ,'存入'
select * from view_cardInfo# R' X& ^! G0 x  i) Q2 O
select * from view_transInfo
GO

--3.产生随机卡号的存储过程(一般用当前月份数\当前秒数\当前毫秒数乘以一定的系数作为随机种子)
--drop proc proc_randCardID
create procedure proc_randCardID @randCardID char(19) OUTPUT
AS( Y( ^8 d; @& ]8 U/ T  Z% ]
DECLARE @r numeric(15,8)
DECLARE @tempStr char(10)
SELECT @r=RAND((DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )
               + DATEPART(ms, GETDATE()) )( S8 V% A; C3 v6 \7 T
set @tempStr=convert(char(10),@r) --产生0.xxxxxxxx的数字,我们需要小数点后的八位数字 / ]' u0 w) C# z- v7 n; b, [
set @randCardID='1010 3576 '+SUBSTRING(@tempStr,3,4)+' '+SUBSTRING(@tempStr,7,4) --组合为规定格式的卡号
GO
--4.测试产生随机卡号& X* H+ D3 q: [  V# N4 I, G
DECLARE @mycardID char(19)
EXECUTE proc_randCardID @mycardID OUTPUT& l( ^4 p7 Q$ m1 {
print '产生的随机卡号为：'+@mycardID8 a" N& I( B5 e( i. ^' W
GO0 T8 Z7 k' ~* Z* a$ \1 J

--5.开户的存储过程1 R" G- s# S+ s0 y/ l' W% ^# p
--drop proc proc_openAccount. g9 n5 @2 C; V' y& h
create procedure proc_openAccount @customerName char(8),@PID char(18),@telephone char(13)1 s* z. k8 y3 S1 M
   ,@openMoney money,@savingType char(8),@address varchar(50)=' '
AS
   DECLARE @mycardID char(19),@cur_customerID int 5 N2 s. H/ r; P0 r' b) f  _
   --调用产生随机卡号的存储过程获得随机卡号
   EXECUTE proc_randCardID @mycardID OUTPUT: C# E$ }; O' |) {! }
   while exists(SELECT * FROM cardInfo WHERE cardID=@mycardID)
      EXECUTE proc_randCardID @mycardID OUTPUT
   print '尊敬的客户,开户成功!系统为您产生的随机卡号为:'+@mycardID- f& x6 h. \3 U$ j1 x
   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) # {3 y0 f; U/ N$ U4 Y
   select @cur_customerID=customerID from userInfo where PID=@PID
   INSERT INTO cardInfo(cardID,savingType,openMoney,balance,customerID)
      VALUES(@mycardID,@savingType,@openMoney,@openMoney,@cur_customerID)
   3 [1 w+ }* t  r/ `1 y; O* L
GO: Q) M$ y9 S: B" b! m% c
--6.调用存储过程重新开户
EXEC proc_openAccount '王五','334456889012678','2222-63598978',1000,'活期','河南新乡'
EXEC proc_openAccount '赵二','213445678912342222','0760-44446666',1,'定期' * [% \3 _3 {7 ^
select * from view_userInfo) A1 B: D3 r( |
select * from view_cardInfo
GO) Q: Z: f8 A+ M% v# E. w' y' ]9 a
/*$$$$$$$$$$$$$$$$$$$$$$事务$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/+ \2 ^; O5 O9 B* D- R) [
--1.转帐的事务存储过程# `9 D2 I' v9 a  e" F2 K
create procedure proc_transfer @card1 char(19),@card2 char(19),@outmoney money
AS9 C; j9 y3 R( U; Y( G% ^, k
begin tran; f# S- p  \3 g8 t( f* A& X
   print '开始转帐,请稍后......'7 b; e) Z( K6 d* y2 V9 i7 {
   DECLARE @errors int
   set @errors=0
   INSERT INTO transInfo(transType,cardID,transMoney) VALUES('支取',@card1,@outmoney)- x) O$ u' i; }
   set @errors=@errors+@@error; @( [# J3 E, G$ ^3 @8 Z8 R0 N
   INSERT INTO transInfo(transType,cardID,transMoney) VALUES('存入',@card2,@outmoney)
   set @errors=@errors+@@error
   if (@errors>0)2 L4 a0 J* l3 d1 [% \
      begin" X! r: `; |4 G% ~# j' D$ e% K* |
      print '转帐失败！'
      rollback tran
      end
   else& h. y/ ]3 S3 ~! \+ Y: T" x
      begin4 A  W6 _! a3 _6 `3 e6 e( l5 b- o
      print '转帐成功！'
      commit tran& m. m+ h$ X: h5 v
      end; X3 f4 Q" E( I- G6 [
GO4 i5 Z5 ^6 e) F0 g0 l) X
--2.测试上述事务存储过程
--从李四的帐户转帐2000到张三的帐户
--同上一样,现实中的取款机依靠读卡器读出张三/李四的卡号,这里根据张三/李四的名字查出考号来模拟6 z0 |: `! l6 e# w
declare @card1 char(19),@card2 char(19)6 c* r- ~9 g3 {
select @card1=cardID from cardInfo Inner Join userInfo ON 8 `7 |3 P6 y2 o$ c" [! h
cardInfo.customerID=userInfo.customerID where customerName='李四'; X# p9 h( V9 O. U' V, h
select @card2=cardID from cardInfo Inner Join userInfo ON
cardInfo.customerID=userInfo.customerID where customerName='张三'
--调用上述事务过程转帐
EXEC proc_transfer @card1,@card2,2000
select * from view_userInfo
select * from view_cardInfo& {* _1 g, Z$ C9 z
select * from view_transInfo
GO
/*$$$$$$$$$$$$$$$$$$$$$$安全$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/
--1.添加SQL登录帐号
If not exists(SELECT * FROM master.dbo.syslogins WHERE loginname='sysAdmin')
begin
   EXEC sp_addlogin 'sysAdmin', '1234' --添加SQL登录帐号
   EXEC sp_defaultdb 'sysAdmin' , 'bankDB' --修改登录的默认数据库为bankDB, k  {& s- Q$ n. y. U
end1 f; ?. b5 t  ?( h  X0 a1 k
go
--2.创建数据库用户 ) C5 r5 c) s- G0 e5 ^
EXEC sp_grantdbaccess 'sysAdmin', 'sysAdminDBUser'
GO
--3.--------给数据库用户授权 ( J- d6 G; |) F2 [9 z% D: J
--为sysAdminDBUser分配对象权限(增删改查的权限)
GRANT SELECT,insert,update,delete,select ON transInfo TO sysAdminDBUser
GRANT SELECT,insert,update,delete,select ON userInfo TO sysAdminDBUser
GRANT SELECT,insert,update,delete,select ON cardInfo TO sysAdminDBUser
GO

本文章地址：http://www.110xs.com/thread-8773-1-1.html

收藏本文至： 点击这里，复制这篇文章的地址推荐给QQ或者MSN上的朋友！

请尊重我们的劳动成果！请原谅我们把部分帖子设置为“回复才可以查看”

syziy推广:
来听书-免费有声小说在线试听:http://www.laits.cn
syziy的博客:http://syziy.blog.163.com
syziy的网店:http://youa.baidu.com/shop/0344b6732ae57fc92132cadb
小说-传奇黑客: http://www.qidian.com/Book/1059802.aspx

TOP

‹‹ 上一主题 | 下一主题 ››