1 / 102

Wykład 5: Zaawansowany SQL

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.

starr
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Wykład 5: Zaawansowany SQL

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

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

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

  5. 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!

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

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

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

  9. Więzy spójności dla pojedynczej relacji • not null • primary key • unique • check (P ),gdziePjest predykatem

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

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

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

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

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

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

  16. Więzy spójnościw SQL – Przykład create table klient(klient_nazwisko char(20),klient_ulica char(30),klient_miasto char(30),primary key (klient_nazwisko )) create table oddzial(oddzial_nazwachar(15),oddzial_miasto char(30),aktywa numeric(12,2),primary key(oddzial_nazwa ))

  17. Więzy spójnościw SQL – przykład (cd.) create table rachunek(rachunek_numer char(10),oddzial_nazwa char(15),stan integer,primary key (rachunek_numer), foreign key (oddzial_nazwa)references oddzial ) create table depozytor(klient_nazwiskochar(20),rachunek_numer char(10),primary key(klient_nazwisko, rachunek_numer),foreign key(rachunek_numer ) references rachunek,foreign key(klient_nazwisko )references klient )

  18. Kaskadowe działanie w SQL create table rachunek . . .foreign key(oddzial_nazwa)references oddzialon delete cascade on 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).

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

  20. 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!)

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

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

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

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

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

  26. Struktury proceduralne • Programowanie „wsadowe” (batch) • Zmienne • Instrukcje sterujące • Przetwarzanie błędów • Procedury składowane • Funkcje definiowalne • Synonimy • Wyzwalacze DML

  27. 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!

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

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

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

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

  32. 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.’;

  33. 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ść”).

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

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

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

  37. 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 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;

  38. 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 <> 0PRINT @SaveError; • Funkcji @@ERROR używamy głównie ze względu na kompatybilność ze starszymi wersjami SQL Server np. 2000

  39. 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 ] ]

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

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

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

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

  44. 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ę.

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

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

  47. Procedury składowane 7 • Przykład • USE sample;GOCREATE PROCEDURE increase_budget (@percent INT=5)AS UPDATE projectSET 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ę?)

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

  49. 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_noGO--DECLARE @quantity INT – deklarcja EXECUTE delete_emp @employee_no=28559, @counter=@quantity OUTPUTPRINT @quantityGO

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

More Related