Procedury składowane w T-SQL - tworzenie, modyfikacja, usuwanie. Tworzenie procedur składowanych na serwerze Microsoft SQL Server Sql tworzy procedurę składowaną

Procedura składowana to specjalny typ pakietu instrukcji Transact-SQL utworzony przy użyciu języka SQL i rozszerzeń proceduralnych. Główna różnica między pakietem a procedurą składowaną polega na tym, że ta ostatnia jest przechowywana jako obiekt bazy danych. Innymi słowy, procedury składowane są przechowywane po stronie serwera w celu poprawy wydajności i spójności powtarzalnych zadań.

Aparat bazy danych obsługuje procedury składowane i procedury systemowe. Procedury składowane tworzy się w taki sam sposób, jak wszystkie inne obiekty bazy danych, tj. przy użyciu języka DDL. Procedury systemowe są dostarczane przez Database Engine i mogą być używane do uzyskiwania dostępu i modyfikowania informacji w katalogu systemowym.

Tworząc procedurę składowaną, można zdefiniować opcjonalną listę parametrów. W ten sposób procedura za każdym razem będzie przyjmować odpowiednie argumenty. Procedury składowane mogą zwracać wartość zawierającą informacje zdefiniowane przez użytkownika lub, w przypadku błędu, odpowiedni komunikat o błędzie.

Procedura składowana jest prekompilowana przed zapisaniem jej jako obiektu w bazie danych. Prekompilowana postać procedury jest przechowywana w bazie danych i używana przy każdym wywołaniu. Ta właściwość procedur składowanych zapewnia ważną korzyść polegającą na eliminacji (w prawie wszystkich przypadkach) powtarzających się kompilacji procedur i osiągnięciu odpowiedniej poprawy wydajności. Ta właściwość procedur składowanych wpływa także pozytywnie na ilość danych wymienianych pomiędzy systemem bazodanowym a aplikacjami. W szczególności wywołanie procedury składowanej o rozmiarze kilku tysięcy bajtów może wymagać mniej niż 50 bajtów. Gdy wielu użytkowników wykonuje powtarzalne zadania przy użyciu procedur przechowywanych, skumulowany efekt tych oszczędności może być dość znaczący.

Procedury składowane mogą być również wykorzystywane do następujących celów:

aby utworzyć dziennik działań z tabelami bazy danych.

Korzystanie z procedur składowanych zapewnia poziom kontroli bezpieczeństwa znacznie wykraczający poza zabezpieczenia zapewniane przez instrukcje GRANT i REVOKE, które przyznają użytkownikom różne uprawnienia dostępu. Jest to możliwe, ponieważ autoryzacja do wykonania procedury składowanej jest niezależna od autoryzacji do modyfikowania obiektów zawartych w procedurze składowanej, jak opisano w następnej sekcji.

Procedury składowane tworzące dzienniki operacji zapisu i/lub odczytu tabeli zapewniają dodatkową opcję bezpieczeństwa bazy danych. Korzystając z takich procedur, administrator bazy danych może monitorować modyfikacje wprowadzane w bazie danych przez użytkowników lub aplikacje.

Tworzenie i wykonywanie procedur składowanych

Procedury składowane są tworzone przy użyciu instrukcji STWÓRZ PROCEDURĘ, który ma następującą składnię:

CREATE PROC nazwa_proc [((@param1) typ1 [ ZMIENNY] [= domyślny1] )] (, ...) AS partia | NAZWA ZEWNĘTRZNA nazwa_metody Konwencje składni

Parametr nazwa_schematu określa nazwę schematu przypisaną przez właściciela utworzonej procedury składowanej. Parametr proc_name określa nazwę procedury składowanej. Parametr @param1 jest parametrem procedury (argumentem formalnym), którego typ danych jest określony przez parametr type1. Parametry procedury są lokalne w ramach procedury, tak jak zmienne lokalne są lokalne w pakiecie. Parametry procedury to wartości, które są przekazywane przez osobę wywołującą do procedury w celu jej wykorzystania. Parametr default1 określa wartość domyślną odpowiedniego parametru procedury. (Wartość domyślna może również mieć wartość NULL.)

opcja WYJŚCIE wskazuje, że parametr procedury jest parametrem zwracanym i może zostać użyty do zwrócenia wartości z procedury składowanej do wywołującej procedury lub systemu.

Jak wspomniano wcześniej, prekompilowana forma procedury jest przechowywana w bazie danych i używana przy każdym wywołaniu. Jeśli z jakiegoś powodu procedura składowana musi być kompilowana przy każdym wywołaniu, podczas deklarowania procedury użyj Z opcją RECOMPILE. Użycie opcji WITH RECOMPILE neguje jedną z najważniejszych zalet procedur składowanych: poprawę wydajności dzięki pojedynczej kompilacji. Dlatego opcji WITH RECOMPILE należy używać tylko wtedy, gdy obiekty bazy danych używane przez procedurę składowaną są często modyfikowane.

Klauzula EXECUTE AS definiuje kontekst zabezpieczeń, w którym procedura składowana powinna zostać wykonana po jej wywołaniu. Ustawiając ten kontekst, aparat bazy danych może kontrolować wybór kont użytkowników w celu sprawdzenia uprawnień dostępu do obiektów, do których odwołuje się procedura składowana.

Domyślnie instrukcji CREATE PROCEDURE mogą używać tylko członkowie stałej roli serwera sysadmin oraz stałych ról bazy danych db_owner lub db_ddladmin. Jednak członkowie tych ról mogą przypisać to prawo innym użytkownikom za pomocą instrukcji PROCEDURA TWORZENIA GRANTÓW.

Poniższy przykład pokazuje, jak utworzyć prostą procedurę składowaną do pracy z tabelą projektu:

UŻYJ próbkiDb; PRZEJDŹ, UTWÓRZ PROCEDUrę Zwiększ Budżet (@percent INT=5) JAK AKTUALIZACJA SET projektu Budżet = Budżet + Budżet * @percent/100;

Jak wspomniano wcześniej, aby oddzielić dwa pakiety, użyj Instrukcje GO. Instrukcji CREATE PROCEDURE nie można łączyć z innymi instrukcjami języka Transact-SQL w tej samej partii. Procedura składowana GrowBudget zwiększa budżety wszystkich projektów o określoną wartość procentową, określoną przez parametr @percent. Procedura definiuje również domyślną wartość procentową (5), która jest używana, jeśli ten argument nie jest obecny podczas wykonywania procedury.

Procedury składowane mogą uzyskać dostęp do tabel, które nie istnieją. Ta właściwość umożliwia debugowanie kodu procedury bez uprzedniego tworzenia odpowiednich tabel lub nawet łączenia się z serwerem docelowym.

W przeciwieństwie do podstawowych procedur składowanych, które zawsze są przechowywane w bieżącej bazie danych, możliwe jest tworzenie tymczasowych procedur składowanych, które są zawsze przechowywane w tymczasowej systemowej bazie danych tempdb. Jednym z powodów tworzenia tymczasowych procedur składowanych może być uniknięcie wielokrotnego wykonywania określonej grupy instrukcji podczas łączenia się z bazą danych. Można tworzyć lokalne lub globalne procedury tymczasowe. Aby to zrobić, nazwa procedury lokalnej jest określona pojedynczym znakiem # (#nazwa_proc), a nazwa procedury globalnej jest określona podwójnym znakiem (##nazwa_proc).

Lokalna tymczasowa procedura składowana może zostać wykonana tylko przez użytkownika, który ją utworzył, i tylko wtedy, gdy jest połączony z bazą danych, w której została utworzona. Globalna procedura tymczasowa może być wykonana przez wszystkich użytkowników, ale tylko do momentu zakończenia ostatniego połączenia, na którym jest ona wykonywana (zwykle jest to połączenie twórcy procedury).

