Sql server 2005中output用法解析

﹏ヽ暗。殇╰゛Y 2022-01-28 11:07 340阅读 0赞

http://www.cnblogs.com/yuananyun/archive/2010/12/04/1896276.html

**一、关于output子句**

SQL Server 2005中的output子句，可以使你从修改语句（INSERT、UPDATE、DELETE）中将数据返回到表变量中。带结果的 DML 的有用方案包括清除和存档、消息处理应用程序以及其他方案。这一新的 OUTPUT 子句的语法为：

OUTPUT INTO @table\_variable

可以通过引用插入的表和删除的表来访问被修改的行的旧/新映像，其方式与访问触发器类似。在 INSERT 语句中，只能访问插入的表。在 DELETE 语句中，只能访问删除的表。在 UPDATE 语句中，可以访问插入的表和删除的表。

**二、实例说明**

**1、将 OUTPUT INTO 用于简单 INSERT 语句**

以下示例将行插入 ScrapReason 表，并使用 OUTPUT 子句将语句的结果返回到 @MyTableVar table 变量。由于 ScrapReasonID 列使用 IDENTITY 属性定义，因此未在 INSERT 语句中为该列指定一个值。但请注意，将在列 INSERTED.ScrapReasonID 内的 OUTPUT 子句中返回由数据库引擎 为该列生成的值。

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

use
        
          AdventureWorks
    
        
         go
        
         
    
        
         --
        
         定义一个表格变量
        
         
    
        
         declare
        
          
        
         @mytablevar
        
          
        
         table
        
         ( ScrapReasonID 
        
         smallint
        
         ,
                               Name1 
        
         varchar
        
         (
        
         50
        
         ),
                               ModifiedDate 
        
         datetime
        
         )
    
        
         insert
        
          
        
         into
        
          Production.ScrapReason
    output inserted.ScrapReasonID,inserted.
        
         [
        
         Name
        
         ]
        
         ,inserted.ModifiedDate 
        
         into
        
          
        
         @mytablevar
        
         
    
        
         values
        
          (
        
         '
        
         operator error
        
         '
        
         ,
        
         getdate
        
         ());
    
        
         --
        
         显示@mytablevar中的数据
        
         
    
        
         select
        
          
        
         *
        
          
        
         from
        
          
        
         @mytablevar
        
         
    
        
         --
        
         显示Production.ScrapReason表中的数据
        
         
    
        
         select
        
          
        
         *
        
          
        
         from
        
          Production.ScrapReason
    
        
         go

![复制代码][copycode.gif]

以上语句中inserted是一个临时表，当我们往数据表中插入一条数据的时候数据库会创建一个临时表inserted保存插入的记录；当我们删除某条记录的时候，数据库会创建一个临时表deleted保存删除的记录。以上语句把新插入的数据填充到表变量@mytablevar中，然后输出数据，可以看到@mytablevar中的记录和Production.ScrapReason中新插入的数据是一样的。

**2、 将 OUTPUT 用于 INSERT…SELECT 语句**

以下示例创建 EmployeeSales 表，然后通过使用 SELECT 语句检索源表中的数据将几行插入该表。同时，也计算了列 ProjectedSales 的值并将其插入该表中。OUTPUT 子句将 INSERT 语句的结果返回到执行调用的应用程序。最后的 SELECT 语句验证新 EmployeeSales 表的内容是否与 OUTPUT 子句的结果匹配。

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

