基于銀行資料庫 存儲過程、函數、觸發器、遊标練習

--存儲過程 函數 觸發器

--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