BAZY DANYCH - pg.edu.pl · MS SQL Server pozwala na utworzenie tablic tymczasowych, które są...

BAZY DANYCH

Podstawowe wiadomości o Transact-SQL

Dodatek do instrukcji laboratoryjnej dla studiów niestacjonarnych I stopnia Opr. A. Rams

2

BAZY DANYCH ..................................................................................................................................................................... 1 1. Typy danych ........................................................................................................................................................... 3 2. Język definicji danych (DDL - Data Definition Language) ................................................................................... 4

2.1. Tworzenie tabeli..................................................................................................................................................... 4 2.2. Tablice tymczasowe ............................................................................................................................................... 6 2.3. Zmiana struktury tabeli ......................................................................................................................................... 8 2.4. Usuwanie tabeli .................................................................................................................................................... 10 2.5. Tworzenie widoku ............................................................................................................................................... 10 2.6. Tworzenie indeksu ............................................................................................................................................... 12

3. Język operowania danymi (DML - Data Manipulation Language) .................................................................... 14

3.1. Pobranie istniejących danych (SELECT)............................................................................................................. 14

3.1.1. Klauzula WITH. Wyrażenie tablicowe. ........................................................................................................ 14 3.1.2. Podzapytania (subquery) .............................................................................................................................. 15 3.1.3. Wyrażenie SELECT ..................................................................................................................................... 20 3.1.4. Wyrażenie CASE.......................................................................................................................................... 21 3.1.5. Klauzula INTO ............................................................................................................................................. 22 3.1.6. Funkcje agregujące ....................................................................................................................................... 22 3.1.7. Klauzula FROM ........................................................................................................................................... 23 3.1.8. Klauzula WHERE ........................................................................................................................................ 24 3.1.9. Klauzula GROUP BY ................................................................................................................................... 25 3.1.10. Klauzula ORDER BY ................................................................................................................................... 25 3.1.11. Klauzula COMPUTE .................................................................................................................................... 26 3.1.12. Operator JOIN .............................................................................................................................................. 26 3.1.13. Klauzule UNION, INTERSECT i EXCEPT ................................................................................................ 27

3.2. Dodawanie wierszy do tabeli (INSERT) ............................................................................................................. 28 3.3. Modyfikacja wierszy w tabeli (UPDATE)............................................................................................................ 29 3.4. Usuwanie wierszy z tabeli (DELETE) ................................................................................................................. 30

4. Elementy składni języka T-SQL ........................................................................................................................... 32

4.1. Powołanie zmiennej lokalnej (DECLARE) .......................................................................................................... 32 4.2. Tworzenie procedur składowych (CREATE PROCEDURE) ............................................................................... 32 4.3. Uruchomienie kodu (EXECUTE) ......................................................................................................................... 34 4.4. Sterowanie programem ....................................................................................................................................... 34

4.4.1. Deklaracja bloku (BEGIN...END) ................................................................................................................ 34 4.4.2. Instrukcja warunkowa (IF...ELSE) ............................................................................................................... 35 4.4.3. Instrukcja pętli (WHILE).............................................................................................................................. 35 4.4.4. Instrukcja oczekiwania (WAITFOR) ........................................................................................................... 35

4.5. Kursory ................................................................................................................................................................. 36

4.5.1. Definiowanie (DECLARE CURSOR) ......................................................................................................... 36 4.5.2. Pobieranie danych (FETCH) ........................................................................................................................ 37

4.6. Wyzwalacze (triggery) ......................................................................................................................................... 37

4.6.1. Wyzwalacze DML ........................................................................................................................................ 37 4.6.2. Wyzwalacze DDL ........................................................................................................................................ 38 4.6.3. Wyzwalacze logowania ................................................................................................................................ 39

4.7. Funkcje wbudowane ............................................................................................................................................ 39 4.8. Transakcje............................................................................................................................................................. 40

4.8.1. Poziomy izolacji transakcji w SQL-92 ......................................................................................................... 40 4.8.2. Polecenia związane z transakcjami ............................................................................................................... 41 4.8.3. Transakcje automatyczne ............................................................................................................................. 43 4.8.4. Transakcje sprecyzowane (explicite) ............................................................................................................ 44 4.8.5. Transakcje domyślne (implicite) .................................................................................................................. 45

3

1. Typy danych W wyrażeniach CREATE TABLE lub ALTER TABLE należy podać dla każdej kolumny postać danych (typ), w jakiej dane będą przechowywane. W wykorzystywanym przez MS SQL Server języku Transact SQL typy danych zgrupowane są w następujących kategoriach:

Dane numeryczne - dokładne (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint)

Dane numeryczne - przybliżone (float, real)

Data i czas (date, datetime, datetime2, datetimeoffset, smalldatetime, time)

Ciągi znaków (char, text, varchar)

Ciągi znaków Unicode (nchar, ntext, nvarchar)

Ciągi binarne (binary, image, varbinary)

Inne (cursor, hierarchyid, sql_variant, table, timestamp, uniqueidentyfier, xml)

Przykładowe formaty danych: Typ danych Zakres Ilość bajtów

BIGINT -263 (-9 223 372 036 854,775 808) do 263-1 (9 223 372 036 854 775 807)

8

INTEGER -231 (-2 147 483 648) do 231-1 (2 147 483 647) 4

SMALLINT -215 (-32 768) do 215-1 (32 767) 2

TINYINT 0 do 28-1 (255) 1

MONEY -922 337 203 685 477,5808 do 922 337 203 685 477,5807 8

SMALLMONEY - 214 748,3648 do 214 748,3647 4

DECIMAL, NUMERIC

dec[(m[, n])]. m - łączna ilość cyfr (1 ≤ m ≤ 38), n - cyfry po przecinku, 0 ≤ n ≤ m (n = 9, 19, 28, 38).

5, 9, 13, 17

FLOAT -1.79×10+308 do -2.23×10-308 , 0, 2.23×10-308 do 1.79×10+308 float[(n)], n - ilość bitów mantysy, 1 ≤ n ≤ 53 (n = 24 lub 53)

4 lub 8

REAL -3.40E+38 do -1.18E-38 oraz 1.18E-38 do 3.40E+38 4

CHARACTER char[(n)]. 1 ≤ n ≤ 8000 n

NVARCHAR nvarchar[(n | max)]. 1 ≤ n ≤ 4000, max → length ≤ 231-3 bajtów length + 2

W Transact-SQL użytkownik może definiować swoje własne typy na podstawie typów istniejących i usuwać je. Służą do tego polecenia CREATE TYPE i DROP TYPE.

Przykłady Utwórzmy 12-znakowy typ MAC, oparty na typie CHARACTER, nie dopuszczający wartości NULL: CREATE TYPE MAC

FROM CHAR(12) NOT NULL;

GO

CREATE TABLE Tab1 (

ID INT IDENTITY PRIMARY KEY,

mac1 MAC);

Utwórzmy typ tablicowy PRSN (typu tablicowego nie można go używać w definiowaniu kolumn!), wymuszający długość ciągu znaków w pierwszej kolumnie:

CREATE TYPE PRSN AS TABLE (

PESEL CHAR(11),

Name VARCHAR(15) NOT NULL,

CHECK (LEN(PESEL) = 11));

GO

DECLARE @next_t AS PRSN;

INSERT @next_t

SELECT 89032004091,'Adamski';

SELECT * FROM @next_t;

GO

4

2. Język definicji danych (DDL - Data Definition Language)

2.1. Tworzenie tabeli Do tworzenia nowej (zwykłej lub tymczasowej) tablicy służy polecenie CREATE TABLE: SKŁADNIA

CREATE TABLE

[database_name.[schema_name].|schema_name.]table_name

({<column_definition>|<computed_column_definition>

|<column_set_definition> }

[<table_constraint>] [,...n])

[ON {partition_scheme_name (partition_column_name)|filegroup

|"default" } ]

[{TEXTIMAGE_ON {filegroup|"default"}]

[FILESTREAM_ON {partition_scheme_name|filegroup|"default"} ]

[WITH (<table_option> [,...n ] )]

[ ; ]

Przy pomocy wyrażenia CREATE TABLE wprowadzamy nazwę tablicy oraz definiujemy jedną lub więcej kolumn. Nazwy kolumn (do 128 znaków) muszą być unikalne w ramach tablicy. Nazwa tablicy (maksymalnie 128 znaków) może być uzupełniona nazwą schematu do którego tablica należy oraz nazwą bazy danych. Kolumny mogą być zwykłe lub wyliczane. Kolumna wyliczana jest to kolumna wirtualna, nie przechowywana w tabeli (chyba że oznaczymy ją jako PERSISTED), której wartość obliczana jest na podstawie wyrażenia wykorzystującego inne kolumny z danej tabeli. Definicja każdej kolumny musi zawierać jej nazwę oraz typ danych. Można również wprowadzić opis pomocniczy kolumny: SKŁADNIA

<column_definition> ::= column_name <data_type>

[FILESTREAM ]

[COLLATE collation_name ]

[NULL | NOT NULL ]

[

[CONSTRAINT constraint_name ] DEFAULT constant_expression]

| [IDENTITY [ (seed ,increment) ] [ NOT FOR REPLICATION]

]

[ROWGUIDCOL ] [ <column_constraint> [,...n ]]

[SPARSE ]

<computed_column_definition> ::= column_name AS computed_column_expression

[ PERSISTED [ NOT NULL ] ]

[

[ CONSTRAINT constraint_name ]

{ PRIMARY KEY | UNIQUE }

[ CLUSTERED | NONCLUSTERED ]

[

WITH FILLFACTOR = fillfactor

| WITH (<index_option> [,...n ])

]

| [ FOREIGN KEY ]

REFERENCES referenced_table_name [(ref_column) ]

[ ON DELETE { NO ACTION | CASCADE } ]

[ ON UPDATE { NO ACTION } ]

[ NOT FOR REPLICATION ]

| CHECK [ NOT FOR REPLICATION ] (logical_expression)

[ ON {partition_scheme_name (partition_column_name)

|filegroup|"default"} ]

]

Aby wymusić wprowadzanie danych do kolumny używamy zwrotu NOT NULL. Wartość domyślną dla kolumny można wprowadzić za pomocą zwrotu DEFAULT constant_expression. Kolumna zawierająca dane numeryczne dokładne może mieć wprowadzane automatycznie unikalne, narastające wartości, jeżeli użyte zostanie: IDENTITY [(seed, increment)]. Ewentualna klauzula NOT FOR REPLICATION powoduje, że przy powielaniu tabeli reguły inkrementacji nie zostaną użyte.

5

Każda z kolumn może również mieć zdefiniowane ograniczenia: SKŁADNIA

<column_constraint> ::= [ CONSTRAINT constraint_name ]

{


[CLUSTERED | NONCLUSTERED]

[

WITH FILLFACTOR = fillfactor

| WITH ( < index_option > [ , ...n ] )

]

[ON { partition_scheme_name (partition_column_name)

| filegroup | "default" } ]

| [FOREIGN KEY ]

REFERENCES [schema_name . ] referenced_table_name [(ref_column )]

[ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }]

[ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }]

[NOT FOR REPLICATION]

| CHECK [NOT FOR REPLICATION] (logical_expression )

}

Kolumna może być oznaczona jako klucz główny (PRIMARY KEY) lub o niepowtarzających się wartościach (UNIQUE). Może być kluczem obcym ([FOREIGN KEY] REFERENCES), którego wartości muszą występować we wskazanej unikalnej kolumnie innej tabeli. W tym przypadku można zdefiniować działania jakie powinny być wykonane w przypadku modyfikacji (ON UPDATE) lub usunięcia (ON DELETE) wartości klucza w tabeli odniesienia. Dla kolumn wyliczanych istnieje dodatkowo możliwość oznakowania (PERSISTED) jako przechowywanej w tabeli. Wartość takiej kolumny obliczana jest każdorazowo przy wystąpieniu zmian składników wyrażenia. Ponadto można zdefiniować ograniczenia dla całej tabeli. SKŁADNIA

< table_constraint > ::= [CONSTRAINT constraint_name]

{


[CLUSTERED | NONCLUSTERED]

(column [ASC | DESC] [,...n ] )

[

WITH FILLFACTOR = fillfactor |WITH ( <index_option> [, ...n] )

]

[ ON { partition_scheme_name (partition_column_name)

| filegroup | "default" } ]

| FOREIGN KEY

( column [ ,...n ] )

REFERENCES referenced_table_name [ (ref_column [ ,...n ] )]

[ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }]

[ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }]

[NOT FOR REPLICATION]

| CHECK [NOT FOR REPLICATION] (logical_expression )

}

Pozwalają one na określanie niepowtarzających się wartości w zestawach kolumn (UNIQUE), definiowanie złożonych kluczy głównych (PRIMARY KEY) oraz złożonych kluczy obcych (FOREIGN KEY). Jako klucz obcy może być również użyta kolumna z tej samej tabeli (powstaje tzw. samo-odniesienie).

Przykład Utwórzmy tabelę Teachers, posiadającą kolumny TNo, TName, Title, City oraz SupNo: CREATE TABLE Teachers (

TNo CHAR(3) NOT NULL PRIMARY KEY,

TName VARCHAR(30),

Title VARCHAR (10),

City VARCHAR (30),

SupNo CHAR (3) FOREIGN KEY REFERENCES Teachers(TNo));

GO

6

2.2. Tablice tymczasowe MS SQL Server pozwala na utworzenie tablic tymczasowych, które są automatycznie usuwane po zakończeniu ich używania bądź po zakończeniu sesji. Nazwa takiej tablicy może zawierać do 116 znaków i powinna rozpoczynać się od znaku #. Taka tablica tymczasowa widoczna jest wyłącznie w ramach bieżącej sesji. Istnieje również odmiana globalna tablic tymczasowych, które są widoczne we wszystkich sesjach. Ich nazwa powinna rozpoczynać się od znaków ##. Polecenie tworzenia tablicy tymczasowej obsługuje definicje wszystkich ograniczeń za wyjątkiem FOREIGN KEY. Gdyby polecenie CREATE TABLE takie ograniczenie zawierało, to zostanie wygenerowane ostrzeżenie i definicja FOREIGN KEY zostanie pominięta. Tablica będzie utworzona bez tego ograniczenia. Tablice tymczasowe nie mogą również występować w klauzuli REFERENCES innych tablic. Jeśli w danej procedurze tworzone jest kilka tablic tymczasowych, to muszą się one różnić nazwami. Tablice tymczasowe mogą być generowane również wewnątrz procedur - w takim przypadku są one automatycznie usuwane po zakończeniu działania procedury. Zasięg widoczności takiej tablicy tymczasowej obejmuje wyłącznie daną procedurę i ewentualne jej podprocedury.

Przykłady

Tworzenie tablicy tymczasowej w procedurze CREATE PROCEDURE Test

AS

CREATE TABLE #t (x INT PRIMARY KEY);

INSERT INTO #t VALUES (9);

SELECT TestCol = X FROM #t;

GO

EXEC Test

GO

Wynik: (1 row(s) affected) TestCol

1 9

