Co to jest procedura składowana sql. Dane źródłowe dla przykładów. Rozwiązania Zbieranie z Internetu informacji „Jaka jest różnica między procedurą składowaną a widokiem?”

Procedura składowana - obiekt bazy danych, będący zestawem instrukcji SQL, który jest jednorazowo kompilowany i przechowywany na serwerze. Procedury składowane są bardzo podobne do zwykłych procedur języka wysokiego poziomu, mogą posiadać parametry wejściowe i wyjściowe oraz zmienne lokalne, mogą wykonywać obliczenia numeryczne i operacje na danych znakowych, których wyniki można przypisać do zmiennych i parametrów. Procedury składowane mogą wykonywać standardowe operacje na bazach danych (zarówno DDL, jak i DML). Ponadto procedury składowane dopuszczają pętle i rozgałęzienia, to znaczy mogą używać instrukcji do kontrolowania procesu wykonywania.

Procedury składowane są podobne do funkcji zdefiniowanych przez użytkownika (UDF). Główna różnica polega na tym, że funkcji zdefiniowanych przez użytkownika można używać jak każdego innego wyrażenia w instrukcji SQL, podczas gdy procedury składowane muszą być wywoływane za pomocą funkcji CALL:

Procedura CALL(…)

WYKONAJ procedurę(…)

Procedury składowane mogą zwracać wiele wyników, czyli wyników zapytania SELECT. Takie zestawy wyników można przetwarzać przy użyciu kursorów, innych procedur składowanych, które zwracają wskaźnik zestawu wyników, lub aplikacji. Procedury składowane mogą również zawierać zadeklarowane zmienne do przetwarzania danych i kursory, które umożliwiają zapętlanie wielu wierszy w tabeli. Standard SQL udostępnia funkcje IF, LOOP, REPEAT, CASE i wiele innych. Procedury składowane mogą akceptować zmienne, zwracać wyniki lub modyfikować zmienne i zwracać je, w zależności od tego, gdzie zmienna jest zadeklarowana.

Implementacja procedur składowanych różni się w zależności od systemu DBMS. Większość głównych dostawców baz danych obsługuje je w takiej czy innej formie. W zależności od systemu DBMS procedury składowane mogą być implementowane w różnych językach programowania, takich jak SQL, Java, C lub C++. Procedury składowane, które nie są napisane w języku SQL, mogą, ale nie muszą, samodzielnie wykonywać zapytania SQL.

Za

    Dzielenie się logiką z innymi aplikacjami. Procedury składowane zawierają funkcjonalność; zapewnia to łączność umożliwiającą dostęp do danych i zarządzanie nimi w różnych aplikacjach.

    Izolowanie użytkowników od tabel bazy danych. Dzięki temu możesz zapewnić dostęp do procedur składowanych, ale nie do samych danych tabeli.

    Zapewnia mechanizm ochronny. Zgodnie z poprzednim punktem, jeśli dostęp do danych można uzyskać wyłącznie za pomocą procedur składowanych, nikt inny nie może usunąć danych za pomocą polecenia SQL DELETE.

    Poprawiona realizacja w wyniku zmniejszonego ruchu sieciowego. Korzystając z procedur składowanych, można łączyć wiele zapytań.

Przeciwko

    Zwiększone obciążenie serwera bazy danych ze względu na fakt, że większość pracy wykonywana jest po stronie serwera, a mniej po stronie klienta.

    Będziesz musiał się dużo nauczyć. Aby napisać procedury składowane, będziesz musiał nauczyć się składni wyrażeń MySQL.

    Duplikujesz logikę aplikacji w dwóch miejscach: kod serwera i kod procedur składowanych, co komplikuje proces manipulacji danymi.

    Migracja z jednego systemu DBMS do innego (DB2, Serwer SQL itp.) może prowadzić do problemów.

Cel i zalety procedur składowanych

Procedury składowane poprawiają wydajność, zwiększają możliwości programowania i obsługują funkcje bezpieczeństwa danych.

Zamiast przechowywać często używane zapytania, klienci mogą odwoływać się do odpowiedniej procedury składowanej. Po wywołaniu procedury składowanej jej zawartość jest natychmiast przetwarzana przez serwer.

