Procedury składowane w SQL | Przegląd

W tym blogu omówimy procedury składowane w języku SQL, których można użyć do zapisania zestawu kodu i używania go wielokrotnie, kiedy tylko jest to potrzebne. Procedury składowane są podobne do widoków. Można jednak przeprowadzać typowe operacje na tabelach , takie jak DROP, TRUNCATE, DELETE itp. z procedurami składowanymi, których nie można wykonywać z widokami.

Procedury składowane są również wstępnie kompilowane, dzięki czemu działają szybciej niż widoki. Minimalizuje również ilość danych przesyłanych do serwera bazy danych.

Spis treści

• Składnia procedur składowanych w języku SQL: bez parametrów
• Składnia procedur składowanych w języku SQL: z parametrami
• Przykładowy scenariusz procedur przechowywanych w SSMS
• Drugi przykładowy scenariusz
• Trzeci przykładowy scenariusz
• Lokalizacja zapisanych procedur składowanych w SQL
• Tworzenie procedury składowanej w języku SQL z parametrami
• Tworzenie procedury składowanej z wartością domyślną
• Wniosek

Składnia procedur składowanych w języku SQL: bez parametrów

Aby utworzyć procedurę składowaną bez parametrów, należy rozpocząć od funkcji CREATE . Następnie dodaj funkcję PROC , wpisz nazwę procedury zaraz po niej i dodaj funkcję AS .

Tworzenie procedury składowanej w jest podobne do tworzenia tabel i widoków. Różnica polega jednak na procesie pozyskiwania danych.

Na przykład, jeśli chcemy uzyskać dane z widoku, używamy „ SELECT * FROM nazwa_widoku ”. Z drugiej strony w przypadku procedur składowanych używamy EXEC, co oznacza „wykonać”, a następnie wpisujemy nazwę procedury składowanej po.

Gdy wykonamy procedurę składowaną , wyświetli ona również informacje na podstawie dodanych instrukcji lub poleceń.

Składnia procedur składowanych w języku SQL: z parametrami

Przejdźmy do . Proces jest podobny do poprzedniego przykładu, który zademonstrowałem. Jedyna różnica polega na tym, że musisz dołączyć parametry przed funkcją AS , a także typ danych zaraz po niej.

Możesz także użyć parametru, który zadeklarowałeś podczas tworzenia procedury składowanej w swoich poleceniach. Ma to na celu zaoszczędzenie czasu na wprowadzanie wielu wartości. Na przykład zamiast ręcznie wpisywać customerID1 , customerID2 i tak dalej, użyliśmy @custid lub parametru, aby uniknąć długiego zestawu kodu.

Pamiętaj, że podczas używania lub dołączania parametru ważne jest, aby używać symbolu @ . Możesz także podać dowolną liczbę parametrów, a nie tylko jeden. W ten sposób wykonujemy procedury składowane z parametrami.

Jak możesz zauważyć, podczas wykonywania procedury składowanej użyliśmy również parametru o wartości równej 1 . Spowoduje to wyświetlenie tylko informacji o custid1 .

Przykładowy scenariusz procedur przechowywanych w SSMS

Miejmy więcej przykładów procedur składowanych w SQL. W pierwszej kolejności utworzę przykładową procedurę składowaną za pomocą podświetlonego polecenia poniżej.

W przypadku konwencji nazewnictwa podczas tworzenia procedury składowanej zwykle używamy „usp” lub „sp”, aby wskazać, że jest to procedura składowana. Jeśli zastanawiasz się, co oznacza „usp”, oznacza to po prostu procedurę składowaną zdefiniowaną przez użytkownika. 

Następnie napiszemy zapytania do procedury składowanej, które będą przetwarzane po jej wykonaniu.

Zapytania z poprzedniego przykładu po prostu usuną tabelę o nazwie dbo.stageOrders . Następnie odtworzy dbo.stageOrders z danymi z tabeli Sales.SalesOrderHeader . 

Utwórzmy tę procedurę składowaną , podświetlając następujący kod i klikając przycisk Wykonaj .

Następnie powinieneś zobaczyć taki komunikat.

Następnie wykonamy teraz procedurę składowaną, uruchamiając EXEC usp_TEST .

Po wykonaniu procedury składowanej usp_TEST dbo.stageOrders powinien teraz mieć dane z Sales.SalesOrderHeader . Sprawdźmy zawartość pliku dbo.stageOrders , uruchamiając poniższe polecenie.

W rezultacie są to dane, które utworzyliśmy w dbo.stageOrders na podstawie poleceń, które dodaliśmy do naszej procedury składowanej.

Drugi przykładowy scenariusz

Przejdźmy do innego przykładu. Tym razem nie chcę usuwać tabeli dbo.stageOrders i odtwarzać tej samej tabeli z danymi pochodzącymi z tabeli Sales.SalesOrderHeader .

Zamiast tego chcę, aby dane z tabeli Sales.SalesOrderHeader były wyświetlane za każdym razem, gdy wykonuję procedurę składowaną „ usp_TEST ” .

Aby to wykonać, muszę wprowadzić zmiany w ostatnio utworzonej procedurze składowanej za pomocą instrukcji ALTER .

W tym przykładzie wykorzystaliśmy instrukcję ALTER , która służy do modyfikowania naszej procedury składowanej. Można to również zastosować do widoków i tabel. Gdy uruchomimy kod na powyższym zrzucie ekranu, powinien on zmienić zapytania, które mają być przetwarzane przez naszą procedurę składowaną.

Tym razem nie odtworzy żadnej tabeli, tak jak zrobiliśmy to wcześniej. Jeśli wykonamy „ usp_TEST ”, po prostu przeniesie dane z tabeli Sales.SalesOrderHeader .

