储存过程

╰半夏微凉° 2022-09-24 12:19 201阅读 0赞

创建基本储存过程：

CREATE PROC spEmployee
    AS
       SELECT * FROM HumanResources.Employee;
    GO

使用ALTER修改储存过程

使用ALTERPROC和CREATE PROC语句的唯一区别：

ALTER PROC:期望找到一个已有的储存过程，而CREARE则不是。

ALTER PROC:保留了储存过程上已经建立的任何权限。

ALTER PROC：在可能调用被修改的储存过程的其他对象上保留了人和依赖信息。

删除储存过程：

DROP PROC |PROCEDURE<sproc name>\[;\]

参数化储存过程：

声明参数：

名称。

数据类型。

默认值。

方向。

注：对于名称，有一组简单的规则。首先，他必须以@符号开始。声明数据类型时需要记住的一点是，当声明CURSOR类型参数时，必须也使用VARYING和OUTPUT选项。OUTPUT可简写为OUT。

CREATE PROC spEmployeeByName
        @LastName  nvarchar(50)
    AS
    
    SELECT p.LastName, p.FirstName, e.JobTitle, e.HireDate
    FROM Person.Person p
    JOIN HumanResources.Employee e
        ON p. BusinessEntityID = e.BusinessEntityID
    WHERE p.LastName LIKE @LastName + '%';
    GO
    
    
    EXEC spEmployeeByName 'Dobney';

1、提供默认值

CREATE PROC spEmployeeByName
    @LastName nvarchar(50) = NULL
    AS
    IF @LastName IS NOT NULL
        SELECT p.LastName, p.FirstName, e.JobTitle, e.HireDate
        FROM Person.Person p
        JOIN HumanResources.Employee e
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE p.LastName LIKE @LastName + '%';
    ELSE
        SELECT p.LastName, p.FirstName, e.JobTitle, e.HireDate
        FROM Person.Person p
        JOIN HumanResources.Employee e
            ON p.BusinessEntityID = e.BusinessEntityID;
    
    GO
    
    EXEC spEmployeeByName;
    
    GO

2、创建输出参数

BEGIN TRY
    -- Try and create our table
    CREATE TABLE OurIFTest(
        Col1    int     PRIMARY KEY
        )
    END TRY
    BEGIN CATCH
        -- Uh oh, something went wrong, see if it's something
        -- we know what to do with
        DECLARE @MyOutputParameter int;
    
        IF ERROR_NUMBER() = 2714 -- Object exists error, we knew this might happen
        BEGIN
            PRINT 'WARNING: Skipping CREATE as table already exists';
            EXEC dbo.uspLogError @ErrorLogID = @MyOutputParameter OUTPUT;
            PRINT 'An error was logged. The Log ID for our error was ' 
                    + CAST(@MyOutputParameter AS varchar);
        END
        ELSE    -- hmm, we don't recognize it, so report it and bail
            RAISERROR('something not good happened this time around', 16, 1 );
    END CATCH
    GO

SELECT * 
    FROM ErrorLog
    WHERE ErrorLogID = 1; -- change this value to whatever your 
                         -- results said it was logged as
    GO

注：对于储存过程声明中的输出参数，需要使用OUTPUT的关键字。和声明储存过程一样，调用储存过程时必须使用OUTPUT关键字。赋给输出结果的变量不需要和储存过程中的内部参数拥有相同的名称。EXEC关键字是必须的，因为对储存过程的调用并不是批处理要做的第一件事。

如何使用RETURN语句：

事实上，不管是否提供返回值，程序都会收到一个返回值。SQL Server默认会在完成储存过程是自动返回一个0值。

语法：

RETURN \[<interger value to return >\]

注：返回值必须为整数。

例：

CREATE PROC spTestReturns
    AS
       DECLARE @MyMessage        varchar(50);
       DECLARE @MyOtherMessage   varchar(50);
    
       SELECT @MyMessage = 'Hi, it''s that line before the RETURN';
       PRINT @MyMessage;
       RETURN;
       SELECT @MyOtherMessage = 'Sorry, but we won''t get this far';
       PRINT @MyOtherMessage;
    RETURN;
    GO
    
    DECLARE @Return int;
    
    EXEC @Return = spTestReturns;
    SELECT @Return;
    
    GO

错误处理：

错误类型：

产生运行时错误并终止代码继续运行的错误。

SQL Server 知道的、但不产生使代码停止运行时错误的错误。这类错误也称为内联错误。

更具逻辑性但在SQL Server中不太引起注意的错误。

处理内联错误：

内联错误是指那些能让SQL Server继续运行但是因为某种原因而不能成功完成指定任务的错误。

利用@@ERROR：

@@ERROR包含了最后执行的T-SQL语句的错误号。如果该值为0，则没有发生错误。@@ERROR根据执行的每条语句接收一个新值。

例：