use
        
          AdventureWorks
    
        
         go
        
         
    
        
         if
        
          
        
         object_id
        
         (
        
         '
        
         dbo.EmployeeSales
        
         '
        
         ,
        
         '
        
         u
        
         '
        
         ) 
        
         is
        
          
        
         not
        
          
        
         null
        
         
    
        
         drop
        
          
        
         table
        
          dbo.EmployeeSales
    
        
         go
        
         
    
        
         create
        
          
        
         table
        
          dbo.EmployeeSales
    (
        EmployeeID 
        
         nvarchar
        
         (
        
         11
        
         ) 
        
         not
        
          
        
         null
        
         ,
        LastName 
        
         nvarchar
        
         (
        
         20
        
         ) 
        
         not
        
          
        
         null
        
         ,
        FirstName 
        
         nvarchar
        
         (
        
         20
        
         ) 
        
         not
        
          
        
         null
        
         ,
        CurrentSales 
        
         money
        
          
        
         not
        
          
        
         null
        
         ,
        ProjectedSales 
        
         money
        
          
        
         not
        
          
        
         null
        
         
    
    )
    
        
         go
        
         
    
        
         insert
        
          
        
         into
        
          dbo.EmployeeSales
    output inserted.EmployeeID,inserted.LastName,inserted.FirstName,inserted.CurrentSales,inserted.ProjectedSales
    
        
         SELECT
        
          e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD, sp.SalesYTD 
        
         *
        
          
        
         1.10
        
         
    
        
         FROM
        
          HumanResources.Employee 
        
         AS
        
          e
            
        
         INNER
        
          
        
         JOIN
        
          Sales.SalesPerson 
        
         AS
        
          sp
            
        
         ON
        
          e.EmployeeID 
        
         =
        
          sp.SalesPersonID 
            
        
         INNER
        
          
        
         JOIN
        
          Person.Contact 
        
         AS
        
          c
            
        
         ON
        
          e.ContactID 
        
         =
        
          c.ContactID
        
        
         WHERE
        
          e.EmployeeID 
        
         LIKE
        
          
        
         '
        
         2%
        
         '
        
         
        
        
         ORDER
        
          
        
         BY
        
          c.LastName, c.FirstName;
    
        
         GO
        
         
    
        
         SELECT
        
          EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
    
        
         FROM
        
          dbo.EmployeeSales;
    
        
         GO

![复制代码][copycode.gif]

**3、 将 OUTPUT 用于 DELETE 语句**

以下示例将删除 ShoppingCartItem 表中的所有行。子句 OUTPUT DELETED.\* 指定 DELETE 语句的结果（即已删除的行中的所有列）返回到执行调用的应用程序。后面的 SELECT 语句验证对 ShoppingCartItem 表所执行的删除操作的结果。

![复制代码][copycode.gif]

USE
       
         AdventureWorks;
    
       
        GO
       
        
    
       
        DELETE
       
         Sales.ShoppingCartItem
        OUTPUT DELETED.
       
        *
       
         ;
    
    
       
        --
       
        验证表中所有数据都被删除
       
        
    
       
        SELECT
       
         
       
        COUNT
       
        (
       
        *
       
        ) 
       
        AS
       
         
       
        [
       
        Rows in Table
       
        ]
       
         
       
        FROM
       
         Sales.ShoppingCartItem;
    
       
        GO

![复制代码][copycode.gif]

**4、 将 OUTPUT INTO 用于 UPDATE**

下面的示例将 Employee 表中 VacationHours 列的前 10 行更新 25%。OUTPUT 子句将返回 VacationHours 值，该值在将列 DELETED.VacationHours 中的 UPDATE 语句和列 INSERTED.VacationHours 中的已更新值应用于 @MyTableVar table 变量之前存在。  
在它后面的两个 SELECT 语句返回 @MyTableVar 中的值以及 Employee 表中更新操作的结果。请注意，INSERTED.ModifiedDate 列中的结果与 Employee 表中的 ModifiedDate 列不具有相同的值。这是因为对 Employee 表定义了将 ModifiedDate 的值更新为当前日期的 AFTER UPDATE 触发器。但是，从 OUTPUT 中返回的列反映触发器激发之前的数据。

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

