Ostatnia aktualizacja: 14.08.2017

Często operacja danych przedstawia zestaw instrukcji, które należy wykonać w określonej sekwencji. Na przykład, dodając zakup towarów, musisz dokonać danych w tabeli zamówień. Jednak zanim to konieczne jest sprawdzenie i czy istnieje zakupiony produkt w magazynie. Być może konieczne będzie sprawdzenie kolejnej liczby dodatkowych warunków. To jest w rzeczywistości proces zakupu towarów obejmuje kilka działań, które należy wykonać w określonej sekwencji. W tym przypadku będzie bardziej optymalnie zamknięcie wszystkich tych działań w jednym obiekcie - procedura składowana Procedura składowana).

Oznacza to, że zasadniczo składowane procedury przedstawia zestaw instrukcji wykonywanych jako pojedynczy liczbę całkowitą. W ten sposób procedury przechowywane umożliwiają uproszczenie kompleksowych operacji i zabrać je do jednego obiektu. Proces zakupu towarów zmieni się odpowiednio, wystarczy, aby zmienić kod procedury. Oznacza to, że procedura upraszcza również kod zarządzania.

Procedury przechowywane umożliwiają ograniczenie dostępu do danych w tabelach, a tym samym zmniejszają prawdopodobieństwo celowych lub nieświadomych niepożądanych działań dla tych danych.

I jeszcze jeden ważny aspekt - wydajność. Procedury przechowywane są zazwyczaj wykonywane szybsze niż zwykłe instrukcje SQL. Wszystko, ponieważ kod procedury jest kompilowany raz po raz pierwszy, a następnie utrzymywał się w skompilowanej formie.

Aby utworzyć procedurę przechowywaną, stosuje się polecenie Utwórz procedurę lub Utwórz proc.

W ten sposób procedura przechowywana ma trzy kluczowe cechy: Uprość kod, bezpieczeństwo i wydajność.

Na przykład, niech baza danych ma tabelę, która przechowuje dane dotyczące produktów:

Utwórz produkty stołowe (Int Int Intentity Klucz podstawowy, ProductName Nvarchar (30) Nie Null, Producent NVarchar (20) Nie Null, ProductCount Innt Domyślnie 0, Pieniądze Cena Nie Null);

Utwórz procedurę składowaną, aby wyodrębnić dane z tej tabeli:

Użyj produktówDB; Utwórz procedurę Produkt Płatność Jak wybrać Produkt Nazwa jako produkt, producent, Cena od produktów

Ponieważ należy nazwać zespół Create Procedury oddzielny pakietNastępnie po komendzie użytkowania, który ustawia bieżącą bazę danych, użyj polecenia GO, aby określić nowy pakiet.

Po nazwie procedury powinno iść słowo kluczowe TAK JAK.

Aby oddzielić korpus procedury od reszty skryptu, kod procedury jest często umieszczony w jednostce końcowej rozpoczęcia ...

Użyj produktówDB; Utwórz procedurę Utwórz Produkt Płatność Jak rozpocznij Wybierz ProductName jako produkt, producent, cena od końca produktów;

Po dodaniu procedury widzimy go w węźle bazy danych w SQL Server Management Studio w Subselle Programowalność -\u003e Procedury składowane:

I będziemy mogli zarządzać procedurą również poprzez interfejs wizualny.

Procedura

Aby wykonać procedurę zapisaną, polecenie EXEC lub Execute jest nazywane:

Exec Podział

Usuwanie procedury

Polecenie procedury DROP ma zastosowanie do usuwania procedury:

Spadek Produkty Płatność.

1. Sprawdź operatorzy opisujące procedury przechowywane i zasady przekazywania parametrów wejściowych i wyjściowych.
2. Sprawdź procedurę tworzenia i debugowania procedur przechowywanych na serwerze MS SQL Server 2000.
3. Opracuj pięć podstawowych procedur przechowywanych baza akademicka Dane "Biblioteka".
4. Przygotuj raport o pracy wykonanej w formie elektronicznej.

1. Ogólne informacje o procedurach przechowywanych

Procedura zapisana (procedura składowana) - Jest to zestaw poleceń, przechowywanych na serwerze i wykonany jako całość. Procedury przechowywane są mechanizmem, z którym można tworzyć podprogramy działające na serwerze i kontrolowane przez jego procesy. Takie podprogramy można aktywować przez aplikację, którą je dzwoni. Ponadto mogą być spowodowane przepisami, które wspierają integralność danych lub wyzwalacze.

Procedury przechowywane mogą zwracać wartości. W procedurze można porównać wartości wprowadzone przez użytkownika z informacjami z wyprzedzeniem w systemie. Procedury przechowywane są wykorzystywane w działaniu silnych rozwiązań sprzętowych SQL Server. Są one oparte na bazach danych i ściśle interakcji z optymalizatorem SQL Server. Pozwala to uzyskać wysoką wydajność podczas przetwarzania danych.

W procedurach przechowywanych możliwe jest przesyłanie wartości i uzyskania z nich wyników pracy, a niekoniecznie związane z tabelą roboczą. Procedura zapisana może obliczyć wyniki podczas pracy.

Procedury zapisane są dwa typy: normalna i rozszerzony. Konwencjonalne procedury przechowywane są zestawem poleceń dotyczących Transact-SQL, podczas gdy rozszerzone procedury przechowywane są przedstawiane jako biblioteki dynamiczne (DLL). Takie procedury, w przeciwieństwie do zwykłego, mają prefiks XP_. Serwer ma standardowy zestaw rozszerzonych procedur, ale użytkownicy mogą napisać swoje procedury w dowolnym języku programowania. Najważniejszą rzeczą jest użycie interfejsu programowania Sql. Serwer otwarty API usług danych.. Rozszerzone procedury zapisane mogą znajdować się tylko w bazie danych głównej.

Konwencjonalne procedury przechowywane można również podzielić na dwa typy: systemowy i zwyczaj. Procedury systemowe. - jest to standardowe proceduryobsługa serwera; Niestandardowe - wszelkie procedury utworzone przez użytkownika.

1.1. Zalety procedur przechowywanych

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

• Wysoka wydajność. Jest wynikiem lokalizacji procedur przechowywanych na serwerze. Serwer jest zwykle silniejszą maszyną, więc czas wykonania procedury na serwerze jest znacznie mniejszy niż na stacja robocza. Ponadto informacje z procedury bazy danych i procedury przechowywanej znajdują się w tym samym systemie, więc transmisja zapisów przez sieć jest praktycznie nie wydana. Procedury przechowywane mają bezpośredni dostęp do baz danych, co sprawia, że \u200b\u200bpraca z informacjami bardzo szybko.
• Zaletą rozwoju systemu w architekturze klient-serwer. Jest to możliwość oddzielnie utworzenia oprogramowania i serwera klienta. Ta zaleta jest kluczem do opracowywania, a dzięki temu można znacznie zmniejszyć czas wymagany do końca projektu. Kod uruchomiony na serwerze można opracować oddzielnie od kodu części klienta. W tym przypadku składniki części serwera mogą być udostępniane przez elementy strony klienta.
• Poziom bezpieczeństwa. Procedury przechowywane mogą działać jako narzędzie do poprawy bezpieczeństwa. Możesz tworzyć procedury przechowywane, które wykonują operacje dodawania, zmiany, usuwania i list wyświetlanych, a tym samym uzyskać kontrolę nad każdym względem dostępu do informacji.
• Wzmocnienie zasad serwera pracy z danymi. Jest to jedna z najważniejszych przyczyn korzystania z inteligentnego rdzenia bazy danych. Procedury przechowywane umożliwiają zastosowanie reguł i innych logiki, które pomagają kontrolować informacje włączone w systemie.

Mimo że język SQL. Jest zdefiniowany jako bez zabezpieczenia, SQL Server wykorzystuje słowa kluczowe związane z zarządzaniem wykonaniem procedury. Takie słowa kluczowe są używane podczas tworzenia procedur, które można zapisać w celu późniejszego wykonania. Przechowywane procedury można zastosować zamiast programów utworzonych języki standardowe. Programowanie (na przykład z lub Visual Basic) i wykonywanie operacji w bazie danych dane SQL. Serwer.

Procedury przechowywane są kompilowane na pierwszym wykonaniu i są zapisywane w tabeli systemowej bieżącej bazy danych. Podczas kompilacji są one zoptymalizowane. W takim przypadku wybrany jest najlepszym sposobem dostępu do informacji o tabeli. Taka optymalizacja uwzględnia rzeczywistą pozycję danych w tabeli, dostępne indeksy, obciążenie tabel itp.

Specjalne procedury przechowywane mogą znacząco poprawić wydajność systemu. Warto jednak zauważyć, że statystyki danych z momentu stworzenia procedury, dopóki jej wykonanie może być przestarzałe, a wskaźniki mogą stać się nieskuteczne. I chociaż możesz uaktualnić statystyki i dodać nowe, bardziej wydajne indeksy, plan wykonania procedury jest już skompilowany, czyli procedura jest opracowana, w wyniku czego metoda dostępu do danych może przestać być skuteczna. Dlatego możliwe jest ponowne skompilowanie procedur z każdym połączeniem.

Z drugiej strony, rekompulacja zajmie trochę czasu za każdym razem. Dlatego kwestia skuteczności rekompilowania procedury lub jednorazowej kompilacji jego wdrożenia jest dość subtelna i powinna być rozpatrywana dla każdego konkretnego przypadku oddzielnie.

Procedury przechowywane mogą być wykonywane na maszynie lokalnej lub na zdalnym systemie SQL Server. Umożliwia to uaktywnienie procesów na innych maszynach i pracy nie tylko z lokalnymi bazami danych, ale także z informacjami na temat wielu serwerów.

Programy aplikacji napisane w jednym z języków wysokiego szczebla, takich jak w przypadku Visual Basic .NET, mogą również powodować procedury przechowywane, co zapewnia optymalne rozwiązanie w dystrybucji obciążenia między oprogramowanie Część klienta i serwer SQL.

1.2. Tworzenie procedur składowanych

Utwórz instrukcję procedury dotyczy utworzenia procedury zapisanej. Nazwa procedury zapisanej może wynosić do 128 znaków, w tym symbole # i ##. Składnia definicji procedury:

Utwórz jakość Nazwa ProC [; numer]
[(@ Parameter Type_data) [\u003d Value___Uomolement]]] [, ... N]

Tak jak
<Инструкции_SQL>

Rozważ parametry tego polecenia:

• Nazwa: Procedura_Name - Nazwa procedury; Musi spełniać zasady identyfikatorów: jego długość nie może przekroczyć 128 znaków; W przypadku lokalnych procedur czasowych nazwa jest używana przed nazwą i do globalnych procedur tymczasowych - znaki ##;
• Numer jest opcjonalnym Integer używany do grupowania wielu procedur pod jedną nazwą;
• @ Parametr type_data. - listę nazw parametrów procedur, wskazując odpowiedni typ danych dla każdego; Takie parametry mogą mieć do 2100. Wartość parametru jest dozwolona do przesyłania NULL. Wszystkie typy danych mogą być używane z wyjątkiem typów tekstu, NTEXT i obrazu. Możesz użyć typu danych kursora jako parametru wyjściowego (wyjście słów kluczowych lub różniących się). Parametry z danymi kursora mogą być tylko parametry wyjściowe;
• Varying to słowo kluczowe określające, że wynikowy parametr jest używany jako parametr wyników (używany tylko dla typu kursora);
• Wyjście - sugeruje, że określony parametr może być używany jako wyjście;
• value_to_ux. - Używany w przypadku przy wywołaniu procedury brakuje parametru; musi być stała i może zawierać znaki maski (%, _, [,], ^) i wartość null. ;
• Dzięki rekompiluje - słowa kluczowe pokazujące, że SQL Server nie zapisuje planu procedury w pamięci podręcznej i utworzy go za każdym razem, gdy zostanie wykonany;
• Dzięki szyfrowaniu - słowa kluczowe pokazujące, że SQL Server będzie szyfrować procedurę przed zapisaniem do tabeli systemów SYSComments. Aby wprowadzić tekst zaszyfrowanych procedur, należy przywrócić, konieczne jest usunięcie cortices z tabel zyskujących odpowiadający im po szyfrowaniu;
• W przypadku replikacji - słowa kluczowe przedstawiające, że ta procedura jest tworzona tylko do replikacji. Ta opcja jest niezgodna ze słowami kluczowymi Rekompilowana;
• Jako początek definicji tekstu procedury;
• <Инструкции_SQL> - Set. dopuszczalne instrukcje SQL, ograniczony tylko przez maksymalny rozmiar procedury przechowywanej - 128 KB. Nieprawidłowe są następujących operatorów: Alter baza danych, alter procedury, zmienić tabelę, Utwórz domyślną, tworzyć procedurę, alter wyzwalacza, alter Widok, utwórz bazę danych, utwórz regułę, utwórz schemat, utwórz wyzwalacz, tworzenie widoku, dysk Resize, Resize, Resize, Drop Drop. , Drop Default, Drop Procedura, Reguła Drop, Drop Trigger, Widok zawijający, Resotre Database, Restore Log, Reconfiguruje, Statystyki aktualizacji.

