ATM-简单SQL查询
程序员文章站
2022-05-16 10:04:24
use master go if exists(select * from sysDatabases where name = 'BankDB') drop database BankDB go create database BankDB go use BankDB go --建用户信息表 if ... ......
use master
go
if exists(select * from sysdatabases where name = 'bankdb')
drop database bankdb
go
create database bankdb
go
use bankdb
go
--建用户信息表
if exists(select * from sysobjects where name = 'xxl_userinfo')
drop table xxl_userinfo
go
create table xxl_userinfo
(
xxl_user_id int not null primary key identity ,
xxl_user_name nvarchar(20) not null ,
xxl_user_sex bit not null check(xxl_user_sex in (0,1)),
xxl_user_idcard char(18) not null unique ,
xxl_user_moblie char(11) not null check(xxl_user_moblie like '1[3579][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
xxl_user_address nvarchar(50) not null
)
go
--建用户卡信息表
if exists(select * from sysobjects where name = 'xxl_cardinfo')
drop table xxl_cardinfo
go
create table xxl_cardinfo
(
xxl_card_no char(16) not null primary key check(xxl_card_no like '66668888[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ,
xxl_card_pwd char(6) not null default('666888') ,
from_xxl_user_id int not null references xxl_userinfo(xxl_user_id),
xxl_card_date datetime not null default(getdate()) ,
xxl_card_balance decimal(18,2) not null check(xxl_card_balance >= 0) ,
xxl_card_state int not null check(xxl_card_state in (0,1,2)),
xxl_card_text nvarchar(50)
)
go
--建交易信息表
if exists(select * from sysobjects where name = 'xxl_transinfo')
drop table xxl_transinfo
go
create table xxl_transinfo
(
xxl_trans_flownum int not null identity primary key ,
from_xxl_card_no char(16) not null references xxl_cardinfo(xxl_card_no) ,
xxl_trans_type int not null check(xxl_trans_type in (1,2)) ,
xxl_trans_quota decimal(18,2) not null check(xxl_trans_quota > 0) ,
xxl_trans_date datetime not null default(getdate()) ,
xxl_trans_ed_balance decimal(18,2) not null check(xxl_trans_ed_balance >= 0) ,
xxl_trans_text varchar(50) not null
)
go
------添加用户信息
insert xxl_userinfo values('徐小龙','1','42028120000114125x','13071226588','湖北武汉')
insert xxl_userinfo values('张小杨','0','42028119980515543x','13045114154','湖北武汉')
insert xxl_userinfo values('吴小心','0','42028120001202114x','13071557444','湖北武汉')
------添加用户卡信息
insert xxl_cardinfo values('6666888845125214','666888','1','2006-2-12','600','0','使用')
insert xxl_cardinfo values('6666888865896548','666888','1','2007-2-20','3000','0','使用')
insert xxl_cardinfo values('6666888812454852','666888','2','2016-6-12','6300','0','使用')
insert xxl_cardinfo values('6666888852145698','666888','3','2018-3-24','500','0','使用')
------添加交易信息
insert xxl_transinfo values('6666888845125214','1','300','2016-3-12','300','存入300元')
insert xxl_transinfo values('6666888845125214','1','300','2017-5-3','600','存入300元')
insert xxl_transinfo values('6666888865896548','1','6000','2013-9-1','6000','存入6000元')
insert xxl_transinfo values('6666888865896548','2','3000','2014-9-1','3000','转账3000元给6666888812454852')
insert xxl_transinfo values('6666888812454852','1','3000','2017-3-6','3000','6666888865896548转入的3000元')
insert xxl_transinfo values('6666888812454852','1','3300','2017-12-1','6300','存入3300元')
insert xxl_transinfo values('6666888852145698','1','3000','2018-6-3','3000','存入3000元')
insert xxl_transinfo values('6666888852145698','2','2500','2018-7-3','500','取出2500元')
------备份交易信息表
select * into xxl_transinfo_bak from xxl_transinfo
--------查询各表数据
--select * from xxl_userinfo
--select * from xxl_cardinfo
--select * from xxl_transinfo
--select * from xxl_transinfo_bak
----------------------------------------创建函数----------------------------------------
--加逗号的函数
if exists(select * from sysobjects where name='function_jiadouhao')
drop function function_jiadouhao
go
create function function_jiadouhao( @money decimal(18,2))
returns varchar(50) as
begin
declare @a varchar(50)= left(@money,len(@money)-3)
declare @b varchar(50)= right(@money,3)
while (len(@a)>3)
begin
select @b = ','+right(@a,3)+@b
select @a = left(@a,len(@a)-3)
end
return @a+@b
end
go
------------------------------------------结束------------------------------------------
----------------------------------------创建视图----------------------------------------
--用户信息视图
if exists(select * from sysobjects where name ='vw_userinfo')
drop view vw_userinfo
go
create view vw_userinfo
as
select
xxl_user_id 编号,
xxl_user_name 姓名,
case xxl_user_sex
when 0 then '女'
when 1 then '男'
end 性别,
xxl_user_idcard 身份证,
xxl_user_moblie 联系电话,
xxl_user_address 籍贯
from xxl_userinfo
go
--使用视图
--select * from vw_userinfo
--卡信息视图
if exists(select * from sysobjects where name='vw_cardinfo')
drop view vw_cardinfo
go
create view vw_cardinfo
as
select
xxl_card_no 卡号,
xxl_user_name 姓名,
xxl_card_balance 余额,
xxl_card_date 开卡日期,
case xxl_card_state
when 0 then '正常'
when 1 then '冻结'
when 2 then '注销'
end 状态,
dbo.function_jiadouhao(xxl_card_balance) 货币表示
from xxl_userinfo userinfo inner join xxl_cardinfo cardinfo on userinfo.xxl_user_id = cardinfo.from_xxl_user_id
go
--使用视图
--select * from vw_cardinfo
--交易记录视图
if exists(select * from sysobjects where name='vw_transinfo')
drop view vw_transinfo
go
create view vw_transinfo
as
select ----卡号，交易日期，交易类型，交易金额，余额，描述
xxl_card_no 卡号,
xxl_trans_date 交易日期,
case xxl_trans_type
when 1 then '存入'
when 2 then '支取'
end 交易类型,
case xxl_trans_type
when 1 then '+'+convert(varchar(20),xxl_trans_quota)
when 2 then '-'+convert(varchar(20),xxl_trans_quota)
end 交易金额,
xxl_trans_ed_balance 余额,
xxl_trans_text 描述
from xxl_cardinfo cardinfo inner join xxl_transinfo transinfo on cardinfo.xxl_card_no = transinfo.from_xxl_card_no
go
--使用视图
--select * from vw_transinfo
--------------------------------------------结束--------------------------------------------
----------------------------------------创建存储过程----------------------------------------
--1、 查询余额
if exists(select * from sysobjects where name='p_selectbalance')
drop proc p_selectbalance
go
create proc p_selectbalance
@cardno char(16)
as
select 货币表示 as 余额 from vw_cardinfo where 卡号 = @cardno
go
--exec p_selectbalance '6666888845125214'
--2、 查询某两日期之间交易记录
if exists(select * from sysobjects where name='p_selectstart_stopdate')
drop proc p_selectstart_stopdate
go
create proc p_selectstart_stopdate
@cardno char(16),
@startdate datetime,
@stopdate datetime
as
select * from vw_transinfo where 卡号 = @cardno and 交易日期 >= @startdate and 交易日期 < dateadd(dd,1,@stopdate)
go
--exec p_selectstart_stopdate '6666888845125214','1990-1-1','2018-9-9'
--3、 修改密码功能
if exists(select * from sysobjects where name='p_update_pwd')
drop proc p_update_pwd
go
create proc p_update_pwd
@cardno char(16),
@cardpwdstart char(6),
@cardpwdstop char(6)
as
update xxl_cardinfo set xxl_card_pwd=@cardpwdstop where xxl_card_no = @cardno and xxl_card_pwd = @cardpwdstart
go
--exec p_update_pwd '6666888845125214','666888','548888'
--4、 存款功能（备份）
if exists(select * from sysobjects where name='p_sevemoney')
drop proc p_sevemoney
go
create proc p_sevemoney
@cardno char(16),
@quota decimal(18,2)
as
if @quota < 0
begin
begin tran
declare @err int = 0
declare @startbalance decimal(18,2) = 0
select @startbalance=xxl_card_balance from xxl_cardinfo where xxl_card_no = @cardno
insert xxl_transinfo values(@cardno,'1',@quota,getdate(),(@startbalance + @quota),('存入' + convert(varchar(50), @quota) + '元'))
select @err = @@error + @err
update xxl_cardinfo set xxl_card_balance = (@startbalance + @quota) where xxl_card_no = @cardno
select @err = @@error + @err
if @err = 0
begin
print '操作成功'
commit tran
return 0
end
begin
print '未知错误！'
rollback tran
return -1
end
end
else
begin
print '输入金额有误！'
return -1
end
go
--5、 取款功能（备份）
if exists(select * from sysobjects where name='p_getmoney')
drop proc p_getmoney
go
create proc p_getmoney
@cardno char(16),
@quota decimal(18,2)
as
if @quota < 0
begin
declare @startbalance decimal(18,2)
select @startbalance=xxl_card_balance from xxl_cardinfo where xxl_card_no = @cardno
if @startbalance < @quota
begin
begin tran
declare @err int = 0
insert xxl_transinfo values(@cardno,'2',@quota,getdate(),(@startbalance - @quota),('取出' + convert(varchar(50), @quota) + '元'))
select @err = @@error + @err
update xxl_cardinfo set xxl_card_balance = (@startbalance - @quota) where xxl_card_no = @cardno
select @err = @@error + @err
if @err = 0
begin
print '操作成功'
commit tran
return 0
end
else
begin
print '未知错误！'
rollback tran
return -1
end
end
else
begin
print '余额不足！'
return -1
end
end
else
begin
print '输入金额有误！'
return -1
end
go
--6、 转帐功能（备份）
if exists(select * from sysobjects where name='p_teansfermoney')
drop proc p_teansfermoney
go
create proc p_teansfermoney
@fromcardno char(16),
@tocardno char(16),
@quota decimal(18,2)
as
if @fromcardno = @tocardno
begin
if (select count(*) from xxl_cardinfo where xxl_card_no = @tocardno) =1
begin
if @quota < 0
begin
declare @fromstartbalance decimal(18,2) = 0 -- 转出前
select @fromstartbalance=xxl_card_balance from xxl_cardinfo where xxl_card_no = @fromcardno
if @fromstartbalance < @quota
begin
begin tran
declare @err int = 0
declare @tostartbalance decimal(18,2) = 0 --转入前
select @tostartbalance=xxl_card_balance from xxl_cardinfo where xxl_card_no = @tocardno
insert xxl_transinfo values(@fromcardno,'1',@quota,getdate(),(@fromstartbalance - @quota), '转出' + convert(varchar(50), @quota) + '元给'+@tocardno)
select @err = @@error + @err
insert xxl_transinfo values(@tocardno,'2',@quota,getdate(),(@tostartbalance + @quota),('由' +@fromcardno+ '转入'+ convert(varchar(50), @quota) + '元'))
select @err = @@error + @err
update xxl_cardinfo set xxl_card_balance = (@fromstartbalance - @quota) where xxl_card_no = @fromcardno
select @err = @@error + @err
update xxl_cardinfo set xxl_card_balance = (@tostartbalance + @quota) where xxl_card_no = @tocardno
select @err = @@error + @err
if @err = 0
begin
print '操作成功！'
commit tran
return 0
end
else
begin
print '未知错误！'
rollback tran
return -1
end
end
else
begin
print '余额不足！'
return -1
end
end
else
begin
print '输入金额有误！'
return -1
end
end
else
begin
print '转账账户不存在！'
return -1
end
end
else
begin
print '转账账户不可以为自己！'
return -1
end
go
--exec p_teansfermoney '6666888812454852','6666888845125214',300.00
--7、 随机产生卡号（卡号格式为：8228 6688 xxxx xxxx） 注：随机产生的卡号已经存在的不能用
if exists(select * from sysobjects where name='p_generatebankcard')
drop proc p_generatebankcard
go
create proc p_generatebankcard
@card varchar(16) output
as
declare @id varchar(20)
select @id = substring(convert( varchar(20) ,convert( dec(10,10) ,rand())), 3,8)
select @card = convert (varchar(8), '66668888')+@id
while(select count(*) from xxl_cardinfo where xxl_card_no = @card) = 1
begin
select @id = substring(convert( varchar(20) ,convert( dec(10,10) ,rand())),3,8)
select @card = convert (varchar(8), '66668888')+@id
end
go
declare @card varchar(16)
exec p_generatebankcard @card output
select @card as 卡号
--8、 开户功能
if exists(select * from sysobjects where name = 'p_accountopening')
drop proc p_accountopening
go
create proc p_accountopening
@name nvarchar(20),
@sex bit,
@idcard char(18),
@moblie char(11),
@address nvarchar(50),
@pwd char(6)
as
if (select count(*) from xxl_userinfo where xxl_user_idcard =@idcard) = 1
begin
begin tran
declare @userid int
declare @err int = 0
insert xxl_userinfo values(@name,@sex,@idcard,@moblie,@address)
select @err = @@error + @err
declare @card varchar(16) = ''
exec p_generatebankcard @card output
select @userid = xxl_user_id from xxl_userinfo where xxl_user_idcard = @idcard
insert xxl_cardinfo values(@card,@pwd,@userid,getdate(),'0','0','使用')
select @err = @@error + @err
if(@err = 0)
begin
print '开户成功！'
commit tran
return 0
end
else
begin
print '未知错误！'
rollback tran
return -1
end
end
else
begin
print '同一个身份证只可开一个户！'
return -1
end
go
--select * from xxl_userinfo
--select * from xxl_cardinfo
--exec p_accountopening '徐小龙','1','420281200001141255','13071226588','湖北武汉'
--select * from xxl_userinfo
--9、 解冻功能
if exists(select * from sysobjects where name = 'p_thawaccount')
drop proc p_thawaccount
go
create proc p_thawaccount
@count int
as
if @count = 0
begin
update xxl_cardinfo set xxl_card_state = 0
print '解除冻结成功！'
return 0
end
go
--10、 根据用户身份证，查询该用户下所有的银行卡信息
if exists(select * from sysobjects where name = 'p_selectcard')
drop proc p_selectcard
go
create proc p_selectcard
@idcard nchar(18)
as
declare @id varchar(20)
select @id = xxl_user_id from xxl_userinfo where xxl_user_idcard = @idcard
select * from xxl_cardinfo where from_xxl_user_id = @id
go
--exec p_selectcard '42028120000114125x'
--------------------------------------------结束--------------------------------------------
--select * from xxl_cardinfo
--select * from xxl_userinfo
--select xxl_user_id from xxl_userinfo where xxl_user_idcard = '42028120000114125x'
use master
go
if exists(select * from sysdatabases where name = 'bankdb')
drop database bankdb
go
create database bankdb
go
use bankdb
go
--建用户信息表
if exists(select * from sysobjects where name = 'xxl_userinfo')
drop table xxl_userinfo
go
create table xxl_userinfo
(
xxl_user_id int not null primary key identity ,
xxl_user_name nvarchar(20) not null ,
xxl_user_sex bit not null check(xxl_user_sex in (0,1)),
xxl_user_idcard char(18) not null unique ,
xxl_user_moblie char(11) not null check(xxl_user_moblie like '1[3579][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
xxl_user_address nvarchar(50) not null
)
go
--建用户卡信息表
if exists(select * from sysobjects where name = 'xxl_cardinfo')
drop table xxl_cardinfo
go
create table xxl_cardinfo
(
xxl_card_no char(16) not null primary key check(xxl_card_no like '66668888[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ,
xxl_card_pwd char(6) not null default('666888') ,
from_xxl_user_id int not null references xxl_userinfo(xxl_user_id),
xxl_card_date datetime not null default(getdate()) ,
xxl_card_balance decimal(18,2) not null check(xxl_card_balance >= 0) ,
xxl_card_state int not null check(xxl_card_state in (0,1,2)),
xxl_card_text nvarchar(50)
)
go
--建交易信息表
if exists(select * from sysobjects where name = 'xxl_transinfo')
drop table xxl_transinfo
go
create table xxl_transinfo
(
xxl_trans_flownum int not null identity primary key ,
from_xxl_card_no char(16) not null references xxl_cardinfo(xxl_card_no) ,
xxl_trans_type int not null check(xxl_trans_type in (1,2)) ,
xxl_trans_quota decimal(18,2) not null check(xxl_trans_quota > 0) ,
xxl_trans_date datetime not null default(getdate()) ,
xxl_trans_ed_balance decimal(18,2) not null check(xxl_trans_ed_balance >= 0) ,
xxl_trans_text varchar(50) not null
)
go
------添加用户信息
insert xxl_userinfo values('徐小龙','1','42028120000114125x','13071226588','湖北武汉')
insert xxl_userinfo values('张小杨','0','42028119980515543x','13045114154','湖北武汉')
insert xxl_userinfo values('吴小心','0','42028120001202114x','13071557444','湖北武汉')
------添加用户卡信息
insert xxl_cardinfo values('6666888845125214','666888','1','2006-2-12','600','0','使用')
insert xxl_cardinfo values('6666888865896548','666888','1','2007-2-20','3000','0','使用')
insert xxl_cardinfo values('6666888812454852','666888','2','2016-6-12','6300','0','使用')
insert xxl_cardinfo values('6666888852145698','666888','3','2018-3-24','500','0','使用')
------添加交易信息
insert xxl_transinfo values('6666888845125214','1','300','2016-3-12','300','存入300元')
insert xxl_transinfo values('6666888845125214','1','300','2017-5-3','600','存入300元')
insert xxl_transinfo values('6666888865896548','1','6000','2013-9-1','6000','存入6000元')
insert xxl_transinfo values('6666888865896548','2','3000','2014-9-1','3000','转账3000元给6666888812454852')
insert xxl_transinfo values('6666888812454852','1','3000','2017-3-6','3000','6666888865896548转入的3000元')
insert xxl_transinfo values('6666888812454852','1','3300','2017-12-1','6300','存入3300元')
insert xxl_transinfo values('6666888852145698','1','3000','2018-6-3','3000','存入3000元')
insert xxl_transinfo values('6666888852145698','2','2500','2018-7-3','500','取出2500元')
------备份交易信息表
select * into xxl_transinfo_bak from xxl_transinfo
--------查询各表数据
--select * from xxl_userinfo
--select * from xxl_cardinfo
--select * from xxl_transinfo
--select * from xxl_transinfo_bak
----------------------------------------创建函数----------------------------------------
--加逗号的函数
if exists(select * from sysobjects where name='function_jiadouhao')
drop function function_jiadouhao
go
create function function_jiadouhao( @money decimal(18,2))
returns varchar(50) as
begin
declare @a varchar(50)= left(@money,len(@money)-3)
declare @b varchar(50)= right(@money,3)
while (len(@a)>3)
begin
select @b = ','+right(@a,3)+@b
select @a = left(@a,len(@a)-3)
end
return @a+@b
end
go
------------------------------------------结束------------------------------------------
----------------------------------------创建视图----------------------------------------
--用户信息视图
if exists(select * from sysobjects where name ='vw_userinfo')
drop view vw_userinfo
go
create view vw_userinfo
as
select
xxl_user_id 编号,
xxl_user_name 姓名,
case xxl_user_sex
when 0 then '女'
when 1 then '男'
end 性别,
xxl_user_idcard 身份证,
xxl_user_moblie 联系电话,
xxl_user_address 籍贯
from xxl_userinfo
go
--使用视图
--select * from vw_userinfo
--卡信息视图
if exists(select * from sysobjects where name='vw_cardinfo')
drop view vw_cardinfo
go
create view vw_cardinfo
as
select
xxl_card_no 卡号,
xxl_user_name 姓名,
xxl_card_balance 余额,
xxl_card_date 开卡日期,
case xxl_card_state
when 0 then '正常'
when 1 then '冻结'
when 2 then '注销'
end 状态,
dbo.function_jiadouhao(xxl_card_balance) 货币表示
from xxl_userinfo userinfo inner join xxl_cardinfo cardinfo on userinfo.xxl_user_id = cardinfo.from_xxl_user_id
go
--使用视图
--select * from vw_cardinfo
--交易记录视图
if exists(select * from sysobjects where name='vw_transinfo')
drop view vw_transinfo
go
create view vw_transinfo
as
select ----卡号，交易日期，交易类型，交易金额，余额，描述
xxl_card_no 卡号,
xxl_trans_date 交易日期,
case xxl_trans_type
when 1 then '存入'
when 2 then '支取'
end 交易类型,
case xxl_trans_type
when 1 then '+'+convert(varchar(20),xxl_trans_quota)
when 2 then '-'+convert(varchar(20),xxl_trans_quota)
end 交易金额,
xxl_trans_ed_balance 余额,
xxl_trans_text 描述
from xxl_cardinfo cardinfo inner join xxl_transinfo transinfo on cardinfo.xxl_card_no = transinfo.from_xxl_card_no
go
--使用视图
--select * from vw_transinfo
--------------------------------------------结束--------------------------------------------
----------------------------------------创建存储过程----------------------------------------
--1、 查询余额
if exists(select * from sysobjects where name='p_selectbalance')
drop proc p_selectbalance
go
create proc p_selectbalance
@cardno char(16)
as
select 货币表示 as 余额 from vw_cardinfo where 卡号 = @cardno
go
--exec p_selectbalance '6666888845125214'
--2、 查询某两日期之间交易记录
if exists(select * from sysobjects where name='p_selectstart_stopdate')
drop proc p_selectstart_stopdate
go
create proc p_selectstart_stopdate
@cardno char(16),
@startdate datetime,
@stopdate datetime
as
select * from vw_transinfo where 卡号 = @cardno and 交易日期 >= @startdate and 交易日期 < dateadd(dd,1,@stopdate)
go
--exec p_selectstart_stopdate '6666888845125214','1990-1-1','2018-9-9'
--3、 修改密码功能
if exists(select * from sysobjects where name='p_update_pwd')
drop proc p_update_pwd
go
create proc p_update_pwd
@cardno char(16),
@cardpwdstart char(6),
@cardpwdstop char(6)
as
update xxl_cardinfo set xxl_card_pwd=@cardpwdstop where xxl_card_no = @cardno and xxl_card_pwd = @cardpwdstart
go
--exec p_update_pwd '6666888845125214','666888','548888'
--4、 存款功能（备份）
if exists(select * from sysobjects where name='p_sevemoney')
drop proc p_sevemoney
go
create proc p_sevemoney
@cardno char(16),
@quota decimal(18,2)
as
if @quota < 0
begin
begin tran
declare @err int = 0
declare @startbalance decimal(18,2) = 0
select @startbalance=xxl_card_balance from xxl_cardinfo where xxl_card_no = @cardno
insert xxl_transinfo values(@cardno,'1',@quota,getdate(),(@startbalance + @quota),('存入' + convert(varchar(50), @quota) + '元'))
select @err = @@error + @err
update xxl_cardinfo set xxl_card_balance = (@startbalance + @quota) where xxl_card_no = @cardno
select @err = @@error + @err
if @err = 0
begin
print '操作成功'
commit tran
return 0
end
begin
print '未知错误！'
rollback tran
return -1
end
end
else
begin
print '输入金额有误！'
return -1
end
go
--5、 取款功能（备份）
if exists(select * from sysobjects where name='p_getmoney')
drop proc p_getmoney
go
create proc p_getmoney
@cardno char(16),
@quota decimal(18,2)
as
if @quota < 0
begin
declare @startbalance decimal(18,2)
select @startbalance=xxl_card_balance from xxl_cardinfo where xxl_card_no = @cardno
if @startbalance < @quota
begin
begin tran
declare @err int = 0
insert xxl_transinfo values(@cardno,'2',@quota,getdate(),(@startbalance - @quota),('取出' + convert(varchar(50), @quota) + '元'))
select @err = @@error + @err
update xxl_cardinfo set xxl_card_balance = (@startbalance - @quota) where xxl_card_no = @cardno
select @err = @@error + @err
if @err = 0
begin
print '操作成功'
commit tran
return 0
end
else
begin
print '未知错误！'
rollback tran
return -1
end
end
else
begin
print '余额不足！'
return -1
end
end
else
begin
print '输入金额有误！'
return -1
end
go
--6、 转帐功能（备份）
if exists(select * from sysobjects where name='p_teansfermoney')
drop proc p_teansfermoney
go
create proc p_teansfermoney
@fromcardno char(16),
@tocardno char(16),
@quota decimal(18,2)
as
if @fromcardno = @tocardno
begin
if (select count(*) from xxl_cardinfo where xxl_card_no = @tocardno) =1
begin
if @quota < 0
begin
declare @fromstartbalance decimal(18,2) = 0 -- 转出前
select @fromstartbalance=xxl_card_balance from xxl_cardinfo where xxl_card_no = @fromcardno
if @fromstartbalance < @quota
begin
begin tran
declare @err int = 0
declare @tostartbalance decimal(18,2) = 0 --转入前
select @tostartbalance=xxl_card_balance from xxl_cardinfo where xxl_card_no = @tocardno
insert xxl_transinfo values(@fromcardno,'1',@quota,getdate(),(@fromstartbalance - @quota), '转出' + convert(varchar(50), @quota) + '元给'+@tocardno)
select @err = @@error + @err
insert xxl_transinfo values(@tocardno,'2',@quota,getdate(),(@tostartbalance + @quota),('由' +@fromcardno+ '转入'+ convert(varchar(50), @quota) + '元'))
select @err = @@error + @err
update xxl_cardinfo set xxl_card_balance = (@fromstartbalance - @quota) where xxl_card_no = @fromcardno
select @err = @@error + @err
update xxl_cardinfo set xxl_card_balance = (@tostartbalance + @quota) where xxl_card_no = @tocardno
select @err = @@error + @err
if @err = 0
begin
print '操作成功！'
commit tran
return 0
end
else
begin
print '未知错误！'
rollback tran
return -1
end
end
else
begin
print '余额不足！'
return -1
end
end
else
begin
print '输入金额有误！'
return -1
end
end
else
begin
print '转账账户不存在！'
return -1
end
end
else
begin
print '转账账户不可以为自己！'
return -1
end
go
--exec p_teansfermoney '6666888812454852','6666888845125214',300.00
--7、 随机产生卡号（卡号格式为：8228 6688 xxxx xxxx） 注：随机产生的卡号已经存在的不能用
if exists(select * from sysobjects where name='p_generatebankcard')
drop proc p_generatebankcard
go
create proc p_generatebankcard
@card varchar(16) output
as
declare @id varchar(20)
select @id = substring(convert( varchar(20) ,convert( dec(10,10) ,rand())), 3,8)
select @card = convert (varchar(8), '66668888')+@id
while(select count(*) from xxl_cardinfo where xxl_card_no = @card) = 1
begin
select @id = substring(convert( varchar(20) ,convert( dec(10,10) ,rand())),3,8)
select @card = convert (varchar(8), '66668888')+@id
end
go
declare @card varchar(16)
exec p_generatebankcard @card output
select @card as 卡号
--8、 开户功能
if exists(select * from sysobjects where name = 'p_accountopening')
drop proc p_accountopening
go
create proc p_accountopening
@name nvarchar(20),
@sex bit,
@idcard char(18),
@moblie char(11),
@address nvarchar(50),
@pwd char(6)
as
if (select count(*) from xxl_userinfo where xxl_user_idcard =@idcard) = 1
begin
begin tran
declare @userid int
declare @err int = 0
insert xxl_userinfo values(@name,@sex,@idcard,@moblie,@address)
select @err = @@error + @err
declare @card varchar(16) = ''
exec p_generatebankcard @card output
select @userid = xxl_user_id from xxl_userinfo where xxl_user_idcard = @idcard
insert xxl_cardinfo values(@card,@pwd,@userid,getdate(),'0','0','使用')
select @err = @@error + @err
if(@err = 0)
begin
print '开户成功！'
commit tran
return 0
end
else
begin
print '未知错误！'
rollback tran
return -1
end
end
else
begin
print '同一个身份证只可开一个户！'
return -1
end
go
--select * from xxl_userinfo
--select * from xxl_cardinfo
--exec p_accountopening '徐小龙','1','420281200001141255','13071226588','湖北武汉'
--select * from xxl_userinfo
--9、 解冻功能
if exists(select * from sysobjects where name = 'p_thawaccount')
drop proc p_thawaccount
go
create proc p_thawaccount
@count int
as
if @count = 0
begin
update xxl_cardinfo set xxl_card_state = 0
print '解除冻结成功！'
return 0
end
go
--10、 根据用户身份证，查询该用户下所有的银行卡信息
if exists(select * from sysobjects where name = 'p_selectcard')
drop proc p_selectcard
go
create proc p_selectcard
@idcard nchar(18)
as
declare @id varchar(20)
select @id = xxl_user_id from xxl_userinfo where xxl_user_idcard = @idcard
select * from xxl_cardinfo where from_xxl_user_id = @id
go
--exec p_selectcard '42028120000114125x'
--------------------------------------------结束--------------------------------------------
--select * from xxl_cardinfo
--select * from xxl_userinfo
--select xxl_user_id from xxl_userinfo where xxl_user_idcard = '42028120000114125x'
上一篇： pl/sql下载