Obszar działania tablicy tymczasowej CREATE PROCEDURE Test2

AS



SELECT TestCol2 = X FROM #t;

GO

CREATE PROCEDURE Test1

AS



SELECT TestCol1 = X FROM #t;

EXEC Test2

GO



GO

EXEC Test1

SELECT TestCol = X FROM #t;

GO

Wynik: (1 row(s) affected) TestCol1

1 1

7

(1 row(s) affected) TestCol2

1 2

(1 row(s) affected) TestCol

1 9

Lokalna tablica tymczasowa utworzona wewnątrz procedury składowej lub wyzwalacza może mieć nazwę identyczną z tablicą tymczasową utworzoną przed wywołaniem procedury pod warunkiem, że struktura tabeli i nazwy kolumn będą również identyczne. To samo dotyczy tworzenia tablic tymczasowych w procedurach zagnieżdżonych. W wywołaniu funkcji wbudowanych, przy odwoływaniu się do tablicy tymczasowej, należy jej nazwę poprzedzić nazwą bazy danych.

Przykłady W drugim przypadku nie użyto rozszerzenia nazwy, co skutkowało negatywnym wynikiem poszukiwania obiektu. CREATE TABLE #t (x INT PRIMARY KEY);


GO

SELECT Id = OBJECT_ID('tempdb..#t','U');

GO

SELECT Id = OBJECT_ID('#t','U');

GO

Wynik: (1 row(s) affected)

Id

1 805577908

(1 row(s) affected) Id

1 NULL

Innym sposobem tworzenia lokalnych tablic tymczasowych jest użycie zmiennej typu table ( zob. przykład).

Zmienne lokalne posiadają nazwy rozpoczynające się od znaku @. Podstawową różnicą jest to, że zmienne lokalne nie są zapisywane w logach transakcyjnych, zatem nie reagują na polecenia rollback.

Przykład

Porównanie tablicy tymczasowej i zmiennej typu table CREATE TABLE #T (s VARCHAR(128))

DECLARE @T TABLE (s VARCHAR(128))

INSERT INTO #T SELECT 'old value #'

INSERT INTO @T SELECT 'old value @'

BEGIN TRANSACTION

UPDATE #T SET s='new value #'

UPDATE @T SET s='new value @'

ROLLBACK TRANSACTION

SELECT * FROM #T

SELECT * FROM @T

8

Wynik: (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) s

1 old value #

(1 row(s) affected) s

1 new value @

2.3. Zmiana struktury tabeli Polecenie ALTER TABLE umożliwia dodanie nowej kolumny, modyfikację bądź usunięcie kolumny istniejącej. SKŁADNIA

ALTER TABLE [database_name . [schema_name ] . | schema_name . ] table_name

{

ALTER COLUMN column_name

{

[type_schema_name. ] type_name [( { precision [,scale]

| MAX | xml_schema_collection } ) ]

[COLLATE collation_name]

[NULL | NOT NULL ] [SPARSE ]

| {ADD | DROP }

{ ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE }

}

| [WITH { CHECK | NOCHECK }]

| ADD

{

<column_definition>

| <computed_column_definition>

| <table_constraint>

| <column_set_definition>

} [,...n]

| DROP

{

[CONSTRAINT] constraint_name

[WITH ( <drop_clustered_constraint_option> [,...n] )]

| COLUMN column_name

} [,...n ]

| [WITH { CHECK | NOCHECK }] { CHECK | NOCHECK } CONSTRAINT

{ ALL | constraint_name [,...n] }

| { ENABLE | DISABLE } TRIGGER

{ ALL | trigger_name [,...n] }

| { ENABLE | DISABLE } CHANGE_TRACKING

[WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )]

| SWITCH [PARTITION source_partition_number_expression]

TO target_table

[PARTITION target_partition_number_expression]

| SET ( FILESTREAM_ON = { partition_scheme_name | filegroup |

"default" | "NULL" } )

| REBUILD

[ [PARTITION = ALL]

[WITH ( <rebuild_option> [,...n] )]

| [PARTITION = partition_number

[WITH ( <single_partition_rebuild_option> [,...n] )]

]

]

| (<table_option>)

}

[ ; ]

9

Polecenie ALTER COLUMN nie ma zastosowania do kolumn zawierających typ danych timestamp, kolumn wyliczanych, kolumn użytych w indeksach, chyba że nie zmienia się typu, nowy rozmiar jest nie mniejszy od starego a indeks nie jest kluczem głównym.

Przykłady

Dodawanie nowej kolumny CREATE TABLE dbo.TEST (column_a INT) ;

GO

ALTER TABLE dbo.TEST ADD column_b VARCHAR(20) NULL ;

GO

EXEC sp_help TEST; -- Dokumentacja tabeli TEST

GO

DROP TABLE dbo.TEST;

GO

Usuwanie kolumny CREATE TABLE dbo.TEST (column_a INT, column_b VARCHAR(20) NULL) ;

GO

ALTER TABLE dbo.TEST DROP COLUMN column_b ;

GO


GO

Zmiana typu danych w kolumnie CREATE TABLE dbo.TEST (column_a INT ) ;

GO

INSERT INTO dbo.TEST (column_a) VALUES (10) ;

GO

ALTER TABLE dbo.TEST ALTER COLUMN column_a DECIMAL (5, 2) ;

GO


Dodawanie kolumny z ograniczeniami CREATE TABLE dbo.TEST (column_a INT) ;

GO

ALTER TABLE dbo.TEST ADD column_b VARCHAR(20) NULL

CONSTRAINT TEST_unique UNIQUE ;

GO

EXEC sp_help TEST;

GO


GO

Dodanie ograniczenia CHECK dla istniejącej kolumny Użyto klauzuli WITH NO CHECK, aby nie sprawdzać już istniejących danych. CREATE TABLE dbo.TEST ( column_a INT) ;

GO

INSERT INTO dbo.TEST VALUES (-1) ;

GO

ALTER TABLE dbo.TEST WITH NOCHECK

ADD CONSTRAINT TEST_check CHECK (column_a > 1) ;

GO

EXEC sp_help TEST ;

GO


GO

10

Dodanie ograniczenia DEFAULT dla istniejącej kolumny W przykładzie zostają wprowadzone dane do pierwszej kolumny, pozostałe będą zawierały NULL. Po wprowadzeniu ograniczenia DEFAULT dla drugiej kolumny wprowadzamy nowy wiersz (tylko wartość w pierwszej kolumnie) i sprawdzamy wynik poleceniem SELECT. CREATE TABLE dbo.TEST ( column_a INT, column_b INT) ;

GO

INSERT INTO dbo.TEST (column_a)VALUES ( 7 ) ;

GO

ALTER TABLE dbo.TEST

ADD CONSTRAINT col_b_def

DEFAULT 50 FOR column_b ;

GO

INSERT INTO dbo.TEST (column_a) VALUES ( 10 ) ;

GO

SELECT * FROM dbo.TEST;

GO


GO

2.4. Usuwanie tabeli Polecenie DROP TABLE usuwa tabelę i wszystkie zawarte w niej dane z bazy danych, wszystkie zdefiniowane na niej indeksy oraz wszystkie związane z nią uprawnienia dostępu. SKŁADNIA

DROP TABLE [database_name . [ schema_name ] . | schema_name . ]

table_name [,...n] [ ; ]

Nie można usunąć tabeli wskazanej jako odniesienie w kluczu obcym w innej tabeli. Najpierw należy usunąć tamtą tabelę lub ograniczenie FOREIGN KEY. Usunięcie wszystkich danych z tabeli nie powoduje usunięcia tabeli. Wymaga posiadania uprawnienia CONTROL na tabeli lub ALTER na schemacie do którego tabela należy.

Przykład W poniższym przykładzie zostaje utworzona tymczasowa tablica (tablice tymczasowe umieszczane są w bazie tempdb), testowane jest jej istnienie a następnie tablica zostaje usunięta. Do testowania użyto funkcji OBJECT_ID, której argumentami są nazwa obiektu i jego typ (U – tablica użytkownika, V – widok). Funkcja zwraca NULL, gdy nie znajdzie obiektu. CREATE TABLE #temptable (col1 INT);

GO

INSERT INTO #temptable

VALUES (10);

GO

SELECT * FROM #temptable;

GO

IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL

DROP TABLE #temptable;

GO

--Test the drop.

SELECT * FROM #temptable;

2.5. Tworzenie widoku Polecenie CREATE VIEW tworzy wirtualną tablicę, której zawartość jest definiowana przez zapytanie. Może być używane dla:

wprowadzenia osobistych form przedstawienia danych dla poszczególnych użytkowników zabezpieczenia przed bezpośrednim dostępem użytkowników do tablic bazy danych zapewnienia wstecznej kompatybilności bazy przez emulację tablic, których struktura uległa zmianie

SKŁADNIA

CREATE VIEW [schema_name . ] view_name [(column [,...n] )]

[WITH <view_attribute> [,...n]]

AS SELECT_statement

[WITH CHECK OPTION] [ ; ]

11

<view_attribute> ::=

{

[ENCRYPTION]

[SCHEMABINDING]

[VIEW_METADATA] }

Nazwa widoku musi być unikalna w zbiorze nazw istniejących tablic i widoków. Wyrażenie SELECT definiuje widok. Może ono wykorzystywać więcej niż jedną tablicę oraz inne widoki. Może zawierać wiele poleceń SELECT rozdzielonych przez UNION lub UNION ALL. Nie może zawierać następujących elementów:

klauzul COMPUTE lub COMPUTE BY, klauzuli ORDER BY, chyba że w liście wyboru polecenia SELECT występuje również klauzula TOP, słowa kluczowego INTO klauzuli OPTION odniesienia do tabeli tymczasowej lub zmiennej typu tablicowego.

Wyrażenie WITH CHECK OPTION wymusza aktualizację wyświetlanych danych przez widok. Zmiana danych bezpośrednio w tablicach użytych w widoku nie jest weryfikowana wyrażeniem SELECT widoku. Dane w tablicach użytych w widoku mogą być modyfikowane poprzez widok pod warunkiem że:

wszelkie modyfikacje: UPDATE, INSERT i DELETE muszą się odnosić do kolumn jednej tablicy modyfikowane kolumny odnoszą się bezpośrednio do kolumn tablic. Nie mogą one być wynikiem operacji

takich jak: o funkcje agregacji AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR i VARP, o wyrażenia używające innych kolumn o operacje zbiorowe jak UNION, UNION ALL, EXCEPT i INTERSECT

modyfikowane kolumny nie są objęte działaniem GROUP BY, HAVING lub DISTINCT klauzula TOP nie jest użyta gdziekolwiek w wyrażeniu SELECT widoku wraz z klauzulą WITH CHECK

OPTION. Usuwanie widoku wykonywane jest za pomocą polecenia DROP VIEW.

Przykłady

Proste użycie CREATE VIEW W przykładzie wykorzystano dane z przykładowej bazy danych AdventureWorks2008R2 dołączonej do MS SQL Server 2008. Dane pochodzą z tablic HumanResources.Employee oraz Person.Person. Widok daje dostęp do wybranych danych pracowników. IF OBJECT_ID ('hiredate_view', 'V') IS NOT NULL

DROP VIEW hiredate_view ;

GO

CREATE VIEW hiredate_view AS

SELECT p.FirstName, p.LastName, e.BusinessEntityID, e.HireDate

FROM HumanResources.Employee e

JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ;

GO

Użycie klauzuli WITH CHECK OPTION Próba wprowadzenia w polu City wartości innej niż Seattle spowoduje błąd. IF OBJECT_ID ('dbo.SeattleOnly', 'V') IS NOT NULL

DROP VIEW dbo.SeattleOnly ;

GO

CREATE VIEW dbo.SeattleOnly AS

SELECT p.LastName, p.FirstName, e.JobTitle, a.City, sp.StateProvinceCode

FROM HumanResources.Employee e

INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID

INNER JOIN Person.BusinessEntityAddress bea

ON bea.BusinessEntityID = e.BusinessEntityID

INNER JOIN Person.Address a ON a.AddressID = bea.AddressID

INNER JOIN Person.StateProvince sp ON sp.StateProvinceID = a.StateProvinceID

WHERE a.City = 'Seattle'

WITH CHECK OPTION ;

GO

12

Użycie funkcji wbudowanych IF OBJECT_ID ('Sales.SalesPersonPerform', 'V') IS NOT NULL

DROP VIEW Sales.SalesPersonPerform ;

GO

CREATE VIEW Sales.SalesPersonPerform

AS

SELECT TOP (100) SalesPersonID, SUM(TotalDue) AS TotalSales

FROM Sales.SalesOrderHeader

WHERE OrderDate > CONVERT(DATETIME,'20001231',101)

GROUP BY SalesPersonID;

GO

2.6. Tworzenie indeksu Do utworzenia nowego indeksu na jednej lub kilku kolumnach tabeli służy polecenie CREATE INDEX. SKŁADNIA

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name

ON <object> (column [ASC | DESC] [,...n] )

[INCLUDE (column_name [,...n] ) ]

[WHERE <filter_predicate> ]

[WITH ( <relational_index_option> [,...n] ) ]

[ON { partition_scheme_name (column_name)

| filegroup_name

| default

}

]

[FILESTREAM_ON

{ filestream_filegroup_name | partition_scheme_name | "NULL" }]

[ ; ]

<object> ::=

{

[database_name. [schema_name] . | schema_name.] table_or_view_name

}

Nazwa indeksu jest unikalna dla danej tabeli lub widoku. Indeks niepowtarzalny (UNIQUE) może być utworzony jedynie w przypadku, gdy dane w tabeli na to pozwolą, w przypadku przeciwnym sygnalizowany będzie błąd. Indeks posiadający cechę CLUSTERED narzuca fizyczną kolejność rekordów w tablicy. Tablica (lub widok) może mieć tylko jeden indeks z taką cechą - wszelkie inne indeksy nie są związane z fizyczną kolejnością rekordów. Właściwości utworzonego indeksu ustala się za pomocą opcji. SKŁADNIA

<relational_index_option> ::=

{

PAD_INDEX = { ON | OFF }

| FILLFACTOR =fillfactor

| SORT_IN_TEMPDB = { ON | OFF }

| IGNORE_DUP_KEY = { ON | OFF }

| STATISTICS_NORECOMPUTE = { ON | OFF }

| DROP_EXISTING = { ON | OFF }

| ONLINE = { ON | OFF }

| ALLOW_ROW_LOCKS = { ON | OFF }

| ALLOW_PAGE_LOCKS = { ON | OFF }

| MAXDOP = MAX_degree_of_parallelism

| DATA_COMPRESSION = { NONE | ROW | PAGE}

[ON PARTITIONS ( {<partition_number_expression> | <range>} [, ...n])]

}

Do usunięcia indeksu służy polecenie DROP INDEX.

13

Przykłady

Utworzenie prostego indeksu Poniższe polecenia spowodują utworzenie indeksu na kolumnie BisinessEntityID w tablicy Purchasing.ProductVendor. IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'IX_ProductVendor_VendorID')

DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;

GO

CREATE INDEX IX_ProductVendor_VendorID

ON Purchasing.ProductVendor (BusinessEntityID);

GO

Utworzenie indeksu złożonego Tworzymy indeks na kolumnach SalesQuota i SalesYDT w tablicy Sales.SalesPerson. IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')

DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;

GO

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD

ON Sales.SalesPerson (SalesQuota, SalesYTD);

GO

Utworzenie indeksu unikalnego Ten indeks wymusi niepowtarzalność danych w kolumnie Name w tablicy Production.UnitMeasure. IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'AK_UnitMeasure_Name')

DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;

GO

CREATE UNIQUE INDEX AK_UnitMeasure_Name

ON Production.UnitMeasure(Name);

GO

Użycie klauzuli DROP_EXISTING do usunięcia i przebudowy indeksu CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID

ON Production.WorkOrder(ProductID)

WITH (DROP_EXISTING = ON);

GO

14

3. Język operowania danymi (DML - Data Manipulation Language)

3.1. Pobranie istniejących danych (SELECT) Do wyboru odpowiedniego zestawu danych z tabeli lub widoku służy polecenie SELECT. Pełna postać tego polecenia jest skomplikowana, w ogólnym zarysie ma postać:

[ WITH <common_table_expression>]

SELECT SELECT_list [ INTO new_table ]

[ FROM table_source ] [ WHERE search_condition ]

[ GROUP BY group_by_expression ]

[ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

Wybór danych odbywa się za pomocą zapytania. Do łączenia wyników zapytań w jeden zbiór mogą być użyte operatory UNION, EXCEPT oraz INTERSECT. Specyfikacja pojedynczego zapytania może zawierać klauzule INTO, FROM, WHERE, GROUP BY oraz HAVING. SKŁADNIA

<SELECT statement> ::=

[WITH <common_table_expression> [,...n]]

<query_expression>

[ORDER BY {order_by_expression|column_position [ASC|DESC]} [,...n] ]

[ COMPUTE

{{AVG|COUNT|MAX|MIN|SUM} (expression)} [ ,...n ] [BY expression [,...n]]

]

[ <FOR Clause>]

[ OPTION ( <query_hint> [ ,...n ] ) ]

<query_expression> ::=

{<query_specification> | (<query_expression>)}

[{UNION [ALL]|EXCEPT|INTERSECT}

<query_specification>|(<query_expression>)[,...n]

]

<query_specification> ::=

SELECT [ALL|DISTINCT] [TOP (expression) [PERCENT] [WITH TIES]]

< SELECT_list >

[INTO new_table ]

[FROM {<table_source>} [,...n]]

[WHERE <search_condition>]

[<GROUP BY>]

[HAVING <search_condition>]

3.1.1. Klauzula WITH. Wyrażenie tablicowe. Wprowadza nazwę i formę tymczasowego zestawu danych (CTE - common table expression), definiowanych w prostym zapytaniu SELECT, INSERT, UPDATE, MERGE lub DELETE. SKŁADNIA

[WITH <common_table_expression> [,...n]]

<common_table_expression>::=

expression_name [(column_name [,...n])]

AS

(<CTE_query_definition>)

Po CTE może wystąpić proste wyrażenie SELECT, INSERT, UPDATE, MERGE lub DELETE, z którego pochodzą niektóre (lub wszystkie) kolumny CTE. CTE może być również użyte w definicji widoku, jako część wyrażenia SELECT.

Mnogie definicje CTE mogą być budowane (nierekursywnie) za pomocą operatorów UNION ALL, UNION, INTERSECT lub EXCEPT.

CTE może używać odniesień do innych CTE zdefiniowanych uprzednio w tej samej klauzuli WITH. Dopuszczalna jest tylko pojedyncza klauzula WITH (nie jest dopuszczalne ich zagnieżdżanie) Wewnątrz CTE_query_definition nie są dopuszczalne klauzule:

15

o COMPUTE lub COMPUTE BY o ORDER BY (chyba że wraz z klauzulą TOP) o INTO o OPTION o FOR XML o FOR BROWSE

Jeśli wyrażenie CTE jest użyte w skrypcie, poprzednie polecenie musi być zakończone średnikiem. Zapytania używające CTE mogą wystąpić w definicjach kursorów. Jeśli w klauzuli FROM zostanie nadany alias CTE, ten alias musi być używany we wszystkich innych

odniesieniach do CTE.

Przykład

Utworzenie prostego CTE Poniższe polecenia spowodują wyświetlenie zestawienia zamówień dla każdego sprzedawcy: -- Definicja nazwy CTE i listy kolumn.

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS

-- Definicja zapytania dla CTE.

(

SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear


WHERE SalesPersonID IS NOT NULL

)

-- Zewnętrzne zapytanie działające na zdefiniowanym CTE.

SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear

FROM Sales_CTE

GROUP BY SalesYear, SalesPersonID

ORDER BY SalesPersonID, SalesYear;

GO

3.1.2. Podzapytania (subquery) Podzapytanie jest to zapytanie zagnieżdżone wewnątrz polecenia SELECT, INSERT, UPDATE lub DELETE, albo wewnątrz innego podzapytania. Podzapytanie może wystąpić wszędzie, gdzie dozwolone jest wystąpienie wyrażenia, jeśli zwraca pojedynczą wartość. W przykładzie poniżej mamy podzapytanie w postaci wyrażenia kolumnowego, któremu nadano nazwę MaxUnitPrice wewnątrz polecenia SELECT. Polecenie SELECT zwraca wartości SalesOrderID i OrderDate zatablicy SalesOrderHeader oraz uzyskaną przez podzapytanie wartość MAX(UnitPrice) z tablicy SalesOrderDetail, dla tych rekordów, dla których wartość pola SalesOrderID w tej tablicy zgodna jest z wartością pola SalesOrderID z tablicy SalesOrderHeader: SELECT Ord.SalesOrderID, Ord.OrderDate, (

SELECT MAX(OrdDet.UnitPrice)

FROM Sales.SalesOrderDetail AS OrdDet

WHERE Ord.SalesOrderID = OrdDet.SalesOrderID

) AS MAXUnitPrice

FROM.Sales.SalesOrderHeader AS Ord

Podzapytanie jest też nazywane zapytaniem wewnętrznym, podczas gdy polecenie zawierające je nazywane jest zapytaniem zewnętrznym. Podzapytanie zwracające pojedynczą wartość może być użyte w miejscu, gdzie dozwolone jest wyrażenie: SELECT Name, ListPrice,

(SELECT AVG(ListPrice) FROM Production.Product) AS Average,

ListPrice - (SELECT AVG(ListPrice) FROM Production.Product)

AS Difference

FROM Production.Product

WHERE ProductSubcategoryID = 1;

Wiele wyrażeń Transact-SQL zawierających podzapytania może być alternatywnie sformułowane jako złączenia. Niektóre mogą być realizowane wyłącznie za pomocą podzapytań. Czasami, gdy musi być sprawdzane istnienie, złączenie daje lepszą jakość. W przeciwnym przypadku zagnieżdżone podzapytanie musi być przetwarzane dla każdego wyniku zapytania zewnętrznego aby wyeliminować duplikaty. Poniższy przykład pokazuje dwie wersje polecenia SELECT: -- SELECT statement built using a subquery.

SELECT Name

16

FROM AdventureWorks2008R2.Production.Product

WHERE ListPrice =

(SELECT ListPrice


WHERE Name = 'Chainring Bolts' );

-- SELECT statement built using a join that returns the same result set.

SELECT Prd1.Name

FROM AdventureWorks2008R2.Production.Product AS Prd1

JOIN AdventureWorks2008R2.Production.Product AS Prd2

ON (Prd1.ListPrice = Prd2.ListPrice)

WHERE Prd2. Name = 'Chainring Bolts';

Podzapytania mogą być zagnieżdżane. Maksymalny poziom zagnieżdżenia równy jest 32. -- Nested subqueries.

SELECT LastName, FirstName

FROM Person.Person

WHERE BusinessEntityID IN

(SELECT BusinessEntityID

FROM HumanResources.Employee



FROM Sales.SalesPerson))

Podzapytanie zagnieżdżone w zewnętrznym poleceniu SELECT zawiera następujące elementy: Wyrażenie SELECT zawierające listę kolumn. Klauzulę FROM określającą jedną lub więcej tabel lub widoków. Opcjonalnie klauzulę WHERE. Opcjonalnie klauzulę GROUP BY. Opcjonalnie klauzulę HAVING.

Podzapytanie jest zawsze zawarte w nawiasach. Nie może ono zawierać klauzul COMPUTE ani FOR BROWSE, ale może zawierać klauzulę ORDER BY, jeżeli występuje również klauzula TOP. Jeżeli jakaś tablica występuje wyłącznie w podzapytaniu, to jej kolumny nie mogą pojawić się w wyniku zapytania zewnętrznego. Typowe wyrażenia zawierające podzapytania:

WHERE <wyrażenie> [NOT] IN (<podzapytanie>) WHERE <wyrażene><operator porównania> [ANY|ALL] (<podzapytanie>) WHERE [NOT] EXISTS (<podzapytanie>)

W niektórych wyrażeniach Transact-SQL podzapytanie jest przetwarzane jak niezależne zapytanie, a jego wyniki są przekazywane do zapytania zewnętrznego. Są trzy podstawowe typy podzapytań:

Działające na listach - poprzedzone przez IN lub te, w których poprzedzający operator porównania jest zmodyfikowany przez ANY bądź ALL.

Zwracające pojedynczą wartość - poprzedzone niemodyfikowanym operatorem porównania. Testy istnienia - poprzedzone przez EXISTS.

3.1.2.1. Ograniczenia użycia podzapytań Podzapytanie podlega następującym ograniczeniom:

Lista wyboru podzapytania poprzedzonego operatorem porównania może zawierać tylko jedno wyrażenie lub nazwę kolumny (w odróżnieniu od EXISTS oraz IN, które działają odpowiednio na SELECT * lub SELECT <lista>).

Jeżeli klauzula WHERE zapytania zewnętrznego zawiera nazwę kolumny, musi być ona dostępna (złączeniowo-zgodna) z kolumną z listy podzapytania.

W listach podzapytań nie mogą być użyte kolumny typu ntext, text ani image. Podzapytania poprzedzone przez niemodyfikowany operator porównania (jeżeli nie występuje po nim ANY

lub ALL) nie mogą zawierać klauzul GROUP BY ani HAVING (bo muszą zwracać pojedynczą wartość). Słowo kluczowe DISTINCT nie może występować w podzapytaniach zawierających GROUP BY. Klauzule COMPUTE i INTO nie są dozwolone. ORDER BY może wystąpić jedynie w parze z TOP. Widoki utworzone z wykorzystaniem podzapytań są nieaktualizowalne. W liście wyboru podzapytania poprzedzonego przez EXISTS występuje * zamiast nazwy pojedynczej

kolumny. Warunki dla takiego podzapytania są identyczne jak dla standardowej listy wyboru, ponieważ wykonuje ono test istnienia i zwraca wartość TRUE lub FALSE zamiast danych.

17

3.1.2.2. Kwalifikowanie nazw kolumn w podzapytaniach W poniższym przykładzie kolumna CustomerID w klauzuli WHERE zapytania zewnętrznego jest niejawnie kwalifikowana przez nazwę tablicy w klauzuli FROM tego zapytania, tj. Sales.Store. Kolumna CustomerID w liście wyboru podzapytania jest kwalifikowana przez tabelę Sales.Customer, występującą w klauzuli FROM podzapytania. Gdyby ta kolumna nie występowała w tabeli, byłaby niejawnie kwalifikowana przez tabelę z klauzuli FROM polecenia zewnętrznego. SELECT Name

FROM Sales.Store

WHERE CustomerID NOT IN

(SELECT CustomerID

FROM Sales.Customer

WHERE TerritoryID = 5)

Zawsze istnieje możliwość zadeklarowania tabeli, aby zastąpić niejawne założenie jawną kwalifikacją.

3.1.2.3. Podzapytania z aliasami Wiele poleceń w których podzapytanie i zapytanie zewnętrzne odnoszą sie do tej samej tabeli mogą być zastąpione samozłączeniami (self-join). Przykładowo można znaleźć adresy pracowników z określonego stanu za pomocą podzapytania:

SELECT StateProvinceID, AddressID

FROM Person.Address

WHERE AddressID IN

(SELECT AddressID

FROM Person.Address

WHERE StateProvinceID = 39)

Można też użyć złączenia:

SELECT e1.StateProvinceID, e1.AddressID

FROM Person.Address AS e1

INNER JOIN Person.Address AS e2

ON e1.AddressID = e2.AddressID AND e2.StateProvinceID = 39;

Tu aliasy tablic są wymagane, ponieważ tablica łączona z samą sobą występuje w dwu różnych rolach. Aliasów można również użyć dla uzyskania czytelniejszego zapisu w podzapytaniach które odwołują się do tej samej tabeli w zapytaniu wewnętrznym i zewnętrznym.

SELECT e1.StateProvinceID, e1.AddressID


WHERE e1.AddressID IN

(SELECT e2.AddressID


WHERE e2.StateProvinceID = 39)

3.1.2.4. Podzapytania z IN (lub NOT IN) Wynikiem podzapytania poprzedzonego przez IN (lub NOT IN) jest lista zawierająca zero lub więcej wartości, które mogą być użyte przez zapytanie zewnętrzne.

SELECT Name


WHERE ProductSubcategoryID IN

(SELECT ProductSubcategoryID

FROM Production.ProductSubcategory

WHERE Name = 'Wheels');

Zapytanie powyższe działa w dwóch etapach. Najpierw w tabeli Production.ProductSubcategory poszukuje numeru identyfikacyjnego dla podkategorii o nazwie 'Wheel'. Następnie używa tego numeru do wybrania z tabeli Production.Product nazw produktów należących do tej podkategorii. Jedyna różnica w użyciu złączenia zamiast podzapytania jest ta, że w wyniku można pokazać więcej niż jedną kolumnę z więcej niż jednej tablicy.

SELECT p.Name, s.Name AS N'Subcategory'

FROM Production.Product p

INNER JOIN Production.ProductSubcategory s

ON p.ProductSubcategoryID = s.ProductSubcategoryID AND s.Name = 'Wheels';

Zapytanie poniżej znajduje nazwy wszystkich klientów, którzy mają dobry status kredytowania, u których zamówiono co najmniej 20 elementów i których średni czas dostawy jest mniejszy niż 16 dni.

18

SELECT Name

FROM Purchasing.Vendor

WHERE CreditRating = 1

AND BusinessEntityID IN


FROM Purchasing.ProductVendor

WHERE MinOrderQty >= 20 AND AverageLeadTime < 16);

Zapytanie wewnętrzne jest realizowane zwracając identyfikatory klientów, którzy spełniają warunki. Następnie realizowane jest zapytanie zewnętrzne. To samo zapytanie przy użyciu złączenia:

SELECT DISTINCT Name

FROM Purchasing.Vendor v