Rozważmy przykład procedury przechowywanej. Opracujemy zapisaną procedurę, która liczy się i wyświetla liczbę kopii książek, które są obecnie w bibliotece:

Utwórz procedurę Count_ex1.
- Procedura liczenia liczby kopii książek,
- Obecnie w bibliotece,
- Nie na czytelnikach
Tak jak
- Ustaw tymczasową zmienną lokalną
Deklaruj @n int.
Wybierz @N \u003d Count (*) od Exemplar, gdzie tak_no \u003d "1"
Wybierz @N.
Udać się.

Ponieważ procedura zapisana jest pełnoprawna komponent bazy danych, w jaki sposób już zrozumiałeś, możesz utworzyć nową procedurę dla bieżącej bazy danych. Podczas pracy w analizatorze zapytania SQL Server, ustawienie bieżącej bazy danych jest wykonywana za pomocą operatora Użyj operatora, a następnie nazwa bazy danych, w której należy utworzyć procedurę zapisaną. Wybierz aktualną bazę danych może być również używany za pomocą listy rozwijanej.

Po utworzeniu w zapisanym systemie procedury SQL. Serwer kompiluje go i sprawdza podprogramy wykonywalne. Jeśli wystąpią jakiekolwiek problemy, procedura zostanie odrzucona. Przed ponownym przesłuchaniem błąd należy wyeliminować.

SQL Server 2000 wykorzystuje odroczony nazwany o nazwie o nazwie Nazwa Rozdzielczość Nazwa, więc jeśli procedura zapisana zawiera odwołanie do innego, nie jest jeszcze wdrożonej procedury, wyświetlane jest ostrzeżenie, ale wezwanie do procedury nieistniejącej jest zachowane.

Jeśli opuścisz system z nieokreśloną procedurą przechowywaną, próbując go wykonać, użytkownik otrzyma komunikat o błędzie.

Możesz także utworzyć procedurę zapisaną za pomocą SQL Server Enterprise Manager:

W celu sprawdzenia wydajności utworzonej procedury zapisanej, musisz przejść do analizatora zapytania i uruchomić procedurę wykonania przez operatora Exec.<имя процедуры> . Wyniki rozpoczęcia procedur utworzonych przez nas są prezentowane na FIG. cztery.

Figa. 4. Uruchom procedurę zapisaną w analizatorze zapytania

Figa. 5. Wynik procedury bez operatora wyjściowego na ekranie

1.3. Parametry procedur przechowywanych

Przechowywane procedury są bardzo potężnym narzędziem, ale maksymalna wydajność można osiągnąć tylko poprzez uczynienie ich dynamicznie. Deweloper musi być w stanie przekazać wartość do procedury przechowywanej, dzięki której będzie działać, czyli parametry. Poniżej znajdują się podstawowe zasady stosowania parametrów w procedurach przechowywanych.

• W przypadku procedury można zdefiniować jeden lub więcej parametrów.
• Parametry są używane jako nazwane lokalizacje pamięci, tak jak zmienne w językach programowania, takich jak C, Visual Basic .NET.
• Nazwa parametru jest koniecznie poprzedzona symbolem @.
• Nazwy parametrów są lokalne w tej procedurze, w których są zdefiniowane.
• Parametry służą do przesyłania procedury informacyjnej podczas jej wykonania. Kolidują one wiersz polecenia po nazwie procedury.
• Jeśli procedura ma kilka parametrów, są one oddzielone przecinkami.
• Aby określić typ informacji przesyłanej jako parametr, użyj układu systemowego lub typy niestandardowe dane.

Poniżej znajduje się definicja procedury posiadającej jeden parametr wejściowy. Zmień poprzednie zadanie i przyjmiemy nie wszystkie kopie książek, ale tylko przypadki konkretnej książki. Książki są zdecydowanie identyfikowane przez unikalną ISBN, więc przeniesiemy ten parametr do procedury. W tym przypadku tekst procedury zapisanej zmieni się i będzie miał następujący formularz:

Utwórz procedurę Count_ex (@isbn Varchar (14))
Tak jak
Deklaruj @n int.
Wybierz @N.
Udać się.

Po rozpoczęciu tej procedury musimy przesyłać wartość parametru wejściowego (rys. 6).

Figa. 6. Uruchamianie procedury z przeniesieniem parametru

Aby utworzyć wiele wersji tej samej procedury, która ma tę samą nazwę, wykonaj główną nazwę, aby umieścić średnik i liczbę całkowitą. Jak to zrobić pokazano w poniższym przykładzie, gdzie opisano tworzenie dwóch procedur o tej samej nazwie, ale z różnymi numerami wersji (1 i 2). Numer służy do kontrolowania wersji tej procedury. Jeśli numer wersji nie zostanie określony, wykonana jest pierwsza wersja procedury. Ta opcja nie jest pokazana w poprzednim przykładzie, ale mimo to jest dostępna dla Twojej aplikacji.

Aby wyświetlić wiadomość identyfikującą wersję, obie procedury stosują instrukcję drukowania. Pierwsza wersja uważa liczbę darmowych instancji, a druga jest liczbą kopii na rękach dla tej książki.

Tekst obu wersji procedur jest poniżej:

Utwórz procedurę Count_Ex_all; jeden
(@Isbn varchar (14))
- Procedura liczenia darmowych kopii danej książki
Tak jak
Deklaruj @n int.
Wybierz @N \u003d Count (*) z Exemplar, gdzie ISBN \u003d @isbn i Yes_no \u003d "1"
Wybierz @N.
--
Udać się.
--
Utwórz procedurę Count_Ex_all; 2.
(@Isbn varchar (14))
- Procedura liczenia darmowych kopii danej książki
Tak jak
Oświadczyć @ n1 int
Wybierz @ N1 \u003d Count (*) z Exemplar, gdzie ISBN \u003d @ISBN i YES_NO \u003d "0"
Wybierz @ n1.
Udać się.

Wyniki procedury z różnymi wersjami przedstawiono na FIG. 7.

Figa. 7. Uruchom wyniki różne wersje. ta sama i ta sama procedura przechowywana

Podczas pisania wielu wersji należy pamiętać o następujących ograniczeniach: Ponieważ wszystkie wersje procedury są kompilowane razem, wszystkie zmienne lokalne są uważane za wspólne. Dlatego też, jeśli jest to wymagane przez algorytm przetwarzania, musisz użyć różnych nazw zmiennych wewnętrznych, które wykonaliśmy, wywołując zmienną @N w drugiej procedury przez @ N1.

Napisaliśmy procedury nie zwracają żadnego parametru, wystarczy wyświetlać wynikowy numer. Jednak najczęściej musimy uzyskać parametr do dalszego przetwarzania. Istnieje kilka sposobów powrotu parametrów z procedury zapisanej. Najłatwiej jest użyć wartości powrotnych operatora zwrotu. Ten operator pozwoli Ci zwrócić jedną wartość liczbową. Ale musimy określić nazwę zmiennej lub ekspresji, która jest przypisana do zwracanego parametru. Poniżej znajdują się wartości zwracane przez operatora zwrotu, zarezerwowane przez system:

Kod	Wartość
0	Wszystko w porządku
-1.	Obiekt nie jest znaleziony
-2.	Błąd typu danych.
-3.	Proces był ofiarą "Deadlock"
-4.	Błąd dostępu
-5.	Błąd składni
-6.	Jakiś błąd.
-7.	Błąd z zasobami (bez miejsca)
-8.	Był skorygowany błąd wewnętrzny
-9.	Limit systemu jest wyczerpany
-10.	Nieprawidłowe naruszenie wewnętrznej integralności
-11.	Podobnie
-12.	Zniszczenie stołu lub indeksu
-13.	Zniszczenie bazy danych.
-14.	Błąd sprzętu.

Tak więc, aby nie zaprzeczać systemowi, możemy zwrócić tylko całe dodatnie liczby za pośrednictwem tego parametru.

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

Utwórz procedurę Count_ex2 (@isbn Varchar (14))
Tak jak
Deklaruj @n int.
Wybierz @N \u003d Count (*) z Exemplar
Gdzie isbn \u003d @isbn i yes_no \u003d "1"
- zwróć wartość zmiennej @N,
- Jeśli wartość zmiennej nie jest zdefiniowana, zwróć 0
Return Coalesce (@N, 0)
Udać się.

Teraz możemy uzyskać wartość zmiennej @N i użyć go do dalszego przetwarzania. W tym przypadku zwrócona wartość jest przypisana do najbardziej przechowywanej procedury, a aby go przeanalizować, można go użyć następującego formatu wywołania procedury przechowywanej:

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

Przykład wywołania naszej procedury jest pokazany na FIG. osiem.

Figa. 8. Transmisja zwracanej wartości procedury składowanej zmiennej lokalnej

Parametry wejściowe procedur przechowywanych mogą korzystać z wartości domyślnej. Ta wartość zostanie użyta, jeśli wartość parametru nie została określona podczas wywołania procedury.

Wartość domyślna jest ustawiana przez równy znak po opisaniu parametru wejściowego i jego typu. Rozważ procedurę składowaną, która uważa liczbę kopii książek określonego roku uwalniania. Domyślnie rok rozwoju - 2006.

Utwórz procedurę ex_books_now (@ lat \u003d 2006)
- licząc liczbę kopii książek określonego roku wydania
Tak jak
Deklaruj @N_books int.
Wybierz @N_Books \u003d Count (*) z książek, Exemplar
Gdzie books.isbn \u003d exemplar.isbn i Rygid \u003d @year
Powrót Coalesce (@N_booki, 0)
Udać się.

Na rys. 9 przedstawia przykład dzwonienia tej procedury, wskazując parametr wejściowy i bez niego.

Figa. 9. Wywołanie procedury zapisanej za pomocą parametru i bez parametru

Wszystkie przykłady stosowania parametrów w procedurach przechowywanych podanych tylko dla parametrów wejściowych. Jednak parametry mogą być wyjściem. Oznacza to, że wartość parametru po zakończeniu procedury zostanie przeniesiona do tego, który spowodował tę procedurę (inną procedurę, spust, pakiet poleceń itp.). Oczywiście, aby uzyskać parametr wyjściowy, gdy połączenie powinno być określone jako rzeczywisty parametr nie jest stałą, ale zmienną.

Należy pamiętać, że definicja w procedurze parametru, ponieważ wyjście nie zobowiązuje się do używania go jako takiego. Oznacza to, że jeśli określisz stałą jako aktualny parametr, błędy nie nastąpi i będzie używany jako normalny parametr wejściowy.

Aby określić, że używany jest parametr wyjściowy, używana jest instrukcja wyjściowa. To słowo kluczowe jest rejestrowane po opisaniu parametru. Po opisaniu parametrów procedur przechowywanych pożądane jest ustawienie wartości parametrów wyjściowych po wejściu.

Rozważmy przykład używania parametrów wyjściowych. Piszemy procedurę zapisaną, która dla danej książki oblicza całkowitą liczbę swoich instancji w bibliotece i liczbie wolnych instancji. Nie będziemy mogli korzystać z operatora zwrotu zwrotu tutaj, ponieważ zwraca tylko jedną wartość, więc musimy określić parametry wyjściowe tutaj. Tekst procedury przechowywanej może wyglądać tak:

Utwórz procedurę Count_books_all.
(@Isbn Varchar (14), @ fall int Wyjście, @Free int Wyjście)
- Procedura obliczania całkowitej liczby składowisk danej książki
- i liczba wolnych próbek
Tak jak
- Liczenie całkowitej liczby kopii
Wybierz @all \u003d Count (*) z Exemplar, gdzie ISBN \u003d @isbn
Wybierz @Free \u003d Count (*) od Exemplar, gdzie ISBN \u003d @ISBN i YES_NO \u003d "1"
Udać się.

Przykład tej procedury jest pokazany na FIG. 10.

Figa. 10. Testowanie procedur przechowywanych z parametrami wyjściowymi

Jak wspomniano wcześniej, w celu uzyskania wartości parametrów wyjściowych do analizy, musimy je ustawić zmienne, a zmienne te powinny być opisane przez Operatora deklaracji. Ostatni operator wyjściowy pozwolił nam po prostu wyświetlić uzyskane wartości.

Parametry procedury mogą nawet być zmiennymi kursorami typu. Aby to zrobić, zmienna musi być opisana jako specjalny rodzaj danych różniących się, bez wiązania ze standardowymi typami danych systemowych. Ponadto należy określić, że jest to kursor typu zmiennej.

Piszemy najprostszą procedurę, która wyświetla listę książek w naszej bibliotece. W takim przypadku, jeśli nie ma już żadnych książek, przynosimy ich nazwy w samej procedurze, a jeśli lista książek przekracza określoną liczbę, przesyłamy je jako kursor za pomocą programu wywołania lub modułu.

