存储过程学习

存储过程学习

什么是存储过程呢？

定义：
      存储过程是指一组作为单个代码单元一起存储于数据库中的 T-SQL 语句。您可以使用输入参数传入运行时信息，并取回作为结果集或输出参数的数据。

存储过程的优点：
                      1.使用存储过程可提高数据库执行速度。存储过程在首次运行时将被编译。这将产生一个执行计划 - 实际上是 Microsoft® SQL Server™ 为在存储过程中获取由 T-SQL 指定的结果而必须采取的步骤的记录。然后，执行计划在内存中得到缓存，以备以后使用。这样会改善存储过程的性能，因为 SQL Server 无需为确定如何处理代码而重新分析它，而只需引用缓存的计划即可。这个缓存的计划一直可用，直到 SQL Server 重新启动，或直到它由于使用率较低而溢出内存。
                        2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
                      3.存储过程可以重复使用,可减少数据库开发人员的工作量
                      4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。它们可以防止某些类型的 SQL 插入攻击

存储过程的种类：
    1.系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,如 sp_help就是取得指定对象的相关信息
  2.扩展存储过程  以XP_开头,用来调用操作系统提供的功能
                              exec master..xp_cmdshell 'ping 10.8.16.1'
  3.用户自定义的存储过程,这是我们所指的存储过程
常用格式

Create procedure procedue_name
[@parameter data_type][output]
[with]{recompile|encryption}
as
    sql_statement
解释: 
output：通过使用 OUTPUT 参数返回标量数据，可以略微提高速度并节省少量的处理功率。在应用程序需要返回单个值的情况下，请尝试此方法
with {recompile|encryption}
recompile:表示每次执行此存储过程时都重新编译一次
encryption:所创建的存储过程的内容会被加密
例：
下面这个例子，传入一个参数@username,判断用户是否存在。
判断用户是否存在
-------------------------------------------------------------------------------
CREATE PROC IsExistUser
(
    @username varchar(20),
    @IsExistTheUser varchar(25) OUTPUT--输出参数
)
as
    SELECT @IsExistTheUser = count(username)
    FROM users
    WHERE username = @username 
GO
下面这个例子是存储过程结合事务的一个例子，添加用户，涉及到两个表，一个用户资料表，一个密码表：
增加用户
-------------------------------------------------------------------------------
create procedure AddUser
(
    @username varchar(20),--用户名
    @passwords varchar(18),--密码
    @email varchar(100),--邮箱
    @qq varchar(11),--QQ号码
    @imageid int--头像
)
as
    -- 设置NOCOUNT为ON，使不在反返统计信息（存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。）
    SET NOCOUNT ON

    DECLARE @CurrentError int

    -- 创建事务, 插入数据到2个表
    BEGIN TRANSACTION

    -- 创建用户信息
    INSERT INTO users (username, email, qq, imageid)
    VALUES (@username, @email, @qq, @imageid)

    select @CurrentError = @@Error

    IF @CurrentError != 0
        BEGIN
              GOTO ERROR_HANDLER
        END

    -- 创建用户密码
    INSERT INTO Signon (username, [password])
    VALUES (@username, @passwords)

    select @CurrentError = @@Error

    IF @CurrentError != 0
        BEGIN
              GOTO ERROR_HANDLER
        END

    -- 结束事务
    COMMIT TRANSACTION

    -- 恢复NOCOUNT设置为OFF
    SET NOCOUNT OFF

    -- 返回0表示成功，其他则为失败
    RETURN 0

    -- 错误处理
    ERROR_HANDLER:
        ROLLBACK TRANSACTION
        SET NOCOUNT OFF
        RETURN @CurrentError
    GO