將最美的計算機軟件奉獻給社會

存儲過程的優(yōu)點 1.存儲過程只在創(chuàng)造時進行編譯，以后每次執(zhí)行存儲過程都不需再重新編譯，而一般SQL語句每執(zhí)行一次就編譯一次,所以使用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度。 2.當(dāng)對數(shù)據(jù)庫進行復(fù)雜操作時(如對多個表進行Update,Insert,Query,Delete時），可將此復(fù)雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務(wù)處理結(jié)合一起使用。 3.存儲過程可以重復(fù)使用,可減少數(shù)據(jù)庫開發(fā)人員的工作量 4.安全性高,可設(shè)定只有某此用戶才具有對指定存儲過程的使用

 存儲過程的優(yōu)點

    1.存儲過程只在創(chuàng)造時進行編譯，以后每次執(zhí)行存儲過程都不需再重新編譯，而一般SQL語句每執(zhí)行一次就編譯一次,所以使用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度。
    2.當(dāng)對數(shù)據(jù)庫進行復(fù)雜操作時(如對多個表進行Update,Insert,Query,Delete時），可將此復(fù)雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務(wù)處理結(jié)合一起使用。
    3.存儲過程可以重復(fù)使用,可減少數(shù)據(jù)庫開發(fā)人員的工作量
    4.安全性高,可設(shè)定只有某此用戶才具有對指定存儲過程的使用權(quán)



-- =============================================
-- Author:<Author,,laishunsheng>
-- Create date: <Create Date,,2008-12-27>
-- Description:    <Description,,無參數(shù)的存儲過程>
-- =============================================
CREATE PROCEDURE pro_select_book    
AS

BEGIN
    select * from tb_book
END


-- =============================================
-- Author:<Author,,laishunsheng>
-- Create date: <Create Date,,2008-12-27>
-- Description:    <Description,,要傳入一個參數(shù)的存儲過程>
-- =============================================
CREATE PROCEDURE pro_selectBookByColor
(
@book_color nchar(10)
)
AS
BEGIN
select * from tb_book where book_color = @book_color
END

--=============================================
執(zhí)行上面的存儲過程
--=============================================
execute pro_selectBookByColor 'Red'


-- =============================================
-- Author:<Author,,LaiShunsheng>
-- Create date: <Create Date,,2008-12-27>
-- Description:    <Description,,要傳多個參數(shù)的存儲過程>
-- =============================================
CREATE PROCEDURE pro_selectBookByColor_2th
(
    @color char(10),
    @name char(20)
)
AS
BEGIN
    select * from tb_book where book_color=@color and book_name=@name
END


-- =============================================
-- Author:<Author,,laishunsheng>
-- Create date: <Create Date,,2008-12-27>
-- Description:    <Description,,要傳入多個參數(shù)的插入記錄存儲過程>
-- =============================================
create procedure pro_insertRecords
(
    @name char(20),
    @color char(10),
    @type int,
    @amount int
)
as
begin
insert into tb_book(book_name,book_color,book_type,book_amount) values(@name,@color,@type,@amount)
end


-- =============================================
-- Author:<Author,,laishunsheng>
-- Create date: <Create Date,,2008-12-27>
-- Description:    <Description,,根據(jù)書名刪除該本書的記錄的存儲過程>
-- =============================================
create procedure pro_deleteBook
(
    @name char(10)
)
as
begin
delete from tb_book where book_name = @name
end

-- =============================================
-- Author:<Author,,laishunsheng>
-- Create date: <Create Date,,2008-12-27>
-- Description:    <Description,,修改某書庫存數(shù)量的存儲過程>
-- =============================================
create procedure pro_updateBook_amount
(
    @name char(10),
    @amount int
)
as
begin
update tb_book set book_amount=@amount where book_name = @name
end


-- =============================================
-- Author:<Author,,laishunsheng>
-- Create date: <Create Date,,2008-12-27>
-- Description:    <Description,,帶輸出參數(shù)的存儲過程>
-- =============================================
create procedure pro_output
(
    @SUM int output,
    @type int
)
as
select @SUM = sum(book_amount) from(select * from tb_book where book_type=@type) as tempTable


-- =============================================
-- Author:        <Author,,laishunsheng>
-- Create date: <Create Date,2008-12-27,>
-- Description:    <Description,加密>
-- =============================================
CREATE PROCEDURE [dbo].[pro_select_book_encryption]
with encryption     
AS

BEGIN
    select * from tb_book
END



-- =============================================
-- Author:<Author,,laishunsheng>
-- Create date: <Create Date,,2008-12-27>
-- Description:    <Description,,帶輸出參數(shù)的存儲過程>
-- =============================================
create procedure pro_output_3
(
    @amount int output,
    @type int
    
)
as
begin
select @amount = sum(book_amount) from tb_book where book_type = @type
end


-- =============================================
-- Author:<Author,,laishunsheng>
-- Create date: <Create Date,,2008-12-27>
-- Description:    <Description,,帶輸出參數(shù)的存儲過程>
-- =============================================
create procedure pro_output_3
(
    @amount int output,  
)
as
begin
select @amount = sum(book_amount) from tb_book
end



-- =============================================
-- Author:<Author,,laishunsheng>
-- Create date: <Create Date,,2008-12-27>
-- Description:    <Description,,多表連接的存儲過程>
-- =============================================
create procedure pro_temporarySale
as
select p.product_id,p.product_name,o.order_customer,o.order_amount,p.product_bookAmount*o.order_amount as TotalMoney
into #tempTable from tb_product p inner join tb_order o on p.product_id = o.product_id
if @@error=0
print 'good !'
else
print 'fail !'