Oprócz faktycznego wykonywania zapytania, procedury składowane umożliwiają także wykonywanie obliczeń i manipulowanie danymi - zmienianie, usuwanie, wykonywanie instrukcji DDL (nie we wszystkich systemach DBMS!) i wywoływanie innych procedur przechowywanych oraz wykonywanie złożonej logiki transakcyjnej. Pojedyncza instrukcja pozwala wywołać złożony skrypt zawarty w procedurze składowanej, unikając wysyłania setek poleceń przez sieć, a w szczególności konieczności przesyłania dużej ilości danych od klienta do serwera.

W większości systemów DBMS procedura składowana jest uruchamiana po raz pierwszy, a następnie jest ona kompilowana (parsowana i generowany plan dostępu do danych). W przyszłości jego przetwarzanie będzie szybsze. Oracle DBMS interpretuje zapisany kod proceduralny przechowywany w słowniku danych. Począwszy od wersji Oracle 10g obsługiwana jest tzw. natywna kompilacja zapisanego kodu proceduralnego w języku C, a następnie do kodu maszynowego maszyny docelowej, po czym po wywołaniu procedury składowanej bezpośrednio wykonywany jest jej skompilowany kod obiektowy.

Możliwości programowania

Utworzoną procedurę składowaną można wywołać w dowolnym momencie, co zapewnia modułowość i zachęca do ponownego użycia kodu. To drugie ułatwia utrzymanie bazy danych, ponieważ jest ona odizolowana od zmieniających się reguł biznesowych. W dowolnym momencie możesz zmodyfikować procedurę składowaną zgodnie z nowymi regułami. Po tym wszystkie aplikacje korzystające z niego automatycznie dostosują się do nowych zasad biznesowych bez bezpośrednich modyfikacji.

Bezpieczeństwo

Stosowanie procedur składowanych pozwala ograniczyć lub całkowicie wyeliminować bezpośredni dostęp użytkowników do tabel bazy danych, pozostawiając użytkownikom jedynie uprawnienia do wykonywania procedur składowanych, które zapewniają pośredni i ściśle regulowany dostęp do danych. Ponadto niektóre systemy DBMS obsługują szyfrowanie tekstu (zawijanie) procedury składowanej.

Te funkcje bezpieczeństwa umożliwiają odizolowanie struktury bazy danych od użytkownika, zapewniając integralność i niezawodność bazy danych.

Prawdopodobieństwo działań takich jak wstrzyknięcie SQL jest zmniejszone, ponieważ dobrze napisane procedury składowane dodatkowo sprawdzają parametry wejściowe przed przekazaniem zapytania do systemu DBMS.

Implementowanie procedur składowanych

Procedury składowane są zwykle tworzone przy użyciu języka SQL lub jego specyficznej implementacji w wybranym systemie DBMS. Przykładowo do tych celów w Microsoft SQL Server DBMS istnieje język Transact-SQL, w Oracle - PL/SQL, w InterBase i Firebird - PSQL, w PostgreSQL - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, w IBM DB2 - SQL/PL (angielski), w Informix - SPL. MySQL dość ściśle podąża za standardem SQL:2003, jego język jest podobny do SQL/PL.

Niektóre systemy DBMS pozwalają na użycie procedur składowanych napisanych w dowolnym języku programowania, które mogą tworzyć niezależne pliki wykonywalne, na przykład C++ lub Delphi. W terminologii Microsoft SQL Server takie procedury nazywane są rozszerzonymi procedurami przechowywanymi i są po prostu funkcjami zawartymi w bibliotece DLL Win32. I na przykład w Interbase i Firebird funkcje wywoływane z DLL/SO mają inną nazwę - UDF (Funkcja zdefiniowana przez użytkownika). W MS SQL 2005 wprowadzono możliwość pisania procedur składowanych w dowolnym języku .NET, a w przyszłości planuje się rezygnację z rozszerzonych procedur składowanych. Z kolei Oracle DBMS umożliwia pisanie procedur składowanych w Język Java. W IBM DB2 pisanie procedur składowanych i funkcji w konwencjonalnych językach programowania jest sposobem tradycyjnym, wspieranym od samego początku, a rozszerzenie proceduralne SQL zostało dodane do tego DBMS dopiero w dość późnych wersjach, po włączeniu go do standardu ANSI. Informix obsługuje także procedury w językach Java i C.

