存储过程

 存储过程的优点

    1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
    2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
    3.存储过程可以重复使用,可减少数据库开发人员的工作量
    4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权



-- =============================================
-- Author:<Author,,laishunsheng>
-- Create date: <Create Date,,2008-12-27>
-- Description:    <Description,,无参数的存储过程>
-- =============================================
CREATE PROCEDURE pro_select_book    
AS

BEGIN
    select * from tb_book
END


-- =============================================
-- Author:<Author,,laishunsheng>
-- Create date: <Create Date,,2008-12-27>
-- Description:    <Description,,要传入一个参数的存储过程>
-- =============================================
CREATE PROCEDURE pro_selectBookByColor
(
@book_color nchar(10)
)
AS
BEGIN
select * from tb_book where book_color = @book_color
END

--=============================================
执行上面的存储过程
--=============================================
execute pro_selectBookByColor 'Red'


-- =============================================
-- Author:<Author,,LaiShunsheng>
-- Create date: <Create Date,,2008-12-27>
-- Description:    <Description,,要传多个参数的存储过程>
-- =============================================
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,,要传入多个参数的插入记录存储过程>
-- =============================================
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,,根据书名删除该本书的记录的存储过程>
-- =============================================
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,,修改某书库存数量的存储过程>
-- =============================================
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,,带输出参数的存储过程>
-- =============================================
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,,带输出参数的存储过程>
-- =============================================
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,,带输出参数的存储过程>
-- =============================================
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 !'