Bazy Danych

Procedury

Procedury pozwalają zdefiniować dowolne zapytanie i wywołać je za pomocą komendy EXEC nazwa_procedury.

CREATE PROCEDURE procedure_name { @parameter data_type }

AS

{transact sql code}

Zmienne lokalne, globalne i parametry funkcji i procedur oznaczamy poprzez @ i nazwę zmiennej.

Create procedure PokazOddzial @od int
as
select * from Oddzialy where id = @od;

exec PokazOddzial 10

Odpowiednio do zmiany albo usuwania procedury służą polecenia drop i alter.

IF OBJECT_ID('PokazOddzial','P') IS NOT NULL
DROP PROCEDURE PokazOddzial
go

Create procedure PokazOddzial @od int
as
select * from Oddzialy where id = @od;
go

exec PokazOddzial 10

drop procedure PokazOddzial

Polecenie go wysyła zdefiniowaną część kodu lub zapytania do wywołania na serwer.

W procedurach możemy stosować instrukcje takie jak IF, ELSE, WHILE:

IF OBJECT_ID('PokazOddzial','P') IS NOT NULL
DROP PROCEDURE PokazOddzial
go

Create procedure PokazOddzial @od int
as
if(@od % 10 = 0)
select * from Oddzialy where id = @od;
else
select 'Nie ma'
go

exec PokazOddzial 10
exec PokazOddzial 1

drop procedure PokazOddzial

Zmienne w skryptach deklarujemy za pomocą słowa kluczowego DECLARE nazwa_zmiennej typ.

Wartości zmienny ustawiamy przez polecenie SET.

IF OBJECT_ID('PokazOddzial','P') IS NOT NULL
DROP PROCEDURE PokazOddzial
go

Create procedure PokazOddzial @od int
as
        declare @ile int;
        set @ile=@od;
        while (@ile % 10 <> 0)
        Begin
                select @ile;
                set @ile=@ile+1;
        END
        select * from Oddzialy where id = @ile;
go

exec PokazOddzial 1

drop procedure PokazOddzial

Procedura może zwracać wartości jeżeli zadeklarujemy parametr typu OUTPUT.

Funkcje

Funkcje użytkownika mogą zwracać skalarne lub tablicowe wartości. Funkcji możemy używać w widokach, w innych funkcja, w aplikacjach oraz procedurach.

CREATE FUNCTION function_name

( @parameter_name parameter_data_type

)

RETURNS return_data_type

[ AS ]

BEGIN

function_body

RETURN scalar_expression

END

Przykład:

drop function dbo.szefa;
go

CREATE FUNCTION szefa (@nazwisko varchar(200))
RETURNS int
AS
BEGIN
        declare @wynik int;
        set @wynik =  (select szef from Pracownicy where nazwisko like @nazwisko);
        return @wynik;
END
go

select dbo.szefa('MALINIAK')
go

Funkcje mogą zwracać także typy tablicowe:

drop function dbo.szefa;
go

CREATE FUNCTION szefa (@nazwisko varchar(200))
RETURNS TABLE
AS
        return  (select * from Pracownicy where nazwisko like @nazwisko);
go

select * from dbo.szefa('MALINIAK')
go

Zmienne teblicowe

Jak zauważyliśmy wcześniej zmienne mogą przyjmować też typ tablicowy:

CREATE PROCEDURE nowa
AS
BEGIN
        DECLARE @dana TABLE (placa int, nazwisko varchar(30));
        INSERT INTO @dana (placa, nazwisko)
        SELECT
                Placa_pod, nazwisko FROM Pracownicy;
                select * from @dana;
END
go

exec nowa;

Wyzwalacze

Wyzwalacze (ang. Triggers) są procedurami składowymi uruchamianymi automatycznie w wyniku zaistnienia określonego zdarzenia. Zdarzeniami jakie mogą wywoływać uruchamianie wyzwalaczy są polecenia modyfikujące tabele w bazie danych, modyfikujące bazy danych, a także inne zdarzenia systemowe w bazie danych.

CREATE TRIGGER nazwa_wyzwalacza

ON { nazwa_tabeli | nazwa_widoku }

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

