Adatbziskezels 2 12 Gyakorlat Dr Pauler Gbor egyetemi

Adatbáziskezelés 2 12. Gyakorlat Dr. Pauler Gábor, egyetemi docens PTE-TTK BRTT, 7624 Pécs, Ifjúság u. 6. F 104 Mobil: 30/9015 -488, Skype: gjpauler E-mail: pauler@t-online. hu Facebook: Pécs Gazdinfo Adatbázis http: //www. facebook. com/groups/278606362188127/ ftp: //gamma. ttk. pte. hu/pub/pauler/Database 2/

A gyakorlat tartalma 11. Házi Feladat ellenőrzése: Webform készítése saját projektben 12 -1. Gyakorló feladat: Invoicing: Vevő és adatai létrehozása lekérdezésekkel SQL lekérdezések MS SQL projektekhez rendelése 12 -2. Gyakorló feladat: Invoicing: Vevő és adatai létrehozása tárolt eljárásokkal 12. Házi Feladat: Vevő törlése az Invoicing adatbázisból Szakirodalom

12 -1. Gyakorló feladat: Invoicing: Vevő és adatai létrehozása lekérdezésekkel 1 Eddig a Presentation 3 -ban normalizáltuk iskolapélda szőnyegszámlázó adatbázisunkat, majd a Practice 3 -ban megterveztük MS Visioban, és legeneráltuk az üres adatbázis szerkezetet MSSQL-ben, amit feltöltöttünk néhány mintarekorddal a Practice 6 -ban, és backupoltuk Invoicing. bak néven. A Presentation 10 -ben főűrlapos/Alűrlapos felhasználói felületet terveztünk hozzá MS Accessben, majd Practice 11 -ben. Net alól is. Most azt feltételezzük, hogy a felhasználói felület fejlesztéséhez nem áll rendelkezésre a Presentation 11 -ben ismertett Oracle Designerhez hasonló fejlett űrlap generátor, ami a felhasználói felület és az adatbázis közti minden adatmozgást automatikusan programoz SQL-ben (pl. sima PHP-ban programozzuk a GUI-t). Vagy. Net-ben dolgozunk, ami ezt csak az 1 táblás adatforrású űrlapokra képes megtenni. Vagy az automatikusan generált lekérdezések túl lassan futnak az adatbázis nagy mérete és adatforgalma miatt. Ekkor lehet szükség tárolt eljárások kézi programozásával történő tranzakciókezelési megoldásokra.

12 -1. Gyakorló feladat: Invoicing: Vevő és adatai létrehozása lekérdezésekkel 2 Vegyük pl. 1 új vevő (Buyers) azonosításának problémáját, ami minden kereskedőcégnél rutin feladat: a normalizált adatbázis tábla szerkezet miatt a GUI-ról bejövő adatokat több táblába kell lerakni, úgy hogy gondoskodni kell a köztük lévő relációk/kulcsok értékeinek megfelelő feltöltéséről: Countries: Kikeresni az országának a nevét a Country. Descr mezőben, ha még nincs olyan, új rekordként beszúrni, feljegyezni az új rekord automatikusan számozott Country. ID elsődleges kulcs értékét Street. Types: Kikeresni a közterület típusának a nevét a Street. Type. Descr mezőben, ha még nincs olyan, új rekordként beszúrni, feljegyezni az új rekord automatikusan számozott Street. Type. ID elsődleges kulcs értékét Zips: Kikeresni a Zip. ID irányítószámát adott Country. ID mellett, ha nincs ilyen, új rekordot beszúrni, ami a településnevet (City) is tárolja Addresses: Kikeresni, hogy adott Zip. ID és Street. Type. ID mellett létezik-e már a Street, House. Num, Building, Door, Floor, Line. Phone, Fax mezőkkel leírt cím. Ha igen, visszadani a létező Address. ID-t, és figyelmeztetést adni, hogy ezen a címen már vettünk fel vevőt. Ha nincs ilyen, új rekordot beszúrni a megadott adatokkal, feljegyezni az új rekord automatikusan számozott Address. ID elsődleges kulcs értékét. Buyers: Kikeresni, hogy adott Address. ID mellet létezik-e First. Name, Last. Name néven megadott vevő. Ha igen, visszaadni a Buyer. ID-t és figyelmeztetést adni, hogy már van ilyen vevő. Ha nem, új rekordot beszúrni a fenti adatokkal (plusz E-mail, Cell. Phone-al), feljegyezni az új rekord automatikusan számozott Buyer. ID elsődleges kulcs értékét.