INNER JOIN Purchasing.ProductVendor p

ON v.BusinessEntityID = p.BusinessEntityID

WHERE CreditRating = 1

AND MinOrderQty >= 20

AND AverageLeadTime < 16;

Złączenie zawsze może być zastąpione podzapytaniem. Podzapytanie często (choć nie zawsze) można zastąpić złączeniem, ponieważ złączenia są symetryczne a podzapytania niekoniecznie. Poniższe zapytanie poszukuje nazw produktów nie będących gotowymi rowerami.

SELECT Name


WHERE ProductSubcategoryID NOT IN



WHERE Name = 'Mountain Bikes'

OR Name = 'Road Bikes'

OR Name = 'Touring Bikes');

Powyższe zapytanie nie może być zastąpione złączeniem, gdyż analogiczne nierównozłączenie posiada inne znaczenie.

3.1.2.5. Podzapytania z operatorami porównania Podzapytanie może być poprzedzone przez jeden z operatorów porównania: =, <>, >, >=, <, !>, !< lub <=. Takie podzapytanie musi zwracać pojedynczą wartość, nie możemy więc użyć GROUP BY ani HAVING, chyba że znając dane wiemy, że wynik będzie pojedynczą wartością (np. grupowanie wg klucza głównego i warunek HAVING wybierający konkretną wartość tego klucza). Podzapytania te często zawierają funkcje agregujące. SELECT Name


WHERE ListPrice >

(SELECT AVG (ListPrice)

FROM Production.Product);

3.1.2.6. Operatory porównania modyfikowane przez ANY, SOME lub ALL Operator porównania poprzedzający podzapytanie może być modyfikowany przez słowa kluczowe ALL lub ANY (ISO używa SOME jako ekwiwalentu ANY). Takie podzapytanie może zwracać zero lub więcej wartości i posiadać klauzulę GROUP BY i HAVING. Może być zastąpione przez podzapytanie z EXISTS. Przykładowo > ALL oznacza, że poszukiwana wartość musi być większa od dowolnej ze zwracanych wartości, natomiast > ANY oznacza, że poszukiwana wartość musi być większa przynajmniej od jednej ze zwracanych wartości. SELECT Name


WHERE ListPrice >= ANY

(SELECT MAX (ListPrice)


GROUP BY ProductSubcategoryID) ;

Operator = ANY jest równoważny do IN, operator <>ALL jest równoważny do NOT IN.

3.1.2.7. Podzapytania z [NOT] EXISTS Przeprowadzają one test istnienia. Klauzula WHERE zapytania zewnętrznego testuje, czy podzapytanie zwróciło jakieś wiersze. Wyrażenie [NOT] EXISTS (<podzapytanie>) zwraca wartość TRUE lub FALSE.

19

Poniższe zapytanie poszukuje nazw wszystkich produktów z podkategorii Wheels: SELECT Name


WHERE EXISTS

(SELECT *


WHERE ProductSubcategoryID =

Production.Product.ProductSubcategoryID

AND Name = 'Wheels');

Różnice w porównaniu z innymi podzapytaniami: Słowo kluczowe EXISTS nie jest poprzedzone nazwą kolumny, stałą ani innym wyrażeniem Lista wyboru w podzapytaniu niemal zawsze zawiera *. Nie ma potrzeby wypisywać nazw kolumn,

ponieważ testowane jest jedynie czy są jakieś wiersze spełniające warunek. Często można użyć IN lub operatora porównania modyfikowanego przez ANY lub ALL dla uzyskania takich samych efektów. Przykładowo poprzednie zapytanie może być zastąpione przez: SELECT Name


WHERE ProductSubcategoryID IN



WHERE Name = 'Wheels');

3.1.2.8. Podzapytania skorelowane W zapytaniach zawierających podzapytania skorelowane, zapytanie wewnętrzne jest realizowane oddzielnie dla każdego wiersza z zapytania zewnętrznego. SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID

FROM Person.Person AS c JOIN HumanResources.Employee AS e

ON e.BusinessEntityID = c.BusinessEntityID

WHERE 5000.00 IN

(SELECT Bonus

FROM Sales.SalesPerson sp

WHERE e.BusinessEntityID = sp.BusinessEntityID) ;

GO

Zapytanie wewnętrzne może się odnosić do tej samej tabeli w dwu rolach. W tym przypadku konieczne jest użycie aliasów. SELECT DISTINCT pv1.ProductID, pv1.BusinessEntityID

FROM Purchasing.ProductVendor pv1

WHERE ProductID IN

(SELECT pv2.ProductID

FROM Purchasing.ProductVendor pv2

WHERE pv1.BusinessEntityID <> pv2.BusinessEntityID)

ORDER BY pv1.BusinessEntityID

Podzapytania skorelowanego, poprzedzonego operatorem porównania użyjemy dla wyszukania pozycji zamówień, w których ilość jest poniżej średniej dla danego produktu: SELECT ProductID, OrderQty

FROM Sales.SalesOrderDetail s1

WHERE s1.OrderQty <

(SELECT AVG (s2.OrderQty)

FROM Sales.SalesOrderDetail s2

WHERE s2.ProductID = s1.ProductID);

Zapytanie zewnętrzne wybiera kolejno wiersze z tablicy pozycji (s1). Dla każdego z nich podzapytanie oblicza średnią ilość dla danego produktu i jeżeli ilość w s1 jest mniejsza od tej średniej, to rekord zostaje dołączony do wyniku. Podzapytanie może być również użyte w klauzuli HAVING zapytania zewnętrznego. W poniższym przykładzie poszukujemy modeli produktów, których maksymalna cena przekracza dwukrotnie średnią dla modelu. SELECT p1.ProductModelID

FROM Production.Product p1

GROUP BY p1.ProductModelID

HAVING MAX(p1.ListPrice) >= ALL

(SELECT 2 * AVG(p2.ListPrice)

FROM Production.Product p2

WHERE p1.ProductModelID = p2.ProductModelID) ;

20

GO

W tym przypadku podzapytanie jest wykonywane jednokrotnie dla każdej grupy zdefiniowanej w zapytaniu zewnętrznym.

3.1.3. Wyrażenie SELECT Określa kolumny zwracane przez zapytanie. SKŁADNIA

SELECT [ALL | DISTINCT] [TOP (expression) [PERCENT] [WITH TIES]]

<SELECT_list>

<SELECT_list> ::=

{

*

| {table_name | view_name | table_alias}.*

| {

[{table_name | view_name | table_alias}.]

{column_name | $IDENTITY | $ROWGUID}

| udt_column_name [{ . | :: } {{property_name | field_name}

| method_name (argument [,...n])}]

| expression

[[AS] column_alias]

}

| column_alias =expression

} [,...n]

ALL oznacza, że w wyniku mogą wystąpić identyczne wiersze (wartość domyślna), podczas gdy DISTINCT oznacza wybrania jedynie unikalnych wierszy. Klauzula TOP określa jaka część wyniku (ilość wierszy numerycznie lub procentowo) będzie zwrócona przez zapytanie.

Przykłady

Użycie SELECT do pobrania kolumn i wierszy Pobranie wszystkich kolumn i wierszy (nie ma klauzuli WHERE) SELECT *


ORDER BY Name ASC;

Pobranie wybranych kolumn i wszystkich wierszy, użycie aliasu dla tablicy SELECT p.Name, p.ProductNumber, p.ListPrice AS Price

FROM Production.Product AS p

ORDER BY Name ASC;

GO

Pobranie wybranych kolumn i wierszy. SELECT Name, ProductNumber, ListPrice AS Price


WHERE ProductLine = 'R'

AND DaysToManufacture < 4

ORDER BY Name ASC;

GO

Użycie SELECT z nagłówkami kolumn i wyliczeniami Wyświetlenie całkowitej sprzedaży i upustów dla każdego produktu. SELECT p.Name AS ProductName,

NonDiscountSales = (OrderQty * UnitPrice),

Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)


INNER JOIN Sales.SalesOrderDetail AS sod

ON p.ProductID = sod.ProductID

ORDER BY ProductName DESC;

GO

Wyświetlenie całkowitego zysku dla każdego produktu. SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)),

21

' for ', p.Name AS ProductName




ORDER BY ProductName ASC;

GO

Użycie DISTINCT w poleceniu SELECT SELECT DISTINCT JobTitle

FROM HumanResources.Employee

ORDER BY JobTitle;

3.1.4. Wyrażenie CASE Wyrażenie CASE może być użyte wewnątrz dowolnego polecenia lub klauzuli dopuszczającej wyrażenie. Można więc wystąpić w poleceniach SELECT, UPDATE, DELETE oraz SET i klauzulach takich jak select list, IN, WHERE, ORDER BY i HAVING. Wyrażenie CASE ma dwie formy:

prosta – porównanie wyrażenia wejściowego z zestawem wartości, aby określić wynik. Jeżeli żadna z wartości when_expression nie jest równa input_expression, to wynikiem jest else_result_expression (lub NULL).

logiczna – wykonanie szeregu wyrażeń logicznych dla określenia wyniku. Jeżeli żadne z wyrażeń nie zwraca wartości TRUE, to to wynikiem jest else_result_expression (lub NULL).

SKŁADNIA

Proste wyrażenie CASE:

CASE input_expression

WHEN when_expression THEN result_expression [ ...n ]

[ ELSE else_result_expression ]

END

Logiczne wyrażenie CASE:

CASE

WHEN Boolean_expression THEN result_expression [ ...n ]

[ ELSE else_result_expression ]

END

Przykład Proste wyrażenie CASE dopuszcza jedynie test równości:

SELECT ProductNumber, Category =

CASE ProductLine

WHEN 'R' THEN 'Road'

WHEN 'M' THEN 'Mountain'

WHEN 'T' THEN 'Touring'

WHEN 'S' THEN 'Other sale items'

ELSE 'Not for sale'

END,

Name


ORDER BY ProductNumber;

GO

W wyrażeniu CASE logicznym możemy innych operacji: SELECT ProductNumber, Name, 'Price Range' =

CASE

WHEN ListPrice = 0 THEN 'Mfg item - not for resale'

WHEN ListPrice < 50 THEN 'Under $50'

WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'

WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'

ELSE 'Over $1000'

END


ORDER BY ProductNumber ;

GO

22

3.1.5. Klauzula INTO Umieszcza wynik zapytania w nowej tablicy. Jeżeli w zapytaniu wystąpi kolumna posiadająca atrybut IDENTITY, do atrybut ten jest dziedziczony, chyba że wyrażenie SELECT zawiera złączenie, klauzulę GROUP BY lub funkcje agregacji albo dana kolumna wchodzi w skład wyrażenia. SKŁADNIA [INTO new_table]

Przykład

Tworzenie tablicy tymczasowej przy pomocy SELECT .. INTO IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL

DROP TABLE #Bicycles;

GO

SELECT *

INTO #Bicycles


WHERE ProductNumber LIKE 'BK%';

GO

Tworzenie tablicy na podstawie wielu źródeł SELECT c.FirstName, c.LastName, e.JobTitle, a.AddressLine1, a.City,

sp.Name AS [State/Province], a.PostalCode

INTO dbo.EmployeeAddresses

FROM Person.Person AS c

JOIN HumanResources.Employee AS e

ON e.BusinessEntityID = c.BusinessEntityID

JOIN Person.BusinessEntityAddress AS bea

ON e.BusinessEntityID = bea.BusinessEntityID

JOIN Person.Address AS a

ON bea.AddressID = a.AddressID

JOIN Person.StateProvince AS sp

ON sp.StateProvinceID = a.StateProvinceID;

GO

3.1.6. Funkcje agregujące Funkcje agregujące wykonują obliczenia na zbiorze danych i zwracają pojedynczą wartość. Za wyjątkiem COUNT, funkcje agregujące pomijają wartości NULL. Wszystkie funkcje agregujące są deterministyczne, tzn. zwracają tę samą wartość za każdym razem, kiedy zostaną uruchomione z tym samym zbiorem danych wejściowych. Funkcje agregujące mogą wystąpić jako wyrażenia jedynie w:

liście wyboru polecenia SELECT, klauzuli COMPUTE lub COMPUTE BY, klauzuli HAVING.

Transact-SQL udostępnia następujące funkcje agregujące, występujące w standardzie SQL: AVG - obliczanie wartości średniej zbioru wartości liczbowych, COUNT - zliczanie liczby elementów, MAX - zwraca wartość maksymalną zbioru, MIN - zwraca wartość minimalną zbioru, SUM - oblicza sumę wartości elementów zbioru liczbowego,

oraz szereg innych funkcji agregujących, jak np.: STDEV - oblicza standardową dewiację w zbiorze wartości liczbowych, VAR - oblicza wariancję w zbiorze wartości liczbowych.

Przykład Wyliczenie sum cen i kosztów dla wybranych produktów, zgrupowanych wg koloru SELECT Color, SUM(ListPrice), SUM(StandardCost)


WHERE Color IS NOT NULL

23

AND ListPrice != 0.00

AND Name LIKE 'Mountain%'

GROUP BY Color

ORDER BY Color;

GO

3.1.7. Klauzula FROM Określa tablice, widoki, tablice pochodne i złączenia wykorzystywane w wyrażeniach DELETE, SELECT i UPDATE. Wyrażenie SELECT musi zawierać klauzulę FROM, chyba że na liście wyboru znajdują się wyłącznie stałe, zmienne i wyrażenia arytmetyczne (nie ma nazw kolumn). SKŁADNIA


<table_source> ::=

{

table_or_view_name [[AS] table_alias] [<tablesample_clause>]

[WITH (< table_hint > [,...n])]

| rowset_function [[AS] table_alias]

[(bulk_column_alias [,...n])]

| user_defined_function [[AS] table_alias]]

| OPENXML <openxml_clause>

| derived_table [AS] table_alias [(column_alias [,...n])]

| <joined_table>

| <pivoted_table>

| <unpivoted_table>

| @variable [[AS] table_alias]

| @variable.function_call (expression [,...n]) [[AS] table_alias]

[(column_alias [,...n])]

}

<tablesample_clause> ::=

TABLESAMPLE [SYSTEM] (sample_number [PERCENT | ROWS])

[REPEATABLE (repeat_seed)]

<joined_table> ::=

{

<table_source> <join_type> <table_source> ON <search_condition>

| <table_source> CROSS JOIN <table_source>

| left_table_source {CROSS | OUTER} APPLY right_table_source

| [ ( ] <joined_table> [ ) ]

}

<join_type> ::=

[{INNER | {{LEFT | RIGHT | FULL} [OUTER]}} [<join_hint>]] JOIN

<pivoted_table> ::=

table_source PIVOT <pivot_clause> [AS] table_alias

<pivot_clause> ::=

(aggregate_function(value_column [,...n])

FOR pivot_column IN (<column_list>)

)

<unpivoted_table> ::=

table_source UNPIVOT <unpivot_clause> [AS] table_alias

<unpivot_clause> ::=(value_column FOR pivot_column IN (<column_list>))

<column_list> ::=column_name [,...n]

Przykład

Prosta klauzula FROM SELECT TerritoryID, Name