W Oracle DBMS procedury składowane można łączyć w tak zwane pakiety. Pakiet składa się z dwóch części – specyfikacji pakietu, która określa definicję procedury składowanej, oraz treści pakietu, która zawiera jej implementację. Tym samym Oracle umożliwia oddzielenie interfejsu kodu programu od jego implementacji.

W systemie IBM DB2 DBMS procedury składowane można łączyć w moduły.

Składnia

UTWÓRZ PROCEDUrę `p2`()

DEFINITOR ZABEZPIECZEŃ SQL

KOMENTARZ „Procedura”

WYBIERZ „Witaj świecie!”;

Pierwsza część kodu tworzy procedurę składowaną. Kolejny zawiera parametry opcjonalne. Następnie pojawia się nazwa i wreszcie treść samej procedury.

4 cechy procedury składowanej:

Język: Ze względu na przenośność domyślnym językiem jest SQL.

Deterministyczny: jeśli procedura zawsze zwraca ten sam wynik i przyjmuje te same parametry wejściowe. Dotyczy to procesu replikacji i rejestracji. Wartość domyślna NIE JEST DETERMINISTYCZNA.

Bezpieczeństwo SQL: uprawnienia użytkownika są sprawdzane podczas połączenia. INVOKER to użytkownik wywołujący procedurę składowaną. DEFINER jest „twórcą” procedury. Wartość domyślna to DEFINER.

Komentarz: Dla celów dokumentacyjnych wartością domyślną jest „”

Wywoływanie procedury składowanej

WYWOŁAJ nazwę_procedury_zapisanej (param1, param2, ....)

CALL procedura1(10, "parametr stringowy", @parametr_zmienna);

Modyfikowanie procedury składowanej

MySQL ma instrukcję ALTER PROCEDURE do zmiany procedur, ale nadaje się ona tylko do zmiany niektórych cech. Jeśli chcesz zmienić parametry lub treść procedury, powinieneś ją usunąć i utworzyć na nowo.

Usuwanieprzechowywaneprocedury

PROCEDURA UPUSZCZANIA, JEŚLI ISTNIEJE p2;

To jest proste polecenie. Instrukcja IF EXISTS wychwytuje błąd, jeśli taka procedura nie istnieje.

Opcje

UTWÓRZ PROCEDUrę proc1(): pusta lista parametrów

CREATE PROCEDURE proc1 (IN nazwa_zmiennej DATA-TYPE): jeden parametr wejściowy. Słowo IN jest opcjonalne, ponieważ domyślnymi parametrami są IN (in).

CREATE PROCEDURE proc1 (zmienna OUT DATA-TYPE): zwrócony został jeden parametr.

CREATE PROCEDURE proc1 (INOUT nazwa zmiennej DATA-TYPE): jeden parametr, zarówno wejściowy, jak i zwracany.

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

DECLARE nazwa_zmiennej TYP DANYCH DOMYŚLNA wartość domyślna;

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

Rodzaje procedur składowanych

SQL Server ma kilka typów procedur przechowywanych.

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

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

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

Wyzwalacze

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

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

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

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

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

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

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

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

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

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

    wsparcie replikacji.

Poniżej przedstawiono podstawowy format polecenia CREATE TRIGGER:

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

UTWÓRZ WYZWALACZ nazwa_wyzwalacza

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

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

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

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

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

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

Niepoprawnie napisane wyzwalacze mogą prowadzić do poważnych problemów, takich jak zakleszczenia. Wyzwalacze mogą blokować wiele zasobów na długi czas, dlatego należy zwrócić na to uwagę Specjalna uwaga aby zminimalizować konflikty dostępu.

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

1. Uwzględnij linię w swoich procedurach - SET NOCOUNT ON: Przy każdym wyrażeniu DML serwer SQL ostrożnie zwraca nam komunikat zawierający liczbę przetworzonych rekordów. Ta informacja Może nam się to przydać podczas debugowania kodu, ale potem będzie już zupełnie bezużyteczne. Wpisując SET NOCOUNT ON wyłączamy tę funkcję. W przypadku procedur składowanych zawierających wiele wyrażeń i/lub pętli ta akcja może dać znaczny wzrost wydajności, ponieważ wielkość ruchu zostanie znacznie zmniejszona.

UTWÓRZ PROC dbo.NazwaProc
JAK
USTAW NOCOUNT NA;
--Oto kod procedury
WYBIERZ kolumnę 1 Z dbo.TblTable1
--Przełącz SET NOCOUNT do stanu początkowego
WYŁĄCZ NOCOUNT;
IŚĆ

