Az oracle sql

Négy résznyelve bontható:

lekérdezõ QUERY Language

adatdefiníciós DDL (Data Definition Language)

adatmanipulációs DML (Data Manipulation Language)

adatvezérlõ DCL (Data Control Language

SQL parancsok

Szerkesztõ parancsok

Ábra Az aktuális buffer és a külsõ editor

Szükséges alapfogalmak

Literál

A CHAR, DATE, NUMBER standard ORACLE adattípusok értékeinek konstans alakja.

Pl: 'karaktersorozat'

-123.45

'01-JAN-97'

Kifejezés

Literál, változó, függvény, mezõnév magában is, vagy ezeknek operátorokkal való összekapcsolása kifejezést alkot.

A kifejezésnek értéke van: pl. egy szám, szöveg, dátum, vagy igaz, hamis.

Az igaz, hamis értékû kifejezést logika 818c24i i kifejezésnek, vagy feltételnek hívjuk.

Változó

Neve van

Értéket adhatunk neki (DEF[INE] változó = érték, vagy a @|RUN parancsfájl p1[ p2]. paraméterek segítségével, lásd 2.2 részben)

Hivatkozhatunk rá (ACCEPT vagy DEFINE által létrehozott változóra &változónév, az @|RUN parancsfájl p1[ p2]. paraméterekre &1, &2 stb. lásd 2.2)

A változóknak csak literálokat adhatunk értékül. (Lásd 2.2 rész.)

Pl:'&varos'

Operátorok

Lekérdezõ

Logikai

Numerikus

Relációs vagy összehasonlító

Dátumra

dátumkifejezés - dátumkifejezés è napok száma a két dátum között

Sztringekre

karakterkifejezés || karakterkifejezés è Konkatenáció

QUERY nyelv

Projekció megvalósítása

SELECT [DISTINCT] kifejezés [aliasnév][, kifejezés [aliasnév]].

FROM táblanév;

SELECT Vnev, Unev, lakcim FROM olvaso;

SELECT * FROM konyv;

SELECT * FROM tab; (csak az Oracle-nél)

Szelekció megadása

SELECT [DISTINCT] kifejezés [aliasnév][, kifejezés [aliasnév]].

FROM táblanév

WHERE keresési_feltétel;

Keresési feltétel (logikai kifejezés)

egyszerû összehasonlítás

összehasonlítás egy halmaz elemeivel

összehasonlítás NULL értékkel

összetett keresési feltétel

Egyszerû összehasonlítás

oszlopnév relációs_operátor kifejezés|konstans

Összehasonlítás egy halmaz elemeivel

oszlopnév összehasonlító_operátor halmaz_definíció

Összehasonlítás NULL értékkel

oszlopnév IS NULL

Összetett keresési feltételek

A használható operátorokról bõvebben a 2.3.4 részben olvashatunk.

SELECT isbn,cim, kiad_dat FROM konyv WHERE kiad_dat BETWEEN AND

SELECT * FROM olvaso WHERE okod IS NULL;

A kiválasztott sorok rendezése

SELECT [DISTINCT] kifejezés [aliasnév][, kifejezés [aliasnév]].

FROM táblanév

[WHERE keresési_feltétel]

ORDER BY kifejezés [DESC] [, kifejezés[DESC]].;

SELECT isbn, cim,kiad_dat FROM konyv

WHERE kiad_azon='K001' /* MSACCESS-ben ' helyett "*/

ORDER BY cim;

SELECT isbn, lelt_szam,kolcs_e FROM peldany

ORDER BY isbn, lelt_szam;

Csoportok képzése

SELECT [DISTINCT] kifejezés [aliasnév][, kifejezés [aliasnév]].

FROM táblanév

[WHERE keresési_feltétel]

GROUP BY kifejezés [,kifejezés].

[HAVING csopkiv. feltétel]

[ORDER BY kifejezés [DESC] [, kifejezés[DESC]].];

Csoportfüggvények (Oracle-nél és az Access-nél is ugyanaz)

A függvények NUMBER típusú adatokra alkalmazhatók, kivéve a MAX(), MIN(), COUNT() függvényeket, amelyek CHAR és DATE típusú adatokra is mûködnek.

SELECT okod, min(beir_dat), max(beir_dat), COUNT(*)

FROM olvaso

GROUP BY okod

HAVING COUNT(*)>1;

Sztringátalakító függvények

Aritmetikai függvények

egyéb mûveletek:*,/,+,-

Dátumkezelõ függvények

Az NVL függvény

NVL(oszlopkif, kif) =oszlopkif, ha az nem NULL, egyébként =kif

JOIN

SELECT [DISTINCT] kifejezés [aliasnév][, kifejezés [aliasnév]].

FROM táblanév [aliasnév], táblanév [aliasnév]

WHERE kapcs_oszlop1 összehas_operátor kapcs_oszlop2 AND további_feltétel

[GROUP BY kifejezés [,kifejezés].]

[HAVING csopkiv_feltétel]

[ORDER BY kifejezés [DESC] [, kifejezés[DESC]].];

Outer Join(+) azt a táblát egészíti ki NULL értékekkel, amely a kapcsoló oszlop mellett van.

Listázzuk ki azokat, akik késõbb iratkoztak be, mint GIPSZ JAKAB.

Pl: Listázzuk ki, hogy az egyes olvasóknál hány könyv van.

Egymásbaágyazott lekérdezések

Az elsõ SELECT WHERE részében újabb SELECT, össz. 15.

A beágyazott SELECT egyszerre több rekordot is visszaadhat. Ezek kezelésére használhatók a következõk:

ANY

ALL

EXISTS

A belsõ SELECT-bõl átvehetünk több oszlopot is.

Listázzuk ki azokat, akik késõbb iratkoztak be, mint GIPSZ JAKAB.

Listázzuk ki azon könyveket (ISBN, cim), amelyek Gipsz Jakabnál vannak.

Listázzuk ki azokat a könyveket, amelyeknek az ára nagyobb a könyv kiadója által kiadott könyvek átlagáránál.

lelt_szam isbn cim ar kiad_azon

L001      100001 TUSKEVAR 1100 K002

L002      100001 TUSKEVAR 1100 K002

L003      100001 TUSKEVAR 1150 K002

L004      100002 EGRI CSILLAGOK 800 K001

L005      100002 EGRI CSILLAGOK 800 K001

L006      100003 KOSZIVU EMBER FIAI 1200 K001

Pl: Lisázzuk ki azokat a mûveket, amelyek kölcsönözhetõk.

SELECT szerzo,cim

FROM konyv

WHERE isbn in (select isbn From peldany

where kolcs_e=1);

Listázzuk ki azokat a mûveket, amelyeket Londonban adtak ki, és van kölcsönözhetõ példány belõlük.

Táblákban kódolt hierarchiák

SELECT  [ALL] | [DISTINCT] ,kifejezés [aliasnév]]...

