Wyk ad 5 zaawansowany sql
This presentation is the property of its rightful owner.
Sponsored Links
1 / 102

Wykład 5: Zaawansowany SQL PowerPoint PPT Presentation


  • 92 Views
  • Uploaded on
  • Presentation posted in: General

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.

Download Presentation

Wykład 5: Zaawansowany SQL

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Wyk ad 5 zaawansowany sql

Wykład 5: Zaawansowany SQL


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

Typy danych czas-data

  • date:Data zawierające (4 cyfry) rok, miesiąci 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

    • odejmowaniejednej wartości date/time/timestamp od innej daje wartość typu interval

    • Wartości typu intervalmogą być dodawane do wartości date/time/timestamp


Typy danych czas data cd

Typy danych czas-data (cd.)

  • Możemy pobierać wartości poszczególnych pól z wartości date/time/timestamp

    • Przykład: extract (year fromr.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ładMSSQL:

      • cast('1 październik 2003' as datetime)


Typy definiowalne

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

Więzy dla dziedzin

  • Domain constraintssą 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 zlotynumeric(12, 2);create domaineuro 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: (castr.Aaseuro) (Powinno również przemnożyć wynik przez kurs wymiany)


Typy opisuj ce du e obiekty

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

  • 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

Więzy spójności dla pojedynczej relacji

  • not null

  • primary key

  • unique

  • check (P ),gdziePjest predykatem


Wi zy no t n ull

Więzy not null

  • Deklarujemy, że oddzial_nazwa dlarelacji aktywa jestnot null

    oddzial_nazwachar(15) not null

  • Dziedzina Euro ma być not null

    create domainEuronumeric(12,2)not null


Wi zy unique

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

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 TABLEwyborcaADD CONSTRAINT

CK_wiek_18

CHECK (DateDiff(yy,DateofBirth, DateofVote)>=18);


Klauzula check1

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

Klauzula check (cd.)

  • W standardzie SQL-92 klauzula checkpozwala na ograniczanie dziedzin:

    • Można jej użyć np. do sprawdzenia czy stawka godzinowa jest większa od wartość określona prawem.

      create domainstawka_godzinanumeric(5,2)constraintsprawdz_stawkecheck(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

    • Klauzulaconstraintsprawdz_stawke jest opcjonalna; wykorzystywana przy sygnalizacji, jakie więzy zostały naruszone przy modyfikacji danych.


Wi zy referencyjnej sp jno ci

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łupojawiającą się w jednej z krotek w relacji rachunek, to musi istnieć odpowiednia krotka w relacjioddzialdla oddziału “Centrum”.

  • Klucze główne, klucze kandydujące oraz klucze obce mogą być specyfikowane jako części polecenia SQLcreate table :

    • Klauzulaprimary keywymienia atrybuty tworzące klucz główny.

    • Klauzula unique [key]wymienia atrybuty tworzące klucz kandydujący.

    • Klauzula foreign keywymienia 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

Więzy spójnościw SQL – Przykład

create table klient(klient_nazwiskochar(20),klient_ulicachar(30),klient_miastochar(30),primary key (klient_nazwisko ))

create table oddzial(oddzial_nazwachar(15),oddzial_miastochar(30),aktywanumeric(12,2),primary key(oddzial_nazwa ))


Wi zy sp jno ci w sql przyk ad cd

Więzy spójnościw SQL – przykład (cd.)

create table rachunek(rachunek_numerchar(10),oddzial_nazwachar(15),staninteger,primary key (rachunek_numer), foreign key (oddzial_nazwa)references oddzial )

create table depozytor(klient_nazwiskochar(20),rachunek_numerchar(10),primary key(klient_nazwisko, rachunek_numer),foreign key(rachunek_numer ) references rachunek,foreign key(klient_nazwisko )references klient )


Kaskadowe dzia anie w sql

Kaskadowe działanie w SQL

create table rachunek

. . .foreign key(oddzial_nazwa)references oddzialon delete cascadeon update cascade. . . )

  • Klauzulaon delete cascadespowoduje, ż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

Kaskadowe działanie w SQL(cd.)

  • Jeśli istnieje łańcuch zależności kluczy obcych z on delete cascadeokreś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

Więzy spójnościw 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 a ssertions

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

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 zapewnienia1

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 kredytwhere not exists ( select * from kredytobiorca, depozytor, rachunekwhere kredyt.kredyt_numer = kredytobiorca.kredyt_numerand kredytobiorca.klient_nazwisko = depozytor.klient_nazwiskoand depozytor.rachunek_numer = rachunek.rachunek_numerand rachunek.stan >= 3000)))


Przyk ad zapewnienia2

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 oddzialwhere (select sum(kwota) from kredytwhere kredyt.oddzial_nazwa = oddzial.oddzial_nazwa ) >= (select sum (stan) from rachunekwhere rachunek.oddzial_nazwa = oddzial.oddzial_nazwa )))


Wyk ad 5 zaawansowany sql

