我们时刻为您服务

当前位置: 银行流水帐打印软件 > 制作银行流水软件 >

SELECT @r=RAND((DATEPART(mm

   GRANT SELECT,insert,update,delete ON userInfo TOsysAdminDBUser

GRANTSELECT,insert,update,deleteONcardInfo TOsysAdminDBUser

GRANTSELECT,insert,update,delete ON transInfo TOsysAdminDBUser

--为sysAdminDBUser分配对象权限(增删改查的权限)

--3.--------给数据库用户授权

EXEC sp_grantdbaccess 'sysAdmin', 'sysAdminDBUser'

--2.创建数据库用户

EXECsp_defaultdb 'sysAdmin' , 'bankDB'--修改登录的默认数据库为bankDB

EXEC sp_addlogin 'sysAdmin','1234' --看着银行流水账单怎么打添加SQL登录帐号

begin

If not exists(SELECT * FROM master.dbo.syslogins WHEREloginname='sysAdmin')

--1.添加SQL登录帐号

/*$$$$$$$$$$$$$$$$$$$$$$银行卡制作软件安 全$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/

select * from view_transInfo

select * from view_cardInfo

select * from view_userInfo

EXEC proc_transfer @card1,@card2,2000

--调用上述事务过程转帐

cardInfo.customerID=userInfo.customerID wherecustomerName='张三'

select @card2=cardID from cardInfo Inner Join userInfo ON

cardInfo.customerID=userInfo.customerID wherecustomerName='datepart李四'

select @card1=cardID from cardInfo Inner Join userInfo ON

declare @card1 char(19),@card2 char(19)

--同上一样,现实中的取款机依靠读卡器读出张三/李四的卡号,这里根据张三/李四的名字查出考号来模拟

--从李四的帐户转帐2000到张三的帐户

--2.测试上述事务存储过程

commit tran

print '转帐成功!'

begin

else

rollback tran

print '转帐失败!'

begin

if (@errors>0)

set @errors=@errors+@@error

INSERT INTO transInfo(transType,cardID,transMoney)VALUES('存入',@card2,@outmoney)

set @errors=@errors+@@error

INSERT INTO transInfo(transType,cardID,transMoney)VALUES('听听rand支取',@card1,@outmoney)

set @errors=0

DECLARE @errors int

print '开始转帐,请稍后......'

begin tran

create procedure proc_transfer @card1 char(19),@card2char(19),@outmoney money

--1.转帐的事务存储过程

/*$$$$$$$$$$$$$$$$$$$$$$事务$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/

select * from view_cardInfo

select * from view_userInfo

EXEC proc_openAccount'赵二','','0760-',1,'定期'

EXEC proc_openAccount'王五','','2222-',1000,'活期','河南新乡'

--6.银行转账单制作软件调用存储过程重新开户

VALUES(@mycardID,@savingType,@openMoney,@openMoney,@cur_customerID)

INSERT INTOcardInfo(cardID,savingType,openMoney,balance,customerID)

select @cur_customerID=customerID from userInfo wherePID=@PID

VALUES(@customerName,@PID,@telephone,@address)

INSERT INTO userInfo(customerName,PID,telephone,address )

IF not exists(select * from userInfo where PID=@PID)

print'银行卡制作软件开户日期'+convert(char(10),getdate(),111)+' 开户金额:'+convert(varchar(20),@openMoney)

print'尊敬的客户,开户成功!系统为您产生的随机卡号为:'+@mycardID

EXECUTE proc_randCardID @mycardID OUTPUT

while exists(SELECT * FROM cardInfo WHEREcardID=@mycardID)

EXECUTE proc_randCardID @mycardID OUTPUT

--相比看银行流水账单怎么打调用产生随机卡号的存储过程获得随机卡号

DECLARE @mycardID char(19),@cur_customerID int

,@openMoney money,@savingType char(8),@address varchar(50)=''

create procedure proc_openAccount @customerName char(8),@PIDchar(18),@telephone char(13)

--drop procproc_openAccount

--5.开户的存储过程

print '产生的随机卡号为:'+@mycardID

EXECUTE proc_randCardID @mycardID OUTPUT

DECLARE @mycardID char(19)

--4.测试产生随机卡号

set@randCardID='1010 3576 '+SUBSTRING(@tempStr,3,4)+''+SUBSTRING(@tempStr,7,4) --看着@r=RAND((DATEPART(mm组合为规定格式的卡号

set@tempStr=convert(char(10),@r)--产生0.xxxxxxxx的数字,我们需要小数点后的八位数字

+ DATEPART(ms, GETDATE()) )

SELECT @r=RAND((DATEPART(mm, GETDATE()) *)+ (DATEPART(ss, GETDATE()) * 1000 )

DECLARE@tempStr char(10)

DECLARE @rnumeric(15,8)

create procedure proc_randCardID @randCardID char(19) OUTPUT

--drop proc proc_randCardID

--3.制作银行流水产生随机卡号的存储过程(一般用当前月份数\当前秒数\当前毫秒数乘以一定的系数作为随机种子)

select * from view_transInfo

select * from view_cardInfo

EXEC proc_takeMoney @card,500 ,'存入'

cardInfo.customerID=userInfo.customerID wherecustomerName='李四'

select @card=cardID from cardInfo Inner Join userInfo ON

declare @card char(19)

EXEC proc_takeMoney @card,300 ,'支取',''

cardInfo.customerID=userInfo.customerID wherecustomerName='张三'

select @card=cardID from cardInfo Inner Join userInfo ON

declare @card char(19)

--现实中的取款机依靠读卡器读出张三的卡号,这里根据张三的名字查出考号来模拟

--2.调用存储过程取钱或存钱 张三取300,http://www.hxcjxxw.com/liushu/1143.html。李四的卡号存入200

INSERT INTOtransInfo(transType,cardID,transMoney) VALUES(@type,@card,@m)

return

raiserror ('@r=RAND((DATEPART(mm密码错误!',16,1)

begin

if ((SELECT pass FROM cardInfo WHEREcardID=@card)<>@inputPass )

if (@type='支取')

print'交易正进行,请稍后......'

create procedure proc_takeMoney @card char(19),@m money,@typechar(4),@inputPass char(6)=''

--drop proc proc_takeMoney

--1.取钱或存钱的存储过程

/*$$$$$$$$$$$$$$$$$$$$$$存储过程$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/

INSERT INTO transInfo(transType,cardID,transMoney)VALUES('存入',@card,200)

cardInfo.customerID=userInfo.customerID wherecustomerName='李四'

select @card=cardID from cardInfo Inner Join userInfo ON

declare @card char(19)

INSERT INTO transInfo(transType,cardID,transMoney)VALUES('银行流水账单怎么打支取',@card,1000)

cardInfo.customerID=userInfo.customerID wherecustomerName='张三'

select @card=cardID from cardInfo Inner Join userInfo ON

declare @card char(19)

--现实中的取款机依靠读卡器读出张三的卡号,这里根据张三的名字查出考号来模拟

--测试触发器:张三的卡号支取1000,银行卡制作软件。会往交易信息表transInfo中添加一条交易记录,当存钱或取钱(如500元)时候,将致电催款。听说欧普照明。---*/

print'卡号'+@myCardID+' 余额:'+convert(varchar(20),@mybalance)

SELECT@mybalance=balance FROM cardInfo WHERE cardID=@myCardID

print'交易成功!交易金额:'+convert(varchar(20),@outMoney)

update cardInfo set balance=balance+@outMoney WHEREcardID=@myCardID

else

return

print 'r卡号'+@myCardID+' 余额:'+convert(varchar(20),@mybalance)

rollback tran

raiserror ('交易失败!余额不足!',16,1)

begin

else

update cardInfo set balance=balance-@outMoney WHEREcardID=@myCardID

if (@mybalance>=@outMoney+1)

if(@myTransType='事实上银行卡制作软件支取')

SELECT@mybalance=balance FROM cardInfo WHERE cardID=@myCardID

DECLARE@mybalance money

SELECT@myTransType=transType,@outMoney=transMoney ,@myCardID=cardID FROMinserted

DECLARE@myTransType char(4),@outMoney MONEY,@myCardID char(19)

CREATE TRIGGER trig_trans ON transInfo FOR INSERT

--drop trigger trig_trans

--对比一下制作银行流水改进上述的存款或取款语句,学习银行流水账单怎么打。如果发现用户帐上余额少于200元,每个月末,申请挂失

/*$$$$$$$$$$$$$触发器$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/

remark as 备注 from transInfo

selecttransDate as 交易日期,transType as 交易类型, cardID as卡号,transMoney as 交易金额,

create VIEW view_transInfo --交易信息表视图

balance as 余额,pass 密码,IsReportLoss as 是否挂失,customerID as客户编号 from cardInfo

selectcardID as 卡号,curType as 货币种类, savingType as 存款类型,openDateas 开户日期,

create VIEW view_cardInfo --银行卡信息表视图

telephone as 电话号码,address as 居住地址 fromuserInfo

selectcustomerID as 客户编号,customerName as 看看银行转账单制作软件开户名, PID as身份证号,

create VIEW view_userInfo --银行卡信息表视图

--3.创建视图:为了向客户显示信息友好,查询各表要求字段全为中文字段名。

SELECT * FROM transInfo (INDEX=index_cardID) WHERE cardID=' 1234 5678'

--2.按指定索引查询 张三(卡号为1010 3576)的交易记录

create NONCLUSTERED INDEX index_cardID ON transInfo(cardID)WITHFILLFACTOR=70

--1.创建索引:给交易表的卡号cardID字段创建重复索引

/*$$$$$$$$$$$$$索引和视图$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/

FROM userInfo INNER JOIN cardInfo ON userInfo.customerID=cardInfo.customerID WHERE balance<200

SELECT customerName as 银行卡制作软件客户姓名,telephone as 联系电话,balance as帐上余额

/*------催款提醒:例如某种业务的需要,申请挂失

WHEREcustomerID IN (SELECT customerID FROM cardInfo WHEREIsReportLoss=1)

SELECT customerName as 客户姓名,telephone as 联系电话 FROMuserInfo

/*---------查询挂失帐号的客户信息---------------------*/

exists(SELECT * FROM cardInfo WHERE(DATEDIFF(month,getDate(),openDate)=0))

SELECT DISTINCT cardID FROM transInfo WHERE transMoney=(SELECT Max(transMoney) FROM transInfo) and

SELECT * FROM transInfo

/*---------银行转账单制作软件查询本月交易金额最高的卡号----------------------*/

SELECT * FROM cardInfo WHERE(DATEDIFF(week,getDate(),openDate)=0)

--SELECT * FROM cardInfo WHERE(DATEDIFF(Day,getDate(),openDate)<DATEPART(weekday,openDate))

/*--------查询本周开户的卡号,显示该卡相关信息-----------------*/

print '盈利结算为:'+ convert(varchar(20),@profit)+'RMB'

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

print '银行流通余额总计为:'+convert(varchar(20),@inMoney-@outMoney)+'RMB'

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

SELECT @inMoney=sum(transMoney) FROM transInfo WHERE(transType='存入')

SELECT * FROM transInfo

DECLARE @profit money

DECLARE @outMoney money

DECLARE @inMoney money

--SELECT统计说明:存款代表资金流入,取款代表资金.假定存款利率为千分之3,贷款利率为千分之8

/*--------统计银行的资金流通余额和盈利结算------------------------------*/

SELECT * FROM cardInfo WHERE ((balance between 3000 and 6000) and(savingType='定期') )

/*--------查询余额3000~6000之间的定期卡号,显示该卡相关信息-----------------*/

SELECT * FROM cardInfo

update cardInfo set IsReportLoss=1 WHERE cardID='1010 3576'

--mm李四(卡号为1010 3576 1212 1134)因银行卡丢失,会往交易信息表(transInfo)中添加一条交易记录

/*---------挂失帐号---------*/

SELECT * FROM cardInfo

update cardInfo set pass='' WHERE cardID='1010 3576'

update cardInfo set pass='' WHERE cardID='1010 3576'

--2.我不知道银行转账单制作软件李四(卡号为1010 3576 1212 1134)修改银行卡密码为

--1.张三(卡号为1010 3576 1234 5678)修改银行卡密码为

/*---------修改密码-----*/

/*$$$$$$$$$$$$$常规业务操作$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/

SELECT * FROM transInfo

SELECT * FROM cardInfo

/*--------检查测试数据是否正确---------*/

UPDATE cardInfo SET balance=balance+5000 WHERE cardID='1010 1134'

/*-------------更新银行卡信息表中的现有余额-------------------*/

VALUES('存入','1010 3576',5000)

INSERT INTO transInfo(transType,cardID,transMoney)

/*--------------交易信息表插入交易记录--------------------------*/

UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010 5678'

/*-------------银行卡制作软件更新银行卡信息表中的现有余额-------------------*/

VALUES('支取','1010 3576 1234 5678',900)

INSERT INTO transInfo(transType,cardID,transMoney)

/*--------------交易信息表插入交易记录--------------------------*/

同时应更新银行卡信息表(cardInfo)中的现有余额(如增加或减少500元)

说明:当存钱或取钱(如300元)时候,要求保存交易记录,李四的卡号(1010)存款5000元, 张三的卡号(1010 3576 1234 5678)取款900元,/*---接sqlserver实现ATM(自动取款看看select机)查询系统(1)----*/


银行流水账单怎么打
制作银行流水
你知道银行转账单制作软件
制作银行流水
你知道SELECT
点击次数: 更新时间:2015-07-15 08:45【打印此页】 【关闭