AS { kod_wyzwalacza [ ; ] [ ,...n ] |

Do usuwania lub zmian w wyzwalaczach stosujemy polecenia ALTER i DROP.

Zwykle wyzwalaczy używa się do sprawdzania poprawności wprowadzanych danych, ale można za pomocą nich uruchomić dowolne procedury.

Wyzwalacze mogą być inicjowane zdarzeniami modyfikującymi wiersze tabeli lub widoku (polecenia INSERT, DELETE, UPDATE). Wyzwalacze uruchamiają się niezależnie od tego czy dane polecenia mają wpływ na wiersze w tabeli lub widoku. Dla tabel można wywoływać wyzwalacze po i zamiast danego polecenia (AFTER i INSTEAD OF), natomiast dla widoków, tylko zamiast danego polecenia (INSTEAD OF).

Wyzwalacze DML sprawdzają się wtedy, kiedy za pomocą zwykłych więzów integralności nie jesteśmy w stanie zapisać reguł jakie chcemy wprowadzić w bazie danych, np. reguły dotyczące wprowadzania danych w kolumnie mogą odnosić się do innych kolumn, co nie jest możliwe w przypadku klauzuli CHECK.

CREATE TRIGGER pracownicy_upate
ON Pracownicy
FOR UPDATE
AS
BEGIN
        BEGIN
                declare @placa_nowa int;
                SELECT @placa_nowa = Inserted.placa_pod from inserted
                if (@placa_nowa>10000)
                BEGIN
                        ROLLBACK TRANSACTION
                        PRINT 'Tak dużo nie może zarabiać!'
                END
        END
END

update pracownicy set placa_pod = 20000;

Nowe dane znajdują się w tabeli Inserted a usuwane w tabeli Deleted.

Instrukcje wykonane w ramach ciała wyzwalacza traktowane są jako fragment transakcji jawnie lub niejawnie rozpoczętej przez użytkownika, który odwołał się do danych przechowywanych w powiązanej z wyzwalaczem tabeli. Wynika z tego, że wyzwalacz może zatwierdzić (wykonując instrukcję COMMIT TRANSACTION) lub wycofać (instrukcją ROLLBACK TRANSACTION) zmiany wprowadzone przez użytkownika.

Wyzwalacze, w przeciwieństwie do ograniczeń, wywoływane są w odpowiedzi na akcje użytkownika. Wynika z tego, że dopiero po wykonaniu instrukcji wyzwalany jest wyzwalacz, natomiast warunki ograniczeń sprawdzane są przed wykonaniem instrukcji języka SQL.

Konsekwencją poprzedniego punktu jest kolejność, w jakiej wywoływane są wyzwalacze i ograniczenia – najpierw sprawdzane są warunki zdefiniowane w ograniczeniach, a po ich pomyślnym sprawdzeniu wywoływany jest wyzwalacz.

Wyłącznie właściciel tabeli może utworzyć powiązany z nią wyzwalacz. Uprawnienie do tworzenia wyzwalaczy nie może zostać nikomu nadane czy przekazane.

Wyzwalacze nie mogą zostać powiązane z widokami oraz tabelami tymczasowymi.

Wyzwalacze mogą przetwarzać jednocześnie wiele wierszy tabeli. Możliwe jest również warunkowe przetwarzanie poszczególnych wierszy.

Transakcje

Transakcja jest pojedynczą jednostkę pracy. Jeśli transakcja się powiedzie, wszystkie zmiany danych podczas transakcji są zobowiązane i staje się stałą częścią bazy danych. Jeśli napotka błędy i musi być anulowane lub transakcji wycofana, następnie wszystkie zmiany danych są usuwane.

Transakcję rozpoczynamy poprzez:

BEGIN TRAN

operacje

COMMIT

Instrukcje BEGIN TRAN i COMMIT powodują, że umieszczone pomiędzy nimi polecenia są wykonywane jako całość. Tranasakcje (operacje wykonane w ramach jednej transakcji) wycofuje się poleceniem Rollback.

W MSSQL możemy mieć transakcje typu AUTOCOMMIT - każda operacja jest transakcją. Transakcje jawne uruchomione przy użyciu instrukcji BEGIN TRANSACTION i jawnie zakończone przez COMMIT lub ROLLBACK. Transakcje niejawne zakończone przez COMMIT lub ROLLBACK.

Zadanie Domowe

Wykonac zadania 1,2,3,6,7,8,9 z tematu procedury i funkcje

Termin wykonania zadania: Sobota 31.12.2018 do godziny 24.00.

Rozwiązania proszę przesłać przez stronę:

https://mw.vm.wmi.amu.edu.pl/login

Logujemy się jak na komputery Wydziałowe, przesyłamy plik (jeden wspólny dla wszystkich) z rozwiązaniami.

UWAGA! Rozwiązania można przesłać tylko raz.

*

Wykorzystano materiały z

http://sql-kursy.pl/ms-sql-kurs-create-function-procedure-tworzenie-funkcji-procedur-6.html

https://edu.pjwstk.edu.pl/wiki/e2c3275d0e1a4bc0da360dd225d74a46/(S(4xygsz55i0xgy545pb3f3cjt))/Wyzwalacze%20w%20MS%20SQL%20Server.ashx

https://msdn.microsoft.com/pl-pl/library/programowanie-bazy-danych-procedury-i-funkcje-w-bazie-danych.aspx#6

https://technet.microsoft.com/pl-pl/library/ms174377(v=sql.105).aspx