DECLARE   @Error   int;
    
    -- Bogus INSERT - there is no PersonID or BusinessEntityID of 0. Either of 
    -- these could cause the error we see when running this statement.
    INSERT INTO Person.BusinessEntityContact
               (BusinessEntityID
               ,PersonID
               ,ContactTypeID)
         VALUES
               (0,0,1);
    
    -- Move our error code into safekeeping. Note that, after this statement,
    -- @@Error will be reset to whatever error number applies to this statement
    PRINT 'The Value of @@ERROR is ' + CONVERT(varchar, @@ERROR);
    SELECT @Error = @@ERROR;
    
    -- Print out a blank separator line
    PRINT '';
    
    -- The value of our holding variable is just what we would expect
    PRINT 'The Value of @Error is ' + CONVERT(varchar, @Error);
    
    -- The value of @@ERROR has been reset - it's back to zero
    -- since our last statement (the PRINT) didn't have an error.
    PRINT 'The Value of @@ERROR is ' + CONVERT(varchar, @@ERROR);

在储存过程中使用@@ERROR：

注：建议使用TRY/CATCH更简洁更通用的方法，TRY/CATCH将处理之前的版本中终止脚本执行的各种错误。

通过TRY/CATCH块，可以捕获并处理这一错误，但使用内联错误检查，尝试捕获这样的错误就会失败。

手动引发错误：

消息 ID/消息字符串：

消息ID或消息字符串决定了向客户端发送的消息。

使用消息ID可以手动引发带有指定的ID以及和该ID相关消息的错误，该错误消息可以在master数据库中的sys.message系统视图中找到。

可以以特殊文本的形式提供消息字符串么人不用在系统中创建一个更持久的消息。

重新抛出错误：THROW

THROW和RAISERROR之间的区别：

使用THROW时，error\_number参数不需要已经存在于sysmessages中。但是，应该确保在某个位置定义您的错误。

RAISERROR消息字符串使用printf样式的语法进行动态消息传递，而THROW不这样做。

THROW之前的语句必须以分号终止。

例：

BEGIN TRY
    	INSERT OurIFTest(Col1) VALUES (1);
    END TRY
    BEGIN CATCH
        -- Uh oh, something went wrong, see if it's something
        -- we know what to do with
        PRINT  'Arrived in the CATCH block.';
    
        DECLARE @ErrorNo    int,
                @Severity    tinyint,
                @State        smallint,
                @LineNo        int,
                @Message    nvarchar(4000);
        SELECT 
            @ErrorNo = ERROR_NUMBER(),
            @Severity = ERROR_SEVERITY(),
            @State = ERROR_STATE(),
            @LineNo = ERROR_LINE (),
            @Message = ERROR_MESSAGE();
    
        IF @ErrorNo = 2714 -- Object exists error, not likely this time
            PRINT 'WARNING: object already exists';
        ELSE -- hmm, we don't recognize it, so report it and bail
            THROW;
    END CATCH

添加自定义的错误消息：

可以利用特殊的系统存储过程向系统添加自己的消息，这个储存过程称为sp\_addmessage。

1、@lang

这个参数指定了消息应用语言，在该参数中可以为syslanguages中支持的任何语言指定消息的一个单独版本。

2、@whith\_log

其工作方式与RAISERROR中的一样，如果设为TRUE，当出现错误的时候，会像SQL Server错误日志和NT应用程序日志自动记录错误消息（只有运行在NT环境下才会实现后一操作），这里唯一特别之处就是通过把该参数设置为TRUE而不是使用WITH LOG选项来把消息写入日志。

3、@replace

如果是编辑现有的消息而非创建一个新消息，那么必须把@replace参数设置为REPLACE。倘若不这样做，那么消息已经存在，就会得到一个错误。

4、使用sp\_addmessage

如前所述，sp\_addmessage创建消息的方式和使用RAISERROR创建特殊消息的方式是一样的。

5、删除已有的自定义消息

sp\_dropmessage<message number>

递归简介：

例：

CREATE PROC spFactorial
    @ValueIn int,
    @ValueOut int OUTPUT
    AS 
    DECLARE @InWorking int;
    DECLARE @OutWorking int;
    IF @ValueIn > 1 
    BEGIN
          SELECT @InWorking = @ValueIn - 1;
    
          EXEC spFactorial @InWorking, @OutWorking OUTPUT;
    
          SELECT @ValueOut = @ValueIn * @OutWorking;
    END
    ELSE
    BEGIN
          SELECT @ValueOut = 1;
    END 
    RETURN;
    GO

DECLARE @WorkingOut int;
    DECLARE @WorkingIn int;
    SELECT @WorkingIn = 5;
    EXEC spFactorial @WorkingIn, @WorkingOut OUTPUT;
    
    PRINT CAST(@WorkingIn AS varchar) + ' factorial is ' + CAST(@WorkingOut AS varchar);
    
    GO

注：一旦SQL Server进行了32次的递归，则他会引发错误并停止处理。