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 '編號(hào) '+'名稱 '+'地址 '+'城市 '+'總金額 '+'賬戶數(shù) '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('該銀行總金額少于一百萬(wàn)')print(@SQL+'已執(zhí)行')exec(@SQL)endif(@counts=0)-- else if(@counts=0)beginprint('該銀行不存在用戶')set @SQL='delete from bank with b_id='''+@ids+''''print(@SQL+'已執(zhí)行')print('該銀行已被刪除')exec(@SQL)endend;