--创建建库bankDB
CREATE DATABASE bankDB
ON
(
NAME='bankDB_data',
FILENAME='d:\project\bankDB_data.mdf',
SIZE=5mb,
FILEGROWTH=15%
)
LOG ON
(
NAME= 'bankDB_log',
FILENAME='d:\project\bankDB_log.ldf',
SIZE=5mb,
FILEGROWTH=15%
)
GO
/*$$$$$$$$$$$$$建表$$$$$$$$$$$$$$$$$$$$$$$$*/
USE bankDB
GO
CREATE TABLE userInfo --用户信息表
(
customerID INT IDENTITY(1,1),
customerName CHAR(8) NOT NULL,
PID CHAR(18) NOT NULL,
telephone CHAR(20) NOT NULL,
address VARCHAR(50)
)
GO
CREATE TABLE cardInfo --银行卡信息表
(
cardID CHAR(19) NOT NULL,
curID VARCHAR(10) NOT NULL,
savingID INT NOT NULL,
openDate DATETIME NOT NULL,
openMoney MONEY NOT NULL,
balance MONEY NOT NULL,
pass CHAR(6) NOT NULL,
IsReportLoss BIT NOT NULL,
customerID INT NOT NULL
)
GO
CREATE TABLE tradeInfo --交易信息表
(
tradeDate DATETIME NOT NULL,
tradeType CHAR(4) NOT NULL,
cardID CHAR(19) NOT NULL,
tradeMoney MONEY NOT NULL,
remark TEXT
)
GO
CREATE TABLE Deposit --存款类型表
(
savingID INT IDENTITY(1,1),
savingName VARCHAR(20) NOT NULL,
descrip VARCHAR(50)
)
GO
ALTER TABLE Deposit
ADD CONSTRAINT PK_savingID PRIMARY KEY(savingID)
GO
ALTER TABLE userInfo
ADD CONSTRAINT PK_customerID PRIMARY KEY(customerID),
CONSTRAINT CK_PID CHECK( len(PID)=18 or len(PID)=15 ),
CONSTRAINT UQ_PID UNIQUE(PID),
CONSTRAINT CK_telephone 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 telephone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or telephone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' )
GO
ALTER TABLE cardInfo
ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID),
CONSTRAINT CK_cardID CHECK(cardID LIKE '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'),
CONSTRAINT DF_curID DEFAULT('RMB') FOR curID,
CONSTRAINT DF_openDate DEFAULT(getdate()) FOR openDate,
CONSTRAINT CK_openMoney CHECK(openMoney>=1),
CONSTRAINT CK_balance CHECK(balance>=1),
CONSTRAINT CK_pass CHECK(pass LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'),
CONSTRAINT DF_pass DEFAULT('888888') FOR pass,
CONSTRAINT DF_IsReportLoss DEFAULT(0) FOR IsReportLoss,
CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID),
CONSTRAINT FK_savingID FOREIGN KEY(savingID) REFERENCES deposit(savingID)
GO
ALTER TABLE tradeInfo
ADD CONSTRAINT CK_tradeType CHECK(tradeType IN ('存入','支取')),
CONSTRAINT FK_cardID FOREIGN KEY(cardID) REFERENCES cardInfo(cardID),
CONSTRAINT CK_tradeMoney CHECK(tradeMoney>0),
CONSTRAINT DF_tradeDATE DEFAULT(getdate()) FOR tradeDate
GO
--存款类型
INSERT INTO deposit (savingName,descrip) VALUES ('活期','按存款日结算利息')
INSERT INTO deposit (savingName,descrip) VALUES ('定期一年','存款期是1年')
INSERT INTO deposit (savingName,descrip) VALUES ('定期二年','存款期是2年')
INSERT INTO deposit (savingName,descrip) VALUES ('定期三年','存款期是3年')
INSERT INTO deposit (savingName) VALUES ('定活两便')
INSERT INTO deposit (savingName) VALUES ('通知')
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取一年','存款期是1年')
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取二年','存款期是2年')
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取三年','存款期是3年')
INSERT INTO deposit (savingName,descrip) VALUES ('存本取息五年','按月支取利息')
SELECT * FROM DEPOSIT
INSERT INTO userInfo(customerName,PID,telephone,address )
VALUES('张三','123456789012345','010-67898978','北京海淀')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1234 5678',1,1000,1000,1)
INSERT INTO userInfo(customerName,PID,telephone)
VALUES('李四','321245678912345678','0478-44443333')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1212 1134',2,1,1,2)
INSERT INTO userInfo(customerName,PID,telephone)
VALUES('王五','567891234532124670','010-44443333')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1212 1130',2,1,1,3)
INSERT INTO userInfo(customerName,PID,telephone)
VALUES('丁六','567891321242345618','0752-43345543')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1212 1004',2,1,1,4)
SELECT * FROM userInfo
SELECT * FROM cardInfo
GO
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('支取','1010 3576 1234 5678',900)
UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010 3576 1234 5678'
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('存入','1010 3576 1212 1130',300)
UPDATE cardInfo SET balance=balance+300 WHERE cardID='010 3576 1212 1130'
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('存入','1010 3576 1212 1004',1000)
UPDATE cardInfo SET balance=balance+1000 WHERE cardID='1010 3576 1212 1004'
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('支取','1010 3576 1212 1130',1900)
UPDATE cardInfo SET balance=balance+1900 WHERE cardID='010 3576 1212 1130'
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('存入','1010 3576 1212 1134',5000)
UPDATE cardInfo SET balance=balance+5000 WHERE cardID='1010 3576 1212 1134'
GO
SELECT * FROM cardInfo
SELECT * FROM tradeInfo
update 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 cardInfo
GO
SELECT cardid 卡号,curID 货币,savingName 储蓄种类,opendate 开户日期,openmoney 开户金额,balance 余额,pass 密码,
case IsReportLoss WHEN 1 THEN '挂失' WHEN 0 THEN '未挂失' ELSE NULL end 是否挂失,
customerName 客户姓名
FROM CardInfo, Deposit, UserInfo
WHERE CardInfo.savingID=Deposit.savingID and CardInfo.customerID = UserInfo.customerID
DECLARE @inMoney money
DECLARE @outMoney money
DECLARE @profit money
SELECT * FROM tradeInfo
SELECT @inMoney=sum(tradeMoney) FROM tradeInfo WHERE (tradeType='存入')
SELECT @outMoney=sum(tradeMoney) FROM tradeInfo WHERE (tradeType='支取')
print '银行流通余额总计为:'+ convert(varchar(20),@inMoney-@outMoney)+'RMB'
set @profit=@outMoney*0.008-@inMoney*0.003
print '盈利结算为:'+ convert(varchar(20),@profit)+'RMB'
GO
SELECT c.cardID 卡号,u.customerName 姓名,c.curID 货币,d.savingName 存款类型,c.openDate 开户日期,c.openMoney 开户金额,c.balance 存款余额,
CASE c.IsReportLoss WHEN 0 THEN '正常账户'
WHEN 1 THEN '挂失账户'
ELSE NULL
END 账户状态
FROM cardInfo c INNER JOIN userInfo u ON (c.customerID = u.customerID)
INNER JOIN Deposit d ON (c.savingID = d.savingID )
WHERE (DATEDIFF(Day,getDate(),openDate)<DATEPART(weekday,openDate))
--或
SELECT c.cardID 卡号,u.customerName 姓名,c.curID 货币,d.savingName 存款类型,c.openDate 开户日期,c.openMoney 开户金额,c.balance 存款余额,
CASE c.IsReportLoss WHEN 0 THEN '正常账户'
WHEN 1 THEN '挂失账户'
ELSE NULL
END 账户状态
FROM cardInfo c INNER JOIN userInfo u ON (c.customerID = u.customerID)
INNER JOIN Deposit d ON (c.savingID = d.savingID )
WHERE (DATEDIFF(Day,getDate(),openDate)<7)
SELECT * FROM tradeInfo
SELECT DISTINCT cardID FROM tradeInfo WHERE tradeMoney=
(SELECT Max(tradeMoney) FROM tradeInfo
WHERE DATEPART(mm,tradeDate)=DATEPART(mm,getdate())
AND DATEPART(yy,tradeDate)=DATEPART(yy,getdate()))
SELECT customerName as 客户姓名,telephone as 联系电话 FROM userInfo
WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1)
SELECT customerName as 客户姓名,telephone as 联系电话,balance as 存款余额
FROM userInfo INNER JOIN cardInfo ON userInfo.customerID=cardInfo.customerID
WHERE balance<200
if exists (select * from sysobjects where name = 'vw_userInfo')
drop view vw_userInfo
go
create VIEW vw_userInfo --客户信息表视图
AS
select customerID as 客户编号,customerName as 开户名, PID as 身份证号,
telephone as 电话号码,address as 居住地址 from userInfo
GO
--使用视图
SELECT * FROM vw_userInfo
GO
--2.创建视图:查询银行卡信息
if exists (select * from sysobjects where name = 'vw_cardInfo')
drop view vw_cardInfo
go
create 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.customerID
GO
--使用视图
SELECT * FROM vw_cardInfo
GO
--3.创建视图:查看交易信息
if exists (select * from sysobjects where name = 'vw_tradeInfo')
drop view vw_tradeInfo
go
create VIEW vw_tradeInfo --交易信息表视图
AS
select tradeDate as 交易日期,tradeType as 交易类型, cardID as 卡号,tradeMoney as 交易金额,
remark as 备注 from tradeInfo
GO
--使用视图
SELECT * FROM vw_tradeInfo
if exists (select * from sysobjects where name = 'usp_takeMoney')
drop proc usp_takeMoney
go
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 0
GO
declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo ON
cardInfo.customerID=userInfo.customerID where customerName='张三'
EXEC usp_takeMoney @card,10 ,'支取','123456'
GO
select * 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_cardInfo
select * from vw_tradeInfo
GO
if exists (select * from sysobjects where name = 'usp_randCardID')
drop proc usp_randCardID
go
create 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 OUTPUT
print '产生的随机卡号为:'+@mycardID
GO
if exists (select * from sysobjects where name = 'usp_openAccount')
drop proc usp_openAccount
GO
create 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 =@savingName
if @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_userInfo
select * from vw_cardInfo
GO
if exists (select * from sysobjects where name = 'usp_CheckSheet')
drop proc usp_CheckSheet
GO
CREATE PROCEDURE usp_CheckSheet
@cardID varchar(19),
@date1 datetime=NULL,
@date2 datetime=NULL
AS
DECLARE @custName varchar(20)
DECLARE @curName varchar(20)
DECLARE @savingName varchar(20)
DECLARE @openDate datetime
SELECT @cardID=c.cardID, @curName=c.curID, @custName=u.customerName,
@savingName=d.savingName , @openDate=c.openDate
FROM cardInfo c, userInfo u, deposit d
WHERE c.customerID=u.customerID and c.savingID = d.savingID and cardID = @cardID
PRINT '卡号:' + @cardID
PRINT '姓名:' + @custName
PRINT '货币:' + @curName
PRINT '存款类型:' + @savingName
PRINT '开户日期:' + 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
BEGIN
SELECT tradeDate 交易日, tradeType 类型, tradeMoney 交易金额, remark 备注
FROM tradeInfo
WHERE cardID='1010 3576 1212 1134'
ORDER BY tradeDate
RETURN
END
ELSE IF @date2 IS NULL
SET @date2 = getdate()
SELECT tradeDate 交易日, tradeType 类型, tradeMoney 交易金额, remark 备注
FROM tradeInfo
WHERE cardID=@cardID AND tradeDate BETWEEN @date1 AND @date2
ORDER BY tradeDate
GO
--测试打印对帐单
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_pagingDisplay
GO
CREATE PROCEDURE usp_pagingDisplay
@records int = 10,
@page int = 1
AS
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,@rec2
GO
--
EXEC usp_pagingDisplay 2,2
if exists (select * from sysobjects where name = 'usp_getWithoutTrade')
drop proc usp_getWithoutTrade
GO
create procedure usp_getWithoutTrade
@Num int output,
@Amount decimal(18,2) output,
@date1 datetime = NULL,
@date2 datetime = NULL
AS
IF @date1 IS NULL
BEGIN
declare @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 NULL
SET @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)
GO
DECLARE @NUM int
DECLARE @Amount decimal(18,2)
DECLARE @date1 datetime
DECLARE @date2 datetime
SET @date1 = '2009-1-1'
SET @date2 = getdate()
EXEC usp_getWithoutTrade @NUM OUTPUT, @Amount OUTPUT--, @date1, @date2
PRINT '统计未发生交易的客户'
PRINT '---------------------------------------'
PRINT '客户人数:' + CAST(@NUM AS varchar(10)) + ' 客户总余额:' + CAST(@Amount AS varchar(20))
if exists (select * from sysobjects where name = 'usp_getTradeInfo')
drop proc usp_getTradeInfo
GO
create 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) = NULL
AS
-- 初始化变量
SET @Num1 = 0
SET @Amount1 = 0
SET @Num2 = 0
SET @Amount2 = 0
IF @date2 IS NULL
SET @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+'%'
END
GO
DECLARE @CNT1 int
DECLARE @Total1 decimal(18,2)
DECLARE @CNT2 int
DECLARE @Total2 decimal(18,2)
DECLARE @date1 datetime
DECLARE @date2 datetime
SET @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))
GO0/
--转帐的事务存储过程
if exists (select * from sysobjects where name = 'usp_tradefer')
drop proc usp_tradefer
GO
create procedure usp_tradefer
@card1 char(19),
@pwd char(6),
@card2 char(19),
@outmoney money
AS
DECLARE @date1 datetime
DECLARE @date2 datetime
SET @date1 = getdate()
begin tran
print '开始转帐,请稍后......'
DECLARE @errors int
set @errors=0
DECLARE @result int
EXEC @result=usp_takeMoney @card1,@outmoney ,'支取',@pwd --'123123'
set @errors=@errors+@@error
if (@errors > 0 or @result <> 0)
begin
print '转帐失败!'
rollback tran
RETURN -1
end
EXEC @result=usp_takeMoney @card2,@outmoney ,'存入'
set @errors=@errors+@@error
if (@errors > 0 or @result <> 0)
begin
print '转帐失败!'
rollback tran
RETURN -1
end
else
begin
print '转帐成功!'
commit tran
SET @date2 = getdate()
print '打印转出账户对账单'
PRINT '-------------------'
EXEC usp_CheckSheet @card1,@date1,@date2
print '打印转入账户对账单'
PRINT '-------------------'
EXEC usp_CheckSheet @card2,@date1,@date2
RETURN 0
end
GO
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=userInfo.customerID where customerName='张三'
EXEC usp_tradefer @card1,'123123',@card2,2000
select * from vw_userInfo
select * from vw_cardInfo
select * from vw_tradeInfo
GO
--1.添加SQL登录帐号
CREATE LOGIN appAdmin WITH PASSWORD='bank1234'
GO
--2.创建数据库用户
USE bankDB
GO
CREATE USER appAdmin FOR LOGIN appAdmin
GO
--3. 给数据库用户appAdmin授权(增删改查的权限)
GRANT select,insert,update,delete,select ON vw_userInfo
TO appAdmin
GRANT select,insert,update,delete,select ON vw_tradeInfo
TO appAdmin
GRANT select,insert,update,delete,select ON vw_cardInfo
TO appAdmin
GO
sql语句 创建数据库 操作 以及插入数据 ,事务处理
最新推荐文章于 2024-01-25 21:15:27 发布