FROM táblanév

CONNECT BY PRIOR oszlopnév1= oszlopnév2

START WITH oszlopnév=kifejezés;

A CONNECT BY részben adható meg a hierarchiát kódoló két oszlop neve (az oszlopnév1 az alacsonyabb, míg a oszlopnév2 a magasabb szinten lévõ adat a hierarchiában).

A visszakeresés kiindulási pontját a START WITH után lehet megadni.

Példa:

Listázzuk ki NAGY KLARA összes beosztottját!

SELECT LPAD(' ', 2*LEVEL)||VNEV||UNEV STRUKTURA,LEVEL,D_AZON,FONOK

FROM DOLGOZO

CONNECT BY PRIOR D_AZON = FONOK START WITH VNEV='NAGY' AND UNEV='KLARA';

STRUKTURA LEVEL D_AZON FONOK

-------- ----- ------ -----

NAGY KLARA 1 D01

KISS TEREZ 2 D02 D01

SZILARD ISTVAN 3 D04 D02

BARNA PETER 2 D03 D01

KEREK EMIL 3 D05 D03

FUTO ERZSEBET 2 D06 D01

Listázzuk ki NAGY KLARA összes közvetlen beosztottját!

SELECT LPAD(' ', 2*LEVEL)||VNEV||UNEV STRUKTURA,LEVEL,D_AZON,FONOK

FROM DOLGOZO

WHERE level=2

