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, … Amtworzą 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 CONSTRAINTCK_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 cascadeon 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 Wstawia w krotce podrzędnej wartości puste

on delete set default 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 )))

PROGRAMOWANIESkarb 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;GOCREATE TABLE T1 (C1 int NOT NULL);INSERT INTO T1 VALUES (1);INSERT INTO T1 VALUES (2,2);INSERT INTO T1 VALUES (3);GOSELECT * 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 = CustomerNameFROM 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;

GOUPDATE HumanResources.EmployeePayHistory SET PayFrequency = 4 WHERE EmployeeID = 1;

IF @@ERROR <> 0 -- funkcja sysytemowa BEGIN

PRINT ‘An error occured in the previous statement.’; RETURN; ENDELSE

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 = 1END

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;

GOSELECT 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 AbbreviationFROM AdventureWorks2008.HumanResources.DepartmentWHERE 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 TRYBEGIN CATCH

-- Logika obsługi błędówEND 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 TRYSELECT 1/0;

END TRYBEGIN 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 <> 0PRINT @@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 <> 0PRINT @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, -- Severity1); -- 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;

GOCREATE 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_onWHERE emp_no = @employee_noDELETE FROM employeeWHERE emp_no = @employee_noDELETE FROM works_onWHERE emp_no = @employee_no

GO--DECLARE @quantity INT – deklarcja EXECUTE delete_emp @employee_no=28559, @counter=@quantity

OUTPUTPRINT @quantityGO

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

CREATEPROC OrderTransactionUpdateTVP (

@OrderID INT OUTPUT, @CustomerID INT,@OrderDate DateTime, @Details as OrderDetailsType READONLY )

ASSET 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 11If @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 TryBegin Catch;

RollBack;End CatchRETURN;

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 DataTypeAS

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 INTASBEGIN;RETURN @A * @B;END;goSELECT dbo.fsMultiply (3,4),dbo.fsMultiply (7, DEFAULT);

UDF 5 Funkcje typu inline Składnia

CREATE FUNCTION FunctionName (InputParameters)RETURNS TableASRETURN (Select Statement);

Pełnią rolę podobną do widoków ale mogą mieć parametry Przykład:

USE OrderProcessingSystem;GOCREATE FUNCTION ppinline1(@custcode as int)

RETURNS TABLEAS

RETURN(SELECT * FROM orders o JOIN

products p ON o.product=p.codeWHERE CustomerAccount=@custcode);

GO

SELECT * FROM ppinline1(4504);

UDF 6 Wieloliniowe funkcje tabelaryczne Składania:

CREATE FUNCTION FunctionName (InputParamenters)RETURNS @TableName TABLE (Columns)ASBEGIN; -- kod, który wypełni tabelęRETURN;END;

UDF 7 Przykład:

CREATE FUNCTION ppmulti1()RETURNS @pp1 TABLE (

ca int,kwota money)

ASBEGIN 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)

ASBEGIN 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;

RETURNEND;

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 rreferencing new row as nrowfor 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 zapasyreferencing old row as orow, new row as nrowfor each row when nrow.poziom < = (select poziom

from minpoziomwhere minpoziom.produkt = orow.produkt)

and orow.poziom > (select poziomfrom 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 updateas if inserted.stan < 0begin 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_numerend

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

Mechanizmy autoryzacji i autentykacji, które pozwalają określonym użytkownikom na dostęp tylko do odpowiednich danych

Omawiamy głównie autoryzację Poziom systemu operacyjnego

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 urzednikgrant update (stan) on rachunek to urzednikgrant all privileges on rachunek to menadzer

grant urzednik to mendzer

grant urzednik to alicja, bolekgrant 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

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

Z poziomu języka gospodarza znajdź nazwy i miasta klientów z kwotą większą niż zmienna suma na jakimkolwiek rachunku.

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_EXECPowtarzanie 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