Cykl życia procedury składowanej składa się z dwóch etapów: jej utworzenia i wykonania. Każda procedura jest tworzona raz i wykonywana wielokrotnie. Procedura składowana jest wykonywana przy użyciu WYKONAJ instrukcje użytkownik będący właścicielem procedury lub mający uprawnienia EXECUTE umożliwiające dostęp do tej procedury. Instrukcja EXECUTE ma następującą składnię:

[] [@return_status =] (nazwa_proc | @nazwa_proc_zmienna) ([[@parametr1 =] wartość | [@parametr1=] @zmienna ] | DEFAULT).. Konwencje składniowe

Z wyjątkiem parametru return_status, wszystkie parametry instrukcji EXECUTE mają to samo znaczenie logiczne, co te same parametry instrukcji CREATE PROCEDURE. Parametr return_status określa zmienną całkowitą, która przechowuje status powrotu procedury. Wartość można przypisać do parametru za pomocą stałej (wartości) lub zmiennej lokalnej (@zmiennej). Kolejność wartości nazwanych parametrów nie jest istotna, jednak wartości nienazwanych parametrów należy podać w takiej kolejności, w jakiej są zdefiniowane w instrukcji CREATE PROCEDURE.

Klauzula DOMYŚLNA udostępnia domyślną wartość parametru procedury, która została określona w definicji procedury. Gdy procedura oczekuje wartości parametru, dla którego nie zdefiniowano wartości domyślnej, a parametru brakuje lub podano słowo kluczowe DEFAULT, pojawia się błąd.

Jeśli instrukcja EXECUTE jest pierwszą instrukcją wsadową, słowo kluczowe EXECUTE można pominąć. Jednak bezpieczniej jest umieścić to słowo w każdym pakiecie. Użycie instrukcji EXECUTE pokazano w poniższym przykładzie:

UŻYJ próbkiDb; WYKONAJ Zwiększenie budżetu 10;

Instrukcja EXECUTE w tym przykładzie wykonuje procedurę składowaną ZwiększBudżet, która zwiększa budżet wszystkich projektów o 10%.

Poniższy przykład pokazuje, jak utworzyć procedurę składowaną do przetwarzania danych w tabelach Employee i Works_on:

Przykładowa procedura ModifyEmpId ilustruje użycie procedur przechowywanych w ramach procesu utrzymywania integralności referencyjnej (w tym przypadku między tabelami Employee i Works_on). Podobną procedurę składowaną można zastosować w definicji wyzwalacza, która faktycznie zapewnia integralność referencyjną.

Poniższy przykład ilustruje użycie klauzuli OUTPUT w procedurze składowanej:

Tę procedurę składowaną można wykonać, korzystając z następujących instrukcji:

DECLARE @quantityDeleteEmployee INT; WYKONAJ DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee WYJŚCIE; PRINT N"Usunięci pracownicy: " + konwersja(nvarchar(30), @quantityDeleteEmployee);

Procedura ta zlicza liczbę projektów, nad którymi pracuje pracownik o numerze personalnym @empId i przypisuje wynikową wartość do parametru ©counter. Po usunięciu wszystkich wierszy dla danego numeru personelu z tabel Employee i Works_on obliczona wartość jest przypisana do zmiennej @quantityDeleteEmployee.

Wartość parametru jest zwracana do procedury wywołującej tylko wtedy, gdy określono opcję OUTPUT. W powyższym przykładzie procedura DeleteEmployee przekazuje parametr @counter do procedury wywołującej, w związku z czym procedura składowana zwraca wartość do systemu. Dlatego parametr @counter musi być podany zarówno w opcji OUTPUT podczas deklarowania procedury, jak i w instrukcji EXECUTE podczas jej wywoływania.

Klauzula WITH RESULTS SETS instrukcji EXECUTE

W SQL Server 2012 w przypadku instrukcji EXECUTE należy wprowadzić Klauzula With RESULTS SETS, dzięki któremu po spełnieniu określonych warunków można zmienić formę zestawu wyników procedury składowanej.

Poniższe dwa przykłady pomogą wyjaśnić to zdanie. Pierwszy przykład jest przykładem wprowadzającym, który pokazuje, jak może wyglądać wynik w przypadku pominięcia klauzuli WITH RESULTS SETS:

Procedura EmployeesInDept jest prostą procedurą wyświetlającą numery personelu i nazwiska wszystkich pracowników pracujących w konkretnym dziale. Numer działu jest parametrem procedury i należy go podać podczas wywoływania. Wykonanie tej procedury tworzy tabelę zawierającą dwie kolumny, których nagłówki odpowiadają nazwom odpowiednich kolumn w tabeli bazy danych, tj. Identyfikator i nazwisko. Aby zmienić nagłówki kolumn wynikowych (jak również ich typ danych), SQL Server 2012 używa nowej klauzuli WITH RESULTS SETS. Zastosowanie tego zdania pokazano na poniższym przykładzie:

UŻYJ próbkiDb; EXEC EmployeesInDept "d1" Z ZESTAWAMI WYNIKÓW ((INT NIE NULL, [Nazwisko] CHAR(20) NIE NULL));

Wynik wykonania procedury składowanej wywołanej w ten sposób będzie następujący:

Jak widać, uruchomienie procedury składowanej przy użyciu klauzuli WITH RESULT SETS w instrukcji EXECUTE umożliwia zmianę nazw i typów danych kolumn w zestawie wyników generowanym przez procedurę. Zatem ta nowa funkcjonalność zapewnia większą elastyczność w wykonywaniu procedur składowanych i umieszczaniu ich wyników w nowej tabeli.

Zmiana struktury procedur składowanych

Aparat bazy danych również obsługuje tę instrukcję ZMIEŃ PROCEDURA modyfikować strukturę procedur przechowywanych. Instrukcja ALTER PROCEDURE jest zwykle używana do zmiany instrukcji języka Transact-SQL w ramach procedury. Wszystkie parametry instrukcji ALTER PROCEDURE mają takie samo znaczenie jak te same parametry instrukcji CREATE PROCEDURE. Głównym celem użycia tej instrukcji jest uniknięcie zastąpienia istniejących praw procedur składowanych.

Silnik bazy danych obsługuje Typ danych CURSOR. Ten typ danych służy do deklarowania kursorów w procedurach przechowywanych. Kursor to konstrukcja programistyczna używana do przechowywania wyników zapytania (zwykle zestawu wierszy) i umożliwiania użytkownikom wyświetlania tych wyników wiersz po wierszu.

Aby usunąć jedną lub grupę procedur składowanych, użyj Instrukcja PROCEDURY UPUSZCZANIA. Tylko właściciel lub członkowie stałych ról db_owner i sysadmin mogą usunąć procedurę składowaną.

Procedury składowane i środowisko uruchomieniowe języka wspólnego

SQL Server obsługuje środowisko uruchomieniowe języka wspólnego (CLR), które umożliwia tworzenie różnych obiektów bazy danych (procedur składowanych, funkcji zdefiniowanych przez użytkownika, wyzwalaczy, agregacji zdefiniowanych przez użytkownika i niestandardowych typów danych) przy użyciu języków C# i Visual Basic. Środowisko CLR umożliwia także wykonywanie tych obiektów przy użyciu wspólnego systemu wykonawczego.

Środowisko wykonawcze języka wspólnego można włączać i wyłączać za pomocą tej opcji clr_włączone procedura systemowa sp_configure, który jest uruchamiany do wykonania na podstawie instrukcji SKONFIGURUJ PONOWNIE. Poniższy przykład pokazuje, jak można użyć procedury systemowej sp_configure w celu włączenia środowiska CLR:

UŻYJ próbkiDb; EXEC sp_configure "clr_enabled",1 PONOWNA KONFIGURACJA