2. Użyj nazwy schematu z nazwą obiektu: Cóż, myślę, że to jasne. Ta operacja informuje serwer, gdzie ma szukać obiektów i zamiast losowo szperać w swoich pojemnikach, natychmiast będzie wiedział, dokąd musi się udać i co zabrać. Dzięki dużej liczbie baz danych, tabel i procedur składowanych może znacząco zaoszczędzić nasz czas i nerwy.

WYBIERZ * Z dbo.MyTable — dobrze jest to zrobić
-- Zamiast
WYBIERZ * Z MyTable - A robienie tego jest złe
--Wywołanie procedury
EXEC dbo.MyProc -Znowu dobrze
--Zamiast
EXEC MyProc — źle!

3. Nie używaj przedrostka „sp_” w nazwie swoich procedur składowanych: Jeśli nazwa naszej procedury zaczyna się od „sp_”, SQL Server najpierw przeszuka swoją główną bazę danych. Faktem jest, że ten prefiks jest używany w osobistych, wewnętrznych procedurach przechowywanych serwera. Dlatego jego użycie może wiązać się z dodatkowymi kosztami, a nawet błędnymi wynikami, jeśli w jego bazie znajdzie się procedura o tej samej nazwie co Twoja.

4. Użyj JEŚLI ISTNIEJE (WYBIERZ 1) zamiast JEŚLI ISTNIEJE (WYBIERZ *): Aby sprawdzić istnienie rekordu w innej tabeli, używamy instrukcji IF EXISTS. To wyrażenie zwraca wartość true, jeśli z wyrażenia wewnętrznego zwrócona jest przynajmniej jedna wartość, nie ma znaczenia „1”, wszystkie kolumny czy tabela. Zwracane dane w zasadzie nie są w żaden sposób wykorzystywane. Dlatego, aby skompresować ruch podczas transmisji danych, bardziej logiczne jest użycie „1”, jak pokazano poniżej:

JEŚLI ISTNIEJE (WYBIERZ 1 Z sysobjects
GDZIE nazwa = „MojaTabela” ORAZ typ = „U”)

5. Użyj TRY-Catch, aby wyłapać błędy: Przed serwerami 2005 po każdym żądaniu zapisywano w procedurze ogromną liczbę kontroli błędów. Więcej kodu zawsze zużywa więcej zasobów i więcej czasu. W przypadku SQL Server 2005 bardziej poprawny i wygodnym sposobem rozwiązania tego problemu:

ZACZNIJ SPRÓBOWAĆ
--kod
ZAKOŃCZ PRÓBĘ
ROZPOCZNIJ ŁADOWANIE
--błąd podczas przechwytywania kodu
KOŃCÓWKA

Wniosek
W zasadzie tyle mam na dzisiaj. Powtarzam jeszcze raz, że to tylko te techniki, które osobiście stosowałem w swojej praktyce i mogę ręczyć za ich skuteczność.

P.S.
Mój pierwszy post, nie oceniajcie zbyt surowo.

Procedury składowane SQL to wykonywalne moduły programu, które można przechowywać w postaci różnych obiektów. Inaczej mówiąc, jest to obiekt zawierający instrukcje SQL. Te procedury składowane mogą być wykonywane na kliencie programy aplikacyjne aby uzyskać dobrą wydajność. Ponadto takie obiekty są często wywoływane z innych skryptów lub nawet z innej sekcji.

Wstęp

Wiele osób uważa, że ​​są one podobne do różnych procedur (odpowiednio z wyjątkiem MS SQL). Być może to prawda. Mają podobne parametry i mogą dawać podobne wartości. Co więcej, w niektórych przypadkach się dotykają. Są one na przykład łączone z bazami danych DDL i DML oraz funkcjami użytkownika (o nazwie kodowej UDF).

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

Gdy wywoływana jest procedura, jest ona natychmiast przetwarzana przez sam serwer, bez zbędnych procesów i interwencji użytkownika. Następnie możesz przeprowadzić dowolne usuwanie, wykonanie lub modyfikację. Za to wszystko odpowiada operator DDL, który samodzielnie wykonuje najbardziej złożone akcje przetwarzania obiektów. Co więcej, wszystko to dzieje się bardzo szybko, a serwer tak naprawdę nie jest ładowany. Taka szybkość i wydajność pozwala na bardzo szybkie przesłanie dużej ilości informacji od użytkownika do serwera i odwrotnie.

