ADATBÁZIS HASZNÁLAT I. 4. gyakorlat
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!
Weboldalak www.orakulum.com www.jerry.gorextar.hu/ab1 www.gorex.gorextar.hu/ab1 www.gazdinfo.hu
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.
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.
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]
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';
Pl2.: SELECT * FROM emp WHERE ename = '&valtozo2';
Létrehozza a valtozo nevű felhasználói változót (megnézhetjük az aktuális értékét a DEFINE paranccsal). 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.
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
típusát.
az összes felhasználói változó értékét és
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.
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ó).
ACCEPT
Szintaktikája ACCEPT változónév [adattípus] [FORMAT 'formátummaszk'] [PROMPT 'üzenet'] [HIDE] Adattípus
FORMAT 'formátummaszk'
Megadja használandó formátummaszkokat (könyv 1. fejezet: Dátumok és számok formázott megjelenítése)
PROMPT 'üzenet'
NUMBER CHAR DATE
Üzenetet jelenít meg a felhasználónak az adat megadása előtt
HIDE
Elrejti a felhasználó által megadott adatot (jelszó)
ACCEPT (Feladat)
Listázza a felhasználó által megadott dátum előtt belépett, és foglalkozású dolgozókat!
ACCEPT (Megoldás) ACCEPT datum DATE FORMAT 'YYYY-MM-DD' PROMPT 'Mi legyen a dátum?: ' ACCEPT munkakor PROMPT 'Milyen munkakör?: ' Select * from emp where hiredate < TO_DATE('&datum','YYYY-MM-DD') AND upper(job) = upper('&munkakor');
ACCEPT (Feladat 2)
Írjon olyan programot, amely a felhasználó által kiadott lekérdező utasítást futtatja.
Megoldás 2
ACCEPT utasitas PROMPT 'Adjon meg egy lekérdező utasítást: ' SELECT * FROM (&utasitas);
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
UNDEFINE (Feladat)
Kérdezzük le a változókat. Módosítsuk az utasitas változó értékét. Kérdezzük le az utasitas valtozot. Töröljük le az utasitas nevű változót.
UNDEFINE (Feladat)
Kérdezzük le a változókat. DEFINE
Módosítsuk az utasitas változó értékét. DEFINE
Kérdezzük le az utasitas valtozot. DEFINE
utasitas = valami utasitas
Töröljük le az utasitas nevű változót. UNDEFINE
utasitas
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
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
Az SQL*Plus környezet beállítása
A számunkra érdekes rendszerváltozók:
Numwidth
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
Megadja a numerikus mezőszélességet. SET Numwidth n
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
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
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 TITLE 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.
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
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
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}
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
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
Oszlop szintű formázások COLUMN
NOPRINT / PRINT
OFF / ON
Elrejti az oszlopot vagy megjeleníti azt. Kikapcsolja vagy bekapcsolja az oszlop formázását
CLEAR
Formázások törlése.
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 * from emp;
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
Formázások törlése (Feladat)
Töröljünk minden formázást.
Formázások törlése (Megoldás)
CLEAR BREAKS CLEAR COLUMNS TTITLE OFF BTITLE OFF
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.
Megoldás SET FEEDBACK OFF COLUMN DOLGOZÓJÖVEDELME FORMAT 9999L SET verify off ACCEPT neve Prompt 'Dolgozó neve: ' SELECT d.ename AS dolazonosító, d.sal+NVL(d.comm,0) AS DOLGOZÓJÖVEDELME, dept.loc AS részlegnév, fonok.ename AS 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)) from emp d) AS jövedelembeállás FROM emp d, emp fonok, dept WHERE d.mgr=fonok.empno AND d.deptno=dept.deptno AND UPPER(d.ename)=UPPER('&neve');
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.
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.job as munkakör, al.összjövedelem as osszjovedelem, fonok.ename as fonoknev, fonok. sal + nvl(fonok.comm,0) as fonokjovedelem, (dolgozo.sal + NVL(dolgozo.comm,0)) - al.összjövedelem as jövedelembeállás FROM emp dolgozo, emp fonok, (select job as munkakor, sum(sal + nvl(comm,0)) As összjövedelem from emp group by job) al WHERE al.munkakor = dolgozo.job AND UPPER(dolgozo.ename) = UPPER('&neve') AND dolgozo.mgr = fonok.empno;
