Co to jest procedura składowana?

Procedura składowana jest zbiorem wstępnie skompilowanych instrukcji SQL, które są używane do wykonywania zadań specjalnych.

Przykład: jeśli mam Employee tabelę

Employee ID  Name       Age  Mobile
---------------------------------------
001          Sidheswar  25   9938885469
002          Pritish    32   9178542436

Najpierw odzyskuję Employee tabelę:

Create Procedure Employee details
As
Begin
    Select * from Employee
End

Aby 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)
End

Aby uruchomić procedurę parametryzacji 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 tabeli Employee Name rozmiar kolumny musi wynosić varchar(30).

Procedura składowana to grupa poleceń SQL, które zostały utworzone i zapisane w bazie danych. Procedura składowana akceptuje parametry wejściowe, dzięki czemu pojedyncza procedura może być używana przez sieć przez kilku klientów korzystających z różnych danych wejściowych. Procedury przechowywane zmniejszą ruch sieciowy i zwiększą wydajność. Jeśli zmodyfikujemy procedurę składowaną, wszyscy klienci otrzymają zaktualizowaną procedurę składowaną.

Przykład tworzenia procedury składowanej

CREATE PROCEDURE test_display
AS
    SELECT FirstName, LastName
    FROM tb_test;

EXEC test_display;

Zalety stosowania procedur składowanych

• 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 składowana pozwala na szybsze wykonanie.

  Jeśli operacja wymaga dużej ilości kodu SQL, która jest wykonywana powtarzalnie, procedury przechowywane mogą być szybsze. Są przetwarzane i optymalizowane, gdy są wykonywane po raz pierwszy, a skompilowane wersja procedury składowanej pozostaje w pamięci podręcznej do późniejszego użycia. Oznacza to, że procedura składowana nie musi być reparsowana i ponownie optymalizowana przy każdym użyciu, co skutkuje znacznie krótszym czasem wykonania.

• Procedura składowana może zmniejszyć ruch sieciowy.

  Operacja wymagająca setek linii kodu Transact-SQL może być wykonana za pomocą pojedynczej instrukcji, która wykonuje kod w procedurze, a nie wysyłając setki linii kodu przez sieć.

• Procedury przechowywane zapewniają lepsze bezpieczeństwo danych

  Użytkownicy mogą otrzymać pozwolenie na wykonanie procedury składowanej, nawet jeśli nie mają pozwolenia na bezpośrednie wykonanie instrukcji procedury.

  W SQL Server mamy różne typy procedur składowanych:

  • procedury przechowywane w systemie
  • procedury składowane zdefiniowane przez Użytkownika
  • Extended stored Procedury

• System - procedury przechowywane są przechowywane w głównej bazie danych i zaczynają się od prefiksu sp_. Procedury te mogą być używane do wykonywania różnych zadań obsługujących funkcje SQL Server dla zewnętrznych wywołań aplikacji w tabelach systemowych

  Przykład: sp_helptext [StoredProcedure_Name]

• Procedury przechowywane zdefiniowane przez Użytkownika są zwykle przechowywane w bazie danych użytkownika i są zazwyczaj zaprojektowane tak, aby zadania w bazie danych użytkowników. Podczas kodowania tych procedur nie należy używać prefiksu sp_, ponieważ jeśli najpierw użyjemy prefiksu sp_, sprawdzi on główną bazę danych, a następnie dojdzie do zdefiniowanej przez użytkownika bazy danych.

• Extended procedury składowane są procedurami, które wywołują funkcje z plików DLL. Obecnie rozszerzone procedury składowane są przestarzałe z tego powodu, że lepiej byłoby unikać stosowania rozszerzonych procedur składowanych.

Ogólnie, procedura składowana jest " funkcją SQL."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 = @PersonID

Jest to przykład skupiony na T-SQL. Procedury przechowywane mogą wykonywać większość poleceń SQL, zwracać wartości skalarne i tabel i są uważane za bardziej bezpieczne, ponieważ zapobiegają atakom SQL injection.

Pomyśl o takiej sytuacji,

Masz bazę danych z danymi.
• Istnieje wiele różnych aplikacji potrzebnych do uzyskania dostępu do tej centralnej bazy danych, a w przyszłości także kilka nowych aplikacji.
• Jeśli chcesz wstawić wewnętrzne zapytania do bazy danych, aby uzyskać dostęp do centralnej bazy danych, w kodzie każdej aplikacji indywidualnie, prawdopodobnie będziesz musiał ponownie powielać to samo zapytanie w kodzie różnych aplikacji.
• w tym rodzaju sytuacji, można użyć procedur składowanych (SPs). Dzięki procedurom przechowywanym zapisujesz liczbę typowych zapytań (procedur) i przechowujesz je w centralnej bazie danych.
Teraz powielanie pracy nigdy nie nastąpi tak jak wcześniej, 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 do interakcji ze wszystkimi aplikacjami? Tak. To będzie możliwa alternatywa. Ale,
• główną zaletą SPS nad tym podejściem jest to, w przeciwieństwie do kodu dostępu do danych z zapytaniami inline, SPs są wstępnie skompilowanymi instrukcjami, więc będą działać szybciej. A koszty komunikacji (za pośrednictwem sieci) będą minimalne.
• W przeciwieństwie do tego, SPs doda trochę więcej obciążenia do serwera bazy danych. Jeśli byłoby to problemem w zależności od sytuacji, scentralizowany serwer dostępu do danych z zapytaniami inline będzie lepszym wyborem.