Aby utworzyć, skompilować i zapisać procedurę przy użyciu środowiska CLR, należy wykonać następującą sekwencję kroków w podanej kolejności:

Utwórz procedurę składowaną w języku C# lub Visual Basic, a następnie skompiluj ją przy użyciu odpowiedniego kompilatora.

Korzystanie z instrukcji UTWÓRZ MONTAŻ, utwórz odpowiedni plik wykonywalny.

Wykonaj procedurę za pomocą instrukcji EXECUTE.

Poniższy rysunek przedstawia graficzny diagram wcześniej opisanych kroków. Poniżej znajduje się bardziej szczegółowy opis tego procesu.

Najpierw utwórz wymagany program w środowisku programistycznym, takim jak Visual Studio. Skompiluj gotowy program do kodu wynikowego przy użyciu kompilatora C# lub Visual Basic. Kod ten jest przechowywany w pliku biblioteki dołączanej dynamicznie (.dll), który służy jako źródło instrukcji CREATE ASSEMBLY, która tworzy pośredni kod wykonywalny. Następnie wydaj instrukcję CREATE PROCEDURE, aby zapisać wykonywalny kod jako obiekt bazy danych. Na koniec uruchom procedurę, używając znanej instrukcji EXECUTE.

Poniższy przykład pokazuje kod źródłowy procedury składowanej w języku C#:

Korzystanie z System.Data.SqlClient; przy użyciu Microsoft.SqlServer.Server; publiczna klasa częściowa StoredProcedures ( public static int CountEmployees() ( int rows; SqlConnection połączenie = nowe SqlConnection("Context Connection=true"); połączenie.Open(); SqlCommand cmd = połączenie.CreateCommand(); cmd.CommandText = "wybierz count(*) jako „Liczba pracowników” „ + „od pracownika”; wiersze = (int)cmd.ExecuteScalar(); połączenie.Close(); zwróć wiersze; ) )

Ta procedura implementuje zapytanie liczące liczbę wierszy w tabeli Pracownik. Używając dyrektyw na początku programu, określ przestrzenie nazw wymagane do wykonania programu. Użycie tych dyrektyw umożliwia określenie nazw klas w kodzie źródłowym bez jawnego określania odpowiednich przestrzeni nazw. Następnie definiowana jest klasa StoredProcedures, dla której Atrybut procedury Sql, który informuje kompilator, że ta klasa jest procedurą składowaną. Metoda CountEmployees() jest zdefiniowana w kodzie klasy. Połączenie z systemem bazodanowym nawiązywane jest poprzez instancję klasy Połączenie SQL. Aby otworzyć połączenie, używana jest metoda Open() tej instancji. A Metoda CreateCommand(). umożliwia dostęp do instancji klasy Polecenie SQL, do którego przekazywane jest wymagane polecenie SQL.

W poniższym fragmencie kodu:

Cmd.CommandText = "wybierz liczbę(*) jako "Liczbę pracowników" " + "od pracownika";

używa instrukcji SELECT do zliczenia liczby wierszy w tabeli Pracownik i wyświetlenia wyniku. Tekst polecenia określa się poprzez ustawienie właściwości CommandText zmiennej cmd na instancję zwróconą przez metodę CreateCommand(). Dalej to się nazywa Metoda ExecuteScalar(). Instancja SqlCommand. Ta metoda zwraca wartość skalarną, która jest konwertowana na typ danych całkowitych i przypisana do zmiennej rows.

Możesz teraz skompilować ten kod przy użyciu programu Visual Studio. Dodałem tę klasę do projektu o nazwie CLRStoredProcedures, więc Visual Studio skompiluje zestaw o tej samej nazwie z rozszerzeniem *.dll. Poniższy przykład pokazuje kolejny krok w tworzeniu procedury składowanej: utworzenie kodu wykonywalnego. Zanim uruchomisz kod w tym przykładzie, musisz znać lokalizację skompilowanego pliku dll (zwykle znajduje się on w folderze Debug projektu).

UŻYJ próbkiDb; Przejdź, utwórz zestaw CLRStoredProcedures z „D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll” Z PERMISSION_SET = SAFE

Instrukcja CREATE ASSEMBLY pobiera kod zarządzany jako dane wejściowe i tworzy odpowiedni obiekt, na którym można tworzyć procedury składowane CLR, funkcje zdefiniowane przez użytkownika i wyzwalacze. Ta instrukcja ma następującą składnię:

CREATE ASSEMBLY nazwa_zespołu [ AUTORYZACJA nazwa_właściciela ] FROM (plik_dll) Konwencje składni

Parametr nazwa_zespołu określa nazwę zestawu. Opcjonalna klauzula AUTHORIZATION określa nazwę roli właściciela tego zestawu. Klauzula FROM określa ścieżkę, w której znajduje się zestaw do załadowania.

Z klauzulą PERMISSION_SET jest bardzo ważną klauzulą instrukcji CREATE ASSEMBLY i zawsze należy ją określić. Definiuje zestaw uprawnień przyznanych kodowi asemblera. Zestaw uprawnień SAFE jest najbardziej restrykcyjny. Kod asemblera posiadający te uprawnienia nie może uzyskać dostępu do zewnętrznych zasobów systemowych, takich jak pliki. Zestaw uprawnień EXTERNAL_ACCESS umożliwia kodowi asemblera dostęp do pewnych zewnętrznych zasobów systemu, podczas gdy zestaw praw UNSAFE umożliwia nieograniczony dostęp do zasobów zarówno wewnątrz, jak i na zewnątrz systemu bazy danych.

Aby zapisać informacje o kodzie zestawu, użytkownik musi mieć możliwość wydania instrukcji CREATE ASSEMBLY. Właścicielem zestawu jest użytkownik (lub rola) wykonujący instrukcję. Możesz uczynić innego użytkownika właścicielem zestawu, używając klauzuli AUTHORIZATION instrukcji CREATE SCHEMA.

Aparat bazy danych obsługuje także instrukcje ALTER ASSEMBLY i DROP ASSEMBLY. Instrukcja ALTER ASSEMBLY używany do aktualizacji zestawu do najnowszej wersji. Ta instrukcja dodaje lub usuwa również pliki powiązane z odpowiednim zespołem. Instrukcja montażu DROP Usuwa określony zestaw i wszystkie powiązane z nim pliki z bieżącej bazy danych.

Poniższy przykład pokazuje, jak utworzyć procedurę składowaną w oparciu o zaimplementowany wcześniej kod zarządzany:

UŻYJ próbkiDb; PRZEJDŹ, UTWÓRZ PROCEDURĘ CountEmployees JAKO NAZWA ZEWNĘTRZNA CLRStoredProcedures.StoredProcedures.CountEmployees

Instrukcja CREATE PROCEDURE w przykładzie różni się od tej samej instrukcji z poprzednich przykładów tym, że zawiera Parametr NAZWA ZEWNĘTRZNA. Ta opcja określa, że kod jest generowany przez środowisko uruchomieniowe języka wspólnego. Imię w tym zdaniu składa się z trzech części:

nazwa_zespołu.nazwa_klasy.nazwa_metody

nazwa_zespołu - wskazuje nazwę złożenia;

nazwa_klasy - wskazuje nazwę klasy ogólnej;

nazwa_metody – część opcjonalna, określa nazwę metody zdefiniowanej wewnątrz klasy.

Wykonanie procedury CountEmployees przedstawia poniższy przykład:

UŻYJ próbkiDb; DECLARE @count INT EXECUTE @count = CountPracowników DRUKUJ @count -- Zwróć 7

Instrukcja PRINT zwraca bieżącą liczbę wierszy w tabeli Pracownik.

Podczas pracy z SQL Server użytkownicy mogą tworzyć własne procedury realizujące określone działania. Procedury składowane są pełnoprawnymi obiektami bazy danych, dlatego każda z nich przechowywana jest w określonej bazie danych. Bezpośrednie wywołanie procedury składowanej jest możliwe tylko wtedy, gdy odbywa się w kontekście bazy danych, w której znajduje się procedura.

Rodzaje procedur składowanych

SQL Server ma kilka typów procedur przechowywanych.

Systemowe procedury składowane służą do wykonywania różnych czynności administracyjnych. Prawie wszystkie czynności związane z administracją serwerem wykonywane są za ich pomocą. Można powiedzieć, że systemowe procedury składowane są interfejsem umożliwiającym pracę z tabelami systemowymi, która ostatecznie sprowadza się do zmiany, dodawania, usuwania i pobierania danych z tabel systemowych zarówno użytkowników, jak i systemowych baz danych. Procedury składowane systemu są poprzedzone sp_, są przechowywane w bazie danych systemu i można je wywoływać w kontekście dowolnej innej bazy danych.

Niestandardowe procedury składowane implementują określone działania. Procedury składowane są pełnoprawnym obiektem bazy danych. Dzięki temu każda procedura składowana znajduje się w określonej bazie danych, gdzie jest wykonywana.

Tymczasowe procedury składowane istnieją tylko przez krótki czas, po czym są automatycznie niszczone przez serwer. Dzielą się na lokalne i globalne. Lokalne tymczasowe procedury składowane można wywołać wyłącznie z połączenia, w którym zostały utworzone. Tworząc taką procedurę należy nadać jej nazwę zaczynającą się od pojedynczego znaku #. Podobnie jak wszystkie obiekty tymczasowe, procedury składowane tego typu są automatycznie usuwane po rozłączeniu się użytkownika lub ponownym uruchomieniu lub zatrzymaniu serwera. Globalne tymczasowe procedury składowane są dostępne dla każdego połączenia z serwera, który ma tę samą procedurę. Aby go zdefiniować wystarczy nadać mu nazwę zaczynającą się od znaków ##. Procedury te są usuwane w momencie ponownego uruchomienia lub zatrzymania serwera lub w przypadku zamknięcia połączenia w kontekście, w którym zostały utworzone.

Wyzwalacze

Wyzwalacze są rodzajem procedury składowanej. Są one wykonywane, gdy na tabeli wykonywany jest operator języka manipulacji danymi (DML). Wyzwalacze służą do sprawdzania integralności danych, a także do wycofywania transakcji.

Spust to skompilowana procedura SQL, której wykonanie jest uwarunkowane wystąpieniem określonych zdarzeń w relacyjnej bazie danych. Stosowanie wyzwalaczy jest w większości bardzo wygodne dla użytkowników baz danych. Mimo to ich użycie często wiąże się z dodatkowymi kosztami zasobów na potrzeby operacji we/wy. Gdy te same wyniki (przy znacznie mniejszym nakładzie pracy) można osiągnąć przy użyciu procedur składowanych lub aplikacji, użycie wyzwalaczy jest niepraktyczne.

Wyzwalacze to specjalne narzędzie serwera SQL służące do utrzymania integralności danych w bazie danych. Ograniczenia integralności, reguły i wartości domyślne nie zawsze zapewniają pożądany poziom funkcjonalności. Często konieczne jest wdrożenie złożonych algorytmów weryfikacji danych, aby zapewnić ich wiarygodność i realność. Ponadto czasami trzeba monitorować zmiany wartości tabeli, aby powiązane dane można było modyfikować w razie potrzeby. Wyzwalacze można traktować jako rodzaj filtrów, które zaczynają działać po wykonaniu wszystkich operacji zgodnie z regułami, standardowymi wartościami itp.

Spust to specjalny rodzaj procedury składowanej, która jest uruchamiana automatycznie przez serwer przy próbie zmiany danych w tabelach, z którymi powiązane są wyzwalacze. Każdy Spust jest powiązany z konkretną tabelą. Wszelkie dokonane przez niego modyfikacje danych traktowane są jako jedna transakcja. W przypadku wykrycia błędu lub naruszenia integralności danych transakcja zostaje wycofana. Dlatego zmiany są zabronione. Wszelkie zmiany wprowadzone już przez wyzwalacz również zostaną cofnięte.

Tworzy spust tylko właściciel bazy danych. Ograniczenie to pozwala uniknąć przypadkowych zmian w strukturze tabel, sposobach łączenia z nimi innych obiektów itp.

Spust Jest to bardzo przydatne i zarazem niebezpieczne lekarstwo. Jeśli więc logika jego działania jest niepoprawna, można łatwo zniszczyć całą bazę danych, dlatego wyzwalacze należy debugować bardzo ostrożnie.

W odróżnieniu od zwykłego podprogramu, spust jest wykonywany niejawnie za każdym razem, gdy wystąpi zdarzenie wyzwalające i nie ma żadnych argumentów. Aktywacja jest czasami nazywana uruchomieniem spustu. Za pomocą wyzwalaczy osiąga się następujące cele:

Sprawdzanie poprawności wprowadzonych danych i egzekwowanie złożonych ograniczeń integralności danych, których utrzymanie jest trudne, jeśli nie niemożliwe, przy użyciu ograniczeń integralności ustawionych w tabeli;

wydawanie ostrzeżeń przypominających o konieczności wykonania określonych czynności przy aktualizacji tabeli zaimplementowanej w określony sposób;

gromadzenie informacji audytowych poprzez rejestrowanie informacji o dokonanych zmianach i osobach, które je przeprowadziły;

wsparcie replikacji.

Poniżej przedstawiono podstawowy format polecenia CREATE TRIGGER:

<Определение_триггера>::=

UTWÓRZ WYZWALACZ nazwa_wyzwalacza

PRZED | PO<триггерное_событие>

NA<имя_таблицы>

<список_старых_или_новых_псевдонимов>]

<тело_триггера>

Zdarzenia wyzwalające obejmują wstawianie, usuwanie i aktualizowanie wierszy w tabeli. W tym drugim przypadku możesz określić konkretne nazwy kolumn tabeli dla zdarzenia wyzwalającego. Moment wyzwalacza jest określany za pomocą słów kluczowych BEFORE ( Spust uruchamia się przed wykonaniem powiązanych z nim zdarzeń) lub PO (po ich wykonaniu).

Akcje wykonywane przez wyzwalacz są określone dla każdego wiersza (DLA KAŻDEGO WIERSZA) objętego zdarzeniem lub tylko raz dla każdego zdarzenia (DLA KAŻDEJ INSTRUKCJI).

Niepoprawnie napisane wyzwalacze mogą prowadzić do poważnych problemów, takich jak zakleszczenia. Wyzwalacze mogą blokować wiele zasobów na długi czas, dlatego należy zwrócić szczególną uwagę na minimalizację konfliktów dostępu.

Spust można utworzyć tylko w bieżącej bazie danych, ale w ramach wyzwalacza istnieje możliwość dostępu do innych baz danych, w tym także tych znajdujących się na zdalnym serwerze.

procedura składowana jest możliwe jedynie wówczas, gdy odbywa się w kontekście bazy danych, w której zlokalizowana jest procedura.

Rodzaje procedur składowanych

SQL Server ma kilka typów procedury składowane.

System procedury składowane przeznaczony do wykonywania różnych czynności administracyjnych. Prawie wszystkie czynności związane z administracją serwerem wykonywane są za ich pomocą. Można powiedzieć, że systemowe procedury składowane to interfejs umożliwiający pracę z tabelami systemowymi, która docelowo sprowadza się do zmiany, dodawania, usuwania i pobierania danych z tabel systemowych zarówno baz użytkowników, jak i systemowych. System procedury składowane mają przedrostek sp_, są przechowywane w bazie danych systemu i można je wywołać w kontekście dowolnej innej bazy danych.
Zwyczaj procedury składowane wdrożyć określone działania. Procedury składowane– pełnoprawny obiekt bazy danych. W rezultacie każdy procedura składowana znajduje się w określonej bazie danych, w której jest wykonywany.
Tymczasowy procedury składowane istnieją tylko przez jakiś czas, po czym są automatycznie niszczone przez serwer. Dzielą się na lokalne i globalne. Lokalne tymczasowe procedury składowane można wywołać tylko z połączenia, w którym zostały utworzone. Tworząc taką procedurę należy nadać jej nazwę zaczynającą się od pojedynczego znaku #. Podobnie jak wszystkie obiekty tymczasowe, procedury składowane tego typu są automatycznie usuwane po rozłączeniu się użytkownika lub ponownym uruchomieniu lub zatrzymaniu serwera. Globalny tymczasowy procedury składowane są dostępne dla wszelkich połączeń z serwera, który ma tę samą procedurę. Aby go zdefiniować, po prostu nadaj mu nazwę zaczynającą się od znaków ## . Procedury te są usuwane w momencie ponownego uruchomienia lub zatrzymania serwera lub w przypadku zamknięcia połączenia w kontekście, w którym zostały utworzone.

Twórz, modyfikuj i usuwaj procedury składowane

kreacja procedura składowana polega na rozwiązaniu następujących problemów:

określenie rodzaju tworzonego procedura składowana: tymczasowe lub niestandardowe. Ponadto możesz stworzyć własny system procedura składowana, nadając mu nazwę poprzedzoną sp_ i umieszczając go w bazie danych systemu. Procedura ta będzie dostępna w kontekście dowolnej bazy danych serwera lokalnego;
planowanie praw dostępu. Podczas tworzenia procedura składowana należy wziąć pod uwagę, że będzie miał takie same prawa dostępu do obiektów bazy danych jak użytkownik, który ją utworzył;
definicja parametry procedury składowanej. Podobnie jak procedury zawarte w większości języków programowania, procedury składowane może mieć parametry wejściowe i wyjściowe;
rozwój kodu procedura składowana. Kod procedury może zawierać sekwencję dowolnych poleceń SQL, łącznie z wywołaniami innych procedury składowane.

Tworzenie nowego i zmiana istniejącego procedura składowana wykonane za pomocą następującego polecenia:

<определение_процедуры>::= (CREATE | ALTER ) nazwa_procedury [;liczba] [(@nazwa_parametru typ_danych ) [=domyślnie] ][,...n] AS operator_sql [...n]

Przyjrzyjmy się parametrom tego polecenia.

Używając przedrostków sp_, #, ##, utworzoną procedurę można zdefiniować jako systemową lub tymczasową. Jak widać ze składni polecenia nie wolno podawać nazwy właściciela, który będzie właścicielem utworzonej procedury, a także nazwy bazy danych, w której ma się ona znajdować. Zatem, aby umieścić stworzone procedura składowana w konkretnej bazie danych, należy wydać komendę CREATE PROCEDURE w kontekście tej bazy danych. Kiedy odwracamy się od ciała procedura składowana nazwy skrócone można stosować dla obiektów tej samej bazy danych, tj. bez podawania nazwy bazy danych. Jeśli chcesz uzyskać dostęp do obiektów znajdujących się w innych bazach danych, podanie nazwy bazy danych jest obowiązkowe.

Liczba w nazwie jest numerem identyfikacyjnym procedura składowana, co jednoznacznie identyfikuje go w grupie procedur. Aby ułatwić zarządzanie, procedury są logicznie tego samego typu procedury składowane można grupować, nadając im tę samą nazwę, ale różne numery identyfikacyjne.

Aby przesłać dane wejściowe i wyjściowe w utworzonym procedura składowana można zastosować parametry, których nazwy, podobnie jak nazwy zmiennych lokalnych, muszą zaczynać się od symbolu @. Jeden procedura składowana Można określić wiele parametrów oddzielonych przecinkami. W treści procedury nie należy używać zmiennych lokalnych, których nazwy pokrywają się z nazwami parametrów tej procedury.

Aby określić typ danych, który odpowiada parametr procedury składowanej, odpowiednie są dowolne typy danych SQL, łącznie ze zdefiniowanymi przez użytkownika. Jednakże typ danych CURSOR może być używany tylko jako parametr wyjściowy procedura składowana, tj. określenie słowa kluczowego OUTPUT.

Obecność słowa kluczowego OUTPUT oznacza, że odpowiedni parametr ma zwracać dane procedura składowana. Nie oznacza to jednak, że parametr nie nadaje się do przekazywania wartości procedura składowana. Określenie słowa kluczowego OUTPUT powoduje, że serwer zakończy działanie procedura składowana przypisz bieżącą wartość parametru do zmiennej lokalnej, która została podana podczas wywoływania procedury jako wartość parametru. Należy pamiętać, że przy podawaniu słowa kluczowego OUTPUT wartość odpowiedniego parametru podczas wywoływania procedury można ustawić wyłącznie za pomocą zmiennej lokalnej. Wszelkie wyrażenia lub stałe dozwolone dla parametrów regularnych są niedozwolone.

Słowo kluczowe VARYING jest używane w połączeniu z

Procedura składowana procedura składowana) jest nazwany obiektem programu bazy danych. SQL Server ma kilka typów procedur przechowywanych.

