Az Oracle SQL 1.

1. Az Oracle SQL 1. Az első lépések

2. Történelem 1. • E. F. Codd, 1970: relációs adatmodell("A Relational Model of Data for Large Shared Data Banks", Communications of the ACM) • P. Chen, 1976: kiterjeszti a kulcsok halmazelméleti leírásával • IBM, 1976: adatbázisgép tervének közzététele • Alapja az „R” adatbázis • Kidolgozói Codd és Chen

3. Célkitűzések IBM-nél • A tárolás módja és a logikai adatszerkezet független legyen egymástól • Külön adatbázisnyelv, ami szabványosítja • az adatszerkezet definiálását • az adatok kezelését • az adatok biztonságára vonatkozó előírásokat • Az adatkezelő utasítások az „adatbevitel - módosítás - törlés” logikáját követik, a fizikai műveleteket elrejtik • Munkájuk eredménye a SEQUEL nyelv (Structured English Query Language) • tervezői: D. D. Chamberlin, R. F. Boyce

4. Történelem 2. • A SEQUEL név később SQL-re rövidül • Hosszú csend, nincs jelentős fejlesztés az IBM-nél (védik a piacon lévő termékeiket?) • Relational Software, 1979: az SQL első, kereskedelemben hozzáférhető megvalósítása • Új cégek jelennek meg saját relációs adatbáziskezelővel és SQL-lel: • 1983: Oracle (adatbáziskezelőjük: Oracle) • 1984: Relational Co. (adatbáziskezelőjük: Informix)

5. Történelem 3. • 1986: ANSI szabvány az SQL alaputasításaira (SQL-86, vagy SQL-1) • 1987: ISO szabvány az SQL alaputasításaira (SQL-87) • Új cégek, növekvő verseny, az IBM is erősít • 1989: második ISO szabvány az SQL-re, újabb területeket szabványosít (SQL-89) • 1992: harmadik ISO szabvány az SQL-re (SQL-92, vagy SQL-2)

6. Történelem 4. • 1996: a tárolt eljárások és függvények szabványosítása • 1999: ISO szabvány (SQL-1999, vagy SQL-3) • 2003: ISO szabvány (SQL-2003) • 2006: ISO szabvány (SQL-2006) • A szabványok kompromisszumok, csak a meglévő gyakorlatot szentesítik! Minden megvalósítás tartalmaz eltéréseket!

7. Illik tudni... • Az Oracle egy cég neve, 1983-ban alakult • Többféle programtermékük is van • Ezek közül a legismertebb az Oracle adatbázis-kezelő • Különböző operációs rendszerekre létezik • Az idők során több verziója is elkészült • Mi az Oracle 9i-t használjuk

8. Ismerkedési lehetőségek • Kipróbálás céljából a teljes rendszer letölthető ingyenesen az Oracle honlapjáról (www.oracle.com) • A teljes (angol nyelvű) dokumentáció elérhető az Interneten több helyen is • Nálunk a címe: http://www.ttk.pte.hu/oracle/ • Az Oracle Press és az O’Reilly könyvei

9. A „gyári” dokumentáció fontosabb könyvei • Error Messages • Master Glossary • Master Index • SQL Reference • SQL*Plus User’s Guide and Reference

10. Az SQL nyelv bemutatása

11. Az SQL jelentősége Az Oracle adatbázis csak SQL nyelvű utasításokkal érhető el!

12. Az SQL nyelv fontosabb formai jellemzői • A kulcsszavakban nincs különbség a kis- és nagybetű között • select, Select, SELECT egyenértékű • Az utasítások végén pontosvessző van • Az utasítások szabadon széttörhetők több sorra

13. Az utasítások szerkezete • Követik a beszélt nyelv logikáját, kiolvasva értelmes, kerek angol mondatokat kapunk • Az utasítások szakaszokra (záradék, clause) oszthatók • minden szakaszt jellemző kulcsszó vezet be • az egyes szakaszok pontosítják az utasítás tárgyát és a végrehajtás feltételeit

14. Példa SQL utasításra alaputasítás SELECT vevokod, nev1, telefon FROM vevok WHERE nev1 LIKE ‘K%’ ORDER BY nev1, vevokod; WHERE- szakasz: válogatás ORDER BY szakasz: a rendezés megadása

15. Az utasítások fajtái • Adatdefiníciós: CREATE, ALTER, DROP • DDL, data definition language • Adatkezelési: SELECT, INSERT, UPDATE, DELETE • DML, data manipulation language • Adatbiztonsági: GRANT, REVOKE • DCL, data control language • Kiegészítő utasítások az egyes megvalósításokban • a tárolás előírása • az adatmentés és a visszatöltés elvégzése • a működés körülményeinek beállítása

16. Create • Adatbázis objektum létrehozása • CREATE TABLE Szamla ( Szamlaszam NUMERIC(24), Tulajdonos VARCHAR(60), Nyitas DATE, Allapot VARCHAR(1), PRIMARY KEY (Szamlaszam) );