USE
        
          AdventureWorks;
    
        
         GO
        
         
    
        
         DECLARE
        
          
        
         @MyTableVar
        
          
        
         table
        
         (
        EmpID 
        
         int
        
          
        
         NOT
        
          
        
         NULL
        
         ,
        OldVacationHours 
        
         int
        
         ,
        NewVacationHours 
        
         int
        
         ,
        ModifiedDate 
        
         datetime
        
         );
    
        
         UPDATE
        
          
        
         TOP
        
          (
        
         10
        
         ) HumanResources.Employee
    
        
         SET
        
          VacationHours 
        
         =
        
          VacationHours 
        
         *
        
          
        
         1.25
        
          
    OUTPUT INSERTED.EmployeeID,
           DELETED.VacationHours,
           INSERTED.VacationHours,
           INSERTED.ModifiedDate
    
        
         INTO
        
          
        
         @MyTableVar
        
         ;
    
        
         --
        
         显示@MyTableVar的值
        
         
    
        
         SELECT
        
          EmpID, OldVacationHours, NewVacationHours, ModifiedDate
    
        
         FROM
        
          
        
         @MyTableVar
        
         ;
    
        
         GO
        
         
    
        
         --
        
         显示插入表的值
        
         
    
        
         SELECT
        
          
        
         TOP
        
          (
        
         10
        
         ) EmployeeID, VacationHours, ModifiedDate
    
        
         FROM
        
          HumanResources.Employee;
    
        
         GO

![复制代码][copycode.gif]

**5、 使用 OUTPUT INTO 返回表达式**

以下示例在示例 D 的基础上生成，方法是通过将 OUTPUT 子句中的表达式定义为已更新的 VacationHours 值与应用更新之前的 VacationHours 值之间的差异。该表达式的值返回到列 VacationHoursDifference 中的 @MyTableVar table 变量。

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

USE
        
          AdventureWorks;
    
        
         GO
        
         
    
        
         DECLARE
        
          
        
         @MyTableVar
        
          
        
         table
        
         (
        EmpID 
        
         int
        
          
        
         NOT
        
          
        
         NULL
        
         ,
        OldVacationHours 
        
         int
        
         ,
        NewVacationHours 
        
         int
        
         ,
        VacationHoursDifference 
        
         int
        
         ,
        ModifiedDate 
        
         datetime
        
         );
    
        
         UPDATE
        
          
        
         TOP
        
          (
        
         10
        
         ) HumanResources.Employee
    
        
         SET
        
          VacationHours 
        
         =
        
          VacationHours 
        
         *
        
          
        
         1.25
        
          
    OUTPUT INSERTED.EmployeeID,
           DELETED.VacationHours,
           INSERTED.VacationHours,
           INSERTED.VacationHours 
        
         -
        
          DELETED.VacationHours,
           INSERTED.ModifiedDate
    
        
         INTO
        
          
        
         @MyTableVar
        
         ;
    
        
         --
        
         显示表变量中的数据
        
         
    
        
         SELECT
        
          EmpID, OldVacationHours, NewVacationHours, 
        VacationHoursDifference, ModifiedDate
    
        
         FROM
        
          
        
         @MyTableVar
        
         ;
    
        
         GO
        
         
    
        
         SELECT
        
          
        
         TOP
        
          (
        
         10
        
         ) EmployeeID, VacationHours, ModifiedDate
    
        
         FROM
        
          HumanResources.Employee;
    
        
         GO

![复制代码][copycode.gif]

**6、 在 UPDATE 语句中使用包含 from\_table\_name 的 OUTPUT INTO**

以下示例使用指定的 ProductID 和 ScrapReasonID，针对 WorkOrder 表中的所有工作顺序更新 ScrapReasonID 列。OUTPUT INTO 子句返回所更新表 (WorkOrder) 中的值以及 Product 表中的值。在 FROM 子句中使用 Product 表来指定要更新的行。由于 WorkOrder 表具有对其定义的 AFTER UPDATE 触发器，因此需要 INTO 关键字。

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