Aby wdrożyć tę technologię do pracy z informacjami, istnieje kilka języków programowania. Należą do nich na przykład PL/SQL od Oracle, PSQL w systemach InterBase i Firebird, a także klasyczny Microsoft Transact-SQL. Wszystkie są przeznaczone do tworzenia i wykonywania procedur składowanych, co pozwala dużym procesorom baz danych na korzystanie z własnych algorytmów. Jest to również konieczne, aby osoby zarządzające takimi informacjami mogły chronić wszystkie obiekty przed nieuprawnionym dostępem osób trzecich i, w związku z tym, utworzeniem, modyfikacją lub usunięciem niektórych danych.

Wydajność

Te obiekty bazy danych można programować na różne sposoby. Pozwala to użytkownikom wybrać najbardziej odpowiedni rodzaj stosowanej metody, oszczędzając wysiłek i czas. Dodatkowo procedura jest przetwarzana sama, co pozwala uniknąć ogromnego czasu poświęconego na komunikację pomiędzy serwerem a użytkownikiem. Ponadto moduł można w każdej chwili przeprogramować i zmienić w pożądanym kierunku. Na szczególną uwagę zasługuje szybkość, z jaką uruchamiana jest procedura składowana SQL: proces ten przebiega 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 tym, że gwarantuje zwiększone bezpieczeństwo. Zapewnia to możliwość całkowitego wykluczenia dostępu do procedur przez innych użytkowników. Pozwoli to administratorowi na samodzielne przeprowadzanie z nimi operacji, bez obawy o przechwycenie informacji czy nieuprawniony dostęp do bazy danych.

Transfer danych

Relacja między procedurą składowaną SQL a aplikacją kliencką polega na użyciu parametrów i wartości zwracanych. Ta ostatnia nie musi przekazywać danych do procedury składowanej, jednak informacja ta (głównie na żądanie użytkownika) jest przetwarzana dla SQL. Po zakończeniu pracy procedura składowana wysyła pakiety danych z powrotem (ale znowu opcjonalnie) do aplikacji, która ją wywołała, używając różne metody, za pomocą którego można wykonać zarówno wywołanie zapisanej procedury SQL, jak i powrót, na przykład:

Przesyłanie danych przy użyciu parametru typu Output;

Przekazywanie danych za pomocą operatora powrotu;

Przekazywanie danych za pomocą operatora wyboru.

Przyjrzyjmy się teraz, jak ten proces wygląda od środka.

1. Utwórz procedurę składowaną EXEC w SQL

Procedurę możesz utworzyć w MS SQL (Managment Studio). Po utworzeniu procedura zostanie ona uwzględniona w programowalnym węźle bazy danych, w którym operator realizuje procedurę tworzenia. Do wykonania procedury składowane SQL wykorzystują proces EXEC, który zawiera nazwę samego obiektu.

Kiedy tworzysz procedurę, jako pierwsza pojawia się jej nazwa, po której następuje jeden lub więcej przypisanych do niej parametrów. Parametry mogą być opcjonalne. Po zapisaniu parametrów, czyli treści procedury, należy wykonać kilka niezbędnych operacji.

Chodzi o to, że w ciele mogą znajdować się zmienne lokalne i te zmienne są także lokalne w stosunku do procedur. Innymi słowy, można je przeglądać jedynie w treści procedury Microsoft SQL Server. Procedury składowane w tym przypadku są uważane za lokalne.

Zatem, aby utworzyć procedurę, potrzebujemy nazwy procedury i co najmniej jednego parametru jako treści procedury. Należy pamiętać, że świetną opcją w tym przypadku jest utworzenie i wykonanie procedury z nazwą schematu w klasyfikatorze.

Treść procedury może być dowolnego rodzaju, na przykład utworzyć tabelę, wstawić jeden lub więcej wierszy tabeli, ustalić typ i charakter bazy danych i tak dalej. Jednakże treść procedury ogranicza wykonywanie w jej obrębie pewnych operacji. Poniżej wymieniono niektóre z ważnych ograniczeń:

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

Ciało nie powinno stwarzać fałszywego wrażenia obiektu;