12 -1. Gyakorló feladat: Invoicing: Vevő és adatai létrehozása lekérdezésekkel 3 Mivel ezek elég komplex feladatok, lássuk először az alkatrészeiket képező egyszerű SQL-lekérdezéseket: hogyan terveznénk meg őket az MS SQL szerkesztőjében? Minta-megoldás: Countries tábla kezelése SQL-ben: Elsődleges kulcsérték meghatározása adott ‘Országnév’-hez (NULL, ha nincs): SELECT dbo. Countries. Country. ID FROM dbo. Countries WHERE dbo. Countries. Country. Descr=‘Országnév’; Új ország beszúrása ‘Országnév’-en: INSERT INTO Countries(Country. Descr) VALUES (‘Országnév’); Ezen minta alapján, írjuk fel a hasonló célú lekérdezéseket a Street. Types, Zips, Addresses, Buyers táblák kezeléséhez is! A megoldás: 12 -1 Gyak. Megoldas. zip

SQL lekérdezések MS SQL projektekhez rendelése Mivel a tárolt eljárások programozása során elég sok *. SQL szövegfájlba mentődő lekérdezést fogunk írni az MS SQL szerkesztőjével, célszerű ezeket projektekké összefogni, ami biztosítja az adminisztrációjukat és a Kat hordozhatóságukat: t Az MS SQL Manager File|New|Project menüvel nyissunk új projektet. Itt megadhatjuk a megcélzott helyi/távoli szervert, azon egy adatbázist, és az elérési jelszavakat, valamint egy célkönyvtárat és fájlnevet, ahova a projekt adatai mentődnek (pl. 122 Megoldas. Project. zip). Ezután a View|Solution Explorer menüben kinyithatjuk a képernyő jobb oldalára a Solution Explorer panelt, Ahol a Queries|Jobbkatt|New Query menüvel adhatunk hozzá új lekérdezést. A projekt lekérdezései mind a projektnél megadott adatbázishoz próbálnak kapcsolódni futtatásukkor, de le is választhatók róla és átállíthatók más adatbázisra (Az MS SQL 2005 bosszantó hibája, hogy néha elhagyja a lekérdezéseknél az adatbázis kapcsolatot és újra be kell loginolni futtatás előtt) Korábban már mentett SQL lekérdezés *. SQL fájlja Project|Add Existing Item menüvel adható a projekthez (projekt könyvtárba simán bemásolással nem!!!) Katt Kat Jo Kabt b t t Kat t tt Ka Kat t

A gyakorlat tartalma 11. Házi Feladat ellenőrzése: Webform készítése saját projektben 12 -1. Gyakorló feladat: Invoicing: Vevő és adatai létrehozása lekérdezésekkel SQL lekérdezések MS SQL projektekhez rendelése 12 -2. Gyakorló feladat: Invoicing: Vevő és adatai létrehozása tárolt eljárásokkal 12. Házi Feladat: Vevő törlése az Invoicing adatbázisból Szakirodalom