Procedury zapisane w systemie systemowe procedury składowane) są dostarczane przez programistów DBMS i służą do wykonywania działań na katalogu systemowym lub uzyskiwania informacji o systemie. Ich nazwy zwykle zaczynają się od przedrostka „sp_”. Wszystkie typy procedur składowanych uruchamia się za pomocą polecenia EXECUTE, które można skrócić do EXEC. Na przykład procedura składowana sp_helplogins, uruchamiana bez parametrów, generuje dwa raporty dotyczące nazw kont (Język angielski) loginy) i odpowiadających im użytkowników w każdej bazie danych (Język angielski) użytkowników).

EXEC sp_helplogins;

Aby dać wyobrażenie o działaniach wykonywanych przy użyciu systemowych procedur przechowywanych, Tabela 10.6 pokazuje kilka przykładów. W sumie w SQL Server znajduje się ponad tysiąc systemowych procedur składowanych.

Tabela 10.6

Przykłady procedur składowanych systemu SQL Server

Użytkownik może tworzyć procedury składowane w bazach danych użytkowników oraz w bazie danych obiektów tymczasowych. W tym drugim przypadku będzie to procedura składowana czasowy. Podobnie jak w przypadku tabel tymczasowych, nazwa tymczasowej procedury składowanej musi zaczynać się od przedrostka „#”, jeśli jest to lokalna tymczasowa procedura składowana, lub od „##”, jeśli jest to procedura globalna. Lokalną procedurę tymczasową można zastosować tylko w ramach połączenia, w którym została utworzona, procedurę globalną można zastosować także w ramach innych połączeń.

Programowalne obiekty SQL Server można tworzyć przy użyciu narzędzi lub zestawów języka Transact-SQL (Język angielski) Assembly) w środowisku CRL (Common Language Runtime) platformy Microsoft.Net Framework. W tym samouczku omówimy tylko pierwszą metodę.

Aby utworzyć procedury składowane, należy skorzystać z instrukcji CREATE PROCEDURE (można ją skrócić do PROC), której format podano poniżej:

UTWÓRZ (PROC I PROCEDURA) nazwa_procedury [ ; numer]