USE
        
          AdventureWorks;
    
        
         GO
        
         
    
        
         DECLARE
        
          
        
         @MyTestVar
        
          
        
         table
        
          (
        OldScrapReasonID 
        
         int
        
          
        
         NOT
        
          
        
         NULL
        
         , 
        NewScrapReasonID 
        
         int
        
          
        
         NOT
        
          
        
         NULL
        
         , 
        WorkOrderID 
        
         int
        
          
        
         NOT
        
          
        
         NULL
        
         ,
        ProductID 
        
         int
        
          
        
         NOT
        
          
        
         NULL
        
         ,
        ProductName 
        
         nvarchar
        
         (
        
         50
        
         )
        
         NOT
        
          
        
         NULL
        
         );
    
        
         UPDATE
        
          Production.WorkOrder
    
        
         SET
        
          ScrapReasonID 
        
         =
        
          
        
         4
        
         
    OUTPUT DELETED.ScrapReasonID,
           INSERTED.ScrapReasonID, 
           INSERTED.WorkOrderID,
           INSERTED.ProductID,
           p.Name
        
        
         INTO
        
          
        
         @MyTestVar
        
         
    
        
         FROM
        
          Production.WorkOrder 
        
         AS
        
          wo
        
        
         INNER
        
          
        
         JOIN
        
          Production.Product 
        
         AS
        
          p 
        
        
         ON
        
          wo.ProductID 
        
         =
        
          p.ProductID 
        
        
         AND
        
          wo.ScrapReasonID
        
         =
        
          
        
         16
        
         
        
        
         AND
        
          p.ProductID 
        
         =
        
          
        
         733
        
         ;
    
    
        
         SELECT
        
          OldScrapReasonID, NewScrapReasonID, WorkOrderID, 
        ProductID, ProductName 
    
        
         FROM
        
          
        
         @MyTestVar
        
         ;
    
        
         GO

![复制代码][copycode.gif]

**7、在 DELETE 语句中使用包含 from\_table\_name 的 OUTPUT INTO**

以下示例将按照在 DELETE 语句的 FROM 子句中所定义的搜索条件删除 ProductProductPhoto 表中的行。OUTPUT 子句返回所删除表（DELETED.ProductID、DELETED.ProductPhotoID）中的列以及 Product 表中的列。在 FROM 子句中使用该表来指定要删除的行。

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

USE
        
          AdventureWorks
    
        
         GO
        
         
    
        
         DECLARE
        
          
        
         @MyTableVar
        
          
        
         table
        
          (
        ProductID 
        
         int
        
          
        
         NOT
        
          
        
         NULL
        
         , 
        ProductName 
        
         nvarchar
        
         (
        
         50
        
         )
        
         NOT
        
          
        
         NULL
        
         ,
        ProductModelID 
        
         int
        
          
        
         NOT
        
          
        
         NULL
        
         , 
        PhotoID 
        
         int
        
          
        
         NOT
        
          
        
         NULL
        
         );
    
    
        
         DELETE
        
          Production.ProductProductPhoto
    OUTPUT DELETED.ProductID,
           p.Name,
           p.ProductModelID,
           DELETED.ProductPhotoID
        
        
         INTO
        
          
        
         @MyTableVar
        
         
    
        
         FROM
        
          Production.ProductProductPhoto 
        
         AS
        
          ph
    
        
         JOIN
        
          Production.Product 
        
         as
        
          p 
        
        
         ON
        
          ph.ProductID 
        
         =
        
          p.ProductID 
        
        
         WHERE
        
          p.ProductModelID 
        
         BETWEEN
        
          
        
         120
        
          
        
         and
        
          
        
         130
        
         ;
    
    
        
         SELECT
        
          ProductID, ProductName, ProductModelID, PhotoID 
    
        
         FROM
        
          
        
         @MyTableVar
        
         
    
        
         ORDER
        
          
        
         BY
        
          ProductModelID;
    
        
         GO

![复制代码][copycode.gif]

**8、 将 OUTPUT INTO 用于大型对象数据类型s**

以下示例使用 .WRITE 子句更新 Production.Document 表内 DocumentSummary 这一 nvarchar(max) 列中的部分值。通过指定替换单词、现有数据中要替换的单词的开始位置（偏移量）以及要替换的字符数（长度），将单词 components 替换为单词 features。此示例使用 OUTPUT 子句将 DocumentSummary 列的前像和后像返回到 @MyTableVar table 变量。请注意，将返回 DocumentSummary 列的全部前像和后像。

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

