Adatbázis használat I.

1. Adatbázis használat I. 4. gyakorlat

2. Figyelem!!! A diasor ismerete nem helyettesíti a tankönyvet, és a példatárat. A diasor ismerete szükséges, de nem elégséges feltétele a minimum elégséges érdemjegynek!

3. Weboldalak www.orakulum.com ftp://analog.nik.uni-obuda.hu http://analog.nik.uni-obuda.hu:65111 Hallgato / Adatbazis2013

4. Interaktív környezet • Mit jelenthet az interaktív környezet • A lekérdezéseinket úgy írjuk meg, hogy a felhasználó adhat meg bizonyos paramétereket változókon keresztül.

5. Változók • Két fajtája létezik a változóknak • &vátozónév • Az ilyen módon létrehozott változó nem tárolódik el a munkamenetünkben, elfelejti amint lefutott a parancs. • &&változónév • Eltárolja az SQL*Plus a változó értékét amíg ki nem lépünk belőle vagy nem használjuk az UNDEFINE utasítást.

6. Változók • A &&változót felhasználói változónak is nevezzük • A felhasználói változókat a következőképpen tudjuk létrehozni • &&változónév • DEFINE változónév = érték (minidg CHAR típusú) • ACCEPT változónév [adattípus] [FORMAT 'formátummaszk'] [PROMPT 'üzenet'] [HIDE]

7. Változók • A változókra az SQL parancsokba úgy hivatkozunk, hogy a változonevet ' ' közé tesszük. • Ha egy változó nem létezik a hivatkozáskor, akkor annak bekéri az értékét az SQL*Plus és attól függően, hogy a kódban hogyan hivatkoztunk rá létrehozza a változót vagy felhasználói változót. • Pl.: SELECT * FROM emp WHERE ename = '&&valtozo'; • Létrehozza a valtozo nevű felhasználói változót(megnézhetjük az aktuális értékét a DEFINE paranccsal). • Pl2.: SELECT * FROM emp WHERE ename= '&valtozo2'; • Létrehozza a valtozo2 nevű változót, de ahogy lefutott a parncs el is felejti. • A felhasználói változók értékét mindig csak újabb DEFINE vagy ACCEPT parancsal változtathatjuk meg. Megjegyzés: Ha létezik '&&valtozo', akkor arra hivatkozhatunk '&valtozo' parancsal is.

8. DEFINE • DEFINE változó = érték • Létrehoz egy CHAR típusú változót. • DEFINE változó • Megjeleníti a változó értékét és típusát. • DEFINE • Megjeleníti az összes felhasználói változó értékét és típusát.

9. DEFINE (Feladat) • Hozzunk létre egy nev nevű változót és az értéke legyen MARTIN. • Kérdezzük le MARTIN minden adatát a változónk segítségével.

10. ACCEPT • Az SQL*Plus fejlesztésének későbbi szakaszába készült, emiatt nem csupán CHAR típusú változót tud létrehozni, hanem NUMBER és DATE változót is és ezt a hivatkozások során figyelembe kell vennünk. • Az ACCEPT létrehozza a felhasználói változónkat, egy üzenetet is küld a felhasználónak és képes a bevitelt elrejteni (pl.: jelszó).

11. ACCEPT • Szintaktikája • ACCEPT változónév [adattípus] [FORMAT 'formátummaszk'] [PROMPT 'üzenet'] [HIDE] • Adattípus • NUMBER • CHAR • DATE • FORMAT 'formátummaszk' • Megadja használandó formátummaszkokat (könyv 1. fejezet: Dátumok és számok formázott megjelenítése) • PROMPT 'üzenet' • Üzenetet jelenít meg a felhasználónak az adat megadása előtt • HIDE • Elrejti a felhasználó által megadott adatot (jelszó)

12. ACCEPT (Feladat) • Listázza a felhasználó által megadott dátum előtt belépett, és foglalkozású dolgozókat!

13. ACCEPT (Megoldás) ACCEPT datum DATE FORMAT 'YYYY-MM-DD' PROMPT 'Mi legyen a dátum?: 'ACCEPT munkakor PROMPT 'Milyen munkakör?: ' Select * fromempwherehiredate < TO_DATE('&datum','YYYY-MM-DD') AND upper(job) = upper('&munkakor');

14. ACCEPT (Feladat 2) • Írjon olyan programot, amely a felhasználó által kiadott lekérdező utasítást futtatja.