Tekst procedury jest następujący:

Utwórz procedurę Get3titles.
(@Mykursorowy kursor Różny wyjście)
- Tytuły procedury drukowania z kursorem
Tak jak
- Określ lokalne typ o zmiennym Kursor w procedurze
Ustaw @myCursor \u003d kursor
Aby wybrać wyraźny tytuł
Z książek.
- Otwórz kursor
Otwórz @mykursor.
- Opisz wewnętrzne zmienne lokalne
Oświadczenie @Title Varchar (80), @cnt int
--- Zamontuj stan początkowy miernika książki
Ustaw @cnt \u003d 0
- Idź do pierwszego ciągu kursora
- Podczas gdy są ciągi kursora,
- to jest, podczas gdy przejście do nowy ciąg Poprawny
Podczas gdy (@@ fetch_status \u003d 0) i (@cnt<= 2) BEGIN
Wydrukuj @title.
Pobierz Dalej @myCursor INTE @title
- Zmień status licznika książek
Ustaw @cnt \u003d @cnt + 1
Koniec.
Jeśli @cnt \u003d 0 Drukuj "Brak odpowiednich książek"
Udać się.

Przykład wywołania tej procedury zapisanej jest pokazana na FIG. jedenaście.

W procedurze wywołującego kursor musi być opisany jako zmienna lokalna. Potem nazwaliśmy naszą procedurę i przekazaliśmy nazwę kursora typu zmiennej lokalnej. Procedura zaczęła działać i przyniósł nam pierwsze trzy nazwy na ekranie, a następnie przenieść kontrolę procedury dzwoniącego, a następnie kontynuowała przetwarzanie kursora. Aby to zrobić, zorganizował cykl typu natomiast wzdłuż globalnej zmiennej @@ fetch_status, który śledzi stan kursora, a następnie w cyklu przesunął wszystkie inne linie kursora.

W oknie wyjściowym widzimy powiększony interwał między pierwszymi trzema rzędami a kolejnymi nazwami. Ten interwał wskazuje, że kierownictwo jest przekazywane do programu zewnętrznego.

Należy pamiętać, że zmienna @title, która jest lokalna dla procedury, zostanie zniszczona po zakończeniu swojej operacji, więc w procedurze telefonicznej zostanie ponownie zadeklarowany. Tworzenie i otwarcie kursora w tym przykładzie występuje w procedurze, a zamknięcie, zniszczenie i dodatkowe przetwarzanie są wykonywane w bloku poleceń, w którym wywoływana jest procedura.

Najłatwiejszym sposobem, aby zobaczyć tekst procedury, zmiany lub usuwania go za pomocą interfejsu graficznego Enterprise Manager. Ale możesz to zrobić za pomocą specjalnych systemowych procedur transact-sql. W Transact-SQL, przeglądanie procedury przeprowadza się przy użyciu procedury systemu SP_Helptekstu, a procedura systemu SP_HELP umożliwia wyświetlanie informacji o procedurze procedury. Procedury systemu SP_HELTEXT i SP_HELP są używane i przeglądać takie obiekty bazy danych, takie jak tabele, reguły i ustawienia domyślne.

Informacje o wszystkich wersjach jednej procedury, niezależnie od numeru, jest wyświetlany natychmiast. Usuwanie różnych wersji jednej procedury przechowywania występuje również jednocześnie. W poniższym przykładzie pokazano, jak wersje 1 i 2 definicje licznika_ex_all są wyświetlane, gdy jego nazwa jest wskazana jako parametr procedury systemu SP_Helptekstu (rys. 12).

Figa. 12. Wyświetl procedurę składowaną tekstową za pomocą procedury składowanej w systemie

Procedura systemu SP_Help wyświetla charakterystyki i parametry utworzonej procedury w następujący sposób:

Nazwa. Właściciel Rodzaj	Stworzony_dateTime.
Count_books_all. dBO. procedura składowana.	2006-12-06 23:15:01.217
Nazwa parametru. Rodzaj Długość.	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 odszyfrować te parametry. O czym oni rozmawiają?

1.4. Kompilacja procedury składowanej

Zaleta korzystania z procedur przechowywanych do wykonywania zestawu instrukcji Transact-SQL jest to, że są one skompilowane na pierwszym wykonaniu. Podczas procesu kompilacji instrukcja transact-sql jest konwertowana z początkowej symbolicznej prezentacji w formie wykonywalnej. Wszelkie przedmioty, do których apel procedury jest również przekształcony w alternatywny widok. Na przykład nazwy tabel są konwertowane na identyfikatory obiektów i nazwy kolumn w identyfikatorach kolumn.

Plan wykonawczy jest tworzony w taki sam sposób, aby wykonać jedną instrukcję Transact-SQL. Ten plan zawiera na przykład indeksy używane do odczytu wierszy z tabel, do których rysuje procedurę. Plan procedury jest zapisywany w pamięci podręcznej i jest używany do każdego połączenia.

UWAGA: Rozmiar pamięci podręcznej procedury można określić, dzięki czemu może zawierać większość lub wszystkie dostępne do procedury. Pozwoli to zaoszczędzić czas wymagany do ponownego wygenerowania planu procedury.

1.5. Automatyczne ponowne kompilacja

Zazwyczaj plan wykonania jest w procedurach pamięci podręcznej. Pozwala to zwiększyć wydajność, gdy jest wykonywany. Jednak w pewnych okolicznościach procedura jest automatycznie skompilowana.

• Procedura jest zawsze rekompiluowana po uruchomieniu serwera SQL. Zwykle dzieje się to po ponownym uruchomieniu systemu operacyjnego i po pierwszym wykonaniu procedury po utworzeniu.
• Plan procedury jest zawsze automatycznie rekompiluowany, jeśli wskaźnik tabeli zostanie usunięty, do którego rysuje procedurę. Ponieważ obecny plan wydaje się czytać wiersze tabeli do indeksu, który już nie istnieje, powinieneś utworzyć nowy plan wykonania. Wnioski o procedurę zostaną wykonane tylko wtedy, gdy jest aktualizowany.
• Kompilujący plan wykonania odbywa się w przypadku, gdy drugi użytkownik działa obecnie z tym planem. Dla drugiego użytkownika utworzona jest indywidualna kopia planu wykonania. Jeśli pierwsza kopia planu nie była zajęta, nie byłoby to konieczne, aby utworzyć drugą kopię. Gdy użytkownik wypełnia wykonanie procedury, plan wykonania jest dostępny w pamięci podręcznej innym użytkowniku, który ma odpowiednią rozdzielczość dostępu.
• Procedura jest automatycznie rekompiluowana, jeśli zostanie usunięta i aktualizowana. Ponieważ nowa procedura może się różnić od starej wersji, wszystkie kopie planu wykonania w pamięci podręcznej są usuwane, a plan jest ponownie kompilowany.

SQL Server ma na celu optymalizację procedur przechowywanych przez buforowanie najbardziej intensywnie stosowanych procedur. Dlatego też stary plan wykonania załadowany do pamięci podręcznej może być używany zamiast nowego planu. Aby zapobiec temu problemowi, usunąć i ponownie utworzyć procedurę zapisaną lub zatrzymać i ponownie aktywować SQL Server. Spalenia pamięć podręczną procedury i wykluczy prawdopodobieństwo pracy ze starym planem wykonania.

Procedurę można również utworzyć za pomocą opcji Rekompilowania. W tym przypadku zostanie automatycznie ponownie skompilować za każdym razem. Opcja Rekompilowania powinna być stosowana w przypadkach, w których procedura odnosi się do bardzo dynamicznych tabel, których ciągi są często dodawane są usuwane lub aktualizowane, ponieważ prowadzi do znaczących zmian w indeksy określonych w tabelach.

Jeśli procedury ponownego skompilowania nie są wytwarzane automatycznie, można je sprowadzić siłą. Na przykład, jeśli statystyki używane do określania korzystania z indeksu w tym zapytaniu, lub jeśli utworzono nowy indeks, należy wykonać wymuszone rekompulację. Aby spełnić wymuszone ponowne skompilowanie w instrukcji Execute, zastosowano ofertę Rekompilowania:

Wykonaj nazwę nazwy;
Tak jak
<инструкции Transact-SQL>
Z rekompilacją

Jeśli procedura działa z parametrami kontrolującą procedurę jego wykonania, należy użyć opcji Rekompilowania. Jeśli ustawienia procedury zapisanej mogą określić najlepszą ścieżkę wykonania, zaleca się utworzenie planu wykonania podczas pracy, a nie tworzenie go, gdy procedura jest pierwsza wzywa do użytku ze wszystkimi kolejnymi odwołań.

Uwaga: Czasami trudno jest określić, czy opcja Rekompilowania jest konieczna podczas tworzenia procedury lub nie. Jeśli są wątpliwości, lepiej nie stosować tej opcji, ponieważ procedura rekonfilowania dla każdego wypełnienia doprowadzi do utraty bardzo cennego czasu centralnego procesora. Jeśli potrzebujesz ponownej kompilacji w przyszłości podczas wykonywania procedury przechowywania, będzie możliwe, aby go wyprodukować, dodając propozycję z rekompilowaniem instrukcji wykonawczych.

Nie możesz zastosować opcji z Rekompilowaniem w instrukcji Utwórz procedurę zawierającą opcję dla replikacji. Ta opcja służy do tworzenia procedury wykonywanej podczas replikacji.

1.6. Zagnieżdżanie procedur

W procedurach przechowywanych można zakwestionować inne procedury przechowywane, istnieje jednak ograniczenie poziomów zagnieżdżania. Maksymalny poziom zagnieżdżania wynosi 32. Aktualny poziom zagnieżdżania można określić za pomocą zmiennej globalnej @@ Nestlevel.

2. Funkcje zdefiniowane przez użytkownika (UDF)

W MS SQL Server 2000 istnieje wiele z góry określonych funkcji, które pozwalają na różne działania. Jednak zawsze może być konieczne użycie pewnych określonych funkcji. Aby to zrobić, począwszy od wersji 8.0 (2000), ma możliwość opisania funkcji użytkownika (funkcje zdefiniowane przez użytkownika, UDF) i przechowują je w postaci pełnoprawnego obiektu bazy danych, wraz z procedurami przechowywanymi, reprezentacjami itp.

Wygoda korzystania z funkcji zdefiniowanych przez użytkownika jest oczywista. W przeciwieństwie do procedur przechowywanych, funkcje mogą być osadzone bezpośrednio do instrukcji SELECT i użyć ich obu, aby uzyskać określone wartości (w sekcji Wybierz) i jako źródło danych (w sekcji z odcinka).

Korzystając z UDF jako źródeł danych, ich przewaga nad reprezentacjami polega na tym, że UDF, w przeciwieństwie do widoki, może mieć parametry wejściowe, z którymi można wpływać na wynik funkcji funkcji.

Funkcje zdefiniowane przez użytkownika mogą być trzy typy: funkcje skalarne, funkcje inline. i wiele funkcji operacyjnych, które wynika z tabeli. Rozważ wszystkie te typy funkcji.

2.1. Funkcje skalarne

Funkcje skalarne zwracają jeden wynik skalarny. Wynik ten może być dowolny typ opisany powyżej, z wyjątkiem typów tekstu, ntext, obrazu i znacznika czasu. Jest to najprostsza forma funkcji. Jego składnia ma następujący formularz:

Zwraca skalar_typ_data.

Zaczynać.
body_function.
Zwróć skalar_mapsion.
Koniec.

• Parametr szyfrowania został już opisany w sekcji poświęconej procedurach przechowywanych;
• Schemabinding - wiąże funkcję diagramu. Oznacza to, że niemożliwe jest usuwanie tabel lub widoków na podstawie której opiera się funkcja, bez usuwania lub zmiany samej funkcji. Nie można również zmienić struktury tych tabel, jeśli część zmiennej jest używana przez funkcję. W związku z tym ta opcja umożliwia wykluczenie sytuacji, gdy funkcja używa dowolnych tabel lub poglądów i kogoś, nie wiedząc o tym, usunięte lub ich zmienione;
• Zwraca skalar_typ_data. - opisuje typ danych, który zwraca funkcję;
• skalar_mapsia. - Wyrażenie, które bezpośrednio zwraca wynik funkcji. Musi mieć ten sam typ, co opisany po zwrotach;
• body_funkcja jest zestawem instrukcji dotyczących Transact-SQL.

Rozważmy przykłady stosowania funkcji skalarnych.

Utwórz funkcję, że dwóch liczb całkowitych dostarczanych do wejścia w postaci parametrów wybierze najmniejszy.

Niech funkcja wygląda tak:

Utwórz funkcję Min_num (@ Int, @b int)
Zwraca int.
Zaczynać.
Deklaruj @c int.
Jeśli.< @b SET @c = @a
Else Set @c \u003d @b
Wróć @c.
Koniec.

Wykonamy teraz tę funkcję:

Wybierz DBO.MIN_NUM (4, 7)

W rezultacie otrzymujemy wartość 4.

