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

--存储过程 函数 触发器

--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 [email protected]_name)

--    set @b=(select count(*) from loan where [email protected]_name)

--    set @[email protected][email protected]

--    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 [email protected]_name)

--    set @b=(select count(*) from loan where [email protected]_name)

--    set @[email protected][email protected]

--    end

--    declare @c_count int

--    execute customer_count2 @branch_name='abc',@[email protected]_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 [email protected]_name)

--    set @balance_count=(select sum(balance) from account where [email protected]_name)

--    set @assets_count=(select assets from branch where [email protected]_name)

--end

--declare @am int

--declare @ba int

--declare @as int

--execute money_count @branch_name='abc',@[email protected] output,@[email protected] output,@[email protected] output

--print @am

--print @ba

--print @as

--3.输入任意支行，返回存款客户，客户名称及居住地点

--drop function customer_information

--create function customer_information(@branch_name nvarchar(30))

--returns table

--as return(

--    select c.customer_name,c.customer_street,c.customer_city

--    from account as a,depositor as d,customer as c

--    where [email protected]_name

--)

--select distinct * from customer_information('abc')

--触发器复习

--1.一个用于跟踪哪个用户、何时间更新了Account表的balance，

--drop table table2

--create table table10(

--    account_number nvarchar(30),

--    time datetime,

--    username nvarchar(30),

--    o_balance float,

--    i_balance float

--)

create trigger trigger0 on account after update

as

begin

    if(UPDATE(balance))

        begin

            declare @account_number nvarchar(30)

            declare @o_balance float

            declare @i_balance float

            declare aaa cursor for select i.account_number, i.balance,d.balance from inserted as i,deleted as d

                    open aaa

                    fetch next from aaa into @account_number,@o_balance,@i_balance

                    while @@FETCH_STATUS=0

                    begin

                        insert into table10 values(@account_number,GETDATE(),USER_NAME(),@o_balance,@i_balance)

                        fetch next from aaa into @account_number,@o_balance,@i_balance

                    end

                    close aaa

                    deallocate aaa

        end

    end

update account set balance=1000 where account_number='1'

select * from table10

--2.另一个用于跟踪哪个用户、何时间更新了loan表的amount，

--3.当Branch表的city为NULL时，一律写成“presently uncertain”

--drop trigger trigger1

--create trigger trigger2 on branch after update

--as

--begin

--    if(update(branch_city))

--    begin

--        declare @branch_name nvarchar(30)

--        declare @city nvarchar(30)

--        declare aaa cursor for select branch_city,branch_name from inserted

--        open aaa

--        fetch  next from aaa into @city,@branch_name

--        while @@fetch_status=0

--        begin

--            if(@city is null)

--                begin

--                update branch set branch_city='presently uncertain' where [email protected]_name

--                fetch  next from aaa into @city,@branch_name

--                end

--            else

--                begin

--                update branch set [email protected] where [email protected]_name

--                fetch  next from aaa into @city,@branch_name

--                end

--        end

--        close aaa

--        deallocate aaa

--    end

--end

--insert into branch values('chen' ,'yantai',1000)

--update branch set branch_city=null where branch_name='abc'

--select * from branch

4、透支触发器

当balance更新为负值时，向loan插入相应绝对值的贷款信息，同时向borrower插入相应的关联信息，并将balance置为0

if exists (select name from sysobjects where name = 'OverDraftTrigger' and type = 'TR')

drop trigger OverDraftTrigger

drop trigger Trigger0

go

create trigger OverDraftTrigger on account after update

as

    begin

        if update(balance)

            begin    

                declare @account_number  nvarchar(30)

                declare @branch_name nvarchar(50)

                declare @balance  numeric(10,2)

                declare aaa cursor for select account_number,branch_name,balance from inserted

                open aaa

                fetch next from aaa into @account_number,@branch_name,@balance

                    while @@FETCH_STATUS=0

                        begin    

                            if(@balance<0)

                                begin

                                    update account set balance=0 where account_number = @account_number

                                    set @[email protected]*(-1)

                                    insert into loan values( @account_number,@branch_name,@balance)

                                    insert into borrower select * from depositor where [email protected]_number

                                end

                            fetch next from aaa into @account_number,@branch_name,@balance

                        end

                close aaa

                deallocate aaa

            end

    end

    select * from account

    update account set balance=-100.00 where account_number='15'

    select * from loan

    select * from borrower

    select * from depositor