Pr
Vysoká škola báňská – Technická univerzita Ostrava Fakulta strojní, Katedra automatizační techniky a řízení
Informační systémy 2008/2009 Radim Farana 1
Řešený příklad zVytvořte aplikaci pro sledování pracovních výkonů jednotlivých pracovníků a výpočet jejich denní odměny na základě stanovených cen za výkon jednotlivých typů práce.
Informační systémy
2
Struktura navržené databáze
Informační systémy
3
1
Vytvoření databáze CREATE TABLE [dbo].[Platy] ALTER TABLE ( [dbo].[Platy] WITH NOCHECK ALTER ADD TABLE [dbo].[Platy] ADD [pl_ID] [int] IDENTITYCONSTRAINT (1, 1) NOT NULL [PK_Platy] , PRIMARY KEYCONSTRAINT CLUSTERED [FK_Platy_Pracovnici] FOREIGN KEY [pl_Pracovnik] [int] NULL ( , ( [pl_Datum] [datetime] NULL , [pl_ID] [pl_Pracovnik] [pl_Castka] [money] NULL ) ON [PRIMARY] ) REFERENCES [dbo].[Pracovnici] ( ) ON [PRIMARY] GO [pr_OSC] GO ALTER TABLE [dbo].[Pracovnici] WITH NOCHECK ADD) CREATE TABLE [dbo].[Pracovnici] (CONSTRAINT [PK_Pracovnici] GO PRIMARY KEY CLUSTERED [pr_OSC] [int] NOT NULL ( , ALTER TABLE [dbo].[prace] ADD [pr_Jmeno] [varchar] (50) COLLATE[pr_OSC] Czech_CI_AS NULL CONSTRAINT [FK_prace_Pracovnici] FOREIGN KEY ) ON [PRIMARY] ) ON [PRIMARY] ( GO GO [p_pracovnik] CREATE TABLE [dbo].[prace] ALTER TABLE ( [dbo].[prace] WITH NOCHECK ADD ) REFERENCES [dbo].[Pracovnici] ( [p_id] [int] IDENTITY CONSTRAINT (1, 1) NOT NULL [PK_prace] , PRIMARY KEY CLUSTERED[pr_OSC] [p_pracovnik] [int] NOT ( NULL , ), [p_typ] [varchar] (50) COLLATE Czech_CI_AS [p_id] NULL , CONSTRAINT [FK_prace_typyprace] FOREIGN KEY [p_kusu] [float] NULL), ON [PRIMARY] ( [p_datum] GO [datetime] NULL [p_typ] ) ON [PRIMARY] ALTER TABLE [dbo].[typyprace] WITH NOCHECK ADD ) REFERENCES [dbo].[typyprace] ( GO CONSTRAINT [PK_typyprace] PRIMARY KEY CLUSTERED [tp_nazev] CREATE TABLE [dbo].[typyprace] ( ( ) ON UPDATE CASCADE [tp_nazev] [varchar] (50) COLLATE [tp_nazev] Czech_CI_AS GONOT NULL , [tp_popis] [text] COLLATE ) ON Czech_CI_AS [PRIMARY] NULL , [tp_cena] GO [money] NULL ) ON [PRIMARY] TEXTIMAGE_ON ALTER TABLE[PRIMARY] [dbo].[Platy] ADD GO CONSTRAINT [CK_Platy] CHECK ([pl_Castka] > 10000) GO
Informační systémy
4
Testovací data INSERT INTO typyprace (tp_nazev, tp_popis, tp_cena) VALUES ('kvákání','skupinové',1000); INSERT INTO typyprace (tp_nazev, tp_popis, tp_cena) VALUES ('praní','každý kousek zvlášť',10); INSERT INTO typyprace (tp_nazev, tp_popis, tp_cena) VALUES ('spaní','celou noc',120); INSERT INTO typyprace (tp_nazev, tp_popis, tp_cena) VALUES ('škudlení','každý okamžik',1); INSERT INTO typyprace (tp_nazev, tp_popis, tp_cena) VALUES ('hrdlení','jen mezi příbuznými',25); INSERT INTO Pracovnici (pr_OSC, pr_Jmeno) VALUES (122,'Jan Kvákal'); INSERT INTO Pracovnici (pr_OSC, pr_Jmeno) VALUES (123,'Patrik Svatý'); INSERT INTO Pracovnici (pr_OSC, pr_Jmeno) VALUES (124,'Ivanka Desátá-Jedenáctá'); INSERT INTO Pracovnici (pr_OSC, pr_Jmeno) VALUES (125,'Jean-Dean Sikorski'); INSERT INTO Pracovnici (pr_OSC, pr_Jmeno) VALUES (126,'Patricia de Souza y Cachal de Brito'); INSERT INTO Pracovnici (pr_OSC, pr_Jmeno) VALUES (127,'Jan Pavel I'); INSERT INTO Pracovnici (pr_OSC, pr_Jmeno) VALUES (128,'Karla Osmá'); INSERT INTO Pracovnici (pr_OSC, pr_Jmeno) VALUES (130,'Orgoj Chorchoj'); INSERT INTO Pracovnici (pr_OSC, pr_Jmeno) VALUES (131,'Piotr Ivanovič Ustinovskij'); INSERT INTO Pracovnici (pr_OSC, pr_Jmeno) VALUES (133,'Jana Růžena Kostičkovitá'); INSERT INTO Pracovnici (pr_OSC, pr_Jmeno) VALUES (140,'Jan Evangelista Svatovítský'); INSERT INTO Pracovnici (pr_OSC, pr_Jmeno) VALUES (141,'Ian z Glenyanu'); INSERT INTO Pracovnici (pr_OSC, pr_Jmeno) VALUES (152,'Pavla Ospalá'); INSERT INTO Pracovnici (pr_OSC, pr_Jmeno) VALUES (153,'Karel Ospalý'); INSERT INTO prace (p_pracovnik, p_typ, p_kusu, p_datum) VALUES (122,'praní',4, CAST ( '03/12/2006' AS DateTime)); INSERT INTO prace (p_pracovnik, p_typ, p_kusu, p_datum) VALUES (122,'praní',5, CAST ( '03/13/2006' AS DateTime)); INSERT INTO prace (p_pracovnik, p_typ, p_kusu, p_datum) VALUES (123,'škudlení',12, CAST ( '03/14/2006' AS DateTime)); INSERT INTO prace (p_pracovnik, p_typ, p_kusu, p_datum) VALUES (131,'praní',1, CAST ( '03/13/2006' AS DateTime)); INSERT INTO prace (p_pracovnik, p_typ, p_kusu, p_datum) VALUES (131,'praní',5, CAST ( '03/14/2006' AS DateTime)); INSERT INTO prace (p_pracovnik, p_typ, p_kusu, p_datum) VALUES (131,'škudlení',18, CAST ( '03/15/2006' AS DateTime)); INSERT INTO prace (p_pracovnik, p_typ, p_kusu, p_datum) VALUES (140,'praní',20, CAST ( '03/12/2006' AS DateTime)); INSERT INTO prace (p_pracovnik, p_typ, p_kusu, p_datum) VALUES (140,'praní',2, CAST ( '03/13/2006' AS DateTime)); INSERT INTO prace (p_pracovnik, p_typ, p_kusu, p_datum) VALUES (140,'praní',31, CAST ( '03/14/2006' AS DateTime)); INSERT INTO prace (p_pracovnik, p_typ, p_kusu, p_datum) VALUES (128,'praní',3, CAST ( '03/15/2006' AS DateTime)); INSERT INTO prace (p_pracovnik, p_typ, p_kusu, p_datum) VALUES (128,'škudlení',2, CAST ( '03/12/2006' AS DateTime)); INSERT INTO prace (p_pracovnik, p_typ, p_kusu, p_datum) VALUES (128,'praní',6, CAST ( '03/13/2006' AS DateTime)); INSERT INTO prace (p_pracovnik, p_typ, p_kusu, p_datum) VALUES (128,'praní',7, CAST ( '03/14/2006' AS DateTime));
Informační systémy
5
Agregace zUrčete počet záznamů o práci pro jednotlivé pracovníky zSELECT p_pracovnik, COUNT(p_id) as Pocet FROM prace GROUP BY p_pracovnik;
p_pracovnik ----------122 123 128 131 140
Pocet ----------2 1 4 3 3
(5 row(s) affected)
Informační systémy
6
2
Agregace zUrčete průměrný počet kusů ze všech záznamů práce zSELECT AVG(p_kusu) FROM prace;
8.9230769230769234 (1 row(s) affected)
Informační systémy
7
Agregace zUrčete průměrný počet kusů pro jednotlivé druhy práce zSELECT p_typ, AVG(p_kusu) FROM prace GROUP BY p_typ; p_typ --------------- -----------------praní 8.4000000000000004 škudlení 10.666666666666666 (2 row(s) affected)
Informační systémy
8
Vnořený dotaz vracející skalární hodnotu zUrčete záznamy o práci s nadprůměrným počtem kusů zSELECT * FROM prace WHERE p_kusu> (SELECT AVG(p_kusu) FROM prace); p_id ----------6 9 10 12
p_pracovnik ----------123 131 140 140
p_typ ------------škudlení škudlení praní praní
p_kusu --------12.0 18.0 20.0 31.0
p_datum ---------------------2006-03-14 00:00:00.000 2006-03-15 00:00:00.000 2006-03-12 00:00:00.000 2006-03-14 00:00:00.000
(4 row(s) affected)
Informační systémy
9
3
Vnořený dotaz s propojením zUrčete záznamy o práci s nadprůměrným počtem kusů v rámci stejného druhu práce zSELECT * FROM prace WHERE p_kusu> (SELECT AVG(p_kusu) ---------------FROM prace As TMP WHERE TMP.p_typ=prace.p_typ); p_id
p_pracovnik p_typ
p_kusu p_datum
----------10 12 6 9
----------140 140 123 131
-----20.0 31.0 12.0 18.0
------------praní praní škudlení škudlení
----------2006-03-12 00:00:00.000 2006-03-14 00:00:00.000 2006-03-14 00:00:00.000 2006-03-15 00:00:00.000
(4 row(s) affected)
Informační systémy
10
Vnořený dotaz vracející vektor hodnot zUrčete všechny práce, kterých bylo v jednom dni více než tři (vnořený dotaz) z SELECT * FROM prace WHERE p_datum In (SELECT p_id p_datum p_pracovnik p_typ p_kusu p_datum ----------- ----------- ------ ------------ ----------------------15 prace 128 praní 6.0 2006-03-13 00:00:00.000 FROM 11 140 praní 2.0 2006-03-13 00:00:00.000 5 praní 5.0 2006-03-13 00:00:00.000 GROUP BY 122 p_datum 7 131 praní 1.0 2006-03-13 00:00:00.000 8 131 praní 5.0 2006-03-14 00:00:00.000 HAVING Count(*)>3) 6 123 škudlení 12.0 2006-03-14 00:00:00.000 12 140 praní 31.0 2006-03-14 00:00:00.000 16 128 praní 7.0 2006-03-14 00:00:00.000 ORDER BY p_datum; (8 row(s) affected)
Informační systémy
11
Vnořený dotaz s propojením vracející vektor hodnot zUrčete všechny práce, kterých bylo v jednom dni více než dvě stejného druhu z SELECT * FROM prace WHERE p_datum In (SELECT p_datum p_id p_pracovnik p_typ p_kusu p_datum FROM prace as ----------TMP --------- ------ ---------------------------5 122 praní 5.0 2006-03-13 00:00:00.000 GROUP BY7 p_datum,p_typ 131 praní 1.0 2006-03-13 00:00:00.000 11 140 praní 2.0 2006-03-13 00:00:00.000 HAVING Count(*)>2 AND TMP.p_typ=prace.p_typ) 15 128 praní 6.0 2006-03-13 00:00:00.000 16 128 praní 7.0 2006-03-14 00:00:00.000 ORDER BY12p_datum; 140 praní 31.0 2006-03-14 00:00:00.000 8
131
praní
5.0
2006-03-14 00:00:00.000
(7 row(s) affected)
Informační systémy
12
4
Spojení tabulek zUrčete pro každou osobu a den výdělek za jednotlivé práce (počet kusů krát cena) zSELECT p_pracovnik, p_datum, p_kusu*tp_cena As Plat FROM prace INNER JOIN typyprace On p_typ=tp_nazev; p_pracovnik ----------122 122 123 131 131 131 140 140 140 128 128 128 128
p_datum ------------------------2006-03-12 00:00:00.000 2006-03-13 00:00:00.000 2006-03-14 00:00:00.000 2006-03-13 00:00:00.000 2006-03-14 00:00:00.000 2006-03-15 00:00:00.000 2006-03-12 00:00:00.000 2006-03-13 00:00:00.000 2006-03-14 00:00:00.000 2006-03-15 00:00:00.000 2006-03-12 00:00:00.000 2006-03-13 00:00:00.000 2006-03-14 00:00:00.000
Plat -----40.0 50.0 12.0 10.0 50.0 18.0 200.0 20.0 310.0 30.0 2.0 60.0 70.0
(13 row(s) affected)
Informační systémy
13
Spojení dat spolu s agregací zUrčete pro každou osobu a den celkový výdělek Suma(počet kusů krát cena) zSELECT p_pracovnik,p_datum, Sum(p_kusu*tp_cena) As Plat FROM prace INNER JOIN typyprace On p_typ=tp_nazev GROUP BY p_pracovnik,p_datum; p_pracovnik ----------122 128 140 122 128 131 140 123 128 131 140 128 131
p_datum ------------------------2006-03-12 00:00:00.000 2006-03-12 00:00:00.000 2006-03-12 00:00:00.000 2006-03-13 00:00:00.000 2006-03-13 00:00:00.000 2006-03-13 00:00:00.000 2006-03-13 00:00:00.000 2006-03-14 00:00:00.000 2006-03-14 00:00:00.000 2006-03-14 00:00:00.000 2006-03-14 00:00:00.000 2006-03-15 00:00:00.000 2006-03-15 00:00:00.000
Plat ----40.0 2.0 200.0 50.0 60.0 10.0 20.0 12.0 70.0 50.0 310.0 30.0 18.0
(13 row(s) affected)
Informační systémy
14
Odstranění dat z tabulky zOdstraňte všechny záznamy v tabulce Platy zDELETE FROM Platy;
(0 row(s) affected)
Informační systémy
15
5
Vložení dat do tabulky z Přidejte do tabulky platy pro každého pracovníka za každý den plat ve výši jeho celkového výdělku Suma(počet kusů krát cena) z INSERT INTO Platy (pl_Pracovnik, pl_Datum, pl_Castka) SELECT p_pracovnik,p_datum, Sum(p_kusu*tp_cena) As Plat FROM prace Server: MsgOn 547, p_typ=tp_nazev Level 16, State 1, Line 1 INNER JOIN typyprace INSERT statement conflicted with COLUMN CHECK constraint 'CK_Platy'. The conflict occurred in GROUP BY p_pracovnik,p_datum; database 'prace', table 'Platy', column 'pl_Castka'. The statement has been terminated.
Informační systémy
16
Vložení dat do tabulky – oprava zZjistěte, proč nebyly platy vloženy Nyní je vložení platů možné: INSERT INTO Platy z(Je nutno odstranit omezení CK_Platy: (pl_Pracovnik, pl_Datum, pl_Castka) ([pl_Castka] > 10000): SELECT p_pracovnik,p_datum, zALTER TABLE Platy Sum(p_kusu*tp_cena) As Plat DROP CONSTRAINT CK_Platy; FROM prace INNER JOIN typyprace On p_typ=tp_nazev (13 row(s) affected) GROUP BY p_pracovnik,p_datum; Informační systémy
17
Určení záznamů bez podřízených zUrčete seznam pracovníků, kteří nemají žádnou práci zSELECT Pracovnici.* FROM Pracovnici LEFT JOIN prace ON pr_OSC=p_pracovnik WHERE p_ID IS NULL; pr_OSC ----------124 125 126 127 130 133 141 152 153
pr_Jmeno ----------------------------------------Ivanka Desátá-Jedenáctá Jean-Dean Sikorski Patricia de Souza y Cachal de Brito Jan Pavel I Orgoj Chorchoj Jana Růžena Kostičkovitá Ian z Glenyanu Pavla Ospalá Karel Ospalý
(9 row(s) affected)
Informační systémy
18
6
Vložený dotaz v definici sloupce zPro každého pracovníka určete počet prací pr_OSC
pr_Jmeno
Počet
152 153
Pavla Ospalá Karel Ospalý
0 0
-------------------------------------------------- -----z SELECT ----------*, 122 Jan Kvákal 2 Patrik Svatý 1 (SELECT123 COUNT(*) 124 Ivanka Desátá-Jedenáctá 0 125 0 FROM prace ASJean-Dean TMPdeSikorski 126 Patricia Souza y Cachal de Brito 0 127 Jan Pavel I 0 Karla Osmá 4 WHERE 128 TMP.p_pracovnik=Pracovnici.pr_OSC) 130 Orgoj Chorchoj 0 Piotr Ivanovič Ustinovskij 3 AS Počet131 133 Jana Růžena Kostičkovitá 0 140 Jan Evangelista Svatovítský 3 FROM Pracovnici; 141 Ian z Glenyanu 0
(14 row(s) affected)
Informační systémy
19
Vložený dotaz v definici sloupce zPro každého pracovníka určete počet dnů, kdy pracoval pr_OSC
pr_Jmeno
Počet
152 153
Pavla Ospalá Karel Ospalý
0 0
---------------------------------------- ----------z SELECT ----------*, 122 Jan Kvákal 2 Patrik Svatý (SELECT123 COUNT(DISTINCT p_datum) 01 124 Ivanka Desátá-Jedenáctá 125 Jean-Dean Sikorski 0 FROM prace ASPatricia TMPde Souza y Cachal de Brito 126 0 127 Jan Pavel I 0 Karla Osmá 4 WHERE 128 TMP.p_pracovnik=Pracovnici.pr_OSC) 130 Orgoj Chorchoj 0 Piotr Ivanovič Ustinovskij 3 AS Počet131 133 Jana Růžena Kostičkovitá 0 140 Jan Evangelista Svatovítský 3 FROM Pracovnici; 141 Ian z Glenyanu 0
(14 row(s) affected)
Informační systémy
20
Konverze dat – eliminace NULL z Pro každého pracovníka určete počet kusů z tabulky prace, pokud dosud nepracoval, bude počet 0 pr_OSC
pr_Jmeno
Počet
140 141 152 153
Jan Evangelista Svatovítský Ian z Glenyanu Pavla Ospalá Karel Ospalý
53.0 0.0 0.0 0.0
----------------------------------------z SELECT ----------pr_OSC, pr_Jmeno, 122 Jan Kvákal 9.0 123 Patrik Svatý 12.0 COALESCE(SUM(p_kusu),0) AS Počet 124 Ivanka Desátá-Jedenáctá 0.0 125 Jean-Dean Sikorski 0.0 FROM pracovnici 126 Patricia de Souza y Cachal de Brito 0.0 127 Jan Pavel I 0.0 128 Karla Osmá 18.0 LEFT JOIN prace on pr_OSC=p_pracovnik 130 Orgoj Chorchoj 0.0 131 Piotr Ivanovič Ustinovskij 24.0 GROUP BY pr_OSC, pr_Jmeno; 133 Jana Růžena Kostičkovitá 0.0
(14 row(s) affected)
Informační systémy
21
7
Agregace - opakovaná z Pro každého pracovníka zobrazte jeho osobní číslo, jméno a první a poslední den, ve kterém pracoval z SELECT pr_OSC, pr_Jmeno, Min(p_datum) As prvni, Max(p_datum) As poslední FROM pracovnici INNER pr_OSC pr_Jmeno prvni JOIN prace poslední ------- ---------------------------- ----------------------- ----------------------140 Jan Evangelista Svatovítský 2006-03-12 00:00:00.000 2006-03-14 00:00:00.000 ON pr_OSC=p_pracovnik 122 Jan Kvákal 2006-03-12 00:00:00.000 2006-03-13 00:00:00.000 128 Karla Osmá 2006-03-12 00:00:00.000 2006-03-15 00:00:00.000 GROUP BY pr_OSC, pr_Jmeno 123 Patrik Svatý 2006-03-14 00:00:00.000 2006-03-14 00:00:00.000 131 Piotr Ivanovič Ustinovskij ORDER BY pr_Jmeno;2006-03-13 00:00:00.000 2006-03-15 00:00:00.000 (5 row(s) affected)
Informační systémy
22
Konverze dat z Pro každého pracovníka zobrazte jeho osobní číslo, jméno a počet dnů mezi první a poslední prací z SELECT pr_OSC, pr_Jmeno, CAST(Max(p_datum)-Min(p_datum) AS Integer) FROM pracovnici INNER JOIN prace ON pr_OSC=p_pracovnik pr_OSC pr_Jmeno ----------- -------------------------------------------------- ----------140 JanBY Evangelista Svatovítský 2 GROUP pr_OSC, pr_Jmeno 122 Jan Kvákal 1 128 Karla Osmá 3 ORDER BY pr_Jmeno; 123 Patrik Svatý 0 131
Piotr Ivanovič Ustinovskij
2
(5 row(s) affected)
Informační systémy
23
Dávka – využití kurzoru z Vytvořte dávku, která vygeneruje pro tabulku typyprace dávku definicí dat tabulky DECLARE @nazev varchar(50), @popis varchar(8000), @cena money DECLARE listtypyprace CURSOR FORWARD_ONLY FOR SELECT [tp_nazev], [tp_popis], [tp_cena] FROM [prace].[dbo].[typyprace] OPEN listtypyprace FETCH NEXT FROM listtypyprace INTO @nazev, @popis, @cena
INSERT INTO typyprace ([tp_nazev], [tp_popis], [tp_cena])
VALUES ('hrdlení', 'jen mezi příbuznými', 25.00); WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO typyprace ([tp_nazev], [tp_popis], [tp_cena]) PRINT 'INSERT INTO typyprace ([tp_nazev], [tp_popis], VALUES[tp_cena])' ('kvákání', 'skupinové', 1000.00); PRINT 'VALUES (''' + @nazev + ''', ''' + @popis + ''', ' + CAST(@cena as varchar) + INSERT INTO typyprace ([tp_nazev], [tp_popis], [tp_cena]) ');' VALUES ('praní', 'každý kousek zvlášť', 10.00); FETCH NEXT FROM listtypyprace INSERT INTO typyprace ([tp_nazev], [tp_popis], [tp_cena]) INTO @nazev, @popis, @cena VALUES ('spaní', 'celou noc', 120.00); END CLOSE listtypyprace DEALLOCATE listtypyprace Informační systémy GO
INSERT INTO typyprace ([tp_nazev], [tp_popis], [tp_cena]) VALUES ('škudlení', 'každý okamžik', 1.00);
24
8
Uložená procedura z Vytvořte uloženou proceduru ‚VlozPraci‘, která převezme údaje o pracovníkovi, typu práce, počtu kusů a nepovinné datum (v tom případě vloží aktuální datum), zkontroluje existenci pracovníka a typu práce, vloží nový záznam do databáze a vrátí jeho ID (1 row(s) affected) CREATE PROCEDURE VlozPraci @pracovnik INT,
IF EXISTS (SELECT * FROM pracovnici WHERE pr_OSC=@pracovnik) BEGIN
18 @typ VARCHAR(50), IF EXISTS (SELECT * FROM typyprace WHERE tp_nazev=@typ) pr_OSC@kusu FLOAT, pr_Jmeno BEGIN @datum DATETIME = NULL, INSERT INTO prace ----------------------------------------------Test @ID INT OUTPUT (p_pracovnik, p_typ, p_kusu, p_datum) 122 Jan Kvákal DECLARE @MyID INT AS VALUES 123 Patrik Svatý EXEC VlozPraci DECLARE @MyERR INT (@pracovnik, @typ, @kusu, @datum) @pracovnik=152, 124 Ivanka Desátá-Jedenáctá IF @datum IS NULL SELECT @MyERR = @@ERROR @typ='praní', 125 Jean-Dean Sikorski BEGIN IF @MyERR!=0 GOTO err_handler @kusu=2, 126 Patricia de Souza y Cachal de Brito DECLARE @actdate as datetime SELECT @ID = @@IDENTITY @ID=@MyID OUTPUT 127 Jan Pavel I SET @actdate=getdate() END PRINT @MyID 128 Karla Osmá SET @datum=CAST(CAST(DATEPART(m, @actdate) ELSE AS VARCHAR) + '/' + CAST(DATEPART(d, GO @actdate) AS 130 VARCHAR)+ Orgoj Chorchoj '/' + CAST(DATEPART(yy, @actdate) SELECT AS VARCHAR) @ID = 0 AS DATETIME) SELECT * FROM pracovnici 131 Piotr Ivanovič Ustinovskij END END 133 Jana Růžena Kostičkovitá ELSE SELECT @ID = 0 140 Jan Evangelista Svatovítský RETURN 0 141 Ian z Glenyanu err_handler: 152 Pavla Ospalá SELECT @ID = -1 153 Karel Ospalý (14 row(s) affected) Informační systémy
RETURN @MyERR GO
25
Spoušť Zdroj: Výsledek: ALTER TRIGGER pracemeniplaty ALTER TRIGGER ON [dbo].[prace] pracemeniplaty ON [dbo].[prace] FOR INSERT, UPDATE, FOR DELETE INSERT, UPDATE, DELETE AS AS -- zpracujeme vložení nové DECLARE práce @pid INT, @pprac INT, @ptyp VARCHAR(50), @pkusu FLOAT, @pdatum DATETIME, @pcena money IF EXISTS (SELECT * -FROM zpracujeme INSERTED) odstranění práce BEGIN IF EXISTS (SELECT * FROM deleted) DECLARE @pid INT, BEGIN @pprac INT, @ptyp VARCHAR(50), @pkusu FLOAT, @pdatum DATETIME, @pcena money -- zjistíme kolik záznamů -- zjistíme je přidáno kolik záznamů je odstraněno -- zpracujeme vložení nové práce DECLARE cpom CURSOR DECLARE cpom CURSOR IF FOR EXISTS (SELECT * FROM INSERTED) FOR BEGIN SELECT p_id, p_pracovnik, SELECT p_typ, p_id, p_kusu, p_pracovnik, p_datum p_typ, FROM p_kusu, inserted p_datum FROM deleted -zjistíme kolik záznamů je přidáno OPEN cpom OPEN cpom -- vezmeme první novýDECLARE --záznam vezmemecpom prvníCURSOR odstraněný záznam FOR FETCH NEXT FROM cpom FETCH NEXT FROM cpom SELECT p_id, p_pracovnik, p_kusu, p_datum FROM inserted INTO @pid, @pprac, @ptyp, INTO @pid, @pkusu, @pprac, @pdatum @ptyp,p_typ, @pkusu, @pdatum OPEN cpom WHILE @@FETCH_STATUS WHILE @@FETCH_STATUS =0 =0 --BEGIN vezmeme první nový záznam BEGIN FETCH NEXT FROM cpom práce -- Určíme cenu vložené --práce Určíme cenu odstraněné INTO @pid, @pprac, @ptyp, @pkusu, @pdatum SELECT @pcena = Max(tp_cena) SELECT @pcena FROM=typyprace Max(tp_cena) WHERE FROM tp_nazev=@ptyp typyprace WHERE tp_nazev=@ptyp WHILE = 0 funguje -- Pokud již tento pracovník -- Předpokládámě, v@@FETCH_STATUS daném dni pracoval, že vše řádně budeme záznam a záznam upravovat, o platu jinak existuje musíme přidat nový IF EXISTS (SELECT *BEGIN UPDATE FROM platy platy WHERE SET pl_castka=pl_castka-@pkusu*CAST(@pcena pl_pracovnik=@pprac AND pl_datum=@pdatum) AS FLOAT) WHERE pl_pracovnik=@pprac AND pl_datum=@pdatum -- Určíme cenuFROM vložené práce UPDATE platy SET pl_castka=pl_castka+@pkusu*CAST(@pcena FETCH NEXT cpom AS FLOAT) WHERE pl_pracovnik=@pprac AND pl_datum=@pdatum SELECT @pcena = Max(tp_cena) FROM@pdatum typyprace WHERE tp_nazev=@ptyp ELSE INTO @pid, @pprac, @ptyp, @pkusu, -- Pokud již tento pracovník v daném dni pracoval, budeme záznam upravovat, jinak musíme nový INSERT INTO platy(pl_pracovnik, END pl_datum, pl_castka) VALUES (@pprac, @pdatum, @pkusu*CAST(@pcena AS přidat FLOAT)) IF EXISTS FETCH NEXT FROM CLOSE cpom cpom(SELECT * FROM platy WHERE pl_pracovnik=@pprac AND pl_datum=@pdatum) UPDATE platy SET pl_castka=pl_castka+@pkusu*CAST(@pcena AS FLOAT) WHERE pl_pracovnik=@pprac AND pl_datum=@pdatum INTO @pid, @pprac, DEALLOCATE @ptyp, @pkusu, cpom @pdatum END ENDELSE INSERT INTO platy(pl_pracovnik, pl_datum, pl_castka) VALUES (@pprac, @pdatum, @pkusu*CAST(@pcena AS FLOAT)) CLOSE cpom FETCH NEXT FROM cpom DEALLOCATE cpom INTO @pid, @pprac, @ptyp, @pkusu, @pdatum END END GO CLOSE cpom DEALLOCATE cpom END -- po skončení je možné, že některý záznam o platu se dostal na nulovou částku DELETE FROM Platy WHERE pl_castka=0 GO
zDoplňte spoušť o část reagující na odstranění záznamu o práci
Informační systémy
26
Kontrolní otázka zCo se stane, pokud se změní cena některého typu práce? z Odměny za tento typ práce nebudou odpovídat nové ceně. z Důvodem je chyba v koncepci databáze – tabulky typyprace obsahuje aktuální hodnoty, zatímco tabulky prace a platy obsahují historická data. z Řešením je uložení aktuální ceny typu práce přímo k nově vložené práci, kde zůstane zachována i do budoucna. Informační systémy
27
9