Ciało nie powinno tworzyć żadnych wyzwalaczy.

2. Ustawienie zmiennej w treści procedury

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

Czasami możesz zauważyć, że w jednym wierszu ustawiono kilka zmiennych, a każdy parametr zmiennej jest oddzielony przecinkiem. Należy również pamiętać, że zmienna jest poprzedzona @. W treści procedury możesz ustawić zmienną w dowolnym miejscu. Na przykład zmienną @NAME1 można zadeklarować na końcu treści procedury. Do przypisania wartości zadeklarowanej zmiennej wykorzystywany jest zbiór danych osobowych. W przeciwieństwie do sytuacji, gdy w tym samym wierszu zadeklarowano więcej niż jedną zmienną, w tej sytuacji wykorzystywany jest tylko jeden zestaw danych osobowych.

Użytkownicy często zadają pytanie: „Jak przypisać wiele wartości w jednej instrukcji w treści procedury?” Dobrze. To ciekawe pytanie, ale jest o wiele łatwiejsze do wykonania, niż myślisz. Odpowiedź: Używanie par takich jak „Wybierz Var = wartość”. Możesz użyć tych par, oddzielając je przecinkiem.

Różnorodne przykłady pokazują, jak ludzie tworzą prostą procedurę składowaną i ją wykonują. Procedura może jednak akceptować parametry takie, że proces ją wywołujący będzie miał wartości zbliżone do niej (choć nie zawsze). Jeśli się pokrywają, wówczas w ciele rozpoczynają się odpowiednie procesy. Na przykład, jeśli utworzysz procedurę, która przyjmie miasto i region od wywołującego i zwróci dane o liczbie autorów należących do odpowiedniego miasta i regionu. Procedura wykona zapytanie do tabel autorów bazy danych, takich jak Puby, w celu zliczenia autorów. Na przykład, aby uzyskać te bazy danych, Google pobiera skrypt SQL ze strony SQL2005.

W poprzednim przykładzie procedura przyjmuje dwa parametry, które język angielski warunkowo będzie nazywany @State i @City. Typ danych jest zgodny z typem zdefiniowanym w aplikacji. Treść procedury zawiera zmienne wewnętrzne @TotalAuthors i ta zmienna służy do wyświetlania liczby autorów. Następnie następuje sekcja wyboru zapytania, która wszystko oblicza. Na koniec obliczona wartość jest drukowana w oknie wyjściowym za pomocą instrukcji print.

Jak wykonać procedurę składowaną w SQL

Istnieją dwa sposoby przeprowadzenia zabiegu. Pierwszy sposób pokazuje, poprzez przekazanie parametrów, w jaki sposób wykonywana jest lista rozdzielana przecinkami po nazwie procedury. Załóżmy, że mamy dwie wartości (jak w poprzednim przykładzie). Wartości te zbierane są przy pomocy zmiennych parametrów procedury @State i @City. W tej metodzie przekazywania parametrów ważna jest kolejność. Ta metoda nazywa się porządkowym przekazywaniem argumentów. W drugiej metodzie parametry są już przypisane bezpośrednio i w tym przypadku kolejność nie ma znaczenia. Ta druga metoda nazywana jest przekazywaniem nazwanych argumentów.

Procedura może nieznacznie odbiegać od typowej. Wszystko jest takie samo jak w poprzednim przykładzie, tylko tutaj parametry są przesunięte. Oznacza to, że parametr @City jest przechowywany jako pierwszy, a parametr @State jest przechowywany obok wartości domyślnej. Parametr domyślny jest zwykle podświetlany osobno. Procedury składowane SQL są przekazywane jako parametry. W takim przypadku parametr „UT” zastępuje wartość domyślną „CA”. W drugim wykonaniu do parametru @City przekazywana jest tylko jedna wartość argumentu, a parametr @State przyjmuje wartość domyślną „CA”. Doświadczeni programiści radzą, aby domyślnie wszystkie zmienne umieszczać na końcu listy parametrów. W przeciwnym razie wykonanie nie będzie możliwe i wówczas będziesz musiał pracować z przekazywaniem nazwanych argumentów, co jest dłuższe i bardziej złożone.

4. Procedury zapisane w bazie SQL Server: metody zwrotu

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

Zwróć wartość procedury składowanej;

Dane wyjściowe parametrów procedury składowanej;