PROGRAMOWANIE

Skarb DBA(głównie na przykładzie MS SQL Server)


Wyk ad 5 zaawansowany sql

  • Struktury proceduralne

    • Programowanie „wsadowe” (batch)

    • Zmienne

    • Instrukcje sterujące

    • Przetwarzanie błędów

  • Procedury składowane

  • Funkcje definiowalne

  • Synonimy

  • Wyzwalacze DML


Wyk ad 5 zaawansowany sql

  • 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

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


Wyk ad 5 zaawansowany sql

  • 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

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

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

Instrukcje sterujące

  • BEGIN … END grupuje instrukcje używane razem z IF, WHILE, CASE

  • IF … ELSE

    • USE AdventureWorks;GOUPDATE HumanResources.EmployeePayHistorySET PayFrequency = 4 WHERE EmployeeID = 1;IF @@ERROR <> 0 -- funkcja sysytemowa BEGINPRINT ‘An error occured in the previous statement.’; RETURN; ENDELSEPRINT ‘No error occured in the previous statement.’;


Instrukcje steruj ce 2

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

Instrukcje sterujące 3

  • CASE

  • USE AdventureWorks2008;GOSELECT Name,CASE NameWHEN ‘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

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

Zarządzanie błędami 2

  • Blok TRY … CATCH …

  • Składnia

    • BEGIN TRY-- Kod mogący generować błędyEND 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

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

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

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 ID10, -- 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

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

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

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

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

