Procedury

Skrypty,
procedury przechowywane
i wyzwalane
Wprowadzenie do systemów baz danych
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.
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

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
Deklarowanie zmiennych
DECLERE @<nazwa_zmiennej> [AS] <typ_zmiennej>[,
@<nazwa_zmiennej> [AS] <typ_zmiennej>,…]
Przykłady:
DECLERE
@NR int,
@Nazwiko AS varchar(15)
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))
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))
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))
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)
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
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
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
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)
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
Tworzenie procedury przechowywanej
CREATE PROCEDURE <nazwa_procedury>
[@nazwa_parametru [AS] typ_danych
[=wartość_domyślna | NULL] [OUTPUT|OUT], …]
AS
kod procedury
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())
Wywołanie procedury
USE [studenci]
GO
EXEC InsertStudent 'Aabik', 'Jan', '1234567', 'Gdynia',
'10 Lutego 11'
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ć
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
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
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
Zastosowania
 Tworzenie procesów wymagających
wywoływania akcji proceduralnych
 Poprawa bezpieczeństwa
 Poprawa wydajności
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
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
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
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
Przykłady wywołań funkcji
 Wywołanie funkcji zwracającej tabelę

select * from ponad_avg()
 Wywołanie funkcji skalarnej

select dbo.suma('tekst1', ' Text2') as wynik

select dbo.suma('tekst1',default) as wynik
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
Tworzenie funkcji - PostgreSQL
 CREATE FUNCTION nazwa([typ1[,...typN]])
RETURNS typ_zwracany
AS {’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
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’
Elementy języka PL/pgsql
 Komentarze
 Deklaracje zmiennych
 Instrukcje przypisania
 Instrukcje SQL
 Instrukcje warunkowe
 Pętle WHILE i FOR
 Wyjątki i komunikaty
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
Tworzenie wyzwalacza SQL Server
 CREATE TRIGGIER nazwa
ON nazwa_tabeli lub widoku
{{FOR | AFTER} <[DELETE][,][INSERT][,][UPDATE]
| INSTED OF}}
AS
instrukcje SQL
Instrukcja CREATE RULE
 Instrukcja CREATE RULE jest rozszerzeniem
języka SQL zastosowanym w PostgreSQL
 CREATE RULE nazwa AS
ON {SELECT | UPDATE | DELETE |
INSERT}
TO nazwa_tabeli
[WHERE warunek]
DO [INSTED] {instrukcja sql | NOTHING}
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
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