sql语句 创建数据库 操作 以及插入数据 ,事务处理

--创建建库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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值