CONNECT BY PRIOR D_AZON = FONOK START WITH VNEV='NAGY' AND UNEV='KLARA';

Számítsuk ki a különbözõ vezetõi szinteken az átlagfizetést!

SELECT LEVEL,AVG(FIZETES)

FROM DOLGOZO

CONNECT BY PRIOR D_AZON = FONOK START WITH VNEV='NAGY' AND UNEV='KLARA'

GROUP BY LEVEL;

feladatok

Határozzuk meg, hány londoni kiadó van az adatbázisunkban.

Határozzuk meg, kik azok a debreceniek, akik 1990 februárjában iratkoztak be.

Határozzuk meg, hány darab kölcsönözhetõ, és hány darab köteles példány van a könyvtárunkban.

Határozzuk meg, hány darab 1994 január 1-tõl régebbi kölcsönözhetõ mû (létezik kölcsönözhetõ példánya) van a könyvtárunkban.

Melyik kiadótól van a legrégebbi könyvünk.

Listázzuk ki az olvasókat és adataik mellet azt, hogy az általuk legrégebben kivitt példány hány hete van náluk, névsor szerint rendezve.

Listázzuk ki a FEKETE ISTVAN által írt könyveket.

Irassuk ki azokat a könyveket, amelyekre egynél több elõjegyzés van.

Irassuk ki azokat a könyveket, amelyek drágábbak, mint a TANKONYVKIADO által kiadott könyvek átlagára.

Irassuk ki, hogy az egyes szerzõk hány könyvet írtak, névsor szerint rendezve.

Irassuk ki, hogy az egyes olvasóknál hány példány van kint, darabszám szerint csökkenõen rendezve, és a listában csak azok legyenek benne, akiknél legalább két könyv van.

Irassuk ki, hogy az egyes olvasók hány könyvre jegyeztek elõ.

Hány olvasónak nincs még figyelmeztetése (okod IS NULL).

Mennyi pénz szükséges a jelenlegi dolgozók havi bérének 10 %-os emeléséhez.

Irassuk ki, hogy az EGRI CSILLAGOKra jegyeztek-e elõ, és ha igen, akkor ki(k).

Határozzuk meg a könyvtárosok átlagfizetését.

Irassuk ki BARNA PETER szintjén az átlagfizetést.

Irassuk ki az EGRI CSILLAGOK címû könyv bent lévõ példányait.

Kiviheti-e GIPSZ JAKAB az TUSKEVAR címû könyvet? És az EGRI CSILLAGOKat.

Listázzuk ki a CIMVAL változó által meghatározott könyvbõl bent lévõ kivihetõ (kölcsönözhetõ és nincs kikölcsönözve) példányokat.

megoldások

SELECT COUNT(kiad_azon) FROM kiado WHERE varos='LONDON';

SELECT * FROM olvaso WHERE lakcim LIKE 'DEBRECEN %' AND beir_dat<'01-MAR-1990' AND beir_dat>='01-FEB-1990';

SELECT kolcs_e, COUNT(*) FROM peldany GROUP BY kolcs_e;

SELECT COUNT(ISBN) FROM konyv WHERE kiad_dat<'01-JAN-1994' AND ISBN IN (SELECT ISBN FROM peldany WHERE kolcs_e=1);

SELECT cim,kiad_dat,a.* FROM kiado a, konyv k WHERE k.kiad_azon=a.kiad_azon AND kiad_dat=(SELECT MIN(kiad_dat) FROM konyv);

SELECT o.*, (sysdate-kolcs_dat)/7 FROM olvaso o,kolcson k WHERE o.o_azon=k.o_azon AND kolcs_dat = (SELECT MIN(kolcs_dat) FROM kolcson WHERE o_azon=o.o_azon);

SELECT * FROM konyv WHERE ISBN IN (SELECT ISBN FROM irta i, szerzo s WHERE i.szerzo_azon=s.szerzo_azon AND vnev='FEKETE' AND unev='ISTVAN');

SELECT * FROM konyv WHERE ISBN IN (SELECT ISBN FROM elojegy GROUP BY ISBN HAVING COUNT(ISBN)>1);

