Co to jest procedura przechowywana?
Odpowiedzi:
Procedury składowane to zestaw instrukcji SQL, które można wykonać na kilka sposobów. Większość głównych DBM obsługuje procedury składowane; jednak nie wszyscy tak robią. Konieczne będzie sprawdzenie za pomocą konkretnej dokumentacji pomocy DBMS w celu uzyskania szczegółowych informacji. Ponieważ jestem najbardziej zaznajomiony z programem SQL Server, użyję go jako moich próbek.
Aby utworzyć procedurę przechowywaną, składnia jest dość prosta:
CREATE PROCEDURE <owner>.<procedure name>
<Param> <datatype>
AS
<Body>Na przykład:
CREATE PROCEDURE Users_GetUserInfo
@login nvarchar(30)=null
AS
SELECT * from [Users]
WHERE ISNULL(@login,login)=loginZaletą procedur przechowywanych jest to, że można scentralizować logikę dostępu do danych w jednym miejscu, które jest następnie łatwe do zoptymalizowania przez DBA. Procedury składowane mają również tę zaletę bezpieczeństwa, że można przyznać uprawnienia do wykonywania procedury przechowywanej, ale użytkownik nie będzie musiał mieć uprawnień do odczytu / zapisu w bazowych tabelach. To dobry pierwszy krok przeciwko iniekcji SQL.
Przechowywane procedury mają swoje wady, w zasadzie konserwację związaną z podstawową obsługą CRUD . Powiedzmy, że dla każdej tabeli masz Wstaw, Aktualizuj, Usuń i przynajmniej jeden wybór na podstawie klucza podstawowego, co oznacza, że każda tabela będzie miała 4 procedury. Teraz weź porządną bazę danych zawierającą 400 tabel i masz 1600 procedur! Zakładając, że nie masz duplikatów, które prawdopodobnie będziesz mieć.
W tym przypadku użycie ORM lub innej metody automatycznego generowania podstawowych operacji CRUD ma mnóstwo zalet.
Procedura składowana to zestaw wstępnie skompilowanych instrukcji SQL służących do wykonania specjalnego zadania.
Przykład: jeśli mam Employeestolik
Employee ID Name Age Mobile
---------------------------------------
001 Sidheswar 25 9938885469
002 Pritish 32 9178542436Najpierw odzyskuję Employeetabelę:
Create Procedure Employee details
As
Begin
Select * from Employee
EndAby uruchomić procedurę na serwerze SQL:
Execute Employee details
--- (Employee details is a user defined name, give a name as you want)Następnie wstawiam wartość do tabeli pracowników
Create Procedure employee_insert
(@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
Insert Into Employee
Values (@EmployeeID, @Name, @Age, @Mobile)
EndAby uruchomić sparametryzowaną procedurę na serwerze SQL:
Execute employee_insert 003,’xyz’,27,1234567890
--(Parameter size must be same as declared column size)Przykład: @Name Varchar(30)
W Employeetabeli Namerozmiar kolumny musi wynosić varchar(30).
Procedura składowana to grupa instrukcji SQL, która została utworzona i zapisana w bazie danych. Procedura składowana akceptuje parametry wejściowe, dzięki czemu pojedyncza procedura może być używana w sieci przez kilku klientów korzystających z różnych danych wejściowych. Procedury składowane zmniejszą ruch w sieci i zwiększą wydajność. Jeśli zmodyfikujemy procedurę przechowywaną, wszyscy klienci otrzymają zaktualizowaną procedurę przechowywaną.
Przykład tworzenia procedury składowanej
CREATE PROCEDURE test_display
AS
SELECT FirstName, LastName
FROM tb_test;
EXEC test_display;Zalety korzystania z procedur przechowywanych
Procedura składowana umożliwia programowanie modułowe.
Możesz utworzyć procedurę raz, zapisać ją w bazie danych i wywołać dowolną liczbę razy w swoim programie.
Procedura przechowywana umożliwia szybsze wykonanie.
Jeśli operacja wymaga dużej ilości kodu SQL, który jest wykonywany powtarzalnie, procedury składowane mogą być szybsze. Są one parsowane i optymalizowane przy pierwszym uruchomieniu, a skompilowana wersja procedury przechowywanej pozostaje w pamięci podręcznej pamięci do późniejszego wykorzystania. Oznacza to, że procedura składowana nie musi być ponownie analizowana i ponownie optymalizowana przy każdym użyciu, co skutkuje znacznie szybszym czasem wykonania.
Procedura przechowywana może zmniejszyć ruch w sieci.
Operację wymagającą setek wierszy kodu Transact-SQL można wykonać za pomocą pojedynczej instrukcji, która wykonuje kod w procedurze, zamiast wysyłać setki wierszy kodu przez sieć.
Procedury przechowywane zapewniają lepsze bezpieczeństwo danych
Użytkownikom można przyznać uprawnienia do wykonania procedury składowanej, nawet jeśli nie mają uprawnień do bezpośredniego wykonywania instrukcji procedury.
W SQL Server mamy różne rodzaje procedur przechowywanych:
- Systemowe procedury składowane
- Procedury składowane zdefiniowane przez użytkownika
- Rozszerzone procedury składowane
System -stored procedury są przechowywane w bazie danych master, a te zaczynają z
sp_prefiksem. Tych procedur można używać do wykonywania różnych zadań w celu obsługi funkcji SQL Server dla zewnętrznych wywołań aplikacji w tabelach systemowychPrzykład: sp_helptext [StoredProcedure_Name]
Procedury składowane zdefiniowane przez użytkownika są zwykle przechowywane w bazie danych użytkowników i zwykle są zaprojektowane do wykonywania zadań w bazie danych użytkowników. Podczas kodowania procedury te nie korzystają z
sp_prefiksu bo jeśli używamysp_przedrostka najpierw sprawdzi bazę danych master, a następnie dochodzi do bazy danych zdefiniowane przez użytkownika.Rozszerzone procedury składowane to procedury wywołujące funkcje z plików DLL. Obecnie rozszerzone procedury przechowywane są przestarzałe z tego powodu, że lepiej byłoby unikać używania rozszerzonych procedur przechowywanych.
Zasadniczo procedura przechowywana to „funkcja SQL”. Oni mają:
-- a name
CREATE PROCEDURE spGetPerson
-- parameters
CREATE PROCEDURE spGetPerson(@PersonID int)
-- a body
CREATE PROCEDURE spGetPerson(@PersonID int)
AS
SELECT FirstName, LastName ....
FROM People
WHERE PersonID = @PersonIDTo jest przykład skoncentrowany na języku T-SQL. Procedury przechowywane mogą wykonywać większość instrukcji SQL, zwracać wartości skalarne i tabelowe i są uważane za bardziej bezpieczne, ponieważ zapobiegają atakom iniekcyjnym SQL.
Pomyśl o takiej sytuacji,
- Masz bazę danych z danymi.
- Dostęp do tej centralnej bazy danych wymaga wielu różnych aplikacji, a w przyszłości także nowych aplikacji.
- Jeśli zamierzasz wstawić wbudowane zapytania do bazy danych, aby uzyskać dostęp do centralnej bazy danych, oddzielnie w kodzie każdej aplikacji, prawdopodobnie prawdopodobnie będziesz musiał powtarzać to samo zapytanie w kodzie różnych aplikacji.
- W takiej sytuacji możesz użyć procedur przechowywanych (SP). Dzięki procedurom składowanym zapisujesz liczbę typowych zapytań (procedur) i zapisujesz je w centralnej bazie danych.
- Teraz powielanie pracy nigdy nie nastąpi jak poprzednio, a dostęp do danych i konserwacja będą wykonywane centralnie.
UWAGA:
- W powyższej sytuacji możesz się zastanawiać: „Dlaczego nie możemy wprowadzić centralnego serwera dostępu do danych w celu interakcji ze wszystkimi aplikacjami? Tak. To będzie możliwa alternatywa. Ale,
- Główną zaletą SP w porównaniu z tym podejściem jest to, że w przeciwieństwie do kodu dostępu do danych z wbudowanymi zapytaniami, SP są wstępnie skompilowanymi instrukcjami, więc będą działać szybciej. A koszty komunikacji (w sieciach) będą minimalne.
- W przeciwieństwie do tego, SP dodają więcej obciążenia do serwera bazy danych. Jeśli byłoby to problemem w zależności od sytuacji, lepszym wyborem będzie scentralizowany serwer dostępu do danych z wbudowanymi zapytaniami.
Procedura przechowywana służy głównie do wykonywania niektórych zadań w bazie danych. Na przykład
- Uzyskaj zestawy wyników bazy danych z jakiejś logiki biznesowej danych.
- Wykonuj wiele operacji na bazie danych w jednym wywołaniu.
- Służy do migracji danych z jednej tabeli do drugiej.
- Może być wywoływany dla innych języków programowania, takich jak Java.
Procedura składowana to nic innego jak grupa instrukcji SQL skompilowanych w jeden plan wykonania.
- Utwórz raz i nazwij to n razy
- Zmniejsza ruch w sieci
Przykład: tworzenie procedury składowanej
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetEmployee
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees
WHERE EmployeeID = @EmployeeID
END
GOZmień lub zmodyfikuj procedurę przechowywaną:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE GetEmployee
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees
WHERE EmployeeID = @EmployeeID
END
GOUpuść lub usuń procedurę przechowywaną:
DROP PROCEDURE GetEmployeeProcedura składowana to wstępnie skompilowany zestaw jednej lub więcej instrukcji SQL, które wykonują określone zadanie.
Procedura przechowywana powinna być wykonywana autonomicznie przy użyciu
EXECProcedura składowana może zwrócić wiele parametrów
Procedurę składowaną można zastosować do realizacji transakcji
Odpowiedź „Co to jest procedura składowana” została już zamieszczona w innych postach tutaj. To, co opublikuję, to mniej znany sposób korzystania z procedury składowanej. To jest grouping stored procedureslub numbering stored procedures.
Odwołanie do składni
; numberzgodnie z tym
Opcjonalna liczba całkowita używana do grupowania procedur o tej samej nazwie. Te zgrupowane procedury można zrzucić razem, używając jednej instrukcji DROP PROCEDURE
Przykład
CREATE Procedure FirstTest
(
@InputA INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)
END
GO
CREATE Procedure FirstTest;2
(
@InputA INT,
@InputB INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GOPosługiwać się
exec FirstTest 10
exec FirstTest;2 20,30Wynik
Kolejna próba
CREATE Procedure SecondTest;2
(
@InputA INT,
@InputB INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GOWynik
Msg 2730, poziom 11, stan 1, procedura SecondTest, wiersz 1 [Batch Start Line 3] Nie można utworzyć procedury „SecondTest” z numerem grupy 2, ponieważ procedura o tej samej nazwie i numerze grupy 1 obecnie nie istnieje w baza danych. Należy wykonać polecenie UTWÓRZ PROCEDURĘ „SecondTest”; najpierw 1.
Referencje :
- UTWÓRZ PROCEDURĘ ze składnią liczby
- Numerowane przechowywane procedury w SQL Server - techie-friendly.blogspot.com
- Grupowanie przechowywanych procedur - sqlmag
UWAGA
- Po pogrupowaniu procedur nie można ich upuścić indywidualnie.
- Ta funkcja może zostać usunięta w przyszłej wersji Microsoft SQL Server.
Procedura składowana to nazwany zbiór instrukcji SQL i logiki proceduralnej, tj. Skompilowany, zweryfikowany i zapisany w bazie danych serwera. Procedura przechowywana jest zwykle traktowana jak inne obiekty bazy danych i kontrolowana przez mechanizm bezpieczeństwa serwera.
W DBMS procedura składowana to zestaw instrukcji SQL o przypisanej nazwie, który jest przechowywany w bazie danych w skompilowanej formie, dzięki czemu może być współdzielony przez wiele programów.
Zastosowanie procedury składowanej może być pomocne w
Zapewnienie kontrolowanego dostępu do danych (użytkownicy końcowi mogą tylko wprowadzać lub zmieniać dane, ale nie mogą pisać procedur)
Zapewnienie integralności danych (dane byłyby wprowadzane w spójny sposób) oraz
Poprawia produktywność (instrukcje procedury składowanej należy zapisać tylko raz)
dla prostych
Procedura składowana to przechowywane programy , program / funkcja przechowywane w bazie danych.
Każdy przechowywany program zawiera treść składającą się z instrukcji SQL. Ta instrukcja może być złożoną instrukcją złożoną z kilku instrukcji oddzielonych średnikami (;).
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;Procedury przechowywane w SQL Server mogą akceptować parametry wejściowe i zwracać wiele wartości parametrów wyjściowych; w SQL Server: instrukcje programu procedur przechowywanych do wykonywania operacji w bazie danych i zwracania wartości statusu do procedury wywołującej lub partii.
Korzyści z używania procedur przechowywanych w SQL Server
Umożliwiają programowanie modułowe. Pozwalają na szybsze wykonanie. Mogą zmniejszyć ruch w sieci. Mogą być używane jako mechanizm bezpieczeństwa.
Oto przykład procedury składowanej, która pobiera parametr, wykonuje zapytanie i zwraca wynik. W szczególności procedura przechowywana akceptuje identyfikator BusinessEntityID jako parametr i używa go do dopasowania klucza podstawowego tabeli HumanResources.Employee w celu zwrócenia żądanego pracownika.
> create procedure HumanResources.uspFindEmployee `*<<<---Store procedure name`*
@businessEntityID `<<<----parameter`
as
begin
SET NOCOUNT ON;
Select businessEntityId, <<<----select statement to return one employee row
NationalIdNumber,
LoginID,
JobTitle,
HireData,
From HumanResources.Employee
where businessEntityId =@businessEntityId <<<---parameter used as criteria
endNauczyłem się tego od essential.com ... jest to bardzo przydatne.
W procedurach przechowywanych instrukcje są zapisywane tylko raz, co zmniejsza ruch w sieci między klientami a serwerami. Możemy także uniknąć ataków typu Injection Sql.
- Jeśli korzystasz z aplikacji innej firmy w swojej aplikacji do przetwarzania płatności, baza danych powinna ujawniać tylko potrzebne informacje i działania, które ta osoba trzecia uzyskała autoryzację, dzięki temu możemy uzyskać poufność danych poprzez ustawienie uprawnień za pomocą Procedur przechowywanych.
- Aktualizacja tabeli powinna dotyczyć tylko tabeli, na którą jest kierowana, ale nie powinna aktualizować żadnej innej tabeli, dzięki której możemy osiągnąć integralność danych za pomocą przetwarzania transakcji i obsługi błędów.
- Jeśli chcesz zwrócić jeden lub więcej elementów o typie danych, lepiej użyć parametru wyjściowego.
- W procedurach przechowywanych używamy parametru wyjściowego do wszystkiego, co należy zwrócić. Jeśli chcesz zwrócić tylko jeden element z typem danych całkowitych, lepiej użyj wartości zwracanej. W rzeczywistości wartość zwracana ma jedynie informować o powodzeniu lub niepowodzeniu procedury składowanej.
ORMlubiszEntity FrameworkwykonywaćCRUDoperacje?