select

Wykład 5: Zaawansowany
SQL
Zaawansowany SQL
 Typy danych SQL i schematy
 Więzy integralności (spójności)
 Wyzwalacze (triggers)
 Procedury składowane (stored procedures)
 Funkcje
 Autoryzacja
 Role
 Zanurzony SQL
 Dynamiczny SQL
Typy danych czas-data
 date: Data zawierające (4 cyfry) rok, miesiąc i dzień
Przykład: date ‘2007-1-27’
 time: czas w godzinach, minutach i sekundach.
 Przykład: time ‘11:55:30’
time ‘11:55:30.65’
 timestamp: data oraz czas
 Przykład: timestamp ‘2007-1-27 11:55:30.75’
 interval: Przedział czasu
 Przykład: interval ‘1’ day
 odejmowanie jednej wartości date/time/timestamp od innej
daje wartość typu interval
 Wartości typu interval mogą być dodawane do wartości
date/time/timestamp

Typy danych czas-data (cd.)
 Możemy pobierać wartości poszczególnych pól z wartości
date/time/timestamp
 Przykład: extract (year from r.starttime)
 MSSQL używa funkcji DAY, MONTH, YEAR, DATEADD,
DATEDIFF, DATEPART oraz GETDATE:

SELECT YEAR(starttime) from r;
 Możemy rzutować łańcuchy znaków na wartości typu
date/time/timestamp
 Przykład:


cast <wyrażenie tekstowe> as date
Przykład MSSQL:

cast ('1 październik 2003' as datetime)
Typy definiowalne
 create type – taka konstrukcja w SQL tworzy typ definiowalny
(typ uzytkownika)
create type zloty as numeric (12,2) final

Tylko ORACLE wspiera tą konstrukcję!
 create domain - taka konstrukcja w SQL-92 tworzy dziedziny
typów - definiowalnych
create domain person_name char(20) not null
 Typy i dziedziny są podobne. Dziedziny mogą posiadać więzy
np. not null

Większość producentów SZRBD nie wspiera tych konstrukcji!
Więzy dla dziedzin
 Domain constraints są podstawową postacią więzów
spójności. Sprawdzają wartości wprowadzane do bazy i
sprawdzają czy porównania w kwerendach mają sens:


CREATE DOMAIN VALID_EMPL_IDS INTEGER
CHECK (VALUE BETWEEN 101 AND 199);
 Nowe dziedziny mogą być tworzone z istniejących typów
danych
 Przykład: create domain zloty numeric(12, 2);
create domain euro numeric(12,2);
 Nie można przyrównać/przypisać wartości typu zloty do
wartości typu euro.
 Ale możemy przekształcić typy tak jak poniżej:
(cast r.A as euro)
(Powinno również przemnożyć wynik przez kurs wymiany)
Typy opisujące duże obiekty
 Obiekty takie jak zdjęcia, pliki wideo, ... są przechowywane w bazie
jako large object:
 blob: binary large object – obiekt jest kolekcją binarnych danych,
których interpretacji dokonuje aplikacja poza systemem bazy
danych
 clob: character large object – kolekcja znaków
 Jeżeli w kwerendzie znajduje się zapytanie o duże obiekty
zwracany jest wskaźnik a nie sam obiekt.
 Przykład ORACLE:
 Typ BLOB pozwala na przechowanie do 8 terabajtów danych
binarnych w bazie danych.
 Typ CLOB pozwala na przechowanie do 8 terabajtów
jednobajtowych znaków w bazie danych.
 Typ NCLOB wielobajtowe CBLOB.
 Typ BFILE przechowuje duże dane binarne w plikach
zewnętrznych wzlędem bazy danych.
Więzy spójności
 Więzy spójności zapobiegają przypadkowemu uszkodzeniu
bazy danych. Sprawdzają, czy zmiany w bazie nie powodują
utraty spójności danych.
 Rachunek oszczędnościowy musi mieć stan co najmniej
30,000.00
 Pensja pracownika nie może być mniejsza niż 7 zł za
godzinę
 Klient musi posiadać telefon (niepusta wartość atrybutu)
Więzy spójności dla pojedynczej relacji
 not null
 primary key
 unique
 check (P ), gdzie P jest predykatem
Więzy not null
 Deklarujemy, że oddzial_nazwa dla relacji aktywa jest not null
oddzial_nazwa char(15) not null
 Dziedzina Euro ma być not null
create domain Euro numeric(12,2) not null
Więzy unique
 unique ( A1, A2, …, Am)
 Specyfikacja unique stwierdza, że atrybuty
A1, A2, … Am
tworzą klucz kandydujący.
 W przeciwieństwie do kluczy głównych klucze kandydujące mogą
być puste (null)
Klauzula check
 check (P ), gdzie P jest predykatem (MySQL nie
realizuje klauzuli check)
Przykład: Deklarujemy oddzial_nazwa jako klucz główny i żądamy aby wartości
aktywów nie były ujemne.
create table oddzial
(oddzial_nazwa
char(15),
oddzial_miasto
char(30),
aktywa
integer,
primary key (oddzial_nazwa),
check (aktywa >= 0))
Zbliżają się wybory, w MS SQL sprawdzamy, czy wyborca ma 18 lat w dniu wyborów:
ALTER TABLE wyborca ADD CONSTRAINT
CK_wiek_18
CHECK (DateDiff(yy,DateofBirth, DateofVote)>=18);
Klauzula check
 check może być wykorzystane jako więzy dla krotek (w
poprzednim przypadku warunek dotyczył jednego
atrybutu, poniżej mamy dwa atrybuty wymienione w
warunku)
Przykład:
CREATE TABLE Campus (
location char(25),
enrollment integer,
rank integer,
CHECK(enrollment >= 10,000 OR rank > 5)
);
Klauzula check (cd.)
 W standardzie SQL-92 klauzula check pozwala na ograniczanie
dziedzin:
 Można jej użyć np. do sprawdzenia czy stawka godzinowa
jest większa od wartość określona prawem.
create domain stawka_godzina numeric(5,2)
constraint sprawdz_stawke check(value > = 4.00)
 W ten sposób więzy są nałożone na dziedzinę atrybutu i
zapewniają, że nikt w bazie nie może nam przypisać stawki
mniejszej
 Klauzula constraint sprawdz_stawke jest opcjonalna;
wykorzystywana przy sygnalizacji, jakie więzy zostały
naruszone przy modyfikacji danych.
Więzy referencyjnej spójności
 Zapewniają, że wartość pojawiająca się w jednej relacji dla
danego zbioru atrybutów pojawi się również w innej relacji dla
jakiegoś zbioru atrybutów.
 Przykład: Jeśli “Centum” jest nazwą oddziału pojawiającą się
w jednej z krotek w relacji rachunek, to musi istnieć
odpowiednia krotka w relacji oddzial dla oddziału “Centrum”.
 Klucze główne, klucze kandydujące oraz klucze obce mogą być
specyfikowane jako części polecenia SQL create table :
 Klauzula primary key wymienia atrybuty tworzące klucz
główny.
 Klauzula unique [key] wymienia atrybuty tworzące klucz
kandydujący.
 Klauzula foreign key wymienia atrybuty tworzące klucz obcy