FROM Sales.SalesTerritory

ORDER BY TerritoryID

24

Użycie złączeń w klauzuli FROM Źródłem będzie iloczyn tablic (CROSS JOIN): SELECT e.BusinessEntityID, d.Name AS Department

FROM HumanResources.Employee AS e

CROSS JOIN HumanResources.Department AS d

ORDER BY e.BusinessEntityID, d.Name ;

Dzięki użyciu złączenie FULL (OUTER) JOIN zapytanie zwróci wiersze zawierające nazwę produktu i numer zamówienia, dla których jest jednakowy identyfikator produktu w tablicach SalesOrderDetail i Product. Ponadto zamieszczone będą numery zamówień, w których identyfikator produktu nie występuje w tablicy Product oraz produkty, których identyfikatory nie występują w tablicy zamówień. -- Słowo kluczowe OUTER jest tu opcjonalne.

SELECT p.Name, sod.SalesOrderID


FULL OUTER JOIN Sales.SalesOrderDetail AS sod


WHERE p.ProductID IS NULL OR sod.ProductID IS NULL

ORDER BY p.Name ;

To zapytanie zwróci wszystkie wiersze z tablicy Product i te wiersze z tablicy SalesOrderDetail, w których zgodne są identyfikatory produktu. SELECT p.Name, sod.SalesOrderID


LEFT OUTER JOIN Sales.SalesOrderDetail AS sod


ORDER BY p.Name ;

-- Alternatywnie można to zapisać:


FROM Sales.SalesOrderDetail AS sod

RIGHT OUTER JOIN Production.Product AS p


ORDER BY p.Name ;

W wyniku tego zapytania uzyskamy wyłącznie te produkty, które były zamówione. -- Domyślnie, SQL Server wykonuje INNER JOIN nawet gdy tylko samo słowo

-- kluczowe JOIN zostało użyte





ORDER BY p.Name ;

3.1.8. Klauzula WHERE Klauzula WHERE określa warunki które muszą spełniać wiersze zwracane przez polecenia DELETE, MERGE, SELECT lub UPDATE. SKŁADNIA

[WHERE <search_condition>]

Klauzula WHERE składa się z jednego lub więcej (nie ma ograniczeń) warunków wyboru połączonych operatorami logicznymi AND, OR i NOT. SKŁADNIA

<search_condition> ::=

{[NOT] <predicate> | (<search_condition>)}

[{AND | OR} [NOT] {<predicate> | ( <search_condition>)}] [,...n]

<predicate> ::=

{expression {= | <> | != | > | >= | !> | < | <= | !<} expression

| string_expression [NOT] LIKE string_expression [ESCAPE 'escape_character']

| expression [ NOT ] BETWEEN expression AND expression

| expression IS [ NOT ] NULL

| CONTAINS ({column | *} ,'<contains_search_condition>')

| FREETEXT ({column | *} ,'freetext_string')

| expression [NOT] IN (subquery | expression [,...n])

| expression {= | <> | != | > | >= | !> | < | <= | !<} {ALL | SOME | ANY}

(subquery)

| EXISTS (subquery)}

25

Operator LIKE wykorzystuje znaki specjalne: % - dowolny ciąg 0 lub więcej znaków _ - dowolny pojedynczy znak [.-.] - dowolny pojedynczy znak z danego zakresu (np. [0-9] oznacza dowolną cyfrę) [^.-.] - dowolny znak spoza danego zakresu.

Znak zdefiniowany jako ESCAPE niweluje specjalne znaczenie tych znaków.

Przykład

Użycie WHERE z LIKE i ESCAPE W poniższym przykładzie poszukiwany będzie ciąg znaków green_: SELECT * FROM Production.ProductPhoto

WHERE LargePhotoFileName LIKE '%greena_%' ESCAPE 'a' ;

3.1.9. Klauzula GROUP BY Klauzula ta grupuje wybrany zbiór wierszy wg jednej lub większej ilości kolumn lub wyrażeń. Dla każdej grupy zwracany jest jeden wiersz. Funkcje agregacji w liście wyboru polecenia SELECT dostarczają informacji na temat każdej z grup, a nie pojedynczych wierszy. SKŁADNIA GROUP BY <column_expression> [,...n]

Przykłady

Proste użycie GROUP BY SELECT SalesOrderID, SUM(LineTotal) AS SubTotal

FROM Sales.SalesOrderDetail AS sod

GROUP BY SalesOrderID

ORDER BY SalesOrderID;

Użycie GROUP BY z wieloma tablicami SELECT a.City, COUNT(bea.AddressID) AS EmployeeCount

FROM Person.BusinessEntityAddress AS bea

INNER JOIN Person.Address AS a

ON bea.AddressID = a.AddressID

GROUP BY a.City

ORDER BY a.City;

Użycie GROUP BY z wyrażeniem SELECT DATEPART(yyyy,OrderDate) AS N'Year', SUM(TotalDue) AS N'Total Order Amount'


GROUP BY DATEPART(yyyy,OrderDate)

ORDER BY DATEPART(yyyy,OrderDate);

Użycie GROUP BY z klauzulą HAVING W poleceniach SELECT z grupowaniem można użyć klauzuli HAVING dla określenia które z wierszy w każdej z grup będą brane pod uwagę. Jeżeli w poleceniu SELECT klauzula GROUP BY nie występuje, to HAVING zachowuje się dokładnie jak WHERE. W klauzuli HAVING nie mogą być używane pola typu text, image ani ntext.

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal

FROM Sales.SalesOrderDetail

GROUP BY SalesOrderID

HAVING SUM(LineTotal) > 100000.00

ORDER BY SalesOrderID ;

3.1.10. Klauzula ORDER BY Określa sposób uporządkowania elementów w kolumnach zwróconych przez polecenie SELECT. Klauzula ta ma ograniczone zastosowanie w przypadku widoków, podzapytań, tablic pochodnych.

26

SKŁADNIA [ ORDER BY

{

order_by_expression [ COLLATE collation_name ] [ ASC | DESC ]

} [ ,...n ]

]

COLLATE określa specyficzny sposób sortowania danych tekstowych (np. uwzględniający znaki diakrytyczne języków). Wartości NULL są traktowane jako najmniejsze.

Przykład Uporządkowanie malejące numerycznej zawartości kolumny ProductID. SELECT ProductID, Name FROM Production.Product

WHERE Name LIKE 'Lock Washer%'

ORDER BY ProductID DESC;

Pominięcie słowa DESC wywoła porządkowanie domyślne (narastające).

3.1.11. Klauzula COMPUTE Służy do generowania podsumowania z użyciem wybranej funkcji agregującej, na zakończenie danych wynikowych. Ta klauzula będzie wycofana. SKŁADNIA [ COMPUTE

{ { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM }

( expression ) } [ ,...n ]

[ BY expression [ ,...n ] ]

]

Wyrażenie BY (występujące wyłącznie wraz z ORDER BY) generuje sumy częściowe.

Przykłady

Użycie COMPUTE dla uzyskania podsumowań W wyrażeniu SELECT użyto prostej klauzuli COMPUTE dla uzyskania sumy ogólnej dla kolumn SubTotal i TotalDue: SELECT CustomerID, OrderDate, SubTotal, TotalDue


WHERE SalesPersonID = 35

ORDER BY OrderDate

COMPUTE SUM(SubTotal), SUM(TotalDue);

Użycie COMPUTE BY dla uzyskania podsumowań z sumami częściowymi W wyrażeniu SELECT użyto prostej COMPUTE dla uzyskania sumy ogólnej dla kolumn SubTotal i TotalDue i podsumowań dla poszczególnych wartości w SalesPersonID: SELECT SalesPersonID, CustomerID, OrderDate, SubTotal, TotalDue


ORDER BY SalesPersonID, OrderDate

COMPUTE SUM(SubTotal), SUM(TotalDue) BY SalesPersonID;

3.1.12. Operator JOIN Operator JOIN definiuje sposób połączenia dwu tabel w zapytaniu przez:

Wyszczególnienie kolumn z każdej z tabel, które mają być użyte dla złączenia. Typowy warunek złączenia określa klucz obcy z jednej z tabel i skojarzony z nim klucz z drugiej tabeli.

Określenie operatora (np. = lub <>), który będzie zastosowany do porównania wartości w kolumnach. Dostępne są następujące typy operatora:

INNER JOIN, zwracane są wszystkie dopasowane pary wierszy z obu tabel. INNER jest wartością domyślną. FULL OUTER JOIN, oprócz wierszy dopasowanych zwracane są wszystkie pozostałe wiersze z obu tabel,

uzupełnione wartościami NULL. LEFT OUTER JOIN, oprócz wierszy dopasowanych zwracane są wszystkie pozostałe wiersze z lewej tabeli,

uzupełnione wartościami NULL. RIGHT OUTER JOIN, oprócz wierszy dopasowanych zwracane są wszystkie pozostałe wiersze z prawej

tabeli, uzupełnione wartościami NULL.

27

CROSS JOIN, zwraca produkt kartezjański obu tabel SKŁADNIA <first_table> <join_type> <second_table> ON <join_condition>

|<first_table> CROSS JOIN <second_table>

|<first_table> {CROSS|OUTER} APPLY <second_table>

<join_type>::=

[{ INNER|{{LEFT|RIGHT|FULL}[ OUTER]}}[ <join_hint>]] JOIN

3.1.13. Klauzule UNION, INTERSECT i EXCEPT UNION łączy wyniki dwóch lub więcej zapytań w pojedynczy zestaw wierszy, które należały do poszczególnych zapytań. Klauzula UNION usuwa powtarzające się wiersze z wyniku, chyba że dodano słowo kluczowe ALL. EXCEPT zwraca unikalne wartości z wyników zapytania po lewej stronie, których nie ma w wynikach zapytania z prawej strony. INTERSECT zwraca unikalne wartości występujące jednocześnie w wynikach obu zapytań. Muszą przy tym być zachowane następujące warunki:

Ilość i kolejność kolumn musi być taka sama we wszystkich zapytaniach Typy danych w odpowiednich kolumnach muszą być kompatybilne.

SKŁADNIA

{<query_specification>|(<query_expression>)}

UNION [ALL] <query_specification>|(<query_expression>)

[UNION [ALL] <query_specification>|(<query_expression>) [,...n]]

{<query_specification>|(<query_expression>)}

{EXCEPT|INTERSECT}{<query_specification>|(<query_expression>)}

Jeśli w wyrażeniu zostały użyte obok siebie operatory UNION, EXCEPT I INTERSECT, to kolejność wykonywania jest następująca:

1. wyrażenia w nawiasach 2. operacja INTERSECT 3. operacje UNION i EXCEPT w kolejności od lewej do prawej

Przykłady

Proste użycie UNION W następującym przykładzie dane wynikowe składają się z zawartości kolumn ProductModelID i Name wziętych z tablic ProductModel oraz Gloves: IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL

DROP TABLE dbo.Gloves;

GO

-- Create Gloves table.

SELECT ProductModelID, Name

INTO dbo.Gloves

FROM Production.ProductModel

WHERE ProductModelID IN (3, 4);

GO

-- Here is the simple union.


FROM Production.ProductModel

WHERE ProductModelID NOT IN (3, 4)

UNION


FROM dbo.Gloves

ORDER BY Name;

GO

Użycie EXCEPT I INTERSECT W pierwszym zapytaniu pobieramy wszystkie elementy kolumny ProductID z tablicy Production.Product. SELECT ProductID

28

FROM Production.Product ;

--Result: 504 Rows

Następnie wybieramy tylko te elementy, których ID występują w tablicy Production.WorkOrder: SELECT ProductID


INTERSECT

SELECT ProductID

FROM Production.WorkOrder ;

--Result: 238 Rows (produkty, na które s¹ zlecenia produkcji)

Następnie te elementy, których nie ma w tablicy WorkOrders: SELECT ProductID


EXCEPT

SELECT ProductID

FROM Production.WorkOrder ;

--Result: 266 Rows (produkty, na które nie ma zleceñ produkcji)

A w końcu te zlecania produkcyjne, w których ID nie występuje w tablicy produktów: SELECT ProductID

FROM Production.WorkOrder

EXCEPT

SELECT ProductID

FROM Production.Product ;

--Result: 0 Rows (zlecenia produkcji bez produktów)

3.2. Dodawanie wierszy do tabeli (INSERT) Do wprowadzenia nowych wierszy w tabeli służy polecenie SELECT. SKŁADNIA [ WITH <common_table_expression> [ ,...n ] ]

INSERT

{

[ TOP ( expression ) [ PERCENT ] ]

[ INTO ] { <object> | rowset_function_limited

[ WITH ( <Table_Hint_Limited> [ ...n ] ) ] }

{

[ ( column_list ) ]

[ <OUTPUT Clause> ]

{ VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ]

| derived_table

| execute_statement

| <dml_table_source>

| DEFAULT VALUES

}

}

}

[ ; ]

gdzie: <object> ::=

{

[ server_name . database_name . schema_name .

| database_name .[ schema_name ] .

| schema_name .

]

table_or_view_name

}

DBMS automatycznie wprowadza wartości w kolumnach: posiadających właściwość IDENTITY posiadających zdefiniowaną wartość DEFAULT mają typ danych datastamp dopuszczających wartość NULL o wartości obliczanej na podstawie wyrażenia.

29

Przykłady

Wstawianie pojedynczego wiersza W poniższym przykładzie wprowadzamy wiersz do tablicy Production.UnitMeasure, posiadającej kolumny UnitMeasureCode, Name oraz ModifiedDate. INSERT INTO Production.UnitMeasure

VALUES (N'FT', N'Feet', '20080414');

Wstawianie wielu wierszy (MS SQL Server 2008) INSERT INTO Production.UnitMeasure

VALUES (N'FT2', N'Square Feet ', '20080923'),

(N'Y', N'Yards', '20080923'),

(N'Y3', N'Cubic Yards', '20080923');

Wstawianie w zmienionej kolejności kolumn INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate)

VALUES (N'Square Yards', N'Y2', GETDATE());

Wstawianie danych z innej tabeli INSERT INTO RetiredEmployee (Name, EmployeeID)

SELECT (Name, SalesmanID) FROM Salesmen

WHERE Salesmen.DateOfBirth < '19450101';

3.3. Modyfikacja wierszy w tabeli (UPDATE) Do modyfikacji wierszy w tabeli służy polecenie UPDATE. SKŁADNIA [ WITH <common_table_expression> [...n]]

UPDATE

[ TOP (expression) [PERCENT]]

{ { table_alias | <object> | rowset_function_limited

[WITH (<Table_Hint_Limited> [...n])]

}

|@table_variable

}

SET

{column_name = {expression|DEFAULT|NULL}

|{udt_column_name.{{property_name=expression

|field_name = expression}

|method_name (argument[,...n])

}

}

|column_name {.WRITE (expression, @Offset, @Length)}

|@variable = expression

|@variable = column = expression

|column_name {+=|-=|*=|/=|%=|&=|^=| |=} expression

|@variable {+=|-=|*=|/=|%=|&=|^=| |=} expression

|@variable = column {+=|-=|*=|/=|%=|&=|^=| |=} expression

} [,...n]