SELECT * FROM konyv WHERE ar>(SELECT AVG(NVL(ar,0)) FROM konyv k, kiado a WHERE a.kiad_azon=k.kiad_azon (+) AND kiad_nev='TANKONYVKIADO');

SELECT MAX(vnev), MAX(unev), COUNT(s.szerzo_azon) FROM szerzo s, irta i
WHERE s.szerzo_azon=i.szerzo_azon
GROUP BY s.szerzo_azon
ORDER BY MAX(vnev), MAX(unev);

SELECT MAX(vnev), MAX(unev), COUNT(NVL(lelt_szam,0)) FROM olvaso o, kolcson k
WHERE o.o_azon=k.o_azon (+)
GROUP BY o.o_azon
HAVING COUNT(NVL(lelt_szam,0))>1
ORDER BY COUNT(NVL(lelt_szam,0)) DESC;

SELECT MAX(vnev), MAX(unev),COUNT(NVL(ISBN,0)) FROM olvaso o, elojegy e
WHERE o.o_azon=e.o_azon (+)
GROUP BY o.o_azon;

SELECT * FROM olvaso WHERE okod IS NULL OR okod=0;

SELECT SUM(1.1*fizetes-fizetes) FROM dolgozo;

SELECT * FROM olvaso WHERE o_azon in (SELECT o_azon FROM elojegy e,konyv k
WHERE k.ISBN=e.ISBN AND cim='EGRI CSILLAGOK');

SELECT AVG(fizetes) FROM dolgozo WHERE beosztas='KONYVTAROS';

SELECT AVG(fizetes) FROM dolgozo
WHERE LEVEL=(SELECT LEVEL FROM dolgozo CONNECT BY PRIOR d_azon=fonok START WITH vnev='BARNA' AND unev='PETER')
CONNECT BY PRIOR d_azon=fonok START WITH vnev='BARNA' AND unev='PETER';

SELECT lelt_szam FROM peldany MINUS SELECT k.lelt_szam FROM kolcson k, peldany p WHERE k.lelt_szam=p.lelt_szam AND ISBN = (SELECT ISBN FROM konyv WHERE cim='EGRI CSILLAGOK');

GIPSZ JAKABnál kint lévõ könyvek száma kisebb-e, mint a maximálisan kivihetõ könyvek száma (3)?
SELECT COUNT(NVL(lelt_szam,0))<3 FROM olvaso o, kolcson k
WHERE o.o_azon=k.o_azon (+) AND vnev='GIPSZ' AND unev='JAKAB'
 Van-e jelenleg az TUSKEVAR ból példány GIPSZ JAKABnál?
SELECT vnev,unev FROM olvaso o, kolcson k
WHERE o.o_azon=k. o_azon AND vnev='GIPSZ' AND unev='JAKAB' AND lelt_szam IN
(SELECT lelt_szam FROM konyv k, peldany p WHERE k.ISBN=p.ISBN AND cim='TUSKEVAR');
Az elõjegyzési sorban GIPSZ JAKAB elõtt lévõk száma kisebb-e, mint ahány szabad, kivihetõ példány van az TUSKEVARból jelenleg a könyvtárban?
Az elõjegyzési sorban GIPSZ JAKAB elõtt lévõk száma:
SELECT COUNT(e.o_azon) FROM olvaso o, elojegy e WHERE o.o_azon=e.o_azon AND ISBN=(SELECT ISBN FROM konyv WHERE cim=' TUSKEVAR') AND eloj_dat < (SELECT eloj_dat FROM olvaso o, elojegy e WHERE o.o_azon=e.o_azon AND vnev='GIPSZ' AND unev='JAKAB');
Hány szabad, kivihetõ példány van az TUSKEVARból?
SELECT COUNT(p.lelt_szam) FROM peldany p, kolcson k WHERE p.lelt_szam =k.lelt_szam (+) AND kolcs_e=1 AND o_azon is NULL AND ISBN = (SELECT ISBN FROM konyv WHERE cim=' TUSKEVAR');
Az EGRI CSILLAGOK címû könyv esetén a megoldás ugyanez, érdemes ilyen esetekben változókat használni (cim=&CIMVALT).