[(gparametr typ_danych)

[„domyślny] |

[Z [ ,...N ] ]

[DO REPLIKACJI]

AS ([ POCZĄTEK ] instrukcja_sql [;] [ ...n ] [ KONIEC ])

Jeśli utworzona zostanie procedura składowana (lub wyzwalacz, funkcja, widok) z opcją ENCRYPTION, jej kod zostanie przekształcony w taki sposób, że tekst stanie się nieczytelny. Jednocześnie, jak zauważono, zastosowany algorytm został przeniesiony z wcześniejszych wersji SQL Server i nie można go uważać za niezawodny algorytm zabezpieczający - istnieją narzędzia, które pozwalają szybko wykonać konwersję odwrotną.

Opcja RECOMPILE określa, że system będzie rekompilował tekst przy każdym wywołaniu procedury. W normalnym przypadku procedura skompilowana przy pierwszym uruchomieniu jest przechowywana w pamięci podręcznej, co pozwala na zwiększenie wydajności.

EXECUTE AS określa kontekst bezpieczeństwa, w którym ma zostać wykonana procedura. Następnie jedna z wartości f CALLER | SAM | WŁAŚCICIEL | "nazwa użytkownika"). CALLER jest wartością domyślną i oznacza, że kod zostanie wykonany w kontekście bezpieczeństwa użytkownika wywołującego ten moduł. W związku z tym użytkownik musi mieć uprawnienia nie tylko do samego programowalnego obiektu, ale także do innych obiektów bazy danych, na które ma on wpływ. EXECUTE AS SELF oznacza wykorzystanie kontekstu użytkownika tworzącego lub modyfikującego programowalny obiekt. OWNER określa, że kod zostanie wykonany w kontekście bieżącego właściciela procedury. Jeśli nie określono dla niego właściciela, przyjmuje się, że jest on właścicielem schematu, do którego należy. EXECUTE AS „nazwa_użytkownika” pozwala jawnie określić nazwę użytkownika (w pojedynczym cudzysłowie).

Dla procedury można określić parametry. Są to zmienne lokalne służące do przekazywania wartości do procedury. Jeżeli parametr zadeklarowany jest słowem kluczowym OUTPUT (lub w skrócie OUT), jest to wartość wyjściowa: wartość nadana mu w procedurze po jej zakończeniu może zostać wykorzystana przez program, który wywołał procedurę. Słowo kluczowe READONLY oznacza, że wartości parametru nie można zmienić w procedurze składowanej.

Parametrom można przypisać wartości domyślne, które zostaną zastosowane w przypadku, gdy wartość parametru nie zostanie jawnie podana przy wywołaniu procedury. Spójrzmy na przykład:

UTWÓRZ PROC surma (@a int, @b int=0,

©result int WYJŚCIE) AS

USTAW @wynik=0a+0b

Stworzyliśmy procedurę z trzema parametrami, przy czym parametr @b ma domyślną wartość =0, natomiast parametr @result jest parametrem wyjściowym: zwraca wartość do programu wywołującego. Wykonywane akcje są dość proste – parametr wyjściowy otrzymuje wartość sumy dwóch parametrów wejściowych.

Podczas pracy w SQL Server Management Studio utworzoną procedurę składowaną można znaleźć w sekcji programowalnych obiektów bazy danych (Język angielski) Programowalność) w podsekcji dotyczącej procedur składowanych (ryc. 10.2).

Wywołując procedurę, jako parametrów wejściowych można używać zarówno zmiennych, jak i stałych. Spójrzmy na dwa przykłady. W pierwszym przypadku parametry wejściowe procedury są jawnie określone jako stałe, a słowo kluczowe OUTPUT jest określone jako parametr wyjściowy w wywołaniu. Druga opcja wykorzystuje wartość zmiennej jako pierwszy parametr wejściowy i określa, że dla drugiego parametru należy zastosować wartość domyślną, używając słowa kluczowego DEFAULT:

Ryż. 10.2.

DECLARE @с int;

Suma EXEC 10,5,@c WYJŚCIE;

DRUKUJ 0c; – wyświetli się 15

DEKLARUJ Gi int = 5;

– dzwoniąc użyj wartości domyślnej

EXEC summa Gi, DOMYŚLNE, 0c WYJŚCIE;

DRUKUJ 0c; – wyświetli się 5

Rozważmy teraz przykład z analizą kodu powrotu, którym kończy się procedura. Załóżmy, że musimy obliczyć, ile książek w tabeli Bookl zostało opublikowanych w danym przedziale lat. Ponadto, jeśli rok początkowy jest większy od roku końcowego, procedura zwraca „1” i nie liczy się, w przeciwnym razie liczymy liczbę książek i zwracamy 0:

UTWÓRZ PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int WYJŚCIE) AS

JEŻELI 0Pierwszy rok>0Ostatni rok POWRÓT 1

SET @result= (WYBIERZ LICZBĘ(*) Z dbo.Bookl

GDZIE MIĘDZY 0pierwszym rokiem a 0ostatnim rokiem);

Rozważmy wariant wywołania tej procedury, w którym kod powrotu zapisywany jest w zmiennej całkowitej 0ret, po czym analizowana jest jego wartość (w tym przypadku będzie to 1). Funkcja CAST użyta w instrukcji PRINT służy do konwersji wartości zmiennej całkowitej Gres na typ łańcuchowy:

DEKLARUJ 0ret int, Gres int

EXEC Gret = rownum 2004, 2002, Gres OUT;

IF 0ret=l PRINT "Rok rozpoczęcia jest większy niż rok zakończenia"

DRUKUJ „Liczba książek” + CAST(Gres as varchar(20))

Procedury składowane mogą nie tylko odczytywać dane z tabeli, ale także modyfikować dane, a nawet tworzyć tabele i wiele innych obiektów bazy danych.

Nie można jednak tworzyć schematów, funkcji, wyzwalaczy, procedur i widoków na podstawie procedury składowanej.

Poniższy przykład ilustruje zarówno te możliwości, jak i zagadnienia związane z zakresem obiektów tymczasowych. Następująca procedura składowana sprawdza istnienie tabeli tymczasowej #TaL2; jeśli ta tabela nie istnieje, tworzy ją. Następnie do tabeli #TaL2 wprowadzane są wartości dwóch kolumn, a zawartość tabeli wyświetlana jest za pomocą instrukcji SELECT:

UTWÓRZ PROC My_Procl (@id int, @name varchar(30))