15. Megoldás 2 • ACCEPT utasitas PROMPT 'Adjon meg egy lekérdező utasítást: ' SELECT * FROM (&utasitas);

16. UNDEFINE • Már korábban említettük, hogy az ACCEPT a DEFINE és && egyaránt felhasználói változókat hoz létre és ezek megőrződnek az SQL*Plusban kilépésig vagy az UNDEFINE utasítás kiadásáig. • Az UNDEFINE kitörli a felhasználói változót. • Használata • UNDEFINE változónév

17. UNDEFINE (Feladat) • Kérdezzük le a változókat. • Módosítsuk az utasitasváltozó értékét. • Kérdezzük le az utasitasvaltozot. • Töröljük le az utasitas nevű változót.

18. UNDEFINE (Feladat) • Kérdezzük le a változókat. • DEFINE • Módosítsuk az utasitasváltozó értékét. • DEFINE utasitas = valami • Kérdezzük le az utasitasvaltozot. • DEFINE utasitas • Töröljük le az utasitas nevű változót. • UNDEFINE utasitas

19. PROMPT • Üzenet kiírására használják egy szkriptből. • Használata • PROMPT [szöveg] • Ha nem adunk meg szöveget akkor egy sort beszúr. • Vegyük észre, hogy itt nincsen ' ' jel közé zárva a szöveg, mint ACCEPTNÉL • Lehetőség van itt is változót használni • Ilyenkor viszont így kell hivatkozni a változóra • PROMPT szöveg &változó.Azaz a változónév után .-ot kell hogy tegyünk • Pl.: PROMPT Én a &_USER. nevű felhasználó vagyok

20. Az SQL*Plus környezet beállítása • Az aktuális beállításokat a SHOW ALL paranccsal kérhetjük le. • Ha csak egyre vagyunk kíváncsiak SHOW rendszerváltozó • Ezen változók értékeit a következőképpen módosíthatjuk • SET Rendszerváltozó Érték • A HELP SET paranccsal megnézhetjük mire állíthatjuk be az egyes változókat

21. Az SQL*Plus környezet beállítása • A számunkra érdekes rendszerváltozók: • Numwidth • Megadja a numerikus mezőszélességet. • SET Numwidth n • Feedback • Alapértelmezetten, ha a lista 6 sornál több sorból áll, akkor kiírja a sorok számát, ezt ki-, bekapcsolhatjuk, illetve átírhatjuk más sorszámra. • SET feedback {6|n|on|off} • Verify • Amikor változót használunk akkor kiírja a behelyettesítés értékét, ezzel a parancsal ezt kikapcsolhatjuk. • SET verify {on|off} • Linesize • A sor hosszának maximális számát adja meg. • SET linesize n

22. Formázások • Az SQL*Plusban a listánkat formázni is tudjuk bizonyos mértékig. • A formázásnak két szintje van • Lista szintű formázások • Oszlop szintű formázások

23. Lista szintű formázások • TTITLE ["Szöveg" | ON | OFF] • Minden oldal tetején megjelenő fejlécet adhatjuk meg, kapcsolhatjuk ki és be. • BTITLE ["Szöveg" | ON | OFF] • Ugyanaz mint a TTITLE csak láblécben jelenik meg. • BREAK [ON {oszlopnév |oszlopkifejezés} [ON {oszlopnév |oszlopkifejezés}]…] • Kiszűri az ismétlődő értékek megjelenését az egymást követő sorokban és sortöréssel tagolja az adatsorokat.

24. Lista szintű formázások (Feladat) • Állítsuk be a fejlécet, hogy a saját nevünk jelenjen meg. • Állítsuk be a láblécet, hogy a csoport kódja jelenjen meg. • Kérdezzük le az emp táblát részlegazonosítóval növekvően rendezve és egy részlegazonosítót csak egyszer írjon ki. • Kapcsoljuk ki a fej és láblécet

25. Lista szintű formázások (Megoldás) • TTITLE "Index Elek" • BTITLE "MM10" • BREAK ON deptno • SELECT * FROM emp ORDER BY deptno; • TTITLE OFF • BTITLE OFF

26. Oszlop szintű formázások COLUMN • Az oszlopok és fejlécének megjelenési formáját szabályozza. • Szintaktika: • COLUMN {oszlopnév | oszlopkifejezés} [FORMAT 'formátumaszk'] [HEADING "Szöveg"] [JUSTIFY {LEFT | CENTER | RIGHT}] • COLUMN oszlopnév {PRINT | NOPRINT | OFF |ON | CLEAR}