oraz nawę relacji kojarzonej poprzez klucz obcy. Domyślnie
klucz obcy odnosi się do klucza głównego drugiej tabeli.
Więzy spójności w SQL – Przykład
create table klient
(klient_nazwisko char(20),
klient_ulica
char(30),
klient_miasto
char(30),
primary key (klient_nazwisko ))
create table oddzial
(oddzial_nazwa char(15),
oddzial_miasto char(30),
aktywa
numeric(12,2),
primary key (oddzial_nazwa ))
Więzy spójności w SQL – przykład (cd.)
create table rachunek
(rachunek_numer
char(10),
oddzial_nazwa
char(15),
stan
integer,
primary key (rachunek_numer),
foreign key (oddzial_nazwa) references oddzial )
create table depozytor
(klient_nazwisko
char(20),
rachunek_numer
char(10),
primary key (klient_nazwisko, rachunek_numer),
foreign key (rachunek_numer ) references rachunek,
foreign key (klient_nazwisko ) references klient )
Kaskadowe działanie w SQL
create table rachunek
...
foreign key(oddzial_nazwa) references oddzial
on delete cascade
on update cascade
...)
 Klauzula on delete cascade spowoduje, że jeśli usuwanie jakiegoś
oddziału w relacji oddział powoduje naruszenie więzów spójności to
odpowiednia krotka w relacji rachunek zostanie także usunięta.
 Podobnie sprawa wygląda dla kaskadowych uaktualnień (zmiana
nazwy w tabeli oddzial z „Grudziądz” na „Grudziądz Rynek” powinna
się przenieść do tabeli rachunek).
Kaskadowe działanie w SQL (cd.)
 Jeśli istnieje łańcuch zależności kluczy obcych z on delete cascade
określonym dla każdej zależności to usuwanie (modyfikacja) na jednym
końcu łańcucha propaguje się do drugiego końca (jak kostki domina).
 Jeżeli kaskadowe działanie narusza więzy integralności na jakimś stopniu
kaskady, system porzuca transakcję.
 W wyniku, wszystkie zmiany zostaną wycofane (rollback).
 Więzy spójności są sprawdzane na końcu transakcji
 Cząstkowe kroki mogą łamać więzy spójności przy założeniu,
późniejsze kroki usuną naruszenie
 W przeciwnym przypadku byłoby niemożliwe opisywanie pewnych
faktów, np. wstawienie dwóch krotek, których klucze obce wskazują
wzajemnie na siebie: zawieranie małżeństwa
Więzy spójności w SQL (cd.)
 Alternatywą dla kaskad mogą być:

on delete set null


on delete set default



Wstawia w krotce podrzędnej wartości puste
Wstawia w krotce podrzędnej watości dpmyślne
on delete restrict
on delete no action (MS SQL)

Nie pozwala na usuwanie jeśli istnieje krotka zależna
 Ale wartości puste komplikują „logikę” więzów integralności
jeśli jakikolwiek atrybut klucza obcego ma wartość null, to krotka
spełnia więzy integralności z definicji!
 W MySQL set null nie może dotyczyć sytuacji gdy pole w tabeli ma
warunek not null (oczywiste!)

Zapewnienia (assertions)
 Nie wszystkie warunki można wyrazić przy pomocy więzów
omawianych poprzednio (jak check)
 Zapewnienie (assertion) jest predykatem wyrażającym
warunek, który zawsze ma spełniać cała baza.
 Zapewnienie w SQL przyjmuje postać
create assertion <nazwa_zapewnienia> check <predykat>
 Kiedy wstawione jest „zapewnienie” system sprawdza jego
poprawność oraz sprawdza czy predykat jest spełniony przy
modyfikacji, która może nie spełniać warunku.
 Takie testowanie może wprowadzić duże obciążenie do
bazy, zapewnienia powinny być używane z ostrożnością.
 MS SQL ich nie posiada
Przykład zapewnienia
 Średnia ocen jest > 3.0 and średnia dochod <
1000
CREATE ASSERTION Avgs CHECK(
3.0 < (SELECT avg(ocena) FROM Student)
AND 1000 > (SELECT avg(dochod) FROM Student))
 Student ze średnią < 3.0 może się tylko strać o kampus z
rankingiem > 4.
 CREATE ASSERTION RestrictApps CHECK(
NOT EXISTS (SELECT * FROM Student, Apply,
Campus WHERE Student.ID = Apply.ID AND
Apply.location = Campus.location AND
Student.GPA < 3.0 AND Campus.rank <= 4))

Przykład zapewnienia
 Każdy kredyt posiada przynajmniej jednego kredytobiorcę, który
posiada rachunek ze stanem przynajmniej 3000.00 zł
create assertion stan_wiezy check
(not exists (
select *
from kredyt
where not exists (
select *
from kredytobiorca, depozytor, rachunek
where kredyt.kredyt_numer = kredytobiorca.kredyt_numer
and kredytobiorca.klient_nazwisko =
depozytor.klient_nazwisko
and depozytor.rachunek_numer =
rachunek.rachunek_numer
and rachunek.stan >= 3000)))
Przykład zapewnienia
 Suma wszystkich kwot kredytów w każdym oddziale musi być
mniejsza od sumy stanów rachunków w tym oddziale.
create assertion suma_wiezy check
(not exists (select *
from oddzial
where (select sum(kwota )
from kredyt
where kredyt.oddzial_nazwa =
oddzial.oddzial_nazwa )
>= (select sum (stan )
from rachunek
where rachunek.oddzial_nazwa =
oddzial.oddzial_nazwa )))
PROGRAMOWANIE
Skarb DBA
(głównie na przykładzie MS SQL Server)
 Struktury proceduralne
Programowanie „wsadowe” (batch)
 Zmienne
 Instrukcje sterujące
 Przetwarzanie błędów
Procedury składowane
Funkcje definiowalne
Synonimy
Wyzwalacze DML





 Wyzwalacze i procedury składowane – należą do najważniejszych





narzędzi DBA (database administrator) oraz DBAD (application
developer)
Wyzwalacze mogą zaoszczędzić |DBA utraconego czasu i
problemów poprzez automatyzację monitorowania stanu bazy i
zadań administracyjnych.
Procedury składowane mogą być wykorzystywane do tworzenia
skryptów administracyjnych , które będą używane wielokrotnie i
zmniejszają czas niezbędny do administracji i szansę na powstanie
błędów.
To będzie bliższe klasycznemu programowaniu
Oprócz tego mamy jeszcze UDFy User Defined Functions
Zaczniemy od podstaw: batche, zmienne i instrukcje sterujące!
Batche
 Batch to szereg instrukcji (T-)SQL umieszczonych w jednym pliku
 W pliku *.sql można umieścić wiele batchy, oddzielamy je instrukcją
GO
 Reguły






Niektóre instrukcje muszą być przesłane w ich własnym batchu:
CREATE PROCEDURE, CREATE VIEW, CREATE FUNCTION,
CREATE DEFAULT, CREATE RULE, CREATE SCHEMA, CREATE
TRIGGER
Zmienne muszą być zdefiniowane i wykorzystane w tym samym batchu
Wieloliniowe komentarze /* …. */ muszą się zawierać w jednym batchu
Struktury tabel nie mogą być zmieniane w tym samym batchu
Błąd kompilacji zatrzyma wykonanie wszystkich instrukcji batcha
Błąd wykonania wstrzyma wykonanie kolejnych instrukcji
 USE tempdb;