USE
        
          AdventureWorks;
    
        
         GO
        
         
    
        
         DECLARE
        
          
        
         @MyTableVar
        
          
        
         table
        
          (
        DocumentID 
        
         int
        
          
        
         NOT
        
          
        
         NULL
        
         ,
        SummaryBefore 
        
         nvarchar
        
         (
        
         max
        
         ),
        SummaryAfter 
        
         nvarchar
        
         (
        
         max
        
         ));
    
        
         UPDATE
        
          Production.Document
    
        
         SET
        
          DocumentSummary .WRITE (N
        
         '
        
         features
        
         '
        
         ,
        
         28
        
         ,
        
         10
        
         )
    OUTPUT INSERTED.DocumentID,
           DELETED.DocumentSummary, 
           INSERTED.DocumentSummary 
        
        
         INTO
        
          
        
         @MyTableVar
        
         
    
        
         WHERE
        
          DocumentID 
        
         =
        
          
        
         3
        
          ;
    
        
         SELECT
        
          DocumentID, SummaryBefore, SummaryAfter 
    
        
         FROM
        
          
        
         @MyTableVar
        
         ;
    
        
         GO

![复制代码][copycode.gif]

**9、 在 INSTEAD OF 触发器中使用 OUTPUT**

以下示例在触发器中使用 OUTPUT 子句返回触发器操作的结果。首先，创建一个 ScrapReason 表的视图，然后对该视图定义 INSTEAD OF INSERT 触发器，从而使用户只修改基表的 Name 列。由于列 ScrapReasonID 是基表中的 IDENTITY 列，因此触发器将忽略用户提供的值。这允许数据库引擎 自动生成正确的值。同样，用户为 ModifiedDate 提供的值也被忽略并设置为正确的日期。OUTPUT 子句返回实际插入 ScrapReason 表中的值。

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

UUSE AdventureWorks;
    
        
         GO
        
         
    
        
         IF
        
          
        
         OBJECT_ID
        
         (
        
         '
        
         dbo.vw_ScrapReason
        
         '
        
         ,
        
         '
        
         V
        
         '
        
         ) 
        
         IS
        
          
        
         NOT
        
          
        
         NULL
        
         
        
        
         DROP
        
          
        
         VIEW
        
          dbo.vw_ScrapReason;
    
        
         GO
        
         
    
        
         CREATE
        
          
        
         VIEW
        
          dbo.vw_ScrapReason
    
        
         AS
        
          (
        
         SELECT
        
          ScrapReasonID, Name, ModifiedDate
        
        
         FROM
        
          Production.ScrapReason);
    
        
         GO
        
         
    
        
         CREATE
        
          
        
         TRIGGER
        
          dbo.io_ScrapReason 
        
        
         ON
        
          dbo.vw_ScrapReason
    INSTEAD 
        
         OF
        
          
        
         INSERT
        
         
    
        
         AS
        
         
    
        
         BEGIN
        
         
    
        
         --
        
         ScrapReasonID is not specified in the list of columns to be inserted 
        
         
    --
        
         because it is an IDENTITY column.
        
         
    
        
             
        
         INSERT
        
          
        
         INTO
        
          Production.ScrapReason (Name, ModifiedDate)
            OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, 
                   INSERTED.ModifiedDate
        
        
         SELECT
        
          Name, 
        
         getdate
        
         ()
        
        
         FROM
        
          inserted;
    
        
         END
        
         
    
        
         GO
        
         
    
        
         INSERT
        
          vw_ScrapReason (ScrapReasonID, Name, ModifiedDate)
    
        
         VALUES
        
          (
        
         99
        
         , N
        
         '
        
         My scrap reason
        
         '
        
         ,
        
         '
        
         20030404
        
         '
        
         );
    
        
         GO

![复制代码][copycode.gif]

