Adatbázisok használata 1. (9. gyakorlat)

1. Adatbázisok használata 1.(9. gyakorlat) 2012. tavaszi félév Vitéz Gergely

2. 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. Hivatkozási adattípusok • Hivatkozhatunk egy másik PL/SQL változó, tábla, mező típusára, így típus változás esetén a PL/SQL blokk deklarációját nem kell módosítani. • DECLARE • v_oszloptiptabla.oszlop%TYPE; • v_valttipv_oszloptip%TYPE; • v_sortiptabla%ROWTYPE; • BEGIN • … • Példa: Töltsük be a KING dolgozó fizetését egy hivatkozott típusú változóba, majd írassuk ki a konzolra!

4. Hivatkozási adattípusok • SET SERVEROUTPUT ON • DECLARE • fiz emp.sal%TYPE; • BEGIN • SELECT sal • INTO fiz • FROM emp • WHERE upper(ename) = 'KING'; • DBMS_OUTPUT.PUT_LINE('A főnök fizetése: ' || fiz || '$'); • end; • /

5. Rekord adattípus • DECLARE • TYPE tipusnev IS RECORD OF • (oszlop1 tipus1, • oszlop2 tipus2) • [NOT NULL] • INDEX BY BINARY_INTEGER; • valtozonevtipusnev; • BEGIN • … • Bővítsük az előző szkriptet úgy, hogy egy rekord típusba lekérjük a dolgozó kódját és nevét is!

6. Rekord adattípus • DECLARE • TYPE dolgtip IS RECORD • (empno NUMBER, • ename VARCHAR2(200), • sal NUMBER); • dolgfizdolgtip; • BEGIN • SELECT empno,ename,sal • INTO dolgfiz • FROM emp • WHERE upper(ename) = 'KING'; • DBMS_OUTPUT.PUT_LINE(dolgfiz.ename || '(' || dolgfiz.empno || ') fizetése: ' || dolgfiz.sal || '$'); • END; • /

7. Gyüjtőtábla típus • DECLARE • TYPE tipusnev IS TABLE OF • tipus • [NOT NULL] • INDEX BY BINARY_INTEGER; • valtozonevtipusnev; • BEGIN • …

8. Kurzorok • Soronként dolgozhatjuk fel velük egy lekérdezés eredményét • Explicit - felhasználó által generált kurzor • DECLARE • CURSOR kurzor IS • SELECT * FROM emp; • BEGIN • …

9. Kurzorok • SET SERVEROUTPUT ON • DECLARE • CURSOR kurzor IS • SELECT * FROM emp; • sor kurzor%ROWTYPE; • BEGIN • OPEN kurzor; --megnyitjuk • LOOP • FETCH kurzor INTO sor; --beolvasunk egy sort • EXIT WHEN kurzor%NOTFOUND; --kilépünk a ciklusból • dbms_output.put_line(sor.ename); • END LOOP; • CLOSE kurzor; --ne felejtsük el lezárni • dbms_output.put_line('vége'); • END; • /

10. Kurzorok egyszerűbben • FOR ciklussal egyszerűbben is feldolgozhatjuk • SET SERVEROUTPUT ON • DECLARE • CURSOR kurzor IS • SELECT * FROM emp; • BEGIN • FOR sor IN kurzor • LOOP • dbms_output.put_line(sor.ename); • END LOOP; • dbms_output.put_line('vége'); • END; • /

11. Kurzorok lusta fejlesztőknek • Megadhatjuk magát a lekérdezést is, így rejtett kurzort tudunk használni • SET SERVEROUTPUT ON • BEGIN • FOR sor IN (SELECT * FROM emp) • LOOP • dbms_output.put_line(sor.ename); • END LOOP; • dbms_output.put_line('vége'); • END; • /