K Ka att tt 12 -2. Gyakorló feladat: Invoicing: Vevő és adatai létrehozása tárolt eljárásokkal 1 Miután az alap SQL lekérdezéseket megírtuk, próbáljuk összefogni és paraméterezhetővé tenni őket tárolt eljárások segítségével. Szemben az adatbázismotor által mindig külön újra lefordított SQL lekérdezésekkel – a tárolt eljárás és a benne foglalt lekérdezések csak egyszer fordítódnak le, így sokkal gyorsabb! Minta megoldás: Countries: Kikeresni a vevő országának a nevét a Country. Descr mezőben, ha még nincs olyan, új rekordként beszúrni, feljegyezni az új rekord automatikusan számozott Country. ID elsődleges kulcs értékét Az MS SQL Manager Object Explorer|Databases|Invoicing|Programmability|Stored Procedures|Jobbkatt|New Stored Procedure menüvel új tárolt eljárást definiálunk: CREATE PROCEDURE Make. Or. Find. Country @Country. Descr varchar(16), --Országnév bemenő változóként @Country. ID int OUTPUT --Létezõ vagy új országkód kiadása AS BEGIN SET NOCOUNT ON; --Írtsd ki a feliratozást SELECT -- Keress létezõ országkódot @Country. ID = dbo. Countries. Country. ID FROM dbo. Countries WHERE dbo. Countries. Country. Descr=@Country. Descr; IF Is. Null(@Country. ID, 0)=0 BEGIN --Ha nincs ilyen ország. . . t INSERT INTO --Csinálj egyet, és… Kat Countries(Country. Descr) VALUES att K (@Country. Descr); SELECT --Keresd ki az automatikusan osztott kódját @Country. ID = dbo. Countries. Country. ID FROM dbo. Countries WHERE dbo. Countries. Country. Descr=@Country. Descr; END Ka GO tt

12 -2. Gyakorló feladat: Invoicing: Vevő és adatai létrehozása tárolt eljárásokkal 2 Az elkészült SQL kódot File|Save as… menüvel Make. Or. Find. Country. sql néven mentjük Project|Add new item menüvel felvesszük a projektünk lekérdezései közé Parse ( ) gombbal leellenőrizzük szintaktikailag. Mire kell itt figyelni: Ne felejtsük, hogy a szöveges konstansokat egyszeres idézőjelek közé kell rakni (pl. ‘Afganisztán’) Azon adatbázis tábla/mező neveket, amelyek speciális karaktereket tartalmaznak kettős idézőjelek közé kell rakni (pl. „E-mail”) Az eljárások kimenő változóihoz ne felejtsük odarakni az eljárás fejlécén, hogy OUTPUT, és más tárolt eljárásokból az EXEC paranccsal történő meghívásuknál is oda kell rakni az output paraméter után, hogy OUTPUT, különben az eljárás lefut, csak nem adja vissza a hívónak! Execute ( ) gombbal lefuttatjuk a Transact SQL scriptet, hogy létrehozza a az adatbázisban a lefordított tárolt eljárást. Mire kell itt figyelni: Az SQL script helyes adatbázisra legyen kapcsolva (ezt a projekt általában biztosítja) Helyes lefutás esetén nincs hibajelzés és az adatbázis objektum hierarchiájában a Stored Procedures pont alatt megjelenik a megadott néven a tárolt eljárásunk (nem az SQL fájl nevén, hanem Procedure nevén!)

12 -2. Gyakorló feladat: Invoicing: Vevő és adatai létrehozása tárolt eljárásokkal 3 Az új tárolt eljáráson Jobkatt|Execute Stored Procedure menüvel próbafuttatjuk a tárolt eljárást. Előjön egy beállítópanel, ahol megadhatjuk a bemenő paramétereit, és OK gombbal indítjuk. Ha hiba nélkül lefut, készít egy script-mintát, hogyan kellene a tárolt eljárást máshonnan EXEC-el hívni és táblázatos formában visszaadja a kimenő értékeit. Ha futásidejű hiba van, vagy nem azt csinálja, amit szeretnénk, Jobbkatt|Delete-vel töröljük le a tárolt eljárást, az SQL scriptben javítsuk ki a hibát, és a fent leírt módon generáljuk újra. Ne próbáljuk azonos néven törlés nélkül mégegyszer generálni, mert nem fogja engedni! Alternatív megoldásként Jobbkatt|Modify-ra újragenerálja a létrehozó SQL scriptjét módosító üzemmódban (ALTER PROCEDURE), és ezt lefuttatva is módosíthatjuk. Ez akkor jó, ha a tárolt eljárásra már más objektumok hivatkoznak, mert akkor nem lehetne egyszerűen törölni. Hátránya, hogy ilyenkor a módosított script automatikusan nem mentődik projektünkbe, kézzel kell belevenni. Az adatbázisban tévesen létrehozott/vagy próba rekordokat ne felejtsük el kézzel kitörölni a táblákból. Itt ügyelnünk kell, hogy a függőségi szabályokat törlés közben be kell tartanunk (mindig a több oldali táblából töröljük először a hivatkozó rekordot, és csak utána az 1 oldali táblából a hivatkozott rekordot) Ka tt Jo Kabt b t Ka tt J Kaobb tt tt Ka