Możesz zastosować tę funkcję do znalezienia najmniejszego wśród wartości kolumny tabeli:

Wybierz min_lvl, max_lvl, min_num (min_lvl, max_lvl)
Z pracy.

Utwórz funkcję, która otrzyma parametr typu DateTime i zwróć datę i godzinę odpowiadającą początkowi określonego dnia. Na przykład, jeśli parametr wejściowy wynosi 20.09.03 1333, wówczas wynik będzie 20.09.03 00:00.

Utwórz funkcję DBO.DYBEGIN (@DAT DATETIME)
Zwraca smallDateTime jak.
Zaczynać.
Konwertuj zwrot (Datetime, Floor (Convert (Float, @Dat)))
Koniec.

Tutaj funkcja Konwersja wykonuje konwersję typu. Po pierwsze, typ daty jest napędzany przez rodzaj pływaka. Dzięki tej wiodącej całej części jest liczba dni, licząc od 1 stycznia 1900 roku, a czas ułamkowy. Następnie zaokrągla się do mniejszej całości za pomocą funkcji podłogi i wprowadzenie do typu daty.

Sprawdź funkcję działania:

Wybierz DBO.DYBEGIN (GETDATE ())

Tutaj GetDate () to funkcja, która zwraca bieżącą datę i godzinę.

Poprzednie funkcje zastosowano przy obliczaniu tylko parametrów wejściowych. Możesz jednak użyć danych przechowywanych w bazie danych.

Utwórz funkcję, która otrzyma dwa daty jako parametry: początek i koniec przedziału czasu - i oblicz całkowitych przychodów ze sprzedaży dla tego interwału. Data sprzedaży i ilości zostanie pobrana ze stoliki sprzedaży, a ceny sprzedawanych edycji znajdują się w tabeli tytułów.

Twórz funkcję DBO.Sumsales (@Datebegin DateTime, @Dateend DateTime)
Zwraca pieniądze.
Tak jak
Zaczynać.
Deklaruj pieniądze @sum.
Wybierz @Sum \u003d Sum (T.Price * S.QTY)

Zwróć @sum.
Koniec.

2.2. Funkcje inline.

Ten typ funkcji zwraca wartość skalarną w wyniku czego i tabela, lub raczej zestaw danych. Może to być bardzo wygodne w przypadkach, gdy w różnych procedurach, wyzwalacze itp. Często wykonywane jest ten sam rodzaj podzewności. Następnie, zamiast napisać to żądanie wszędzie, możesz utworzyć funkcję i dalej go używać.

Funkcje tego typu są jeszcze bardziej przydatne w przypadkach, w których jest to wymagane, aby zwrócona tabela zależy od parametrów wejściowych. Wiesz, widoki nie mogą mieć parametrów, dlatego tylko funkcje inline mogą rozwiązać problem.

Cechą Fuccia Inline jest to, że mogą zawierać tylko jedno żądanie w organizmie. W ten sposób funkcje tego typu są bardzo podobne do poglądów, ale mogą dodatkowo mieć parametry wejściowe. Składnia funkcji inline:

Utwórz funkcję [Właściciel] Nazwa funkcji
([(Nazwa parametrów Scalar_tip_data [\u003d Value_p__Oromolement]) [, ... N]])
Zwraca tabelę

Powrót [(<запрос>)]

W definiowaniu funkcji wskazuje, że zwróci tabelę;<запрос> - To jest zapytanie, którego wynik będzie wynikiem funkcji.

Piszemy funkcję podobną do funkcji skalaru z ostatniego przykładu, ale zwracając nie tylko wynik sumujący, ale także linie sprzedaży, w tym datę sprzedaży, nazwa książki, ceny, liczbę sztuk i ilość sprzedaż. Należy wybrać tylko te sprzedaż, które wchodzą w określony czas. Szyfrujemy tekst funkcji, aby inni użytkownicy mogli go użyć, ale nie mogli go przeczytać i poprawić:

Utwórz funkcję Sales_period (@Datebegin DateTime, @Dateend DateTime)
Zwraca tabelę
Z szyfrowaniem
Tak jak
Powrót (
Wybierz t.Title, T.Price, S.QTY, Ord_date, T.Price * S.QTY jako STOIM
Od tytułów T. Dołącz sprzedaż s na t.title_id \u003d s.title_id
Gdzie ord_date między @dateebegin a @dateend
)

Teraz zadzwoń do tej funkcji. Jak już wspomniano, możesz zadzwonić tylko w sekcji z operatora Wybierz:

Wybierz * od Sales_period ("01.09.94", "13.09.94")

2.3. Wiele funkcji operacyjnych, które wynika z tabeli

Pierwszy uważany rodzaj funkcji umożliwił korzystanie z wielu instrukcji dotyczących transact-sql, ale zwrócił tylko wynik skalarny. Drugi typ funkcji może zwrócić tabele, ale jego ciało reprezentuje tylko jedno żądanie. Funkcje wieloosobowe, które zwracają wynik tabelarycznego, umożliwiają połączenie właściwości pierwszych dwóch funkcji, które jest, mogą one zawierać w organizmie wiele instrukcji dotyczących transaksu-sql i zwrócić tabelę w rezultacie. Składnia funkcji wielofunkcyjnej:

Utwórz funkcję [Właściciel] Nazwa funkcji
([(Nazwa parametrów Scalar_tip_data [\u003d Value_p__Oromolement]) [, ... N]])
Zwraca tabelę @ Name_named_leult
<описание_таблицы>

Zaczynać.
<тело_функции>
Powrót.
Koniec.

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

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

Niech będzie drzewo katalogowe i pliki leżące w nich. Niech całą strukturę zostanie opisane w bazie danych w postaci tabel (rys. 13). W rzeczywistości tutaj mamy hierarchiczną strukturę do katalogów, więc schemat zawiera połączenie tabeli folderów ze sobą.

Figa. 13. Struktura bazy danych do opisywania hierarchii plików i katalogów

Teraz napisz funkcję, która otrzyma identyfikator katalogów na wejściu i wyjściu Wszystkie pliki przechowywane w nim i we wszystkich katalogach w dół hierarchii. Na przykład, jeśli katalogi instytutu są tworzone w katalogu wydziału1, wydziału2 itp. Istnieją katalogi Departamentu, w każdym z katalogów znajdują się pliki, a następnie po określeniu parametru identyfikatora katalogu, Instytut musi mieć listę wszystkich plików dla wszystkich tych katalogów. Dla każdego pliku należy wyświetlić nazwę, rozmiar i datę tworzenia.

Niemożliwe jest rozwiązanie zadania za pomocą funkcji Inline, ponieważ SQL nie ma na celu wykonywania hierarchicznych zapytań, więc jedna prośba SQL nie jest tutaj. Nie można również zastosować funkcji skalarnej, ponieważ wynik powinien być tabelą. Tutaj musimy pomóc i uzyskać wielofunkcyjną funkcję, która zwraca tabelę:

Utwórz funkcję DBO.GetFiles (@ Folder_id int)
Zwroty @files Table (Nazwa Varchar (100), Date_Create DateTime, plikissize int) AS
Zaczynać.
DELIGE @TMP Tabela (folder_id int)
Deklaruj @cnt int.
Włóż do wartości @Tmp (@ Folder_id)
Ustaw @cnt \u003d 1
Podczas @cnt.<> 0 Rozpocznij.
Wstaw do @Tmp Wybierz folder_id_id
Od folderów fo @tmp t na f f.parent \u003d t.folder_id
Gdzie folder_id z @tmp), w którym folder_id z
Ustaw @cnt \u003d @@ rowcount
Koniec.
Wstaw do @files (nazwa, dat_create, plikise)
Wybierz f.name, f.date_create, f.filesize
Z plików fold folders fl on f.folder_id \u003d fl.id
Dołącz @tmp t na fl.id \u003d t.folder_id
Powrót.
Koniec.

Tutaj w cyklu w zmiennej @Tmp Dodaj wszystkie załączone katalogi na wszystkich poziomach zagnieżdżenia, aż zagnieżdżone katalogi pozostanie. Następnie wszystkie niezbędne atrybuty plików w katalogach wymienione w zmiennej @TMP są rejestrowane w zmiennej wyników @files.

Zadania dla niezależnej pracy

Musisz utworzyć i debugować pięć procedur przechowywanych z następującej listy obowiązkowej:

Procedura 1. Wzrost w momencie dostawy kopii książki na tydzień, jeśli obecny czas dostawy leży w ciągu trzech dni przed datą bieżącej do trzech dni po bieżącej dacie.

Procedura 2. Liczenie liczby bezpłatnych wystąpień danej książki.

Procedura 3. Sprawdź istnienie czytelnika z określonym nazwiskiem i datą urodzenia.

Procedura 4. Wprowadzanie nowego czytnika z kontrolą jej istnienia w bazie danych i definicji numeru biletu nowego czytnika.

Procedura 5. Liczenie grzywny w warunkach pieniężnych dla czytelników dłużników.

Krótki opis procedur

Procedura 1. Zwiększone książki

Dla każdego wpisu w tabeli Exemplar jest sprawdzany, jeśli data dostawy książki wpada w określony przedział czasu. Jeśli trafi, data powrotu książki wzrasta na tydzień. Podczas wykonywania procedury należy użyć funkcji dat:

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

Procedura 2. Liczenie liczby bezpłatnych wystąpień określonej książki

Parametr wejściowy procedury jest ISBN - unikalny szyfr książki. Procedura zwraca 0 (zero), jeśli wszystkie instancje tej książki znajdują się na rękach czytelników. Procedura zwraca wartość N, równa liczbie kopii książki, które są obecnie w rękach czytelników.

Jeśli książki z ustalonym ISBN nie znajdują się w bibliotece, procedura zwraca -100 (minus sto).

Procedura 3. Sprawdzanie istnienia czytelnika z określonymi nazwiskami i datą urodzenia

Procedura zwraca czytnik czytnika, jeśli czytnik ma takie dane istnieje, i 0 (zero) w przeciwnym razie.

Porównując datę urodzenia, należy użyć funkcji konwersji Konwertuj (), aby przekonwertować datę urodzenia - typ zmiennej znakowej Varchar (8) używany jako parametr wejściowy procedury do danych DataTime, który jest używany w tabeli czytelników. W przeciwnym razie operacja porównawcza podczas wyszukiwania tego czytnika nie będzie działać.

Procedura 4. Wprowadzanie nowego czytnika

Procedura ma pięć parametrów wejściowych i trzech wyjść.

Parametry wejściowe:

• Pełna nazwa z inicjałami;
• Adres;
• Data urodzenia;
• Telefon domowy;
• Pracownik telefoniczny.

Parametry wyjściowe:

• Numer czytelnika;
• Znak, czy czytelnik został wcześniej nagrany w bibliotece (0 - nie, 1 był);
• Liczba książek wymienionych po czytelniku.

Procedura 5. Liczenie grzywny w warunkach pieniężnych dla czytelników dłużników

Procedura działa z kursorem, który zawiera listę numerów biletów czytnika wszystkich dłużników. Podczas pracy należy utworzyć globalną tabelę tymczasową ## Dolg, w której jego całkowity dług zostanie wymieniony dla każdego dłużnika dla wszystkich książek, które trwały dłuższe zwroty. Odszkodowanie monetarne oblicza się w 0,5% ceny za książkę na dzień opóźnienia.

Procedura wykonywania pracy

• kopie ekranów (zrzuty ekranu) Potwierdzające zmiany w bazie danych;
• zawartość tabel bazy danych, które są wymagane do potwierdzenia poprawności pracy;
• procedura składana tekstowa z komentarzami;
• proces rozpoczęcia procedury przechowywanej z wyjściem wyników wydajności.

Dodatkowe zadania

Poniższe dodatkowe procedury składowane są przeznaczone do indywidualnych zadań.

Procedura 6. Liczenie liczby książek na danym obszarze tematycznym, który obecnie istnieje w bibliotece przynajmniej w jednej instancji. Obszar tematu jest przesyłany jako parametr wejściowy.

Procedura 7. Wprowadź nową książkę wskazującą jego instancje. Podczas wprowadzania instancji nowej książki nie zapomnij wprowadzić poprawnych numerów zapasów. Pomyśl, jak można to zrobić. Przypominam, że masz funkcje Max i Min, które pozwalają znaleźć maksymalną lub minimalną wartość dowolnego atrybutu numerycznego do narzędzi Wybierz żądanie.

Procedura 8. Utworzenie tabeli z listą czytelników dłużników, czyli, którzy mieli zwrócić książki do biblioteki, ale jeszcze nie wrócił. W wynikowej tabeli każdy Dłużnik powinien pojawić się tylko raz, niezależnie od tego, ile książek zawdzięcza. Oprócz nazwy i numeru czytnika w wynikowej tabeli należy określić adres i adres telefonu.

