Ostatnia aktualizacja: 14.08.2017

Często operacja na danych to zestaw instrukcji, które muszą zostać wykonane w określonej kolejności. Na przykład przy dodawaniu pozycji zakupu należy wprowadzić dane do tabeli zamówień. Jednak wcześniej musisz sprawdzić, czy zakupiony przedmiot jest w magazynie. Konieczne może być sprawdzenie kilku dodatkowych warunków. Oznacza to, że w rzeczywistości proces zakupu produktu obejmuje kilka czynności, które należy wykonać w określonej kolejności. W tym przypadku bardziej optymalne byłoby zamknięcie wszystkich tych działań w jednym obiekcie - procedura składowana (procedura składowana).

Oznacza to, że w istocie procedury składowane to zestaw instrukcji wykonywanych jako całość. W ten sposób procedury składowane pozwalają uprościć złożone operacje i przenieść je do jednego obiektu. Zmieni się proces zakupu towarów, w związku z czym wystarczy zmienić kod procedury. Oznacza to, że procedura ułatwia również zarządzanie kodem.

Procedury składowane pozwalają również ograniczyć dostęp do danych w tabelach, a tym samym zmniejszyć prawdopodobieństwo celowych lub nieświadomych niepożądanych działań w odniesieniu do tych danych.

Kolejnym ważnym aspektem jest wydajność. Procedury składowane są zwykle szybsze niż zwykłe instrukcje SQL. Dzieje się tak, ponieważ kod procedury jest kompilowany przy pierwszym uruchomieniu, a następnie zapisywany w skompilowanej formie.

Polecenie CREATE PROCEDURE lub CREATE PROC służy do tworzenia procedury składowanej.

W związku z tym procedura składowana ma trzy kluczowe funkcje: uproszczenie kodu, bezpieczeństwo i wydajność.

Na przykład załóżmy, że w bazie danych znajduje się tabela, która przechowuje dane produktów:

TWORZENIE TABELI Produkty (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR (30) NOT NULL, producent NVARCHAR (20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL);

Utwórzmy procedurę składowaną w celu pobrania danych z tej tabeli:

UŻYWAJ productsdb; PROCEDURA GO TWÓRZ Podsumowanie produktu JAK WYBIERZ ProductName AS produkt, producent, cena z produktów

Ponieważ polecenie CREATE PROCEDURE musi być wywoływane w oddzielnym pakiecie, polecenie GO jest używane po poleceniu USE, które instaluje bieżącą bazę danych w celu zdefiniowania nowego pakietu.

Słowo kluczowe AS musi występować po nazwie procedury.

Aby oddzielić treść procedury od reszty skryptu, kod procedury jest często umieszczany w bloku BEGIN ... END:

UŻYWAJ productsdb; PRZEJDŹ PROCEDURĘ TWORZENIA Podsumowanie produktu JAK POCZĄTEK WYBIERZ ProductName AS produkt, producent, cena od końca produktu;

Po dodaniu procedury możemy ją zobaczyć w węźle bazy danych w SQL Server Management Studio w podwęźle Programowalność -\u003e procedury składowane:

Będziemy również mogli kontrolować procedurę za pomocą interfejsu wizualnego.

Wykonanie procedury

Polecenie EXEC lub EXECUTE jest wywoływane w celu wykonania procedury składowanej:

Podsumowanie produktów EXEC

Usuwanie procedury

Aby usunąć procedurę, użyj polecenia DROP PROCEDURE:

PROCEDURA UPUSZCZANIA Podsumowanie produktu

1. Zbadanie operatorów opisujących procedury składowane i zasady przekazywania ich parametrów wejściowych i wyjściowych.
2. Poznaj kolejność tworzenia i debugowania procedur składowanych na MS SQL Server 2000.
3. Opracuj pięć podstawowych procedur składowanych dla przykładowej bazy danych biblioteki.
4. Sporządzić raport z wykonanej pracy w formie elektronicznej.

1. Ogólne informacje o procedurach składowanych

Procedura składowana To zestaw poleceń przechowywanych na serwerze i wykonywanych jako całość. Procedury składowane to mechanizm, za pomocą którego można tworzyć procedury działające na serwerze i kontrolowane przez jego procesy. Takie procedury mogą być wywoływane przez aplikację wywołującą. Mogą być również wyzwalane przez reguły lub wyzwalacze integralności danych.

Procedury składowane mogą zwracać wartości. Procedura pozwala porównać wartości wprowadzone przez użytkownika z informacjami ustawionymi w systemie. Procedury składowane wykorzystują zaawansowane rozwiązania sprzętowe SQL Server. Są zorientowane na bazę danych i ściśle współpracują z SQL Server Optimizer. Pozwala to na wysoką wydajność w przetwarzaniu danych.

Możesz przekazywać wartości do procedur składowanych i uzyskiwać z nich wyniki, niekoniecznie związane z arkuszem. Procedura składowana może obliczać wyniki w locie.

Procedury składowane są dwojakiego rodzaju: zwyczajny i rozszerzony... Zwykłe procedury składowane to zestaw poleceń języka Transact-SQL, podczas gdy rozszerzone procedury składowane są reprezentowane jako biblioteki dołączane dynamicznie (DLL). Takie procedury, w przeciwieństwie do zwykłych, mają przedrostek xp_. Serwer ma standardowy zestaw rozszerzonych procedur, ale użytkownicy mogą pisać własne procedury w dowolnym języku programowania. Najważniejsze jest użycie interfejsu programowania Interfejs API usług SQL Server Open Data Services... Rozszerzone procedury składowane mogą znajdować się tylko w bazie danych Master.

Zwykłe procedury składowane można również podzielić na dwa typy: systemowy i zwyczaj... Procedury systemowe to standardowe procedury używane do obsługi serwera; niestandardowe - dowolne procedury stworzone przez użytkownika.

1.1. Zalety procedur składowanych

W najbardziej ogólnym przypadku procedury składowane mają następujące zalety:

• Wysoka wydajność. Wynika to z lokalizacji procedur składowanych na serwerze. Serwer z reguły jest maszyną o większej mocy, więc czas wykonania procedury na serwerze jest znacznie krótszy niż na stacji roboczej. Ponadto informacje z bazy danych i procedury składowanej znajdują się w tym samym systemie, więc praktycznie nie ma straty czasu na przesyłanie rekordów przez sieć. Procedury składowane mają bezpośredni dostęp do baz danych, dzięki czemu praca z informacjami jest bardzo szybka.
• Zaleta projektowania systemu w architekturze klient-serwer. Polega na możliwości oddzielnego tworzenia oprogramowania klienta i serwera. Ta zaleta jest kluczowa w rozwoju i może znacznie skrócić czas potrzebny na ukończenie projektu. Kod działający na serwerze można opracować niezależnie od kodu po stronie klienta. W takim przypadku komponenty po stronie serwera mogą być współużytkowane przez komponenty po stronie klienta.
• Poziom bezpieczeństwa. Procedury składowane mogą działać jako narzędzie zwiększające bezpieczeństwo. Procedury składowane można tworzyć w celu wykonywania operacji dodawania, modyfikowania, usuwania i wyświetlania list, a tym samym uzyskania kontroli nad każdym aspektem dostępu do informacji.
• Wzmocnienie reguł serwera, które działają z danymi. To jeden z najważniejszych powodów, dla których warto korzystać z inteligentnego silnika bazy danych. Procedury składowane umożliwiają stosowanie reguł i innej logiki, które pomagają kontrolować informacje wprowadzane do systemu.

Chociaż SQL jest zdefiniowany jako nieproceduralny, SQL Server używa słów kluczowych związanych z kontrolą przepływu procedur. Te słowa kluczowe służą do tworzenia procedur, które można zapisać do późniejszego wykonania. Procedury składowane mogą być używane zamiast programów napisanych w standardowych językach programowania (takich jak C lub Visual Basic), które wykonują operacje na bazie danych SQL Server.

Procedury składowane są kompilowane podczas ich pierwszego wykonania i zapisywane w tabeli systemowej bieżącej bazy danych. Są optymalizowane po kompilacji. To wybiera najlepszy sposób dostępu do informacji o tabeli. Taka optymalizacja uwzględnia faktyczne położenie danych w tabeli, dostępne indeksy, ładowanie tabeli itp.

Skompilowane procedury składowane mogą znacznie poprawić wydajność systemu. Warto jednak zauważyć, że statystyki danych od momentu utworzenia procedury do jej wykonania mogą stać się nieaktualne, a indeksy mogą stać się nieskuteczne. Chociaż istnieje możliwość aktualizacji statystyk i dodawania nowych, wydajniejszych indeksów, to plan wykonania procedury został już sporządzony, czyli procedura jest skompilowana iw efekcie sposób dostępu do danych może przestać być efektywny. Dlatego istnieje możliwość rekompilacji procedur przy każdym wywołaniu.

Z drugiej strony rekompilacja za każdym razem zajmie trochę czasu. Dlatego kwestia efektywności ponownego skompilowania procedury lub sporządzenia planu jej wykonania w jednym czasie jest dość delikatna i powinna być rozpatrywana oddzielnie dla każdego konkretnego przypadku.

Procedury składowane mogą być wykonywane na komputerze lokalnym lub w zdalnym systemie SQL Server. Dzięki temu można aktywować procesy na innych maszynach i pracować nie tylko z lokalnymi bazami danych, ale także z informacjami na kilku serwerach.

Aplikacje napisane w jednym z języków wysokiego poziomu, takim jak C lub Visual Basic .NET, mogą również wywoływać procedury składowane, co zapewnia optymalne rozwiązanie równoważenia obciążenia między oprogramowaniem po stronie klienta a oprogramowaniem serwera SQL.

1.2. Tworzenie procedur składowanych

Instrukcja Create Procedure służy do tworzenia procedury składowanej. Nazwa procedury składowanej może mieć do 128 znaków, w tym znaki # i ##. Składnia definicji procedury:

CREATE PROC sp_name [; numer]
[(typ danych @ parametr) [\u003d wartość_domyślna]] [, ... n]

TAK JAK
<Инструкции_SQL>

Rozważ parametry tego polecenia:

• Nazwa_procedury - nazwa procedury; musi być zgodny z zasadami dotyczącymi identyfikatorów: jego długość nie może przekraczać 128 znaków; w przypadku lokalnych procedur tymczasowych, # jest używane przed nazwą, a dla globalnych procedur tymczasowych, ##;
• Liczba to opcjonalna liczba całkowita używana do grupowania wielu procedur pod tą samą nazwą;
• @ parametr typ_danych - lista nazw parametrów procedury ze wskazaniem odpowiedniego typu danych dla każdego; takich parametrów może być do 2100. Wartość parametru może wynosić NULL. Można używać wszystkich typów danych z wyjątkiem tekstu, tekstu i obrazu. Typ danych Cursor może być użyty jako parametr wyjściowy (słowo kluczowe OUTPUT lub VARYING). Parametry o typie danych Cursor mogą być tylko parametrami wyjściowymi;
• VARYING jest słowem kluczowym określającym, że zestaw wyników jest używany jako parametr wyjściowy (używany tylko dla typu Cursor);
• WYJŚCIE - wskazuje, że określony parametr może być użyty jako wyjście;
• domyślna wartość - używany w przypadku, gdy parametr jest pomijany przy wywołaniu procedury; musi być stała i może zawierać znaki maskujące (%, _, [,], ^) i NULL;
• WITH RECOMPILE - słowa kluczowe wskazujące, że SQL Server nie zapisze planu procedury do pamięci podręcznej, ale utworzy go za każdym razem, gdy zostanie wykonany;
• WITH ENCRYPTION słowa kluczowe wskazujące, że SQL Server zaszyfruje procedurę przed zapisaniem do tabeli systemowej Syscomments. Aby uniemożliwić odtworzenie tekstu zaszyfrowanych procedur, po zaszyfrowaniu konieczne jest usunięcie odpowiednich krotek z tabeli syscomments;
• DO REPLIKACJI - słowa kluczowe wskazujące, że ta procedura jest tworzona tylko do replikacji. Ta opcja jest niekompatybilna ze słowami kluczowymi WITH RECOMPILE;
• AS - definicja tekstu początku procedury;
• <Инструкции_SQL> - zestaw poprawnych instrukcji SQL, ograniczony tylko maksymalnym rozmiarem procedury składowanej - 128 KB. Następujące instrukcje są niepoprawne: ALTER DATABASE, ALTER PROCEDURE, ALTER TABLE, CREATE DEFAULT, CREATE PROCEDURE, ALTER TRIGGER, ALTER VIEW, CREATE DATABASE, CREATE REGLE, CREATE SCHEMA, CREATE TRIGGER, CREITATE, VIEW, DISPASE DEFAULT DROP DEFAULT, DROP PROCEDURE, DROP RLE, DROP TRIGGER, DROP VIEW, RESOTRE DATABASE, RESTORE LOG, RECONFIGURE, UPDATE STATYSTICS.

Spójrzmy na przykład procedury składowanej. Opracujmy procedurę składowaną, która zlicza i wyświetla liczbę kopii książek znajdujących się obecnie w bibliotece:

UTWÓRZ procedurę Count_Ex1
- procedura liczenia egzemplarzy książek,
- aktualnie w bibliotece,
- nie w rękach czytelników
Tak jak
- ustaw tymczasową zmienną lokalną
Zadeklaruj @N int
Wybierz @N \u003d count (*) z Exemplar Where Yes_No \u003d "1"
Wybierz @N
UDAĆ SIĘ

Ponieważ procedura składowana jest pełnoprawnym składnikiem bazy danych, to, jak już wiesz, możesz utworzyć nową procedurę tylko dla bieżącej bazy danych. Podczas pracy w programie SQL Server Query Analyzer ustawienie bieżącej bazy danych odbywa się za pomocą instrukcji Use, po której następuje nazwa bazy danych, w której ma zostać utworzona procedura składowana. Możesz również wybrać bieżącą bazę danych za pomocą listy rozwijanej.

Po utworzeniu procedury składowanej w systemie SQL Server kompiluje ją i weryfikuje procedury, które są uruchamiane. W przypadku jakichkolwiek problemów procedura jest odrzucana. Błędy muszą zostać poprawione przed ponownym nadaniem.

SQL Server 2000 używa opóźnionego rozpoznawania nazw, więc jeśli procedura składowana zawiera wywołanie innej, jeszcze nie zaimplementowanej procedury, generowane jest ostrzeżenie, ale wywołanie procedury nieistniejącej zostaje zachowane.

Jeśli pozostawisz wywołanie niezidentyfikowanej procedury składowanej w systemie, użytkownik otrzyma komunikat o błędzie podczas próby jej wykonania.

Możesz również utworzyć procedurę składowaną za pomocą programu SQL Server Enterprise Manager:

W celu sprawdzenia funkcjonalności utworzonej procedury składowanej należy przejść do Query Analyzer i uruchomić procedurę do wykonania przez operatora EXEC<имя процедуры> ... Wyniki uruchomienia utworzonej procedury przedstawiono na rys. 4.

Figa. 4. Uruchamianie procedury składowanej w programie Query Analyzer

Figa. 5. Wynik wykonania procedury bez operatora wyświetlania

1.3. Parametry procedury składowanej

Procedury składowane mają bardzo duże możliwości, ale maksymalne wykorzystanie ich możliwości jest możliwe tylko poprzez nadanie im dynamiki. Deweloper musi mieć możliwość przekazywania wartości do procedury składowanej, z którą będzie pracować, czyli parametrów. Poniżej przedstawiono podstawowe zasady używania parametrów w procedurach składowanych.

• Dla procedury można zdefiniować jeden lub więcej parametrów.
• Parametry są używane jako nazwane lokalizacje przechowywania, podobnie jak zmienne w językach programowania, takich jak C, Visual Basic .NET.
• Nazwa parametru jest koniecznie poprzedzona symbolem @.
• Nazwy parametrów są lokalne dla procedury, w której zostały zdefiniowane.
• Parametry służą do przekazywania informacji do procedury podczas jej wykonywania. Pojawią się w linii poleceń po nazwie procedury.
• Jeśli procedura ma kilka parametrów, są one oddzielone przecinkami.
• Typy danych zdefiniowane przez system lub użytkownika służą do określania typu informacji przekazywanych jako parametr.

Poniżej znajduje się definicja procedury, która ma jeden parametr wejściowy. Zmieńmy poprzednie zadanie i nie będziemy liczyć wszystkich egzemplarzy książek, a jedynie egzemplarze określonej książki. Nasze książki są jednoznacznie identyfikowane poprzez ich unikalny numer ISBN, więc przekażemy ten parametr do procedury. W takim przypadku tekst procedury składowanej zmieni się i będzie wyglądał następująco:

Utwórz procedurę Count_Ex (@ISBN varchar (14))
Tak jak
Zadeklaruj @N int
Wybierz @N
UDAĆ SIĘ

Uruchamiając tę \u200b\u200bprocedurę do wykonania, musimy przekazać jej wartość parametru wejściowego (rys. 6).

Figa. 6. Rozpoczęcie procedury z przekazaniem parametrów

Aby utworzyć wiele wersji tej samej procedury o tej samej nazwie, dodaj średnik i liczbę całkowitą po nazwie podstawowej. Jak to zrobić, pokazano w następującym przykładzie, który opisuje, jak utworzyć dwie procedury o tej samej nazwie, ale różnych numerach wersji (1 i 2). Numer służy do kontrolowania uruchomionej wersji tej procedury. Jeśli nie określono numeru wersji, wykonywana jest pierwsza wersja procedury. Ta opcja nie jest pokazana w poprzednim przykładzie, ale mimo to jest dostępna dla twojej aplikacji.

Obie procedury używają instrukcji print do wyświetlenia komunikatu identyfikującego wersję. Pierwsza wersja liczy liczbę bezpłatnych egzemplarzy, a druga - liczbę dostępnych egzemplarzy danej książki.

Tekst obu wersji procedur podano poniżej:

CREATE Procedura Count_Ex_all; 1
(@ISBN varchar (14))
- procedura liczenia bezpłatnych egzemplarzy danej książki
Tak jak
Zadeklaruj @N int
Wybierz @N \u003d count (*) z Exemplar Where ISBN \u003d @ISBN and Yes_No \u003d "1"
Wybierz @N
--
UDAĆ SIĘ
--
CREATE Procedura Count_Ex_all; 2
(@ISBN varchar (14))
- procedura liczenia bezpłatnych egzemplarzy danej książki
Tak jak
Zadeklaruj @ N1 int
Wybierz @ N1 \u003d count (*) z przykładu, gdzie ISBN \u003d @ISBN i Yes_No \u003d "0"
Wybierz @ N1
UDAĆ SIĘ

Wyniki wykonania zabiegu w różnych wersjach przedstawiono na rys. 7.

Figa. 7. Wyniki działania różnych wersji tej samej procedury składowanej

Podczas pisania wielu wersji należy pamiętać o następujących ograniczeniach: ponieważ wszystkie wersje procedury są kompilowane razem, wszystkie zmienne lokalne są uznawane za wspólne. Dlatego jeśli wymaga tego algorytm przetwarzania, konieczne jest użycie różnych nazw zmiennych wewnętrznych, co uczyniliśmy, nadając zmiennej @N nazwę @ N1 w drugiej procedurze.

Napisane przez nas procedury nie zwracają ani jednego parametru, a jedynie wyświetlają wynikową liczbę. Jednak najczęściej musimy uzyskać parametr do dalszej obróbki. Istnieje kilka sposobów zwracania parametrów z procedury składowanej. Najprościej jest użyć operatora RETURN. Ten operator zwróci pojedynczą wartość liczbową. Ale musimy określić nazwę zmiennej lub wyrażenie, które jest przypisane do zwracanego parametru. Poniżej przedstawiono wartości zarezerwowane przez system zwracane przez operator RETURN:

Kod	Wartość
0	W porządku
–1	Obiekt nie znaleziony
–2	Błąd typu danych
–3	Proces stał się ofiarą „impasu”
–4	błąd dostępu
–5	Błąd składni
–6	Jakiś błąd
–7	Błąd zasobu (brak spacji)
–8	Wystąpił naprawialny błąd wewnętrzny
–9	Osiągnięto limit systemu
–10	Śmiertelne naruszenie integralności wewnętrznej
–11	Podobnie
–12	Zniszczenie tabeli lub indeksu
–13	Zniszczenie bazy danych
–14	Błąd sprzętu

Zatem, aby nie zaprzeczać systemowi, przez ten parametr możemy zwracać tylko dodatnie liczby całkowite.

Na przykład możemy zmienić tekst wcześniej zapisanej procedury składowanej Count_ex w następujący sposób:

Utwórz procedurę Count_Ex2 (@ISBN varchar (14))
Tak jak
Zadeklaruj @N int
Wybierz @N \u003d count (*) z Exemplar
Gdzie ISBN \u003d @ISBN i YES_NO \u003d „1”
- zwracamy wartość zmiennej @N,
- jeśli wartość zmiennej nie jest zdefiniowana, zwraca 0
Return Coalesce (@N, 0)
UDAĆ SIĘ

Teraz możemy pobrać wartość zmiennej @N i wykorzystać ją do dalszego przetwarzania. W takim przypadku wartość zwracana jest przypisywana do samej procedury składowanej i aby ją przeanalizować, można użyć następującego formatu instrukcji wywołania procedury składowanej:

Exec<переменная> = <имя_процедуры> <значение_входных_параметров>

Przykład wywołania naszej procedury pokazano na rys. 8.

Figa. 8. Przekazywanie wartości zwracanej przez procedurę składowaną do zmiennej lokalnej

Parametry wejściowe procedur składowanych mogą używać wartości domyślnej. Ta wartość zostanie użyta, jeśli wartość parametru nie została określona podczas wywoływania procedury.

Wartość domyślna jest oznaczona znakiem równości po opisie parametru wejściowego i jego typu. Rozważmy procedurę składowaną, która zlicza egzemplarze książki w danym roku wydania. Domyślny rok to 2006.

UTWÓRZ PROCEDURĘ ex_books_now (@year int \u003d 2006)
- zliczanie egzemplarzy książek w danym roku wydania
TAK JAK
Zadeklaruj @N_books int
select @N_books \u003d count (*) from books, exemplar
gdzie Books.ISBN \u003d exemplar.ISBN i YEARIZD \u003d @year
return coalesce (@N_books, 0)
UDAĆ SIĘ

Na rys. 9 przedstawia przykład wywołania tej procedury z parametrem wejściowym i bez niego.

Figa. 9. Wywołanie procedury składowanej z parametrem i bez niego

Wszystkie omówione powyżej przykłady użycia parametrów w procedurach składowanych podały tylko parametry wejściowe. Jednak parametry mogą być wyprowadzane. Oznacza to, że wartość parametru po zakończeniu procedury zostanie przekazana temu, kto wywołał tę procedurę (inna procedura, wyzwalacz, paczka poleceń itp.). Oczywiście, aby uzyskać parametr wyjściowy, podczas wywoływania należy określić nie stałą, ale zmienną jako rzeczywisty parametr.

Zauważ, że zdefiniowanie parametru jako wyjścia w procedurze nie zobowiązuje Cię do używania go jako takiego. Oznacza to, że jeśli określisz stałą jako parametr rzeczywisty, błąd nie wystąpi i będzie używany jako normalny parametr wejściowy.

Instrukcja OUTPUT służy do wskazania, że \u200b\u200bparametr jest parametrem wyjściowym. To słowo kluczowe jest zapisywane po opisie parametru. Przy opisywaniu parametrów procedur składowanych pożądane jest określenie wartości parametrów wyjściowych po parametrach wejściowych.

Rozważmy przykład użycia parametrów wyjściowych. Napiszmy procedurę składowaną, która dla danej książki oblicza całkowitą liczbę jej egzemplarzy w bibliotece oraz liczbę egzemplarzy wolnych. Nie możemy tutaj użyć operatora RETURN, ponieważ zwraca on tylko jedną wartość, więc musimy tutaj zdefiniować parametry wyjściowe. Tekst procedury składowanej może wyglądać następująco:

UTWÓRZ procedurę Count_books_all
(@ISBN varchar (14), @all int wyjście, @free int wyjście)
- procedura obliczania łącznej liczby egzemplarzy danej książki
- oraz liczbę bezpłatnych egzemplarzy
Tak jak
- liczenie całkowitej liczby kopii
Wybierz @all \u003d count (*) z Exemplar Where ISBN \u003d @ISBN
Wybierz @free \u003d count (*) z Exemplar Where ISBN \u003d @ISBN and Yes_No \u003d "1"
UDAĆ SIĘ

Przykład tej procedury pokazano na ryc. dziesięć.

Figa. 10. Testowanie procedury składowanej z parametrami wyjściowymi

Jak wspomniano wcześniej, aby uzyskać wartości parametrów wyjściowych do analizy, musimy ustawić je za pomocą zmiennych, a zmienne te muszą być opisane za pomocą instrukcji Declare. Ostatnia instrukcja wyjściowa pozwoliła nam po prostu wyświetlić wynikowe wartości.

Parametrami procedury mogą być nawet zmienne typu Cursor. W tym celu zmienna musi być opisana jako specjalny typ danych VARYING, bez powiązania ze standardowymi typami danych systemowych. Ponadto należy określić, że jest to zmienna Cursor.

Napiszmy prostą procedurę, która wymienia książki w naszej bibliotece. W takim przypadku, jeśli nie ma więcej niż trzy książki, wyświetlamy ich nazwy w ramach samej procedury, a jeśli lista książek przekracza określoną liczbę, przenosimy je jako kursor do wywołującego programu lub modułu.

Tekst procedury wygląda następująco:

UTWÓRZ PROCEDURĘ GET3TITLES
(@MYCURSOR CURSOR VARYING OUTPUT)
- procedura drukowania tytułów książek z kursorem
TAK JAK
- zdefiniuj w procedurze zmienną lokalną typu Cursor
SET @MYCURSOR \u003d CURSOR
WYBIERZ WYRÓŻNIONY TYTUŁ
Z KSIĄŻEK
- otwórz kursor
OPEN @MYCURSOR
- opisujemy wewnętrzne zmienne lokalne
ZADEKLARUJ @TITLE VARCHAR (80), @CNT INT
--- ustaw początkowy stan licznika książek
SET @CNT \u003d 0
- przejdź do pierwszej linii kursora
- gdy są linie kursora,
- to znaczy o ile podział wiersza jest poprawny
WHILE (@@ FETCH_STATUS \u003d 0) AND (@CNT<= 2) BEGIN
PRINT @TITLE
FETCH NEXT FROM @MYCURSOR INTO @TITLE
- zmień stan licznika książek
USTAW @CNT \u003d @CNT + 1
KONIEC
IF @CNT \u003d 0 PRINT "BRAK ODPOWIEDNICH KSIĄŻEK"
UDAĆ SIĘ

Przykład wywołania tej procedury składowanej pokazano na rys. jedenaście.

W procedurze wywołującej kursor musi być zadeklarowany jako zmienna lokalna. Następnie wywołaliśmy naszą procedurę i przekazaliśmy jej nazwę zmiennej lokalnej typu Cursor. Procedura zaczęła działać i wyświetlała nam pierwsze trzy nazwy, a następnie przekazała kontrolę do procedury wywołującej, a ta procedura kontynuowała przetwarzanie kursora. Aby to zrobić, zorganizowała pętlę While na zmiennej globalnej @@ FETCH_STATUS, która śledzi stan kursora, a następnie zrzuciła wszystkie pozostałe linie kursora w pętli.

W oknie wyników widzimy zwiększone odstępy między pierwszymi trzema wierszami i kolejnymi tytułami. Ten przedział pokazuje tylko, że sterowanie zostało przekazane do programu zewnętrznego.

Zwróć uwagę, że zmienna @TITLE, będąca lokalną dla procedury, zostanie zniszczona po zakończeniu działania, więc jest ponownie zadeklarowana w bloku wywołującym. Tworzenie i otwieranie kursora w tym przykładzie odbywa się w procedurze, podczas gdy zamykanie, niszczenie i dodatkowe przetwarzanie są wykonywane w bloku poleceń, w którym wywoływana jest procedura.

Najłatwiej przeglądać tekst procedury, zmieniać lub usuwać go za pomocą interfejsu graficznego Enterprise Manager. Można to jednak zrobić również za pomocą specjalnych procedur składowanych w systemie Transact-SQL. W języku Transact-SQL procedura systemowa sp_helptext służy do przeglądania definicji procedury, a procedura systemowa sp_help wyświetla informacje o procedurze inspekcji. Procedury systemowe sp_helptext i sp_help są również używane do przeglądania obiektów bazy danych, takich jak tabele, reguły i ustawienia domyślne.

Informacje o wszystkich wersjach jednej procedury, niezależnie od liczby, są wyświetlane natychmiast. Usuwanie różnych wersji tej samej procedury składowanej również odbywa się w tym samym czasie. Poniższy przykład przedstawia sposób wyświetlania definicji Count_Ex_all w wersji 1 i 2, gdy jego nazwa jest określona jako parametr procedury systemowej sp_helptext (Rysunek 12).

Figa. 12. Wyświetlanie treści procedury składowanej przy użyciu systemowej procedury składowanej

Procedura systemowa SP_HELP wyświetla charakterystykę i parametry utworzonej procedury w następującej postaci:

Imię Właściciel Rodzaj	Created_datetime
Count_books_all dbo procedura składowana	2006-12-06 23:15:01.217
Nazwa parametru Rodzaj Długość Prec	Skala	Param_order Collation
@ISBN varchar 14 14	ZERO	1	Cyrillic_General_CI_AS
@wszystko int 4 10	0	2	ZERO
@wolny int 4 10	0	3	ZERO

Spróbuj samodzielnie zdekodować te parametry. O czym oni rozmawiają?

1.4. Kompilowanie procedury składowanej

Zaletą używania procedur składowanych do wykonywania zestawu instrukcji języka Transact-SQL jest to, że są one kompilowane przy pierwszym wykonaniu. Podczas kompilacji instrukcje języka Transact-SQL są konwertowane z ich oryginalnej reprezentacji symbolicznej do postaci wykonywalnej. Wszystkie obiekty, do których uzyskano dostęp w ramach procedury, są również konwertowane na alternatywną reprezentację. Na przykład nazwy tabel są konwertowane na identyfikatory obiektów, a nazwy kolumn na identyfikatory kolumn.

Plan wykonania jest tworzony w taki sam sposób, jak w przypadku wykonywania pojedynczej instrukcji języka Transact-SQL. Ten plan zawiera na przykład indeksy używane do odczytywania wierszy z tabel, do których uzyskuje dostęp procedura. Plan wykonania procedury jest zapisywany w pamięci podręcznej i używany przy każdym jej wywołaniu.

Uwaga: Pamięć podręczna procedur może być tak dobrana, aby zawierała większość lub wszystkie dostępne procedury. Oszczędza to czas potrzebny do zregenerowania planu leczenia.

1.5. Automatyczna rekompilacja

Zwykle plan wykonania znajduje się w pamięci podręcznej procedur. Pozwala to zwiększyć wydajność podczas jej wykonywania. Jednak w pewnych okolicznościach procedura zostanie automatycznie ponownie skompilowana.

• Procedura jest zawsze ponownie kompilowana podczas uruchamiania programu SQL Server. Zwykle dzieje się to po ponownym uruchomieniu systemu operacyjnego i pierwszym uruchomieniu procedury po utworzeniu.
• Plan wykonania procedury jest zawsze automatycznie rekompilowany, jeśli indeks tabeli, do której procedura uzyskuje dostęp, zostanie usunięty. Ponieważ bieżący plan uzyskuje dostęp do indeksu, który już nie istnieje, aby odczytywać wiersze tabeli, należy utworzyć nowy plan wykonania. Zapytania proceduralne będą uruchamiane tylko wtedy, gdy zostaną zaktualizowane.
• Plan wykonania jest również kompilowany, jeśli inny użytkownik pracuje obecnie z planem w pamięci podręcznej. Dla drugiego użytkownika tworzona jest indywidualna kopia planu wykonania. Gdyby pierwsza kopia planu nie była zajęta, nie byłoby potrzeby tworzenia drugiej kopii. Gdy użytkownik zakończy wykonywanie procedury, plan wykonania jest dostępny w pamięci podręcznej dla innego użytkownika z odpowiednim uprawnieniem dostępu.
• Procedura jest automatycznie rekompilowana, jeśli zostanie usunięta i ponownie utworzona. Ponieważ nowa procedura może różnić się od starej wersji, wszystkie kopie planu wykonania w pamięci podręcznej są usuwane, a plan jest ponownie kompilowany.

SQL Server stara się optymalizować procedury składowane poprzez buforowanie najczęściej używanych procedur. Dlatego zamiast nowego planu można użyć starego planu wykonania załadowanego do pamięci podręcznej. Aby zapobiec temu problemowi, należy usunąć i ponownie utworzyć procedurę składowaną lub zatrzymać i ponownie aktywować SQL Server. Spowoduje to wyczyszczenie pamięci podręcznej procedur i wyeliminuje możliwość pracy ze starym planem wykonania.

Procedurę można również utworzyć za pomocą opcji Z REKOMPILOWANĄ. W takim przypadku dokompiluje się automatycznie przy każdym wykonaniu. Opcji WITH RECOMPILE należy używać, gdy procedura ma dostęp do bardzo dynamicznych tabel, których wiersze są często dodawane, usuwane lub aktualizowane, ponieważ powoduje to istotne zmiany w indeksach zdefiniowanych dla tabel.

Jeśli procedury nie zostaną automatycznie ponownie skompilowane, można je wymusić. Na przykład, jeśli statystyki używane do określenia, czy indeks może być użyty w danym zapytaniu, zostały zaktualizowane lub jeśli utworzono nowy indeks, należy przeprowadzić wymuszoną rekompilację. Aby wymusić ponowną kompilację, instrukcja EXECUTE używa klauzuli WITH RECOMPILE:

EXECUTE nazwa_procedury;
TAK JAK
<инструкции Transact-SQL>
Z RECOMPILE

Jeżeli procedura operuje parametrami sterującymi kolejnością jej wykonywania, użyj opcji Z REKOMPILEM. Jeśli parametry procedury składowanej mogą określić najlepszy sposób jej wykonania, zaleca się generowanie planu wykonania w locie, zamiast tworzenia go przy pierwszym wywołaniu procedury do użycia we wszystkich kolejnych wywołaniach.

Uwaga: czasami trudno jest określić, czy podczas tworzenia procedury użyć opcji WITH RECOMPILE, czy nie. W razie wątpliwości najlepiej nie używać tej opcji, ponieważ ponowna kompilacja procedury za każdym razem, gdy jest ona wykonywana, spowoduje stratę bardzo cennego czasu procesora. Jeśli w przyszłości zajdzie potrzeba ponownej kompilacji podczas wykonywania procedury składowanej, można to zrobić, dodając klauzulę WITH RECOMPILE do instrukcji EXECUTE.

Nie można użyć opcji WITH RECOMPILE w instrukcji CREATE PROCEDURE zawierającej opcję FOR REPLICATION. Ta opcja służy do tworzenia procedury, która jest wykonywana podczas procesu replikacji.

1.6. Zagnieżdżanie procedur składowanych

Procedury składowane mogą wywoływać inne procedury składowane, ale istnieje ograniczenie na poziomie zagnieżdżenia. Maksymalny poziom zagnieżdżenia to 32. Bieżący poziom zagnieżdżenia można określić za pomocą zmiennej globalnej @@ NESTLEVEL.

2. Funkcje zdefiniowane przez użytkownika (UDF)

MS SQL SERVER 2000 posiada wiele predefiniowanych funkcji, które umożliwiają wykonywanie różnorodnych czynności. Jednak zawsze może być konieczne użycie określonych funkcji. W tym celu, począwszy od wersji 8.0 (2000), stało się możliwe opisywanie funkcji zdefiniowanych przez użytkownika (UDF) i przechowywanie ich jako pełnoprawnych obiektów bazy danych wraz z procedurami składowanymi, widokami itp.

Użyteczność funkcji zdefiniowanych przez użytkownika jest oczywista. W przeciwieństwie do procedur składowanych, funkcje mogą być osadzane bezpośrednio w instrukcji SELECT i mogą być używane zarówno do pobierania określonych wartości (w klauzuli SELECT), jak i jako źródło danych (w klauzuli FROM).

W przypadku korzystania z funkcji UDF jako źródeł danych ich przewaga nad widokami polega na tym, że w przeciwieństwie do widoków UDF mogą mieć parametry wejściowe, za pomocą których można wpływać na wynik funkcji.

Funkcje zdefiniowane przez użytkownika mogą mieć trzy typy: funkcje skalarne, funkcje inline i funkcje złożone z wielu instrukcji zwracające wynik w tabeli... Rozważmy bardziej szczegółowo wszystkie te typy funkcji.

2.1. Funkcje skalarne

Funkcje skalarne zwracają pojedynczy wynik skalarny. Ten wynik może być dowolnym z typów opisanych powyżej, z wyjątkiem tekstu, ntext, obrazu i znacznika czasu. To najprostszy rodzaj funkcji. Jego składnia jest następująca:

RETURNS scalar_data_type

ZACZYNAĆ
ciało_funkcji
RETURN scalar_expression
KONIEC

• Parametr ENCRYPTION został już opisany w sekcji dotyczącej procedur składowanych;
• SCHEMABINDING - wiąże funkcję ze schematem. Oznacza to, że nie można usunąć tabel lub widoków, na których jest oparta funkcja, bez usunięcia lub zmodyfikowania samej funkcji. Nie można również zmienić struktury tych tabel, jeśli zmieniona część jest używana przez funkcję. W ten sposób opcja ta pozwala wykluczyć sytuacje, w których funkcja korzysta z jakichkolwiek tabel lub widoków, a ktoś nieświadomie je usunął lub zmienił;
• RETURNS scalar_data_type - opisuje typ danych zwracanych przez funkcję;
• scalar_expression - wyrażenie, które bezpośrednio zwraca wynik wykonania funkcji. Musi być tego samego typu, co opisywany po ZWROTACH;
• function_body to zestaw instrukcji języka Transact-SQL.

Spójrzmy na przykłady użycia funkcji skalarnych.

Utwórz funkcję, która wybiera najmniejszą z dwóch liczb całkowitych podanych jako parametry.

Niech funkcja wygląda tak:

UTWÓRZ FUNKCJĘ min_num (@a INT, @b INT)
ZWROTY INT
ZACZYNAĆ
ZADEKLAROWAĆ @c INT
Jeśli< @b SET @c = @a
ELSE SET @c \u003d @b
RETURN @c
KONIEC

Wykonajmy teraz tę funkcję:

SELECT dbo.min_num (4, 7)

W rezultacie otrzymujemy wartość 4.

Możesz użyć tej funkcji, aby znaleźć najmniejszą wartość wśród kolumn tabeli:

SELECT min_lvl, max_lvl, min_num (min_lvl, max_lvl)
FROM Jobs

Stwórzmy funkcję, która jako dane wejściowe otrzyma parametr typu datetime i zwróci datę i godzinę odpowiadającą początkowi określonego dnia. Na przykład, jeśli parametr wejściowy to 09.20.03 13:31, wynikiem będzie 09.20.03 00:00.

UTWÓRZ FUNKCJĘ dbo.daybegin (@dat DATETIME)
ZWRACA smalldatetime AS
ZACZYNAĆ
RETURN CONVERT (datetime, FLOOR (konwertuj (FLOAT, @dat)))
KONIEC

W tym przypadku funkcja CONVERT wykonuje konwersję typu. Najpierw typ daty i godziny jest rzutowany na typ FLOAT. Przy tej redukcji część całkowita to liczba dni, licząc od 1 stycznia 1900 roku, a część ułamkowa to czas. Następnie zaokrąglanie do najmniejszej liczby całkowitej następuje przy użyciu funkcji PODŁOGA i rzutowaniu na typ daty i godziny.

Sprawdźmy działanie funkcji:

SELECT dbo.daybegin (GETDATE ())

Tutaj GETDATE () jest funkcją, która zwraca bieżącą datę i godzinę.

Poprzednie funkcje używały tylko parametrów wejściowych w obliczeniach. Możesz jednak również skorzystać z danych przechowywanych w bazie danych.

Stwórzmy funkcję, która przyjmie dwie daty jako parametry: początek i koniec przedziału czasu - i obliczmy całkowity przychód ze sprzedaży dla tego przedziału. Data sprzedaży i ilość zostaną pobrane z tabeli Sprzedaż, a ceny za sprzedane tytuły zostaną pobrane z tabeli Tytuły.

UTWÓRZ FUNKCJĘ dbo.SumSales (@datebegin DATETIME, @dateend DATETIME)
ZWRACA pieniądze
TAK JAK
ZACZYNAĆ
ZADEKLARUJ @Sum Money
SELECT @Sum \u003d sum (t.price * s.qty)

RETURN @Sum
KONIEC

2.2. Funkcje wbudowane

Ten rodzaj funkcji zwraca w rezultacie nie wartość skalarną, ale tabelę, a raczej zestaw danych. Może to być bardzo wygodne w przypadkach, gdy ten sam typ podzapytania jest często wykonywany w różnych procedurach, wyzwalaczach itp. Następnie zamiast pisać to zapytanie wszędzie, możesz utworzyć funkcję i używać jej w przyszłości.

Funkcje tego typu są jeszcze bardziej przydatne w przypadkach, gdy chcesz, aby zwracana tabela zależała od parametrów wejściowych. Jak wiesz, widoki nie mogą mieć parametrów, więc tego rodzaju problem można rozwiązać tylko za pomocą funkcji wbudowanych.

Osobliwością funkcji wbudowanych jest to, że mogą zawierać tylko jedno żądanie w swoim ciele. Dlatego funkcje tego typu są bardzo podobne do widoków, ale mogą dodatkowo mieć parametry wejściowe. Składnia funkcji wbudowanej:

UTWÓRZ FUNKCJĘ [właściciel.] Nazwa_funkcji
([(@ nazwa_parametru typ_danych_skalarnych [\u003d wartość_domyślna]) [, ... n]])
TABELA ZWROTÓW

POWRÓT [(<запрос>)]

Definicja funkcji stwierdza, że \u200b\u200bzwróci ona tabelę;<запрос> - jest to żądanie, którego wynikiem będzie wynik funkcji.

Napiszmy funkcję podobną do funkcji skalarnej z poprzedniego przykładu, ale zwracającą nie tylko wynik sumowania, ale także wiersze sprzedaży, w tym datę sprzedaży, tytuł książki, cenę, liczbę sztuk i sprzedaną kwotę. Należy wybierać tylko te sprzedaże, które obejmują określony okres. Zaszyfrujmy tekst funkcyjny, aby inni użytkownicy mogli z niego korzystać, ale nie mogą go odczytać i poprawić:

UTWÓRZ FUNKCJĘ Sales_Period (@datebegin DATETIME, @dateend DATETIME)
TABELA ZWROTÓW
Z SZYFROWANIEM
TAK JAK
POWRÓT (
SELECT t.title, t.price, s.qty, ord_date, t.price * s.qty as stoim
OD tytułów t DOŁĄCZ Sprzedaż na t.title_Id \u003d s.Title_ID
GDZIE ord_date POMIĘDZY @datebegin i @dateend
)

Teraz nazwijmy tę funkcję. Jak już wspomniano, można go wywołać tylko w klauzuli FROM instrukcji SELECT:

SELECT * FROM Sales_Period ("01.09.94", "13.09.94")

2.3. Funkcje zawierające wiele instrukcji zwracające wynik w tabeli

Pierwszy rozważany typ funkcji umożliwiał użycie dowolnej liczby instrukcji języka Transact-SQL, ale zwracał tylko wynik skalarny. Funkcja drugiego typu może zwracać tabele, ale jej treść reprezentuje tylko jedno zapytanie. Funkcje z wieloma operatorami zwracające wynik tabeli pozwalają łączyć właściwości dwóch pierwszych funkcji, to znaczy mogą zawierać wiele instrukcji języka Transact-SQL w treści i zwracać tabelę jako wynik. Składnia funkcji wielu operatorów:

UTWÓRZ FUNKCJĘ [właściciel.] Nazwa_funkcji
([(@ nazwa_parametru typ_danych_skalarnych [\u003d wartość_domyślna]) [, ... n]])
ZWROTY @ nazwa_zmiennej_wyniku TABELA
<описание_таблицы>

ZACZYNAĆ
<тело_функции>
POWRÓT
KONIEC

• STÓŁ<описание_таблицы> - opisuje strukturę zwracanej tabeli;
• <описание_таблицы> - zawiera wyliczenie kolumn i ograniczeń.

Spójrzmy teraz na przykład, który można wykonać tylko przy użyciu funkcji tego typu.

Niech będzie drzewo katalogów i plików w nich leżących. Niech cała ta konstrukcja zostanie opisana w bazie danych w postaci tabel (Rys. 13). W rzeczywistości mamy tutaj hierarchiczną strukturę katalogów, więc diagram pokazuje relację tabeli Folders ze sobą.

Figa. 13. Struktura bazy danych opisująca hierarchię plików i katalogów

Teraz napiszmy funkcję, która pobierze identyfikator katalogu jako dane wejściowe i wyprowadzi wszystkie pliki, które są w nim przechowywane i we wszystkich katalogach w dół hierarchii. Przykładowo, jeśli w katalogu Instytutu tworzone są katalogi Wydział1, Wydział2 itd., To mają one katalogi wydziałowe, a każdy z katalogów zawiera pliki, to przy podaniu identyfikatora katalogu Instytutu jako parametru naszej funkcji lista wszystkich plików dla wszystkich te katalogi. Nazwa, rozmiar i data utworzenia powinny być wyświetlane dla każdego pliku.

Nie można rozwiązać problemu za pomocą funkcji inline, ponieważ SQL nie jest zaprojektowany do wykonywania zapytań hierarchicznych, więc jedno zapytanie SQL nie wystarczy. Nie można również zastosować funkcji skalarnej, ponieważ wynik musi być tabelą. Tutaj z pomocą przyjdzie nam funkcja multi-operator, która zwraca tabelę:

UTWÓRZ FUNKCJĘ dbo.GetFiles (@Folder_ID int)
RETURNS @files TABLE (nazwa VARCHAR (100), Date_Create DATETIME, FileSize INT) AS
ZACZYNAĆ
ZADEKLAROWAĆ @tmp TABELĘ (Folder_Id int)
ZADEKLAROWAĆ @Cnt INT
INSERT INTO @tmp values \u200b\u200b(@Folder_ID)
SET @Cnt \u003d 1
WHILE @Cnt<> 0 BEGIN
INSERT INTO @tmp SELECT Folder_Id
Z folderów f DOŁĄCZ @tmp t ON f.parent \u003d t.Folder_ID
WHERE F.id NOT IN (SELECT Folder_ID FROM @tmp)
SET @Cnt \u003d @@ ROWCOUNT
KONIEC
INSERT INTO @Files (Name, Date_Create, FileSize)
SELECT F.Name, F.Date_Create, F.FileSize
Z plików f DOŁĄCZ Foldery Fl on f.Folder_id \u003d Fl.id
DOŁĄCZ @tmp t on Fl.id \u003d t.Folder_Id
POWRÓT
KONIEC

Tutaj, w pętli, wszystkie podkatalogi na wszystkich poziomach zagnieżdżenia są dodawane do zmiennej @tmp, aż nie będzie już żadnych podkatalogów. Następnie zmienna wynikowa @Files przechowuje wszystkie niezbędne atrybuty plików w katalogach wymienionych w zmiennej @tmp.

Zadania do samodzielnej nauki

Należy utworzyć i debugować pięć procedur składowanych z następującej wymaganej listy:

Procedura 1. Wydłuż termin wykonania kopii książki o jeden tydzień, jeśli bieżący termin wypada między trzema dniami przed bieżącą datą a trzema dniami po bieżącej dacie.

Procedura 2. Obliczanie liczby bezpłatnych egzemplarzy danej książki.

Procedura 3. Sprawdzenie istnienia czytelnika o podanym nazwisku i dacie urodzenia.

Procedura 4. Wprowadzenie nowego czytnika, sprawdzenie jego istnienia w bazie danych i ustalenie nowego numeru karty bibliotecznej.

Procedura 5. Obliczenie kary pieniężnej dla czytelników-dłużników.

Krótki opis procedur

Procedura 1. Wydłużenie terminu wymagalności ksiąg

Dla każdego rekordu w tabeli Wzory sprawdzane jest, czy termin wykonania książki przypada w określonym przedziale czasu. Jeśli tak, to termin zwrotu książki wydłuża się o tydzień. Wykonując procedurę należy skorzystać z funkcji do pracy z datami:

DateAdd (dzień,<число добавляемых дней>, <начальная дата>)

Procedura 2. Obliczanie liczby bezpłatnych egzemplarzy danej książki

Parametrem wejściowym procedury jest ISBN - unikalny szyfr książkowy. Procedura zwraca 0 (zero), jeśli wszystkie egzemplarze tej książki są w rękach czytelników. Procedura zwraca wartość N równą liczbie kopii książki, które aktualnie znajdują się w rękach czytelników.

Jeśli książki o podanym numerze ISBN nie ma w bibliotece, procedura zwraca –100 (minus sto).

Procedura 3. Sprawdzenie istnienia czytelnika o podanym nazwisku i dacie urodzenia

Procedura zwraca numer biletu czytelnika, jeśli istnieje czytnik z takimi danymi, aw przeciwnym razie 0 (zero).

Porównując datę urodzenia, należy użyć funkcji konwersji Convert (), aby przekonwertować datę urodzenia, zmienną znakową typu Varchar (8) używaną jako parametr wejściowy procedury, na dane typu datatime, które są używane w tabeli Readers. W przeciwnym razie operacja porównania nie zadziała podczas wyszukiwania danego czytnika.

Procedura 4. Wprowadzanie nowego czytnika

Procedura ma pięć parametrów wejściowych i trzy parametry wyjściowe.

Parametry wejściowe:

• Imię i nazwisko z inicjałami;
• Adres;
• Data urodzenia;
• Telefon domowy;
• Telefon działa.

Parametry wyjściowe:

• Numer karty bibliotecznej;
• Wskazanie, czy czytelnik był wcześniej zapisany w bibliotece (0 - nie było, 1 - było);
• Liczba książek, które posiada czytelnik.

Procedura 5. Obliczenie kary pieniężnej dla czytelników-dłużników

Procedura działa z kursorem zawierającym listę numerów kart bibliotecznych wszystkich dłużników. W toku prac należy stworzyć globalną tymczasową tabelę ## DOLG, w której dla każdego dłużnika zostanie wpisany jego całkowity dług w ujęciu pieniężnym dla wszystkich ksiąg, które posiadał dłużej niż okres spłaty. Rekompensata pieniężna wynosi 0,5% ceny książki za dzień opóźnienia.

Porządek pracy

• zrzuty ekranu (zrzuty ekranu) potwierdzające zmiany wprowadzone w bazach danych;
• zawartość tabel bazy danych, które są wymagane do sprawdzenia poprawności operacji;
• tekst procedury składowanej z komentarzami;
• proces uruchamiania procedury składowanej z danymi wyjściowymi wyników pracy.

Dodatkowe zadania

Poniższe dodatkowe procedury składowane dotyczą poszczególnych zadań.

Procedura 6. Zliczenie liczby książek z danego obszaru tematycznego, które są aktualnie dostępne w bibliotece w co najmniej jednym egzemplarzu. Temat jest przekazywany jako parametr wejściowy.

Procedura 7. Wprowadzenie nowej książki ze wskazaniem liczby kopii. Wprowadzając egzemplarze nowej książki, pamiętaj, aby wprowadzić ich prawidłowe numery magazynowe. Pomyśl, jak możesz to zrobić. Przypominamy, że masz funkcje Max i Min, które pozwalają znaleźć maksymalną lub minimalną wartość dowolnego atrybutu liczbowego za pomocą zapytania wybierającego.

Procedura 8. Utworzenie tabeli z listą czytelników-dłużników, czyli tych, którzy musieli zwrócić książki do biblioteki, ale jeszcze nie wrócili. W wynikowej tabeli każdy czytelnik-dłużnik powinien pojawić się tylko raz, niezależnie od tego, ile książek jest winien. Oprócz pełnej nazwy i numeru karty bibliotecznej w wynikowej tabeli należy podać adres i numer telefonu.

Procedura 9. Wyszukiwanie bezpłatnego egzemplarza według podanego tytułu książki. Jeśli istnieje bezpłatna kopia, procedura zwraca numer inwentarzowy instancji; jeśli nie, procedura zwraca listę czytelników, którzy mają tę książkę, ze wskazaniem daty zwrotu książki i numeru telefonu czytelnika.

Procedura 10. Lista czytelników, którzy aktualnie nie posiadają żadnych książek. Podaj nazwę i numer telefonu na liście.

Procedura 11. Wyświetlenie listy książek ze wskazaniem liczby egzemplarzy tej książki w bibliotece oraz liczby egzemplarzy bezpłatnych w danym momencie.

wersja do druku

Procedury składowane SQL to wykonywalna jednostka programu, która może być przechowywana jako różne obiekty. Innymi słowy, jest to obiekt zawierający instrukcje SQL. Te procedury składowane mogą być wykonywane w kliencie aplikacji, aby uzyskać dobrą wydajność. Ponadto takie obiekty są często wywoływane z innych skryptów lub nawet z innej sekcji.

Wprowadzenie

Wiele osób uważa, że \u200b\u200bsą one podobne do różnych procedur (odpowiednio z wyjątkiem MS SQL). Może to prawda. Mają podobne parametry i mogą dawać podobne wartości. Co więcej, w wielu przypadkach się dotykają. Na przykład można je łączyć z bazami danych DDL i DML, a także funkcjami użytkownika (o nazwie kodowej UDF).

W rzeczywistości procedury składowane SQL mają wiele zalet, które odróżniają je od podobnych procesów. Bezpieczeństwo, zmienność programowania, produktywność - wszystko to coraz bardziej przyciąga użytkowników pracujących z bazami danych. Szczyt popularności procedur przypadł na lata 2005-2010, kiedy to pojawił się program firmy Microsoft o nazwie „SQL Server Management Studio”. Z jego pomocą praca z bazami danych stała się dużo łatwiejsza, bardziej praktyczna i wygodniejsza. Z roku na rok zyskiwało to popularność wśród programistów. Dziś jest to absolutnie znany program, który dla użytkowników „komunikujących się” z bazami danych, wyrósł na równi z Excelem.

Gdy wywoływana jest procedura, jest ona natychmiast przetwarzana przez sam serwer, bez zbędnych procesów i interwencji użytkownika. Następnie możesz przeprowadzić dowolne usunięcie, wykonanie, zmianę. Za wszystko to odpowiada operator DDL, który samodzielnie wykonuje najbardziej złożone operacje przetwarzania obiektów. A wszystko to dzieje się bardzo szybko, a serwer w rzeczywistości nie jest załadowany. Taka prędkość i wydajność pozwalają na bardzo szybkie przesyłanie dużej ilości informacji od użytkownika na serwer i odwrotnie.

Aby wdrożyć tę technologię do pracy z informacjami, istnieje kilka języków programowania. Należą do nich np. PL / SQL firmy Oracle, PSQL w systemach InterBase i Firebird, a także klasyczny „Microsoft” Transact-SQL. Wszystkie są przeznaczone do tworzenia i wykonywania procedur składowanych, co umożliwia dużym programom obsługi baz danych używanie własnych algorytmów. Jest to również konieczne, aby osoby zarządzające takimi informacjami mogły chronić wszystkie obiekty przed nieuprawnionym dostępem osób trzecich, a tym samym przed tworzeniem, modyfikacją lub usuwaniem niektórych danych.

Wydajność

Te obiekty bazy danych można programować na różne sposoby. Dzięki temu użytkownicy mogą wybrać najbardziej odpowiedni rodzaj stosowanej metody, co oszczędza czas i wysiłek. Ponadto procedura jest obsługiwana samodzielnie, co pozwala uniknąć ogromnego czasu spędzonego na wymianie między serwerem a użytkownikiem. Ponadto moduł można przeprogramować i zmienić w wybranym kierunku w dowolnym momencie. Szczególnie warto zwrócić uwagę na szybkość, z jaką uruchamiana jest procedura składowana SQL: proces ten jest szybszy niż inne, podobny do niego, co czyni go wygodnym i wszechstronnym.

Bezpieczeństwo

Ten rodzaj przetwarzania informacji różni się od podobnych procesów tym, że gwarantuje zwiększone bezpieczeństwo. Jest to możliwe dzięki temu, że dostęp innych użytkowników do procedur może być całkowicie i całkowicie wykluczony. Pozwoli to administratorowi na samodzielne prowadzenie z nimi operacji, bez obawy o przechwycenie informacji czy nieuprawniony dostęp do bazy danych.

Transfer danych

Relacja między procedurą składowaną SQL a aplikacją kliencką polega na wykorzystaniu parametrów i wartości zwracanych. Ten ostatni nie musi przekazywać danych do procedury składowanej, ale informacje te (głównie na żądanie użytkownika) są przetwarzane dla SQL. Po zakończeniu pracy procedura składowana wysyła pakiety danych z powrotem (ale ponownie opcjonalnie) do aplikacji wywołującej przy użyciu różnych metod, które mogą być używane zarówno do wywoływania procedury składowanej SQL, jak i zwracania, na przykład:

Przekazywanie danych za pomocą parametru typu Output;

Przesyłanie danych za pomocą instrukcji zwrotu;

Przekazywanie danych za pomocą operatora wyboru.

A teraz zastanówmy się, jak ten proces wygląda od wewnątrz.

1. Tworzenie procedury składowanej EXEC w języku SQL

Możesz stworzyć procedurę w MS SQL (Managment Studio). Po utworzeniu procedura zostanie wyświetlona w programowalnym węźle bazy danych, w którym procedura tworzenia jest wykonywana przez operatora. Procedury składowane SQL używają do wykonania procesu EXEC, który zawiera nazwę samego obiektu.

Podczas tworzenia procedury jej nazwa pojawia się jako pierwsza, po czym generowany jest jeden lub więcej przypisanych do niej parametrów. Parametry mogą być opcjonalne. Po zapisaniu parametrów, czyli treści procedury, należy wykonać kilka niezbędnych operacji.

Chodzi o to, że ciało może mieć zlokalizowane w sobie zmienne lokalne, a zmienne te są również lokalne dla procedur. Innymi słowy, można je przeglądać tylko w treści procedury Microsoft SQL Server. W tym przypadku procedury składowane są uważane za lokalne.

Tak więc, aby utworzyć procedurę, potrzebujemy nazwy procedury i co najmniej jednego parametru jako treści procedury. Zauważ, że świetną opcją w tym przypadku jest utworzenie i wykonanie procedury o nazwie schema w klasyfikatorze.

Treść procedury może być dowolnego rodzaju, na przykład może obejmować tworzenie tabeli, wstawianie jednego lub więcej wierszy tabeli, ustalanie typu i charakteru bazy danych itd. Jednak treść procedury ogranicza wykonywanie w niej niektórych operacji. Poniżej wymieniono niektóre z ważnych ograniczeń:

Treść nie powinna tworzyć żadnej innej procedury składowanej;

Ciało nie może stwarzać fałszywego wrażenia przedmiotu;

Ciało nie powinno tworzyć żadnych wyzwalaczy.

2. Ustawienie zmiennej w treści procedury

Możesz ustawić zmienne jako lokalne dla treści procedury, a wtedy będą one zlokalizowane wyłącznie w treści procedury. Dobrą praktyką jest tworzenie zmiennych na początku treści procedury składowanej. Ale możesz także ustawić zmienne w dowolnym miejscu w treści tego obiektu.

Czasami zauważysz, że kilka zmiennych jest ustawionych w jednej linii, a każdy parametr zmiennej jest oddzielony przecinkiem. Zwróć również uwagę, że zmienna ma prefiks @. W treści procedury możesz ustawić zmienną w dowolnym miejscu. Na przykład @ NAME1 można zadeklarować na końcu treści procedury. W celu nadania wartości zadeklarowanej zmiennej wykorzystywany jest zestaw danych osobowych. W przeciwieństwie do sytuacji, w której w jednym wierszu zadeklarowano więcej niż jedną zmienną, w takiej sytuacji używany jest tylko jeden zestaw danych osobowych.

Często użytkownicy zadają pytanie: „Jak przypisać wiele wartości w jednej instrukcji w treści procedury?” Dobrze. Ciekawe pytanie, ale jest o wiele łatwiejsze niż myślisz. Odpowiedź: używając par, takich jak „Wybierz zmienną \u003d wartość”. Możesz użyć tych par, oddzielając je przecinkami.

Na wielu różnych przykładach ludzie pokazują, jak utworzyć prostą procedurę składowaną i wykonać ją. Jednak procedura może przyjmować takie parametry, że proces wywołujący będzie miał wartości zbliżone do siebie (ale nie zawsze). Jeśli się pokrywają, odpowiednie procesy rozpoczynają się w ciele. Na przykład, jeśli utworzysz procedurę, która zaakceptuje miasto i region od dzwoniącego i zwróci dane o liczbie autorów należących do odpowiedniego miasta i regionu. Procedura wysyła zapytanie do tabel autorów bazy danych, na przykład Pubs, w celu obliczenia liczby autorów. Na przykład, aby uzyskać te bazy danych, Google pobiera skrypt SQL ze strony SQL2005.

W poprzednim przykładzie procedura przyjmuje dwa parametry, które w języku angielskim będą umownie nazwane @State i @City. Typ danych odpowiada typowi zdefiniowanemu w aplikacji. Ciało procedury zawiera zmienne wewnętrzne @TotalAuthors (ogółem autorów), a zmienna ta służy do wyświetlania ich liczby. Następnie pojawia się sekcja wyboru zapytania, która oblicza wszystko. Na koniec obliczona wartość jest wyświetlana w oknie wyjściowym za pomocą instrukcji print.

Jak wykonać procedurę składowaną w języku SQL

Istnieją dwa sposoby zakończenia procedury. Pierwsza ścieżka, przekazując parametry, pokazuje, w jaki sposób lista oddzielona przecinkami jest wykonywana po nazwie procedury. Powiedzmy, że mamy dwie wartości (jak w poprzednim przykładzie). Te wartości są zbierane przy użyciu parametrów procedury @State i @City. W tym sposobie przekazywania parametrów ważna jest kolejność. Ta metoda jest nazywana porządkowym przekazywaniem argumentów. W drugiej metodzie parametry są już bezpośrednio przypisane, w takim przypadku kolejność nie jest ważna. Ta druga metoda jest znana jako przekazywanie nazwanych argumentów.

Procedura może nieco odbiegać od typowej. Wszystko jest takie samo jak w poprzednim przykładzie, ale tutaj parametry są przesunięte. Oznacza to, że @City jest przechowywane jako pierwsze, a @State jest przechowywane obok wartości domyślnej. Domyślny parametr jest zwykle wyróżniony osobno. Procedury składowane SQL są przekazywane jako parametry. W tym przypadku, pod warunkiem, że parametr „UT” zastępuje domyślny „CA”. W drugim wykonaniu tylko jedna wartość argumentu jest przekazywana dla parametru @City, a parametr @State przyjmuje wartość domyślną „CA”. Doświadczeni programiści radzą, aby wszystkie domyślne zmienne znajdowały się blisko końca listy parametrów. W przeciwnym razie wykonanie nie jest możliwe i wtedy trzeba pracować z przekazywaniem nazwanych argumentów, co jest dłuższe i bardziej skomplikowane.

4. Procedury składowane programu SQL Server: metody zwrotu

Istnieją trzy ważne sposoby wysyłania danych w wywołanej procedurze składowanej. Są one wymienione poniżej:

Zwracanie wartości procedury składowanej;

Wyjście parametrów procedury składowanej;

Wybór jednej z procedur składowanych.

4.1 Zwracanie wartości procedur składowanych SQL

W tej technice procedura przypisuje wartość zmiennej lokalnej i zwraca ją. Procedura może również bezpośrednio zwrócić stałą wartość. W poniższym przykładzie utworzyliśmy procedurę zwracającą całkowitą liczbę autorów. Jeśli porównasz tę procedurę z poprzednimi, zobaczysz, że wartość wydruku jest odwrócona.

Zobaczmy teraz, jak wykonać procedurę i wydrukować zwracaną wartość. Wykonanie procedury wymaga ustawienia zmiennej i wydrukowania, które jest wykonywane po całym procesie. Należy pamiętać, że zamiast instrukcji print można użyć instrukcji Select, takiej jak Select @RetValue i OutputValue.

4.2 Parametry wyjściowe procedur składowanych SQL

Wartość zwracana może służyć do zwracania pojedynczej zmiennej, jak widzieliśmy w poprzednim przykładzie. Użycie parametru Output umożliwia procedurze wysłanie co najmniej jednej wartości zmiennej do obiektu wywołującego. Parametr wyjściowy jest oznaczany tak samo przez to słowo kluczowe „Wyjście” podczas tworzenia procedury. Jeśli parametr jest określony jako parametr wyjściowy, obiekt procedury musi przypisać mu wartość. Procedury składowane SQL, których przykłady można zobaczyć poniżej, są następnie zwracane z podsumowaniem.

Nasz przykład będzie miał dwie nazwy wyjściowe: @TotalAuthors i @TotalNoContract. Są one wskazane na liście parametrów. Zmiennym tym przypisywane są wartości w treści procedury. Kiedy używamy parametrów out, wywołujący może zobaczyć wartość ustawioną w treści procedury.

Ponadto w poprzednim skrypcie zadeklarowano dwie zmienne, aby zobaczyć wartości, które ustawiają procedury składowane MS SQL Server w parametrze out. Następnie procedura jest wykonywana poprzez podanie normalnej wartości parametru „CA”. Wyprowadzane są następujące parametry, a zatem deklarowane zmienne są przekazywane w określonej kolejności. Zwróć uwagę, że podczas przechodzenia przez zmienne słowo kluczowe output jest również ustawiane w tym miejscu. Po pomyślnym zakończeniu procedury wartości zwrócone przez parametry wyjściowe są wyświetlane w oknie komunikatu.

4.3 Wybór jednej z procedur składowanych SQL

Ta technika służy do zwracania zestawu wartości jako tabeli danych (RecordSet) do wywołującej procedury składowanej. W tym przykładzie SQL procedura składowana z parametrami @AuthID wysyła zapytanie do tabeli Authors, filtrując zwrócone rekordy za pomocą tego parametru @AuthId. Instrukcja Select decyduje, co powinno zostać zwrócone do obiektu wywołującego procedurę składowaną. Kiedy procedura składowana jest wykonywana, AuthId jest przekazywany z powrotem. Taka procedura zawsze zwraca tylko jeden rekord lub nie zwraca go wcale. Ale procedura składowana nie ma żadnych ograniczeń dotyczących zwracania więcej niż jednego rekordu. Nierzadko można spotkać przykłady, w których zwracanie danych przy użyciu wybranych parametrów obejmujących zmienne obliczeniowe odbywa się poprzez podanie wielu sum.

Wreszcie

Procedura składowana to dość poważny program, który zwraca lub przekazuje i ustawia wymagane zmienne za pośrednictwem aplikacji klienckiej. Ponieważ procedura składowana wykonuje się sama na serwerze, można uniknąć ogromnej wymiany danych między serwerem a aplikacją kliencką (w przypadku niektórych obliczeń). Pozwala to zmniejszyć obciążenie serwerów SQL, które oczywiście trafia w ręce ich posiadaczy. Procedury składowane T SQL są jednym z podgatunków, ale ich badanie jest niezbędne dla tych, którzy zajmują się tworzeniem imponujących baz danych. Istnieje również duża, a nawet ogromna liczba niuansów, które mogą być przydatne podczas studiowania procedur składowanych, ale jest to potrzebne bardziej dla tych, którzy planują ściśle zająć się programowaniem, w tym zawodowym.

Cel - dowiedzieć się, jak tworzyć i używać procedur składowanych na serwerze bazy danych.

1. Opracowanie wszystkich przykładów i przeanalizowanie wyników ich wykonania w narzędziu SQL Server Management Studio. Sprawdzanie obecności utworzonych procedur w bieżącej bazie danych.

2. Zaliczenie wszystkich przykładów i zadań w trakcie zajęć laboratoryjnych.

3. Realizacja indywidualnych zleceń według opcji.

Objaśnienia do pracy

Aby opanować programowanie procedur składowanych, użyjemy przykładu bazy danych o nazwie DB_Books, który powstał w pracy laboratoryjnej nr 1. Wykonując przykłady i zadania zwróć uwagę na zgodność nazw bazy danych, tabel i innych obiektów projektu.

Procedury składowane to zestaw poleceń składający się z co najmniej jednej instrukcji lub funkcji SQL, przechowywanych w bazie danych w skompilowanej formie.

Typy procedur składowanych

Systemowe procedury składowane są przeznaczone do wykonywania różnych czynności administracyjnych. Za ich pomocą wykonywane są prawie wszystkie czynności administracyjne na serwerze. Można powiedzieć, że systemowe procedury składowane są interfejsem do pracy z tabelami systemowymi. Systemowe procedury składowane mają prefiks sp_, są przechowywane w bazie danych systemu i można je wywołać w kontekście dowolnej innej bazy danych.

Niestandardowe procedury składowane implementują określone akcje. Procedury składowane to kompletny obiekt bazy danych. W rezultacie każda procedura składowana znajduje się w określonej bazie danych, w której jest wykonywana.

Tymczasowe procedury składowane istnieją tylko przez krótki czas, po czym są automatycznie niszczone przez serwer. Są podzielone na lokalne i globalne. Lokalne tymczasowe procedury składowane można wywoływać tylko z połączenia, w którym zostały utworzone. Tworząc taką procedurę należy nadać jej nazwę zaczynającą się od jednego znaku #. Podobnie jak wszystkie obiekty tymczasowe, procedury składowane tego typu są automatycznie usuwane po rozłączeniu, ponownym uruchomieniu lub zatrzymaniu serwera przez użytkownika. Globalne tymczasowe procedury składowane są dostępne dla wszystkich połączeń z serwerem, które mają tę samą procedurę. Aby go zdefiniować, wystarczy nadać mu nazwę zaczynającą się od symboli ##. Procedury te są usuwane po ponownym uruchomieniu lub zatrzymaniu serwera lub po zamknięciu połączenia, w kontekście którego zostały utworzone.

Tworzenie, modyfikowanie procedur składowanych

Utworzenie procedury składowanej obejmuje rozwiązanie następujących problemów: planowanie praw dostępu. Podczas tworzenia procedury składowanej należy pamiętać, że będzie ona miała takie same prawa dostępu do obiektów bazy danych, jak użytkownik, który ją utworzył; definicji parametrów procedury składowanej, procedury składowane mogą mieć parametry wejściowe i wyjściowe; opracowywanie kodu procedury składowanej. Kod procedury może zawierać dowolną sekwencję poleceń SQL, w tym wywołania innych procedur składowanych.

Składnia dla operatora do tworzenia nowej lub modyfikacji istniejącej procedury składowanej w notacji MS SQL Server:

(CREATE | ALTER) PROC [EDURE] nazwa_procedury [; numer] [(typ danych @ nazwa_parametru) [VARYING] [\u003d DEFAULT] [WYJŚCIE]] [, ... n] [WITH (RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION)] [DO REPLIKACJI] jako instrukcja sql_ [... n]

Rozważmy parametry tego polecenia.

Używając przedrostków sp_, #, ##, utworzoną procedurę można zdefiniować jako systemową lub tymczasową. Jak widać ze składni komendy, nie można podać 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ć. Dlatego, aby umieścić tworzoną procedurę składowaną w określonej bazie danych, należy uruchomić polecenie CREATE PROCEDURE w kontekście tej bazy danych. Odwołując się do obiektów tej samej bazy danych z treści procedury składowanej, można używać nazw skróconych, czyli bez określania nazwy bazy danych. W przypadku konieczności odwoływania się do obiektów znajdujących się w innych bazach danych wymagane jest określenie nazwy bazy danych.

Nazwy parametrów muszą zaczynać się od symbolu @, aby przekazywać dane wejściowe i wyjściowe do wygenerowanej procedury składowanej. W jednej procedurze składowanej można określić wiele parametrów, oddzielając je przecinkami. Ciało procedury nie powinno używać zmiennych lokalnych, których nazwy są takie same, jak nazwy parametrów tej procedury. Dowolny typ danych SQL, w tym zdefiniowany przez użytkownika, jest odpowiedni do definiowania typu danych parametrów procedury składowanej. Jednak typ danych CURSOR może być używany tylko jako parametr wyjściowy procedury składowanej, tj. określając słowo kluczowe OUTPUT.

Obecność słowa kluczowego OUTPUT wskazuje, że odpowiedni parametr służy do zwracania danych z procedury składowanej. Nie oznacza to jednak, że parametr nie nadaje się do przekazywania wartości do procedury składowanej. Określenie słowa kluczowego OUTPUT nakazuje serwerowi, wychodząc z procedury składowanej, przypisać bieżącą wartość parametru do zmiennej lokalnej, która została określona jako wartość parametru podczas wywołania procedury. Należy zwrócić uwagę, że jeśli podano słowo kluczowe OUTPUT, wartość odpowiedniego parametru podczas wywoływania procedury można ustawić tylko za pomocą zmiennej lokalnej. Nie wolno używać żadnych wyrażeń ani stałych, które są prawidłowe dla normalnych parametrów. Słowo kluczowe VARYING jest używane w połączeniu z parametrem OUTPUT typu CURSOR. Określa, że \u200b\u200bparametr wyjściowy będzie zestawem wyników.

Słowo kluczowe DEFAULT to wartość, która zaakceptuje odpowiedni parametr domyślny. Dlatego podczas wywoływania procedury nie można jawnie określać wartości odpowiedniego parametru.

Ponieważ serwer buforuje plan wykonania zapytania i skompilowany kod, następne wywołanie procedury użyje gotowych wartości. Jednak w niektórych przypadkach nadal trzeba ponownie skompilować kod procedury. Określenie słowa kluczowego RECOMPILE nakazuje systemowi wygenerowanie planu wykonania procedury składowanej przy każdym jej wywołaniu.

Parametr FOR REPLICATION jest wymagany podczas replikacji danych i dołączania wygenerowanej procedury składowanej jako artykułu w publikacji. Słowo kluczowe ENCRYPTION nakazuje serwerowi szyfrowanie kodu procedury składowanej, co może zapewnić ochronę przed algorytmami autorskimi, które implementują procedurę składowaną. Słowo kluczowe AS jest umieszczane na początku rzeczywistej treści procedury składowanej. Ciało procedury może używać prawie wszystkich poleceń SQL, deklarować transakcje, uzyskiwać blokady i wywoływać inne procedury składowane. Z procedury składowanej można wyjść za pomocą polecenia RETURN.

Usuwanie procedury składowanej

DROP PROCEDURE (nazwa_procedury) [, ... n]

Wykonywanie procedury składowanej

Aby wykonać procedurę składowaną, użyj polecenia: [[EXEC [UTE] nazwa_procedury [; numer] [[@ nazwa_parametru \u003d] (wartość | @ nazwa_zmiennej) [WYJŚCIE] | [DOMYŚLNIE]] [, ... n]

Jeśli wywołanie procedury składowanej nie jest jedynym poleceniem w pakiecie, wymagane jest polecenie EXECUTE. Ponadto polecenie to jest wymagane do wywołania procedury z treści innej procedury lub wyzwalacza.

Użycie słowa kluczowego OUTPUT w wywołaniu procedury jest dozwolone tylko w przypadku parametrów, które zostały zadeklarowane podczas tworzenia procedury za pomocą słowa kluczowego OUTPUT.

Jeśli dla parametru podczas wywoływania procedury określono słowo kluczowe DEFAULT, zostanie użyta wartość domyślna. Oczywiście określone słowo DEFAULT jest dozwolone tylko dla tych parametrów, dla których zdefiniowano wartość domyślną.

Ze składni komendy EXECUTE widać, że nazwy parametrów można pominąć podczas wywoływania procedury. Jednak w tym przypadku użytkownik musi podać wartości parametrów w tej samej kolejności, w jakiej zostały wymienione podczas tworzenia procedury. Nie można przypisać wartości domyślnej do parametru, po prostu pomijając ją podczas wyliczania. Jeśli chcesz pominąć parametry, dla których zdefiniowano wartość domyślną, wystarczy jawnie określić nazwy parametrów podczas wywoływania procedury składowanej. Ponadto w ten sposób możesz wyświetlić parametry i ich wartości w dowolnej kolejności.

Należy zauważyć, że podczas wywoływania procedury podawane są albo nazwy parametrów z wartościami, albo tylko wartości bez nazwy parametru. Ich połączenie jest niedozwolone.

Używanie RETURN w procedurze składowanej

Umożliwia wyjście z procedury w dowolnym momencie zgodnie z określonym stanem, a także umożliwia przesłanie wyniku procedury jako liczby, po której można ocenić jakość i poprawność procedury. Przykład tworzenia procedury bez parametrów:

UTWÓRZ PROCEDURĘ Count_Books AS SELECT COUNT (Code_book) FROM Books GO

Ćwiczenie 1.

EXEC Count_Books

Sprawdź wynik.

Przykład tworzenia procedury z parametrem wejściowym:

UTWÓRZ PROCEDURĘ Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages\u003e \u003d @Count_pages GO

Zadanie 2... Utwórz tę procedurę w sekcji Procedury składowane bazy danych DB_Books za pomocą narzędzia SQL Server Management Studio. Uruchom go za pomocą polecenia

EXEC Count_Books_Pages 100

Sprawdź wynik.

Przykład tworzenia procedury z parametrami wejściowymi:

UTWÓRZ PROCEDURĘ Count_Books_Title @Count_pages AS INT, @Title AS CHAR (10) AS SELECT COUNT (Code_book) FROM Books WHERE Pages\u003e \u003d @Count_pages AND Title_book LIKE @Title GO

Zadanie 3. Utwórz tę procedurę w sekcji Procedury składowane bazy danych DB_Books za pomocą narzędzia SQL Server Management Studio. Uruchom go za pomocą polecenia

EXEC Count_Books_Title 100, „P%”

Sprawdź wynik.

Przykład tworzenia procedury z parametrami wejściowymi i wyjściowymi:

UTWÓRZ PROCEDURĘ Count_Books_Itogo @Count_pages INT, @Title CHAR (10), @Itogo INT OUTPUT AS SELECT @Itogo \u003d COUNT (Code_book) FROM Books WHERE Pages\u003e \u003d @Count_pages AND Title_book LIKE @Title GO

Zadanie 4. Utwórz tę procedurę w sekcji Procedury składowane bazy danych DB_Books za pomocą narzędzia SQL Server Management Studio. Uruchom z zestawem poleceń:

Sql\u003e Zadeklaruj @q As int EXEC Count_Books_Itogo 100, "P%", @q output select @q

Sprawdź wynik.

Przykład tworzenia procedury z parametrami wejściowymi i RETURN:

CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM autorzy WHERE Code_author \u003d @param) \u003d "Pushkin A.S." POWRÓT 1 INNE POWRÓT 2

Zadanie 5. Utwórz tę procedurę w sekcji Procedury składowane bazy danych DB_Books za pomocą narzędzia SQL Server Management Studio. Zacznij od poleceń:

DECLARE @return_status INT EXEC @return_status \u003d checkname 1 SELECT "Return Status" \u003d @return_status

Przykład tworzenia procedury bez parametrów w celu dwukrotnego zwiększenia wartości pola kluczowego w tabeli Zakupy:

UTWÓRZ PROC update_proc AS UPDATE ZESTAW Zakupów Code_purchase \u003d Code_purchase * 2

Zadanie 6. Utwórz tę procedurę w sekcji Procedury składowane bazy danych DB_Books za pomocą narzędzia SQL Server Management Studio. Uruchom go za pomocą polecenia

EXEC update_proc

Przykład procedury z parametrem wejściowym, aby uzyskać wszystkie informacje o konkretnym autorze:

UTWÓRZ PROC select_author @k CHAR (30) AS SELECT * FROM Authors WHERE name_author \u003d @k

Zadanie 7.

EXEC select_author "Pushkin A.S." lub select_author @ k \u003d "Pushkin A.S." lub EXEC select_author @ k \u003d "Pushkin A.S."

Przykład tworzenia procedury z parametrem wejściowym i wartością domyślną w celu zwiększenia wartości pola klucza w tabeli Zakupy określoną liczbę razy (domyślnie 2 razy):

CREATE PROC update_proc @p INT \u003d 2 AS UPDATE Zakupy SET Code_purchase \u003d Code_purchase * @p

Procedura nie zwraca żadnych danych.

Zadanie 8. Utwórz tę procedurę w sekcji Procedury składowane bazy danych DB_Books za pomocą narzędzia SQL Server Management Studio. Zacznij od poleceń:

EXEC update_proc 4 lub EXEC update_proc @p \u003d 4 lub EXEC update_proc - zostanie użyta wartość domyślna.

Przykład tworzenia procedury z parametrami wejściowymi i wyjściowymi. Utwórz procedurę określania liczby zamówień złożonych w podanym okresie:

CREATE PROC count_purchases @ d1 SMALLDATETIME, @ d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @ c \u003d COUNT (Code_purchase) FROM Purchases WHERE Date_order BETWEEN @ d1 AND @ d2 SET @c \u003d ISNULL (@c, 0)

Zadanie 9. Utwórz tę procedurę w sekcji Procedury składowane bazy danych DB_Books za pomocą narzędzia SQL Server Management Studio. Zacznij od poleceń:

DECLARE @ c2 INT EXEC count_purchases '01 - jun-2006 ', '01 - lip-2006', @ c2 OUTPUT SELECT @ c2

Opcje zadań do pracy laboratoryjnej nr 4

Postanowienia ogólne. W narzędziu SQL Server Management Studio utwórz nową stronę dla kodu (przycisk Utwórz zapytanie). Programowo uaktywnij utworzone DB_Books za pomocą operatora Use. Utwórz procedury składowane za pomocą instrukcji Utwórz procedury i samodzielnie zdefiniuj nazwy procedur. Każda procedura wykona jedno zapytanie SQL, które zostało wykonane w drugim laboratorium. Ponadto kod zapytań SQL musi zostać zmieniony, aby można było do nich przekazać wartości pól, według których przeprowadzane jest wyszukiwanie.

Na przykład oryginalne zadanie i zapytanie w laboratorium nr 2:

/ * Wybierz z katalogu dostawców (tabela Dostawy) nazwy firm, telefony i NIP (pola Nazwa_firma, Telefon i INN), które mają nazwę firmy (pole Nazwa_firma) „OJSC MIR”.

SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company \u003d "OJSC MIR"

* / - W tej pracy zostanie utworzona procedura:

CREATE PROC select_name_company @comp CHAR (30) AS SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company \u003d @comp

–Aby rozpocząć procedurę, użyj polecenia:

EXEC select_name_company „OJSC MIR”

Lista zadań

Utwórz nowy program w SQL Server Management Studio. Uaktywnij programowo indywidualną bazę danych, utworzoną w pracy laboratoryjnej nr 1, za pomocą operatora Use. Utwórz procedury składowane za pomocą instrukcji Utwórz procedury i samodzielnie zdefiniuj nazwy procedur. Każda procedura wykona jedno zapytanie SQL, które są przedstawiane jako oddzielne zadania z opcjami.

opcja 1

1. Wyświetl listę pracowników, którzy mają co najmniej jedno dziecko.

2. Wyświetl listę dzieci, które otrzymały prezenty w określonym czasie.

3. Wyświetl listę rodziców, którzy mają małoletnie dzieci.

4. Wyświetl informacje o prezentach o wartości większej niż podana liczba, posortowane według daty.

Opcja 2

1. Wyświetl listę urządzeń określonego typu.

2. Wyświetl liczbę naprawionych urządzeń i całkowity koszt napraw od określonego mastera.

3. Wyświetl listę właścicieli urządzeń i liczbę ich połączeń, posortowaną według liczby połączeń w porządku malejącym.

4. Wyświetl informacje o mistrzach z rangą wyższą niż podana liczba lub z datą zatrudnienia wcześniejszą niż podana data.

Wariant 3

2. Wyświetl listę kodów sprzedaży, dla których sprzedano kwiaty za kwotę przekraczającą podaną liczbę.

3. Wyświetl datę sprzedaży, kwotę, sprzedającego i kwiat dla określonego kodu sprzedaży.

4. Wymień kwiaty i odmianę kwiatów o wysokości większej niż podana liczba lub kwitnienia.

Opcja 4

1. Wyświetl listę leków ze wskazanym wskazaniem do stosowania.

2. Wyświetlić listę terminów dostaw, dla których sprzedano więcej niż określony numer leku o tej samej nazwie.

3. Wyświetl datę dostawy, kwotę, imię i nazwisko kierownika od dostawcy oraz nazwę leku za pomocą kodu odbioru większego niż podana liczba.

Opcja 5

2. Wyświetl listę wycofanego sprzętu z określonego powodu.

3. Wyświetlić datę odbioru, nazwę sprzętu, nazwisko osoby odpowiedzialnej oraz datę odpisu za sprzęt odpisany w określonym czasie.

4. Wyświetl listę sprzętu o określonym typie lub z datą odbioru większą niż określona wartość

Opcja 6

1. Wyświetl listę potraw ważących więcej niż podana liczba.

2. Wyświetl listę produktów z określonym fragmentem słowa w nazwie.

3. Podaj objętość produktu, nazwę dania, nazwę produktu z kodem dania od określonej wartości początkowej do określonej wartości końcowej.

4. Wyświetl kolejność przygotowania potrawy i nazwę potrawy z ilością węglowodanów większą niż określona wartość lub liczbą kalorii większą niż określona wartość.

Opcja 7

1. Wyświetl listę pracowników na określonym stanowisku.

3. Wyświetl datę rejestracji, rodzaj dokumentu, pełną nazwę rejestratora i nazwę organizacji w przypadku dokumentów zarejestrowanych w określonym okresie.

4. Wyświetl listę zarejestrowanych dokumentów z określonym typem dokumentu lub z datą rejestracji większą niż określona wartość.

Opcja 8

1. Wyświetl listę pracowników z określonym powodem zwolnienia.

3. Wyświetl datę rejestracji, powód zwolnienia, imię i nazwisko pracownika w przypadku dokumentów zarejestrowanych w określonym okresie.

Opcja 9

1. Wyświetl listę pracowników, którzy skorzystali z urlopu określonego typu.

2. Wyświetl listę dokumentów z datą rejestracji w podanym okresie.

3. Wyświetl datę rejestracji, rodzaj urlopu, imię i nazwisko pracownika dla dokumentów zarejestrowanych w podanym okresie.

4. Wyświetl listę zarejestrowanych dokumentów z kodem dokumentu w określonym zakresie.

Opcja 10

1. Wyświetl listę pracowników na określonym stanowisku.

2. Wyświetl listę dokumentów zawierających określony fragment słowa.

3. Wyświetl datę rejestracji, rodzaj dokumentu, pełną nazwę nadawcy i nazwę organizacji dla dokumentów zarejestrowanych w określonym okresie.

4. Wypisz zarejestrowane dokumenty z określonym typem dokumentu lub z kodem dokumentu mniejszym niż określona wartość.

Opcja 11

1. Wyświetl listę pracowników przypisanych do określonego stanowiska.

2. Wyświetl listę dokumentów z datą rejestracji w podanym okresie.

3. Wyświetlać datę rejestracji, stanowisko, imię i nazwisko pracownika dla dokumentów zarejestrowanych w podanym okresie.

4. Wyświetl listę zarejestrowanych dokumentów z kodem dokumentu w określonym zakresie.

Opcja 12

3. Wyświetl listę osób, które wypożyczyły sprzęt i liczbę ich rozmów posortowanych według liczby połączeń w porządku malejącym.

Opcja 13

1. Wyświetl listę sprzętu określonego typu. 2. Wyświetl listę sprzętu, który został spisany przez określonego pracownika.

3. Wyświetla ilość wycofanego wyposażenia pogrupowanego według typu wyposażenia.

4. Wyświetl informacje o pracownikach, których data zatrudnienia jest dłuższa niż określona data.

Opcja 14

1. Wypisz kwiaty z określonym rodzajem liści.

2. Wyświetl listę kodów paragonów, dla których sprzedano kwiaty za kwotę większą niż określona wartość.

3. Wyświetl datę odbioru, kwotę, nazwę dostawcy i kolory dla określonego kodu dostawcy.

4. Wymień kwiaty i odmiany dla kwiatów o wysokości większej niż określona liczba lub kwitnienia.

Opcja 15

1. Wyświetl listę klientów, którzy zameldowali się w pokojach w podanym okresie.

2. Wyświetl łączną kwotę płatności za pokoje dla każdego klienta.

3. Wyświetl datę zameldowania, rodzaj pokoju, imiona i nazwiska klientów zarejestrowanych w podanym okresie.

4. Wyświetlić listę zarejestrowanych klientów w pokojach określonego typu.

Opcja 16

1. Wyświetl listę sprzętu określonego typu.

2. Wyświetl listę sprzętu wypożyczonego przez konkretnego klienta.

3. Wyświetl listę osób, które wypożyczyły sprzęt i liczbę ich rozmów posortowanych według liczby połączeń w porządku malejącym.

4. Wyświetl informacje o klientach posortowane według adresów.

Opcja 17

1. Wyświetl listę wartości, w których wartość zakupu jest większa niż określona wartość lub okres gwarancji jest dłuższy niż określona liczba.

2. Wyświetl listę lokalizacji zasobów materialnych, w nazwie których występuje określone słowo.

3. Wyświetl sumę wartości kosztowności z kodem w określonym przedziale.

4. Wyświetlić listę osób odpowiedzialnych finansowo z datą zatrudnienia w określonym zakresie.

Opcja 18

1. Wyświetl listę napraw wykonanych przez określonego mistrza.

2. Wymień etapy pracy zawarte w pracy, w tytule których występuje określone słowo.

3. Wyświetl sumę kosztów etapów prac naprawczych do pracy z kodem w określonym zakresie.

4. Wyświetl listę mistrzów z datą zatrudnienia w określonym zakresie.

Opcja 19

1. Wyświetl listę leków z określonym wskazaniem.

2. Wyświetl listę numerów czeków, dla których sprzedano więcej niż określoną liczbę leków.

3. Wyświetl datę sprzedaży, kwotę, imię i nazwisko kasjera oraz lekarstwo na paragonie z podanym numerem.

4. Wyświetl listę leków i jednostek miary dla leków, których ilość w opakowaniu jest większa niż określona liczba lub kod leku jest mniejszy niż określona wartość.

Opcja 20

1. Wyświetl listę pracowników na określonym stanowisku.

2. Wyświetl listę dokumentów zawierających określony fragment słowa.

3. Podać datę rejestracji, rodzaj dokumentu, pełną nazwę kontrahenta oraz fakt wykonania dla dokumentów zarejestrowanych w podanym okresie.

4. Wyświetl listę zarejestrowanych dokumentów z określonym typem dokumentu lub z kodem dokumentu w określonym zakresie.

W MySQL 5 jest wiele nowych funkcji, z których jedną z najważniejszych jest tworzenie procedur składowanych. W tym samouczku opowiem o tym, czym one są i jak mogą ułatwić Ci życie.

Wprowadzenie

Procedura składowana to sposób na hermetyzację powtarzających się akcji. W procedurach składowanych można deklarować zmienne, manipulować przepływami danych i używać innych technik programowania.

Przyczyna ich powstania jest jasna i potwierdzona częstym ich użyciem. Z drugiej strony, jeśli porozmawiasz z tymi, którzy nie pracują z nimi regularnie, opinie podzielą się na dwie zupełnie przeciwstawne flanki. Nie zapomnij o tym.

Za

• Udostępnianie logiki innym aplikacjom. Procedury składowane obejmują funkcjonalność; zapewnia to spójność dostępu do danych i zarządzania nimi między różnymi aplikacjami.
• Izolacja użytkowników z tabel bazy danych. Pozwala to na udzielenie dostępu do procedur składowanych, ale nie do rzeczywistych danych tabeli.
• Zapewnia mechanizm obronny. Podobnie jak w poprzednim punkcie, jeśli dostęp do danych można uzyskać tylko za pośrednictwem procedur składowanych, nikt inny nie może usunąć danych za pomocą polecenia SQL DELETE.
• Zwiększona wydajność w wyniku zmniejszonego ruchu sieciowego. Dzięki procedurom składowanym można łączyć wiele zapytań.

Przeciwko

• Zwiększone obciążenie serwera bazy danych ze względu na fakt, że większość pracy wykonywana jest po stronie serwera, a mniej po stronie klienta.
• Można się wiele nauczyć. Będziesz musiał nauczyć się składni wyrażeń MySQL, aby napisać swoje procedury składowane.
• Powielasz logikę aplikacji w dwóch miejscach: w kodzie serwera i kodzie procedury składowanej, co komplikuje proces manipulowania danymi.
• Migracja z jednego DBMS do innego (DB2, SQL Server itp.) Może prowadzić do problemów.

Narzędzie, w którym pracuję, nazywa się MySQL Query Browser i jest dość standardowe do interakcji z bazami danych. Narzędzie wiersza poleceń MySQL to kolejny doskonały wybór. Mówię ci o tym, ponieważ ulubiony phpMyAdmin wszystkich nie obsługuje wykonywania procedur składowanych.

Przy okazji, używam podstawowej struktury tabeli, aby ułatwić Ci zrozumienie tego tematu. Mówię o procedurach składowanych, które są wystarczająco złożone, aby zagłębić się w nieporęczną strukturę tabel.

Krok 1: ustaw ogranicznik

Separator to znak lub ciąg znaków, który jest używany do wskazania klientowi MySQL, że zakończyłeś pisanie instrukcji SQL. Od wieków separatorem był średnik. Jednak mogą pojawić się problemy, ponieważ w procedurze składowanej może występować wiele wyrażeń, z których każde musi kończyć się średnikiem. W tym samouczku jako separatora używam ciągu „//”.

Krok 2: Jak pracować z procedurami składowanymi

Tworzenie procedury składowanej

DELIMITER // TWORZENIE PROCEDURY `p2` () JĘZYK SQL DETERMINISTYCZNY DEFINER BEZPIECZEŃSTWA SQL KOMENTARZ" Procedura "BEGIN SELECT" Hello World! "; KONIEC //

Pierwszy fragment kodu tworzy procedurę składowaną. Następny zawiera opcjonalne parametry. Potem pojawia się nazwa i wreszcie treść samej procedury.

W nazwach procedur składowanych rozróżniana jest wielkość liter. Nie można również tworzyć wielu procedur o tej samej nazwie. W procedurze składowanej nie może być żadnych wyrażeń modyfikujących samą bazę danych.

4 cechy procedury składowanej:

• Język: w celu ułatwienia przenośności domyślnie określono język SQL.
• Deterministyczne: jeśli procedura zwraca cały czas ten sam wynik i przyjmuje te same parametry wejściowe. Dotyczy to procesu replikacji i rejestracji. Wartością domyślną jest NIE DETERMINISTYCZNA.
• Bezpieczeństwo SQL: Podczas połączenia sprawdzane są prawa użytkownika. INVOKER to użytkownik wywołujący procedurę składowaną. DEFINER jest „twórcą” procedury. Wartość domyślna to DEFINER.
• Komentarz: na potrzeby dokumentacji wartość domyślna to „”

Wywołanie procedury składowanej

Aby wywołać procedurę składowaną, należy wpisać słowo kluczowe CALL, po którym następuje nazwa procedury, a po niej parametry (zmienne lub wartości) w nawiasach. Nawiasy są wymagane.

CALL nazwa_procedury_przechowywanej (param1, param2, ....) CALL procedura1 (10, "parametr ciągu", @parameter_var);

Modyfikowanie procedury składowanej

MySQL udostępnia instrukcję ALTER PROCEDURE do modyfikowania procedur, ale jest odpowiednia do zmiany tylko kilku cech. Jeśli chcesz zmienić parametry lub treść procedury, musisz ją usunąć i ponownie utworzyć.

Usuwanie procedury składowanej

PROCEDURA UPUSZCZANIA, JEŚLI ISTNIEJE p2;

To jest prosta komenda. Wyrażenie IF EXISTS wyłapuje błąd, jeśli taka procedura nie istnieje.

Krok 3: Opcje

Zobaczmy, jak przekazać parametry do procedury składowanej.

• TWORZENIE PROCEDURY proc1 (): pusta lista parametrów
• CREATE PROCEDURE proc1 (IN varname DATA-TYPE): jeden parametr wejściowy. Słowo IN jest opcjonalne, ponieważ parametry domyślne to IN (przychodzące).
• CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): jeden zwracany parametr.
• TWORZENIE PROCEDURY proc1 (INOUT nazwa-zmiennej DATA-TYPE): jeden parametr, zarówno wprowadzający, jak i zwracający.

Oczywiście możesz określić kilka parametrów różnych typów.

Przykład parametru IN

DELIMITER // TWORZENIE PROCEDURY `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result; KONIEC //

Przykład parametru OUT

DELIMITER // TWORZENIE PROCEDURY `proc_OUT` (OUT var1 VARCHAR (100)) BEGIN SET var1 \u003d" To jest test "; KONIEC //

Przykład parametru INOUT

DELIMITER // TWORZENIE PROCEDURY `proc_INOUT` (OUT var1 INT) BEGIN SET var1 \u003d var1 * 2; KONIEC //

Krok 4: zmienne

Teraz nauczę Cię, jak tworzyć zmienne i przechowywać je w procedurach. Musisz je jawnie zadeklarować na początku bloku BEGIN / END, wraz z typami danych. Po zadeklarowaniu zmiennej można jej używać w tym samym miejscu, co zmienne sesji, literały lub nazwy kolumn.

Składnia deklarowania zmiennej wygląda następująco:

DECLARE nazwa_zmiennej DATA-TYPE DEFAULT defaultvalue;

Zadeklarujmy kilka zmiennych:

ZADEKLAROWAĆ a, b INT DEFAULT 5; DECLARE str VARCHAR (50); ZADEKLARUJ dzisiaj TIMESTAMP DEFAULT CURRENT_DATE; ZADEKLAROWAĆ v1, v2, v3 TINYINT;

Praca ze zmiennymi

Po zadeklarowaniu zmiennej możesz ustawić jej wartość za pomocą poleceń SET lub SELECT:

DELIMITER // TWORZENIE PROCEDURY `var_proc` (IN paramstr VARCHAR (20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR (50); ZADEKLARUJ dzisiaj TIMESTAMP DEFAULT CURRENT_DATE; ZADEKLAROWAĆ v1, v2, v3 TINYINT; WSTAWIĆ DO tabeli1 WARTOŚCI (a); SET str \u003d "Jestem stringiem"; SELECT CONCAT (str, paramstr), dzisiaj FROM table2 WHERE b\u003e \u003d 5; KONIEC //

Krok 5: Struktury kontroli przepływu

MySQL obsługuje konstrukcje IF, CASE, ITERATE, LEAVE LOOP, WHILE i REPEAT do sterowania przepływem w ramach procedury składowanej. Przyjrzymy się, jak używać funkcji IF, CASE i WHILE, ponieważ są one najczęściej używane.

Konstrukcja IF

Korzystając z konstrukcji IF, możemy wykonać zadania zawierające warunki:

DELIMITER // TWORZENIE PROCEDURY `proc_IF` (IN param1 INT) BEGIN DECLARE zmienna1 INT; SET zmienna1 \u003d param1 + 1; JEŻELI zmienna1 \u003d 0 TO WYBIERZ zmienną1; KONIEC JEŻELI; JEŻELI param1 \u003d 0 TO WYBIERZ "Wartość parametru \u003d 0"; ELSE SELECT ”Wartość parametru<> 0 "; KONIEC JEŻELI; KONIEC //

Konstrukcja CASE

CASE to kolejna metoda sprawdzenia warunków i wyboru odpowiedniego rozwiązania. To świetny sposób na zastąpienie wielu konstrukcji IF. Konstrukcję można opisać na dwa sposoby, zapewniając elastyczność w zarządzaniu wieloma warunkami.

DELIMITER // TWORZENIE PROCEDURY `proc_CASE` (W param1 INT) BEGIN DECLARE zmienna1 INT; SET zmienna1 \u003d param1 + 1; CASE zmienna1 WHEN 0 TO INSERT INTO table1 VALUES (param1); KIEDY 1 TO WSTAWIĆ DO tabeli1 WARTOŚCI (zmienna1); ELSE INSERT INTO table1 VALUES (99); KOŃCÓWKA; KONIEC //

DELIMITER // TWORZENIE PROCEDURY `proc_CASE` (W param1 INT) BEGIN DECLARE zmienna1 INT; SET zmienna1 \u003d param1 + 1; PRZYPADEK GDY zmienna1 \u003d 0 TO WSTAWIĆ DO tabeli1 WARTOŚCI (param1); KIEDY zmienna1 \u003d 1 TO WSTAWIĆ DO tabeli1 WARTOŚCI (zmienna1); ELSE INSERT INTO table1 VALUES (99); KOŃCÓWKA; KONIEC //

PODCZAS budowy

Technicznie rzecz biorąc, istnieją trzy rodzaje pętli: pętla WHILE, pętla LOOP i pętla REPEAT. Możesz również zorganizować pętlę za pomocą techniki programowania Dartha Vadera: wyrażenia GOTO. Oto przykład pętli:

DELIMITER // TWORZENIE PROCEDURY `proc_WHILE` (W param1 INT) BEGIN DECLARE zmienna1, zmienna2 INT; SET zmienna1 \u003d 0; WHILE zmienna 1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Krok 6: kursory

Kursory służą do przechodzenia przez zestaw wierszy zwróconych przez zapytanie, a także do przetwarzania każdego wiersza.

MySQL obsługuje kursory w procedurach składowanych. Oto krótka składnia tworzenia i używania kursora.

DEKLARUJ nazwę kursora KURSOR DO WYBORU ...; / * Deklarowanie kursora i wypełnianie go * / DECLARE CONTINUE HANDLER FOR NOT FOUND / * Co zrobić, gdy nie ma już rekordów * / OPEN nazwa-kursora; / * Otwórz kursor * / FETCH nazwa-kursora INTO zmienna [, zmienna]; / * Przypisywanie wartości do zmiennej równej bieżącej wartości kolumny * / CLOSE nazwa-kursora; / * Zamknij kursor * /

W tym przykładzie wykonamy kilka prostych operacji kursora:

DELIMITER // TWORZENIE PROCEDURY `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; ZADEKLAROWAĆ kursor cur1 dla wyboru col1 z tabeli1; ZADEKLAROWAĆ KONTYNUUJ HANDLER DLA NIEZNALEZIONEGO ZBIORU b \u003d 1; OPEN cur1; SET b \u003d 0; SET c \u003d 0; GDY b \u003d 0 DOBIERZ cur1 DO a; JEŚLI b \u003d 0 TO USTAWIĆ c \u003d c + a; KONIEC JEŻELI; KONIEC GDY; CLOSE cur1; SET param1 \u003d c; KONIEC //

Kursory mają trzy właściwości, które musisz zrozumieć, aby uniknąć nieoczekiwanych wyników:

• Niewrażliwy: po otwarciu kursor nie będzie odzwierciedlał późniejszych zmian w tabeli. W rzeczywistości MySQL nie gwarantuje, że kursor zostanie zaktualizowany, więc nie licz na to.
• Tylko do odczytu: kursorów nie można zmieniać.
• Bez przewijania do tyłu: kursor może poruszać się tylko w jednym kierunku - do przodu, nie można przeskakiwać wierszy bez ich zaznaczania.

Wniosek

W tym samouczku przedstawiłem podstawy pracy z procedurami składowanymi i niektóre specyficzne właściwości z nimi związane. Oczywiście, zanim zostaniesz prawdziwym guru procedur MySQL, będziesz musiał pogłębić swoją wiedzę w takich obszarach, jak bezpieczeństwo, wyrażenia SQL i optymalizacja.

Należy obliczyć korzyści z używania procedur składowanych w określonej aplikacji, a dopiero potem utworzyć tylko niezbędne procedury. Ogólnie używam procedur; moim zdaniem powinny być wdrażane w projektach ze względu na ich bezpieczeństwo, utrzymanie kodu i ogólny projekt. Należy również pamiętać, że nadal trwają prace nad procedurami MySQL. Spodziewaj się ulepszeń funkcjonalnych i ulepszeń. Nie wahaj się i podziel się swoimi opiniami.