Informan systmy Realizace uloench procedur a spout jejich

Informační systémy Realizace uložených procedur a spouští, jejich praktické využití.

Obsah cvičení • Tvorby uložené procedury. • Aplikace uložené procedury. • Tvorba spouště. • Aplikace spouště.

Uložená procedura (1) q Vytvořit uloženou proceduru Proc. Vloz. Spoulures pro vkládání záznamů do tabulky TSpolures. Vstupními parametry procedury jsou (název projektu, příjmení spoluřešitele, jméno spoluřešitele , datum začátku spolupráce a datum konce spolupráce). USE Evid. Projektu; GO -- Definice pro vytvoření procedury CREATE PROCEDURE Proc. Vloz. Spolures (@Nazev. Proj varchar(max) , @Spolures. P nchar(25), @Spolures. J nchar(15), @Od smalldatetime, @Do smalldatetime) AS -- Deklarace proměnných pro zjištění IC projektu a IC zaměstnance. DECLARE @ICProjektu int, @ICSpolures char(5) … pokračování na dalším snímku

Uložená procedura (2) -- Kontrola existence názvu projektu IF NOT EXISTS (SELECT p_ICislo FROM TProjekt WHERE p_Nazev=@Nazev. Proj) BEGIN PRINT('Projekt s tímto názvem není v databázi!!') RETURN END ELSE -- Uložit číslo projektu pro jeho další využití pro funkci INSERT SET @ICProjektu=(SELECT p_ICislo FROM TProjekt WHERE p_Nazev=@Nazev. Proj) … pokračování na dalším snímku

Uložená procedura (3) -- Kontrola existence spoluřešitele -- (pokud je zadáno příjmení a jméno, zda existuje, pak zjistit jeho ID) IF ((@Spolures. J IS NOT NULL AND @Spolures. J IS NOT NULL) AND EXISTS (SELECT z_Prijmeni, z_Jmeno FROM TZamestnanec WHERE z_Prijmeni=@Spolures. P AND z_Jmeno=@Spolures. J)) BEGIN -- Kontrola, zda neexistuje více zaměstnanců se stejným jménem a příjmením IF (SELECT Count(z_IC) FROM TZamestnanec GROUP BY z_Jmeno, z_Prijmeni HAVING z_Jmeno=@Spolures. J AND z_Prijmeni=@Spolures. P)>1 BEGIN PRINT('Zaměstnanců s tímto jménem a příjmením je více. Záznam nebude vložen. ') RETURN END ELSE -- Zjistit ID zaměstance a uložit jej pro jeho další využití pro funkcí INSERT SET @ICSpolures=(SELECT z_IC FROM TZamestnanec WHERE (z_Jmeno=@Spolures. J AND z_Prijmeni=@Spolures. P)) END … pokračování na dalším snímku

Uložená procedura (4) ELSE -- Zjištění ID čísla zaměstnance, pokud bylo zadáno pouze jméno nebo pouze příjmení BEGIN IF ((@Spolures. P IS NOT NULL AND @Spolures. J IS NULL) AND EXISTS (SELECT z_Prijmeni FROM TZamestnanec WHERE z_Prijmeni=@Spolures. P)) BEGIN -- Kontrola, zda existuje pouze a právě jedno příjmení pro zjištění jeho ID IF (SELECT Count(z_Prijmeni) FROM TZamestnanec WHERE z_Prijmeni=@Spolures. P)=1 SET @ICSpolures=(SELECT z_IC FROM TZamestnanec WHERE z_Prijmeni=@Spolures. P) ELSE BEGIN PRINT('Zaměstnanců s tímto příjmením existuje více, je nutné doplnit ještě jméno. ') RETURN END … pokračování na dalším snímku

