180 likes | 315 Views
Informační systémy. Realizace sběru dat v rámci realizovaných úloh. Obsah cvičení. Grafická tvorba pohledu. Pohled – T-SQL. Kontrola aktualizací pohledu. Kurzory. Grafická tvorba pohledu (1).
E N D
Informační systémy Realizace sběru dat v rámci realizovaných úloh.
Obsah cvičení • Grafická tvorba pohledu. • Pohled – T-SQL. • Kontrola aktualizací pohledu. • Kurzory.
Grafická tvorba pohledu (1) • Zobrazit v pohledu VProjektResitel seznam názvů projektů s jejich řešiteli zobrazenými v jednom sloupci (titul před, příjmení, jméno a titul za). Pohled bude tříděn podle příjmení řešitele.
Grafická tvorba pohledu (2) Zvolit sloupce, které se mají v pohledu zobrazit. • Zobrazit v pohledu VProjektResitel seznam názvů projektů s jejich řešiteli zobrazenými v jednom sloupci s titulem před, příjmenímjménem a titulem za). Pohled bude tříděn podle příjmení řešitele. Nezobrazí se zde žádná data!! Zrušit jednu z vazeb mezi tabulkami Ttitul a Tzamestnanec.
Grafická tvorba pohledu (3) Vložit tabulku TTitul dvakrát a změnit názvy. • Zobrazit v pohledu VProjektResitel seznam názvů projektů s jejich řešiteli zobrazenými v jednom sloupci s titulem před, příjmenímjménem a titulem za). Pohled bude tříděn podle příjmení řešitele. Nastavit třídění dle sloupce Příjmení a dalším krokem je...
Grafická tvorba pohledu (4) Sloučit sloupce do jednoho výsledného sloupce. • Zobrazit v pohledu VProjektResitel seznam názvů projektů s jejich řešiteli zobrazenými v jednom sloupci s titulem před, příjmenímjménem a titulem za). Pohled bude tříděn podle příjmení řešitele. TPred.t_TypTitulu + ' ' +z_Jmeno + ' ' + z_Prijmeni + ' ' + TZa.t_TypTitulu AS Řešitel Odstranit prázdné mezery. RTRIM(TPred.t_TypTitulu) + … pro všechny sloupce kromě posledního Proč jsou někteří řešitelé s hodnotou NULL, když každý projekt má svého řešitele?
Grafická tvorba pohledu (5) Microsoft SQL Server Management Studio (Object Explorer) SELECTTProjekt.p_Nazev, CASEWHENTZa.t_TypTituluIS NULL THEN (RTRIM(TPred.t_TypTitulu) + ' ' + RTRIM(TZamestnanec.z_Jmeno) + ' ' + RTRIM(TZamestnanec.z_Prijmeni)) ELSE (RTRIM(TPred.t_TypTitulu) + ' ' + RTRIM(TZamestnanec.z_Jmeno) + ' ' + RTRIM(TZamestnanec.z_Prijmeni) + ' ' + TZa.t_TypTitulu) ENDAS Řešitel FROMTProjektINNER JOIN TZamestnanecONTProjekt.p_Resitel = TZamestnanec.z_IC LEFT OUTER JOIN TTitulASTZaONTZamestnanec.z_TitulZa = TZa.t_IC LEFT OUTER JOIN TTitulASTPredONTZamestnanec.z_TitulPred = Pred.t_IC ORDER BY TZamestnanec.z_Prijmeni
Pohled – T-SQL • Vytvořte pohled VObory v T-SQL pro zobrazení všech oborů použitých ve všech řešených projektech, jak v části Hlavní obor (TProjekt.p_HlObor), tak v části Vedlejší obor (TProjekt.p_HlObor). Informace pro zobrazení jsou: název oboru. Obory jsou řazeny vzestupně dle jejich názvu. USE EvidProjektu; GO SELECT DISTINCT p_HlObor FROM TProjekt WHERE p_HlObor IS NOT NULL UNION SELECT DISTINCT p_VedlObor FROM TProjekt WHERE p_VedlObor IS NOT NULL; GO USE EvidProjektu; GO CREATE VIEW PObory AS SELECT DISTINCT p_HlObor AS 'Seznam oborů' FROM TProjekt WHERE p_HlObor IS NOT NULL UNION SELECT DISTINCT p_VedlObor FROM TProjekt WHERE p_VedlObor IS NOT NULL; GO
Kontrola aktualizací pohledů (1) • Vytvořte pohled VVlozitProjekt v T-SQL pro zobrazení projektů, které nepatří do skupiny projektů FRVŠ (p_Typ=4) a také nepatří do skupiny projektů pro Doplňkovou činnost (p_Podtyp=1). • Pohled bude zajišťovat kontrolu integrity vložení dat pouze pro projekty typu GAČR, projekty FRVŠ ani projekty typu Doplňková činnost není možné vkládat do databáze. USE EvidProjektu; GO CREATE VIEW VVlozitProjekt AS SELECT * FROM dbo.TProjekt WHERE p_Podtyp<>1 AND p_Typ<>4; GO
USEEvidProjektu; GO INSERT INTO VVlozitProjekt (p_ICislo,p_Nazev,p_ZahajRes,p_KonecRes,p_Poskytovatel,p_HlObor,p_VedlObor, p_Kateg,p_Typ,p_Podtyp,p_Resitel,p_DatumAktual,p_DatumPrijeti,p_Pracoviste) VALUES(3520520,'MujProjekt',1/1/2010,31/12/2012,'GA0','BC','JC', 1,4,5,'sku52',getdate(),NULL,'352'); GO Kontrola aktualizací pohledů (2) • Pohled bude zajišťovat kontrolu integrity vložení dat pouze pro projekty typu GAČR, projekty FRVŠ ani projekty typu Doplňková činnost není možné vkládat do databáze. • Vložit nový projekt, tj. do sloupců vložit konkrétní hodnoty: (p_ICislo=3520520,p_Nazev=’MujProjekt’,p_ZahajRes=’1/1/2010’,p_KonecRes=’31/12/2012’,p_Poskytovatel=’GA0’,p_HlObor=’BC’,p_VedlObor=’JC’, p_Kateg=1,p_Typ=4,p_Podtyp=5,p_Resitel=’sku52’,p_DatumAktual=getdate(),p_DatumPrijeti=NULL,p_Pracoviste=’352’). MujProjekt ??
Kontrola aktualizací pohledů (3) • Pohled bude zajišťovat kontrolu integrity vložení dat pouze pro projekty typu GAČR, projekty FRVŠ ani projekty typu Doplňková činnost není možné vkládat do databáze. USE EvidProjektu; GO ALTER VIEW VVlozitProjekt AS SELECT * FROM dbo.TProjekt WHERE p_Podtyp<>1 AND p_Typ<>4 WITH CHECK OPTION; GO USEEvidProjektu; GO INSERT INTO VVlozitProjekt (p_ICislo,p_Nazev,p_ZahajRes,p_KonecRes,p_Poskytovatel,p_HlObor,p_VedlObor, p_Kateg,p_Typ,p_Podtyp,p_Resitel,p_DatumAktual,p_DatumPrijeti,p_Pracoviste) VALUES(3520520,'MujProjekt',1/1/2010,31/12/2012,'GA0','BC','JC', 1,4,5,'sku52',getdate(),NULL,'352'); GO Msg 550, Level 16, State 1, Line 1 The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. The statement has been terminated.
Kurzory (1) • Provést změnu číslování projektů z původního čísla, např.:3526011na nové číslování 35206011, kde se rozšiřuje část pro uvedení koncového čísla roku na dvě místa. USE EvidProjektu; GO DECLARECURSChangeIDCURSOR FORWARD_ONLYREAD_ONLY FOR SELECT p_ICislo,p_ZahajRes,p_KonecRes,p_Pracoviste FROM Tprojekt OPENCURSChangeID Zde bude vložen vlastní kód kurzoru (viz další snímek). CLOSECURSChangeID DEALLOCATECURSChangeID
Kurzory (2) Další zdrojový text, který provádí kontrolu stávajícího čísla projektu v souvislosti s rokem zahájení: FETCH NEXT FROM CURSChangeID INTO @Cislo,@Zahaj,@Konec,@Prac WHILE @@FETCH_STATUS=0 BEGIN SET @RokIC=substring(convert(char,@Cislo),4,1) SET @Rok=substring(convert(char,year(@Zahaj)),4,1) IF @RokIC<>@Rok BEGIN PRINT 'Rok zahájení nesouhlasí s rokem v kódu projektu !!' PRINT 'Pro projekt s číslem: ' + rtrim(convert(char,@Cislo)) + ' bude opraven kod číslování.' PRINT '-----------------------------------------------------------' END Zde bude vložen další kód pro změnu i opravu čísla projektu (viz další snímek). FETCH NEXT FROM CURSChangeID INTO @Cislo,@Zahaj,@Konec,@Prac END DECLARE @Cislo int DECLARE @Zahaj smalldatetime DECLARE @Konec smalldatetime DECLARE @Prac smallint DECLARE @Rok smallint DECLARE @RokIC char(1)
Kurzory (3) Další zdrojový text, který provádí změnu a opravu čísel projektů: SET @NoveCislo1=convert(char,@Prac) SET @NoveCislo2=substring(convert(char,year(@Zahaj)),3,2) SET @NoveCislo3=substring(convert(char,@Cislo),5,3) SET @NoveCislo=rtrim(@NoveCislo1)+rtrim(@NoveCislo2)+rtrim(@NoveCislo3) PRINT 'Číslo projektu '+ rtrim(convert(char,@Cislo)) + ' je změněno na číslo:'+@NoveCislo SET @Cislo=convert(int,@NoveCislo) Aktualizaci dat v tabulce provést až po zjištění, zda je číslo dobře nahrazeno novým číslem !!! UPDATE TProjekt SET p_ICislo=@Cislo
Kurzory (4) – 1. část celého kódu USE EvidProjektu; GO /****** Object: Index [CIprojekt] Script Date: 04/02/2009 12:57:25 ******/ IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TProjekt]') AND name = N'CIprojekt') DROP INDEX [CIprojekt] ON [dbo].[TProjekt] WITH ( ONLINE = OFF ) /****** Object: Index [PK_TProjekt] Script Date: 04/02/2009 12:58:16 ******/ IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TProjekt]') AND name = N'PK_TProjekt') ALTER TABLE [dbo].[TProjekt] DROP CONSTRAINT [PK_TProjekt] DECLARE @Cislo int DECLARE @Zahaj smalldatetime DECLARE @Konec smalldatetime DECLARE @Prac smallint DECLARE @Rok smallint DECLARE @RokIC char(1) DECLARE @NoveCislo1 char(8),@NoveCislo2 char(8),@NoveCislo3 char(8),@NoveCislo char(25)
Kurzory (5) - 2. část celého kódu DECLARE CURSChangeID CURSOR FORWARD_ONLY FOR SELECT p_ICislo,p_ZahajRes,p_KonecRes,p_Pracoviste FROM TProjekt OPEN CURSChangeID FETCH NEXT FROM CURSChangeID INTO @Cislo,@Zahaj,@Konec,@Prac
WHILE @@FETCH_STATUS=0 BEGIN SET @RokIC=substring(convert(char,@Cislo),4,1) SET @Rok=substring(convert(char,year(@Zahaj)),4,1) IF @RokIC<>@Rok BEGIN PRINT 'Rok zahájení nesouhlasí s rokem v kódu projektu !!' PRINT 'Pro projekt s číslem: ' + rtrim(convert(char,@Cislo)) + ' bude opraven kod číslování.' PRINT '-----------------------------------------------------------' END SET @NoveCislo1=convert(char,@Prac) SET @NoveCislo2=substring(convert(char,year(@Zahaj)),3,2) SET @NoveCislo3=substring(convert(char,@Cislo),5,3) SET @NoveCislo=rtrim(@NoveCislo1)+rtrim(@NoveCislo2)+rtrim(@NoveCislo3) PRINT 'Číslo projektu '+ rtrim(convert(char,@Cislo)) + ' je změněno na číslo:'+@NoveCislo SET @Cislo=convert(int,@NoveCislo) UPDATE TProjekt SET p_ICislo=@Cislo FETCH NEXT FROM CURSChangeID INTO @Cislo,@Zahaj,@Konec,@Prac END CLOSE CURSChangeID DEALLOCATE CURSChangeID Kurzory (5)
Shrnutí • Pohled – je uložená definice dotazu. Pohled lze vytvořit: • v Management Studiu SQL Serveru. • v prostředí T-SQL. • Kontrola aktualizací pohledů: WITH CHECK OPTION. • Tvorba, změna nebo odstranění pohledu: CREATE, ALTER, DROP. • Kurzor – pro práci s řádky dat.