[<OUTPUT Clause> ]


[WHERE {<search_condition>

|{[CURRENT OF

{{[GLOBAL] cursor_name}

|cursor_variable_name

}

]

}

}

30

]

[OPTION (<query_hint> [,...n])]

[ ; ]

Przykłady Zmiana wartości we wszystkich wierszach w pojedynczej kolumnie: UPDATE Person.Address

SET ModifiedDate = GETDATE();

Jednoczesna zmiana wielu kolumn UPDATE Sales.SalesPerson

SET Bonus = 6000, CommissionPct = .10, SalesQuota = NULL;

GO

Wybranie wierszy do modyfikacji za pomocą klauzuli WHERE: UPDATE Production.Product

SET Color = N'Metallic Red'

WHERE Name LIKE N'Road-250%' AND Color = N'Red';

-- Sprawdzenie czy cokolwiek zmodyfikowano

IF @@ROWCOUNT = 0

PRINT 'Nie zmodyfikowano żadnego wiersza w tabeli'

GO

Wybranie pierwszych 10 wierszy za pomocą TOP UPDATE TOP (10) HumanResources.Employee

SET VacationHours = VacationHours * 1.25 ;

GO

Użycie podzapytania Wybranie pierwszych 10 wierszy za pomocą podzapytania porządkującego: UPDATE HumanResources.Employee

SET VacationHours = VacationHours + 8

FROM (SELECT TOP 10 BusinessEntityID FROM HumanResources.Employee

ORDER BY HireDate ASC) AS th

WHERE HumanResources.Employee.BusinessEntityID = th.BusinessEntityID;

GO

Użycie podzapytania w klauzuli SET UPDATE Sales.SalesPerson

SET SalesYTD = SalesYTD +

(SELECT SUM(so.SubTotal)

FROM Sales.SalesOrderHeader AS so

WHERE so.OrderDate = (SELECT MAX(OrderDate)

FROM Sales.SalesOrderHeader AS so2

WHERE so2.SalesPersonID = so.SalesPersonID)

AND Sales.SalesPerson.BusinessEntityID = so.SalesPersonID

GROUP BY so.SalesPersonID);

GO

Użycie wartości domyślnej UPDATE Production.Location

SET CostRate = DEFAULT

WHERE CostRate > 20.00;

3.4. Usuwanie wierszy z tabeli (DELETE) Do usuwania wierszy z tabeli służy polecenie DELETE. SKŁADNIA [WITH <common_table_expression> [,...n]]

DELETE

[TOP (expression) [PERCENT]]

[FROM]

{<object>|rowset_function_limited

[WITH (<table_hint_limited> [...n])]

}

31

[<OUTPUT Clause> ]

[FROM <table_source> [,...n]]

[WHERE {<search_condition>

|{[CURRENT OF

{{[GLOBAL] cursor_name}|cursor_variable_name}]}}

]

[OPTION (<Query Hint> [,...n])]

[ ; ]

Przykłady

Usunięcie wszystkich wierszy DELETE FROM Sales.SalesPersonQuotaHistory;

Usunięcie wybranych wierszy DELETE FROM Production.ProductCostHistory

WHERE StandardCost > 1000.00;

Użycie podzapytania Klasyczne wykorzystanie podzapytania: DELETE FROM Sales.SalesPersonQuotaHistory



FROM Sales.SalesPerson

WHERE SalesYTD > 2500000.00);

GO

Wykorzystanie możliwości Transact-SQL w miejsce podzapytania: DELETE FROM Sales.SalesPersonQuotaHistory

FROM Sales.SalesPersonQuotaHistory AS spqh

INNER JOIN Sales.SalesPerson AS sp

ON spqh.BusinessEntityID = sp.BusinessEntityID

WHERE sp.SalesYTD > 2500000.00;

GO

32

4. Elementy składni języka T-SQL

4.1. Powołanie zmiennej lokalnej (DECLARE) Zmienne są deklarowane w skryptach bądź procedurach za pomocą polecenia DECLARE. Wartości zmiennym nadają polecenia SET oraz SELECT. Po utworzeniu wszystkim zmiennym nadawana jest wartość NULL, chyba że w poleceniu DECLARE zostały one zainicjowane. Zmienna lokalna istnieje tylko wewnątrz funkcji lub bloku w którym była zadeklarowana. SKŁADNIA DECLARE

{

{{@local_variable [AS] data_type}|[ = value]}

|{@cursor_variable_name CURSOR}} [,...n]

|{ @table_variable_name [AS] <table_type_definition>

|<user-defined table type>}

Nazwa zmiennej lokalnej musi się zaczynać od znaku @. Typ danych może być dowolny, za wyjątkiem text, ntext oraz image.

Przykład

Użycie DECLARE DECLARE @find VARCHAR(30);

SET @find = 'Man%';

/* Można także zapisać (w MS SQL Server 2008):

DECLARE @find VARCHAR(30) = 'Man%';

*/

SELECT p.LastName, p.FirstName, ph.PhoneNumber

FROM Person.Person p

JOIN Person.PersonPhone ph

ON p.BusinessEntityID = ph.BusinessEntityID

WHERE LastName LIKE @find;

Zadeklarowanie lokalnej tablicy DECLARE @MyTableVar table(

EmpID INT NOT NULL,

OldVacationHours INT,

NewVacationHours INT,

ModifiedDate DATETIME);

Nadanie wartości poleceniem SELECT DECLARE @var1 NVARCHAR(30);

SELECT @var1 = 'Generic Name';

SELECT @var1 = Name

FROM Sales.Store

WHERE CustomerID = 1000 ;

SELECT @var1 AS 'Company Name';

4.2. Tworzenie procedur składowych (CREATE PROCEDURE) Z obsługą procedur związane są polecenia:

CREATE PROCEDURE ALTER PROCEDURE DROP PROCEDURE

Procedury zawierają kod, który może być uruchamiany za pomocą polecenia EXECUTE. SKŁADNIA CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]

[ { @parameter [ type_schema_name. ] data_type }

[ VARYING ] [ = default ] [ OUT | OUTPUT ]

33

] [ ,...n ]

[ WITH <procedure_option> [ ,...n ] ]

[ FOR REPLICATION ]

AS { <sql_statement> [;][ ...n ] | <method_specifier> }

[;]

Przykład

Procedura z argumentami wejściowymi IF OBJECT_ID ( 'HumanResources.uspGetEmpl', 'P' ) IS NOT NULL

DROP PROCEDURE HumanResources.uspGetEmpl;

GO

CREATE PROCEDURE HumanResources.uspGetEmpl

@LastName NVARCHAR(50),

@FirstName NVARCHAR(50)

AS

SET NOCOUNT ON;

SELECT FirstName,LastName,Department

FROM HumanResources.vEmployeeDepartmentHistory

WHERE FirstName = @FirstName AND LastName = @LastName;

GO

Uruchomienie tej procedury: EXECUTE HumanResources.uspGetEmpl N'Ackerman', N'Pilar';

lub EXECUTE HumanResources.uspGetEmpl @FirstName = N'Pilar', @LastName = N'Ackerman';

Procedura z argumentami wyjściowymi IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL

DROP PROCEDURE Production.uspGetList;

GO

CREATE PROCEDURE Production.uspGetList @Product VARCHAR(40)

, @MaxPrice money

, @ComparePrice money OUTPUT

, @ListPrice money OUT

AS

SET NOCOUNT ON;

SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'


JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @Product AND p.ListPrice < @ MaxPrice;

-- Populate the output variable @ListPprice.

SET @ListPrice = (SELECT MAX(p.ListPrice)


JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @Product AND p.ListPrice < @ MaxPrice);

-- Populate the output variable @ComparePrice.

SET @ComparePrice = @ MaxPrice;

GO

Wywołanie tej procedury: DECLARE @ComparePrice money, @Cost money

EXECUTE Production.uspGetList '%Bikes%', 700,

@ComparePrice OUT,

@Cost OUTPUT

IF @Cost <= @ComparePrice

BEGIN

PRINT 'These products can be purchased for less than $' +

RTRIM(CAST(@ComparePrice AS VARCHAR(20)))+'.'

END

34

ELSE

PRINT 'The prices for all products in this category exceed $' +

RTRIM(CAST(@ComparePrice AS VARCHAR(20)))+'.'

4.3. Uruchomienie kodu (EXECUTE) Polecenie to wykonuje rozkaz, kod zapisany w skrypcie, funkcję lub procedurę składową.. SKŁADNIA

Uruchomienie procedury składowej lub funkcji [{EXEC|EXECUTE}]

{

[@return_status = ]

{module_name [;number]|@module_name_var}

[[@parameter =] {value|@variable [OUTPUT]|[DEFAULT]}

] [,...n]

[WITH RECOMPILE ]

}

[ ; ]

Uruchomienie kodu w postaci ciągu znaków {EXEC|EXECUTE}

({@string_variable|[N]'tsql_string'} [+ ...n])

[AS {LOGIN|USER} = ' name ']

[ ; ]

Przesłanie polecenia do serwera {EXEC|EXECUTE }

({@string_variable|[N]'command_string [?]'} [ + ...n]

[{, {value|@variable [OUTPUT]}}[ ...n]]

)

[AS {LOGIN|USER} = ' name ' ]

[AT linked_server_name]

[ ; ]

4.4. Sterowanie programem

4.4.1. Deklaracja bloku (BEGIN...END) Blok programu zawarty między wyrażeniami BEGIN i END grupuje polecenia: SKŁADNIA BEGIN

{

sql_statement|statement_block

}

END

Bloki Begin...END mogą być zagnieżdżone.

Przykład BEGIN TRANSACTION;

GO

IF @@TRANCOUNT = 0

BEGIN

SELECT FirstName, MiddleName

FROM Person.Person WHERE LastName = 'Adams';

ROLLBACK TRANSACTION;

PRINT N'Rolling back the transaction two times would cause an error.';

END;


PRINT N'Rolled back the transaction.';

GO

35

4.4.2. Instrukcja warunkowa (IF...ELSE) Instrukcja wykonywana jest przy spełnieniu warunku: SKŁADNIA IF Boolean_expression

{ sql_statement | statement_block }

[ ELSE

{ sql_statement | statement_block } ]

4.4.3. Instrukcja pętli (WHILE) Instrukcja powtarzana jest przy spełnieniu warunku: SKŁADNIA WHILE Boolean_expression

{ sql_statement | statement_block | BREAK | CONTINUE }

Polecenie BREAK powoduje przerwanie pracy pętli. Polecenie CONTINUE powoduje przejście na początek pętli i kontynuację obliczeń. Jeżeli w programie umieścimy etykiety, to możliwe jest także użycie polecenia skoku GOTO.

Przykład WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300

BEGIN

UPDATE Production.Product

SET ListPrice = ListPrice * 2

SELECT MAX(ListPrice) FROM Production.Product

IF (SELECT MAX(ListPrice) FROM Production.Product) > $500

BREAK

ELSE

CONTINUE

END

PRINT 'Too much for the market to bear';

Pętla jest powtarzana aż do osiągnięcia średniej ceny = $300 lub do przekroczenia przez cenę najdroższego produktu wartości $500.

4.4.4. Instrukcja oczekiwania (WAITFOR) Blokuje wykonywanie instrukcji aż do określonego momentu czasu (WAITFOR TIME <czas>) lub o określony czas (WAITFOR DELAY <opóźnienie>). SKŁADNIA WAITFOR

{

DELAY 'time_to_pass'

| TIME 'time_to_execute'

| [ ( receive_statement ) | ( get_conversation_group_statement ) ]

[ , TIMEOUT timeout ]

}

Przykład Procedura wykorzystująca lokalną zmienną, zatrzymująca realizację programu na określony czas. IF OBJECT_ID('dbo.TimeDelay_hh_mm_ss','P') IS NOT NULL

DROP PROCEDURE dbo.TimeDelay_hh_mm_ss;

GO

CREATE PROCEDURE dbo.TimeDelay_hh_mm_ss

(

@DelayLength CHAR(8)= '00:00:00'

)

AS

DECLARE @ReturnInfo VARCHAR(255)

IF ISDATE('2000-01-01 ' + @DelayLength + '.000') = 0

BEGIN

SELECT @ReturnInfo = 'Invalid time ' + @DelayLength

+ ',hh:mm:ss, submitted.';

-- Ten PRINT tylko dla testów

36

PRINT @ReturnInfo

RETURN(1)

END

BEGIN

WAITFOR DELAY @DelayLength

SELECT @ReturnInfo = 'A total time of ' + @DelayLength +

', hh:mm:ss, has elapsed! Your time is up.'

-- ten PRINT tylko dla testów

PRINT @ReturnInfo;

END;

GO

-- Wywołanie procedury dbo.TimeDelay_hh_mm_ss, opóźnienie 10 s.

EXEC TimeDelay_hh_mm_ss '00:00:10';

GO

4.5. Kursory Wynikiem poleceń MS SQL Server w ogólnym przypadku jest tablica, jednak czasem pojawia się potrzeba przetwarzania pojedynczych wierszy tablicy wynikowej. Może to być zrealizowane za pomocą kursora. Z kursorami związane są polecenia:

DECLARE CURSOR OPEN FETCH CLOSE DEALLOCATE

4.5.1. Definiowanie (DECLARE CURSOR) Kursor jest definiowany poleceniem DECLARE CURSOR. Dopuszczalne są dwie składnie: standardowa i rozszerzona, ich elementów nie można mieszać ze sobą. SKŁADNIA -- Składnia ISO

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

FOR select_statement

[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

[;]

-- Rozszerzona składnia Transact-SQL

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]

[ FORWARD_ONLY | SCROLL ]

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

[ TYPE_WARNING ]

FOR select_statement

[ FOR UPDATE [ OF column_name [ ,...n ] ] ]

[;]

Wprowadzenie typu kursora SCROLL, umożliwia stosowanie w FETCH wszystkich opcji dostępu, w przeciwnym przypadku dostępna jest wyłącznie opcja NEXT. Po klauzuli FOR umieszczone jest wyrażenie select_statement, które definiuje dane zwracane przez kursor. Kursor może wykorzystywać funkcję CURSOR_STATUS, która zwraca wartości: 1 gdy wynikiem jest co najmniej jeden wiersz, 0 gdy wynik jest pusty, -1 gdy kursor jest zamknięty, -3 gdy nie ma kursora o podanej nazwie.

Przykład Kursor pobiera kolejne wiersze wybranych kolumn tabeli Teachers. Przed użyciem kursor musi być uaktywniony poleceniem OPEN. Przewijanie może się odbywać tylko do przodu. DECLARE c_teacher CURSOR

FOR SELECT TNo, TName FROM Teachers;

OPEN c_teacher

FETCH NEXT FROM c_teacher;

37

4.5.2. Pobieranie danych (FETCH) Polecenie FETCH używane jest do pobrania z tablicy pojedynczego wiersza wskazywanego kursorem. SKŁADNIA FETCH

