Skrypty, procedury przechowywane i wyzwalane

1. Skrypty, procedury przechowywanei wyzwalane Wprowadzenie do systemów baz danych

2. Potrzeba dodatkowego zabezpieczenia danych • Bezpośredni dostęp użytkownika do zapytań DLL (SELECT, DELETE, INSERT INTO i UPDATE) może powodować lukę w bezpieczeństwie. • Zasady integralności i bezpieczeństwa danych powinny być kontrolowane przez serwer, a nie przez aplikację kliencką. • Więzy integralności określone w instrukcji CREATE TABLE i transakcje są często niewystarczające i potrzebny jest dodatkowy mechanizm kontroli integralności. • Język SQL jest językiem deklaratywnym i dla rozwiązania pewnych zagadnień istnieje potrzeba wprowadzenia elementów proceduralności – proceduralnego języka SQL.

3. Zagadnienia • Przetwarzanie wsadowe, skrypty SQL • Procedury przechowywane (składowane) • Wyzwalacze • Funkcje użytkownika • Narzędzia • Producenci stworzyli różne wersje proceduralnych języków SQL • Microsoft – język T-SQL • Oracle – język PL/SQL • PostgreSQL – PL/pgSQL • Prezentacja omawia przede wszystkim język T-SQL i PL/pgSQL

4. Skrypt – program wsadowy • Sekwencja instrukcji SQL zapisana w osobnym pliku • Program wsadowy to grupa instrukcji T-SQL zebrana w jedną logiczną całość • Skrypt rozdziela się na programy wsadowe instrukcją GO – dotyczy tylko języka T-SQL • Instrukcje w skryptach, innych systemów zarządzania bazami danych, niż MS SQL Server, rozdziela się średnikami

5. Deklarowanie zmiennych DECLERE @<nazwa_zmiennej> [AS] <typ_zmiennej>[, @<nazwa_zmiennej> [AS] <typ_zmiennej>,…] Przykłady: DECLERE @NR int, @Nazwiko AS varchar(15)

6. Przypisywanie wartości zmiennym(instrukcja SET) DECLARE @NR int, @tekst AS varchar(15) SET @NR = 10 SET @tekst = 'Numer = ' print @nr print @tekst + CAST(@NR AS varchar(15))

7. Przypisywanie wartości zmiennym(instrukcja SET) USE studenci GO DECLARE @średnia_ocen as NUMERIC(5,3) SET @średnia_ocen = (SELECT AVG(ocena) FROM oceny) PRINT @średnia_ocen PRINT 'średnia ocen studentów = ' + CAST(@średnia_ocen AS CHAR(5))

8. Przypisywanie wartości zmiennym(instrukcja SELECT) USE studenci GO DECLARE @średnia_ocen as NUMERIC(5,3) SELECT@średnia_ocen = AVG(ocena) FROM oceny PRINT @średnia_ocen PRINT 'średnia ocen studentów = ' + CAST(@średnia_ocen AS CHAR(5))

9. Ważne funkcje systemowe • @@IDENTITY – zwraca ostatnią wartość identyfikatora ostatniej instrukcji INSERT lub SELECT INTO • @@ROWCOUNT – zwraca liczbę wierszy, których dotyczyła ostatnia instrukcja • @@VERSION – zwraca informację o serwerze • @@ERROR – zwraca numer błędu ostatniej instrukcji T-SQL (0 oznacza brak błędu)

10. Przykład użycia @@IDENTITY USE studenci GO DECLARE @ID int INSERT INTO student(nazwisko, imie, data_urodzenia, nr_albumu, adres_miasto, adres_ulica) VALUES (N'Nowik', N'Jan', '1991-11-11', N'123', N'Gdynia', N'Polska 35') SET @ID = @@IDENTITY PRINT @ID

11. Przykład: @@ROWCOUNT, @@ERROR, @@VERSION USE studenci GO SET NOCOUNT ON; SELECT * from student; PRINT 'Liczba wierszy: ' + CAST(@@ROWCOUNT AS char(4)) PRINT 'Błąd: ' + CAST(@@ERROR AS char(4)) PRINT 'Wersja systemu: ' + @@VERSION

12. Instrukcja GO • GO rozdziela skrypt na kilka programów wsadowych • GO umieszcza się zawsze w nowej linii • Pewne instrukcje wymagają osobnych programów wsadowych, co można uzyskać dzieląc skrypt instrukcjami GO

