Transcript Az Oracle SQL 1.

Az Oracle SQL 1.
Az első lépések
2007. augusztus 9.
Markó Tamás, PTE TTK
1
A rádiótelefonokat
kérem
KIKAPCSOLNI!
2007. augusztus 9.
Markó Tamás, PTE TTK
2
Ajánlott irodalom
• Szelezsán János: SQL kézikönyv
(alapvetően ezt követjük, a példa-adatbázis
is innen való)
• George Koch, Kevin Loney: Oracle8i: teljes
referencia
2007. augusztus 9.
Markó Tamás, PTE TTK
3
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
2007. augusztus 9.
Markó Tamás, PTE TTK
4
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
5
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)
Markó Tamás, PTE TTK
6
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)
2007. augusztus 9.
Markó Tamás, PTE TTK
7
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!
2007. augusztus 9.
Markó Tamás, PTE TTK
8
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áziskezelő
• 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
2007. augusztus 9.
Markó Tamás, PTE TTK
9
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
2007. augusztus 9.
Markó Tamás, PTE TTK
10
A „gyári” dokumentáció
fontosabb könyvei
•
•
•
•
•
Error Messages
Master Glossary
Master Index
SQL Reference
SQL*Plus User’s Guide and Reference
2007. augusztus 9.
Markó Tamás, PTE TTK
11
Az SQL nyelv bemutatása
2007. augusztus 9.
Markó Tamás, PTE TTK
12
Az SQL jelentősége
Az Oracle adatbázis csak SQL
nyelvű utasításokkal érhető el!
2007. augusztus 9.
Markó Tamás, PTE TTK
13
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
2007. augusztus 9.
Markó Tamás, PTE TTK
14
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
2007. augusztus 9.
Markó Tamás, PTE TTK
15
Példa SQL utasításra
alaputasítás
SELECT vevokod, nev1,
telefon
FROM vevok
WHERE nev1 LIKE ‘K%’
ORDER BY nev1, vevokod;
WHEREszakasz:
válogatás
ORDER BY szakasz:
a rendezés megadása
2007. augusztus 9.
Markó Tamás, PTE TTK
16
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
2007. augusztus 9.
Markó Tamás, PTE TTK
17
A NULL érték
• Ismeretlen, határozatlan, hiányzó adatot jelöl
• Bármilyen adattípusú mező értéke lehet NULL
• Ha szerepel egy kifejezésben, akkor az egész
kifejezés értéke NULL, kivéve:
– TRUE OR NULL értéke TRUE
– FALSE AND NULL értéke FALSE
• Semmivel sem egyenlő (önmagával se!):
– X = NULL értéke mindig FALSE
– NULL = NULL értéke is FALSE
• Kezelése:
– feltételekben: pl. X IS NULL, X IS NOT NULL
2007.
9.
Markó
Tamás, PTE TTK
–augusztus
feldolgozásnál:
NVL
függvény
(pl. 0-val helyettesíthető)18
Az SQL utasítások feldolgozása
• Az alkalmazás egy SQL utasítást (szöveg!) küld az
adatbázisba
SQL utasítás
alkalmazás
adatok
adatbázis
• 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
2007. augusztus 9.
Markó Tamás, PTE TTK
19
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
2007. augusztus 9.
Markó Tamás, PTE TTK
20
SQL utasítások használata programból 1.
• Sok magasszintű nyelvbe beiktathatók SQL
utasítások megfelelő szintaktikával
• A befogadó (gazda, host) programban megfelelő
adatszerkezetek kellenek az adatcsere
lebonyolításához
• A forrásnyelvi programot egy előfordító
(precompiler) feldolgozza:
– az SQL utasításokat megfelelő gazdanyelvi
eljáráshívásokkal helyettesíti
– tisztán gazdanyelvi program keletkezik
2007. augusztus 9.
Markó Tamás, PTE TTK
21
SQL utasítások használata programból 2.
• Ezt a szokásos módon lefordítják
• Szerkesztéskor (link) az SQL-t feldolgozó
eljárásokat tartalmazó könyvtárat is hozzá kell
szerkeszteni
előford.
gazdanyelv ford.
gazdanyelv
+ SQL
gépi kód
link
futtatható
program
SQL
könyvtár
• Az Oracle a C/C++, COBOL, FORTRAN és PL/I
részére szállít előfordítót
2007. augusztus 9.
Markó Tamás, PTE TTK
22
SQL utasítások használata programból 3.
• A közösen használt változók deklarálása:
EXEC SQL INCLUDE SQLCA;
állapot-információ,
hibakód
EXEC SQL BEGIN DECLARE SECTION;
szokásos gazdanyelvi deklarációk
EXEC SQL END DECLARE SECTION;
• Az SQL utasítások azonosítása:
EXEC SQL … ;
2007. augusztus 9.
Markó Tamás, PTE TTK
23
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
2007. augusztus 9.
Markó Tamás, PTE TTK
24
Az SQL*Plus
2007. augusztus 9.
Markó Tamás, PTE TTK
25
Az SQL*Plus indítása a tantermekben
• Nincs mindegyik teremben installálva!
• Start menü / Programok / Oracle - OraHome92 /
Application Development / SQL Plus
• Bejelentkezés:
– Felhasználó: tanfxx (xx a gép száma, pl. tanf06)
– Jelszó: ugyanaz
– Bejelentkezési: oratanf (ez az adatbázis neve, ahova be
akarunk lépni)
• A bejelentkezésnél a kis- és nagybetűk
egyenrangúak
2007. augusztus 9.
Markó Tamás, PTE TTK
26
Kilépés az SQL*Plusból
• A harmadik sikertelen belépési kísérletnél
automatikusan leáll
• EXIT parancs
• QUIT parancs
• Az ablak bezárása
2007. augusztus 9.
Markó Tamás, PTE TTK
27
SQL - SQL*Plus - PL/SQL
• SQL:
– szabványos lekérdező nyelv
– az Oracle egy kiterjesztett SQL-t használ
• SQL*Plus:
– kliens program, egy ablak az SQL parancsok
beírására (Oracle specialitás)
• PL/SQL:
– „hagyományos” programozási nyelv pl. a tárolt
eljárások megírásához (Oracle specialitás)
2007. augusztus 9.
Markó Tamás, PTE TTK
28
Az SQL*Plus használata 1.
• Alapvetően SQL parancsok kiadására
• Saját parancsai is vannak (a környezet
beállítására)
• A kis- és nagybetűk egyenrangúak
• A parancsokat pontosvesszővel zárjuk
• Az SQL parancsok szabadon több sorra
törhetők
2007. augusztus 9.
Markó Tamás, PTE TTK
29
Az SQL*Plus használata 2.
• Fapados!!!
• A / az utolsó parancsot újra végrehajtja
• A korábbi parancsok javítása, újbóli
kiadása:
– van egy saját nehézkes editora
– a legjobb: az egérrel kijelölni és átmásolni
2007. augusztus 9.
Markó Tamás, PTE TTK
30
Az SQL*Plus testreszabása
• SELECT * FROM scott.emp;
– a rekordok két sorra tördelődnek
• SET LINESIZE 120;
– a sor hosszát 120 karakterre állítja
– most felesleges oszlopfejléceket kapunk 10 rekord
után
• SET PAGESIZE 20;
– csak 20 rekord kiírása után kapunk oszlopfejlécet
2007. augusztus 9.
Markó Tamás, PTE TTK
31
Az SQL Developer
2007. augusztus 9.
Markó Tamás, PTE TTK
32
Mi is az SQL Developer?
• Fejlesztők és adatbázis-adminisztrátorok részére
készített kliens oldali eszköz
• Az adatbázis objektumainak áttekintésére
• SQL utasítások kényelmes kiadására
• PL/SQL-ben írt programok fejlesztésére
– a forráskód szerkesztésére
– a programok futtatására
– hibakeresésre
• Javaban készült, csak Oracle 9i-től kezdve használható
– Windowsra, Linuxra, MacOS-re
2007. augusztus 9.
Markó Tamás, PTE TTK
33
Hogy juthatunk hozzá?
• Nem része az adatbáziskezelővel szállított
Oracle Client programgyűjteménynek
• Önálló termék
• Ingyen letölthető az Oracle honlapjáról és
szabadon felhasználható
2007. augusztus 9.
Markó Tamás, PTE TTK
34
Az SQL Developer indítása
• Nem látszik a Start menüben a programok
között
– pl. az Intézőből indítható dupla kattintással
• Indításkor nem jön létre automatikusan a
kapcsolat az adatbázissal!
– bejelentkezés: a Connections fülnél a kapcsolat
megnyitásával
• Kilépés: a szokásos módon
2007. augusztus 9.
Markó Tamás, PTE TTK
35
Új kapcsolat létrehozása
• Kattintás az eszköztár New… ikonján
• A felnyíló ablakra OK
• A következő párbeszédablakban:
–
–
–
–
–
–
Connection name: kifejező nevet adunk
Username: értelemszerűen
Password: értelemszerűen
Hostname: oracle.szk
Port: 1521
SID: oraokt
2007. augusztus 9.
Markó Tamás, PTE TTK
36
Az SQL Developer leírása
• Csak angolul!
• A saját menüjében
(Help / Table of Contents)
• Az Oracle honlapján egyéb általános
ismertetők is találhatók
2007. augusztus 9.
Markó Tamás, PTE TTK
37
Fő részei
•
•
•
•
Navigátor (Connections)
SQL Worksheet
Táblázatos adatlekérdező és –módosító felület
Forráskód-szerkesztő:
– tárolt programok szerkesztője
– adatbázis-triggerek szerkesztője
• PL/SQL futtató felület
– a kód végrehajtása
– nyomkövetés
• Listázó (Reports)
2007. augusztus 9.
Markó Tamás, PTE TTK
38
A navigátor
a már definiált
adatbázis-kapcsolatok
a + megnyomásával
megnyílik a lista
az elérhető
objektumok
2007. augusztus 9.
Markó Tamás, PTE TTK
39
Az SQL Worksheet
a parancsok
beírása
az eredmény
megjelenítése
2007. augusztus 9.
Markó Tamás, PTE TTK
40
Az Oracle munkakörnyezete
2007. augusztus 9.
Markó Tamás, PTE TTK
41
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
2007. augusztus 9.
Markó Tamás, PTE TTK
42
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ázisesemé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
2007. augusztus 9.
Markó Tamás, PTE TTK
43
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;
2007. augusztus 9.
Markó Tamás, PTE TTK
44
A TNSNAMES.ORA fájl
• Egyszerű szövegfájl
• A hagyományos kliensprogramok esetében
minden kliensgépen megtalálható
(nálunk 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
2007. augusztus 9.
Markó Tamás, PTE TTK
45
A definiált felhasználók
• Az ALL_USERS view tartalmazza az
adataikat
• USER_USERS: csak a saját adataink, de
több (pl. hogy meddig érvényes a login
nevünk)
• Nagyon sok rendszer-view-nak van egy
ALL_… és egy USER_… változata
• Ezek a SYS nevű felhasználó tulajdonában
vannak
2007. augusztus 9.
Markó Tamás, PTE TTK
46
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
2007. augusztus 9.
Markó Tamás, PTE TTK
47
Mások tábláinak megnézése
• ALL_TABLES rendszer-view:
– minden olyan tábla adatait taralmazza, amihez
hozzáférhetünk
– SELECT table_name FROM all_tables
WHERE owner=‘SCOTT’;
• A tábla neve előtt meg kell adni a
tulajdonost:
– SELECT * FROM scott.emp;
2007. augusztus 9.
Markó Tamás, PTE TTK
48
A DEPT tábla
• A tábla szerkezetének megnézése:
– DESCR scott.dept;
• DEPT: department (részleg)
– DEPTNO: department number (a részleg
száma)
– DNAME: department name (a részleg neve)
– LOC: location (helység)
2007. augusztus 9.
Markó Tamás, PTE TTK
49
Az EMP tábla
• EMP: employee, alkalmazott
–
–
–
–
–
–
–
–
EMPNO: employee number, az alkalmazott kódja
ENAME: employee name, az alkalmazott neve
JOB: munkakör
MGR: manager, a főnökének a kódja
HIREDATE: belépési dátum
SAL: salary, fizetés
COMM: commission, ???
DEPTNO: department number (a részleg száma)
2007. augusztus 9.
Markó Tamás, PTE TTK
50
A példa-adatbázis szerkezete
2007. augusztus 9.
Markó Tamás, PTE TTK
51
A példa-adatbázis szerkezete
• A Stolnicki-könyvből átvéve, egyszerűsítve
• A mezőtípusok az Oracle lehetőségeihez
igazítva
2007. augusztus 9.
vevők
rendelések
cikktörzs
a rendelések
tételei
Markó Tamás, PTE TTK
52
A vevők adatai (VEVOK tábla)
Mezőnév
Vevokod
Nev1
Nev2
Nev3
Telefon
Telefax
Orszag
Ir_szam
Varos
Utca_hsz
Feldolg_dat
2007. augusztus 9.
Adattípus
VARCHAR2(8)
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(20)
VARCHAR2(20)
VARCHAR2(40)
VARCHAR2(40)
VARCHAR2(40)
VARCHAR2(40)
DATE
Leírás
A vevő kódja. Nem üres! Kulcs!
A vevő nevének 1. része. Nem üres!
A vevő nevének 2. része.
A vevő nevének 3. része.
Telefonszám
Telefax-szám
Levélcím - ország
Levélcím – irányítószám
Levélcím – város
Levélcím – utca, házszám
A feldolgozás dátuma
Markó Tamás, PTE TTK
53
A rendelések adatai (RENDEL tábla)
Mezőnév
Rszam
Vevokod
Fizetve
Penznem
Adattípus
NUMBER(6)
VARCHAR2(8)
NUMBER(12, 2)
VARCHAR2(3)
Fiz_mod
Fiz_hi
Feldolg_kod
Feldolg_dat
VARCHAR2(4)
DATE
VARCHAR2(8)
DATE
2007. augusztus 9.
Leírás
A rendelés sorszáma. Nem üres! Kulcs!
A vevő kódja. Nem üres! Idegen kulcs!
A vételár már kifizetett része. Nemnegatív!
A kifizetett összeg pénzneme. Csak HUF, Ft, $, USD,
DEM, ATS, YPN, EUR lehet!
Fizetési mód. Csak k.p., cs., v., akr. lehet.
Fizetési határidő
A feldolgozó személy kódja. Nem üres!
A feldolgozás dátuma. Nem üres!
Markó Tamás, PTE TTK
54
A rendelések tételei (RTETEL tábla)
Mezőnév
Rszam
Adattípus
NUMBER(6)
Sorszam
NUMBER(3)
Cikkszam
Mennyiseg
Egysegar
Szall_hi
Feldolg_dat
NUMBER(13)
NUMBER
NUMBER(10, 2)
DATE
DATE
2007. augusztus 9.
Leírás
A rendelés sorszáma. Nem üres! A tétel sorszámával
együtt kulcs! Idegen kulcs!
A tétel sorszáma. Nem üres! A rendelés sorszámával
együtt kulcs!
Cikkszám. Nem üres! Idegen kulcs!
Az áru mennyisége. Pozitív!
Egységár. Nemnegatív!
Szállítási határidő
A feldolgozás dátuma.
Markó Tamás, PTE TTK
55
A cikkek adatai (CIKK tábla)
Mezőnév
Cikkszam
Cikknev
M_egyseg
ITJ_kod
Cikkcsop
Egysegar
Atlegysegar
Feldolg_dat
Feldolg_kod
Szoveg
2007. augusztus 9.
Adattípus
NUMBER(13)
VARCHAR2(40)
VARCHAR2(4)
VARCHAR2(12)
NUMBER(3)
NUMBER(10, 2)
NUMBER(10, 2)
DATE
VARCHAR2(8)
VARCHAR2(240)
Leírás
Cikkszám. Nem üres! Kulcs!
A cikk neve. Nem üres!
Mennyiségi egység (pl. kg, liter, stb.)
ITJ-kód
A cikkcsoport kódja
Ajánlati egységár
Átlagos eladási egységár
A feldolgozás dátuma. Nem üres!
A feldolgozást végző személy kódja
Megjegyzés
Markó Tamás, PTE TTK
56