Procedura 9. Wyszukaj bezpłatną kopię określonej nazwy książki. Jeśli jest wolna kopia, procedura zwraca numer inwentaryzacji instancji; Jeśli nie, procedura zwraca listę czytelników, którzy mają tę książkę, wskazując datę zwrotu książki i telefonem czytnika.

Procedura 10. Wyjście listy czytelników, którzy w tej chwili nie posiadają jednej książki na ręce. Na liście określ nazwę i telefon.

Procedura 11. Wyjście listy książek wskazujących liczbę kopii tej książki w bibliotece i liczbie darmowych instancji w tej chwili.

Wydrukuj wersję

Procedury zapisane SQL są wykonywalnym modułem oprogramowania, który można przechowywać w różnych obiektach. Innymi słowy, jest to obiekt, w którym zawarte są instrukcje SQL. Te przechowywane procedury można wykonać w kliencie aplikacji, aby uzyskać dobrą wydajność. Ponadto takie obiekty są często wywoływane z innych scenariuszy lub nawet z jakiejkolwiek innej partycji.

Wprowadzenie

Wielu wierzy, że są podobne do procedur różnych (odpowiednio, z wyjątkiem MS SQL). Być może to prawda. Mają podobne parametry, mogą wytwarzać podobne wartości. Co więcej, w niektórych przypadkach wchodzą w kontakt. Na przykład łączy się z bazami danych DDL i DML, a także z funkcjami użytkownika (nazwa kodu - UDF).

W rzeczywistości procedury SQL przechowywane mają szeroki zakres korzyści, które przydzielają je wśród takich procesów. Bezpieczeństwo, zmienność programowania, wydajność - wszystko to przyciąga użytkowników pracujących z bazami danych, coraz więcej. Szczyt popularności procedur spadł na lata 2005-2010, kiedy program Microsoft o nazwie "SQL Server Management Studio" został opublikowany. Dzięki temu była znacznie łatwiejsza do pracy z bazami danych, bardziej praktycznymi i wygodniejszymi. Z roku na rok zdobył popularność w programistrach. Dzisiaj jest absolutnie znany program, który dla użytkowników, "komunikowanie" z bazami danych, stał na równi z "Excel".

Podczas wywołania procedury jest natychmiast przetwarzany przez sam serwer bez niepotrzebnych procesów i interwencji użytkownika. Następnie możesz wykonać wszelkie usuwanie, wykonanie, zmianę. W tym wszystkim jest odpowiedzialny przez operatora DDL, który sam dokonuje najbardziej złożonych skutków przetwarzania obiektów. A wszystko to dzieje się bardzo szybko, a serwer nie jest faktycznie załadowany. Taka prędkość i wydajność umożliwiają bardzo szybkie przesyłanie dużych ilości informacji od użytkownika do serwera i odwrotnie.

Aby wdrożyć tę technologię, istnieje kilka języków programowania. Należą do nich, na przykład, PL / SQL z Oracle, PSQL w systemach Interbase i Firebird, a także klasyczny "Microsoft" Transact-SQL. Wszystkie z nich mają na celu tworzenie i wykonanie procedur przechowywanych, co pozwala na duże oferty obsługi bazy danych do korzystania z własnych algorytmów. Jest to konieczne dla tych, którzy zarządzają takimi informacjami, mogą chronić wszystkie obiekty przed nieautoryzowanym dostępem osób trzecich i, odpowiednio, tworzenie, modyfikacji lub usuwania niektórych danych.

Wydajność

Te obiekty bazy danych można zaprogramować na różne sposoby. Pozwala to użytkownikom wybrać rodzaj stosowanej metody, która będzie najbardziej odpowiednia, która oszczędza siłę i czas. Ponadto sama procedura jest przetwarzana, która pozwala uniknąć ogromnych kosztów wymiany między serwerem a użytkownikiem. Ponadto moduł może być przeprogramowany i zmieniony na żądany kierunek w absolutnie w dowolnym momencie. W szczególności warto zwrócić uwagę na szybkość, z jaką rozpoczęto rozpoczęcie procedury zapisanej SQL: proces ten występuje szybciej niż inne podobne do niego, co czyni go wygodnym i uniwersalnym.

Bezpieczeństwo

Ten rodzaj przetwarzania informacji różni się od podobnych procesów, ponieważ gwarantuje zwiększone bezpieczeństwo. Jest to zapewnione dzięki temu, że dostęp innych użytkowników do procedur można wyłącznie wyeliminować. Pozwoli to administratorowi prowadzić z nimi operacje niezależnie, bez strachu przed przechwytywaniem informacji lub nieautoryzowanego dostępu do bazy danych.

Transfer danych

Związek między procedurą zapisaną przez SQL a aplikacją klienta jest użycie parametrów i zwracanych wartości. Ten ostatni nie może zostać przeniesiony do danych w procedurze przechowywanej, jednak te informacje (głównie na żądanie użytkownika) i przetworzone dla SQL. Po zakończeniu procedury przechowywania odnosi się do jej pracy, odnosi się do pakietów danych z powrotem (ale ponownie, jeśli jest to pożądane) do aplikacji, która spowodowała, że \u200b\u200bprzy użyciu różnych metod, które mogą być zaimplementowane jako wezwanie do procedury przechowywanej SQL i powrotu, na przykład:

Transmisja danych za pomocą parametru typu wyjściowego;

Transfer danych za pomocą operatora zwrotu;

Transmisja danych za pomocą operatora wyboru.

A teraz zrozumiemy to, jak ten proces wygląda z wnętrza.

1. Tworzenie procedury zapisanej przez EXEC w SQL

Możesz utworzyć procedurę w MS SQL (Studio Management). Po utworzeniu procedury zostanie wyświetlony na programowalny węzeł baz danych, w którym procedura tworzenia jest wykonywana przez operatora. Aby wykonać zapisane procedury SQL, użyj procesu EXEC, który zawiera nazwę samego obiektu.

Podczas tworzenia procedury jego nazwa pojawia się najpierw, po czym przypisano jeden lub więcej parametrów. Parametry mogą być opcjonalne. Po parametrze (s), treść procedury zostanie zapisana, musisz przeprowadzić pewne niezbędne operacje.

Faktem jest, że ciało może mieć lokalne zmienne znajdujące się w niej, a zmienne te są lokalne i w stosunku do procedur. Innymi słowy, można je rozważyć tylko w ciele procedury Microsoft SQL Server. Procedury przechowywane w tym przypadku są uważane za lokalne.

Tak więc, aby utworzyć procedurę, potrzebujemy nazwy procedury i co najmniej jeden parametr jako treść procedury. Należy pamiętać, że doskonała opcja w tym przypadku jest tworzenie i wykonanie procedury z nazwą schematu w klasierze.

Ciało procedury może mieć jakiś przykładowy przykład, taki jak tworzenie tabeli, włóż jedną lub więcej wierszy stołowych, ustawiając typ i charakter bazy danych, i tak dalej. Niemniej jednak organizm procedury ogranicza realizację niektórych operacji. Niektóre ważne ograniczenia są wymienione poniżej:

Ciało nie powinno tworzyć żadnej innej procedury przechowywanej;

Ciało nie powinno tworzyć fałszywego zrozumienia obiektu;

Ciało nie powinno tworzyć żadnych wyzwalaczy.

2. Instalacja zmiennej w treści procedurze

Możesz dokonać zmiennych lokalnych dla procedury organizmu, a następnie będą one wyłącznie wewnątrz procedury. Dobrą praktyką jest tworzenie zmiennych na początku ciała procedury przechowywanej. Ale możesz również ustawić zmienne w dowolnym miejscu w ciele tego obiektu.

Czasami widać, że kilka zmiennych jest zainstalowanych w jednym wierszu, a każdy parametr zmiennych jest oddzielony przecinkiem. Należy również pamiętać, że zmienna ma przedrostek @. W treści procedury możesz ustawić zmienną, w której chcesz. Na przykład, zmienna @ Name1 można zadeklarować bliżej końca korpusu procedury. Aby przypisać wartość ogłoszonej zmiennej, używany jest zestaw danych osobowych. W przeciwieństwie do sytuacji, gdy zadeklarował więcej niż jedną zmienną w jednej linii, w takiej sytuacji stosuje się tylko jeden zestaw danych osobowych.

Często użytkownicy zadają pytanie: "Jak przypisać wiele wartości w jednym operatorze w treści procedury?" Dobrze. Pytanie jest interesujące, ale znacznie łatwiej jest robić to, co myślisz. Odpowiedź: Korzystanie z pary, takiego jak "Wybierz var \u003d wartość". Możesz użyć tych par, oddzielając ich przecinek.

W wielu różnych przykładach ludzie pokazują tworzenie prostej procedury przechowywanej i wykonując go. Jednak procedura może podjąć takie parametry, że proces powodujący, będzie miało bliskie wartości (ale nie zawsze). Jeśli pasują, odpowiednie procesy zaczynają się w ciele. Na przykład, jeśli utworzysz procedurę, która zajmie miasto i region od dzwoniącego i zwrotu danych o tym, ile autorów odnosi się do odpowiedniego miasta i regionu. Procedura poprosi o tabelę autorów bazy danych, na przykład, pubów, aby wykonać to liczenie autorów. Aby uzyskać na przykład te bazy danych, Google ładuje skrypt SQL z strony SQL2005.

W poprzednim przykładzie procedura bierze dwa parametry, które w języku angielskim uzyskuje się zwalnianie @state i @city. Typ danych odpowiada typowi zdefiniowanym w aplikacji. Ciało procedury ma zmienne wewnętrzne @Totalathors (total autorzy), a ta zmienna służy do wyświetlania ich ilości. Pojawi się następujący sekcja wyboru, co liczy wszystko. Wreszcie obliczona wartość jest wyświetlana w oknie wyjściowym za pomocą instrukcji drukowania.

Jak wykonać procedurę składowaną w SQL

Istnieją dwa sposoby wykonania procedury. Pierwsza ścieżka pokazuje przekazywanie parametrów, ponieważ lista oddzielona przecinkiem jest wykonywana po nazwie procedury. Przypuśćmy, że mamy dwie wartości (jak w poprzednim przykładzie). Wartości te są gromadzone przy użyciu zmiennych parametrów procedury @state i @city. W tej metodzie przekazywania parametrów należy zamówienie. Ta metoda nazywa się zwykłym transferem argumentami. W drugim metodzie parametry są już wyznaczone bezpośrednio, aw tym przypadku zamówienie nie jest ważne. Ta druga metoda jest znana jako transfer nazwanych argumentów.

Procedura może być nieco odchylona od typowego. Stlikely, jak w poprzednim przykładzie, ale tylko tutaj parametry są przesunięte. Oznacza to, że parametr @city jest przechowywany pierwszy, a @state jest przechowywane obok wartości domyślnej. Domyślny parametr jest zwykle oddzielony oddzielnie. Procedury zapisane SQL przechodzą jako po prostu parametry. W tym przypadku pod warunkiem, że parametr UT zastępuje domyślne "CA". W drugiej egzekucji tylko jedna wartość argumentu dla przepustek parametrów @city, a parametr @state przyjmuje domyślne "CA". Doświadczeni programiści radzą, aby domyślnie wszystkie zmienne są bliższe do końca listy parametrów. W przeciwnym razie wykonanie nie jest możliwe, a następnie musisz pracować z przeniesieniem wymienionych argumentów, co jest dłuższe i trudniejsze.

4. Zapisane procedury SQL Server: Metody powrotu

Istnieją trzy ważne sposoby wysyłania danych w procedurze przechowywanej. Są wymienione poniżej:

Zwróć wartość procedury przechowywanej;

Wyjście procedur przechowywanych;

Wybór jednej z procedur przechowywanych.

4.1 Powrót wartości procedury zapisanej SQL

W tej technice procedura przypisuje wartość zmiennej lokalnej i zwraca go. Procedura może również bezpośrednio zwrócić stałą wartość. W poniższym przykładzie stworzyliśmy procedurę zwracającą całkowitą liczbę autorów. Jeśli porównujesz tę procedurę z poprzednimi, widać, że wartość drukowania jest zastępowana przez odwrót.

Zobaczmy teraz, jak wykonywać procedurę i wyświetlić wartość powróconą do niego. Wykonanie procedury wymaga ustanowienia zmiennej i drukowania, który jest przeprowadzany po całym procesie. Należy pamiętać, że zamiast instrukcji drukowania można użyć Operatora Wybierz, na przykład wybierz @RetValue, a także wyjśćFalue.

4.2 Wyjście parametrów Procedura SQL

Wartość odpowiedzi może być użyta do powrotu jednej zmiennej, którą widzieliśmy w poprzednim przykładzie. Korzystanie z parametru wyjściowego pozwala procedurę wysyłać jedną lub więcej wartości zmiennych dla rozmówcy. Parametr wyjściowy jest wskazany jak w przypadku tego słowa "Wyjście" podczas tworzenia procedury. Jeśli parametr jest określony jako parametr wyjściowy, obiekt procedury musi przypisać do niego wartość. Procedury przechowywane SQL, których przykłady można zobaczyć poniżej, w którym to przypadku są zwracane z ostatecznymi informacjami.