SELECT p.lelt_szam FROM peldany p, kolcson k WHERE p.lelt_szam =k.lelt_szam (+) AND kolcs_e=1 AND o_azon is NULL AND ISBN = (SELECT ISBN FROM konyv WHERE cim=&CIMVAL);

Adatdefiníciós nyelv

Adattípusok

Táblák létrehozása, törlése, módosítása

CREATE TABLE táblanév

(oszlopnév adattípus (szélesség) [NOT NULL],

oszlopnév adattípus (szélesség) [NOT NULL],

oszlopnév adattípus (szélesség) [NOT NULL]);

Táblanév: max 30 karakter, egyedi, a névképzési szabályoknak megfelelõ.

ALTER TABLE táblanév

ADD oszlopnév adattípus (szélesség);

[DROP megszorítás]

Új oszlop táblához adása.

ALTER TABLE táblanév

MODIFY oszlopnév adattípus (új_szélesség) [NOT NULL|NULL];

[DROP megszorítás]

Meglévõ oszlop szélesítése.

ALTER TABLE kiado

ADD telefon NUMBER(10);

ALTER TABLE kiado

MODIFY varos CHAR(20);

Táblában lévõ oszlopot nem lehet direkt módon törölni. (Helyette: Új tábla létrehozása kevesebb oszloppal, értékek átmásolása, régi tábla törlése.)

Csak olyan oszlop típusát lehet megváltoztatni, illetve méretét csökkenteni, amelyben minden sor értéke NULL.

Egy létezõ oszlop csak akkor változtatható NOT NULL típusúvá, ha minden sorában nem NULL érték áll.

DROP TABLE táblanév;

drop table elojegy;

drop table kolcson;

drop table peldany;

drop table konyv;

drop table kiado;

drop table olvaso;

CREATE VIEW nézetnév [aliasnév]

AS szelekciós utasítás;

ORDER BY rész nem lehet benne, több táblára is mûködik.

DROP VIEW nézetnév;

CREATE [UNIQUE] INDEX indnév. ON tábnév. (oszlnév [ASC|DESC]);

DROP INDEX indnév.[ON tábnév];

Adatmanipulációs nyelv

INSERT INTO tábnév [(on1,on2,.)]

VALUES (e1,e2,e3,.) | szelekciós utasítás;

UPDATE tábnév SET on1=e1,on2=e2,..

WHERE keresési felt. | szelekciós utasítás;

DELETE FROM tábnév

[WHERE keresési felt. | szelekciós utasítás};

Csak teljes sor törlése.

Ha nincs WHERE vagy szelekciós, akkor minden sort töröl.

feladatok

Képzeljük el, hogy egy könyvesboltot vezetünk. Készítsünk egy olyan adatbázist, amelyben nyilvántarthatjuk a készleten lévõ könyveket, a rendeléseket, és az eladásokat.

Hozzuk létre a következõ táblákat.

Vigyünk fel néhány szállítót a szallito táblába.

Rendeljünk könyveket.

A rendelés megérkezésekor, állítsuk be a teljesítés dátumát az adott napi dátumra, és aktualizáljuk a készletet, ahol az egységárat a beszerzési ártól (rendeles tábla egysegar mezõje) állítsuk nagyobbra.

Az egyes könyvek eladásakor aktualizáljuk a készletet (darab mezõ), és bõvítsük az eladás táblát.

Kérdezzük le, hogy milyen könyvekbõl kell új rendelést feladni (pl. darab<2).

Megoldások

CREATE TABLE szallito (szall_azon CHAR(3) NOT NULL PRIMARY KEY,
szall_nev CHAR(20) CHECK (szall_nev=UPPER(szall_nev)),
cim CHAR(30) CHECK (cim=UPPER(cim)) );

CREATE TABLE rendeles (rend_szam CHAR(4) NOT NULL PRIMARY KEY,
ISBN CHAR(6) NOT NULL, rend_dat DATE DEFAULT SYSDATE NOT NULL,
szall_azon NOT NULL REFERENCES szallito,
telj_dat DATE,
egysegar NUMBER (4) NOT NULL,
mennyiseg NUMBER (4));

