Procedury składowane pozwalają na zapisywanie często używanych zapytań SQL, co ułatwia zarządzanie, utrzymanie i optymalizację kodu bazodanowego. Dokładniej, są to zestawy instrukcji SQL zapisane w bazie danych, które można wielokrotnie wywoływać. Procedury te mogą zawierać logikę sterującą, zmienne, pętle, warunki i inne struktury programistyczne, co pozwala na wykonywanie bardziej złożonych operacji niż w przypadku pojedynczych zapytań SQL.
Procedury pełnią także funkcję bezpieczeństwa dostępu do danych. Nie chcąc przyznawać dostępu użytkownikom do pełnej bazy danych lub tabel, możemy przyznać dostęp tylko do procedury umożliwiającej im pobranie interesujących danych.
Tworzenie procedury
Procedurę tworzymy poprzez umieszczenie kodu SQL pomiędzy BEGIN i END:
CREATE PROCEDURE allclients AS
BEGIN
SELECT * FROM CLIENTS
END;
Uwaga! Umieszczenie instrukcji w BEGIN – END nie jest konieczne. Jest jednak zalecane, poprawia czytelność kodu i niezbędne w bardziej zaawansowanych przykładach.
Modyfikowanie procedury
ALTER PROCEDURE allclients AS
BEGIN
SELECT * FROM CLIENTS ORDER BY NAME
END;
Usuwanie procedury
DROP PROCEDURE allclients;
Wywoływanie procedury
Używamy funkcji EXECUTE lub EXEC by wywołać procedurę:
EXECUTE allclients;
XACT_ABORT, NOCOUNT
SET XACT_ABORT, NOCOUNT ON;
Domyślnie te opcje są wyłączone, chcąc je zmienić należy dodać je po słowie AS w definicji procedury.
XACT_ABORT włączona powoduje że wszystkie błędy w transakcji powodują jej cofnięcie, NOCOUNT ON pomija wysyłanie komunikatów o liczbie wierszy affected przez nasz kod.
Tworzenie i wywoływanie procedury z parametrami
Najczęściej nasze procedury posiadają parametry. Parametry definiujemy przed blokiem BEGIN – END. Możemy ustawić domyślną wartość naszego parametru. Przykład prostej procedury dodającej rekordy do tabeli Sales:
CREATE PROCEDURE addrow
@KlientID INT, @ProduktID INT, @Ilosc INT = 1
AS
BEGIN
INSERT INTO Sales (KlientID, ProduktID, Ilosc, DataZamowienia)
VALUES (@KlientID, @ProduktID, @Ilosc, GETDATE());
END;
Wywołanie procedury:
EXECUTE addrow @KlientID=8, @ProduktID=7, @Ilosc=6
Lub w wersji mniej rozszerzonej:
EXECUTE addrow 8,7,6
Przykład procedury z deklaracją zmiennej wewnątrz:
Używamy polecenia DECLARE do zadeklarowania zmiennej
CREATE PROCEDURE wstaw_prac
@ename VARCHAR(10), @sal DECIMAL(8,2)
AS
BEGIN
DECLARE @pracno INT
SELECT @pracno = ISNULL(MAX(pracno), 0) + 1
FROM prac
INSERT INTO prac (pracno, ename, sal)
VALUES (@pracno, @ename, @sal)
END
Użycie TRY – CATCH oraz BEGIN TRANSACTION – COMMIT TRANSACTION
Try – Catch to analogiczne do pythonowskich Try-Except funkcje wprowadzające obsługę błędów. Tzn. silnik próbuje wykonać kod w bloky TRY i jeśli nie uda mu się to, nie wyświetli błędu tylko wykona operacje w bloku CATCH.
BEGIN TRANSACTION rozpoczyna transakcję w T-SQL. Tzn. że operacje w bloku BEGIN TRANSACTION-COMMIT TRANSACTION są traktowane jako jeden spójny blok. Albo wszystkie operacje w bloku zostaną wykonane, albo żadne. Zatwierdzeniem zmian w bazie zajmuje się funkcja COMMIT TRANSACTION.
Użycie obu tych funkcji jest dobrą praktyką pisania kodu.
Przykład procedury z tymi blokami:
CREATE PROCEDURE add_and_update
@KlientID INT, @ProduktID INT, @Ilosc INT,
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO Zamowienia (KlientID, ProduktID, Ilosc, DataZamowienia)
VALUES (@KlientID, @ProduktID, @Ilosc, GETDATE());
UPDATE Produkty
SET StanMagazynowy = StanMagazynowy - @Ilosc
WHERE ProduktID = @ProduktID;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(); THROW 50000, @ErrorMessage, 1;
END CATCH
END;
Nasz kod dodaje do tabeli Zamowienia nowe rekordy oraz aktualizuje wartości w tabeli Produkty. Jeżeli wystąpi błąd, za pomocą funkcji ROLLBACK TRANSACTION operacje są cofane oraz wyświetlany jest błąd. Funkcja DECLARE służy do zdeklarowania zmiennej NVARCHAR do 4000 znaków do której przypisujemy błąd (ERROR_MESSAGE()) i „wyrzucamy” błąd funkcją THROW
Używanie większej liczby transakcji
W T-SQL używanie większej liczby transakcji w jednej procedurze generalnie mija się z celem, ponieważ wszystkie transakcje są zagnieżdżone w jednej głównej i wycofanie jednej z nich, wycofuje całą.
Instrukcje warunkowe, pętle i zagnieżdżone BEGIN END
Procedura która wykonuje instrukcje w zagnieżdżonym BEGIN END po spełnieniu warunku poniżej. Funkcja UPDATE w tym przykładzie nie musi być umieszczona w BEGIN – END, ponieważ jest ona jedyną instrukcją do wykonania. Pisanie tej klauzuli jest jednak dobrą praktyką.
CREATE PROCEDURE add_and_update
@ProduktID INT, @NowyStan INT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
IF EXISTS (SELECT 1 FROM Produkty WHERE ProduktID = @ProduktID)
BEGIN UPDATE Produkty SET StanMagazynowy = @NowyStan WHERE ProduktID = @ProduktID;
END
ELSE
BEGIN
RAISERROR('Produkt nie istnieje', 10, 1);
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH
END;
Procedura backupu lub przywracania bazy danych:
CREATE PROCEDURE base
@Operacja NVARCHAR(10), @NazwaBazy NVARCHAR(128), @Sciezka NVARCHAR(256)
AS
BEGIN
IF @Operacja = 'BACKUP'
BEGIN
BACKUP DATABASE @NazwaBazy TO DISK = @Sciezka WITH INIT;
END
ELSE IF @Operacja = 'RESTORE'
BEGIN
RESTORE DATABASE @NazwaBazy FROM DISK = @Sciezka WITH REPLACE;
END
ELSE
BEGIN
RAISERROR('Nieznana operacja', 16, 1);
END
END;
Przykład procedury z pętlą WHILE:
CREATE PROCEDURE update_values
@StartID INT, @EndID INT, @NewValue NVARCHAR(100)
AS
BEGIN
DECLARE @CurrentID INT;
SET @CurrentID = @StartID;
WHILE @CurrentID <= @EndID BEGIN
UPDATE ExampleTable
SET Value = @NewValue
WHERE ID = @CurrentID;
SET @CurrentID = @CurrentID + 1;
END
END;
Dynamiczny SQL w procedurze
Dynamiczny SQL to sposób pisania kodu, że przekazujemy kod SQL do wykonania w postaci stringa
CREATE OR ALTER PROC getdata
@orderid AS INT = NULL,
@orderdate AS DATE = NULL,
@custid AS INT = NULL,
@empid AS INT = NULL
AS
SET XACT_ABORT, NOCOUNT ON;
DECLARE @sql AS NVARCHAR(MAX) = N’SELECT orderid, orderdate, shippeddate, custid, empid,
shipperid
FROM orders
WHERE 1 = 1’
+ CASE WHEN @orderid IS NOT NULL THEN N’ AND orderid = @orderid ‘ ELSE N’’ END
+ CASE WHEN @orderdate IS NOT NULL THEN N’ AND orderdate = @orderdate’ ELSE N’’ END
+ CASE WHEN @custid IS NOT NULL THEN N’ AND custid = @custid ‘ ELSE N’’ END
+ CASE WHEN @empid IS NOT NULL THEN N’ AND empid = @empid ‘ ELSE N’’ END
+ N’;’
EXEC sys.sp_executesql
@stmt = @sql,
@params = N’@orderid AS INT, @orderdate AS DATE, @custid AS INT, @empid AS INT’,
@orderid = @orderid,
@orderdate = @orderdate,
@custid = @custid,
@empid = @empid;
GO