Lekcja 29. Użycie opcji OUTPUT

T-SQL wspiera klauzulę OUTPUT dla instrukcji modyfikujących dane. Klauzula ta zwraca informacje o zmodyfikowanych wierszach. Można użyć ich np do archiwizacji lub kontroli. Lekcja ta używa tabeli Sales.MyOrders i sekwencji Sales.SeqOrderID.

Użycie klauzuli OUTPUT

Konstrukcja klauzuli OUTPUT jest bardzo podobna do klauzuli SELECT w takim sensie, że można w niej określić wyrażenia i przypisać im aliasy. Jedyną różnicą pomiędzy OUTPUT i SELECT jest to, że kiedy odwołujemy się do zmodyfikowanych wierszy to musimy użyć prefiksów INSERTED lub DELETED przed nazwami kolumn. INSERTED reprezentuje wiersze dodane, DELETED oznacza wiersze usunięte. Dla instrukcji UPDATE, INSERTED reprezentuje nowe wiersze natomiast DELETED reprezentuje wiersze z przed aktualizacji. Klauzula OUTPUT podobnie jak SELECT może zwrócić wynik do sesji wywołującej. Można również użyć klauzuli INTO aby zapisać wynik do tabeli. Można mieć dwie klauzule OUTPUT jednocześnie, pierwszą z klauzulą INTO zapisującą wiersze do tabeli i drugą bez ITO, zwracającą wynik. Jeżeli używamy klauzuli INTO to tabela docelowa nie może uczestniczyć w dowolnej stronie relacji klucza obcego oraz nie może mieć zdefiniowanych wyzwalaczy. Uruchom poniższy kod aby przywrócić tabelę ales.MyOrders i sekwencję Sales.SeqOrderID.

IF OBJECT_ID('Sales.MyOrders') IS NOT NULL DROP TABLE Sales.MyOrders;
IF OBJECT_ID('Sales.SeqOrderIDs') IS NOT NULL DROP SEQUENCE Sales.SeqOrderIDs;
CREATE SEQUENCE Sales.SeqOrderIDs AS INT
MINVALUE 1
CYCLE;
CREATE TABLE Sales.MyOrders
(
orderid INT NOT NULL
CONSTRAINT PK_MyOrders_orderid PRIMARY KEY
CONSTRAINT DFT_MyOrders_orderid
DEFAULT(NEXT VALUE FOR Sales.SeqOrderIDs),
custid INT NOT NULL
CONSTRAINT CHK_MyOrders_custid CHECK(custid > 0),
empid INT NOT NULL
CONSTRAINT CHK_MyOrders_empid CHECK(empid > 0),
orderdate DATE NOT NULL
);

INSERT z OUTPUT

Klauzula OUTPUT może być użyta w instrukcji INSERT aby zwrócić informacje o wstawionych wierszach. Praktycznym przykładem może być sytuacja kiedy używamy instrukcji INSERT wstawiającej wiele wierszy i generującej nowe klucze poprzez IDENTITY lub SEQUENCE i dla których chcemy znać wartość wygenerowanych kluczy. Przykładowo załóżmy, że chcemy wstawić zamówienia dla Norwegii z tabeli Sales.Orders do tabeli sales.MyOrders. Nie chcemy użyć oryginalnych wartości ID w tabeli docelowej. Zamiast tego chcemy aby sekwencja wygenerowała te wartości, lecz chcemy otrzymać informacje o wygenerowanych kluczach. Aby to osiągnąć użyjemy klauzuli OUTPUT na końcu instrukcji INSERT:

INSERT INTO Sales.MyOrders (
custid
,empid
,orderdate
)
OUTPUT inserted.orderid
,inserted.custid
,inserted.empid
,inserted.orderdate
SELECT custid
,empid
,orderdate
FROM Sales.Orders
WHERE shipcountry = N'Norway';

Wynik:

orderid custid empid orderdate ----------- ----------- ----------- ---------- 1 70 1 2006-12-18 2 70 7 2007-04-29 3 70 7 2007-08-20 4 70 3 2008-01-14 5 70 1 2008-02-26 6 70 2 2008-04-10

Jak widać sekwencja wygenerowała identyfikatory ID od 1 do 6 dla nowych wierszy. Jeżeli chcemy przechować wynik operacji w tabeli trzeba dodać klauzulę INTO jak poniżej:

INSERT INTO Sales.MyOrders (
custid
,empid
,orderdate
)
OUTPUT inserted.orderid
,inserted.custid
,inserted.empid
,inserted.orderdate
INTO SomeTable(orderid, custid, empid, orderdate)
SELECT custid
,empid
,orderdate
FROM Sales.Orders
WHERE shipcountry = N'Norway';

Ponieważ tabela SomeTable nie istnieje to kod nie wykona się, jest to tylko przykład użycia.

DELETE z OUTPUT

Można użyć klauzuli OUTPUT do zwrócenia informacji o usuniętych przez instrukcję DELETE wierszach. Aby otrzymać wyniki trzeba nadać kolumnom prefiks DELETED. Poniższy przykład przedstawia usuwanie wierszy z identyfikatorem pracownika równym 1 z tabeli Sales.MyOrders. W klauzuli OUTPUT zwracamy identyfikatory usuniętych zamówień:

DELETE FROM Sales.MyOrders
OUTPUT deleted.orderid
WHERE empid = 1;

Wynik:

orderid ----------- 1 5

Aby zapisać wynik OUTPUT w tabeli trzeba użyć klauzuli INTO.

UPDATE z OUTPUT

Można użyć klauzuli OUTPUT do zwrócenia informacji o zmienionych przez instrukcję UPDATE wierszach. Dla zaktualizowanych wierszy mamy dostęp do starych i nowych wartości. Aby odnieść się do wartości kolumn z przed aktualizacji używamy prefiksu DELETED. Aby odnieść się do nowych wartości używamy prefiksu INSERTED. Jako przykład , poniższa instrukcja UPDATE dodaje 1 dzień do daty zamówienia dla zamówień obsługiwanych przez pracownika z identyfikatorem 7. W klauzuli OUTPUT pobieramy wartość starej daty i nową datę zamówienia:

UPDATE Sales.MyOrders
SET orderdate = DATEADD(day, 1, orderdate)
OUTPUT
inserted.orderid,
deleted.orderdate AS old_orderdate,
inserted.orderdate AS neworderdate
WHERE empid = 7;

Wynik:

orderid old_orderdate neworderdate ----------- ------------- ------------ 2 2007-04-29 2007-04-30 3 2007-08-20 2007-08-21

MERGE z OUTPUT

Można użyć klauzuli OUTPUT w instrukcji MERGE, lecz istnieją specjalne zalecenia do użycia klauzuli OUTPUT w tej instrukcji. ponieważ instrukcja MERGE może wykonywać różne akcje na tabeli docelowej to aby uzyskać wiersze OUTPUT musimy wiedzieć jaka akcja (INSERT, UPDATE lub DELETE) została wykonana. Do tego celu SQL Serwer posiada funkcję $ACTION. Zwraca ona literał opisujący wykonaną akcję('INSERT','UPDATE' lub 'DELETE'). Tak jak zostało to wytłumaczone wcześniej można odwołać się do kolumn z usuniętych wierszy używając prefiksu DELETED i analogicznie do dodanych wierszy z prefiksem INSERTED. Wiersze które są rezultatem akcji INSERT posiadają wartości w wierszach INSERTED oraz NULL w wierszach DELETED. Wiersze które są rezultatem akcji DELETE posiadają wartości w wierszach DELETED oraz NULL w wierszach INSERTED. Wiersze które są rezultatem akcji UPDATE posiadają wartości w wierszach INSERTED oraz DELETED. Więc jeżeli chcemy zwrócić klucz modyfikowanego wiersza to możemy użyć wyrażenia COALESCE(inserted.orderi, deleted.orderid). Poniższy kod demonstruje jak używać MERGE z klauzulą OUTPUT oraz funkcją $ACTION:

MERGE INTO Sales.MyOrders AS TGT
USING (VALUES(1, 70, 1, '20061218'),
(2, 70, 7, '20070429'),
(3, 70, 7, '20070820'),
(4, 70, 3, '20080114'),
(5, 70, 1, '20080226'),
(6, 70, 2, '20080410'))
AS SRC(orderid, custid, empid, orderdate )
ON SRC.orderid = TGT.orderid
WHEN MATCHED AND ( TGT.custid <> SRC.custid
OR TGT.empid <> SRC.empid
OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
SET TGT.custid = SRC.custid,
TGT.empid = SRC.empid,
TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN INSERT
VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT
$action AS the_action,
COALESCE(inserted.orderid, deleted.orderid) AS orderid;

Wynik:

the_action orderid ---------- ----------- INSERT 1 INSERT 5 UPDATE 2 UPDATE 3

W instrukcjach INSERT, UPDATE i DELETE w klauzuli OUTPUT można odnieść się tylko do tabeli docelowej natomiast w instrukcji MERGE można również odwołać się do kolumn z tabeli źródłowej. Wyczyśćmy tabelę Sales.MyOrders używając poniższego kodu:

TRUNCATE TABLE Sales.MyOrders;

Składany DML

Kiedy potrzebujemy przechwycić dane wyjściowe z instrukcji modyfikujących lecz interesuje nas tylko podzbiór wierszy wyjściowych to T-SQL ma na to rozwiązanie w postaci tzw composable DML. W T-SQL można zdefiniować coś podobnego do tabeli pochodnej bazującej na klauzuli OUTPUT. Wtedy można użyć wyrażenia zewnętrznego INSERT SELECT na tabeli docelowej, gdzie tabelą źródłową jest specjalna tabela pochodna. Zewnętrzny INSERT SELECT może posiadać klauzulę WHERE w której filtrujemy wiersze z klauzuli OUTPUT i wstawiamy tylko te które nas interesują. Zewnętrzny INSERT SELECT nie może mieć innych elementów występujących po WHERE tzn GROUP BY, HAVING itd. Jako przykład składanego DML rozważmy poniższy przykład instrukcji MERGE w której przechwycimy tylko wiersze które są efektem wstawiania:

DECLARE @InsertedOrders AS TABLE
(
orderid INT NOT NULL PRIMARY KEY,
custid INT NOT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL
);
INSERT INTO @InsertedOrders(orderid, custid, empid, orderdate)
SELECT orderid, custid, empid, orderdate
FROM (MERGE INTO Sales.MyOrders AS TGT
USING (VALUES(1, 70, 1, '20061218'),
(2, 70, 7, '20070429'),
(3, 70, 7, '20070820'),
(4, 70, 3, '20080114'),
(5, 70, 1, '20080226'),
(6, 70, 2, '20080410'))
AS SRC(orderid, custid, empid, orderdate )
ON SRC.orderid = TGT.orderid
WHEN MATCHED AND ( TGT.custid <> SRC.custid
OR TGT.empid <> SRC.empid
OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
SET TGT.custid = SRC.custid,
TGT.empid = SRC.empid,
TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN INSERT
VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT
$action AS the_action, inserted.*) AS D
WHERE the_action = 'INSERT';
SELECT *
FROM @InsertedOrders;

Wynik:

orderid custid empid orderdate ----------- ----------- ----------- ---------- 1 70 1 2006-12-18 2 70 7 2007-04-29 3 70 7 2007-08-20 4 70 3 2008-01-14 5 70 1 2008-02-26 6 70 2 2008-04-10

Tabela pochodna D jest zdefiniowana na instrukcji MERGE z klauzulą OUTPUT. Klauzula OUTPUT zwraca wynik akcji $ACTION. Kod używa INSERT SELECT aby wstawić wynik z tabeli D do tabeli docelowej, w tym wypadku zmiennej tabelarycznej @InsertedOrders. Klauzula WHERE filtruje zewnętrzne zapytanie do wierszy które mają akcję INSERT. jeżeli uruchomimy kod jeszcze raz to wynik będzie pusty ponieważ żaden wiersz nie zostanie dodany.

Ćwiczenia

I. Użycie OUTPUT z instrukcją UPDATE

Wyczyść obiekty używane w ćwiczeniach za pomocą poniższego kodu:

IF OBJECT_ID('Sales.MyOrders') IS NOT NULL DROP TABLE Sales.MyOrders;
IF OBJECT_ID('Sales.SeqOrderIDs') IS NOT NULL DROP SEQUENCE Sales.SeqOrderIDs;

Musimy zaktualizować produkty z kategorią o identyfikatorze 1 które są dostarczone przez dostawcę z identyfikatorem 16. Sprawdźmy dane które muszą zostać poprawione
```
SELECT productid, productname, unitprice
FROM Production.Products
WHERE categoryid = 1
AND supplierid = 16;
```
Wynik:
productid productname unitprice ----------- ---------------------------------------- --------------------- 34 Product SWNJY 14,00 35 Product NEVTJ 18,00 67 Product XLXQF 14,00
Napisz instrukcję UPDATE która zwiększa cenę o 2,5 dla produktów z kategorii 1 i dostawcy nr 16. Użyj klauzuli OUTPUT która przechwyci identyfikator produktu, jego nazwę, starą cenę, nową cenę i procentową różnicę między cenami:
```
UPDATE Production.Products
SET unitprice += 2.5
OUTPUT
inserted.productid,
inserted.productname,
deleted.unitprice AS oldprice,
inserted.unitprice AS newprice,
CAST(100.0 * (inserted.unitprice - deleted.unitprice)
/ deleted.unitprice AS NUMERIC(5, 2)) AS pct
WHERE categoryid = 1
AND supplierid = 16;
```
Wynik:
productid productname oldprice newprice pct ----------- ---------------------------------------- --------------------- --------------------- --------------------------------------- 34 Product SWNJY 14,00 16,50 17.86 35 Product NEVTJ 18,00 20,50 13.89 67 Product XLXQF 14,00 16,50 17.86

Napisz zapytanie które przywraca cenę z przed aktualizacji i pobierz dla niej te same dane:

UPDATE Production.Products
SET unitprice -= 2.5
OUTPUT
inserted.productid,
inserted.productname,
deleted.unitprice AS oldprice,
inserted.unitprice AS newprice,
CAST(100.0 * (inserted.unitprice - deleted.unitprice)
/ deleted.unitprice AS NUMERIC(5, 2)) AS pct
WHERE categoryid = 1
AND supplierid = 16;

II. Użycie składanego DML

Utwórz strukturę tabel uruchamiając poniższy kod:

IF OBJECT_ID('Sales.MyOrdersArchive') IS NOT NULL
DROP TABLE Sales.MyOrdersArchive;
IF OBJECT_ID('Sales.MyOrders') IS NOT NULL
DROP TABLE Sales.MyOrders;
CREATE TABLE Sales.MyOrders
(
orderid INT NOT NULL
CONSTRAINT PK_MyOrders PRIMARY KEY,
custid INT NOT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL
);
INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate)
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders;
CREATE TABLE Sales.MyOrdersArchive
(
orderid INT NOT NULL
CONSTRAINT PK_MyOrdersArchive PRIMARY KEY,
custid INT NOT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL
);

Napisz wyrażenie na tabeli Sales.MyOrders które usuwa zamówienia starsze niż rok 2007. Użyj składanego DML do przechwycenia zamówień dla klientów o identyfikatorach 17 i 19:
```
INSERT INTO Sales.MyOrdersArchive(orderid, custid, empid, orderdate)
SELECT orderid, custid, empid, orderdate
FROM (DELETE FROM Sales.MyOrders
OUTPUT deleted.*
WHERE orderdate < '20070101') AS D
WHERE custid IN (17, 19);
SELECT *
FROM Sales.MyOrdersArchive;
```
Wynik:
orderid custid empid orderdate ----------- ----------- ----------- ---------- 10363 17 4 2006-11-26 10364 19 1 2006-11-26 10391 17 3 2006-12-23

Wyczyść tabele używając poniższego skryptu:

IF OBJECT_ID('Sales.MyOrdersArchive') IS NOT NULL
DROP TABLE Sales. MyOrdersArchive;
IF OBJECT_ID('Sales.MyOrders') IS NOT NULL
DROP TABLE Sales.MyOrders;

Podsumowanie

Dzięki klauzuli OUTPUT możemy przechwycić informacje o modyfikowanych rekordach w instrukcjach modyfikujących.
Klauzula OUTPUT jest zaprojektowana podobnie do klauzuli SELECT pozwalając na użycie wyrażeń i aliasów.
Wynik klauzuli OUTPUT może być przesłany do sesji wywołującej zapytanie modyfikujące lub zapisany do tabeli za pomocą klauzuli INTO.
Do zmodyfikowanych kolumn odwołujemy się za pomocą prefiksów INSERTED i DELETE.
Instrukcja MERGE pozwala na użycie funkcji $ACTION która przechwytuje literał opisujący wykonaną akcję.
Aby filtrować wiersze wyjściowe trzeba użyć tzw composable DML.