[[NEXT |PRIOR |FIRST |LAST |ABSOLUTE{n|@nvar} |RELATIVE{n|@nvar} ]

FROM ]

{{[GLOBAL ]cursor_name} @cursor_variable_name }

[ INTO @variable_name [ ,...n ] ]

Poszczególne kolumny z pobranego przez kursor wiersza mogą być podstawione na zmienne występujące na liście występującej po klauzuli INTO. Przydatne może być użycie zmiennej systemowej @@FETCH_STATUS, która (globalnie) zawiera informację o ostatnio realizowanym poleceniu FETCH. Zmienna przyjmuje wartość 0, gdy operacja zakończyła się pomyślnie, -1 gdy operacja nie została wykonana lub -2, gdy nie ma wiersza do pobrania.

4.6. Wyzwalacze (triggery) Microsoft SQL Server udostępnia wyzwalacze DML, wykonywane zarówno po, jak i zamiast instrukcji INSERT, UPDATE lub DELETE. Można definiować wyzwalacze dla tabel oraz widoków, z tym, że w przypadku widoków można stosować tylko wyzwalacz INSTEAD OF. Od wersji SQL Server 2005 wprowadzono wyzwalacze DDL, które są wykonywane w reakcji na bardzo szeroki zakres zdarzeń, takich jak DROP TABLE, CREATE TABLE, ALTER TABLE i inne. Wprowadzono również specjalne wyzwalacze reagujące na takie zdarzenia jak łączenie bądź rozłączanie się użytkownika z bazą danych. Z wyzwalaczami związane są polecenia:

CREATE TRIGGER ALTER TRIGGER DROP TRIGGER ENABLE TRIGGER DISABLE TRIGGER

4.6.1. Wyzwalacze DML SKŁADNIA CREATE TRIGGER [ schema_name . ]trigger_name

ON { table | view }

[ WITH <dml_trigger_option> [ ,...n ] ]

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

[ NOT FOR REPLICATION ]

AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

Wyzwalacze nie mogą dotyczyć tabel tymczasowych. Definicja wyzwalacza przechowywana jest wraz z definicją tabeli (w zakładce Triggers). Typ FOR | AFTER oznacza realizację procedury wyzwalacza po zrealizowaniu wszystkich czynności związanych z wyzwalającym poleceniem, natomiast typ INSTEAD OF oznacza zastąpienie polecenia wyzwalającego przez procedurę wyzwalacza. Takie wyzwalacze mają dodatkowe ograniczenia w przypadku tabel powiązanych z warunkami ON DELETE lub ON UPDATE w kluczach obcych. Parametr dml_trigger_option w opcjonalnej klauzuli WITH może przyjmować wartości ENCRYPTION (gdy chcemy ukryć tekst procedury) i EXECUTE AS {CALLER|SELF|OWNER|<user_name>} (dające możliwość identyfikacji użytkownika dla kontroli uprawnień). Po słowie kluczowym AS występuje ciało procedury wyzwalacza: zestaw dowolnych poleceń transact-SQL lub odwołanie do zewnętrznej metody z .NET Framework. Jeśli chcemy sprawdzić, czy zdarzenie UPDATE dotyczyło danej kolumny, możemy użyć funkcji logicznej UPDATE(<kolumna>). W przypadku testowania większej ilości wprowadzonych zmian, wygodna może być funkcja COLUMNS_UPDATED, zwracająca binarnie informacje o zmienionych kolumnach (1 - była zmiana, 0 - nie).

Przykład Po wprowadzeniu zmian w kolumnie TITLE w tabeli Teachers pojawi się przypomnienie o potrzebie kontaktu z Dziekanatem: USE School2;

GO

IF OBJECT_ID ('remainder', 'TR') IS NOT NULL

DROP TRIGGER remainder;

GO

38

CREATE TRIGGER remainder

ON Teachers

AFTER UPDATE

AS IF UPDATE('TITLE') RAISERROR ('Notify Dean''s Bureau', 16, 10);

GO

Wyzwalacze DML wykonują swoje działania korzystając z dostępu do logicznych (pojęciowych) tabel inserted i deleted, posiadających strukturę zgodną z tablicą dla której był definiowany wyzwalacz. Przykładowo, chcąc uzyskać listę usuniętych rekordów, należy wykonać polecenie SELECT * FROM Deleted; Dzięki wyzwalaczom DML można poszerzyć integralność referencyjną bazy danych, testować wystąpienie błędów i realizować akcje naprawcze, porównywać stany tabeli przed i po transakcji i odpowiednio zareagować na występujące różnice.

Przykład Przy wprowadzaniu nowego zamówienia sprawdzamy, czy klient nie jest w grupie dłużników. W tym celu porównujemy dane z wprowadzonego zamówienia (tablica Inserted połączona z PurchaseOrderHeader) z tablicą takich klientów (tablica Vendor z warunkiem CreditRanking = 5); USE AdventureWorks2008;

GO

IF OBJECT_ID ('LowCredit','TR') IS NOT NULL

DROP TRIGGER LowCredit;

GO

CREATE TRIGGER LowCredit ON PurchaseOrderHeader

AFTER INSERT

AS

IF EXISTS (SELECT *

FROM PurchaseOrderHeader p

JOIN Inserted AS i

ON p.PurchaseOrderID = i.PurchaseOrderID

JOIN Vendor AS v

ON v.BusinessEntityID = p.VendorID

WHERE v.CreditRating = 5

)

BEGIN

RAISERROR ('A vendor''s credit rating is too low.', 16, 1);


RETURN

END;

GO

4.6.2. Wyzwalacze DDL SKŁADNIA CREATE TRIGGER trigger_name

ON { ALL SERVER | DATABASE }

[ WITH <ddl_trigger_option> [ ,...n ] ]

{ FOR | AFTER } { event_type | event_group } [ ,...n ]

AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }

Wyzwalacz DDL jest uruchamiany po wystąpieniu zdarzenia wywołującego (AFTER, nie ma opcji INSTEAD OF). Przykładowo wyzwalacz DDL może być uruchomiony przez zdarzenie ALTER SERVER CONFIGURATION lub np. gdy usuwana jest tablica za pomocą DROP TABLE. Nie są tworzone tabele inserted ani deleted. Informacja na temat zdarzenia i wywołanych zmian może być uzyskana za pomocą funkcji EVENTDATA, zwracającej strukturę XML zawierającą informację o czasie zdarzenia, identyfikatorze procesu połączenia oraz typie zdarzenia wywołującego wyzwalacz. Każdemu zdarzeniu DDL można przypisać wiele wyzwalaczy. Jest możliwe określenie który z nich ma być realizowany jako pierwszy bądź ostatni.

39

4.6.3. Wyzwalacze logowania SKŁADNIA CREATE TRIGGER trigger_name

ON ALL SERVER

[ WITH <logon_trigger_option> [ ,...n ] ]

{ FOR| AFTER } LOGON

AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }

Przykład Wyzwalacz logowania pozwala cofnąć (ROLLBACK) operację logowania np. przy ograniczeniu ilości logowań z wykorzystaniem tymczasowego hasła. USE master;

GO

CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,

CHECK_EXPIRATION = ON;

GO

GRANT VIEW SERVER STATE TO login_test;

GO

CREATE TRIGGER connection_limit_trigger

ON ALL SERVER WITH EXECUTE AS 'login_test'

FOR LOGON

AS

BEGIN

IF ORIGINAL_LOGIN()= 'login_test' AND

(SELECT COUNT(*) FROM sys.dm_exec_sessions

WHERE is_user_process = 1 AND

original_login_name = 'login_test') > 3

ROLLBACK;

END;

4.7. Funkcje wbudowane W Transact-SQL dostępne są następujące typy funkcji wbudowanych:

Rowset functions Aggregate functions (często używane z klauzulą GROUP BY polecenia SELECT):

o AVG o MIN o CHECKSUM_AGG o OVER Clause o COUNT o COUNT_BIG o STDEV o GROUPING o STDEVP o GROUPING_ID o SUM o MAX o VAR o VARP

Ranking functions Scalar functions, które można podzielić na kategorie:

o Configuration functions (zwracają informacje o aktualnej konfiguracji). o Cryptographic functions (obsługują kodowanie, dekodowanie oraz podpisy cyfrowe i ich

sprawdzanie. o Cursor functions (zwracają informacje o kursorach). o Data Type functions (zwracają informacje o wartości autoinkrementacji i innych typach danych). o Date and Time Data Types and Functions (wykonują operacje na danych daty i czasu i zwracają

dane w postaci ciągu znaków, liczby lub zmiennej typu daty i czasu). o Mathematical functions (wykonują obliczenia na podstawie argumentów wejściowych i zwracają

wartości numeryczne). o Metadata functions (zwracają informacje o bazie danych i jej obiektach).

40

o ODBC Scalar functions. o Replication functions o Security functions (zwracają informacje o użytkownikach i rolach). o String functions (wykonują operacje na ciągach znaków (char, varchar) i zwracają ciągi znaków lub

wartości numeryczne). o System functions (wykonują operacje i zwracają informacje o wartościach, obiektach i ustawieniach

w SQL Server). o System Statistical functions (zwracają informacje statystyczne o systemie). o Text and Image functions (wykonują operacje na tekstach, obrazach lub kolumnach). o Trigger functions (zwracają informacje o wyzwalaczach).

Funkcje wbudowane mogą być deterministyczne (zawsze zwracające ten sam wynik przy tych samych argumentach wejściowych) bądź niedeterministyczne.

4.8. Transakcje Transakcja jest pojedynczym, niepodzielnym zadaniem wykonywanym na bazie danych. Jeżeli transakcja przebiegła pomyślnie, wszystkie modyfikacje danych przeprowadzone podczas jej trwania są zatwierdzone i trwale wprowadzone do bazy danych. Jeżeli podczas wykonywania transakcji wystąpi błąd i transakcja musi być przerwana lub wycofana, to wszelkie wprowadzone zmiany są usuwane. SQL Server wykorzystuje następujące typy transakcji: Transakcje zatwierdzane automatycznie (autocommit transactions) - każde indywidualne polecenie w języku

Transact-SQL jest transakcją. Transakcje sprecyzowane (explicit transactions) - każda transakcja rozpoczyna się od polecenia BEGIN

TRANSACTION i kończy poleceniem COMMIT lub ROLLBACK. Transakcje domyślne (implicit transactions) - nowa transakcja domyślnie rozpoczyna się po zakończeniu

poprzedniej transakcji, ale każda jest w sposób jawny zamykana poleceniem COMMIT lub ROLLBACK. Transakcje wsadowe (batch-scoped transactions) - występują w przypadku tzw. multiple active result sets

umożliwiając jednoczesne przetwarzanie kilku zadań wsadowych. W takim przypadku transakcja, która nie jest zamknięta przez COMMIT lub ROLLBACK wewnątrz wsadu, jest automatycznie wycofywana (ROLLBACK) po jego zakończeniu.

Transakcja, jako sekwencja operacji wykonywanych jako pojedyncze zadanie, musi posiadać cztery właściwości: niepodzielność, spójność, odrębność i trwałość (ACID - atomicity, consistency, isolation, durability). Atomicity albo wszystkie operacje w ramach transakcji są wykonane poprawnie, albo wszystkie zmiany

zostaną wycofane. Użytkownik nie musi się martwić o wyniki niedokończonych transakcji. Consistency po zakończeniu transakcji, baza danych musi się znajdować w stanie spójnym. Muszą być

zastosowane wszystkie zasady kontroli integralności danych. Wszystkie struktury wewnętrzne bazy, jak indeksy, listy muszą być zaktualizowane przy zakończeniu transakcji.

Isolation modyfikacje wprowadzone przez równolegle przeprowadzane transakcje muszą być od siebie odizolowane. Transakcja używa danych ze stanu, jaki był przed rozpoczęciem równoległej transakcji, albo stanu, który nastąpił po jej zakończeniu, nie używa stanów przejściowych. Nazywane jest to szeregowaniem transakcji (serializability) ponieważ skutkuje to wykonaniem ciągu transakcji na kolejnych stanach danych.

Durability kiedy transakcja jest ukończona, jej efekty są na trwale umieszczane w bazie danych. Modyfikacje powinny przetrwać nawet upadek systemu.

W celu zapewnienia tych warunków, DBMS powinien zapewnić możliwość: Blokowania (locking), dla zapewnienia odrębności transakcji. Notowania (logging), dla zapewnienia trwałości transakcji. Nawet jeśli sprzęt, system operacyjny lub DBMS

ulegną awarii, to dzięki dziennikom, po restarcie niezakończone transakcje ulegną automatycznemu wycofaniu do stanu sprzed awarii.

Zarządzania (transaction management), która wymusza niepodzielność i spójność transakcji. Po rozpoczęciu transakcji musi być ona zakończona pomyślnie, w przeciwnym przypadku DBMS ma wycofać wszystkie wprowadzone modyfikacje danych.

4.8.1. Poziomy izolacji transakcji w SQL-92 Chociaż szeregowanie transakcji daje pewność, że dane w bazie są aktualne przez cały czas, to wiele transakcji nie przez cały czas wymaga stosowania pełnej izolacji. Weźmy przykład: wielu autorów pracuje jednocześnie nad różnymi rozdziałami tej samej książki. Nowe rozdziały mogą być włączane do projektu w dowolnym momencie. Jednakże kiedy rozdział został już wykończony, autor nie może dokonywać żadnych zmian bez akceptacji wydawcy. W ten sposób wydawca może mieć pewność aktualności projektu książki, bez względu na dostarczanie nowych, niewykończonych rozdziałów. Wydawca ma dostęp zarówno do rozdziałów wykończonych, jak i do dosłanych ostatnio.

41

Poziom, na jakim transakcja jest przygotowana do przyjmowania niespójnych danych jest określany jako poziom izolacji. Poziom izolacji określa do jakiego stopnia transakcja musi być izolowana od innych transakcji. Mniejszy poziom izolacji zwiększa możliwości jednoczesnego wykonywania transakcji kosztem poprawności danych. I przeciwnie – wysoki poziom izolacji zapewnia dużą poprawność danych ale negatywnie wpływa na możliwości równoczesnego przetwarzania. Poziom izolacji wymagany przez aplikację określa zastosowane przez SQL Server zachowanie blokujące. SQL-92 definiuje następujące poziomy izolacji, wszystkie dostępne w SQL Server.

Read uncommitted (poziom najniższy, w którym transakcje są izolowane tylko na tyle, aby zapewnić, że dane fizycznie niepoprawne nie będą czytane).

Read committed (domyślny poziom w SQL). Repeatable read (domyślny w MS SQL) Serializable (poziom najwyższy, w którym transakcje są w pełni odizolowane od siebie).

Poziom serializable przeprowadzania transakcji zapewnia, że wszystkie równolegle występujące, nakładające się na siebie transakcje zostaną uszeregowane. Pośrednie poziomy izolacji zapewniają różne sposoby reakcji na zjawiska izolacji.

Poziom izolacji Lost update Dirty read Nonrepeatable read

Phantom rows

Read uncommitted

No Yes Yes Yes

Read committed No No Yes Yes

Repeatable read No No No Yes

Serializable No No No No

Transakcja musi być izolowana na poziomie repeatable read lub wyższym aby zapobiec utracie zmian, które mogą nastąpić, kiedy dwie transakcje jednocześnie sięgają do tego samego rekordu danych a następnie zmieniają ten rekord bazując na wartościach oryginalnych. Jeżeli te dwie transakcje modyfikują dane za pomocą pojedynczego polecenia UPDATE i nie opierają się na uprzednio uzyskanych wartościach, już na poziomie izolacji read committed nie powinna nastąpić utrata wprowadzonych zmian (lost update). Izolacja odbywa się przez ustawianie blokady pojedynczych rekordów bazy danych na poziomie odczytu (read lock), zapisu (write lock) i blokady zakresu danych (range lock).

Poziom izolacji Range lock Read lock Write lock

Read uncommitted

No No No

Read committed No No Yes

Repeatable read No Yes Yes

Serializable Yes Yes Yes

4.8.2. Polecenia związane z transakcjami

SET TRANSACTION ISOLATION LEVEL

Ustalenie poziomu izolacji. MS SQL Server wprowadza dodatkowy sposób izolacji (snapshot), w którym dane czytane przez dowolne polecenie są transakcyjnie spójną wersją danych występujących przy starcie transakcji. Izolacja sposobem snapshot nie używa blokady rekordów ale ich wersjonowania. Może współpracować z poziomem izolacji read committed po ustawieniu opcji READ_COMMITTED_SNAPSHOT.

SKŁADNIA

SET TRANSACTION ISOLATION LEVEL

{ READ UNCOMMITTED

| READ COMMITTED

| REPEATABLE READ

| SNAPSHOT

| SERIALIZABLE

42

}

[ ; ]

BEGIN TRANSACTION

Oznaczenie punktu startowego lokalnej transakcji. Licznik transakcji (@@TRANSCOUNT) jest zwiększany o 1. Na potrzeby transakcji zostają zablokowane różne zasoby bazy danych, w zależności od poziomu izolacji. W logu transakcyjnym notowane są jedynie te polecenia, które spowodowały modyfikację danych. Np. polecenie SELECT, użyte jedynie do wyświetlenia danych, może potrzebować blokady, ale nie musi być tam notowane. Klauzula WITH MARK służy do umieszczania nazwy transakcji i ewentualnych komentarzy w logu transakcyjnym.

SKŁADNIA

BEGIN {TRAN|TRANSACTION} [

{transaction_name|@tran_name_variable} [WITH MARK ['description']]

]

[ ; ]

COMMIT TRANSACTION

Oznaczenie punktu końcowego transakcji określonej lub domyślnej. Licznik transakcji (@@TRANSCOUNT) jest zmniejszany o 1. Jeśli się wyzeruje, to zmiany zostają utrwalone w bazie danych. Jeśli nie (transakcja zagnieżdżona), to transakcja pozostaje aktywna, a zasoby nie zostaną zwolnione. COMMIT [WORK] działa identycznie, ale nie dopuszcza nazwy transakcji.

SKŁADNIA

COMMIT {TRAN|TRANSACTION} [transaction_name|@tran_name_variable]

[ ; ]


Wycofanie transakcji określonej lub domyślnej do początku lub do punktu przywracania wewnątrz transakcji. Jeśli został określony punkt powrotu, to licznik transakcji (@@TRANSCOUNT) pozostaje bez zmian. W przeciwnym przypadku licznik transakcji zostaje wyzerowany, a zasoby zostają zwolnione. Przy transakcjach zagnieżdżonych następuje powrót do początku transakcji zewnętrznej. Podobne jest działanie ROLLBACK [WORK], ale nie dopuszcza nazwy transakcji ani punktu przywracania.

SKŁADNIA

ROLLBACK {TRAN|TRANSACTION}

[transaction_name|@tran_name_variable|savepoint_name|@savepoint_variable]

[ ; ]

SAVE TRANSACTION

Polecenie to ma zastosowanie w procedurach składowych. Użytkownik może określić punkt przywracania (lub znacznik) wewnątrz transakcji, tzn. punkt do którego transakcja może być wycofana, jeżeli jej część zostanie odrzucona na skutek zaistniałych warunków (np. wystąpienia błędu). Zasoby wykorzystywane przez transakcję będą nadal blokowane. Dalsze działanie może polegać na kontynuacji transakcji, aż do osiągnięcia COMMIT TRANSACTION, lub do wycofania do początku transakcji.

SKŁADNIA

SAVE {TRAN|TRANSACTION} {savepoint_name|@savepoint_variable}

[ ; ]

Przykład Przykład użycia punktu przywracania dla wycofania modyfikacji wprowadzonych przez procedurę Example, jeżeli aktywna transakcja wystartowała przed jej uruchomieniem.

USE School2

GO

IF OBJECT_ID('Example','P') IS NOT NULL

DROP PROCEDURE Example;

GO

-- Definicja procedury musi być na początku wsadu

CREATE PROCEDURE Example

@InputTeacherId CHAR(3)

AS

DECLARE @TranCnt INT;

SET @TranCnt = @@TRANCOUNT;

-- @TranCnt = 0 - nie było aktywnej transakcji

43

IF @TranCnt > 0

-- Była rozpoczęta transakcja, ustaw punkt powrotu

SAVE TRANSACTION SavePoint;

ELSE

-- Nie było transakcji - utwórz własną transakcję

BEGIN TRANSACTION;

BEGIN TRY

-- Blok wykonawczy procedury

INSERT INTO Teachers VALUES

('@@','Kowalski','Mgr inż.','Gdańsk','T4');

UPDATE Teachers

SET TNo = @InputTeacherId

WHERE TNo = '@@';

-- Jeśli nie było błędów i istnieje własna transakcja

IF @TranCnt = 0

-- Trzeba zatwierdzić własną transakcję

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

-- Blok obsługi błędów

IF @TranCnt = 0

-- Transakcja własna - wycofać zmiany


ELSE

-- Transakcja wystartowała przed procedurą, sprawdź jej stan

-- XACT_STATE() = 1 - transakcja może być zakończona

-- XACT_STATE() = 0 - nie ma aktywnej transakcji

-- XACT_STATE() =-1 - transakcja nie może być zakończona

IF XACT_STATE() <> -1

-- Transakcja jest ważna - cofnij do punktu powrotu

ROLLBACK TRANSACTION SavePoint;

-- Nie można było zakończyć - wyślij tylko komunikat o błędzie

DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE();

SELECT @ErrorSeverity = ERROR_SEVERITY();

SELECT @ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH

GO

-- Wykonanie

USE School2

GO

DECLARE @Tid AS CHAR(3);

SELECT @Tid = 'T6';

BEGIN TRANSACTION

INSERT INTO Teachers VALUES

(@Tid,'Kowalski','BSc','Gdańsk','T3');

SELECT * FROM Teachers;

-- Wywołanie procedury Example z argumentem @Tid = 'T6'’

EXEC Example @Tid; -- Będzie błąd w UPDATE, bo T6 już istnieje

COMMIT TRANSACTION

SELECT * FROM Teachers; -- Rekord T6 wprowadzony

DELETE Teachers WHERE TNo = @Tid

GO

4.8.3. Transakcje automatyczne Jest to domyślny sposób zarządzania transakcjami w SQL-Server. Każde polecenie jest zatwierdzone (commited), jeśli zakończyło się powodzeniem, lub wycofane (rolled back), jeśli wystąpił jakiś błąd. Połączenie z serwerem domyślnie pracuje w trybie autocommit. Po wystąpieniu polecenia BEGIN TRANSACTION, następuje przełączenie do trybu explicit transaction. Powrót do trybu autocommit następuje po zakończeniu transakcji (COMMIT

44

TRANSACTION lub ROLLBACK TRANSACTION) w trybie explicit. Przełączenie z autocommit do trybu implicit transation następuje po wystąpieniu polecenia SET IMPLICIT_TRANSACTIONS ON, a powrót do trybu autocommit po poleceniu SET IMPLICIT_TRANSACTIONS OFF. Jeśli w trybie autocommit wystąpi błąd kompilacji (compile error), to wycofywany jest cały wsad (do instrukcji GO), a nie pojedyncze polecenie SQL. Przy wystąpieniu błędu wykonania (runtime error) wycofywany jest jedynie wynik błędnego polecenia.

Przykłady Wystąpienie błędu kompilacji: IF OBJECT_ID ('TestBatch', 'U') IS NOT NULL

DROP TABLE TestBatch;

CREATE TABLE TestBatch (ID INT PRIMARY KEY, Data CHAR(3));

GO

INSERT INTO TestBatch VALUES (1, 'aaa');

INSERT INTO TestBatch VALUES (2, 'bbb');

INSERT INTO TestBatch VALUSE (3, 'ccc'); -- Błąd składni.

GO

SELECT * FROM TestBatch; -- Zwróci 0 wierszy.

GO

Wystąpienie błędu wykonania: IF OBJECT_ID ('TestBatch', 'U') IS NOT NULL


CREATE TABLE TestBatch (Kol1 INT PRIMARY KEY, Kol2 CHAR(3));

GO



INSERT INTO TestBatch VALUES (1, 'ccc'); -- Powtórzenie wartości klucza.

GO

SELECT * FROM TestBatch; -- Zwróci wiersz 1 i 2.

GO

DBMS w SQL-Server nie sprawdza nazw obiektów w trakcie kompilacji, zatem błąd w nazwie traktowany jest jako błąd wykonania: IF OBJECT_ID ('TestBatch', 'U') IS NOT NULL


CREATE TABLE TestBatch (Kol1 INT PRIMARY KEY, Kol2 CHAR(3));

GO



INSERT INTO TestBtch VALUES (3, 'ccc'); -- Błąd w nazwie tablicy

SELECT * FROM TestBatch; -- Zwróci wiersz 1 i 2.

GO

4.8.4. Transakcje sprecyzowane (explicite) Są to transakcje w sposób jawny zdefiniowane od początku do końca. Skrypty Transact_SQL wykorzystują w tym celu następujące polecenia: BEGIN TRANSACTION – używane do oznaczenia punktu startowego transakcji dla aktywnego połączenia. COMMIT TRANSACTION lub COMMIT WORK - używa się do pomyślnego zakończenia transakcji, jeżeli nie wystąpiły

żadne błędy. Wprowadzone zmiany zostają utrwalone w bazie danych. Zasoby używane przez transakcję zostają zwolnione.

ROLLBACK TRANSACTION lub ROLLBACK WORK - używa się do wycofania transakcji, w której wystąpiły błędy. Wszystkie dane zmodyfikowane podczas transakcji, powracają do stanu sprzed rozpoczęcia transakcji. Zasoby używane przez transakcję zostają zwolnione.

Przykłady Zadeklarowanie transakcji i nadanie jej nazwy "TeacherDelete" oraz potwierdzenie po pomyślnym wykonaniu. USE School2;

BEGIN TRANSACTION TeacherDelete

WITH MARK N'Deleting a Job Candidate';

GO

DELETE FROM Teachers

45

WHERE TNo = 'T7';

GO

COMMIT TRANSACTION TeacherDelete;

GO

Zadeklarowanie transakcji z nazwą w postaci zmiennej, wprowadzenie dwóch wierszy a następnie wycofanie tej transakcji. IF OBJECT_ID('ExplicitTran', 'U') IS NOT NULL

DROP TABLE ExplicitTran;

CREATE TABLE ExplicitTran (

ID INT PRIMARY KEY IDENTITY,

Data CHAR(3) NOT NULL);

GO

DECLARE @TranName VARCHAR(20);

SELECT @TranName = 'MyTransaction';

BEGIN TRAN @TranName;

-- Rozpoczęcie trybu EXPLICITE

INSERT INTO ExplicitTran VALUES('aaa');

SAVE TRAN S1 -- Wstawienie punktu powrotu

INSERT INTO ExplicitTran VALUES('bbb');

SELECT * FROM ExplicitTran; -- Dwa wiersze: (1,aaa)(2,bbb)

ROLLBACK TRAN S1; -- Wycofanie transakcji do punktu S1

INSERT INTO ExplicitTran VALUES('BBB');

COMMIT WORK -- Zakończenie transakcji

-- Wznowienie trybu AUTOCOMMIT

INSERT INTO ExplicitTran VALUES('AAA');

SELECT * FROM ExplicitTran -- Trzy wiersze: (1,aaa)(3,BBB)(4,AAA)

DROP TABLE ExplicitTran

4.8.5. Transakcje domyślne (implicite) Wprowadzenie połączenia w tryb domniemanych transakcji odbywa się za pomocą polecenia: SET IMPLICIT_TRANSACTIONS ON lub przez funkcje i metody API bazy danych. Kiedy połączenie pracuje w tym trybie, nie jest wymagana oddzielna sygnalizacja rozpoczęcia nowej transakcji. DBMS automatycznie rozpoczyna nową transakcję po wykryciu jednego z poleceń:

ALTER TABLE CREATE DELETE DROP FETCH GRANT INSERT OPEN REVOKE SELECT TRUNCATE TABLE UPDATE

Transakcja jest kontynuowana aż do wystąpienia polecenia COMMIT lub ROLLBACK. Odwołanie tego trybu (przejście do transakcji automatycznych) odbywa się za pomocą polecenia: SET IMPLICIT_TRANSACTIONS OFF

Przykład SET QUOTED_IDENTIFIER OFF;

GO

SET NOCOUNT OFF;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET IMPLICIT_TRANSACTIONS OFF;

GO

-- Tryb AUTOCOMMIT

USE School2

IF OBJECT_ID('ImplicitTran', 'U') IS NOT NULL

DROP TABLE ImplicitTran;

46

CREATE TABLE ImplicitTran (

Id INT IDENTITY PRIMARY KEY,

Data CHAR(5) NOT NULL);

GO

-- Początek trybu IMPLICITE

SET IMPLICIT_TRANSACTIONS ON;

-- Pierwszą transakcję rozpoczyna INSERT

INSERT INTO ImplicitTran VALUES ('aaa');

INSERT INTO ImplicitTran VALUES ('bbb');

SELECT * FROM ImplicitTran;

ROLLBACK TRANSACTION -- Koniec transakcji

-- Drugą transakcję rozpoczyna SELECT


INSERT INTO ImplicitTran VALUES ('BBB');

INSERT INTO ImplicitTran VALUES ('AAA');

COMMIT TRANSACTION -- Koniec transakcji (tu zbędny)

-- Koniec trybu IMPLICITE (i jednocześnie koniec transakcji)

SET IMPLICIT_TRANSACTIONS OFF;

-- Początek trybu AUTOCOMMIT – każde polecenie jest transakcją


GO

BAZY DANYCH - pg.edu.pl · MS SQL Server pozwala na utworzenie tablic tymczasowych, które są...

Documents

Transcript of BAZY DANYCH - pg.edu.pl · MS SQL Server pozwala na utworzenie tablic tymczasowych, które są...