W naszym przykładzie pojawią się dwie nazwy wyjściowe: @Totalathors i @totalnocNocontract. Są one określone na liście parametrów. Te zmienne przypisują wartości w treści procedury. Gdy używamy parametrów wyjściowych, rozmówca może zobaczyć wartość ustawioną w treści procedury.

Ponadto w poprzednim scenariuszu ogłasza dwie zmienne, aby zobaczyć wartości, które ustawiają procedury pamięci masowej SQL Server w parametrze wyjściowym. Następnie procedura jest wykonywana, składając normalną wartość parametru "CA". Poniższe parametry są wyprowadzane, a zatem deklarowane zmienne są przesyłane w wyznaczonym sposobie. Należy pamiętać, że podczas przekazywania zmiennych pojawia się również słowo kluczowe wyjścia. Po zakończeniu procedury wartości zwracane przez parametry wyjściowe są wyświetlane w oknie Wiadomości.

4.3 Wybór jednego z procedur przechowywanych SQL

Ta technika służy do przywrócenia zestawu wartości jako tabeli danych (zestaw rekordów) do procedury przechowywanej. W tym przykładzie procedura przechowywana SQL z parametrami @autide wymaga tabeli "autorów", filtrując zwracane wpisy za pomocą tego parametru @autida. Oświadczenie SELECT decyduje, że musi zostać zwrócony do procedury przechowywania dzwoniącego. Podczas wykonywania procedury zapisanej authidid jest przesyłany z powrotem. Taka procedura zawsze zwraca tylko jeden wpis lub każdy. Ale procedura przechowywana nie ma żadnych ograniczeń w odniesieniu do zwrotu więcej niż jednego rekordu. Często można znaleźć przykłady, w których powrót danych przy użyciu wybranych parametrów z udziałem zmiennych obliczonych występuje, zapewniając kilka wartości wynikowych.

Wreszcie

Procedura zapisana jest dość poważnym modułem oprogramowania, który zwraca lub przesyłanie, a także instalację niezbędnych zmiennych dzięki aplikacji klienckim. Ponieważ procedura zapisana jest wykonywana na samym serwerze, można uniknąć wymiany danych w ogromnych wolumenach między serwerem a aplikacją klienta (dla niektórych obliczeń). Pozwala to zmniejszyć ładunek na serwerze SQL, który oczywiście idzie do ręki swoich posiadaczy. Jeden z podgatunków są przechowywane procedury SQL, ale ich badanie jest potrzebne do tych, którzy są zaangażowani w stworzenie imponujących baz danych. Istnieje również duża, nawet ogromna liczba niuansów, które mogą być przydatne podczas studiowania procedur przechowywanych, ale konieczne jest więcej dla tych, którzy planują ściśle angażować się w programowanie, w tym profesjonalnie.

cel pracy - Dowiedz się, jak tworzyć i korzystać z procedur przechowywanych na serwerze bazy danych.

1. Badania wszystkich przykładów, analizę wyników ich wykonania w narzędziu SQL Server Management Studio. Sprawdzanie obecności tworzonych procesów w bieżącej bazy danych.

2. Wykonaj wszystkie przykłady i zadania w trakcie pracy laboratoryjnej.

3. Wykonaj indywidualne zadania przez opcje.

Wyjaśnienie pracy

Aby opanować procedury przechowywane programowania, użyj bazy danych, gdy nazwa bazy danych Db_booki.który został stworzony w laboratorium Numer 1. Podczas wykonywania przykładów i zadań, zapłacić za zgodność z nazwami bazy danych, tabel i innych obiektów projektu.

Procedury przechowywane Istnieje zestaw poleceń składających się z jednego lub więcej operatorów SQL lub funkcji i przechowywane w bazie danych w formularzu kompilacji.

Rodzaje procedur przechowywanych

Procedury przechowywane systemowe mają na celu wykonywanie różnych działań administracyjnych. Prawie wszystkie działania administracyjne serwera są wykonywane za pomocą ich pomocy. Można powiedzieć, że procedury zapisane w systemie są interfejsem, który zapewnia pracę z tabelami systemowymi. Procedury zapisane w systemie mają prefiks SP_, są przechowywane w bazie danych systemu i może być spowodowane w kontekście dowolnej innej bazy danych.

Niestandardowe procedury zapisane wdrażają pewne działania. Procedury przechowywane - pełnoprawny obiekt bazy danych. W rezultacie każda procedura zapisana znajduje się w określonej bazie danych, gdzie jest wykonywana.

Tymczasowe procedury przechowywane istnieją tylko chwilę, po czym są automatycznie zniszczone przez serwer. Są podzielone na LAGIC i Global. Lokalne tymczasowe procedury przechowywane mogą być spowodowane tylko z tego związku, w którym jest tworzony. Podczas tworzenia takiej procedury musi podać nazwę zaczynając od jednego symbolu #. Podobnie jak wszystkie obiekty tymczasowe, przechowywane procedury tego typu są automatycznie usuwane, gdy użytkownik jest odłączony, ponownie uruchomić lub zatrzymać serwer. Global Tymczasowe procedury są dostępne dla dowolnych połączeń serwerowych, na których istnieje ta sama procedura. Aby to określić, wystarczy dać im nazwę zaczynając od znaków ##. Procedury te są usuwane podczas ponownego uruchomienia lub zatrzymywania serwera, a także podczas zamykania połączenia, w kontekście, którego zostały utworzone.

Tworzenie, zmieniając procedury przechowywane

Stworzenie procedury przechowywanej obejmuje decyzję o następujących stawkach: Planowanie praw dostępu. Podczas tworzenia procedury przechowywanej należy pamiętać, że będzie miał te same prawa dostępu do obiektów bazy danych, które stworzyły swojego użytkownika; Określanie parametrów procedur sklepowych, procedury przechowywane mogą mieć parametry wejściowe i wyjściowe; Opracowanie procedury składowanej kodu. Kodeks procedury może zawierać sekwencję dowolnych poleceń SQL, w tym wywoływanie innych procedur przechowywanych.

Składnia operatora utworzenia nowej lub zmiany dostępnej procedury przechowywanej w oznaczeniach MS SQL Server:

(Utwórz | Alter) ProC [Name] Name_name [; Number] [(@ parametry_name type_data) [Varying] [\u003d domyślne] [wyjście]] [, ... N] [z (Rekompilowanie | Szyfrowanie | Rekompilowanie, szyfrowanie)] [Do replikacji] jako operatora SQL_ [... N]

Rozważ parametry tego polecenia.

Korzystanie z SP_, #, ## prefiksy utworzone przez procedurę można zdefiniować jako systemowe lub tymczasowe. Jak widać z składni polecenia, nie wolno określić nazwy właściciela, do którego utworzona procedura, a także nazwa bazy danych, gdzie należy umieścić. W ten sposób umieścić utworzoną procedurę zapisaną w określonej bazie danych, należy uruchomić polecenie Utwórz procedurę w kontekście tej bazy danych. W kontakcie z treścią procedury zapisanej do obiektów tej samej bazy danych można użyć skróconych nazw, tj. Bez określania nazwy bazy danych. Gdy musisz odnieść się do obiektów znajdujących się w innych bazach danych, określając nazwę bazy danych.

Aby przenieść dane wejściowe i wyjściowe w utworzonej procedurze zapisanej, nazwy parametrów muszą rozpocząć się od symbolu @. W jednej procedurze przechowywanej można ustawić wiele parametrów oddzielonych przecinkami. W organizmie procedury nie należy stosować zmienne lokalne, których nazwy są zbiegły się z nazwami parametrów tej procedury. Aby określić typ tych parametrów procedury zapisanej, odpowiednie są wszystkie typy danych SQL, w tym zdefiniowany przez użytkownika. Jednak typ danych kursora może być używany tylko jako parametr wyjściowy procedury zapisanej, tj. Z wyjściem słów kluczowych.

Obecność słowa kluczowego wyjścia oznacza, że \u200b\u200bodpowiedni parametr jest zaprojektowany do zwracania danych z procedury zapisanej. Nie oznacza to jednak, że parametr nie nadaje się do przekazywania wartości w procedurze przechowywanej. Określanie słów kluczowych wyjściowych instruuje serwer podczas wychodzenia z procedury zapisanej w celu przypisania bieżącej wartości parametru zmiennego lokalnego, który został określony, gdy procedura jest wywoływana jako wartość parametru. Należy pamiętać, że przy określaniu wyjścia słów kluczowych wartość odpowiedniego parametru podczas wywoływania procedury można określić tylko za pomocą zmiennej lokalnej. Nie wolno używać żadnych wyrażeń ani stałych dopuszczalnych dla konwencjonalnych parametrów. Zmienne słowo kluczowe jest używane w połączeniu z parametrem wyjściowym mającą rodzaj kursora. Określa, że \u200b\u200bparametr wyjściowy będzie wynikowy zestaw.

Domyślne słowo kluczowe to wartość, która zaakceptuje odpowiedni domyślny parametr. Tak więc, gdy wywoływana jest procedura, nie można określić wartości odpowiedniego parametru.

Ponieważ serwer buforuje plan wykonania zapytań i skompilowany kod, po kolejnym wywołaniu, procedury będą używane już gotowe. Jednak w niektórych przypadkach nadal konieczne jest ponowne obliczenie kodu procedury. Określanie pliku słów kluczowych przepilenia przepisuje system do utworzenia planu procedury przechowywanej dla każdego połączenia.

Parametr do replikacji jest na żądanie, gdy replikujesz dane i włączenie utworzonej procedury zapisanej jako artykułu w publikacji. Słowo kluczowe szyfrowania instruuje serwer do szyfrowania kodeksu procedury przechowywanej, co może zapewnić ochronę przed zastosowaniem algorytmów praw autorskich, które realizują pracę procedury składowanej. Kluczowe słowo jak znajduje się na początku ciała procedury zapisanej. W organizmie procedury prawie wszystkie polecenia SQL mogą być stosowane, zadeklarowane transakcje, blokują blokadę i połączyć inne procedury przechowywane. Wyjście procedury zapisanej można wykonać za pomocą polecenia zwrotnego.

Usuwanie procedury przechowywanej

Procedura kropli (nazwa) [, ... N] N]

Wykonywanie procedury zapisanej