17. Alter • Adatbázis-objektum módosítása • ALTER TABLE Szamla MODIFY Szamlaszam VARCHAR(26);

18. DROP • Egy adatbázisbeli objektum (index) megszüntetése • DROP INDEX Szamla_1; • Egy adatbázisbeli objektum (tábla) megszüntetése • DROP TABLE egy_tabla;

19. COMMENT • Megjegyzést fűz egy adatbázis-objektumhoz • COMMENT ON TABLE Szamla IS 'Ügyfelek bankszámlaszámai'; COMMENT ON COLUMN Szamla.Allapot IS 'Számla állapota: "N" – Normál, "D" – Törölve';

20. Select • leggyakrabban használt utasítás • inkább deklaratívnak tekinthető, mint procedurálisnak • az adatok egy halmazát válogatja ki • a halmaz általában szekvenciálisan olvasható • különleges jelzés, EOF – End of File BOF – Beginning of File

21. Select • akiknek egynél több aktív bankszámlája van • SELECT COUNT(*), Tulajdonos FROM Szamla WHERE Allapot = 'N' GROUP BY Tulajdonos HAVING COUNT(*) > 1 ORDER BY Tulajdonos

22. Több Select • Az eredményhalmaz oszlopait kell itt felsorolni • SELECT Vevo.Nev, (SELECT SUM(Osszeg) FROM Rendeles WHERE VevoID = Vevo.ID) RendelesOsszeg

23. Összetett Select • a legnagyobb árbevételű munkáinkat • SELECT munka_szám FROM munka WHERE óraszám*óradíj = ( SELECT max(óraszám*óradíj) FROM munka );

24. Select/from • mely adatbázis-táblákból szeretnénk összegyűjteni az adatokat ? • SELECT * FROM Beteg, Kezeles

25. Select/from • minden beteghez csak a hozzá tartozó kezelést • SELECT * FROM Beteg KEY JOIN Kezeles • definiált külső kulcs a Kezeles tábláról a Beteg táblára !!!! • Az orcle ezt nem támogatja, még akkor sem, ha van külső kulcs

26. Select/Where • A Boole-algebra kifejezései használhatók, OR, AND és NOT operátorokkal

27. Select/Where • Szűrési feltételeket fogalmaz meg = szűkíti az eredmény halmazt • SELECT * FROM Beteg, Kezeles WHERE Beteg.ID = Kezeles.BetegID AND Kezeles.Datum = CURRENT DATE

28. Select/ GROUP BY • Egyes sorok összevonását, csoportosítását írja elő az eredménytáblában • SELECT COUNT(*), Tulajdonos FROM Szamla WHERE Allapot = 'N' GROUP BY Tulajdonos • Csoportosítás a tulajdonos alapján, COUNT(*) egy-egy csoport sorainak számát adja vissza, az összevonás előtt

29. Select/Having • Szűrés a csoportosítás utáni eredményhalmazra • SELECT COUNT(*), Tulajdonos FROM Szamla WHERE Allapot = 'N' GROUP BY Tulajdonos HAVING COUNT(*) > 1 • csak azok a csoportok jelennek meg, amelyek egynél több sorból lettek összevonva

30. Select/Order By • az eredményhalmaz rendezését adja meg SELECT * FROM Beteg ORDER BY Szuletes DESC születési dátum szerint sorba rendezve, elöl a legfiatalabbal

31. Case • CASE WHEN logikai vizsgálat THEN kifejezés ha igaz .. ELSE kifejezés ha az előzőekre nem illeszkedik • A logikai vizsgálat eredményétől függően vezérelhetjük, hogy mit szeretnénk az adott oszlopban látni • select app_id,budget_info_type,amount from acq_budget_info

32. Case • A lekérdezés transzponáltja • select APP_ID, max(net_income), max(gross_income), max(bonus) from ( select APP_ID, case when BUDGET_INFO_TYPE='net_income' then amount else null end as net_income, case when BUDGET_INFO_TYPE='gross_income' then amount else null end as gross_income, case when BUDGET_INFO_TYPE='bonus' then amount else null end as bonus, from acq_budget_info) xx group by APP_ID

33. Lekérdezési terv • Query plan • Az adatbázis kezelők eltérő algoritmusokat használnak (pl: * és @) • Az eltérések a változókezelés, elágazások, ciklusok kezelése, kivételkezelés stb. vannak • Gyakori a szabály-alapú (rule-based) (relációs adatbázis szerkezetét), és a költség-alapú (cost-based),(átlagát, szórását )

34. Insert • Adatokat ad hozzá egy táblához INSERT INTO Szamla (Szamlaszam, Tulajdonos, Nyitas, Allapot) VALUES (123456, 'H. Kovács Géza', '1996.05.14.', 'N');

35. Update • Módosítást hajtunk végre UPDATE Szamla SET Allapot = 'D' WHERE Szamlaszam = 123456;

36. Delete • Törlés végzünk DELETE FROM Beteg WHERE TAJ = '123 456 789';