Procedura składowana jest używana głównie do wykonywania określonych zadań w bazie danych. Na przykład

• pobranie zestawów wyników bazy danych z logiki biznesowej na danych.
• wykonywanie wielu operacji bazodanowych w jednym wywołaniu.
• używany do migracji danych z jednej tabeli do drugiej tabeli.
• może być wywołany dla innych języków programowania, takich jak Java.

Procedura składowana to nic innego jak grupa poleceń SQL skompilowanych w jeden plan wykonania.

1. Utwórz raz i nazwij go n liczba razy
2. zmniejsza ruch sieciowy

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
GO

Zmiana lub modyfikacja procedury składowanej:

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
GO

Upuść lub usuń procedurę składowaną:

DROP PROCEDURE GetEmployee

Procedura składowana jest używana do pobierania danych, modyfikowania danych i usuwania danych z tabeli bazy danych. Nie musisz pisać całego polecenia SQL za każdym razem, gdy chcesz wstawić, zaktualizować lub usunąć dane w bazie danych SQL.

• Procedura składowana jest wstępnie skompilowanym zestawem jednego lub więcej poleceń SQL, które wykonują określone zadanie.

• Procedura składowana powinna być wykonywana samodzielnie przy użyciu EXEC

• Procedura składowana może zwracać wiele parametrów

• Procedura składowana może być użyta do implementacji transact

"Co to jest procedura składowana" jest już odpowiedział w innych postach tutaj. To, co napiszę, to jeden mniej znany sposób korzystania z procedury składowanej. Jest to grouping stored procedures lub numbering stored procedures.

Syntax Reference

; number Jak na to

Opcjonalna liczba całkowita używana do grupowania procedur o tej samej nazwie. Te zgrupowane procedury można upuścić razem za pomocą jednej procedury upuszczenia oświadczenie

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
GO

Użyj

exec FirstTest 10
exec FirstTest;2 20,30

Wynik

Kolejna Próba

CREATE Procedure SecondTest;2
(
     @InputA INT,
     @InputB INT
)
AS 
BEGIN

SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)

END
GO

Wynik

Msg 2730, Level 11, State 1, Procedure SecondTest, Line 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 nie istnieje obecnie w bazie danych. Musi wykonaj procedurę CREATE 'SecondTest'; 1 pierwszy.

Referencje :

1. Utwórz procedurę ze składnią dla liczby
2. numerowane procedury przechowywane w SQL Server - techie-friendly.blogspot.com
3. grupowanie procedur składowanych - Sqlmag

Uwaga

1. Po pogrupowaniu procedur, nie możesz ich odrzucić indywidualnie.
2. ta funkcja może zostać usunięta w przyszłości wersja Microsoft SQL Server.

Procedura składowana jest nazwanym zbiorem instrukcji SQL i logiki proceduralnej, tj. skompilowanym, zweryfikowanym i przechowywanym w bazie danych serwera. Procedura składowana jest zazwyczaj traktowana jak inne obiekty bazy danych i kontrolowana przez mechanizm bezpieczeństwa serwera.

W DBMS, procedura składowana jest zestawem poleceń SQL z przypisaną nazwą, która jest przechowywana w bazie danych w formie skompilowanej, dzięki czemu może być współdzielona przez wiele programów.

Użycie procedury składowanej może być pomocne w

1. Zapewnienie kontrolowanego dostępu do danych (użytkownicy końcowi mogą tylko wprowadzać lub zmieniać dane, ale nie mogą pisać procedur)

2. Zapewnienie integralności danych (Dane będą wprowadzane w sposób spójny) oraz

3. Poprawia produktywność (instrukcje procedury składowanej muszą być zapisane tylko raz)

Dla prostych,

Procedura składowana to programy przechowywane , program / Funkcja przechowywana w bazie danych.

Każdy przechowywany program zawiera ciało, które składa się z instrukcji SQL. Instrukcja ta może być złożoną instrukcją złożoną z kilku instrukcji oddzielonych średnikiem (;).

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @x = 0;
  REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;

Procedury składowane w SQL Server mogą przyjmować parametry wejściowe i zwracać wiele wartości parametrów wyjściowych; w SQL Server procedury składowane polecenia programu do wykonywania operacji w bazie danych i zwracają wartość statusu do procedury wywołującej lub partii.

Korzyści z używania procedur składowanych w SQL Server

Oto przykład procedury składowanej, która pobiera parametr, wykonuje zapytanie i zwraca wynik. W szczególności, procedura składowana akceptuje Business Entityid jako parametr i wykorzystuje go do dopasowania klucza podstawowego Zasobów Ludzkich.Tabela pracowników, aby zwrócić żą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
end

Nauczyłem się tego od essential.com...it jest bardzo przydatna.

Procedura składowana pomoże Ci stworzyć kod na serwerze.Możesz przekazać parametry i znaleźć wyjście.

create procedure_name (para1 int,para2 decimal)
as
select * from TableName