12 -2. Gyakorló feladat: Invoicing: Vevő és adatai létrehozása tárolt eljárásokkal 4 Programozza le a fentiek alapján a következő tárolt eljárásokat, úgy hogy az egyre bonyolultabb törzs/tranzakció táblákat (pl. Zips, Addresses, Buyers ) kezelő eljárások meghívják magukban az egyszerűbb táblákat (pl. Countries, Street. Types) kezelő eljárásokat, és azok adják át a hívónak outputként eredményeiket (pl. Country. ID, Street. Type. ID, Address. ID): Street. Types: Kikeresni a közterület típusának a nevét a Street. Type. Descr mezőben, ha még nincs olyan, új rekordként beszúrni, feljegyezni az új rekord automatikusan számozott Street. Type. ID elsődleges kulcs értékét Zips: Kikeresni a Zip. ID irányítószámát adott Country. ID mellett, ha nincs ilyen, új rekordot beszúrni, ami a településnevet (City) is tárolja Addresses: Kikeresni, hogy adott Zip. ID és Street. Type. ID mellett létezik-e már a Street, House. Num, Building, Door, Floor, Line. Phone, Fax mezőkkel leírt cím. Ha igen, visszadani a létező Address. ID-t, és figyelmeztetést adni, hogy ezen a címen már vettünk fel vevőt. Ha nincs ilyen, új rekordot beszúrni a megadott adatokkal, feljegyezni az új rekord automatikusan számozott Address. ID elsődleges kulcs értékét. Buyers: Kikeresni, hogy adott Address. ID mellet létezik-e First. Name, Last. Name néven megadott vevő. Ha igen, visszaadni a Buyer. ID-t és figyelmeztetést adni, hogy már van ilyen vevő. Ha nem, új rekordot beszúrni a fenti adatokkal (plusz E-mail, Cell. Phone-al), feljegyezni az új rekord automatikusan számozott Buyer. ID elsődleges kulcs értékét. A megoldás: 12 -2 Megoldas. Project. zip, 12 -2 Megoldas. Database. zip

12. Házi Feladat: Vevő törlése az Invoicing adatbázisból Írjon tárolt eljárás(okat) az Invoicing adatbázisban, amelyek megoldják, hogy egy bemenő paraméterben megadott Buyer. ID-hez tartozó vevőt letörölnek az adatbázisból (feltételezzük, hogy még nincs olyan számla, ami erre avevőre készült). Ha a vevő Address. ID kód alatt futó címét más nem használja, az is törlődjön! A tárolt eljárások transact SQL kódját szöveges posztként töltse fel a tárgy Facebook-csoportjába! Ügyeljen rá, hogy minden tárolt eljárás neve, és be/kimenő paraméterlistájának változónevei az EHA kódja első 3 karakterével kezdődjön! (5 p) A megoldás: 12 -1 Hazi. Megoldas. zip

Szakirodalom Tárolt eljárások bevezető (angolul): http: //technet. microsoft. com/en-us/library/cc 505879. aspx http: //databases. about. com/od/sqlserver/a/storedprocedure. ht m Tárolt eljárások futási sebességének optimalizálása (angolul): http: //blog. sqlauthority. com/2010/02/16/sql-server-storedprocedure-optimization-tips-best-practices/