Wybór jednej z procedur składowanych.

4.1 Zwracanie wartości z procedur przechowywanych SQL

W tej technice procedura przypisuje wartość zmiennej lokalnej i zwraca ją. Procedura może również bezpośrednio zwracać stałą wartość. W poniższym przykładzie utworzyliśmy procedurę, która zwraca Łączna autorski. Jeśli porównasz tę procedurę z poprzednimi, zobaczysz, że wartość wydruku jest odwrócona.

Zobaczmy teraz, jak wykonać procedurę i wydrukować zwracaną przez nią wartość. Wykonanie procedury wymaga ustawienia zmiennej i wydrukowania, co następuje po całym procesie. Należy pamiętać, że zamiast instrukcji print można użyć instrukcji Select, takiej jak Select @RetValue oraz OutputValue.

4.2 Dane wyjściowe parametrów procedury składowanej SQL

Wartość odpowiedzi może zostać użyta do zwrócenia pojedynczej zmiennej, co widzieliśmy w poprzednim przykładzie. Użycie parametru Output umożliwia procedurze wysłanie jednej lub większej liczby wartości zmiennych do osoby wywołującej. Parametr wyjściowy jest dokładnie oznaczony słowem kluczowym „Wyjście” podczas tworzenia procedury. Jeżeli jako parametr wyjściowy podano parametr, to obiekt procedury musi przypisać mu wartość. Procedury składowane SQL, których przykłady można zobaczyć poniżej, w tym przypadku zwracają informacje podsumowujące.

W naszym przykładzie będą dwie nazwy wyjściowe: @TotalAuthors i @TotalNoContract. Są one wskazane na liście parametrów. Zmienne te przypisują wartości w obrębie treści procedury. Kiedy używamy parametrów wyjściowych, osoba wywołująca może zobaczyć wartość ustawioną w treści procedury.

Ponadto w poprzednim scenariuszu zadeklarowano dwie zmienne, aby zobaczyć wartości, które procedury składowane MS SQL Server ustawiają w parametrze wyjściowym. Następnie przeprowadza się procedurę poprzez podanie normalnej wartości parametru „CA”. Następujące opcje są wyprowadzane i dlatego zadeklarowane zmienne są przekazywane w określonej kolejności. Należy pamiętać, że podczas przekazywania zmiennych wynik słowo kluczowe pytano również tutaj. Po pomyślnym zakończeniu procedury w oknie komunikatu wyświetlane są wartości zwrócone przez parametry wyjściowe.

4.3 Wybór jednej z procedur składowanych SQL

Technikę tę stosuje się w celu zwrócenia zestawu wartości w postaci tabeli danych (RecordSet) do wywołującej procedury składowanej. W tym przykładzie procedura składowana SQL z parametrami @AuthID wysyła zapytanie do tabeli Authors, filtrując zwrócone rekordy przy użyciu tego parametru @AuthId. Instrukcja Select decyduje, co powinno zostać zwrócone osobie wywołującej procedurę składowaną. Po wykonaniu procedury składowanej identyfikator AuthId jest przekazywany z powrotem. Ta procedura zawsze zwraca tylko jeden rekord lub nie zwraca żadnego. Jednak procedura składowana nie ma żadnych ograniczeń dotyczących zwracania więcej niż jednego rekordu. Nierzadko można spotkać przykłady zwracania danych przy użyciu wybranych parametrów obejmujących zmienne obliczone poprzez podanie wielu sum.

Wreszcie

Procedura składowana to dość poważny moduł programu, który zwraca lub przekazuje, a także ustawia niezbędne zmienne dzięki aplikacji klienckiej. Ponieważ procedura składowana uruchamia się sama na serwerze, można uniknąć (w przypadku niektórych obliczeń) wymiany dużych ilości danych pomiędzy serwerem a aplikacją kliencką. Pozwala to zmniejszyć obciążenie Serwer SQL, co oczywiście przynosi korzyść ich posiadaczom. Jednym z podtypów są procedury składowane T SQL, jednak ich poznanie jest niezbędne osobom tworzącym imponujące bazy danych. Istnieje również duża, wręcz ogromna liczba niuansów, które mogą być przydatne podczas studiowania procedur składowanych, ale jest to potrzebne bardziej tym, którzy planują zająć się programowaniem, w tym zawodowo.