**10、 将 OUTPUT INTO 用于标识列和计算列**

下面的示例创建 EmployeeSales 表，然后使用 INSERT 语句向其中插入若干行，并使用 SELECT 语句从源表中检索数据。EmployeeSales 表包含标识列 (EmployeeID) 和计算列 (ProjectedSales)。由于这些值是在插入操作期间由数据库引擎生成的，因此，不能在 @MyTableVar 中定义上述两列。

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

USE
        
          AdventureWorks ;
    
        
         GO
        
         
    
        
         IF
        
          
        
         OBJECT_ID
        
          (
        
         '
        
         dbo.EmployeeSales
        
         '
        
         , 
        
         '
        
         U
        
         '
        
         ) 
        
         IS
        
          
        
         NOT
        
          
        
         NULL
        
         
        
        
         DROP
        
          
        
         TABLE
        
          dbo.EmployeeSales;
    
        
         GO
        
         
    
        
         CREATE
        
          
        
         TABLE
        
          dbo.EmployeeSales
    ( EmployeeID   
        
         int
        
          
        
         IDENTITY
        
          (
        
         1
        
         ,
        
         5
        
         )
        
         NOT
        
          
        
         NULL
        
         ,
      LastName     
        
         nvarchar
        
         (
        
         20
        
         ) 
        
         NOT
        
          
        
         NULL
        
         ,
      FirstName    
        
         nvarchar
        
         (
        
         20
        
         ) 
        
         NOT
        
          
        
         NULL
        
         ,
      CurrentSales 
        
         money
        
          
        
         NOT
        
          
        
         NULL
        
         ,
      ProjectedSales 
        
         AS
        
          CurrentSales 
        
         *
        
          
        
         1.10
        
          
    );
    
        
         GO
        
         
    
        
         DECLARE
        
          
        
         @MyTableVar
        
          
        
         table
        
         (
      LastName     
        
         nvarchar
        
         (
        
         20
        
         ) 
        
         NOT
        
          
        
         NULL
        
         ,
      FirstName    
        
         nvarchar
        
         (
        
         20
        
         ) 
        
         NOT
        
          
        
         NULL
        
         ,
      CurrentSales 
        
         money
        
          
        
         NOT
        
          
        
         NULL
        
         
      );
    
    
        
         INSERT
        
          
        
         INTO
        
          dbo.EmployeeSales (LastName, FirstName, CurrentSales)
      OUTPUT INSERTED.LastName, 
             INSERTED.FirstName, 
             INSERTED.CurrentSales
      
        
         INTO
        
          
        
         @MyTableVar
        
         
        
        
         SELECT
        
          c.LastName, c.FirstName, sp.SalesYTD
        
        
         FROM
        
          HumanResources.Employee 
        
         AS
        
          e
            
        
         INNER
        
          
        
         JOIN
        
          Sales.SalesPerson 
        
         AS
        
          sp
            
        
         ON
        
          e.EmployeeID 
        
         =
        
          sp.SalesPersonID 
            
        
         INNER
        
          
        
         JOIN
        
          Person.Contact 
        
         AS
        
          c
            
        
         ON
        
          e.ContactID 
        
         =
        
          c.ContactID
        
        
         WHERE
        
          e.EmployeeID 
        
         LIKE
        
          
        
         '
        
         2%
        
         '
        
         
        
        
         ORDER
        
          
        
         BY
        
          c.LastName, c.FirstName;
    
    
        
         SELECT
        
          LastName, FirstName, CurrentSales
    
        
         FROM
        
          
        
         @MyTableVar
        
         ;
    
        
         GO
        
         
    
        
         SELECT
        
          EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
    
        
         FROM
        
          dbo.EmployeeSales;
    
        
         GO

![复制代码][copycode.gif]

[ExpandedBlockStart.gif]: /images/20220128/6174fbbd2ffc484694207cf789056a76.png
[copycode.gif]: /images/20220128/7e1b3fc684464ec4bcf3e47d57b29d13.png