|
|
这个是北大青鸟 第2学期 项目实战 第一章 ATM代码 自写完整版
/*
Power by YangMin 2007-０５-０１
转载请标明出处，谢谢！
*/
--1:Power design 建立模型 略
--2：建立数据库
exec xp_cmdshell 'mkdir e:\db\bank',no_output
go
if exists(select * from sysdatabases where name='bankDB' )
drop database bankDB
go
create database bankDB
on primary
(
name='bankDB',
filename='e:\db\bank\bankDB.mdf',
size=5mb,
maxsize=100mb,
filegrowth=15%
)
log on
(
name='bankDB_log',
filename='e:\db\bank\bankDB.ldf',
size=2mb,
filegrowth=1mb
)
GO
--3：建立3个表
--4：建立约束，建立关系
Use bankdb
go
--drop table userInfo
create table userInfo
(
customerID int identity(1,1) primary key,
customerName varchar(50) not null,
PID varchar(20) not null unique check (len(PID)=18 or len(PID)=15),
telephone varchar(20) not null 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][0-9]' or len(telephone)=11),
address varchar(200)
)
go
--drop table cardInfo
create table cardInfo
(
cardID varchar(30) not null primary key ,
curType varchar(10) not null default ('RMB'),
savingType varchar(10) check (savingType in('活期','定活两便','定期')),
openDate datetime default getdate(),
openMoney money check (openMoney>=1),
balance money check (balance>=1),
pass char(6) not null default '888888',
IsReportLoss char(2) default '否' check(IsReportLoss in('是','否')),
customerID int foreign key references userInfo(customerID)
)
go
--drop table transInfo
create table transInfo
(
transDate datetime not null default getdate(),
cardID varchar(30) not null foreign key references cardInfo(cardID),
transType char(4) check(transType in ('存入','支取')),
transMoney money not null check(transMoney >0),
remark varchar(100)
)
go
/*
sp_help userInfo
delete from userInfo
truncate table userInfo
*/
--5：插入测试数据
insert into UserInfo(customerName,PID,telephone,address)
values('张三','123456789012345','0010-67898978','北京海淀')
insert into UserInfo(customerName,PID,telephone)
values('李四','321245678912345678','0478-44443333')
--select * from userInfo
--sp_help cardInfo
--delete from cardinfo
insert into cardInfo(cardID,curType,savingType,openDate,openMoney,balance,customerID)
values('1010 3576 1212 1134','RMB','定期','2005-9-1',1,1,2)
insert into cardInfo(cardID,curType,savingType,openDate,openMoney,balance,customerID)
values('1010 3576 1234 5678','RMB','活期','2005-9-1',1000,1000,1)
insert into transInfo(transDate,transType,cardID,transMoney)values('2005-9-1','支取','1010 3576 1212 1134',900)
insert into transInfo(transDate,transType,cardID,transMoney)values('2005-9-1','存入','1010 3576 1234 5678',5000)
--6：常规业务模拟
--修改用户密码
update cardinfo set pass='123456' where customerid=1
update cardinfo set pass='123123' where customerid=2
--统计应行资金流通余额和盈利结算
declare @inmoney money,@outmoney money
select @inmoney=sum(transmoney) from transinfo where transtype='存入'
select @outmoney=sum(transmoney) from transinfo where transtype='支取'
print '银行流通余额总计为：'+convert(varchar(20),@inmoney-@outmoney)+'RMB'
print '盈利结算为：'+convert(varchar(20),@outmoney*0.008-@inmoney*0.008)+'RMB'
/*
银行流通余额总计为：3600.00RMB
盈利结算为：-28.8000000RMB
*/
update cardinfo set balance=900 where cardid='1010 3576 1234 5678'
update cardinfo set balance=5001 where cardid='1010 3576 1212 1134'
--查询本月交易最高的卡号
select top 1 cardid,sum(transmoney) as 交易总金额 from transinfo
where datediff(day,transdate,getdate())<=7
group by cardid
order by 2 desc
--查询挂失
select customername as 客户姓名,telephone as 联系电话 from userinfo u inner join cardinfo c
on u.customerid=c.customerid where isreportloss='是'
--催款业务提醒
select customername as 客户姓名,telephone as 联系电话,balance as 卡上余额
from userinfo u inner join cardinfo c on u.customerid=c.customerid
where balance<=200
/*
select * from userinfo
select * from cardinfo
select * from transInfo
*/
--7：创建索引和视图
--创建索引
CREATE INDEX card_id_ind
ON transinfo (cardid)
WITH FILLFACTOR = 70
GO
select * from transinfo where cardid='1010 3576 1212 1134'
--创建视图
create view v_userinfo
as
select customerID as 客户编号,customername as 开户名,pid as 身份证号,telephone as 电话号码,address as 居住地址 from userinfo
go
select * from v_userinfo
go
create view v_cardinfo
as
select cardid as 卡号,curtype as 货币种类,savingtype as 存款类型,opendate as 开户日期,balance as 余额,pass as 密码,isreportloss as 是否挂失,customerid as 客户编号 from cardinfo
go
select * from v_cardinfo
go
create view v_transInfo
as
select transdate as 交易日期,transtype as 交易类型,cardid as 卡号,transmoney as 交易金额,remark as 备注 from transInfo
go
select * from v_transInfo
/*
sp_help transinfo
sp_help cardinfo
select * from userinfo
select * from cardinfo
select * from transinfo
delete from transinfo where transmoney=200
*/
--8：创建触发器
--交易时自动更新 帐户余额
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig_trans' AND type = 'TR')
DROP TRIGGER trig_trans
GO
create trigger trig_trans on transInfo for insert
as
declare @myTransType char(4),@outMoney money,@myCardID varchar(30),@zhi_balance money
select @myTransType=transType,@outMoney=transMoney,@myCardID=cardID from inserted
if(@myTranstype='存入')
begin
update cardinfo set balance =balance+@outMoney where cardid=@mycardid
print '交易成功,存入'+convert(varchar(15),@outMoney)+'元'
end
else
begin
select @zhi_balance=balance-@outMoney from cardinfo where cardid=@mycardid
if(@zhi_balance<=0)
begin
raiserror('余额不足！',16,1)
rollback tran
end
else
begin
update cardinfo set balance =balance-@outMoney where cardid=@mycardid
print '交易成功,支取'+convert(varchar(15),@outMoney)+'元'
end
end
--9：测试触发器
--张三支取1000，张三这时候只有900
insert into transInfo(transDate,transType,cardID,transMoney)values('2005-9-1','支取','1010 3576 1234 5678',1000)
/*
服务器: 消息 50000，级别 16，状态 1，过程 trig_trans，行 14
余额不足！
*/
--李四存200 存之前有5001，存之后有5201
--update cardinfo set balance=5001 where customerid=2
insert into transInfo(transDate,transType,cardID,transMoney)values('2005-9-1','存入','1010 3576 1212 1134',200)
/*
select * from userinfo
select * from cardinfo
select * from transinfo
*/
--10：创建存储过程
--验证用户密码进行取钱 和存钱
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc_takeMoney' AND type = 'P')
DROP PROCEDURE proc_takeMoney
GO
create proc proc_takeMoney
@card char(19),@m money,@type char(4),@inputpass char(6)='
as
if exists(select * from cardinfo where cardid=@card and pass=@inputpass)
begin
if(@type='支取')
begin
insert into transInfo(transType,cardID,transMoney)values('支取',@card,@m)
print '成功支取'+convert(varchar(12),@m)+'元'
end
else
begin
insert into transInfo(transType,cardID,transMoney)values('存入',@card,@m)
print '成功存入'+convert(varchar(12),@m)+'元'
end
end
else
begin
--rollback tran
print '密码错误'
end
exec proc_takeMoney '1010 3576 1234 5678',300,'支取','888880' --密码错误
exec proc_takeMoney '1010 3576 1234 5678',300,'支取','888888'
/*
（所影响的行数为 1 行）
交易成功,支取300.00元
（所影响的行数为 1 行）
成功支取300.00元
*/
--产生随机卡号存储过程
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc_randCardID' AND type = 'P')
DROP PROCEDURE proc_randCardID
GO
create proc proc_randCardID
@randCardID char(19) output
as
declare @r char(15),@i int,@newid char(9)
--书上方法不好，产生的卡号都一样的！
--select @r=convert(numeric(15,8),rand(datepart(mm,getdate())*100000)+(datepart(ss,getdate())*1000)+datepart(ms,getdate()))
--我的方法能产生不一样的！
select @r=convert(numeric(15,8),datepart(ms,getdate())*rand(10))
select @i=charindex('.',@r)
--select @newid=substring(@r,@i+1,8)
select @newid=substring(@r,@i+1,4)+' '+substring(@r,@i+5,4)
/*
print @r
print @i
print @newid
*/
set @randCardID='1010 3576'+' '+@newid
go
declare @mycardID char(19)
exec proc_randCardID @mycardID output
print '产生的卡号为：'+@mycardID
select rand(10)
select datepart(ss,getdate())
select datepart(ms,getdate())*rand(10)
--产生的卡号为：1010 3576 5768 7051
--不过书上做法一点都不科学，rand都是一样的！
--开户存储过程
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc_openAccount' AND type = 'P')
DROP PROCEDURE proc_openAccount
GO
create proc proc_openAccount
@name varchar(20),@pid char(20),@tel char(13),@openMoney money,@savingType char(8),
@address varchar(50)='
as
declare @mycardID char(19),@cur_customerID int
execute proc_randCardID @mycardID output
while exists(select * from cardinfo where cardid=@mycardid)
execute proc_randCardID @mycardID output
--select * from userinfo
insert into userinfo(customerName,PID,telephone,address)values(@name,@pid,@tel,@address)
select @cur_customerID=customerID from userinfo where pid=@pid
insert into cardinfo(cardid,savingtype,openmoney,balance,customerid)
values(@mycardid,@savingtype,@openMoney,@openMoney,@cur_customerID)
print '尊敬的客户，开户成功！您的卡号为：'+@mycardid+'开户金额:'+convert(varchar(12),@openMoney)+'开户日期：'+convert(char(10),getdate())
go
exec proc_openAccount '王五','334456889012678','2222-63598978',1000,'活期','河南新乡'
exec proc_openAccount '赵二','213345678912342222','0760-44446666',1,'定期'
--尊敬的客户，开户成功！您的卡号为：1010 3576 1182 1707开户金额:1.00开户日期：01 24 2007
/*
select * from userinfo
select * from cardinfo
*/
--11:创建事物 进行模拟转帐 李四转2000到张三卡上
create proc proc_transfer
@card1 char(19),@card2 char(19),@outmoney money
as
begin tran
insert into transinfo(transtype,cardid,transmoney) values('支取',@card1,@outmoney)
insert into transinfo(transtype,cardid,transmoney) values('存入',@card2,@outmoney)
if( @@ERROR<>0)
rollback tran
else
commit tran
--测试
declare @card1 char(19),@card2 char(19)
select @card1=cardid from cardinfo c inner join userinfo u on
c.customerid=u.customerid where customername='李四'
select @card2=cardid from cardinfo c inner join userinfo u on
c.customerid=u.customerid where customername='张三'
exec proc_transfer @card1,@card2,2000
select * from v_cardinfo
select * from v_transinfo
go
--12 创建登陆帐号
exec sp_addlogin 'sysAdmin','1234'--SQL 登陆帐号
exec sp_grantdbaccess 'sysAdmin','sysAdminDBUser'--创建数据库用户
grant select,insert,update,delete on transinfo to sysAdminDBUser
--用sysAdmin登陆
use bankDB
go
select * from userinfo
--拒绝了对对象 'userInfo'（数据库 'bankDB'，所有者 'dbo'）的 SELECT 权限。
insert into UserInfo(customerName,PID,telephone)
values('李四test','321245678912345679','0478-44443333')
--拒绝了对对象 'userInfo'（数据库 'bankDB'，所有者 'dbo'）的 INSERT 权限。
update userinfo set address=' where customername='李四'
/*
服务器: 消息 229，级别 14，状态 5，行 1
拒绝了对对象 'userInfo'（数据库 'bankDB'，所有者 'dbo'）的 SELECT 权限。
服务器: 消息 229，级别 14，状态 1，行 1
拒绝了对对象 'userInfo'（数据库 'bankDB'，所有者 'dbo'）的 UPDATE 权限。
*/
--测试成功
|
|