Deklaracja procedury

STWÓRZ PROCEDURĘ [({IN|OUT|INOUT} [,…])]
[DYNAMICZNY ZESTAW WYNIKÓW ]
ZACZYNAĆ [ATOMOWY]

KONIEC

Słowa kluczowe
. IN (Input) – parametr wejściowy
. OUT (Wyjście) – parametr wyjściowy
. INOUT – wejście i wyjście oraz pole (bez parametrów)
. DYNAMIC RESULT SET wskazuje, że procedura może otworzyć określoną liczbę kursorów, które pozostaną otwarte po powrocie procedury

Notatki
Nie zaleca się używania wielu parametrów w procedurach składowanych (głównie dużych liczb i ciągów znaków) ze względu na przeciążenie sieci i stosu. W praktyce istniejące dialekty Transact-SQL, PL/SQL i Informix znacznie różnią się od standardu, zarówno w deklaracji i użyciu parametrów, w deklaracji zmiennych, jak iw wywoływaniu podprogramów. Firma Microsoft zaleca użycie następującego przybliżenia do oszacowania rozmiaru pamięci podręcznej procedury składowanej:
=(maksymalna liczba jednoczesnych użytkowników)*(rozmiar największego planu wykonania)*1.25. Określenie wielkości planu wykonania na stronach można wykonać za pomocą polecenia: DBCC MEMUSAGE.

Wywołanie procedury

W wielu istniejących systemach DBMS procedury składowane wywoływane są za pomocą operatora:

WYKONANIE PROCEDURY [(][)]

Notatka: Wywołania procedur składowanych można wykonywać z poziomu aplikacji, innej procedury składowanej lub interaktywnie.

Przykład deklaracji procedury

UTWÓRZ PROCEDURĘ Proc1 AS //deklaruj procedurę
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating>200 //deklaruj kursor
OPEN Cur1 //otwiera kursor
FETCH NEXT FROM Cur1 //odczytaj dane z kursora
PODCZAS @@Fetch_Status=0
ZACZYNAĆ
POBIERZ NASTĘPNY OD Cur1
KONIEC
ZAMKNIJ Cur1 //zamknij kursor
USUŃ PRZYDZIELENIE Cur1
EXECUTE Proc1 //uruchom procedurę

Wielopostaciowość
W tym samym schemacie można utworzyć dwa podprogramy o tej samej nazwie, jeśli parametry obu podprogramów różnią się od siebie na tyle, że można je rozróżnić. Aby rozróżnić dwie procedury o tej samej nazwie w tym samym schemacie, każdej z nich nadawana jest alternatywna i unikalna nazwa (nazwa specyficzna). Taka nazwa może zostać wyraźnie podana podczas definiowania podprogramu. Przy wywoływaniu podprogramów o kilku identycznych nazwach, określenie wymaganego podprogramu odbywa się w kilku krokach:
. Początkowo definiowane są wszystkie procedury o podanej nazwie, a jeśli ich nie ma, to wszystkie funkcje o podanej nazwie.
. Do dalszej analizy tylko te podprogramy, w odniesieniu do których tego użytkownika ma uprawnienia do wykonywania (EXECUTE).
. Dla nich wybierane są te, których liczba parametrów odpowiada liczbie argumentów wywołania. Sprawdzane są określone typy danych parametrów i ich pozycje.
. Jeżeli pozostał więcej niż jeden podprogram, wybierany jest ten, którego nazwa kwalifikacyjna jest krótsza.
W praktyce w Oracle polimorfizm jest obsługiwany dla funkcji zadeklarowanych tylko w pakiecie, DB@ - w różnych schematach, a w Sybase i MS SQL Server przeciążenie jest zabronione.

Usuwanie i zmiana procedur
Aby usunąć procedurę, użyj operatora:

Aby zmienić procedurę, użyj operatora:

ZMIEŃ PROCEDURA [([{IN|OUT|INOUT}])]
ZACZYNAĆ [ATOMOWY]

KONIEC

Uprawnienia do wykonywania zabiegów

PRZYZNAJ WYKONANIE WŁĄCZONE DO |PUBLICZNY [Z OPCJAMI DOTACYJNYMI]

Procedury systemowe
Wiele systemów DBMS (w tym SQL Server) ma określony zestaw wbudowanych systemowych procedur przechowywanych, których można używać do własnych celów.