Procedury składowane 5

  • Składnia

    • CREATE PROC[EDURE] [schema_name.]proc_name[([email protected]} 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

Procedury składowane 6

  • Prekompilowana postać procedury jest przechowywana na serwerze

  • Opcja WITH RECOMPILEspowoduje, że procedura będzie rekompilowana przed każdym użyciem.

    • To niszczy jedną z ważnych zalet procedur.

  • KlauzulaEXECUTE ASokreś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łonkowieról sysadmin, db_owneroraz 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

Procedury składowane 7

  • Przykład

  • USE sample;GOCREATE PROCEDURE increase_budget (@percent INT=5)AS UPDATE projectSET budget = budget + [email protected]/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

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

Procedury składowane 9

  • Przykład z wykorzystaniem opcji OUTPUT

    • USE sample;GO

    • CREATE PROCEDURE delete_emp @employee_no INT, @counter INT OUTPUTAS SELECT @counter = COUNT(*)FROM works_onWHERE emp_no = @employee_noDELETE FROM employeeWHERE emp_no = @employee_noDELETE FROM works_onWHERE emp_no = @employee_noGO--DECLARE @quantity INT – deklarcja EXECUTE delete_emp @employee_no=28559, @[email protected] OUTPUTPRINT @quantityGO


Procedury sk adowane 10

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

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 111

Procedury składowane 11

If @OrderID IS NULL

BEGIN;Insert Orders(OrderDate, CustomerID)Values (@OrderDate, @CustomerID);-- Get OrderID value from insertSET @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


Wyk ad 5 zaawansowany sql

  • 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 [email protected] = @OrderID Output ,@CustomerID = '78',@OrderDate = '2008/07/24',

    @Details = @DetailsTVP;

  • Procedura powinna wypisać wartości z tabeli @DetailsTVP


Funkcje definiowalne udf

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

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

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. . .CATCHani RAISERROR.

    • Mogą wywoływać inne UDFy lub też same siebie(aż do 32 poziomu zagnieżdżenia).


Udf 4

UDF 4

  • Szablon funkcji skalarnej:

    • CREATE FUNCTION FunctionName (InputParameters)RETURNS DataTypeASBEGIN; 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

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 TABLEASRETURN(SELECT* FROMorders o JOINproducts p ONo.product=p.codeWHERE [email protected]);GO

        SELECT * FROM ppinline1(4504);


Udf 6

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

UDF 7

  • Przykład:

    • CREATE FUNCTION ppmulti1()RETURNS @pp1 TABLE (ca int,kwota money)ASBEGININSERT @pp1 SELECT CustomerAccount, sum(StoreSales) FROM orders group by CustomerAccount;-- tutaj moze być wiele instrukcji SQLa;RETURNEND;GO

    • SELECT * FROM ppmulti1()


Udf 8

UDF 8

  • Skorelowanie UDF

    • CREATE FUNCTION ppmulti2( @AC as int)RETURNS @pp1 TABLE (AccountNumberint,kwota money)ASBEGIN IF @AC IS NULLINSERT @pp1 SELECT CustomerAccount, sum(StoreSales) FROM orders group by CustomerAccount; ELSEINSERT @pp1 SELECT CustomerAccount, sum(StoreSales) FROM orders WHERE [email protected] GROUP BY CustomerAccount; RETURNEND;GO


Udf 81

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 t riggers

    Wyzwalacze (triggers)

    • Wyzwalacz triggerjest 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

    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 t riggers1

    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 t riggers2

    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

    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 ady1

    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) andNewApp.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

    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

    Przykład wyzwalacza SQL:1999

    create trigger debet_trigger after update on rachunek referencing new row as nrow

    for each rowwhen (nrow.stan< 0) begin atomicinsert 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 = 0where rachunek.rachunek_numer = nrow.rachunek_numer;end


    Wyzwalacze zdarzenia i akcje w sql

    Zdarzenie wyzwalającym może być insert, deletelub 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 przypadkuwstawiania 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

    Wyzwalacze: zdarzenia i akcje w SQL


    R ne poziomy wyzwalania

    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żywamyfor each statement zamiastfor each row

      • Używamyreferencing old tablealboreferencing new tableaby 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

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

    Działania zewnętrzne (cd.)


    Wyzwalacze w ms sql

    Wyzwalacze w MS-SQL

    • CREATE TRIGGER [schema_name.]trigger_nameON {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:

      • WITHENCRYPTION wskazuje, że kod T-SQL wyzwalacza powinien być ukryty lub zakodowany

      • WITH EXECUTE AS `nazwa użytkownika”


    Wyzwalacze w ms sql1

    create trigger debet_trigger onrachunekfor updateas if inserted.stan < 0begin insert into kredytobiorca(select klient_numer,rachunek_numerfrom 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

    Wyzwalacze w MS-SQL


    Kiedy nie nale y u ywa wyzwalaczy

    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 (changelubdelta) 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

    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

    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

    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


    Bezpiecze stwo cd

    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.

    Autoryzacja


    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.

    LiniaUi Uj, mówi, że użytkownikUinadał uprawnieniaupdatena loanużytkownikowiUj.

    Nadawanie uprawnień


    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żytkownikowiU1:

    Uprawnienia muszą być odebrane U4gdyżU1nie ma już uprawnień

    Uprawnienia nie mogą być odebraneU5gdyż posiada on również uprawnienia nadane przez U2

    Nie może być ścieżek, które nie mają połączenia z DBA:

    DBA nadaje uprawnieniaU7

    U7nadaje uprawnieniaU8

    U8nadaje uprawnieniaU7

    DBA usuwa uprawnieniaU7

    Uprawnienia U7dlaU8orazU8dlaU7muszą zostać usunięte bo nie ma już ścieżki od DBA ani do U7ani doU8.

    Graf nadawania uprawnień


    Okre lanie autoryzacji w sql

    Polecenie grantjest 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.

    Określanie autoryzacji w SQL


    Uprawnienia w sql

    select:pozwala na odczyt danych z relacji, jak również na wykonywanie kwerend z wykorzystaniem widoków

    Przykład: nadaj użytkownikomU1, U2, orazU3uprawnienia selectdo relacjioddzial:

    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

    Uprawnienia w SQL


    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 oddzialto U1with grant option

    Uprawnienie do nadawania uprawnień


    Odbieranie uprawnie w sql

    Polecenierevokeodbiera 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łowaall. 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.

    Odbieranie uprawnień w SQL


    Ograniczenia autoryzacji w sql

    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

    • 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.


    Wyk ad 5 zaawansowany sql

    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 urzednikcreate role menadzer

    grant select on oddzialto urzednikgrant update (stan) on rachunek to urzednikgrant all privileges on rachunek to menadzergrant urzednikto mendzergranturzednikto alicja, bolekgrant menadzer to czeslaw

    Role


    Zanurzony embedded sql

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

    • PolecenieEXEC SQLuż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

    Przykład

    • Określamy kwerendę SQL i deklarujemy dla niej cursor

      EXEC SQL

      declare c cursor for select depozytor.klient_nazwisko, klient_miastofrom depozytor, klient, rachunekwhere depozytor.klient_nazwisko = klient.klient_nazwisko and depozytor rachunek_numer = rachunek.rachunek_numerand rachunek.stan > :suma

      END_EXEC

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


    Zanurzony sql c d

    Zanurzony SQL (Cd.)

    • Polecenie openpowoduje wykonanie kwerendy

      EXEC SQL opencEND_EXEC

    • Polecenie fetchpowoduje, ż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.

    • Polecenieclosepowoduje zamknięcie przez bazę danych tymczasowej relacji przechowującej wynik kwerendy.

      EXEC SQL closec END_EXEC

      W Javie jest trochę inaczej (bardziej naturalnie)


    Modyfikacje poprzez kursor

    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 rachunekwhereoddzial_nazwa = ‘Toruń’for update

    • A potem pobieramy kolejne rekordy (fetch) i po każdym pobraniu wykonujemy polecenie

      update rachuneksetstan = stan + 100where current of c


    Odbc i jdbc

    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ą


    Wyk ad 5 zaawansowany sql

    Standard Open DataBase Connectivity(ODBC)

    jest standardemdla 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


    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

    ODBC (cd.)


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

    }

    Przykład kodu ODBC


    Funkcje i procedury

    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


  • Login