Uložená procedura (5) IF ((@Spolures. J IS NOT NULL AND @Spolures. P IS NULL) AND …. Další pokračování kódu na dalším snímku EXISTS (SELECT z_Jmeno FROM TZamestnanec WHERE z_Jmeno=@Spolures. J)) BEGIN -- Kontrola, zda existuje pouze jedno křestní jméno spoluřešitele, pak zjistíme jeho ID IF (SELECT Count(z_Prijmeni) FROM TZamestnanec WHERE z_Prijmeni=@Spolures. P)=1 SET @ICSpolures=(SELECT z_IC FROM TZamestnanec WHERE z_Prijmeni=@Spolures. P) ELSE BEGIN PRINT('Zaměstnanců s tímto křestním jménem existuje více, je nutné doplnit ještě příjmení. ') RETURN END -- Kontrola, zda nebyl vložen parametr s křestním jménem ani příjmením IF (@Spolures. J IS NULL AND @Spolures. P IS NULL) BEGIN PRINT('Nebylo vloženo jméno ani příjmení spoluřešitele!'); RETURN END … pokračování na dalším snímku

Uložená procedura (6) -- Pokud jsme již zde, tak máme číslo projektu i číslo spoluřešitele, pak lze vložit záznam do databáze INSERT TSpolures (s_ICislo, s_ICZamest, s_Od. Datum, s_Do. Datum) VALUES (@ICProjektu, @ICSpolures, @Od, @Do) GO … konec tvorby uložené procedury -- Volání uložené procedury USE [Evid. Projektu] GO DECLARE @return_value int EXEC @return_value = [dbo]. [Proc. Vloz. Spolures] @Nazev. Proj = 'SW signal analyzér a komunikační software s měřicí kartou', @Spolures. P = 'Farana‚ @Spolures. J = 'Radim', @Od = '10 -MAR-08', @Do = '30 -NOV-2008' SELECT 'Return Value' = @return_value GO

Spouště (1) q Vytvořit spoušť, která při vložení spoluřešitele kontroluje datum spolupráce. Datum, které je mimo rozsah doby řešení projektu, nastaví dle této doby řešení projektu.

Spouště (2) USE Evid. Projektu; GO CREATE TRIGGER TRTSpolures. Kontr. Datum /* Vložit spoušť pro tabulku TSpolures*/ /*. . . probíhá kontrola u posledně vloženého záznamu na datum Od, Do*/ ON TSpolures AFTER INSERT AS BEGIN DECLARE @Od smalldatetime, @Do smalldatetime, @Proj int, @Zahaj. Res smalldatetime, @Konec. Res smalldatetime, @Upd smallint SET @Od=(SELECT s_Od. Datum FROM TSpolures WHERE s_IC=(SELECT max(s_IC) FROM TSpolures)); SET @Do=(SELECT s_Do. Datum FROM TSpolures WHERE s_IC=(SELECT max(s_IC) FROM TSpolures)); SET @Proj=(SELECT s_ICislo FROM TSpolures WHERE s_IC=(SELECT max(s_IC) FROM TSpolures)); SET @Zahaj. Res=(SELECT p_Zahaj. Res FROM TProjekt WHERE p_ICislo=@Proj); SET @Konec. Res=(SELECT p_Konec. Res FROM TProjekt WHERE p_ICislo=@Proj);

Spouště (3) -- Za předpokladu, že @Od bude vždy menší než @Do IF @Od<@Zahaj. Res OR @Od>=@Konec. Res BEGIN SET @Upd=1 SET @Od=@Zahaj. Res PRINT('Změna datumu počátku spolupráce. ') END IF @Do<=@Zahaj. Res OR @Do>@Konec. Res BEGIN SET @Upd=1 SET @Do=@Konec. Res PRINT('Změna datumu počátku spolupráce. ') END IF @Upd=1 UPDATE TSpolures SET s_Od. Datum=@Od, s_Do. Datum=@Do WHERE s_IC=(SELECT max(s_IC) FROM TSpolures) END; GO

Shrnutí • Uložená procedura – určena pro úpravy databází v T-SQL. • Uložené procedury je vhodné vytvářet přímo v databázovém serveru, aby nebylo nutné přecházet mezi aplikací a serverem a zajišťovat připojení k serveru. • Spouště – speciální uložené procedury, které se volají při změně obsahu databáze. • Na rozdíl od uložených procedur se spouště aktivují změnou databáze (INSERT, UPDATE, DELETE), nikoliv pomocí EXEC jako uložené procedury.