ATM-简单SQL查询
程序员文章站
2023-11-14 08:43:04
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'
下一篇: Flash对齐工具怎么对齐图形?