JEŚLI OBJECT_ID("tempdb.dbo.#Tab21) ma wartość NULL

WSTAW DO dbo.#Tab2 (id, nazwa) WARTOŚCI (0id, 0 nazwa)

WYBIERZ * Z dbo. #Tab2 –№1

Przed pierwszym wywołaniem procedury składowanej utworzymy używaną w niej tabelę tymczasową #TaL2. Zwróć uwagę na operatora EXEC. W poprzednich przykładach parametry były przekazywane do procedury „po pozycji”, jednak w tym przypadku zastosowano inny format przekazywania parametrów – „po nazwie”, wyraźnie wskazana jest nazwa parametru i jego wartość:

UTWÓRZ TABELĘ dbo.#Tab2 (id int, nazwa varchar(30));

EXEC Mój_Procl 0name="lvan", 0id=2;

WYBIERZ * Z dbo.#Tab2; –№2

W powyższym przykładzie instrukcja SELECT zostanie przetworzona dwukrotnie: za pierwszym razem – wewnątrz procedury, za drugim razem – z fragmentu kodu wywołującego (oznaczonego komentarzem „Nr 2”).

Przed drugim wywołaniem procedury usuniemy tabelę tymczasową #TaL2. Następnie na podstawie procedury składowanej zostanie utworzona tymczasowa tabela o tej samej nazwie:

UPUŚĆ TABELĘ dbo.#Tab2;

EXEC Mój_Procl 0name="Ivan", 0id=2;

WYBIERZ * Z dbo.#Tab2; –№2

W tym wypadku dane wyświetli jedynie instrukcja SELECT znajdująca się wewnątrz procedury (z komentarzem „Xa 1”). Wykonanie polecenia SELECT „Nr 2” spowoduje błąd, ponieważ tabela tymczasowa utworzona w procedurze składowanej zostanie już usunięta z bazy danych tempdb w momencie powrotu procedury.

Procedurę składowaną można usunąć za pomocą instrukcji DROP PROCEDURE. Poniżej przedstawiono jego format. Możesz usunąć kilka procedur składowanych za pomocą jednej instrukcji, wymieniając je oddzielonymi przecinkami:

UPUŚĆ (PROC I PROCEDURA) ( procedura ) [

Na przykład usuńmy wcześniej utworzoną procedurę summy:

DROP PROC summa;

Możesz wprowadzić zmiany w istniejącej procedurze (a właściwie ją przedefiniować) za pomocą instrukcji ALTER PROCEDURE (dozwolone

skrót PROC). Z wyjątkiem słowa kluczowego ALTER, format instrukcji jest zasadniczo taki sam, jak w przypadku CREATE PROCEDURE. Zmieńmy na przykład procedurę dbo. rownum, ustawiając jego wykonanie w kontekście bezpieczeństwa właściciela:

ALTER PROC dbo.rownum (SFirsYear int,

SLastYear int, Sresult int WYJŚCIE)

Z EXECUTE AS Owner – opcja do zainstalowania

JEŻELI 0Pierwszy rok>0Ostatni rok POWRÓT 1 W przeciwnym razie ZACZNIJ

SET 0result= (WYBIERZ LICZBĘ(*) Z dbo.Bookl

GDZIE MIĘDZY PIERWSZYM A OSTATNIM ROKIEM);

W niektórych przypadkach może zaistnieć konieczność dynamicznego wygenerowania polecenia i wykonania go na serwerze bazy danych. Problem ten można również rozwiązać za pomocą operatora EXEC. Poniższy przykład pobiera rekordy z tabeli Bookl jeśli atrybut Rok jest równy wartości określonej przez zmienną:

DEKLARACJA 0у int = 2000;

EXEC („WYBIERZ * Z dbo.Bookl WHERE = „+@y) ;

Wykonanie dynamicznie generowanych instrukcji stwarza warunki do realizacji ataków komputerowych typu „wstrzykiwanie SQL” (Język angielski) wstrzyknięcie SQL). Istota ataku polega na tym, że atakujący wstrzykuje swój własny kod SQL do dynamicznie generowanego zapytania. Zwykle ma to miejsce, gdy zastępowane parametry są pobierane z wyników wprowadzonych przez użytkownika.

Zmieńmy nieco poprzedni przykład:

DEKLARUJ 0у varchar(100);

SET 0у="2ООО"; – otrzymaliśmy to od użytkownika

Jeżeli założymy, że wartość string przypisaną w instrukcji SET otrzymaliśmy od użytkownika (nieważne w jaki sposób, np. poprzez aplikację webową), to przykład ilustruje „normalne” zachowanie naszego kodu.

DEKLARUJ 0у varchar(100);

SET 0="2000; USUŃ Z dbo.Book2"; - zastrzyk

EXEC("WYBIERZ * Z dbo.Book2 WHERE ="+0y);

W takich przypadkach zaleca się, jeśli to możliwe, zastosowanie systemowej procedury składowanej sp_executcsql, która pozwala kontrolować rodzaj parametrów, co jest jedną z barier dla zastrzyków SQL. Nie rozważając szczegółowo jego formatu, spójrzmy na przykład podobny do zaprezentowanego wcześniej:

WYKONAJ sp_executesql

N"WYBIERZ * Z dbo.Bookl WHERE =0y",

To jawnie określa typ parametru użytego w zapytaniu, a SQL Server będzie go kontrolował podczas wykonywania. Litera „N” przed cudzysłowem wskazuje, że jest to stała literału Unicode, zgodnie z wymaganiami procedury. Parametrowi można przypisać nie tylko wartość stałą, ale także wartość innej zmiennej.

Zdefiniowano pojęcie procedur składowanych. Zawiera przykłady tworzenia, modyfikowania i używania procedur składowanych z parametrami. Podano definicję parametrów wejściowych i wyjściowych. Podano przykłady tworzenia i wywoływania procedur składowanych.

Pojęcie procedury składowanej

Procedury składowane to grupy połączonych ze sobą instrukcji SQL, których użycie sprawia, że praca programisty staje się łatwiejsza i bardziej elastyczna procedura składowana jest często znacznie prostsze niż sekwencja pojedynczych instrukcji SQL. Procedury składowane to zestaw poleceń składający się z jednej lub większej liczby instrukcji SQL lub funkcji i przechowywanych w postaci skompilowanej w bazie danych. Wykonanie w bazie danych procedury składowane Zamiast pojedynczych instrukcji SQL użytkownik ma następujące korzyści:

niezbędne operatory znajdują się już w bazie danych;
wszyscy przeszli przez scenę rozbiór gramatyczny zdania i są w formacie wykonywalnym; zanim wykonanie procedury składowanej SQL Server generuje dla niego plan wykonania, przeprowadza jego optymalizację i kompilację;
procedury składowane wsparcie programowanie modułowe, ponieważ pozwalają podzielić duże zadania na niezależne, mniejsze i łatwiejsze w zarządzaniu części;
procedury składowane może powodować inne procedury składowane i funkcje;
procedury składowane można wywołać z innych typów aplikacji;
zazwyczaj, procedury składowane wykonaj szybciej niż sekwencja pojedynczych instrukcji;
procedury składowanełatwiejsze w użyciu: mogą składać się z dziesiątek lub setek poleceń, ale aby je uruchomić, wystarczy podać nazwę żądanego procedura składowana. Pozwala to zmniejszyć rozmiar żądania wysyłanego od klienta do serwera, a co za tym idzie, obciążenie sieci.

Przechowywanie procedur w tym samym miejscu, w którym są wykonywane, zmniejsza ilość danych przesyłanych w sieci i poprawia ogólną wydajność systemu. Aplikacja procedury składowane upraszcza konserwację systemów oprogramowania i wprowadzanie w nich zmian. Zazwyczaj wszystkie ograniczenia integralności w postaci reguł i algorytmów przetwarzania danych są implementowane na serwerze bazy danych i dostępne dla aplikacji końcowej jako zbiór procedury składowane, które reprezentują interfejs przetwarzania danych. Aby zapewnić integralność danych, a także ze względów bezpieczeństwa, aplikacja zwykle nie otrzymuje bezpośredniego dostępu do danych - cała praca z nią odbywa się poprzez wywoływanie określonych procedury składowane.

Takie podejście sprawia, że bardzo łatwo jest modyfikować algorytmy przetwarzania danych, które natychmiast stają się dostępne dla wszystkich użytkowników sieci, a także zapewnia możliwość rozbudowy systemu bez dokonywania zmian w samej aplikacji: wystarczy zmienić procedura składowana na serwerze bazy danych. Programista nie musi rekompilować aplikacji, tworzyć jej kopii ani instruować użytkowników, aby pracowali z nową wersją. Użytkownicy mogą nawet nie być świadomi, że w systemie zaszły zmiany.

Procedury składowane istnieją niezależnie od tabel lub innych obiektów bazy danych. Są wywoływane przez program kliencki, inny procedura składowana lub wyzwalacz. Deweloper może zarządzać prawami dostępu do procedura składowana, zezwalający lub zakazujący jego wykonania. Zmień kod procedura składowana dozwolone tylko przez jego właściciela lub członka stałej roli w bazie danych. W razie potrzeby możesz przenieść jego własność z jednego użytkownika na drugiego.

Procedury składowane w środowisku MS SQL Server

Podczas pracy z SQL Server użytkownicy mogą tworzyć własne procedury realizujące określone działania. Procedury składowane są pełnoprawnymi obiektami bazy danych, dlatego każdy z nich przechowywany jest w konkretnej bazie danych. Bezpośrednie połączenie procedura składowana jest możliwe jedynie wówczas, gdy odbywa się w kontekście bazy danych, w której zlokalizowana jest procedura.

Rodzaje procedur składowanych

SQL Server ma kilka typów procedury składowane.

System procedury składowane przeznaczony do wykonywania różnych czynności administracyjnych. Prawie wszystkie czynności związane z administracją serwerem wykonywane są za ich pomocą. Można powiedzieć, że systemowe procedury składowane to interfejs umożliwiający pracę z tabelami systemowymi, która docelowo sprowadza się do zmiany, dodawania, usuwania i pobierania danych z tabel systemowych zarówno baz użytkowników, jak i systemowych. System procedury składowane mają przedrostek sp_, są przechowywane w bazie danych systemu i można je wywołać w kontekście dowolnej innej bazy danych.
Zwyczaj procedury składowane wdrożyć określone działania. Procedury składowane– pełnoprawny obiekt bazy danych. W rezultacie każdy procedura składowana znajduje się w określonej bazie danych, w której jest wykonywany.
Tymczasowy procedury składowane istnieją tylko przez jakiś czas, po czym są automatycznie niszczone przez serwer. Dzielą się na lokalne i globalne. Lokalne tymczasowe procedury składowane można wywołać tylko z połączenia, w którym zostały utworzone. Tworząc taką procedurę należy nadać jej nazwę zaczynającą się od pojedynczego znaku #. Podobnie jak wszystkie obiekty tymczasowe, procedury składowane tego typu są automatycznie usuwane po rozłączeniu się użytkownika lub ponownym uruchomieniu lub zatrzymaniu serwera. Globalny tymczasowy procedury składowane są dostępne dla wszelkich połączeń z serwera, który ma tę samą procedurę. Aby go zdefiniować, po prostu nadaj mu nazwę zaczynającą się od znaków ## . Procedury te są usuwane w momencie ponownego uruchomienia lub zatrzymania serwera lub w przypadku zamknięcia połączenia w kontekście, w którym zostały utworzone.

Twórz, modyfikuj i usuwaj procedury składowane

kreacja procedura składowana polega na rozwiązaniu następujących problemów:

określenie rodzaju tworzonego procedura składowana: tymczasowe lub niestandardowe. Ponadto możesz stworzyć własny system procedura składowana, nadając mu nazwę poprzedzoną sp_ i umieszczając go w bazie danych systemu. Procedura ta będzie dostępna w kontekście dowolnej bazy danych serwera lokalnego;
planowanie praw dostępu. Podczas tworzenia procedura składowana należy wziąć pod uwagę, że będzie miał takie same prawa dostępu do obiektów bazy danych jak użytkownik, który ją utworzył;
definicja parametry procedury składowanej. Podobnie jak procedury zawarte w większości języków programowania, procedury składowane może mieć parametry wejściowe i wyjściowe;
rozwój kodu procedura składowana. Kod procedury może zawierać sekwencję dowolnych poleceń SQL, łącznie z wywołaniami innych procedury składowane.

Tworzenie nowego i zmiana istniejącego procedura składowana wykonane za pomocą następującego polecenia:

<определение_процедуры>::= (CREATE | ALTER ) PROC nazwa_procedury [;liczba] [(@nazwa_parametru typ_danych ) [=domyślnie] ][,...n] AS operator_sql [...n]

Przyjrzyjmy się parametrom tego polecenia.

Słowo kluczowe VARYING jest używane w połączeniu z parametrem OUTPUT, który jest typu CURSOR. To decyduje parametr wyjściowy będzie zestaw wyników.

Słowo kluczowe DEFAULT reprezentuje wartość odpowiadającą parametr domyślny. Zatem wywołując procedurę, nie trzeba jawnie podawać wartości odpowiedniego parametru.

Ponieważ serwer buforuje plan wykonania zapytania i skompilowany kod, przy następnym wywołaniu procedury zostaną użyte gotowe wartości. Jednak w niektórych przypadkach nadal konieczna jest ponowna kompilacja kodu procedury. Określenie słowa kluczowego RECOMPILE instruuje system, aby utworzył plan wykonania procedura składowana za każdym razem, gdy dzwoni.

Parametr DO REPLIKACJI wymagany jest przy replikowaniu danych i włączaniu utworzonych procedura składowana jako artykuł do publikacji.

Słowo kluczowe ENCRYPTION instruuje serwer, aby zaszyfrował kod procedura składowana, które mogą zapewnić ochronę przed użyciem zastrzeżonych algorytmów realizujących dzieło procedura składowana.

Słowo kluczowe AS jest umieszczane na początku treści procedura składowana, tj. zestaw poleceń SQL, za pomocą których zostanie wykonana ta lub inna akcja. W treści procedury można zastosować prawie wszystkie polecenia SQL, zadeklarować transakcje, ustawić blokady i wywołać inne. procedury składowane. Wyjdź z procedura składowana można to zrobić za pomocą polecenia RETURN.

Usuwanie procedury składowanej wykonywane poleceniem:

PROCEDURA UPUSZCZANIA (nazwa_procedury) [,...n]

Wykonywanie procedury składowanej

Dla wykonać procedurę składowaną Użyte polecenie to:

[[ EXEC [ UTE] nazwa_procedury [;liczba] [[@nazwa_parametru=](wartość | @nazwa_zmiennej) |][,...n]

Jeśli połączenie procedura składowana nie jest jedyną komendą w pakiecie, wymagana jest obecność komendy EXECUTE. Co więcej, polecenie to jest wymagane do wywołania procedury z treści innej procedury lub wyzwalacza.

Użycie słowa kluczowego OUTPUT podczas wywoływania procedury jest dozwolone tylko dla parametrów, które zostały zadeklarowane kiedy stworzenie procedury ze słowem kluczowym OUTPUT.

Jeśli podczas wywoływania procedury zostanie określone słowo kluczowe DEFAULT, zostanie ono użyte domyślna wartość. Oczywiście określone słowo DEFAULT jest dozwolone tylko dla tych parametrów, dla których zostało zdefiniowane domyślna wartość.

Składnia polecenia EXECUTE pokazuje, że nazwy parametrów można pominąć podczas wywoływania procedury. Jednak w tym przypadku użytkownik musi określić wartości parametrów w tej samej kolejności, w jakiej zostały one wymienione stworzenie procedury. Przypisz do parametru domyślna wartość, nie możesz go po prostu pominąć podczas wystawiania aukcji. Jeśli chcesz pominąć parametry, dla których jest zdefiniowany domyślna wartość, wystarczy jawnie określić nazwy parametrów podczas wywoływania procedura składowana. Co więcej, w ten sposób możesz wyświetlić parametry i ich wartości w dowolnej kolejności.

Należy pamiętać, że podczas wywoływania procedury podawane są albo nazwy parametrów z wartościami, albo tylko wartości bez nazwy parametru. Łączenie ich jest niedozwolone.

Przykład 12.1. Procedura bez parametrów. Opracuj procedurę uzyskiwania nazw i kosztów towarów zakupionych przez Iwanowa.

UTWÓRZ PROC my_proc1 JAKO WYBIERZ Nazwa.Produktu, Cena.Produktu*Transakcja.Ilość AS Koszt, Klient.Nazwisko Z DOŁĄCZENIA WEWNĘTRZNEGO klienta (Transakcja WEWNĘTRZNEGO JOIN produktu NA Produkt.ProductCode=Transakcja.ProductCode) ON Customer.CustomerCode=Transakcja.CustomerCode GDZIE Klient .Nazwisko='Iwanow' Przykład 12.1. Procedura uzyskiwania nazw i wartości towarów zakupionych przez Iwanowa.