满意答案
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
00分享举报