Akademia SQL - Część 11: Typy i parametry tabelaryczne
 |
Zawartość strony
 | Typy i parametry tabelaryczne – informacje podstawowe |
| Typy i parametry tabelaryczne – laboratorium |
| Typy i parametry tabelaryczne – referencje |
Typy i parametry tabelaryczne – informacje podstawowe
Parametry tabelaryczne są nowymi parametrami w serwerze SQL 2008, które są deklarowane i używane na bazie typów tabelarycznych. Typy tabelaryczne są z kolei nowymi typami, które może definiować użytkownik.
Parametry typu tabelarycznego pozwalają przesłać wiele rekordów w postaci tabeli do procedury składowanej lub funkcji bez konieczności przesyłania do nich wielu parametrów, tworzenia tabeli tymczasowej czy wykonywania konwersji z typu XML.
Procedury składowane, które wstawiają podczas jednego uruchomienia tylko jeden wiersz do tabeli są nieefektywne, ponieważ do wstawienia kilku – kilkudziesięciu nowych informacji muszą się uruchomić wielokrotnie. Efektem tego jest wzmożony ruch pomiędzy serwerem a aplikacją kliencką. Poza tym, każda zmiana struktury tabeli powodowałaby konieczność dokonania zmian w aplikacji klienckiej, która musiałaby (w większości przypadków) wskazywać odpowiednią liczbę parametrów oczekiwaną przez procedurę składowaną lub funkcję.
Inna metoda wykorzystywana w podobnej sytuacji polegała na utworzeniu tablicy tymczasowej, zapisaniu do niej wszystkich koniecznych informacji i przesłaniu do serwera SQL. Również i to podejście ma wiele wad:
1. | Tablica tymczasowa jest tworzona po stronie aplikacji klienckiej w trakcie wykonywania programu. Serwer baz danych nie może uruchomić procedury składowanej, dopóki aplikacja kliencka nie utworzy tablicy tymczasowej, ponieważ wtedy jej wykonanie zakończyłoby się błędem. |
2. | Tablice tymczasowe po stronie serwera baz danych tworzone są bazie danych tempdb i są podatne na zakleszczanie czy blokowanie. |
3. | Tablice tymczasowe muszą być usuwane po wykonaniu wszystkich operacji w procedurze składowanej. |
4. | Użycie tabel tymczasowych w procedurach składowanych powoduje konieczność częstszej ich rekompilacji, co przekłada się bezpośrednio na zmniejszenie wydajności serwera. |
Metoda polegająca na utworzeniu zmiennej typu XML i zapisaniu w niej całej informacji jest najbardziej elastyczna ze wszystkich omówionych dotychczas istniejących metod. Użycie je powoduje konieczność wykonania konwersji dokumentu XML tak, aby można go było użyć do wykonywania operacji DML z tablicą, do której należy zapisać informacje.
Parametry typu tablicowego wyróżniają się tym spośród omówionych metod, że są najbardziej elastyczne w użyciu. Składają się na to następujące własności parametrów tablicowych, które najważniejsze wymienione są poniżej:
1. | Ich zasięg ogranicza się do procedury czy funkcji, w której są wywoływane, a po opuszczeniu której są automatyczne czyszczone. |
2. | Nie powodują zakleszczeń podczas inicjalizacji, czyli w trakcie wypełniania danymi. |
3. | Nie powodują rekompilacji procedur składowanych czy funkcji. |
4. | Pozwalają określić porządek sortowania czy unikalne klucze. |
Typy i parametry tabelaryczne – laboratorium
Celem laboratorium jest praktyczne zapoznanie się z typami i parametrami tablicowymi. W pierwszym zadaniu pokazane zostaną metody znane ze starszych wersji systemu, w drugim zaprezentowane zostaną typy i parametry tablicowe oraz sposoby ich użycia.
Zadanie 1
1. Uruchom SQL Server Management Studio (SSMS) i połącz się z serwerem EVALUATION używając uwierzytelnienia WINDOWS.
2. Pierwszy przykład tego zadania będzie pokazywał, jak za pomocą wielokrotnej procedury składowanej dodawać informacje o nowych pracownikach. Jedno uruchomienie procedury powoduje dodanie tylko jednego pracownika do tabeli Pracownicy. Dodatkowo, po zmianie struktury tej tabeli procedura składowana musi zostać przebudowana tak, aby uwzględniała nowe informacje.
3. Otwórz nowe okno zapytania (skrót klawiszowy CTRL+N) i wpisz poniższy kod, który utworzy testową bazę danych i tabelę Pracownicy:
CREATE DATABASE AkademiaSQL2008 GO USE AkademiaSQL2008 GO CREATE TABLE dbo.Pracownik ( ID int NOT NULL ,Nazwisko nvarchar(100) NOT NULL ,Email nvarchar(100) NOT NULL ); GO
4. Wykonaj procedurę składowaną DodajPracownika, która wstawi informacje do tabeli Pracownik:
CREATE PROCEDURE DodajPracownika ( @ID int ,@Nazwisko nvarchar(100) ,@Email nvarchar(100) ) AS BEGIN INSERT INTO dbo.Pracownik VALUES( @ID,@Nazwisko,@Email) END; GO
5. Dodaj kilku nowych pracowników używając wykonanej wcześniej procedury i sprawdź dane zapisane w tabeli Pracownik. Zwróć uwagę, że dodanie nowego pracownika wiąże się z wykonanie procedury DodajPracownika
EXEC DodajPracownika 1,'Jan Kowalski','Jan.K@adventureworks.com' EXEC DodajPracownika 2,'Zenon Nowak', 'Zenon.N@adventureworks.com' EXEC DodajPracownika 3,'Piotr Wajda','Piotr.W@contoso.com' SELECT * FROM Pracownik; GO ID Nazwisko Email ----------- ------------- ------------------------- 1 Jan Kowalski Jan.K@adventureworks.com 2 Zenon Nowak Zenon.N@adventureworks.com 3 Piotr Wajda Piotr.W@contoso.com (3 row(s) affected)
6. Drugi przykład pokazuje, w jaki sposób można dodawać nowych pracowników przy pomocy tabeli tymczasowej. Najpierw należy wykonać nową procedurę składowaną:
CREATE PROCEDURE DodajPracownika_Tab AS BEGIN INSERT INTO dbo.Pracownik SELECT * FROM #PracownikTempTable END; GO
7. Tabela tymczasowa jest wykonywana w aplikacji klienckiej, w trakcie jej działania:
CREATE TABLE dbo.#PracownikTempTable ( ID int NOT NULL ,Nazwisko nvarchar(100) NOT NULL ,Email nvarchar(100) NOT NULL ); GO
Uwaga: Struktura tabeli nie jest znana w serwerze baz danych dopóki nie zostanie utworzona. Wykonanie procedury składowanej DodajPracownika_Tab zanim struktura tabeli tymczasowej #PracownikTempTable będzie znana w serwerze baz danych spowoduje błąd, jak pokazaliśmy poniżej:
Msg 208, Level 16, State 0, Procedure DodajPracownika_Tab, Line 4 Invalid object name '#PracownikTempTable'.
8. Dodaj nowych pracowników do tabeli tymczasowej:
INSERT INTO #PracownikTempTable VALUES(10,'Jan Kowalski','Jan.K@adventureworks.com') ,(20,'Zenon Nowak', 'Zenon.N@adventureworks.com') ,(30,'Piotr Wajda','Piotr.W@contoso.com')
9. Uruchom procedurę składowaną DodajPracownika_Tab, która wstawi informacje do tabeli źródłowej. Sprawdź uzyskany wynik. Informacje zapisane w tablicy tymczasowej miały identyfikatory 10, 20 oraz 30, a więc tablica Pracownik będzie miała 6 wpisów:
EXEC DodajPracownika_Tab; GO SELECT * FROM Pracownik; GO ID Nazwisko Email ----------- ------------- --------------- 1 Jan Kowalski Jan.K@adventureworks.com 2 Zenon Nowak Zenon.N@adventureworks.com 3 Piotr Wajda Piotr.W@contoso.com 10 Jan Kowalski Jan.K@adventureworks.com 20 Zenon Nowak Zenon.N@adventureworks.com 30 Piotr Wajda Piotr.W@contoso.com (6 row(s) affected)
10. Ostatnią metodą przedstawioną w zadaniu nr 1 jest przechowanie informacji o nowych pracownikach w zmiennej typu XML i odpowiednim jej przetworzeniu w procedurze składowanej wpisującej dane do tablicy źródłowej. Utwórz nową procedurę składowaną:
CREATE PROCEDURE DodajPracownika_XML
(
@xml_doc XML
)
AS
BEGIN
INSERT INTO dbo.Pracownik
SELECT C.value('@ID','int') as ID
,C.value('@Nazwisko','nvarchar(100)') as Nazwisko
,C.value('@Email','nvarchar(100)') as Email
FROM @xml_doc.nodes('//*') T(C)
END;
GO11. Do zainicjalizowania zmiennej XML posłużymy się następującym kodem T-SQL:
DECLARE @xml_tab XML SELECT @xml_tab = ( SELECT * FROM ( SELECT 100 as ID,'Jan Kowalski' as Nazwisko,'Jan.K@adventureworks.com' as Email UNION SELECT 200,'Zenon Nowak', 'Zenon.N@adventureworks.com' UNION SELECT 300,'Piotr Wajda','Piotr.W@contoso.com' )D FOR XML RAW );
12. Wykonaj procedurę składowaną podając jako parametr zmienną typu XML zadeklarowaną w punkcie powyżej i sprawdź uzyskany wynik. Informacje zapisane w zmiennej @xml_tab miały identyfikatory 100, 200 oraz 300, a więc tablica Pracownik będzie miała 9 wpisów:
EXEC DodajPracownika_XML @xml_tab; GO SELECT * FROM Pracownik; GO ID Nazwisko Email ----------- --------------------------------------------------- 1 Jan Kowalski Jan.K@adventureworks.com 2 Zenon Nowak Zenon.N@adventureworks.com 3 Piotr Wajda Piotr.W@contoso.com 10 Jan Kowalski Jan.K@adventureworks.com 20 Zenon Nowak Zenon.N@adventureworks.com 30 Piotr Wajda Piotr.W@contoso.com 100 Jan Kowalski Jan.K@adventureworks.com 200 Zenon Nowak Zenon.N@adventureworks.com 300 Piotr Wajda Piotr.W@contoso.com (9 row(s) affected)
Zadanie 2
Celem zadania jest zaprezentowanie w jaki sposób należy tworzyć i używać typów oraz parametrów tablicowych.
1. Uruchom SQL Server Management Studio (SSMS) i połącz się z serwerem EVALUATION używając uwierzytelnienia WINDOWS.
2. Otwórz nowe okno zapytania (skrót klawiszowy CTRL+N) i wpisz poniższy kod, który utworzy typ tablicowy, który będzie następnie wykorzystany jako parametr w procedurze składowanej.
CREATE TYPE Pracownik_TypTablicowy AS TABLE ( ID int NOT NULL ,Nazwisko nvarchar(100) NOT NULL ,Email nvarchar(100) NOT NULL ); GO
3. Typ tablicowy zostaje na stałe zapisany w bazie danych AkademiaSQL2008 i jest dostępny w konsoli SSMS, jak pokazaliśmy na rysunku poniżej. W konsoli SSMS można również zaznaczyć sekcję User-Defined Table Type i z menu kontekstowego wybrać opcję New User-Defined Table Type, co pozwoli na dodanie typu tablicowego za pomocą kreatora:
4. Utwórz procedurę składowaną DodajPracownika_2008, która wstawi informacje o pracowniku do tabeli Pracownik. Procedura ta przyjmuje jeden parametr typu tablicowego, który musi być zadeklarowany jako READONLY:
CREATE PROCEDURE DodajPracownika_2008(@Pracownik Pracownik_TypTablicowy READONLY) AS BEGIN INSERT INTO dbo.Pracownik SELECT * FROM @Pracownik END; GO
5. Zadeklaruj oraz wypełnij zmienną typu tablicowego oraz uruchom procedurę składowana DodajPracownika_2008:
DECLARE @Pracownik Pracownik_TypTablicowy INSERT INTO @Pracownik VALUES(1000,'Jan Kowalski','Jan.K@adventureworks.com') ,(2000,'Zenon Nowak', 'Zenon.N@adventureworks.com') ,(3000,'Piotr Wajda','Piotr.W@contoso.com'); EXECUTE DodajPracownika_2008 @Pracownik; GO
6. Sprawdź uzyskany wynik. Informacje zapisane w zmiennej typu tabelarycznego miały identyfikatory 1000, 2000 oraz 3000, a wiec tablica Pracownik będzie miała teraz 12 wpisów:
SELECT * FROM Pracownik; GO ID Nazwisko Email ----------- ------------------- ------- 1 Jan Kowalski Jan.K@adventureworks.com 2 Zenon Nowak Zenon.N@adventureworks.com 3 Piotr Wajda Piotr.W@contoso.com 10 Jan Kowalski Jan.K@adventureworks.com 20 Zenon Nowak Zenon.N@adventureworks.com 30 Piotr Wajda Piotr.W@contoso.com 100 Jan Kowalski Jan.K@adventureworks.com 200 Zenon Nowak Zenon.N@adventureworks.com 300 Piotr Wajda Piotr.W@contoso.com 1000 Jan Kowalski Jan.K@adventureworks.com 2000 Zenon Nowak Zenon.N@adventureworks.com 3000 Piotr Wajda Piotr.W@contoso.com (12 row(s) affected)
Typy i parametry tabelaryczne – referencje
Dodatkowe informacje na temat parametrów tabelarycznych można znaleźć w Internecie:
[1] Witryna SQL Server 2008 Jumpstart
[2] Pliki pomocy Books Online na temat: Table-Valued Parameters
Quiz
| • |
 | Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET) |