37. Egyéb utasítások • Tudunk bonyolultabb algoritmusokat is írni Pl: például komoly adatkezelési feladatokat szeretnénk az ügyfél-oldalról a kiszolgáló-oldalra áthelyezni csak egyszerűsíteni akarjuk a programozást, pl a TAJ számból kiszedni a szóközöket

38. Jelentős különbségek SQL-ben • Az Oracle rendszereiben nincs NUMERIC, helyette a NUMBER-t kell használni • Az Oracle nem támogatja a VARCHAR-t, helyette a VARCHAR2-t javasolja • Az Oracle nem támogatja a LONG VARCHAR-t, helyette a CLOB-ot javasolja • Az Oracle-ben a DATE dátumot és időt is tartalmazhat, más rendszerekben ez csak dátum lehet

39. Jelentős különbségek SQL-ben • SELECT-FROM szakaszba írt összekapcsolási utasításokat, mások csak a SELECT-WHERE-ben fogadják el SELECT * FROM Beteg KEY JOIN Lelet SELECT * FROM Beteg, Lelet WHERE Beteg.ID = Lelet.BetegID

40. Jelentős különbségek SQL-ben • gyors keresésnél csak a teljesen pontosan beírt keresőkérdésre találják meg a választ, néhol figyelni kell a kis- és nagybetűkre, valamint az ékezetek helyes használatára (Oracle, Firebird) • Ahol egyenrangúak (Sybase Ase, Sybase ASA)

41. Jelentős különbségek SQL-ben • From nélküli Select SELECT SYSDATE SQL kiszolgálók esetében nem működik, mivel a FROM náluk kötelező nyelvi elem!! az Oracle minden adatbázisában szerepelteti a DUAL táblát, amelynek egyetlen rekordja van. Így Oracle SQL-ben ezt kell írnunk SELECT SYSDATE FROM DUAL

42. Az SQL utasítások feldolgozása SQL utasítás • Az alkalmazás egy SQL utasítást (szöveg!) küld az adatbázisba • Az adatbázis ellenőrzéseket végez: • szintaxis • a hivatkozott objektumok megléte, az adattípusok helyessége • jogosultság • Az adatbázis elvégzi a kért utasítást • Adatokat és / vagy állapot-információkat ad vissza adatbázis alkalmazás adatok

43. A kliens-server környezet • Az adatbáziskezelő egy szerveren fut (nálunk Linux alatt) • A használatához a kliens gépekre megfelelő programot kell telepíteni • megfelelő módon elküldi az SQL utasításokat • fogadja az adatbázisból érkező adatokat • Az Oracle által szállított hagyományos kliens program az SQL*Plus • Újabb, kényelmesebb az SQL Developer • Mindkettő többfajta operációs rendszerre is létezik

44. Az utasítások végrehajtásának követése • Az SQL utasítások végrehajtási módjáról és annak hatékonyságáról felvilágosítást lehet kérni • További információk a gyári dokumentációban:SQL*Plus User’s Guide and Reference,9. fejezet: Tuning SQL*Plus

45. Az Oracle munkakörnyezete

46. Objektumok egy adatbázisban 1. • Tábla (adattábla, table): az adatok tárolási helye • Nézet (nézettábla, view): • meglévő táblákból kiválogatott / származtatott adatokat „tartalmaz” (inkább: szolgáltat) • táblaként kezelhető • mint a választó lekérdezés Accessben • Index: az adatok gyors visszakeresését segítő segéd-információk • Sorozat (sequence): egyedi egész értékeket szolgáltat • hasonló szerep, mint a számláló adattípusnak Accessben

47. Objektumok egy adatbázisban 2. • Tárolt eljárások (function, procedure, package): PL/SQL nyelven írt, az adatbázisban tárolt és az adatbázis-szerveren futó eljárások / függvények / programcsomagok • Trigger: különleges PL/SQL eljárás • nem az alkalmazás hívja, hanem bizonyos adatbázis-események (pl. rekord beszúrása) hatására automatikusan elindul • ellenőrzésre, az integritás biztosítására használjuk • Az újabb Oracle verziók a PL/SQL mellett a Java használatát is támogatják

48. A DUAL tábla • Az Oracle specialitása • Egy sora és egy oszlopa van • Minden felhasználónak van rá SELECT jogosultsága • Jól használható számítások végrehajtatására vagy függvények kiértékelésére • SELECT 2+3 FROM DUAL; • SELECT USER, SYSDATE FROM DUAL;

49. A TNSNAMES.ORA fájl • Egyszerű szövegfájl • A hagyományos kliensprogramok esetében minden kliensgépen megtalálható(pl. a C:\oracle\ora92\network\Admin-ban) • Az adatbázisok nevéhez megadja a hálózati helyet • A karbantartásához külön program is van:Start menü / Programok / Oracle - OraHome92 / Configuration and Migration Tools / Net Manager

50. A SCOTT felhasználó • Az Oracle telepítésekor egy minta-adatbázis is telepíthető: • egy SCOTT nevű felhasználó TIGER jelszóval • néhány tábla adatokkal • Nagyon sok Oracle példa hivatkozik ezekre az adatokra