GO
CREATE TABLE T1 (C1 int NOT NULL);
INSERT INTO T1 VALUES (1);
INSERT INTO T1 VALUES (2,2);
INSERT INTO T1 VALUES (3);
GO
SELECT * FROM T1;
DROP TABLE T1;
GO
 CREATE TABLE jest kompilowane, po kolei są kompilowane
instrukcje INSERT i wykonywane, ale druga Instrukcja zawiera błąd,
więc do tablei zostanie dodany tylko jeden wiersz
Zmienne
 Zmienną definiujemy poprzedzają jej nazwę małpą (@)
 W jednym batchu możemy użyć do 10^4 zmiennych
 DECLARE @Var1 int;
DECLARE @Var2 as varchar(25);
DECLARE @Var3 decimal(5,2),
@Var4 int;
 Przypisanie wartości:

SET @Var1 = 5;
SET @Var2 = ‘A varchar string’;
SELECT @Var2 = ‘Another varchar string’,
@Var3 = 123.45
Zmienne 2
 Inna forma przypisania (przy pomocy zapytania do bazy SELCT)
 USE PPDB;
DECLARE @CustName varchar(50);
SELECT @CustName = CustomerName
FROM Customer WHERE CustomerID = 1;
 Funkcje systemowe (np. @@Error) nazywane czasami (błędnie )
zmiennymi globalnymi
Instrukcje sterujące
 BEGIN … END grupuje instrukcje używane razem z IF, WHILE,
CASE
 IF … ELSE

USE AdventureWorks;
GO
UPDATE HumanResources.EmployeePayHistory
SET PayFrequency = 4
WHERE EmployeeID = 1;
IF @@ERROR <> 0 -- funkcja sysytemowa
BEGIN
PRINT ‘An error occured in the previous
statement.’;
RETURN;
END
ELSE
PRINT ‘No error occured in the previous
statement.’;
Instrukcje sterujące 2
 WHILE

DECLARE @Counter int;
SET @Counter = 1;
WHILE (@Counter <= 10)
BEGIN
PRINT @Counter;
SET @Counter = @Counter + 1;
END
 Powyżej mamy przykład typowej pętli, ale instrukcję WHILE można
też wykorzystywać z warunkiem EXISTS do wykonywania operacji
na wierszach tabeli

WHILE EXISTS (SELECT * FROM T1 WHERE C1 = 1)BEGIN
-- Wykonaj jakieś operacje na wierszach
-- tabeli T1 z warunkiem C1 = 1
END
 W instrukcji WHILE można korzystać z BREAK i CONTINUE,
których użycie jest typowe dla pętli („oczywista oczywistość”).
Instrukcje sterujące 3
 CASE

USE AdventureWorks2008;
GO
SELECT Name,
CASE Name
WHEN ‘Human Resources’ THEN ‘HR’
WHEN ‘Finance’ THEN ‘FI’
WHEN ‘Information Services’ THEN ‘IS’
WHEN ‘Executive’ THEN ‘EX’
WHEN ‘Facilities and Maintenance’ THEN ‘FM’
END AS Abbreviation
FROM AdventureWorks2008.HumanResources.Department
WHERE GroupName = ‘Executive General and Administration’;
 Instrukcja CASE jest używana w celu zamiany wartości kolumny w
zapytaniu SELECT. W Transact-SQL CASE przetwarza wiersz po
wierszu
Zarządzanie błędami
 Błędy składni
 Błędy wykonania
 PRINT ‘Przed błędem’;
SELECT 1/0;
PRINT ‘Po błędzie’;
 Komunikaty błędów
 Numer błędu (powyżej 50 000 błędy definiowalne przez użytkownika)
 Waga błędu (Severity Level) – powyżej 10 mają charakter informacyjny
 Stan (State) (liczba wskazujące gdzie pojawił się błąd?)
 Numer linii
 Tekst komunikatu
 Przykład:
 Przed błędem
 Msg 8134, Level 16, State 1, Line 2
 Divide by zero error encountered.
 Po błędzie
Zarządzanie błędami 2
 Blok TRY … CATCH …
 Składnia

BEGIN TRY
-- Kod mogący generować błędy
END TRY
BEGIN CATCH
-- Logika obsługi błędów
END CATCH;
 Blok CATCH musi następować zaraz po bloku TRY
 Wewnątrz bloku CATCH można użyć kilku funkcji informacyjnych
(por. następny slajd)
 Funkcje te użyte poza blokiem CATCH zwrócą NULL
Zarządzanie błędami 3
 Funkcje informacyjne bloku CATCH






ERROR_LINE()
ERROR_NUMBER()
ERROR_MESSAGE()
ERROR_PROCEDURE() jeśli błąd pojawił się w procedurze jej nazwa jest
zwracana, w przeciwnym razie NULL
ERROR_SEVERITY()
ERROR_STATE()
 Przykład:

USE AdventureWorks2008;
BEGIN TRY
SELECT 1/0;
END TRY
BEGIN CATCH
INSERT INTO dbo.ErrorLog (Line, Number, ErrorMsg, [Procedure],
Severity, [State])
VALUES (ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(),
ERROR_PROCEDURE(), ERROR_SEVERITY(), ERROR_STATE());
END CATCH;
Zarządzanie błędami 4
 Funkcja @@ERROR
 Zwraca tylko numer błędu
 Instrukcje typu
 SELECT 1/0
 PRINT @@ERROR

Ale co będzie wynikiem poniższego kodu?
 SELECT 1/0;
IF @@ERROR <> 0
PRINT @@ERROR;

Warunek IF @@ ERROR <> 0 resetuje wartość funkcji !!!
Należy wiec użyć zmiennej pośredniej, która zapamięta stan funkcji


DECLARE @SaveError int;
SELECT 1/0;
SET @SaveError = @@ERROR;
IF @SaveError <> 0
PRINT @SaveError;
 Funkcji @@ERROR używamy głównie ze względu na
kompatybilność ze starszymi wersjami SQL Server np. 2000
Zarządzanie błędami 5
 Generacja błędów:
 Czasami chcemy / musimy wygenerować własne błędy (nie
przewidziane przez system)
 Posługujemy się wtedy procedurą składowaną sp_addmessage


Przykład:
EXEC sp_addmessage 50005, -- Message ID
10, -- Severity Level
‘ID bieżącej bazy: %d, nazwa bazy: %s.’;
 Instrukcja RAISEERROR wygeneruje odpowiedni błąd,
 Składnia:




RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
Zarządzanie błędami 6
 Przykład:
 DECLARE @DBID int;
DECLARE @DBNAME nvarchar(128);
SET @DBID = DB_ID();
SET @DBNAME = DB_NAME();
RAISERROR (50005,
10, -- Severity.
1, -- State.
@DBID, -- First substitution argument.
@DBNAME); -- Second substitution argument.
GO
 Można też generować błędy bez dodawania komunikatów do systemu

RAISERROR (‘Custom Message’,
10, -- Severity
1); -- State
 Oprócz dodawania komunikatów mamy też ich usuwanie:

sp_dropmessage
Procedury składowane (MSSQL)
 Procedury składowane są zbiorami operacji przechowywanymi na





serwerze i wykonywanymi przez klienta aplikacji.
Wartości parametrów mogą być przekazywane do procedury
przechowywanej jako wejścia.
Parametry wyjściowe mogą być używane do zwracania wartości
zmiennej do kodu wywołującego.
Procedura składowana może mieć s sumie do 2100 parametrów.
Pojedyncza wartość całkowita jest zazwyczaj używana do
wskazywania sukcesu lub porażki (wykonania procedury).
Istnieje wiele operacji, które mogą być wykonywane przez procedury
przechowywane w bazie danych:
 Zmiana struktury bazy danych i wykonywanie zdefiniowanych
