基于银行数据库 存储过程、函数、触发器、游标练习

--存储过程 函数 触发器

--1.输入任意支行，统计其总客户（存款客户和贷款客户）的个数，这里要单独写
--create function customer_count4(@branch_name nvarchar(30))
--returns integer
-- begin
--    declare @a int
--    declare @b innt
--    declare @c int
--    set @a=(select count(*) from account join depositor on(account.account_number=depositor.account_number) where branch_name=@branch_name)
--    set @b=(select count(*) from loan where branch_name=@branch_name)
--    set @c=@a+@b
--    return @c
--end

--select dbo.customer_count4('ABC') as customer_count

--drop function customer_count1
--drop procedure cun_loan_count

--法二 存储过程
--create procedure customer_count2(@branch_name nvarchar(30),@customer_count int output)
--as begin
--    declare @a int
--    declare @b int
    
--    set @a=(select count(*) from account join depositor on(account.account_number=depositor.account_number) where branch_name=@branch_name)
--    set @b=(select count(*) from loan where branch_name=@branch_name)
--    set @customer_count=@a+@b
--    end
--    declare @c_count int
--    execute customer_count2 @branch_name='abc',@customer_count=@c_count output
--    print @c_count

--2、输入任意支行，返回一个zhi行的贷款总额，存款总额，总资产
--create procedure money_count(@branch_name nvarchar(30),@amount_count int output,@balance_count int output,@assets_count int output)
--as begin
--    set @amount_count=(select sum(amount) from loan where branch_name=@branch_name)
--    set @balance_count=(select sum(balance) from account where branch_name=@branch_name)
--    set @assets_count=(select assets from branch where branch_name=@branch_name)
--end

--declare @am int
--declare @ba int
--declare @as int
--execute money_count @branch_name='abc',@amount_count=@am output,@balance_count=@ba output,@assets_count=@as output
--print @am
--print