27. Oszlop szintű formázások COLUMN • [FORMAT 'formátumaszk'] • Megadja az oszlopadatok megjelenési formáját • Pl.: A15 azt jelenti, hogy a karaktersorozat számára 15 helyet hagyunk • Dátum és szám esetén az első fejezetben a dátum ok és számok formázott megjelenítése (36-37 oldal) található parancsok használhatóak

28. Oszlop szintű formázások COLUMN • [HEADING "Szöveg"] • A fejlécet ezzel beállíthatjuk felülírja az alapértelmezett oszlopnevet. • Speciális karakterek esetén szükséges a szöveg. • Sortörést | karakterrel tudunk beszúrni. • [JUSTIFY {LEFT | CENTER | RIGHT}] • Az oszlopfejléc igazítása

29. Oszlop szintű formázások COLUMN • NOPRINT / PRINT • Elrejti az oszlopot vagy megjeleníti azt. • OFF / ON • Kikapcsolja vagy bekapcsolja az oszlop formázását • CLEAR • Formázások törlése.

30. Oszlop szintű formázások COLUMN (Példa) • COLUMN empno NOPRINT • COLUMN job HEADING "Munkakör" JUSTIFY RIGHT • COLUMN HIREDATE FORMAT A15 JUSTIFY left • COLUMN sal FORMAT L9999 HEADING "A dolgozó|fizetése" JUSTIFY CENTER • Az L9999 azt jelenti, hogy írja ki a helyi devizanemet • Select * fromemp;

31. Formázások törlése • A formázásokat egyszerűen törölhetjük • CLEAR {BREAKS | COLUMNS | SCREEN} • BREAKS törli a break utasítás hatását • COLUMNS törli az összes oszlopformázást • SCREEN a képernyőt törli

32. Formázások törlése (Feladat) • Töröljünk minden formázást.

33. Formázások törlése (Megoldás) • CLEAR BREAKS • CLEAR COLUMNS • TTITLE OFF • BTITLE OFF

34. Házi feladat • Listázza ki a felhasználó által megadott dolgozó azonosítóját, jövedelmét, főnökének nevét és annak jövedelmét, részlegének helyét, valamint a dolgozó jövedelembeállási értékét (vagyis a jövedelmének és a legkisebb jövedelem különbsége). Használjon másodlagos oszlopneveket és formázásokat.

35. Megoldás SET FEEDBACK OFF COLUMN DOLGOZÓJÖVEDELME FORMAT 9999L SET verify off ACCEPT neve Prompt 'Dolgozó neve: ' SELECT d.enameAS dolazonosító, d.sal+NVL(d.comm,0) AS DOLGOZÓJÖVEDELME, dept.locAS részlegnév, fonok.enameAS főnökneve, fonok.sal+NVL(fonok.comm,0) AS Főnökfizetése, d.sal+NVL(d.comm,0) - (Select min(d.sal + nvl(d.comm,0)) fromemp d) AS jövedelembeállás FROM emp d, empfonok, dept WHERE d.mgr=fonok.empnoAND d.deptno=dept.deptno AND UPPER(d.ename)=UPPER('&neve');

36. Házi feladat (2) • Listázza ki a felhasználó által a nevével megadott dolgozó azonosítóját, jövedelmét, munkakörének nevét, továbbá a munkakörében dolgozók összjövedelmét, végül pedig a dolgozó jövedelembeállási értékét (vagyis jövedelméből kivonjuk az összjövedelmet). Használjon kifejező másodlagos oszlopneveket és formázásokat.

37. Megoldás 2 SET VERIFY OFF COLUMN osszjovedelem FORMAT 9999L COLUMN FONOKNEV JUSTIFY CENTER ACCEPT neve PROMPT 'A dolgozó neve: ' SELECT dolgozo.empno AS Azonosító, dolgozo.sal + NVL(dolgozo.comm,0) AS jövedelem, dolgozo.jobas munkakör, al.összjövedelemasosszjovedelem, fonok.enameasfonoknev, fonok. sal + nvl(fonok.comm,0) asfonokjovedelem, (dolgozo.sal + NVL(dolgozo.comm,0)) - al.összjövedelemas jövedelembeállás FROMempdolgozo, emp fonok, (selectjobas munkakor, sum(sal + nvl(comm,0)) As összjövedelem fromemp groupbyjob) al WHEREal.munkakor = dolgozo.job AND UPPER(dolgozo.ename) = UPPER('&neve') AND dolgozo.mgr = fonok.empno;