przez użytkownika transakcje są powszechnymi operacjami
 Procedury składowane mogą być wykorzystane do zwracania
wyniku zapytania SELECT, ale istnieją lepsze narzędzia
Procedury składowane 2
 Omówimy budowanie procedur składowanych Transact-SQL, ale
możliwe jest również zbudowanie takich procedur przy użyciu .NET
(CLR)
 Zalety:



Bezpieczeństwo:
 Prawo do wykonania procedury składowanej jest przyznawane niezależnie
od dostępu do obiektów bazy danych których ona dotyczy.
 Użytkownik, który uzyskuje dostęp do wykonania procedury
składowanej może wykonywać wszystkie operacje w procedurze
przechowywanej. Możliwe jest również , że wykonujemy ją jako inny
użytkownik.
Modularne programowanie:
 Wielokrotne wykorzystywanie,
 Skomplikowane procedury mogą być rozbijane na bloki
Czas wykonania


Procedury są kompilowane raz (w zasadzie)
Czas przesyłania kodu do serwera
Procedury składowane 3
 Wiele operacji bazodanowych może być wykonanych przez inne
obiekty/struktury
 Procedury składowane mogą wykonać prawie wszystkie operacje.
Ale poniższe są zabronione:
 Tworzenie lub modyfikacja następujących obiektów:








Aggregate
Default
Function
Procedure
Rule
Schema
Trigger
View

Instrukcja USE

SET PARSEONLY lub warianty SHOWPLAN
Procedury składowane 4
 Procedura może zwrócić więcej niż jeden zbiór rezultatów do






wywołującej ją aplikacji.
Funkcje tablicowe definiowalne przez użytkownika są lepszym
rozwiązaniem jeśli ma być zwrócony jeden wynik.
Wyniki procedury nie mogą być używane w klauzuli FROM
kwerendy (istnieje funkcja OPENQUERY(), która pozwala na
obejście tego ograniczenia).
Procedury mogą korzystać z tablic tymczasowych.
Tablica tymczasowa istnieje tylko na czas działania procedury.
Procedura zagnieżdżona może korzystać z tablic tymczasowych
utworzonych przez procedurę wywołującą (nadrzędną).
Odwołując się do obiektów wewnątrz procedury zalecane jest
używanie nazwy schematu, unika się w ten sposób błędów
związanych z domyślnym przeszukiwaniem bazy przez procedurę.
Procedury składowane 5
 Składnia

CREATE PROC[EDURE] [schema_name.]proc_name
[({@param1} type1 [ VARYING] [= default1] [OUTPUT])] {, …}
[WITH {RECOMPILE | ENCRYPTION | EXECUTE AS 'user_name'}]
[FOR REPLICATION]
AS batch | EXTERNAL NAME method_name

schema_name – nazwa schematu do którego jest
przypisywana tworzona procedura.
proc_name – oczywista …
Parametr procedury składowanej ma taki sam sens logiczny jak
zmienna lokalna w batchu
@param1 – nazwa pierwszego parametru
type1 - typ pierwszego parametru
default1 – opcjonalna wartość domyślna (może być NULL)
OUTPUT – wskazuje, że parametr może zwrócić wartość z
procedury do systemu (wywołującej aplikacji)






Procedury składowane 6
 Prekompilowana postać procedury jest przechowywana na serwerze
 Opcja WITH RECOMPILE spowoduje, że procedura będzie
rekompilowana przed każdym użyciem.
 To niszczy jedną z ważnych zalet procedur.
 Klauzula EXECUTE AS określa kontekst bezpieczeństwa (jako kto)
wykonywania procedury. W ten sposób można kontrolować, którego
konta użyje baza danych do sprawdzenia uprawnień do obiektów, z
których korzysta procedura.
 Domyślnie tylko członkowie ról sysadmin, db_owner oraz
db_ddladmin mogą wykorzystywać instrukcję CREATE
PROCEDURE. Ale zgodnie z zasadami, członkowie tych ról mogą
przekazać te uprawnienia innym użytkownikom przy pomocy
polecenia GRANT CREATE PROCEDURE.
Procedury składowane 7
 Przykład
 USE sample;
