为了账号安全,请及时绑定邮箱和手机立即绑定

如果这些参数要作为表提示,则需要使用什么来实现?

如果这些参数要作为表提示,则需要使用什么来实现?

回首忆惘然 2022-10-20 17:15:23
create procedure bank_pro @ids varchar(10)asdeclare@bid varchar(10),@bname varchar(20),@baddr varchar(30),@bcity varchar(30),@sums money,@counts int,@SQL varchar(500)declare @curs cursor--select @sums = sum(a_balance) from account where a_bank_num = @ids;--select @counts=count(a_bank_num) from account where a_bank_num=@idsset @curs=cursor scroll dynamicforselect b.b_id,b.b_name,b.b_address,b.b_city,sum(a.a_balance),count(a.a_id)from bank b left join account a on b.b_id=a.a_bank_numwhere b.b_id = @idsgroup by b.b_id,b.b_name,b.b_address,b.b_cityopen @cursfetch next from @curs into @bid,@bname,@baddr,@bcity,@sums,@countswhile(@@fetch_status=0)beginprint '编号 '+'名称 '+'地址 '+'城市 '+'总金额 '+'账户数 'print @bid+@bname+@baddr+@bcity+convert(varchar(20),@sums)+' '+convert(varchar(20),@counts)fetch next from @curs into @bid,@bname,@baddr,@bcity,@sums,@countsif(@sums<1000000)beginset @SQL='insert into account() values(''1010'',''天天'',''60000'',''2012-11-23'','''+@ids+''')'print('该银行总金额少于一百万')print(@SQL+'已执行')exec(@SQL)endif(@counts=0)-- else if(@counts=0)beginprint('该银行不存在用户')set @SQL='delete from bank with b_id='''+@ids+''''print(@SQL+'已执行')print('该银行已被删除')exec(@SQL)endend;
查看完整描述

1 回答

?
慕丝7291255

TA贡献1859条经验 获得超6个赞

declare curs cursor
for
select b.b_id,b.b_name,b.b_address,b.b_city,sum(a.a_balance),count(a.a_id)
from bank b left join account a on b.b_id=a.a_bank_num
where b.b_id = @ids
group by b.b_id,b.b_name,b.b_address,b.b_city

open curs
fetch next from curs into @bid,@bname,@baddr,@bcity,@sums,@counts
while(@@fetch_status=0)
begin
print '编号 '+'名称 '+'地址 '+'城市 '+'总金额 '+'账户数 '
print @bid+@bname+@baddr+@bcity+convert(varchar(20),@sums)+' '+convert(varchar(20),@counts)
fetch next from @curs into @bid,@bname,@baddr,@bcity,@sums,@counts
if(@sums<1000000)
begin
set @SQL='insert into account() values(''1010'',''天天'',''60000'',''2012-11-23'','''+@ids+''')'
print('该银行总金额少于一百万')
print(@SQL+'已执行')
exec(@SQL)
end
if(@counts=0)
-- else if(@counts=0)
begin
print('该银行不存在用户')
set @SQL='delete from bank with b_id='''+@ids+''''
print(@SQL+'已执行')
print('该银行已被删除')
exec(@SQL)
end

CLOSE curs
DEALLOCATE curs

end


查看完整回答
反对 回复 2022-10-24
  • 1 回答
  • 0 关注
  • 87 浏览
慕课专栏
更多

添加回答

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信