Aby wykonać procedurę zapisaną, używana jest polecenie: [[EXEC [UTE] Name_name [; Number] [[@ parametry_name \u003d] (wartość | @_name_name) [wyjście] | [Domyślnie]] [, ... N]

Jeśli wezwanie do procedury zapisanej nie jest jedynym poleceniem w opakowaniu, wymagana jest obecność polecenia Execute. Co więcej, to polecenie jest wymagane do wywołania procedury z treści innej procedury lub wyzwalacza.

Korzystanie ze słowa kluczowego podczas wywoływania procedury jest rozwiązany tylko dla parametrów ogłoszonych podczas tworzenia procedury z wyjściem słowa kluczowym.

Po wywołaniu procedury domyślne słowo kluczowe jest określone dla parametru, zostanie użyta wartość domyślna. Zasadniczo określone słowo domyślne jest dozwolone tylko dla tych parametrów, dla których zdefiniowana jest wartość domyślna.

Z składni komendy Execute, jasne jest, że nazwy parametrów można pominąć po wywołaniu procedury. Jednak w tym przypadku użytkownik musi określić wartości parametrów w tej samej kolejności, w której zostały wymienione podczas tworzenia procedury. Przypisz wartość domyślną do parametru po prostu przekazując go, gdy jest na liście, jest to niemożliwe. Jeśli wymagane są parametry, dla których wartość domyślna jest zdefiniowana, wystarczająco wyraźne wskazanie nazwy parametru podczas wywołania procedury zapisanej. Ponadto w taki sposób można wymienić parametry i ich wartości w dowolnej kolejności.

Należy pamiętać, że podczas wywoływania procedury należy podać nazwy parametrów o wartościach lub tylko wartości bez nazwy parametru. Ich kombinacja nie jest dozwolona.

Użyj powrotu w procedurze przechowywanej

Umożliwia wyjście z procedury w dowolnym punkcie w określonym stanie, a także pozwala na przeniesienie wyniku procedury według numeru, przez który można ocenić jakość i poprawność procedury. Przykład tworzenia procedury bez parametrów:

Utwórz procedurę Count_books jak wybrać Count (Code_book) z książek Idź

Ćwiczenie 1.

Exec Count_booki.

Sprawdź wynik.

Przykład utworzenia procedury z parametrem wejściowym:

Utwórz procedurę Count_books_pages @Cout_pages jako int Jak wybrać Count (code_book) z książek, w których strony\u003e \u003d @count_pages Go

Zadanie 2.. Stwórz tej procedury W sekcji Przechowywane procedury Dane DB_BOOKS za pośrednictwem narzędzia SQL Server Management Studio. Uruchom go za pomocą polecenia

Exec Count_books_pages 100.

Sprawdź wynik.

Przykład utworzenia procedury z parametrami wejściowymi:

Utwórz procedurę Count_books_Title @count_pages jako int, @title jako znak (10) Jak wybrać Count (Code_book) z książek, w których strony\u003e \u003d @count_pages i tytuł_book jak @title Idź

Zadanie 3. Utwórz tę procedurę w sekcji Przechowywane procedury Dane 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 utworzenia procedury za pomocą parametrów wejściowych i parametru wyjściowego:

Utwórz procedurę Count_books_itogo @count_pages Int, @Title Char (10), @Itogo int Wyjście Jak wybrać @itogo \u003d Count (Code_book) z książek, w których strony\u003e \u003d @count_pages i tytuł_book jak @title Go

Zadanie 4. Utwórz tę procedurę w sekcji Przechowywane procedury Dane DB_BOOKS za pomocą narzędzia SQL Server Management Studio. Uruchom za pomocą zestawu poleceń:

SQL\u003e DECLARE @Q jako int Exec Count_books_itogo 100, "P%", wyjście @q Wybierz @q

Sprawdź wynik.

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

Utwórz procedurę CheckName @param int, jak (Wybierz nazwę_Author z autorów, w których kod_author \u003d @param) \u003d "Puszkin A.S." Wróć 1 inny zwrot 2

Zadanie 5. Utwórz tę procedurę w sekcji Przechowywane procedury Dane DB_BOOKS za pomocą narzędzia SQL Server Management Studio. Uruchom go za pomocą poleceń:

Oświadczenie @Return_status int Exec @Return_status \u003d CheckName 1 Wybierz "Status powrotu" \u003d @return_status

Przykład tworzenia procedury bez parametrów do zwiększenia wartości pola klucza w tabeli zakupów 2 razy:

Utwórz proc Update_proC jako aktualizowane zakupy zestaw kodu_purchase \u003d code_purchase * 2

Zadanie 6. Utwórz tę procedurę w sekcji Przechowywane procedury Dane 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 dla wszystkich informacji na temat określonych autorów:

Utwórz proc Select_author @k Char (30) Jak wybrać * od autorów, gdzie nazwa_author \u003d @k

Zadanie 7.

Exec Select_author "Puszkin A.S." lub select_author @ k \u003d "pushkin A.S." lub exec select_author @ k \u003d "pushkin A.S."

Przykład utworzenia procedury z parametrem wejściowym i wartością domyślną, aby zwiększyć wartość pola klucza w tabeli zakupów w określonej liczbie razy (domyślnie 2 razy):

Utwórz proc Update_proc @p int \u003d 2 jako aktualizowane zakupy zestaw code_purchase \u003d code_purchase * @p

Procedura nie zwraca żadnych danych.

Zadanie 8. Utwórz tę procedurę w sekcji Przechowywane procedury Dane DB_BOOKS za pomocą narzędzia SQL Server Management Studio. Uruchom go za pomocą poleceń:

EXEC Update_Proc 4 lub EXEC Update_Proc @p \u003d 4 lub EXEC Update_ProC - Używa się wartość domyślna.

Przykład utworzenia procedury z parametrami wejściowymi i wyjściowymi. Utwórz procedurę w celu określenia liczby zamówień przeprowadzonych w określonym czasie:

Utwórz proc Count_Purchases @ D1 SmallDateTime, @ D2 SmallDateTime, @C Int Wyjście Jak wybrać @ C \u003d Count (Code_Purchase) z zakupów, gdzie date_order między @ D1 a @ d2 set @c \u003d Isnull (@c, 0)

Zadanie 9. Utwórz tę procedurę w sekcji Przechowywane procedury Dane DB_BOOKS za pomocą narzędzia SQL Server Management Studio. Uruchom go za pomocą poleceń:

Oświadczenie @ C2 Int Exec Count_Purchases '01 - Jun- 2006 ', '01 - Jul- 2006', @ C2 Wyjście Wybierz @ C2

Opcje K. praca laboratoryjna №4

Generał. W narzędzia SQL Server Management Studio do tworzenia nowa strona W przypadku kodu ("Utwórz zapytanie" przycisk). Programowo wykonaj aktywną bazę danych DB_BOOKS za pomocą operatora Użyj. Utwórz procedury składowane za pomocą tworzenia operatorów procedur, a niezależnie określają nazwy procedur. Każda procedura przeprowadzi jeden zapytanie SQL, które przeprowadzono w drugiej pracy laboratoryjnej. Ponadto kod Query SQL należy zmienić, aby mogły być przesyłane do wartości pola, dla których przeprowadzana jest wyszukiwanie.

Na przykład początkowe zadanie i żądanie w pracy laboratoryjnej Numer 2:

/ * Wybierz z dostawców książek referencyjnych (dostawy tabeli) nazw, telefonów i zajazdów (Name_Company, telefon i pól), których nazwa firmy (nazwa_kompanii) "Oao World".

Wybierz nazwę_kompanią, telefon, Zajazd z dostaw, gdzie nazwa_kompaniata \u003d "OAO WORLD"

* / - Ta procedura pracy zostanie utworzona:

Utwórz proc Select_name_Company @comp Char (30) Jak wybrać Name_Company, Telefon, Zajazd z dostaw, gdzie_company \u003d @comp

- Kolejność procedury jest używana przez polecenie:

EXEC SELECT_NAME_COMPANY "OJSC WORLD"

Lista zadań

W narzędziu SQL Server Management Studio Utwórz nowy program. Programowo tworzą aktywną indywidualną bazę danych utworzoną w pracy Laboratory Numer 1, przy użyciu operatora Użyj. Utwórz procedury składowane za pomocą tworzenia operatorów procedur, a niezależnie określają nazwy procedur. Każda procedura przeprowadzi jeden zapytanie SQL przedstawione w formie oddzielnych zadań według opcji.

opcja 1

1. Wycofać listę pracowników, którzy mają przynajmniej jedno dziecko.

2. Wycofać listę dzieci, które dały prezenty w określonym okresie.

3. Wycofać listę rodziców, którzy mają dzieciom dzieci.

4. Wycofać informacje o prezentach o koszcie więcej niż określonego numeru posortowanego według daty.

Opcja 2.

1. Lista instrumentów z określony typ.

2. Usuń liczbę naprawionych urządzeń i całkowity koszt napraw w określonym kreatorze.

3. Przynieść listę właścicieli instrumentów i liczbę swoich odwołań, posortowanych według liczby odwołań schodzących.

4. Wyświetla informacje o mistrzach z wyładowaniem więcej niż określoną liczbą lub z datą odbioru mniejsza niż określona data.

Opcja 3.

2. Aby wyświetlić listę kodów sprzedaży, dla których sprzedawane kolory są więcej niż określona liczba.

3. Usuń sprzedaż, kwotę, sprzedawcę i kwiat w określonym kodzie sprzedaży.

4. Wyświetl listę kolorów i klas dla kwiatów o wysokości określonej liczby lub kwitnienia.

Opcja 4.

1. Wycofać listę leków z określonym wskazaniem do użycia.

2. Aby wyświetlić listę dostaw, które sprzedawały więcej niż określoną liczbę tego samego leku.

3. Wycofać datę dostawy, kwotę, tytuł głowy od dostawcy i nazwę leku w kodzie odbioru więcej niż określony numer.

Opcja 5.

2. Wyświetl listę odpisanych urządzeń dla wskazanego powodu.

3. Wycofać datę otrzymania, nazwę sprzętu, nazwa odpowiedzialnego i daty odpisu dla sprzętu odpisanego w określonym czasie.

4. Wyświetl listę sprzętu z określonym typem lub z datą odbioru więcej niż pewna wartość.

Opcja 6.

1. Lista dań o wadze więcej niż określona liczba.

2. Wycofać listę produktów, w tytule, którego znaleziono określony fragment słowa.

3. Wyjmij objętość produktu, nazwa naczynia, nazwa produktu z kodem naczynia z określonego początkowe znaczenie Po określonej wartości końcowej.

4. Wyświetlanie procesu gotowania i nazwa naczynia z ilością węglowodanów jest większa niż określona wartość lub ilość kalorii jest większa niż określona wartość.

Opcja 7.

1. Wycofać listę pracowników z tym stanowiskiem.

3. Wycofać datę rejestracji, rodzaj dokumentu, nazwa rejestratora i nazwa organizacji dokumentów zarejestrowanych w określonym czasie.

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

Opcja 8.

1. Wycofać listę pracowników z określonym powodem zwolnienia.

3. Wycofać datę rejestracji, powód odwołania, nazwę pracownika dokumentów zarejestrowanych w określonym czasie.

Opcja 9.

1. Wycofać listę pracowników, którzy odważa wakacje określonego typu.

2. Wyślij listę dokumentów w dniu rejestracji w określonym czasie.

3. Wycofać datę rejestracji, rodzaju wakacji, FIO pracownika do dokumentów zarejestrowanych w określonym okresie.

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

Opcja 10.

1. Wycofać listę pracowników z tym stanowiskiem.

2. Wycofać listę dokumentów, w których znaleziono określony fragment słowa.

3. Wycofać datę rejestracji, rodzaju dokumentu, FIO nadawcy i nazwę organizacji dla dokumentów zarejestrowanych w określonym czasie.

4. Wyświetl listę zarejestrowanych dokumentów z określonym typem dokumentu lub kodem dokumentu mniej niż określona wartość.

Opcja 11.

1. Wycofać listę pracowników wyznaczonych do określonej pozycji.

2. Wyślij listę dokumentów w dniu rejestracji w określonym czasie.

3. Wycofać datę rejestracji, pozycję, pełną nazwę dokumentów zarejestrowanych w określonym czasie.

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

Opcja 12.

3. Wycofać listę osób, które sprzęt przetoczący do zatrudniania i liczby ich odwołań, posortowanych według liczby odwołań schodzących.

Opcja 13.

1. Wyświetl listę sprzętu z określonym typem. 2. Wyświetl listę sprzętu, które odpisało określony pracownik.

3. Wyświetl ilość odpisanego sprzętu zgrupowanego przez sprzęt.

4. Wyświetl informacje o pracowników z datą zatrudnienia więcej niż określoną datę.

Opcja 14.

1. Wyświetl listę kwiatów z określonym typem arkusza.

2. Wyślij listę kodów dochodów, dla których sprzedawane kolory są więcej niż określona wartość.

3. Usuń datę otrzymania, kwotę, nazwiska dostawcy i kolory w określonym kodeksie dostawcy.

4. Lista kolorów i odmian dla kwiatów o wysokości większej liczby lub kwitnących.

Opcja 15.

1. Wycofać listę klientów, którzy przyszli do liczb w określonym okresie.

2. Wycofać całkowitą kwotę płatności dla każdego klienta.

3. Usuń datę przyjazdu, rodzaju pomieszczenia, nazwy klientów zarejestrowanych w określonym czasie.

4. Wyślij listę zarejestrowanych klientów w określonym typie.

Opcja 16.

1. Wyświetl listę sprzętu z określonym typem.

2. Opublikuj listę urządzeń, które podjęły określony klient do wynajęcia.

3. Wycofać listę osób, które sprzęt do wynajęcia i liczby swoich odwołań posortowanych według liczby odwołań schodzących.

4. Informacje o porowieniu posortowane według adresów.

Opcja 17.

1. Aby wyświetlić listę wartości z zamówieniami kosztować więcej niż określoną wartość lub okres gwarancji więcej niż określona liczba.

2. Aby wprowadzić listę lokalizacji wartości materiału, w tytule, którego znaleziono określone słowo.

3. Wyjmij wartość wartości wartości za pomocą kodu w określonym zakresie.

4. Wyświetl listę osób odpowiedzialnych finansowo o dacie zatrudnienia w określonym zakresie.

Opcja 18.

1. Wyświetl listę prac naprawczych wykonywanych przez określony mistrz.

2. Wycofać listę etapów pracy zawartych w pracy, w tytule, którego znaleziono określone słowo.

3. Wykorzystać kwotę kosztów kroków pracy naprawy do pracy z kodem w określonym zakresie.

4. Wyślij listę mistrzów od daty zatrudnienia w określonym zakresie.

Opcja 19.

1. Wycofać listę leków o określonej wskazaniu.

2. Wyślij listę kontroli kontroli, dla których więcej niż pewna liczba sprzedanych leków.

3. Wycofać datę sprzedaży, kwotę, pełną nazwę i lekarstwo w celu sprawdzenia z określoną liczbą.

4. Wyświetl listę leków i jednostek pomiaru dla leków z opakowaniem w opakowaniu więcej niż określona liczba lub kod leku jest mniejsza niż określona wartość.

Opcja 20.

1. Wycofać listę pracowników z tym stanowiskiem.

2. Wycofać listę dokumentów, w których znaleziono określony fragment słowa.

3. Wycofać datę rejestracji, rodzaj dokumentu, pełnej nazwy oraz fakt wykonania dokumentów zarejestrowanych w określonym czasie.

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

W MySQL 5 istnieje wiele nowych funkcji, z których jeden z najbardziej ważnych jest tworzenie procedur przechowywanych. W tej lekcji powiem ci, co reprezentują, a także jak ułatwiają ci życie.

Wprowadzenie

Procedura przechowywana jest metodą kapsułkowania cyklicznych działań. W procedurach przechowywanych można zadeklarować zmienne, zarządzać strumieniami danych, a także stosować inne techniki programowania.

Powodem ich stworzenia jest jasny i potwierdzony częstym użyciem. Z drugiej strony, jeśli rozmawiasz z tymi, którzy pracują z nimi nieregularnie, opinie są podzielone na dwa całkowicie naprzeciwko flanki. Nie zapomnij o tym.

Za

• Oddzielenie logiki z innymi aplikacjami. Procedury przechowywane Envapsule Funkcjonalność; Zapewnia to łączność z danymi i zarządzania nimi między różnymi aplikacjami.
• Izolacja użytkownika z tabel bazy danych. Pozwala to uzyskać dostęp do procedur przechowywanych, ale nie do samego danych.
• Zapewnia mechanizm bezpieczeństwa. Zgodnie z poprzednim akapitem, jeśli możesz uzyskać dostęp do danych tylko poprzez procedury przechowywane, nikt inny nie może usunąć danych za pomocą polecenia Usuń SQL.
• Poprawa wydajności w wyniku redukcji ruch sieciowy. W procedurach przechowywanych wiele żądań można łączyć.

Vs

• Zwiększone obciążenie serwera bazy danych ze względu na fakt, że większość prac jest wykonywana na części serwera i mniejsza na kliencie.
• Będzie wiele rzeczy do myślenia. Musisz się nauczyć składnia MySQL. Wyrażenia do pisania ich przechowywanych procedur.
• Powierzasz logikę swojej aplikacji w dwóch miejscach: Kod serwera i kodeksu dla procedur przechowywanych, tym samym komplikując proces manipulacji danych.
• Migracja z jednym DBMS do drugiego (DB2, SQL Server itp.) Może prowadzić do problemów.

Narzędzie, w którym działałem, nazywa się przeglądarką MySQL Query, jest dość standard do interakcji z bazami danych. Narzędzie wiersz poleceń MySQL to kolejny doskonały wybór. Mówię ci o tym z tego powodu, że faworyta wszystkich Phpmyadmin nie obsługuje wykonania procedur przechowywanych.

Nawiasem mówiąc, używam elementarnej struktury tabel, dzięki czemu łatwiej jest zrozumieć ten temat. W końcu mówię o procedurach przechowywanych i są dość skomplikowane, aby zagłębiać się w nieporęczną strukturę tabel.

Krok 1: Umieszczamy ogranicznik

Ogranicznik jest znakiem symbolu lub ciągiem, który jest używany do wskazania klienta MySQL, który zakończyłeś pismo wyrażenia SQL. Całkowita wieczność była ogranicznikiem była symbolem średnika. Jednak mogą pojawić się problemy, ponieważ mogą istnieć kilka wyrażeń w procedurze przechowywanej, z których każdy powinien kończyć się średnikiem. W tej lekcji używam ciągów "//" jako ogranicznika.

Krok 2: Jak pracować z procedurami przechowywanymi

Tworzenie procedury składowanej

SELIMITER // Utwórz procedurę `P2` () Język SQL Deterministyczne SQL Security Deterministyczne SQL Security Definer Comment" Procedura "Rozpocznij wybór" Hello World! "; KONIEC //

Pierwsza część kodu tworzy procedurę składowaną. Następnie - zawiera opcjonalne parametry. Wtedy jest imię i wreszcie, treść samej procedury.

Nazwy składowanych procedur są wrażliwe na rejestr. Nie możesz również utworzyć kilku procedur o tej samej nazwie. Wewnątrz procedury zapisanej nie może być wyrażeniami, które zmieniają samą bazę danych.

4 Charakterystyka procedury przechowywanej:

• Język: Aby przeprowadzić przenośność, określa się domyślny SQL.
• Deterministyczny: Jeśli procedura zwraca ten sam wynik cały czas i przyjmuje te same parametry przychodzące. To jest proces replikacji i rejestracji. Wartość domyślna nie jest określona.
• SQL Security: Podczas rozmowy prawa użytkownika są sprawdzane. Invoker jest użytkownikiem powodującym procedurę zapisaną. Definicja jest procedura "Creator". Wartość domyślna - definiująca.
• Komentarz: W celu dokumentacji, wartość domyślna jest ""

Procedura przechowywania połączeń.

Aby wywołać procedurę zapisaną, należy wydrukować słowo kluczowe połączenie, a następnie nazwę procedury i określić parametry (zmienne lub wartości) w nawiasach. Wymagane są wsporniki.

Zadzwoń do przechowywania_procedure_name (param1, param2, ....) Procedura połączenia1 (10, "parametr ciągów", @Parameter_var);

Zmień procedurę zapisaną

MySQL ma komunikację procedury zmienić procedury, ale nadaje się do zmiany tylko niektórych cech. Jeśli chcesz zmienić parametry lub treść procedury, powinieneś usunąć i utrzymać go ponownie.

Usuwanie procedury przechowywanej

Procedura kropli, jeśli istnieje P2;

Jest to prosty zespół. Jeśli istnieje ekspresja występuje błąd, jeśli taka procedura nie istnieje.

Krok 3: Parametry

Zobaczmy, jak parametry mogą być przesyłane do procedury zapisanej.

• Utwórz procedurę ProC1 (): Pusta lista parametrów
• Utwórz procedurę ProC1 (w Varname Data-Type): jeden parametr przychodzący. Słowo w jest opcjonalne, ponieważ domyślne parametry są w (przychodzące).
• Utwórz procedurę ProC1 (Typ danych VarName Data): Jeden zwrócony parametr.
• Utwórz procedurę PROC1 (Inout Varname Data-Type): jeden parametr w tym samym czasie przychodzący i zwracany.

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

Przykład parametru w

DELIMITER // Utwórz procedurę `proc_in` (w VAR1 IN) Rozpocznij w wyniku czego wybrać VAR1 + 2; KONIEC //

Przykład parametru

SELIMITER // Utwórz procedurę `proc_out` (out var1 varchar (100)) Rozpocznij ustawiony VAR1 \u003d" Jest to test "; KONIEC //

Przykład parametru Inout

SELIMITER // Utwórz procedurę `proc_inout` (out VAR1 INT) Rozpocznij Ustaw VAR1 \u003d VAR1 * 2; KONIEC //

Krok 4: Zmienne

Teraz nauczę cię, abyś tworzył zmienne i utrzymać je w procedurach. Musisz je wyraźnie zadeklarować na początku bloku Begin / End wraz z ich typami danych. Po zadeklarowaniu zmiennej możesz użyć go w tym samym miejscu, w którym zmienne sesji, literały lub nazwy głośników.

Składnia deklaracji zmiennej wygląda tak:

Deklaruj domyślną domyślną domyślną domyślną domyślną;

Oznakujmy kilka zmiennych:

Deklaruj domyślne 5; Deklaruj STAR VARCHAR (50); Deklaruj dziś domyślny tyłek Deklaruj V1, V2, V3 Tinyint;

Praca z zmiennymi

Po zadeklarowaniu zmiennej możesz określić wartość za pomocą ustawień lub wybierz polecenia:

SELIMITER // Utwórz procedurę `var_proc` (w Parapstr Varchar (20)) Rozpocznij deklaruj A, B Inny domyślne 5; Deklaruj STAR VARCHAR (50); Zadeklaruj dziś domyślny tyłek timestamp current_date; Deklaruj V1, V2, V3 Tinyint; Wstaw na wartości Tabela1 (A); Ustaw str \u003d "Jestem ciąg"; Wybierz Concat (str, Paramstr), dziś z Tabela2, gdzie b\u003e \u003d 5; KONIEC //

Krok 5: Struktury zarządzania przepływem

MySQL obsługuje, jeśli, sprawa, Itera, opuść pętlę, podczas gdy i powtórz do zarządzania strumieniem w ramach procedury przechowywanej. Patrzymy, jak używać, jeśli, sprawa i podczas gdy najczęściej są używane.

Jeśli projekt

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

SELIMIRITER // Utwórz procedurę `proc_if` (w param1 int) Rozpocznij zmienną1 int; Ustaw zmienną1 \u003d param1 + 1; Jeśli zmienna1 \u003d 0 theTen Wybierz zmienną1; Koniec, jeśli; Jeśli param1 \u003d 0 theTen wybierz "Wartość parametru \u003d 0"; Else wybierz "Wartość parametru<> 0 "; koniec, jeśli; koniec //

Projekt sprawy

Przypadek jest kolejną metodą sprawdzenia warunków i wyboru odpowiedniego rozwiązania. Jest to świetny sposób na zastąpienie wielu projektów. Konstrukcja może być opisana na dwa sposoby, zapewniając elastyczność w kontrolowaniu wielu wyrażeń warunkowych.

SELIMITER // Utwórz procedurę `Proc_case` (w param1 Int) Rozpocznij zmienną1 int; Ustaw zmienną1 \u003d param1 + 1; Zmienna sprawa1, gdy 0 następnie wstawić w wartości Tabela1 (param1); Gdy 1 następnie wstaw w wartości Tabela1 (zmienna1); Inaczej wstawić wartości Tabela1 (99); End Case; KONIEC //

SELIMITER // Utwórz procedurę `Proc_case` (w param1 Int) Rozpocznij zmienną1 int; Ustaw zmienną1 \u003d param1 + 1; Przypadek, gdy zmienna1 \u003d 0, a następnie wstawić w wartości Tabela1 (param1); Gdy zmienna1 \u003d 1 następnie wstaw do wartości tabeli1 (zmienna1); Inaczej wstawić wartości Tabela1 (99); End Case; KONIEC //

Budowa natomiast

Technicznie istnieją trzy typy cykli: podczas cyklu, cyklu pętli i powtórz cykl. Możesz także organizować cykl za pomocą techniki programowania "Darth Vader": Goto Wyrażenia. Oto przykład cyklu:

SELIMIRITER // Utwórz procedurę "proc_Phile" (w param1 Int) Rozpocznij zmienne1, zmienna2 int; Ustaw zmienną1 \u003d 0; Podczas zmiennej1.< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Krok 6: Kursory

Kurozy służą do przekazywania żądania zwróconego wiersza, a także przetwarzanie każdego wiersza.

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

Oświadczenie kursora Nazwa kursora do wyboru ...; / * Kursor reklama i napełnianie go * / deklaruj Kontynuuj przewóz nie znaleziono / * Co robić, gdy nie ma więcej wpisów * / Otwórz nazwę kursora; / * Otwórz nazwę kursora Cursor * / Fetch w zmiennej [, zmiennej]; / * Przypisz wartość zmienną równą bieżącej wartości kolumnowej * / zamknij nazwę kursora; / * Zamknij kursor * /

W tym przykładzie spędzimy proste operacje za pomocą kursora:

SELIMITER // Utwórz procedurę `proc_cursor` (out param1 int) Rozpocznij deklaruj A, B, C Int; Deklaruj kursor CUR1 do wyboru Col1 z Tabela1; Oświadczenie Kontynuuj obsługi dla nie znalezionego zestawu B \u003d 1; Otwarty CUR1; Ustaw b \u003d 0; Ustaw C \u003d 0; Podczas gdy b \u003d 0 pobieraj CUR1 w A; Jeśli b \u003d 0 Następnie ustaw C \u003d C + A; Koniec, jeśli; Koniec; Zamknij Cur1; Ustaw param1 \u003d C; KONIEC //

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

• Nie wrażliwy: Dawno, dawno temu kursor nie wyświetli zmian w tabeli, która wydarzyła się później. W rzeczywistości MySQL nie gwarantuje, co zostanie zaktualizowany kursor, więc nie noszę go.
• Tylko do odczytu: Kursory nie można zmienić.
• Bez przewijania: Kursor jest w stanie przejść tylko w jednym kierunku - naprzód, nie będziesz w stanie pominąć strun bez wybierania ich.

Wniosek

W tej lekcji wprowadziłem cię do podstaw współpracy z procedurami przechowywanymi oraz z niektórymi odpowiednimi właściwościami. Oczywiście będziesz musiał pogłębić wiedzę na obszarach takich jak bezpieczeństwo, wyrażenia SQL i optymalizacja przed staniem się rzeczywistym procedurą MySQL Gurus.

Musisz obliczyć, jakie korzyści zapewni Ci korzystanie z procedur przechowywanych w konkretnej aplikacji, a następnie tworzyć tylko niezbędne procedury. Ogólnie rzecz biorąc, korzystam z procedur; Moim zdaniem powinny być wdrażane w projektach w wyniku ich bezpieczeństwa, służby kodu i ogólnego projektu. Ponadto nie zapominaj, że procedury MySQL wciąż działają. Oczekuj ulepszenia dotyczące funkcjonalności i ulepszeń. Nie wahaj się udostępnić opinie.