CREATE TABLE keszlet (ISBN CHAR(6) NOT NULL, besz_dat DATE DEFAULT SYSDATE NOT NULL,
PRIMARY KEY(ISBN, besz_dat),
cim CHAR(20) NOT NULL CHECK (cim=UPPER(cim)),
szerzo CHAR(25) CHECK (szerzo=UPPER(szerzo),
egysegar NUMBER(4) NOT NULL,
darab NUMBER(4) NOT NULL);

CREATE TABLE eladas (ISBN CHAR(6) NOT NULL, egysegar NUMBER(4) NOT NULL, besz_dat DATE DEFAULT SYSDATE NOT NULL);

INSERT INTO szallito VALUES ('001','FOSPED','BUDAPEST');

INSERT INTO rendeles VALUES ('0001', '100001',SYSDATE,'001',NULL,1300,10);

UPDATE rendeles SET telj_dat=SYSDATE
WHERE rend_szam='0001';

Adatvezérlõ nyelv

Tranzakció-kezelés

Tranzakció: Tetszõleges adatmanipulációs utasítások egy sorozata.

lefutott a tranzakció à ellentmondásmentes adatbázis

nem futott le a tranzakció à ellentmondásos adatbázis

COMMIT

A tranzakció utáni állapotot rögzíti.

ROLLBACK

A tranzakció elõtti állapotot állítja vissza.

Ezek a parancsok automatikusan és expliciten is meghívásra kerülnek.

Privilégiumok és hozzáférési jogok

Jogok

Az Oracle rendszerbe történõ bejelentkezéshez szükséges

felhasználói név

jelszó

jogok

A jogok lehetnek

a rendszer használatával kapcsolatos jogok (privilégiumok)

a táblák használatával kapcsolatos jogok (hozzáférési jogok)

Privilégiumok

Connect

Resource

Dba

CONNECT

bejelentkezhet az Oracle RDBMS-be és használhatja

betekinthet táblákba, amelyekre SELECT jogot kapott

betekinthet a Public minõsítésû táblákba

az adatmanipulációs utasításokat használhatja azokra a táblákra, amelyekre a tábla tulajdonosa megfelelõ jogokat adott (INSERT, DELETE, UPDATE)

nézettáblákat (view) definiálhat

RESOURCE

minden CONNECT jog

táblák, indexek létrehozása és törlése

az általa létrehozott táblákra vonatkozóan jogokat adhat tovább más felhasználóknak

az általa létrehozott táblákra, indexekre igénybe veheti a rendszer AUDITING szolgáltatását

DBA

minden RESOURCE jog

bármely felhasználó adataiba betekinthet, és lekérdezést hajthat végre

jogokat adhat és vonhat vissza bárkitõl

PUBLIC-nak minõsíthet adatokat

rendszer AUDITING

teljes adatbázis export/import

Táblákra vonatkozó hozzáférési jogok

A tábla tulajdonosa vagy DBA jogú felhasználó adhatja illetve vonhatja vissza ezeket a hozzáférési jogokat.

Privilégiumok és hozzáférési jogok adása és visszavonása

Privilégiumok és hozzáférési jogok adása illetve visszavonása a GRANT illetve a REVOKE utasítással történik.

Privilégiumok adása és visszavonása (csak DBA)

GRANT privilégium TO felhasználó IDENTIFIED BY jelszó;

REVOKE privilégium FROM felhasználó;

Hozzáférési jogok adása és visszavonása

GRANT ALL | hozzáférési_jog [,hozzáférési_jog .] ON táblanév TO PUBLIC | felhaszáló [,felhaszáló] [WITH GRANT OPTION];

GRANT oszlop_jog (oszlop)[, oszlop_jog (oszlop) .] ON táblanév TO PUBLIC | felhaszáló [,felhaszáló] [WITH GRANT OPTION];

REVOKE ALL | hozzáférési_jog [,hozzáférési_jog .] ON táblanév FROM PUBLIC | felhaszáló;

A felhasználó helyett állhat a PUBLIC is, ekkor mindenki számára biztosítjuk a jogokat.

Az összes hozzáférési jog helyett állhat ALL.

oszlop_jog: UPDATE, REFERENCES

[WITH GRANT OPTION] ha megadjuk, a felhasználó továbbadhatja a jogokat.