12. ROWID • 18 karakter hosszú • Az adatbázisban minden tábla minden rekordjának van egy ilyen EGYEDI azonosítója • Egyértelműen tudunk hivatkozni vele egy tábla egy bizonyos sorára • (akkor is ha értékek alapján duplikátum lenne, pl. egy elsődleges kulcs nélküli emp táblában 2x szerepelne a KING) • Csináljunk egy duplikált táblát

13. Duplikátum törlése ROWID-val • createtabledolgasselect * fromemp; • insertintodolgselect * fromemp; • selectrowid, dolg.* fromdolg; • selectempno, min(rowid) minid fromdolg • groupbyempno • havingcount(*)> 1;

14. Duplikátum törlése ROWID-val • select minid from • (selectempno, min(rowid) minid fromdolg • groupbyempno • havingcount(*)> 1); • deletefromdolg • whererowidin • (select minid from • (selectempno, min(rowid) minid fromdolg • groupbyempno • havingcount(*)> 1));

15. Kurzorok… • FOR UPDATE záradék • Zárolja a lekérdezés eredményében található sorokat, így azokat szabadon módosíthatjuk, törölhetjük • CURRENT OF feltétel • A kurzor aktuális sorára hivatkozhatunk a segítségével

16. Komplex példa(ZH!) • Hozzon létre egy dolgozó nevű táblát az emp táblának a felhasználó által megadott telephelyű dolgozóiból, változtassa meg az mgr oszlop nevét partner névre, törölje a tartalmát, és töltse fel az alábbi módon értékkel: • Két dolgozó partnere lehet egymásnak, ha még egyiküknek sincs partnere, és a felhasználó által megadott értéknél nem több a fizetésük különbsége. A partnerek azonosítói szerepeljenek egymás partner oszlopában. (Senki önmagának nem lehet partnere, és a partnerség szimmetrikus tulajdonság, azaz ha A partnere B, akkor B partnere A)

17. Komplex példa 2/6 • set serveroutput on; • setverifyon; • droptabledolgozo; • create table dolgozo as select * from emp; • altertabledolgozo • rename column mgr to partner;

18. Komplex példa 3/6 • Minta currentof-ra • declare • cursor kurzor1 is select * fromdolgozofor update; • begin • for rekord1 in kurzor1 • loop • update dolgozo • set partner = 0 • wherecurrent of kurzor1; • end loop; • end; • /

19. Komplex példa 4/6 • acceptkulonbseg prompt 'A legnagyobb fizetés különbség: '; • declare • cursor kurzora is select * fromdolgozofor update; • cursorkurzorb is select * fromdolgozofor update; • rekordakurzora%rowtype; • rekordbkurzorb%rowtype; • kulnumber; • begin • open kurzora; • loop • fetch kurzora intorekorda; • exitwhenkurzora%notfound; • ifrekorda.partner=0 then • openkurzorb; • loop • fetchkurzorbintorekordb; • exitwhenkurzorb%notfound;

20. Komplex példa 5/6 • ifrekordb.partner=0 and rekorda.empno<>rekordb.empno • then • kul:= abs( (rekorda.sal+nvl(rekorda.comm,0))- • (rekordb.sal+nvl(rekordb.comm,0))); • if (kul<&kulonbseg) then • update dolgozo • set partner=rekorda.empno • wherecurrent of kurzorb; • update dolgozo • set partner=rekordb.empno • wherecurrent of kurzora; • closekurzorb; openkurzorb; • close kurzora; openkurzora; • exit; • end if; • end if; • end loop; • closekurzorb; • end if; • end loop; • close kurzora; • commit; • end; • /

21. Komplex példa 6/6 • Ellenőrzés: • selectempno, ename, partner, sal+ nvl(comm,0) asjovfromdolgozoorderbyename; • ZH-ban új sorokat is kell beszúrni a teszteléshez. Csak vége lett 

22. Köszönöm a figyelmet! Pénteken délután 17:00-tól konzultáció Igény szerint ma délután is http://www.orakulum.com http://jerry.gorextar.hu/ab1 vitez.gergely@spectronet.hu