GO
CREATE PROCEDURE increase_budget (@percent INT=5)
AS UPDATE project
SET budget = budget + budget*@percent/100;
 Można tworzyć procedury tymczasowe: lokalne (#nazwa_procedury)
i globalne (##nazwa_procedury).
 Stosują się do nich podobne zasady jak do tablic tymczasowych
 Procedury mają (w pewnym sensie) dwie fazy : faza tworzenia i faza
wykonania
 Polecenie EXECUTE wykonuje istniejąca procedurę (kto może
wykonywać daną procedurę?)
Procedury składowane 8
 Składnia

[[EXEC[UTE]] [@return_status =] {proc_name| @proc_name_var}
{[[@parameter1 =] value | [@parameter1=] @variable
[OUTPUT]] | DEFAULT}..
[WITH RECOMPILE]
 Wszystkie opcje poza @return_status mają analogiczne znaczenie
jak w instrukcji tworzenia procedury
 @return_status – przechowuje status wykonania procedury
 Przykład:

SELECT * FROM project;
EXEC increase_budget 7;
SELECT * FROM project;
GO
Procedury składowane 9
 Przykład z wykorzystaniem opcji OUTPUT


USE sample;
GO
CREATE PROCEDURE delete_emp @employee_no INT, @counter INT
OUTPUT
AS
SELECT @counter = COUNT(*)
FROM works_on
WHERE emp_no = @employee_no
DELETE FROM employee
WHERE emp_no = @employee_no
DELETE FROM works_on
WHERE emp_no = @employee_no
GO
-DECLARE @quantity INT – deklarcja
EXECUTE delete_emp @employee_no=28559, @counter=@quantity
OUTPUT
PRINT @quantity
GO
Procedury składowane 10
 Usuwanie procedury
DROP PROCEDURE nazwa_procedury
 Modyfikacja procedury składowanej
 ALTER PROCEDURE …
 Jest to praktycznie ta sama składnia co dla CREATE
PROCEDURE
 Po co skoro można DROP PRCEDURE oraz CREATE PROC ?
 Ale wtedy znikają zdefiniowane już uprawnienia

Procedury składowane 11
 Od wersji SQL Server 2008 można do procedury przekazywać
parametry o wartościach tabelarycznych (czyli tabele)
 Jest to jedno z lepszych rozszerzeń wprowadzonych do tej wersji
serwera
 Przykład:

W poniższym kodzie korzystamy z typu tablicowego OrderDetailsType,
który musiał być wcześniej zdefiniowany

CREATE
PROC OrderTransactionUpdateTVP (
@OrderID INT OUTPUT, @CustomerID INT,
@OrderDate DateTime, @Details as OrderDetailsType
READONLY )
AS
SET NoCount ON ;
Begin Try
Begin Transaction;
-- Jeśli @OrderID jest NULL to mamy nowe
-- zamówienie a wiêc dodajemy do tabeli ORDER
Procedury składowane 11
If @OrderID IS NULL
BEGIN;
Insert Orders(OrderDate, CustomerID)
Values (@OrderDate, @CustomerID);
-- Get OrderID value from insert
SET @OrderID = Scope_Identity();
END;
-- poniższa instrukcja tylko wyświetla zawartość tabeli
ale można z nią zrobić dużo więcej...
SELECT * FROM @Details ;
Commit Transaction;
End Try
Begin Catch;
RollBack;
End Catch
RETURN;
GO
 Teraz wykorzystamy tą procedurę
Declare @OrderID INT;
DECLARE @DetailsTVP as OrderDetailsType;
INSERT @DetailsTVP
(LineNumber,ProductID,IsNew,IsDirty,IsDeleted)
VALUES
(5, 101, -1, -1, 0),
(2, 999, 0, -1, 0),
(3, null, 0, 0, 0);
exec OrderTransactionUpdateTVP
@OrderID = @OrderID Output ,
@CustomerID = '78',
@OrderDate = '2008/07/24',
@Details = @DetailsTVP;
 Procedura powinna wypisać wartości z tabeli @DetailsTVP
Funkcje definiowalne (UDF)
 UDF mogą zawierać skomplikowaną logikę T-SQL w kwerendzie i
rozwiązywać problemy, które były niemożliwe do rozwiazania lub
wymagały użycia kursorów.
 Dzisiaj stają się jednym z podstawowych narzędzi programisty baz
danych.
 Zalety:
 Por. pierwsze zdanie slajdu „I’ve solved several nasty problems
using user-defined functions” Paul Nilsen .
 Mogą być użyte do budowania nowych funkcji dla
skomplikowanych wyrażeń
 Oferuje podobne zalety jak widoki, gdyż mogą być użyte w
klauzuli FROM. Ponadto pozwalają na użycie parametrów,
czego nie maja widoki.
 Oferują zalety procedur składowanych, gdyż są kompilowane i
optymalizowane w ten sam sposób
UDF 2
 Głównym argumentem przeciwko korzystaniu z UDF może być
zmniejszenie wydajności, przy niewłaściwym ich użyciu
 Jakakolwiek funkcja, która ma być użytq w każdym wierszu w
warunku WHERE na pewno pogorszy (i to chyba znacznie)
wydajność.
 Trzy typy UDF
 Funkcje skalarne zwracające pojedynczą wartość
 Funkcje „Inline” o wartościach tabelarycznych. Podobne do
widoków
 Wielo–liniowe funkcje o wartościach tabelarycznych, tworzące
zbiór wyników przy pomocy kodu
UDF 3
 Funkcje skalarne

Wartość jest zwracana przez polecenie RETURN
Muszą być deterministyczne – dla tych samych parametrów
zwracać tą samą wartość (nie można więc korzystać z
newid(), rand())

Nie mogą modyfikować bazy

Nie mogą zwracać wartości typu blob, text, ntext, timestamp,
image ani wartości typu tabelarycznego czy typu kursora.

Nie mogą zawierać TRY. . .CATCH ani RAISERROR.
Mogą wywoływać inne UDFy lub też same siebie(aż do 32
poziomu zagnieżdżenia).


UDF 4
 Szablon funkcji skalarnej:

CREATE FUNCTION FunctionName (InputParameters)
RETURNS DataType
AS
BEGIN;
Code;
RETURN Expression;
END;
Parametry wejścia muszą określać również typ, może być
podana wartość domyślna
 Przykład:


CREATE FUNCTION dbo.fsMultiply (@A INT, @B INT = 3)
RETURNS INT
AS
BEGIN;
RETURN @A * @B;
END;
go
SELECT dbo.fsMultiply (3,4),
dbo.fsMultiply (7, DEFAULT);
UDF 5
 Funkcje typu inline
 Składnia

CREATE FUNCTION FunctionName (InputParameters)
RETURNS Table
AS
RETURN (Select Statement);

Pełnią rolę podobną do widoków ale mogą mieć parametry
Przykład:
 USE OrderProcessingSystem;
GO
CREATE FUNCTION ppinline1(@custcode as int)
RETURNS TABLE
AS
RETURN(
SELECT * FROM orders o JOIN products p ON
o.product=p.code
WHERE CustomerAccount=@custcode);
GO
SELECT * FROM ppinline1(4504);

UDF 6
 Wieloliniowe funkcje tabelaryczne
 Składania:

CREATE FUNCTION FunctionName (InputParamenters)
RETURNS @TableName TABLE (Columns)
AS
BEGIN;
-- kod, który wypełni tabelę
RETURN;
END;
UDF 7

Przykład:
 CREATE FUNCTION ppmulti1()
RETURNS @pp1 TABLE (
ca int,
kwota money)
AS
BEGIN
INSERT @pp1 SELECT CustomerAccount, sum(StoreSales)
FROM orders group by CustomerAccount;
-- tutaj moze być wiele instrukcji SQLa;
RETURN
END;
GO

SELECT * FROM ppmulti1()
UDF 8
 Skorelowanie UDF

CREATE FUNCTION ppmulti2( @AC as int)
RETURNS @pp1 TABLE (
AccountNumber int,
kwota money)
AS
BEGIN
IF @AC IS NULL
INSERT @pp1 SELECT CustomerAccount, sum(StoreSales)
FROM orders group by CustomerAccount;
ELSE
INSERT @pp1 SELECT CustomerAccount, sum(StoreSales)
FROM orders
WHERE CustomerAccount=@AC
GROUP BY CustomerAccount;
RETURN
END;
GO
UDF 8

SELECT * FROM ppmulti2(5224) ORDER BY AccountNumber DESC

SELECT c.AccountNumber, Firstname, LastName, kwota
FROM Customers C CROSS APPLY ppmulti2(C.AccountNumber);

 Wiązanie schematu (schema binding!)
 Jeśli w definicji funkcji użyjemy opcji
WITH SCHEMA BINDING
 To nie będziemy mogli usuwać tabel do których odnosi się dana
funkcja, a nawet nie będzie można modyfikować tabel, a
przynajmniej tej części ich struktury do której odnosi się funkcja.

Wyzwalacze (triggers)
 Wyzwalacz trigger jest poleceniem, które jest wykonywane
automatycznie jako dodatkowy skutek modyfikacji bazy danych.
 Aby zaprojektować wyzwalacz musimy:
 określić „czas” i „warunki” w jakich wyzwalacz ma zostać
aktywowany
 określić działania wykonywane przez ten wyzwalacz.
 Wyzwalacze wprowadzono do standardu dopiero w SQL-1999, ale
w wielu implementacjach istniały już znacznie wcześniej.
Wyzwalacze 2
 Ogólna postać:

CREATE TRIGGER <nazwa>
BEFORE | AFTER | INSTEAD OF <zdarzenia>
<klauzula referencyjna> // optional
FOR EACH ROW // optional
WHEN (<warunek>) // optional
<akcja>
gdzie <zdarzenia> mogą być:
INSERT ON R
DELETE ON R
UPDATE [OF A1, A2, ..., An] ON R
AFTER <zdarzenia> są najbardziej użyteczne i powszechne.
Pozostałe generują problemy i nie zaleca się ich używania
(Widom)
Wyzwalacze (triggers)
 <warunek>: jak zwykle
 <działanie>: sekwencja poleceń SQL
 FOR EACH ROW (/ FOR EACH STATEMENT)




Jeśli obecne wykonuje wyzwalacz raz dla każdej zmienianej
krotki.
Jeśli nie ma to wykonuje dla każdej instrukcji (for each
statement)
Terminologia: "row-level" kontra "statement-level"
W każdym przypadku wyzwalacz wykonuje się po tym jak
polecenie się wykona (after statement completes).
Wyzwalacze (triggers)
 <klauzula referencyjna>:
REFERENCING <obiekt1> AS <var1> <obiekt2> AS <var2>, itd.
<obiekt> może być:
 OLD TABLE – poprzednimi wartościami usuniętych lub uaktualnionych
krotek, poziom wierszy lub poziom poleceń, DELETE lub UPDATE
 NEW TABLE - poprzednimi wartościami usuniętych lub uaktualnionych
krotek, poziom wierzy lub poziom poleceń, INSERT lub UPDATE
 OLD ROW – poprzednia wartość usunietej lub uaktualnionej krotki, tylko
poziom wierszy, DELETE lub UPDATE
 NEW ROW - poprzednia wartość watawionej lub uaktualnionej krotki,
tylko poziom wierszy, INSERT lub UPDATE
Wyzwalacze (przykłady)
 Jeśli wstawiana jest krotka do tabeli Aplikacja dla kandydata z oceną >3.9 i
IQ>150 do UMK, ustaw decyzję na tak.




CREATE TRIGGER AutoAccept
AFTER INSERT ON Aplikacja
REFERENCING NEW ROW AS NewApp
FOR EACH ROW WHEN
(
NewApp.miejsce = ‘UMK' AND
3.9 < (SELECT ocena FROM Student WHERE ID = NewApp.ID) AND
150 < (SELECT IQ FROM Student WHERE ID = NewApp.ID)
)
UPDATE Aplikacja
SET decyzja = ‘T'
WHERE ID = NewApp.ID
AND miejsce = NewApp.miejsce
AND data = NewApp.data
Wyzwalacze (przykłady)
 To samo ale bez FOR EACH ROW.
 CREATE TRIGGER AutoAccept
 AFTER INSERT ON Aplikacja
 REFERENCING NEW TABLE AS NewApps
 UPDATE Aplikacja
SET decyzja = ‘T'
WHERE ((ID,miejsce,data) IN (SELECT ID,miejsce,data FROM NewApps)
and NewApp.location = ‘UMK'
AND 3.9 < (SELECT ocena FROM Student WHERE ID = NewApp.ID)
AND 150 < (SELECT IQ FROM Student WHERE ID = NewApp.ID))
Inny przykład wyzwalacza
 Przypuśćmy, że zamiast pozwalać na ujemne stany na rachunkach
bank prowadzi następującą politykę
 ustala stan rachunku na zero
 otwiera kredyt z kwotą równą debetowi
 nadaje kredytowi numer taki sam jak numer rachunku na którym
zrobiono debet
 Warunkiem wykonania wyzwalacza jest zmiana relacji rachunek
powodująca, że stan przyjmuje wartość ujemną.
Przykład wyzwalacza SQL:1999
create trigger debet_trigger after update on rachunek
referencing new row as nrow
for each row
when (nrow.stan < 0)
begin atomic
insert into kredytobiorca
(select klient_numer, rachunek_numer
from depozytor
where nrow.rachunek_numer =
depozytor.rachunek_numer);
insert into kredyt values
(nrow.rachunek_numer, nrow.oddzial_nazwa, -nrow.stan);
update rachunek set stan = 0
where rachunek.rachunek_numer = nrow.rachunek_numer;
end
Wyzwalacze: zdarzenia i akcje w SQL
 Zdarzenie wyzwalającym może być insert, delete lub update
 Wyzwalacze przy zmianie krotki mogą być ograniczone do
określonych atrybutów (stan)
 Np. create trigger debet_trigger after update of stan on
rachunek
 Można dowoływać się zarówno do wartości przed jak i po
modyfikacji
 referencing old row as : w przypadki usuwania i zmiany
 referencing new row as : w przypadku wstawiania i zmiany
 Wyzwalacze mogą być aktywowane przed i po zdarzeniu co może
służyć jako dodatkowe więzy. Np. zmienić spacje na null.
create trigger setnull_trigger before update on r
referencing new row as nrow
for each row
when nrow.telefon_numer = ‘ ‘
set nrow.telefon_numer = null
Różne poziomy „wyzwalania”
 Zamiast wykonywać osobne działanie dla każdego wiersza można
wykonać pojedyncze działanie dla wszystkich wierszy
podlegających tej transakcji
 Używamy
for each statement zamiast for each row
 Używamy
referencing old table albo referencing new
table aby odwoływać się do tymczasowych tabel (transition
tables) zawierających zmodyfikowane wiersze
 Warto stosować w sytuacjach, gdy mamy zmienić dużą liczbę
wierszy
Działania zewnętrzne
 Czasami chcemy aby wyzwalacze były aktywowane z zewnątrz
Np. wykonanie zamówienia produktu , którego ilość w hurtowni
znacznie zmalała, włączenie się alarmu,
 Wyzwalacze nie mogą być wykorzystane do bezpośredniej implementacji
działania świata zewnętrznego, ale!
 Wyzwalacze mogą być wykorzystane do zapisania w osobnej tabeli
działań, które mają być podjęte
 Możemy posiadać proces, który w sposób ciągły analizuję tabelę,
przeprowadza działanie zapisane w tabeli i następnie usuwa działanie
z tabeli
 Np. Złóżmy, że hurtownia posiada następujące tabele
 zapasy(produkt, poziom): Ile tego mamy w hurtowni
 minpoziom(produkt, poziom) : Jaki jest poziom mimalny produktu
 ponow_zam(produkt, liczba): Ile powinniśmy zamówić jednorazowo
 zamowienia(produkt, liczba) : Zamówienia do wykonania wykonuje je
proces zewnętrzny w stosunku do bazy danych

Działania zewnętrzne (cd.)
create trigger zamow_trigger after update of liczba on zapasy
referencing old row as orow, new row as nrow
for each row
when nrow.poziom < = (select poziom
from minpoziom
where minpoziom.produkt = orow.produkt)
and orow.poziom > (select poziom
from minpoziom
where minpoziom.produkt = orow.produkt)
begin
insert into zamowienia
(select produkt, liczba
from ponow_zam
where ponow_zam.produkt = orow.produkt)
end
Wyzwalacze w MS-SQL
 CREATE TRIGGER [schema_name.]trigger_name
ON {table_name | view_name}
[WITH dml_trigger_option [,…]]
{FOR | AFTER | INSTEAD OF} { [INSERT] [,] [UPDATE] [,] [DELETE]}
[WITH APPEND]
{AS
sql_statement | EXTERNAL NAME method_name
}
 Klauzula WITH <trigger_option> może zawierać dwie różne opcje:
 WITH ENCRYPTION wskazuje, że kod T-SQL wyzwalacza powinien
być ukryty lub zakodowany
 WITH EXECUTE AS `nazwa użytkownika”
Wyzwalacze w MS-SQL
create trigger debet_trigger on rachunek
for update
as
if inserted.stan < 0
begin
insert into kredytobiorca
(select klient_numer,rachunek_numer
from depozytor, inserted
where inserted.rachunek_numer =
depozytor.rachunek_numer)
insert into kredyt values
(inserted.rachunek_numer, inserted.oddzial_nazwa,
– inserted.stan)
update rachunek set stan = 0
from rachunek, inserted
where rachunek.rachunek_numer = inserted.rachunek_numer
end




Nie ma before, zamiast after użyto on update
Slowo kluczowe AS rozpoczyna opis działania
Zamiast warunku when użyto if
inserted, deleted zamiast referencing new/old table
Kiedy nie należy używać wyzwalaczy
 Dawniej wyzwalaczy używano do
obsługi danych podsumowujących (np. całkowita pensja w
każdym dziale)
 Replikacji bazy danych poprzez zapisywanie zmian do
specjalnych relacji (change lub delta) i stosowanie osobnego
procesu zewnętrznego do zastosowania tych zmian na kopii
bazy danych.
 Dzisiaj robimy to lepiej:
 dane podsumowujące obsługujemy poprzez tzw. widoki
zmaterializowane
 Bazy danych posiadają wbudowane mechanizmy replikacji
 Zamiast wyzwalaczy stosuje się „enkapsulację” (pojęcie z języków
obiektowych)
 Definiuje się metody zmieniające dane
 Działania przeprowadza się jako część tych metod
 Wyzwalacz może wywołać kolejny wyzwalacz (zapętlenie!)

Wyzwalacze – Zalety
 Wyzwalcze dostarczają alternatywnego sposobu sprawdzania
spójności
 Wyzwalacz może wychwycić błędy w logice biznesowej na poziomie
bazy danych.
 Wyzwalacz dostarcza alternatywnego sposobu wykonania zadania
w kolejce. Nie trzeba czekać na kolejkę zadań aby wykonać
zadanie. Można je wykonać przed lub po zmianach w tabelach
bazy danych.
 Wyzwalacz jest bardzo przydatny, gdy używamy go do sprawdzanie
zmian w tabelach bazy danych.
Wyzwalacze - Wady
 Wyzwalacz może dostarczyć tylko rozszerzonej walidacji, nie może
zastąpić innych walidacji. Niektóre proste walidacje mogą być
wykonywane na poziomie aplikacji. Na przykład, mona sprawdzić
dane wprowadzane po stronie klienta przy pomocy javascriptlub po
stronie serwera przy pomocy PHP lub ASP.NET.
 Wyzwalacze wykonują się „niewidzialnie” dla klienta, który łączy się
z serwerem bazy danych, tym samym trudno jest wywnioskować co
się zdarzyło po stronie serwera.
 Wyzwalacze wykonują się przy każdej modyfikacji tabeli, zwiększa
obciążenie bazy danych i spowalnia system.
 Wyzwalacze czy procedury składowane? Zależnie od sytuacji, ale
zasadą może być, że jeśli nie mona czegoś zrobić przy pomocy
procedury składowanej, powinniśmy użyć wyzwalacza.
Procedury składowane
Bezpieczeństwo
 Bezpieczeństwo – zabezpieczenie przed próbami kradzieży lub
modyfikacji danych.
 Poziom bazy danych



Poziom systemu operacyjnego


Mechanizmy autoryzacji i autentykacji, które pozwalają
określonym użytkownikom na dostęp tylko do odpowiednich
danych
Omawiamy głównie autoryzację
Tzw. su systemu operacyjnego mogą zrobić prawie wszystko z
bazą danych! Dlatego wymagane są doskonałe zabezpieczenia
na tym poziomie
Poziom sieci – musimy używać szyfrowania aby zapobiec:


podsłuchowi (nieautoryzowanemu czytaniu komunikatów)
maskaradzie (udawaniu użytkownika autoryzowanego,
przykłady)
Bezpieczeństwo (cd.)

Poziom fizyczny


Fizyczny dostęp do komputerów (kłódka i klucz)
Klęski żywiołowe (wichury, powodzie, pożary, ...)
 Odzyskiwanie danych !

Poziom ludzki


Czy użytkownicy nie „sprzedają” dostępu do danych
Muszą znać podstawowe zasady wyboru bezpiecznych haseł
(nie może to być imię żony i liczba dzieci ani żadne obsceniczne
słowo – Seksmisja!!!)
Autoryzacja
Zakresy autoryzacji na częściach bazy danych (por. MySQL) :
 Read – może przeglądać dane.
 Insert – może wstawiać nowe dane ale nie może zmieniać
istniejących.
 Update – może zmieniać ale nie może usuwać.
 Delete – może usuwać.
Zakresy autoryzacji dla schematów bazy danych
 Index – tworzy i usuwa indeksy.
 Resources – może tworzyć nowe relacje.
 Alteration – może modyfikować schematy relacji (zmieniać
atrybuty).
 Drop – może usuwać relacje.
Nadawanie uprawnień
 Przekazywanie uprawnień od jednego użytkownika do




innego może być reprezentowane przy pomocy grafu .
Węzły tego grafu przedstawiają użytkowników.
Wierzchołkiem grafu jest zawsze administrator bazy
(DBA).
Graf dla nadawania uprawnień update na tabeli kredyt.
Linia Ui  Uj , mówi, że użytkownik Ui nadał uprawnienia
update na loan użytkownikowi Uj.
Graf nadawania uprawnień
 Wymagania: Wszystkie krawędzie grafu muszą być częścią
ścieżki mającej początek na DBA
 Jeśli DBA odwoła uprawnienia użytkownikowi U1:
 Uprawnienia muszą być odebrane U4 gdyż U1 nie ma już
uprawnień
 Uprawnienia nie mogą być odebrane U5 gdyż posiada on
również uprawnienia nadane przez U2
 Nie może być ścieżek, które nie mają połączenia z DBA:
 DBA nadaje uprawnienia U7
 U7 nadaje uprawnienia U8
 U8 nadaje uprawnienia U7
 DBA usuwa uprawnienia U7
 Uprawnienia U7 dla U8 oraz U8 dla U7 muszą zostać usunięte bo
nie ma już ścieżki od DBA ani do U7 ani do U8.
Określanie autoryzacji w SQL
 Polecenie grant jest używane do przekazania (nadania)
uprawnień
grant <lista uprawnień>
on <nazwa relacji lub widoku> to <lista użytkowników>
 < lista użytkowników > ma postać:
 identyfikator użytkownika
 public, co nadaje uprawnienia wszystkim użytkownikom
 rola (o rolach później)
 Nadanie uprawnień do widoku nie implikuje uprawnień do
relacji, na których widok jest zbudowany.
 Nadający uprawnienia musi posiadać nadawane uprawnienia
lub być administratorem bazy danych.
Uprawnienia w SQL
 select: pozwala na odczyt danych z relacji, jak również na




wykonywanie kwerend z wykorzystaniem widoków
 Przykład: nadaj użytkownikom U1, U2, oraz U3 uprawnienia
select do relacji oddzial:
grant select on oddzial to U1, U2, U3
insert: uprawnienia do wstawiania krotek
update: uprawnienia do zmiany wartości atrybutów przy pomocy
polecenia SQL update
delete: uprawnienia do usuwania krotek w relacji
all privileges: wszystkie dopuszczalne uprawnienia
Uprawnienie do nadawania uprawnień
 with grant option: pozwala aby użytkownik posiadający
uprawnienia mógł przekazywać te uprawnienia innym
użytkownikom.
 Przykład:
grant select on oddzial to U1 with grant option
Odbieranie uprawnień w SQL
 Polecenie revoke odbiera uprawnienia.





revoke <lista uprawnień>
on <nazwa relacji lub widoku> from <lista użytkowników>
Przykład:
revoke select on oddzial from U1, U2, U3
<lista przywilejów> może składać się z jednego słowa all. W
takim przypadku odbieramy wszystkie przywileje.
Jeśli <lista użytkowników> zawiera public, wszyscy
użytkownicy, którym nie nadano tego przywileju indywidualnie
tracą uprawnienia.
Można zachować uprawnienia po ich odwołaniu jeżeli nadało je
dwóch różnych użytkowników a tylko jeden je odwołał.
Wszystkie przywileje, które zależą od odbieranego są również
odbierane.
Ograniczenia autoryzacji w SQL
 SQL nie pozwala na autoryzację na poziomie krotek
Np. nie można spowodować aby student widział tylko krotki
odpowiadające swoim danym (to robimy przy pomocy
widoków)
 Wraz z rozwojem sieciowych baz danych pojawiają się nowe
problemy, gdyż większość użytkowników takich baz posiada
jeden (ten sam) identyfikator
 Zadanie autoryzacji w powyższych przykładach przenosi się na
programy aplikacyjne, tzn. poza SQL.
 Zaleta: Szczegółowe rozróżnienie autoryzacji, takie jak dostęp
do indywidualnych krotek może być implementowane na
poziomie aplikacji zewnętrznej
 Wada: autoryzacja spada na programistę, łatwo o błędy np.
dziury w systemie zabezpieczeń

Historia zmian
 Historia zmian – zapis wszystkich zmian (insert/delet/update) na
bazie danych razem z informacją kto, kiedy i gdzie ( z jakiego IP)
je wykonał.
 Korzysta się z niej aby wyśledzić odpowiedzialnych za
wprowadzenie błędnych danych.
 Można je zaimplementować przy pomocy wyzwalaczy ale wiele
baz danych posiada wbudowane odpowiednie narzędzia.
Role
 Role pozwalają na definiowanie zestawu przywilejów dla
grupy użytkowników poprzez tworzenie odpowiednich
“ról”
 Przywileje można nadawać i odpierać rolom tak samo jak
użytkownikom
 Role mogą być przypisane użytkownikom a także innym
rolom
 SQL:1999 zawiera pojęcie ról
create role urzednik
create role menadzer
grant select on oddzial to urzednik
grant update (stan) on rachunek to urzednik
grant all privileges on rachunek to menadzer
grant urzednik to mendzer
grant urzednik to alicja, bolek
grant menadzer to czeslaw
„Zanurzony” (Embedded) SQL
 Standard SQL definiuje zanurzenie SQLa w szeregu
standardowych językach programowania takich jak C, Java czy
Cobol.
 Język do którego wprowadza się polecenia SQL nazywamy
językiem gospodarza (host language), a struktury języka
udostępnione w ten sposób nazywamy zanurzeniem SQL
(embedded SQL)
 Polecenie EXEC SQL używane jest do identyfikowania
zanurzonego SQLa przez tzw. preprocesor
EXEC SQL <polecenie zanurzonego SQL > END_EXEC
Ale w niektórych językach może być nieco inaczej np w Javie:
# SQL { …. };
Przykład
 Z poziomu języka gospodarza znajdź nazwy i miasta klientów z
kwotą większą niż zmienna suma na jakimkolwiek rachunku.
 Określamy kwerendę SQL i deklarujemy dla niej cursor
EXEC SQL
declare c cursor for
select depozytor.klient_nazwisko, klient_miasto
from depozytor, klient, rachunek
where depozytor.klient_nazwisko = klient.klient_nazwisko
and depozytor rachunek_numer =
rachunek.rachunek_numer
and rachunek.stan > :suma
END_EXEC
Zanurzony SQL (Cd.)
 Polecenie open powoduje wykonanie kwerendy
EXEC SQL open c END_EXEC
 Polecenie fetch powoduje, że wartości pojedynczej krotki zostają
umieszczone w zmiennych języka gospodarza.
EXEC SQL fetch c into :cn, :cc END_EXEC
Powtarzanie tej komendy „wyciąga” kolejne krotki z wyniku kwerendy
 W ramach języka istnieje tzw. obszar komunikacyjny SQLCA i
zmienne, które przyjmują odpowiednie wartości przy określonych
zdarzeniach, np. zmienna SQLSTATE przyjmuje wartość ‘02000’ jeśli
w wyniku kwerendy nie ma już dalszych krotek.
 Polecenie close powoduje zamknięcie przez bazę danych
tymczasowej relacji przechowującej wynik kwerendy.
EXEC SQL close c END_EXEC
W Javie jest trochę inaczej (bardziej naturalnie)
Modyfikacje poprzez kursor
 Możemy zmodyfikować bazę z poziomu zanurzonego SQL. Jeżeli
chcemy do każdego rachunku w oddziale Toruń dodać 100 zł to
najpierw wykonujemy polecenie:
declare c cursor for
select *
from rachunek
where oddzial_nazwa = ‘Toruń’
for update
 A potem pobieramy kolejne rekordy (fetch) i po każdym pobraniu
wykonujemy polecenie
update rachunek
set stan = stan + 100
where current of c
ODBC i JDBC
 API (application-program interface) dla programów komunikujących
się z serwerami baz danych
 Aplikacje odwołują się do funkcji API aby
 Połączyć się z bazą danych
 Wysłać polecenia SQL do serwera baz danych
 Pobrać krotki wyniku (jedna po drugiej i zapisać je w zmiennych
programu)
 ODBC (Open Database Connectivity) działa z językami C, C++, C#,
raz Visual Basic
 JDBC (Java Database Connectivity) współpracuje Javą
ODBC
 Standard Open DataBase Connectivity (ODBC)


jest standardem dla aplikacji do komunikowanie się z
serwerem bazy danych
po to aby



otworzyć połączenie z bazą,
wykonywać zapytania i modyfikować bazę danych,
pobierać wyniki zapytań.
 Aplikacje takie jak GUI, arkusze kalkulacyjne, ... mogą korzystać
z ODBC
ODBC (cd.)
 Każdy system bazodanowy „współpracujący z” ODBC dostarcza
sterownika (biblioteki) która musi być „linkowana” z programem
klienckim.
 Kiedy program klienta wywołuje ODBC API, kod w bibliotece
komunikuje się z serwerem aby wykonać żądaną operację i
zwrócić jej wynik.
 Połączenie z bazą danych otwiera SQLConnect(). Parametrami
są :




uchwyt połączenia,
serwer z którym chcemy się połączyć
id użytkownika,
hasło
Przykład kodu ODBC
 int ODBCexample()
{
RETCODE error;
HENV
env;
/* environment */
HDBC
conn; /* database connection */
SQLAllocEnv(&env);
SQLAllocConnect(env, &conn);
SQLConnect(conn, „ferm.fizyka.umk.pl", SQL_NTS, „pp",
SQL_NTS, „pppasswd", SQL_NTS);
{ …. zrób coś w bazie danych … }
SQLDisconnect(conn);
SQLFreeConnect(conn);
SQLFreeEnv(env);
}
Funkcje i procedury
 SQL:1999 wprowadza funkcje i procedury
Funkcje/procedury mogą być pisane w SQL lub zewnętrznym
języku programowania
 Niektóre systemy bazodanowe dostarczają funkcji o
wartościach typu tabelarycznego (wynikiem funkcji jest
relacja)
 SQL:1999 dostarcza podstawowego zestawu poleceń
typowych dla zwykłych języków programowania
 pętle, konstrukcja if-then-else, przypisania
 Wiele systemów baz danych posiada własne rozwiązania
niezgodne z tym standardem