Trzeci przykładowy scenariusz

Zróbmy inny przykład. Tym razem ponownie wykorzystamy tabelę dbo.stageOrders, którą mieliśmy w naszym pierwszym przykładzie tworzenia procedury składowanej w SSMS . Zaczniemy od tego przykładu, wybierając tabelę dbo.stageOrders. 

Wyniki po wybraniu wszystkich danych z dbo.stageOrders powinny wyglądać tak.

Teraz opróżnijmy tabelę dbo.stageOrders, wykonując usp_TEST . Możemy to zrobić, dodając następujące polecenie.

Po dodaniu polecenia TRUNCATE TABLE musimy zaktualizować usp_TEST .

Następnie powinien zostać wyświetlony komunikat informujący, że polecenia zostały zakończone pomyślnie . Następnie ponownie wykonamy usp_TEST .

Po wykonaniu usp_TEST wyświetli wszystkie rekordy z Sales.SalesOrderHeader , a także opróżni tabelę dbo.stageOrders . 

Aby sprawdzić, czy tabela dbo.stageOrders jest pusta, musimy ją zaznaczyć za pomocą następującego polecenia i wykonać.

Po uruchomieniu powyższego kodu widzimy, że tabela dbo.stageOrders jest teraz pusta. Wynika to z polecenia TRUNCATE TABLE , którego użyliśmy do aktualizacji usp_TEST .

Lokalizacja zapisanych procedur składowanych w SQL

Jeśli chcesz zobaczyć, gdzie zapisywana jest procedura składowana w SQL , przejdź do panelu Eksplorator obiektów po lewej stronie i kliknij ikonę „ + ” przed bazą danych, nad którą pracujesz.

Następnie kliknij prawym przyciskiem myszy Programowalność i wybierz Odśwież .

Rozwiń folder lub grupę Programmability , klikając ikonę „ + ”. Następnie rozwiń grupę Procedury składowane, wykonując ten sam krok. Wewnątrz grupy Procedury składowane powinieneś zobaczyć plik dbo.usp_TEST .

Jeśli chcesz sprawdzić, jakie polecenia lub zapytania wykonuje określona procedura składowana w SQL, możesz kliknąć procedurę składowaną prawym przyciskiem myszy i wykonać czynności przedstawione na zrzucie ekranu poniżej.

Następnie otworzy procedurę składowaną w innej karcie, na której można zobaczyć zawarte w niej polecenia. Tak wygląda dbo.usp_TEST po otwarciu.

Jak widać, istnieje kilka domyślnych poleceń przed instrukcją CREATE . Możesz to po prostu usunąć, jeśli chcesz. 

Teraz wiesz, jak sprawdzić, jakie polecenia wykonuje procedura składowana w SQL .

Tworzenie procedury składowanej w języku SQL z parametrami

Następnie utworzymy procedurę składowaną z parametrami. Na przykład użyjemy następującego kodu do utworzenia nowej procedury składowanej.

W przykładowym kodzie użyłem tego samego procesu tworzenia procedury składowanej o nazwie usp_GetCustomer . Następnie dodałem parametr, którym jest @CustomerID z typem danych wejściowych INT .

Zwróć uwagę, że po dodaniu parametru podczas tworzenia procedury składowanej należy zawsze podawać parametr za każdym razem, gdy zamierzasz wykonać polecenie.

Zobaczmy, co się stanie, jeśli wykonamy usp_GetCustomer bez podania parametru.

Po wykonaniu usp_GetCustomer bez parametru pojawił się komunikat o błędzie. Tak by to wyglądało, gdybyśmy wykonali usp_GetCustomer z parametrem.

Dzięki podanemu parametrowi jesteśmy w stanie uzyskać odpowiedni wynik w wykonaniu naszej procedury składowanej .

Tworzenie procedury składowanej z wartością domyślną

Jeśli chcesz uniknąć otrzymywania komunikatu o błędzie podczas wykonywania procedury składowanej z parametrem, możesz ustawić wartość domyślną, która będzie służyć jako parametr domyślny.

Na przykład utworzymy procedurę składowaną o nazwie usp_GetOrdersByYear .

Następnie dodam parametr @OrderYear z typem wejścia „ INT ” i wartością domyślną równą 2011 .

Jeśli wykonamy usp_GetOrdersByYear bez podanego parametru, wyświetli rekordy z rokiem 2011 .

Z drugiej strony, jeśli wykonamy usp_GetOrdersByYear z podanym parametrem 2014 , to powinien wyświetlić rekordy z rokiem 2014 .

W ten sposób wykorzystujesz procedury składowane w codziennych zadaniach związanych z zarządzaniem danymi.

Klauzula HAVING w funkcjach agregujących SQL
ISNULL Funkcja SQL w instrukcjach Case
Wyodrębnianie danych SQL za pomocą OFFSET i FETCH

Wniosek

Biorąc to wszystko pod uwagę, dowiedziałeś się, czym jest procedura składowana w SQL i jakie jest jej przeznaczenie. Omówiliśmy również instrukcję ALTER , która służy do wprowadzania zmian lub aktualizacji w obecnej procedurze składowanej.

Ponadto dowiedziałeś się, że istnieją różne metody tworzenia procedur składowanych w języku SQL i nauczyłeś się, jak uniknąć otrzymywania błędów podczas wykonywania procedury składowanej, podając wartość domyślną.

Co najważniejsze, nauczyłeś się wykorzystywać procedury składowane do przechowywania zestawów poleceń, aby uniknąć wielokrotnego uruchamiania długich zestawów kodu. Na koniec pamiętaj, aby podać parametr, używając symbolu „ @ ”.

Wszystkiego najlepszego,

Hafiz