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
Download ReportTranscript 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
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 danych – obiekt jest kolekcją binarnych danych, których interpretacji dokonuje aplikacja poza systemem bazy
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
(
A
1 ,
A
2 , …,
A
m ) Specyfikacja unique stwierdza, że atrybuty
A
1,
A
2, …
A
m 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
CK_wiek_18 wyborca
CHECK ADD CONSTRAINT
(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 główny.
primary key wymienia atrybuty tworzące klucz Klauzula unique [key] kandydujący.
wymienia atrybuty tworzące klucz 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 klient_miasto
primary key
(
char char
(30), (30),
klient_nazwisko
)) create table oddzial (
oddzial_nazwa oddzial_miasto aktywa
primary key char
(15)
, char
(30),
numeric
(12,2), (
oddzial_nazwa
))
Więzy spójności w SQL – przykład (cd.)
create table rachunek (
rachunek_numer oddzial_nazwa stan
primary key char
(10)
, char
(15),
integer
, (
rachunek_numer),
foreign key
(
oddzial_nazwa
) references oddzial ) create table depozytor (
klient_nazwisko rachunek_numer
primary key
(
char
(20)
, char
(10)
,
klient_nazwisko, rachunek_numer),
foreign key
(
rachunek_numer
)
foreign key
(
klient_nazwisko
) references rachunek, 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
oddziału w relacji
oddział
spowoduje, że jeśli usuwanie jakiegoś 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.)
Alternaty wą 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
warunek
not null
nie może dotyczyć sytuacji gdy pole w tabeli ma (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
check
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.
zmiennymi globalnymi @@Error ) nazywane czasami (błędnie )
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 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 ?
DROP PROCEDURE nazwa_procedury 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, @OrderDate DateTime, READONLY ) @CustomerID INT, @Details as OrderDetailsType 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
GO 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;
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 F
U
NCTION
FunctionName (InputParameters)
RET
U
RNS
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,
DEFA
U
LT
);
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
Wyzwalacze (triggers)
Wyzwalacze (triggers)
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
table referencing old table
albo
referencing new
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 Np.
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 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
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
end
from rachunek ,
inserted
where rachunek.rachunek_numer =
inserted
.
rachunek_numer
Nie ma before , zamiast after Slowo kluczowe AS użyto on update 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 ( bazy danych.
change
lub
delta
) i stosowanie osobnego procesu zewnętrznego do zastosowania tych zmian na kopii 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
Linia
U i update
na
U
j , mówi, że użytkownik
loan
użytkownikowi
U j
.
U
i na tabeli
kredyt
.
nadał uprawnienia
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 Uprawnienia muszą być odebrane uprawnień
U
4 gdyż
U
1 :
U
1 nie ma już Uprawnienia nie mogą być odebrane również uprawnienia nadane przez
U
2
U
5 gdyż posiada on Nie może być ścieżek, które nie mają połączenia z DBA: DBA nadaje uprawnienia
U
7
U
7 nadaje uprawnienia
U
8
U
8 nadaje uprawnienia
U
7 DBA usuwa uprawnienia
U
7 Uprawnienia
U
7 dla
U
8 oraz
U
8 dla
U
7 nie ma już ścieżki od DBA ani do
U
7 muszą zostać usunięte bo ani do
U
8 .
Określanie autoryzacji w SQL
Polecenie
grant
uprawnień jest używane do przekazania (nadania)
grant
< lista uprawnień>
on
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
U
1 ,
U
2 , oraz
select
do relacji
oddzial
:
U
3 uprawnienia grant select on oddzial to U 1
, U
2
, U
3
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
użytkownikom. : pozwala aby użytkownik posiadający uprawnienia mógł przekazywać te uprawnienia innym Przykład: grant select on oddzial to U
1
with grant option
Odbieranie uprawnień w SQL
Polecenie
revoke
odbiera uprawnienia.
revoke
< lista uprawnień>
on
from
< lista użytkowników> Przykład: revoke select on oddzial from U
1 , U 2 , U 3
< lista przywilejów> może składać się z jednego słowa takim przypadku odbieramy wszystkie przywileje.
all.
W Jeśli
public,
wszyscy użytkownicy, którym nie nadano tego przywileju indywidualnie 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 je wykonał.
– zapis wszystkich zmian (insert/delet/update) na bazie danych razem z informacją kto, kiedy i gdzie ( z jakiego IP) 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 “ról” pozwalają na definiowanie zestawu przywilejów dla grupy użytkowników poprzez tworzenie odpowiednich Przywileje można nadawać i odpierać rolom tak samo jak użytkownikom Role rolom mogą być przypisane użytkownikom a także innym 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
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
for update
= ‘Toruń’ 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