SQL SERVER 从入门到精通 第5版 第三篇 高级应用 第10章 存储过程 读书笔记

第10章 存储过程

　　>.存储过程概述

　　存储过程(stored procedure)是预编译SQL语句的集合,这些语句存储在 一个名称下并作为一个单元来处理.存储过程取代了传统的逐条执行SQL语句的方式.一个存储过程中可以包含增删改查等一系列SQL语句,当这个存储过程被调用时,这些操作也会同时被执行.

　　存储过程与其他编程语言中的过程类似,它可以接受输入参数并以输出参数的格式,它可以向调用过程或者批处理返回:

　　　　1.多个值;包含用于在数据库中执行操作(包括调用 其他过程)的编程语句;

　　　　2.状态值,以指明成功或者失败(以及失败的原因)

　　>.存储过程的分类:

　　　　>.系统存储过程:用来管理SQL SERVER和显示有关数据库与用户信息的存储过程;

　　　　>.自定义存储过程: 用户在SQL SERVER中通过采用SQL语句创建的存储过程;

　　　　>.扩展存储过程: 通过编程语言(例如C)创建外部全程,并将这个例程在SQL SERVER 中作为存储过程使用.

　　>.存储过程的优点:

　　　　>.存储过程可以嵌套使用,支持代码重用.

　　　　>.存储过程可以接受与使用参数,动态执行其中的SQL语句.

　　　　>.存储过程比一般的SQL语句执行速度愉.存储过程在创建时已经被编译,每次执行时不需要重新编译,而SQL语句每次执行时都需要编译.

　　　　>.存储过程具有安全性和所有权链接,以及可以附加到它们的证书.用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限.

　　　　>.存储过程允许模块化程序设计.存储过程一旦创建,以后可以在程序中多次调用.

　　　　>.存储过程可以减少网络通信流量.

　　　　>.存储过程可以保证应用程序的安全性.

　　>.创建存储过程:

　　　　>.使用向导创建一个名为test的存储过程.

 　　在"查询"菜单中,选择 "指定模板参数的值",然后修改存储过程的名称(Procedure_name)与SQL语句

 　　>.使用Create PROC语句来创建存储过程　　　　

    -- 此存储过程通过员工ID检索员工信息。
CREATE PROCEDURE [dbo].[GetEmployeeByID]     --GetEmployeeByID存储过程名称
    @EmployeeID INT    -- 参数(可以有多个),要检索的员工的ID。
AS
BEGIN    
    SET NOCOUNT ON;   --  用于防止额外的结果集干扰SELECT语句。

    -- 在此处插入存储过程的语句
    SELECT * 
    FROM Employees 
    WHERE EmployeeID = @EmployeeID;
END
GO

　　>.管理存储过程

　　　　用户可以在对应数据库的 [存储过程] 目录下查看所有存储过程

　　　　　　>.执行存储过程

　　　　　　　　存储过程可以通过EXECUTE或者它的缩写EXEC命令来执行,比如执行 test 存储过程可以写成:

EXEC test;

　　　　　　>.查看存储过程

　　　　　　有三种方法可以查看存储过程的定义:

　　　　　　　　1.使用SYS.SQL_MODULES

　　　　　　　　2.使用OBJECT_DEFINITION

　　　　　　　　3.使用SP_HELPTEXT

SELECT * FROM sys.sql_modules;
SELECT OBJECT_DEFINITION(661577395) AS sql;
EXEC sp_helptext 'test';

　　　　　　>.修改存储过程

　　　　　　　　虽然删除后再重新创建存储过程可以达到修改存储过程的目的,但是重建会丢失与该存储过程相关联的所有权限.

　　　　　　　　有两种方法可以实现对存储过程的修改,一种是通过菜单修改,一种是通过SQL语句修改.

　　　　　　　　1.通过菜单修改.如下图

　　　　　　　　2.使用ALTER PROCEDURE语句实现对存储过程的修改.

ALTER PROCEDURE [dbo].[test]
    -- 新的存储过程参数或注释可在这里添加
AS
BEGIN
    -- 存储过程的新逻辑可在这里添加
    -- 请注意，这里不需要重新声明已有的参数
END
GO

　　　　　　>.重命名存储过程

　　　　　　　　>.通过菜单重命名

 　　　　　　　　>.通过sp_rename  SQL语句重命名

EXEC sp_rename 'dbo.GetEmployeeByID', 'GetEmployeeByEmployeeID';

　　　　　　>.删除存储过程

 　　　　　　　　>.通过菜单删除.略

　　　　　　　　>.通过DROP PROCEDURE SQL语句删除　　

DROP PROCEDURE [dbo].[GetEmployeeByEmployeeID];

小例:

CREATE PROCEDURE [dbo].[YourStoredProcedureName]
AS
BEGIN
    DECLARE @truc INT;
    SET @truc = 0; -- 设置默认值，可以根据需要更改

    BEGIN TRY
        BEGIN TRANSACTION;
        -- 这里是存储过程的逻辑
        -- 使用条件判断语句处理事务
        IF @truc = 2
        BEGIN
            ROLLBACK TRANSACTION;    --回滚
            -- 返回值为25
            SELECT 25 AS ReturnValue;    --返回一个值为25
        END
        ELSE IF @truc = 0
        BEGIN
            COMMIT TRANSACTION;     --提交事务
            -- 返回值为0
            SELECT 0 AS ReturnValue;   --返回一个值为0
        END
        ELSE
        BEGIN
            -- 处理其他情况
        END
    END TRY
    BEGIN CATCH
        -- 处理错误
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- 抛出或记录错误信息
        THROW;
    END CATCH;
END