13. Dynamiczne tworzenie zapytańinstrukcja EXEC • Argumentem instrukcji EXEC jest zmienna łańcuchowa lub łańcuch tekstu zawierający instrukcję • Wykonywana instrukcja może być tworzona dynamicznie w trakcie wykonywania skryptu • EXEC({<zmienna łańcuchowa> | <łańcuch tekstu>} USE studenci GO DECLARE @tabela AS varchar(20) SET @tabela = 'student' EXEC('SELECT * from ' + @tabela)

14. Procedury przechowywane (składowane) • Procedura przechowywana to rodzaj skryptu (programu wsadowego) przechowywanego w bazie danych, a nie w osobnym pliku • Procedura w odróżnieniu od skryptu może posiadać parametry wejściowe, wyjściowe i wartości wynikowe

15. Tworzenie procedury przechowywanej CREATE PROCEDURE <nazwa_procedury> [@nazwa_parametru [AS] typ_danych [=wartość_domyślna | NULL] [OUTPUT|OUT], …] AS kod procedury

16. Przykład procedury dodającej studenta CREATE PROCEDURE dbo.InsertStudent ( @nazwisko varchar(20), @imie varchar(20), @nr_albumu char(11), @adres_miasto varchar(20), @adres_ulica varchar(50) ) AS SET NOCOUNT OFF; INSERT INTO [student] ([nazwisko], [imie], [nr_albumu], [adres_miasto], [adres_ulica]) VALUES (@nazwisko, @imie, @nr_albumu, @adres_miasto, @adres_ulica); SELECT id_studenta, nazwisko, imie, nr_albumu, adres_miasto, adres_ulica FROM student WHERE (id_studenta = SCOPE_IDENTITY())

17. Wywołanie procedury USE [studenci] GO EXEC InsertStudent 'Aabik', 'Jan', '1234567', 'Gdynia', '10 Lutego 11'

18. Uwagi • Słowo kluczowe OUTPUT określa parametry wyjściowe procedury • OUTPUT musi być użyte przy deklaracji procedury i przy jej wywołaniu • Pominięcie OUTPUT przy wywołaniu spowoduje, że wartość parametru wyjściowego nie zostanie przypisana • EXEC można pominąć , gdy wywołanie procedury jest pierwszą instrukcją w programie wsadowym, ale lepiej tego nie robić

19. Przykład zmiennego parametru CREATE PROCEDURE dbo.InsertStudent_NR ( @nazwisko varchar(20), @imie varchar(20), @nr_albumu char(11), @adres_miasto varchar(20), @adres_ulica varchar(50), @numer int OUTPUT ) AS SET NOCOUNT OFF; INSERT INTO [student] ([nazwisko], [imie], [nr_albumu], [adres_miasto], [adres_ulica]) VALUES (@nazwisko, @imie, @nr_albumu, @adres_miasto, @adres_ulica); SET @numer = @@IDENTITY

20. Wywołanie procedury zmieniającej parametr USE [studenci] GO DECLARE @numer int EXEC InsertStudent_NR 'Kitel', 'Jan', 'q234567', 'Gdynia', '10 Lutego 11',@numer OUTPUT PRINT @numer

21. Instrukcje sterujące przebiegiem programu • IF … ELSE • CASE • GOTO • WHILE • RETURN • BEGIN … END • TRY/CATH • WAITFOR – maksymalnie do 24h • WAITFOR DELAY '01:00' - czekaj 1h • WAITFOR TIME '01:00' - czekaj do godziny 1

22. Zastosowania • Tworzenie procesów wymagających wywoływania akcji proceduralnych • Poprawa bezpieczeństwa • Poprawa wydajności

23. Wywoływanie funkcji • SELECT 2*log(10) AS wynik • wynik • 4,60517 • SELECT moja_funkcja(argumenty) • W bazie danych ORACLE w zapytaniu musi być klauzula FROM

24. Funkcje definiowane przez użytkownika CREATE FUNCTION <nazwa_funkcji> [@nazwa_parametru [AS] typ_danych [=wartość_domyślna], … RETURNS {typ_skalarny|TABLE [(def_tabeli)] AS BEGIN kod_funkcji {RETURN obliczony_wynik | RETURN (instrukcja SELECT)} END

25. Przykład funkcji skalarnej CREATE FUNCTION dbo.suma ( @param1 varchar(20) = 'Ala ma kota', @param2 varchar(20) = ' a kot ma Alę' ) RETURNS varchar(40) AS BEGIN DECLARE @suma varchar(40) SET @suma = @param1 + @param2 RETURN @suma END

26. Przykład funkcji zwracającej tabelę CREATE FUNCTION dbo.ponad_avg() RETURNS @table_variable TABLE (student varchar(40), [średnia] REAL) AS BEGIN DECLARE @Param1 real SELECT @Param1 = AVG(ocena) FROM oceny INSERT INTO @table_variable SELECT Student, AVG(ocena) AS Expr1 FROM lista_ocen GROUP BY Student HAVING (AVG(ocena) > @Param1) RETURN END

27. Przykłady wywołań funkcji • Wywołanie funkcji zwracającej tabelę • select * fromponad_avg() • Wywołanie funkcji skalarnej • selectdbo.suma('tekst1', ' Text2') as wynik • select dbo.suma('tekst1',default) as wynik

28. Tworzenie obiektów bazy danych w kodzie zarządzanym • SQL Server 2005 umożliwia tworzenie obiektów bazy danych w preferowanym języku platformy .NET • Najprościej jest tworzyć te obiekty przy pomocy Visual Studio otwierając w wybranym języku SQLServer Project • Po skompilowaniu VS utworzy bibliotekę, która zostanie dołączona do bazy danych a stworzone w projekcie obiekty staną się obiektami bazy danych

29. Tworzenie funkcji - PostgreSQL • CREATE FUNCTION nazwa([typ1[,...typN]])RETURNS typ_zwracanyAS {’nazwa_pliku’ | ’definicja’}LANGUAGE ’nazwa_języka’ • Nazwa języka to jeden z dostępnych w PostgreSQL języków: C, sql, plpgsql, pltcl, plperl • Przed pierwszym użyciem język proceduralny trzeba zainstalować poleceniem CREATE LANGUAGE

30. Przykład • CREATE FUNCTION funkcja(text)RETURNS bool AS’DECLARE tekst ALIAS FOR $1; BEGIN INSERT INTO tabela1 VALUES (tekst); RETURN ’yes’; END;’LANGUAGE ’plpgsql’

31. Elementy języka PL/pgsql • Komentarze • Deklaracje zmiennych • Instrukcje przypisania • Instrukcje SQL • Instrukcje warunkowe • Pętle WHILE i FOR • Wyjątki i komunikaty

32. Tworzenie wyzwalacza PostgreSQL • CREATE TRIGGIER nazwa{AFTER | BEFORE} zdarzenie1 [OR zdarzenie2 ...]ON nazwa_tabeli FOR EACH {ROW | STATEMENT}EXECUTE PROCEDURE nazwa_funkcji(argumenty) • Zdarzeniem może być INSERT, DELETE, UPDATE • Instrukcja CREATE TRIGGIER występuje w SQL3, ale nie występuje w SQL2 i SQL1

33. Tworzenie wyzwalacza SQL Server • CREATE TRIGGIER nazwaON nazwa_tabeli lub widoku{{FOR | AFTER} <[DELETE][,][INSERT][,][UPDATE]| INSTED OF}}ASinstrukcje SQL

34. Instrukcja CREATE RULE • Instrukcja CREATE RULE jest rozszerzeniem języka SQL zastosowanym w PostgreSQL • CREATE RULE nazwa ASON {SELECT | UPDATE | DELETE | INSERT}TO nazwa_tabeli[WHERE warunek]DO [INSTED] {instrukcja sql | NOTHING}

35. Zastosowania • Centralne sprawdzanie poprawności danych • Śledzenie zmian np. zapisanie w osobnej tabeli kto i kiedy dokonywał zmian, a nawet zapisanie danych, które zmieniono • Polepszenie bezpieczeństwa np. wysyłanie wiadomości pocztą elektroniczną o niektórych zmianach • Komunikacja z innymi bazami danych (języki c, pgperl, pgtcl) • Replikacja danych • Przygotowanie danych dla aplikacji klienckich

36. Wady • System oparty głównie na wyzwalaczach i procedurach wyzwalanych może być trudny do zarządzania • Pogarsza się przejrzystość bazy danych • Brak standardowej składni zapisu procedur przechowywanych i wyzwalaczy – zastosowanie ich praktycznie uniemożliwia zmianę SZBD • W miarę wzrostu liczby wyzwalaczy rosną koszty przetwarzania