Parancs Connect
user/pw
jelentése
Példa
* From user_objects
Felhasználóváltás Connect scott/tiger a felhasználónak milyen adattáblái select * from emp; vannak felhasználói adatszótár lekérése
* From adattábla
adattábla tartalmának lekérdezése
sysdate From dual
a rendszeridő ábrázolásának lekérdése
* From user_catalog
rekord AS "kiíráskor megjelenő név" From táblalista Where LogikaiOszlokifejezés Group By csoplist Having logikaioszlkif Order By (asc,desc)
Select
szelekcióslista From tábla1 [{INNER | LEFT | RIGHT | FULL} JOIN tábla2 {ON Táblaösszekötőfeltétel | USING(oszlop1 [, oszlop2, …] ) } ] …
Táblák összekapcsolása a FROM utasításrészben a JOIN segítségével. INNER - mindegyik táblában szerepel Select ename, deptno, loc LEFT/RIGHT - a balra/jobbra álló from emp tábla sorai szerepelnek a táblában right join dept using(deptno); FULL - mindkét irányba engedélyezett a hiányos listasor ON - a jobb és bal oldali táblát összekapcsoló logikai kifejezés
szelekcióslista From tábla [Where Logikaifeltétel] CONNECT BY {PRIOR kif = kif | kif = PRIOR kif} [START WITH kif=kif] …
A szelekciós lista tartalmazhatja a LEVEL belső változót. Megadja, hogy a hierarchia mely szintjén található az aktuálisan listázott elem. START WITH - a feldolgozás hol kezdődjön. CONNECT BY - a megadott feltételt használjuk a sorok és leszármazottainak kiszűrésére. PRIOR - meghatározza a bejárás szerinti szülőcsúcsot.
xxx ed c:\gyakorlas\xxx xxx @
c:\gyakorlas\xxx
rem Megjegyzés -/* */ -
Sorfolytató jel
define_editor= "xxx.exe"
SOUNDEX
Script létrehozása (\oracle\product\10.2.0\db_1\bin) Script létrehozása megadott útvonallal Script futtatása Script futtatása megadott útvonallal előtte csak szóköz állhat előtte csak szóköz állhat megjegyzés
(sztring1)=SOUNDEX(sztring2)
select level as szint, ename as dolgozó, deptno as részleg from emp connect by mgr=prior empno start with Upper(ename)='KING' Order by deptno;
ed példa
@ példa
rem megjegyzés -- megjegyzés /* megjegyzés */ set linesize 400
Az alapértelmezett editort változtatjuk meg
define_editor="write.exe"
Megadja a sztring1-hez hasonló sztringeket
WHERE SOUNDEX(UPPER(ename)) = SOUNDEX(UPPER(similar. munkakör));
Tábla létrehozása, feltöltése, törlése, adatformátumok
Create Table
create table minta (név VARCHAR2(20), szül DATE, tan_átl NUMBER(3,1));
táblanév (adat1 varchar2(szám), adat2 date, adat3 number(szám,szám))
táblanév
Tábla adatainak másolása egy másik Create Table emp1 AS SELECT táblába * FROM emp Tábla törlése DROP TABLE minta;
into tábla Values('xxx', szam)
Egy rekord felvitele
number
(szam1,szam2)
Szam - ennyi karakter összesen, szam2-tizedespont utáni karakterek száma
varchar2
(szam)
táblanév AS SELECT * FROM táblanév Drop Table Insert
desc Update
insert into minta VALUES('CSÍNOM PALKÓ', '1981-ÁPR-05',2.9);
ennyi karakter lehet tábla felépítésének megmutatása (set linesize 60)
táblanév táblanév SET oszlop=érték WHERE oszlop=érték
A tábla egy oszlopának adatainak megváltoztatása
Update emp SET deptno=30 WHERE deptno=50
Speciális Függvények NVL Decode
(oszlopnév, helyettesítő)
a NULL értéket tényleges értékké alakítja át
NVL(munkakör, 'még nincs')
(Oszlopkifejezés, h1, t1, …, kifejezés)
soronként megvizsgálja az oszlopkif-t, ha értéke h1, akkor t1, egyébként kifejezés
select Decode(NVL(comm, -1), -1, 'Nem jár jutalék', comm) as jutalék
When logikaikif Then visszatérőkif [when .. Then..] else visszatérőkif End
Select Case when NVL(comm,-1)=-1 then 'Nem jár jutalék' else To_Char(Comm) End As jutalék from emp;
oszlopkif When érték Then visszatérőkif [when .. Then..] else visszatérőkif End
Case NVL(comm,-1) when -1 then 'Nem jár jutalék' else To_Char(Comm) End As jutalék
Case
Karakterkezelő függvények Lower
({Oszlop | kifejezés })
Upper
({Oszlop | kifejezés })
InitCap
({Oszlop | kifejezés })
||
({Oszlop n | kifejezés n })
Concat
({Oszlop n | kifejezés n })
Substr
({Oszlop | kifejezés }, m , n)
Length
({Oszlop | kifejezés })
A karakterlánc minden betűjét kisbetűvé alakítja A karakterlánc minden betűjét Where Upper(job) in nagybetűvé alakítja ('SALESMAN','CLERK') Minden szó első betűje nagy, a többi kicsi két megadott karakterláncot összefűz
Select Rpad(ename, 6) || Rpad(':', Round((sal/1000),0)+1, '#') from emp
két megadott karakterláncot összefűz, olyan mint a || a karakterlánc m. pozíciójától n karaktert Select ename, Substr(ename,1) ad vissza from emp where deptno=10 karakterlánc hosszát adja vissza
Instr
({Oszlop | kifejezés }, minta, m, n)
a minta karaktersorozatnak az első paraméterként megadott karsorozatbeli Select ename, Instr(ename, 'AR') pozícióját adja vissza. Ha null akkor nem from emp talált. m - kezdőpozíció, n-n-edik előfordulás
Lpad
({Oszlop | kifejezés }, n, 'kitöltő'))
Jobbra igazít, és balról kiegészíti a Select Lpad(ename, 6) || Lpad(':', kitöltő karakterrel, hogy a hossza épp n Round((sal/1000),0)+1, '#') from legyen emp
Rpad
({Oszlop | kifejezés }, n, 'kitöltő'))
Select Rpad(ename, 6) || Rpad(':', Balra igazít, és jobbról kiegészíti a kitöltő Round((sal/1000),0)+1, '#') from karakterrel, hogy a hossza épp n legyen emp
(karakterkód)
a decimálisan megadott kódú karaktert adja vissza soremelés - 10 tab - 9 szóköz - 32
'%string%_%'
Egyező karaktersorozatot keres. % Bármilyen karakter bármilyen WHERE supplier_name like 'Hew hosszúságban; %'; _ - Egyetlen karakter egy hosszúságban
chr
Like
Dátumok és számok formázott megjelenítése Rendszeridő lekérdezése (mindig a dualból)
Sysdate
select sysdate from dual
To_Date
({string | szám} , 'formátummaszk' )
Karakterláncból dátumformátumot állítunk elő
To_Char
({dátum | szám} , 'formátummaszk' )
Dátumból vagy számból formázott karakterláncot állítunk elő
select to_char(sysdate, 'YYYYMM-DD') from dual;
( string1, [ format_mask ], [ nls_language ] )
Karakterláncból számformátumot állítunk elő
to_number('546', '999')
( number, [ decimal_places ] )
számok kerekítésére
round(-125.315, 2) (=-125.31)
( date, [ format ] )
Dátumok kerekítésére
round(to_date ('22AUG-03'),'YEAR')
To_Number
Round YYYY YEAR MM MONTH MON mon WW W DDD A To_Date és DD To_Char-ban alkalmazható D dátum formátumok DY DAY | Day | day HH | HH12 HH24 MI SS SSSSS /,-:._ és a szóköz 9 0 A To_char-al S9999 alkalmazható számformátumo $9999 k L999 | 999L . (pont) , (vessző) session set nls_date_format='yyyy.mon.dd' Alter
teljes évszám a teljes évszám betűkkel Hónap 2 számmal Hónap kiírva Hónap 3 nagybetűvel Hónap 3 kisbetűvel Hét sorszáma az évben Hét sorszáma a hónapban A napok sorszáma az évben A napok sorszáma a hónapban A napok sorszáma a héten (hétfő - 1; Vasárnap - 7) a hét napjának 2 betűs rövidítése a nap teljes neve Az óra (1-12) Az óra (1-24) A perc Másodperc Az éjfél óta eltelt másodpercek száma A dátumelemek között elválasztó elemek Értékes számjegy Vezető nulla Az S karakter helyén az előjel jelenik meg Minden szám elé $ kerül a helyi pénznem jelenik meg A tizedespont jele Az ezres csoportosítás jele A rendszeridő formátumának átírása
session set nls_date_format='yy-mon-dd' Eredeti rendszeridő session set nls_date_format='yyyy.mon.dd HH24:MI:SS'
ALTER session set Rendszeridő másodperc felbontásban nls_date_format='YY-MON-DD HH24:MI:SS'; Az eltelt idő mértékét pontos értékként, hónapokban adja vissza
months_between (to_date ('2003/08/02', 'yyyy/mm/dd'), to_date ('2003/06/02', 'yyyy/mm/dd') )
value1 and value2
két Érték között keres
WHERE column1 between value1 and value2;
Date1 and Date2
Két dátum között keres
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd') AND to_date ('2003/12/31', 'yyyy/mm/dd');
Months_Between ( date1, date2 )
Between
Not Between null Is not null
ALTER session set nls_date_format='YY-MON-DD';
value1 and value2
két Érték, vagy dátum között nem keres Nincs értéke minden aminek értéke van
WHERE supplier_id not between 5000 and 5500; where comm is not null
AVG STDDEV VARIANCE SUM MIN MAX
COUNT
Csoportfüggvények (egymásba ágyazhatók) Az oszlopkifejezés Átlagát adja ([ DISTINCT ] oszlopkifejezés ) meg a csoportra vonatkozóan Az oszlopkifejezés Szórását adja ([ DISTINCT ] oszlopkifejezés ) meg a csoportra vonatkozóan Az oszlopkifejezés ([ DISTINCT ] oszlopkifejezés ) szórásnégyzetét adja meg a csoportra vonatkozóan Az oszlopkifejezés összegét adja ([ DISTINCT ] oszlopkifejezés ) meg a csoportra vonatkozóan megadja az oszlopkifejezés ([ DISTINCT ] oszlopkifejezés ) legkisebb értékét megadja az oszlopkifejezés ([ DISTINCT ] oszlopkifejezés ) legnagyobb értékét
([ * | DISTINCT oszlopkifejezés] | oszlopkifejezés)
select round(avg(sal), 0) as Átlag select stddev(avg(sal), 0) as Átlag
select sum(sal) as "Összeg" from emp; select Min(sal) as "Minimum" from emp; select Max(sal) as "Maximum" from emp;
* - megadja a csoport elemeinek számát DISTINCT oszlopkif - megadja a csoport oszlopkif-re vonatkozó, de nem NULL értékű elemeinek számát oszlopkif megadja a csoport oszlopkif-re vonatkozó, nem NULL értékű elemeinek számát
Distinct - a csoportfüggvény értékek meghatározásában, csak egyszer veszi figyelembe az azonos sorokat Allekérdezések Select ename, job, loc From emp, dept Ez az INLINE nézet. Az Where emp.deptno IN (Select allekérdezéseket mindig deptno másodlagos táblanévvel kell ellátni from dept (inline nézet). Az elemekre where Upper(loc) ='DALLAS' minősített névvel hivatkozunk OR (select és where-ben is). Upper(loc) ='CHICAGO') AND emp.deptno=dept.deptno Order by loc;
From
erre az allekérdezésre még utasításon belül sem lehet hivatkozni. Csak egy értékű lehet!
Szelekciós listában
oszlopkif [NOT] IN halmaz Where
Select ename, job, loc From emp, dept Where Upper(loc) IN ('DALLAS', 'CHICAGO') AND emp.deptno=dept.deptno Order by loc;
oszlopkif relációjel ANY halmaz oszlopkif relációjel ALL halmaz true, ha legalább egy elemet tartalmaz halmaz - {(érték1 [, érték2]...) | (allekérdezés) | halmaz halmazművelet halmaz} allekérdezés - az IN, ANY, ALL esetén csak egyetlen oszlopot tartalmazhat halmazművelet - UNION, INTERSECT (metszet), MINUS (különbség) [NOT] EXISTS halmaz
relációjel - =,<,>,<=,>=,<>,!= valamelyike. Az utolsó kettő jelentése: Nem egyenlő. A relációsjel bal oldalán mindig csak oszlopkifejezés állhat, míg a jobb oldalon allekérdezés is () zárójelek között. Az allekérdezés csak egyetlen értéket adhat vissza. A WHERE-ben szereplő allekérdezéseket nem lehet ellátni másodlagos táblanévvel, ezért nem lehet rá hivatkozni!!
Felhasználói változók SELECT * FROM emp a definiált változókra utalás. Nem WHERE ename='&név' AND kerül eltárolásra! CHAR típus! hiredate<&mainap ha a változót jobbról közvetlenül szöveg követi. Nem kerül PROMPT Ez a(z) &index.-ik eltárolásra! CHAR típus!
változó & változó.
&&
define
Eltárolódik! Csak a DEFINE vagy ACCEPT utasítással változtatható meg. Az UNDEFINE-vel töröljük. CHAR típus!
változó
már definiált változókat és értéküket kérjük le új CHAR típusú változót adunk meg Megjeleníti a változót, értékét és adattípusát Egy DEFINE segítségével létrehozott változó törlése
változó=érték változó
undefine
accept
változó
változó [adattípus] [FORMAT 'formátummaszk'] [PROMPT 'szöveg'] [HIDE]
szöveg
szöveg kiírása. Soremelés kiírjuk képernyőre a változót
prompt &változó
all Show
Set
Rendszerváltozó linesize pagesize rendszerváltozó érték feedback {6|n|OFF|ON} linesize érték pagesize érték
SQL*Plusz-környezet beállítása Az SQL*Plusz környezet lekérdezése rendszerváltozó lekérdezése Sorhossz értékének mutatása
verify {ON | OFF} Help
TTITLE
BTITLE
BREAK
Definiál egy változót, üzenetet küld, és a választ eltárolja. UNDEFINE-vel töröljük. DEFINE vagy ACEPT változtathatja meg. Adattípus - NUMBER, CHAR, DATE HIDE - elrejti a megadott adatot (jelszó)
SQL*Plusz_utasítás Index
Sorhossz állítása Le lehet tiltani a script futásakor, hogy mutassa a belső behelyettesítéseket Az utasítások lekérdezése Az utasítások lekérdezése
UNDEFINE név
ACCEPT jelszo PROMPT 'A jelszó: ' HIDE
PROMPT &név
show numwidth show linesize show pagesize
Set linesize 400 set pagesize 40
HELP SET
SQL*Plusz formázási utasításai (Lista szintű utasítások) Minden oldal tetején megjelenő ["szöveg" | OFF | ON] fejléc megadása, letiltása, engedélyezése Minden oldal alján megjelenő ["szöveg" | OFF | ON] lábléc megadása, letiltása, engedélyezése [ON {oszlopnév | oszlopkif}] [ON {oszlopnév | oszlopkif}] …]
Kiszűri az ismétlődő értékek megjelenítését az egymást követő sorokban, és sortöréssel tagolja az adatsorokat
SQL*Plusz formázási utasításai (Oszlop szintű utasítások)
{oszlopnév | oszlopkif} [FORMAT 'formátummaszk'] [HEADING "szöveg"] [JUSTIFY {left | RIGHT | CENTER}]
Az oszlopok és az oszlopfejlécek megjelenési formáját szabályozza a paraméterek szerint. FORMAT - Karakteres formátumnál Aszám (A8) azt jelenti, hogy hány karakternyi helyet engedélyezünk. Dátumnál, és a számnál az eddig megismertek működnek. HEADING - Oszlop fejléce JUSTIFY - Az oszlopfejléc igazítása
oszlopnév {PRINT | NOPRINT | OFF | ON | CLEAR}
(NO)PRINT - elrejti, ill. engedélyezi az oszlop megjelenítését COLUMN sal NOPRINT OFF,ON - ki- vagy bekapcsolja az SELECT ename, sal, comm FROM emp; oszlophoz rendelt formázást CLEAR - A megadott oszlop formátumának törlése
{BREAKS | COLUMNS | SCREEN}
A formázási beállítások visszaállítása
COLUMN
CLEAR
SET feedback OFF COLUMN sal FORMAT L99999 SELECT ename, sal, comm FROM emp;
A formázó utasításokat mindig egyetlen sorba írjuk!! Használat után állítsuk vissza az eredeti környezeti beállításokat!! DML utasítások ( adattábla tartalmának megváltoztatása) Beszúrás Táblába táblanév - A megváltoztatandó tábla neve BeszurandóOszlopok - ha nem adunk meg minden oszlopnak értéket, akkor kell felsorolni BeszurandóÉrtékek - lehet allekérdezés eredménye is. Minden oszlopnak értéket kell adni!
INSERT INTO dept VALUES (10, 'accounting', 'new york'); INSERT INTO dept VALUES (20, 'research', 'dallas');
UPDATE
táblanév SET MódosítandóOszlop = ÚjÉrték [, MódosítandóOszlop = ÚjÉrték] … [WHERE LogikaiKif];
Tábla módosítása MódosítandóOszlop - az az oszlop melynek egy vagy több értéke meg fog változni ÚjÉrték - mindig csak egy érték lehet! Lehet allekérdezés is.
UPDATE emp1 SET mgr1 = mgr, mgr2 = mgr, empno1=empno;
DELETE
[FROM] táblanév [WHERE LogikaiKif];
A LogikaiKif-ben szereplő sorok mindegyike törlésre kerül. A FROM kulcsszó elhagyható.
táblanév [(BeszurandóOszlopok)] INSERT INTO VALUES (BeszurandóÉrtékek);
Adatbázis-tranzakciók SAVEPOINT MentésiPontNeve; Amíg a változások nincsenek lezárva a COMMIT utasítással, addig e változások visszavonhatók ROLLBACK [TO [SAVEPOINT] MentésipontNeve; (a SAVEPOINT-ig, ha nincs akkor az utolsó COMMIT-ig, ha ez sincs akkor a kiinduló állapotig) COMMIT
;
DDL utasítás nem vonható vissza!
A tranzakció a COMMIT utasítással véglegesíthető.
DDL utasítások ( adattábla megváltoztatása) Tábla létrehozása adattípus - többek között: NUMBER[(m [, t])] : m - mezőszélesség t tizedesek száma CHAR[(n)] : n - max értéke 2000. Kötött hosszúságú. VARCHAR2(n) : n - max értéke 4000. Változó hosszúságú. DATE : dátumok tárolására
CREATE TABLE dept (deptno number(2) Not Null, dname varchar2(14), loc varchar2(13), constraint dept_primary_key primary key (deptno));
táblanév AS allekérdezés;
Tábla létrehozása allekérdezéssel. Csak a NOT NULL megszorítást másolja át!
CREATE TABLE emp1 AS SELECT * FROM emp;
táblanév ADD (Oszlopnév adattípus [DEFAULT kif] [oszlopmegszorítások]);
Új Oszlop hozzáadása
táblanév MODIFY (Oszlopnév [adattípus] [DEFAULT kif] [oszlopmegszorítások]);
Oszlop módosítása
táblanév DROP COLUMN oszlopnév [CASCADE CONSTRAINTS];
Oszlop törlése (eldobása) CASCADE CONSTRAINTS - az oszlophoz rendelt megszorítások ellenére történik a törlés.
táblanév RENAME COLUMN RégiOszlopnév TO ÚjOszlopnév;
Oszlop átnevezése
táblanév;
Tábla törlése (eldobása)
RégiTáblanév TO ÚjTáblanév;
Tábla átnevezése
[felhasználó.]táblanév (oszlopnév adattípus [DEFAULT kif] [Oszlopmegszorítások] [, oszlopnév adattípus [DEFAULT kif] CREATE TABLE [Oszlopmegszorítások] … [, táblamegszorítások]);
ALTER TABLE
DROP TABLE RENAME
ALTER TABLE emp1 modify (empno1 constraint c_empno2 NOT NULL)
DROP TABLE emp;
Integritási megszorítások Oszlop- és táblamegszorítás CONSTRAINT [Megszorításnév] Megszorítástípus hozzáadása
táblanév ADD Oszlopmegszorítások; Megszorítás hozzáadása
ALTER TABLE táblanév DROP [DISABLE | ENABLE] CONSTRAINT Megszorításnév [CASCADE];
ALTER TABLE dolgozo add constraint c_munkakör check ( upper(job)='SALESMAN' AND sal between 1100 and 2000) OR (upper(job) !='SALESMAN'));
Megszorítás törlése. CASCADE - minden olyan megszorítás törlődik, mely függ a törölt megszorítástól DISABLE - egy megszorítást ideiglenes felfüggesztése ENABLE - egy felfüggesztett megszorítás engedélyezése Az adott oszlop tartalmazhat NULL értéket Az adott oszlop NEM tartalmazhat NULL értéket Egyedi kulcs megszorítás ALTER TABLE dolgozó oszlopmegszorításként: ADD CONSTRAINT Az oszlop minden sorában egyedi1 UNIQUE(ename); különböző értéknek kell lennie. Lehet NULL is!
NULL NOT NULL
UNIQUE (vonatkozó_oszlopnév [, vonatkozó_oszlopnév]…)
PRIMARY KEY (vonatkozó_oszlopnév [, vonatkozó_oszlopnév]…)
Egyedi kulcs megszorítás táblamegszorításként: vonatkozó_oszlopnév - Az adott tábla oszlopát jelöli Elsődleges kulcs megszorítás oszlopmegszorításként: Minden sor egyértelműen azonosítható kell legyen, nem lehet NULL értékű! Elsődleges kulcs megszorítás táblamegszorításként
ALTER TABLE dolgozó ADD CONSTRAINT kulcs1 PRIMARY KEY(ename);
hivatkozott_táblanév (hivatkozott_oszlopnév) REFERENCES [ON DELETE CASCADE]
hivatkozott_táblanév (vonatkozó_oszlopnév [, vonatkozó_oszlopnév]…) FOREIGN KEY REFERENCES hivatkozott_táblanév (hivatkozott_oszlopnév [, hivatkozott_oszlopnév]...) [ON DELETE CASCADE]
Hivatkozási megszorítás. Vonatkozhat saját vagy más táblára is. Az általa jelölt oszlop értékeinek szerepelnie kell a hivatkozott tábla hivatkozott oszlopának értékei között. ON DELETE CASCADE - a törlés ALTER TABLE dolgozó tovább gyűrűzhet a vonatkozó ADD CONSTRAINT értékekre is. egyedi1 FOREIGN KEY(deptno) REFERENCES idegen kulcs részeként: lejjebb részleg(deptno) Idegen kulcs táblamegszorításban: ON DELETE CASCADE; Egy oszlopot, illetve oszlopok egy halmazát jelöli ki idegen kulcsként a hivatkozott tábla hivatkozott oszlopára. Használható saját táblára is.
CHECK
(feltétel)
Feltételes megszorítás. A feltétel tetszőleges, az adott tábla oszlopaira vonatkozik. TRUE érték esetén engedélyezi az adatbevitelt, illetve módosítást.
DESC
táblanév;
set linesize 60 tábla felépítésének megmutatása (set desc emp; linesize 60) set linesize 400;
Megszorítások lekérdezése
Select * from user_constraints where table_name='táblanév' ; Select * from user_cons_columns where table_name='táblanév' ; Nézettábla (olyan mint az eljárás)
CREATE
DROP
Nézettábla létrehozása. OR REPLACE - ha van ilyen nevű, akkor felülírja. FORCE - a nézetet mindenképp létrehozza, akkor is ha a hivatkozott tábla nem létezik NOFORCE - csak akkor hozza létre a CREATE OR REPLACE [OR REPLACE] VIEW szegény AS nézetet, ha a lekérdezett táblák [FORCE | NOFORCE] SELECT empno, mindegyike létezik (alap) VIEW NézetNév ROWNUM AS sorszám Másodlagosnév - hivatkozási nevek [(Másodlagosnév [, Másodlagosnév] …)] zárójelben. Ennek száma egyenlő kell FROM (SELECT empno AS allekérdezés FROM dolgozó legyen az allekérdezés kimenő [WITH READ ONLY | ORDER BY sal) oszlopinak számával WITH CHECK OPTION]; WITH READ ONLY - adatmanipulációt WHERE ROWNUM<=3; nem hajthatunk végre. Ha nincs akkor a nézettáblán keresztül módosíthatjuk az eredeti adattábla értékeit. WITH CHECK OPTION lehetőségünk van a hivatkozási integritás ellenőrzésére VIEW NézetNév;
Nézettábla törlése
A nézetre vonatkozó UPDATE, DELETE, INSERT akkorműködik, ha egyetlen táblára épül, és nem tartalmaz GROUP BY, DISTINCT utasításrészt, csoportfüggvényeket, kifejezést, nem hivatkozik pszeudooszlopra. Ha nézettáblán keresztül hajtunk végre DML-utasítást, és késöbb törölni szeretnénk a nézetet és visszaállítani a táblát, akkor elöbb ROLLLBACK, majd a nézet törlése. Felső-N analízis
SELECT
{[oszloplista, ROWNUM] | [oszloplista] } FROM [táblalist, ] allekérdezés WHERE ROWNUM <=n;
Az első vagy utolsó n sor keresése: ROWNUM - pszeudooszlop, amely a kiválasztásra kerülő sorokhoz hozzárendeli a kiválasztásuk sorszámát n - a kiválasztott sorok száma Nem lehet rownum=n !!!
SELECT ROWNUM, ename as név FROM (SELECT ename FROM emp ORDER BY ename) WHERE ROWNUM<=3;
Rendszerjogosultság kezelés (DCL utasítások)
GRANT
REVOKE
Jogosultság adása: rendszerjogosultság - A {rendszerjogosultság | szerepkör} megadni kíván jogosultság [, {rendszerjogosultság | szerepkör}] felhasználó - a jogot kapó … felhasználó azonosítója TO {felhasználó | szerepkör | szerepkör - lásd késöbb PUBLIC} TO - a felhasználó vagy [, {felhasználó | szerepkör}] szerepkör, aki a jogot kapja … PUBLIC - minden (jelenlegi és [WITH ADMIN OPTION]; jövőbeni) felhasználót kijelöl WITH ADMIN OPTION - a jogosultságot tovább lehet adni
{rendszerjogosultság | szerepkör} [, {rendszerjogosultság | szerepkör}] … FROM {felhasználó | szerepkör | PUBLIC} [, {felhasználó | szerepkör}]; … CREATE SESSION CREATE TABLE CREATE ANY TABLE
Jogosultság visszavonása CONNECT system/gazda REVOKE CREATE ANY TABLE FROM tudor; kapcsolódás az adatbázishoz. Mindenkinek kell. Tábla létrehozása a felhasználó saját tulajdonában Tábla létrehozása (az adatbázisban bárhol)
CREATE ANY VIEW CREATE TABLESPACE CREATE PROCEDURE CREATE USER Rendszer- ALTER ANY TABLE jogosultságok
Táblaterület létrehozása a felhasználó saját tulajdonában tárolt eljárás, függvény vagy csomag létrehozása a felhasználó saját tulajdonában felhasználó létrehozása az adatbázisban bármely tábla szerkezetének megváltoztatása
ALTER TABLESPACE ALTER USER ALTER ANY TRIGGER DROP ANY TABLE
az adatbázisban bármely trigger megváltoztatása az adatbázis bármely táblájának törlése
DROP ANY VIEW DROP TABLESPACE
Táblaterület törlése
DROP USER
felhasználó törlése Soha ne adjunk a felhasználónak ilyen jogot!!
UNLIMITED TABLESPACE
CONNECT system/gazda GRANT CREATE SESSION, CREATE TABLESPACE, CREATE USER, CREATE ANY TABLE, CREATE ANY VIEW, ALTER TABLESPACE, ALTER USER, ALTER ANY TABLE, DROP TABLESPACE, DROP USER, DROP ANY TABLE, DROP ANY VIEW TO tudor WITH ADMIN OPTION;
Objektumkezelési jogosultságok
GRANT
REVOKE
{Objektumkezelésijog | ALL} [(oszlopok)] ON objektum TO {felhasználó | szerepkör | PUBLIC} [, {felhasználó | szerepkör}] … [WITH ADMIN OPTION];
{Objektumkezelésijog | ALL} ON objektum FROM {felhasználó | szerepkör | PUBLIC} [, {felhasználó | szerepkör}]…; SELECT
Objektumkezelési jogosultságok
INSERT UPDATE DELETE EXECUTE ALTER READ REFERENCE INDEX
role_sys_privs
Objektumkezelési jog adása: objektumkezelésijog - a megadni kívánt objektumkezelési jog ALL - az összes objektumkezelési jog oszlopok - azoknak a tábláknak vagy nézeteknek (vesszővel elválasztott) oszlopai, amelyre a jogosultság vonatkozik ON objektum - az objektum, amelyre a jogosultság vonatkozik TO - a felhasználó vagy szerepkör, aki a jogosultságot kapja felhasználó - a jogosultságot kapó felhasználó neve szerepkör - lásd késöbb PUBLIC - minden felhasználót kijelöl WITH GRANT OPTION - megadja a felhasználónak a jogosultság továbbadásának jogát. Ez szerepkör esetén nem megengedett.
Objektumkezelési jog visszavonása
GRANT SELECT, INSERT, UPDATE, DELETE ON scott.dept TO tudor WITH GRANT OPTION;
CONNECT system/gazda REVOKE SELECT ON scott.dept FROM tudor;
adott tábla, vagy nézet sorainak megtekintése új sorok hozzáadása adott táblához adott tábla sorainak módosítása sorok törlése adott táblából adott tárolt program végrehajtása adott objektum újradefiniálása adott könyvtár állományainak megtekintése adott táblára hivatkozó megszorítás létrehozása index létrehozása adott táblára Jogosultságok lekérdezése szerpköröknek adott rendszerjogosultságok
role_tab_privs
szerepköröknek adott objektumkezelési jogosultságok
user_sys_privs
a felhasználó számára elérhető jogosultságok
user_role_privs
a felhasználóhoz rendelt szerepkörök felsorolása
user_tab_privs_made
a felhasználó által a saját objktumára más felhasználónak adott objektumkezelési jogok
user_tab_privs_recd
a felhasználó részére adott objektumkezelési jogok
user_col_privs_made
a felhasználó által a saját objektumainak (tábla, nézet) oszlopaira más felhasználónak adott objektumkezelési jogok
user_col_privs_recd
a felhasználó részére adott objektumoszlopokra (tábla, nézet) objektumkezelési jogosultságok
Select * FROM user_sys_privs;
táblaterület CREATE TABLESPACE DATAFILE 'állománynév' [SIZE méret [K | M] [REUSE]];
CREATE TABLE
táblanév (adat1 varchar2(szám), adat2 date, adat3 number(szám,szám)) TABLESPACE táblaterület;
TáblaterületNeve {ONLINE | ALTER TABLESPACE OFFLINE};
TáblaterületNeve DROP TABLESPACE [INCLUDING CONTENTS];
dba_tablespaces user_tablespaces dba_data_files
dba_users dba_tablespaces
Táblaterület Táblaterület létrehozása: Kell hozzá a CREATE TABLESPACE jogosultság!! Táblaterület - a létrehozandó táblaterület neve 'állománynév' - a táblaterület tároló fizikai fájl neve útvonalmegadással együtt SIZE méret - a táblaterület mérete (egész szám) K - a táblaterület mérete kilobájtban M - a táblaterület mérete megabájtban REUSE - a táblaterület létezése esetén annak törlését és létrehozását eredményezi Tábla létrehozás adott táblaterülete: Kell hozzá a CREATE (ANY) TABLE jogosultság
CREATE TABLESPACE könyvtár DATAFILE 'C:\hallgató\konyvek1.dat' SIZE 10 M REUSE;
CREATE TABLE könyv (cím varchar2(20), dátum DATE, példányszám NUMBER) TABLESPACE könyvtár;
Táblaterület módosítása: Kell hozzá az ALTER TABLESPACE jogosultság!! OFFLINE - ha egy táblaterület tartalmát szeretnénk átmozgatni egy másik tártelüretre, akkor először offline állapotba kell hozni. Táblaterület törlése: Kell hozzá a DROP TABLESPACE jog!! Először OFFLINE állapotba kell hozni. DROP TABLESPACE könyvtár; INCLUDING CONTENTS olyan táblaterületet is törölhetünk ahol vannak adatok is.
Táblaterület lekérdezése az adatbázis táblaterületeinek nézete felhasználói táblaterület nézete
SELECT * FROM user_tablespaces
táblaterületek tárolásának nézete Az adatbázishoz hozzáférő felhasználók listája Táblaterületek állapota
CONNECT system/gazda SELECT Username, Default_tablespace FROM dba_users ORDER BY Username;
Felhasználó (USER) Felhasználó kapcsolódása az CONNECT sys/gazda AS CONNECT felhasználó/jelszó SYSDBA adatbázishoz Felhasználó létrehozása: felhasználó - a létrehozandó felhasználó neve jelszó - kapcsolódási jelszava az felhasználó adatbázishoz IDENTIFIED BY jelszó CONNECT scott/tiger Táblaterület - az általa [DEFAULT TABLESPACE CREATE USER tudor létrehozandó objektumok tárolási Táblaterület IDENTIFIED BY tudor123 helye [TEMPORARY TABLESPACE DEFAULT TABLESPACE CREATE IdeiglenesTáblaterület - az IdeiglenesTáblaterület] könyvtár USER általa végzendő műveletek [QUOTA {méret [K | M] | TEMPORARY részeredményeinek ideiglenes UNLIMITED} ON Táblaterület] TABLESPACE temp tárolási helye [QUOTA {méret [K | M] | QUOTA UNLIMITED ON QUOTA - az engedélyezett UNLIMITED} ON könyvtár; tárterület negyságának IdeiglenesTáblaterület]]; korlátozása a megadott táblaterületre vonatkozóan UNLIMITED - esetén nincs korlátozás felhasználónév A felhasználó jelszavának ALTER USER tudor ALTER USER IDENTIFIED BY jelszó; megváltoztatása IDENTIFIED BY torpilla; Felhasználó törlése: CASCADE - törlődnek a DROP USER tudor DROP USER felhasználó [CASCADE] felhasználó által létrehozott CASCADE; objektumok is. Ha van ilyen, akkor kötelező!!
Szerepkör (ROLE) CREATE szerepkör [IDENTIFIED BY jelszó]; Szerepkör létrehozása ROLE DROP ROLE szerepkör; szerepkör törlése
Az SQL új csoportképzési eszközei
GROUP BY
ROLLUP (oszlop1 [, oszlop2]…)
A ROLLUP operátor segítégével csoportfüggvényeket alkalmazhatunk oszlopok sorozatán (sorrend szerint jobbról balra) egyre finomodó részcsoportképzéssel
CUBE (oszlop1 [, oszlop2]…)
A sorok csoportjainak valamely oszlop(ok)ra vonatkozó összes csoportosítási kombináció szerinti részösszegét meghatározza. Oszlopsorrend jobbról balra.
GROUPING SETS (oszlop1 [, oszlop2]…)
SELECT deptno, job, SUM(sal), COUNT(*) AS Létszám FROM emp GROUP BY ROLLUP (deptno,job);
SELECT deptno, job, SUM(sal), COUNT(*) AS Létszám FROM emp GROUP BY CUBE (deptno,job); SELECT mgr, deptno, job, SUM(sal) Több csoportot képezhetünk egy FROM emp lekérdezésben. GROUP BY GROUPING SETS ((mgr, deptno, job), (deptno,job), (mgr));
Külső programok futtatása HOST
SET
HOST cmd; Bármely parancssori környezetben HOST write (DOS) futó program meghívható. c:\munka\próba.sql
ProgramNeve
OFF - Ha nem akarjuk a programfutást látni, csak a végeredményt
termout [OFF | ON]
SET termout OFF HOST del c:\munka\*.bak HOST dir c:\munka\*.* > c:\munka\AAA HOST type c:\munka\BBB | sort.exe /+38 > c:\munka\A_DIR.txt SET termout ON
Táblák Exportálása
HOST
SPOOL
[exp | imp]
[FájlNév[.kiterjesztés] | OFF | OUT]
newpage 0 space 0 linesize 400 SET
pagesize 0 echo OFF feedback OFF heading OFF
exp - exportáló program imp - importáló program
a spool utasítás elindítja vagy leállítja a képernyőkimenet fájlba vagy nyomtatóra való küldését FájlNév - a kimenet tárolására szolgáló fájl neve kiterjesztés - a megadott fájl kiterjesztése OFF - bezárja a file-t, leállítja a további kiírást OUT - leállítja a kiírást, a kimenetet a nyomtatóra küldi. CHR(9) - A tab kódja ne dobjon üres lapot a legelején nincs üres sor lapváltásnál a sorméret elegendő nagy legyen az írás folyamatos legyen (ne legyen lapozás) ne írja ki az utasításokat ne írjon ki semmi visszajelzést törölje az esetleges fejléceket PL/SQL Blokk
[DECLARE deklarációk ] BEGIN végrehajtandó utasítások [EXCEPTION kivételkezelés] END; /
A deklarációs szegmenst a DECLARE kulcsszó vezeti be. Ez tartalmazza az összes változót, kurzort és a kivételeket. Az EXCEPTION kulcsszó vezeti be a kivételkzelő szegmenst. A BEGIN END között található a végrehajtási szegmens. A / zárja le a PL/SQL blokkot!!
HOST exp scott/tiger CONTRAINTS=Y GRANTS=Y INDEXES=Y ROWS=Y TABLES=(emp, dept, salgrade) FILE=c:\mentett\scott.dat SPOOL c:\munka\emp.txt SELECT empno ||CHR(9)|| ename ||CHR(9)|| job ||CHR(9)|| mgr ||CHR(9)|| TO_CHAR(hiredate,'YYYMON_DD') ||CHR(9)|| sal ||CHR(9)|| comm ||CHR(9)|| deptno AS "Az Emp táblázat" FROM emp; SPOOL SPOOL OFF
Változók használata
Lásd: Felhasználói változók
változónév adattípus VARIABLE
PRINT
DECLARE
Az SQL*Plusz felhasználói változói. Értékének beolvasása a PL/SQL blokkon belül &változó módon SET serveroutput ON történhet. ACCEPT változó_1 PROMPT "Kérem adjon meg egy számot: " Az SQL*Plusz környezeti változói. adattípus lehet: NUMBER, CHAR[(n)], VARCHAR2(n) A PL/SQL blokkban nevük elé : -ot kell tenni!!
[változónév]
Környezeti változó tulajdonságainak lekérdezése
[változónév]
Környezeti változó értékének lekérdezése. Csak SQL*Plusz környezetben!!
változónév adattípus; [változónév adattípus;] …
A PL/SQL változói. Adattípusok lehetnek: NUMBER[(m[,t])], CHAR[(n)], VARCHAR2(n), DATE valamint INTEGER, REAL, FLOAT, BINARY_INTEGER, STRING, LONG nagy méret adatok tárolására: LOB, BLOB, CLOB logikai: BOOLEAN (lehet NULL is!) összetett adattípusok: hivatkozási típusok, rekord, gyüjtőtábla, kurzor, stb
változó1 táblanév.oszlopnév%TYPE változó2 változó1%TYPE változó3 táblanév%ROWTYPE
Hivatkozási adattípus. Ahol az elemi típusú változó1 típusa azonos a táblanév nevű tábla oszlopnév nevű oszlopának típusával, a változó2 típusa azonos a változó1 típusával, és a rekord típusú változó3 egyes mezőire minősített nevekkel lehet hivatkozni.
TYPE rekordtípusnév IS RECORD (oszlopnév1 típus1 [oszlopnév2 típus2]…); változónév {rekordtípusnév | tábla%ROWTYPE};
Rekord adattípus. Oszlopnévn - a rekord egy mezőjének neve típusn - {típusnév | változó%TYPE | tábla.oszlop%TYPE }
Gyűjtőtábla típus. A gyűjtőtábla típusú változók adatvagy nézettáblák lekérdezett sorainak a memóriában való listaszerű tárolására alkalmasak. Típusnév - a gyűjtőtábla tíbus neve oszloptípus - standard PL/SQLTYPE adattípus, de lehet rekord is típusnév IS TABLE OF változó%TYPE - hivatkozás korábban {oszloptípus | változó%TYPE | tábla.oszlop%TYPE | tábla%ROWTYPE } deklarált változóra [NOT NULL] tábla.oszlop%TYPE - hivatkozás egy INDEX BY BINARY_INTEGER; tábla oszlopának típusára változónév típusnév; tábla%ROWTYPE - hivatkozás egy táblára NOT NULL - megszorítás megadása INDEX BY BINARY_INEGER - az utasításrész egyetlen funkciója a felhasználó emlékeztetése arra, hogy az INDEX egész típusú
VARIABLE változó_2 NUMBER DECLARE változó_3 NUMBER BEGIN változó_3:=&változó_1; IF változó_3>100 THEN DBMS_OUTPUT.PUT_LINE ('A megadott szám: ' || változó_3); ELSE :vátozó_2:=változó_3; END IF; END; / PRINT változó_2 VARIABLE DEFINE UNDEFINE változó_1 DEFINE
DECLARE v_neve dolgozó.ename%type; v_jutalma dolgozó.sal%type; NincsJutalom EXCEPTION;
SET serveroutput ON DROP TABLE dolgozó; CREATE TABLE dolgozó AS SELECT * FROM emp; DECLARE CURSOR Munkakurzor IS SELECT job, COUNT(job) FROM dolgozó GROUP BY job; TYPE T_munkakör_rekord IS REKORD (munkakör dolgozó.job %TYPE, létszám NUMBER(2)); TYPE T_munkakör_tábla IS TABLE OF T_munkakör_rekord INDEX BY BINARY INTEGER; CURSOR Dolgozókurzor IS SELECT * FROM dolgozó; TYPE T_prémium_rekord IS RECORD (azonosító dolgozó.empno%TYPE, név dolgozó.ename%TYPE, munkakör dolgozó.job%TYPE, fizetés dolgozó.sal%TYPE, létszám NUMBER(2), prémium dolgozó.sal%TYPE); TYPE T_prémium_tábla IS TABLE OF T_prémium_rekord INDEX BY BINARY_INTEGER;
PL/SQL Vezérlési szerkezetek, utasítások
SELECT
IF
LOOP
FOR
WHILE
A SELECT utasítás PL/SQLben: szelekcióslista Az elemi, és rekordváltozók a INTO {ElemiVáltozó [, PL/SQL-ben deklarált változók. ElemiVáltozó] … | RekordVáltozó} WHERE: csak egy sort szabad FROM TáblaLista visszaadnia. [WHERE LogikaiOszlopkif] A szelekciós listában csak azok [GROUP BY CsoportosítóOszlopkif] az oszlopok, és azok is csak [HAVING LogiakiOszlopkif] olyan sorrendben [ORDER BY szerepelhetnek, melyekhez RendezőOszlopkiflista] rendelt elemi változók az INTO kulcsszó után állnak
feltétel THEN utasítások [ELSEIF feltétel THEN utasítások; … [ELSE utasítások] END IF;
Feltételes utasítás: feltétel: logikai változó vagy kif. True, else, null értékei lehetnek! Utasítások: egy vagy több PL/SQL vagy SQL utasítás. Tartalmazhatnak további feltételes utasítást! THEN: a feltétel teljesülése esetén a végrehajtás ága. ELSEIF: ha az első feltétel hamis, akkor a vezérlés ezekre az ágakra adódik, ahol további feltételek adhatók ELSE: ha egyetlen feltétel sem teljesül, akkor a vezérlés erre az ágra adódik END IF: a feltételes utasítás lezárása
utasítás; [utasítás;] … [EXIT; ] [EXIT WHEN feltétel; ] END LOOP;
LOOP-Ciklus: a ciklusmag utasításait feltétel nélkül végrehajtja. A kilépő utasításrészek a ciklusmag tetszőleges helyén állhatnak.
ciklusváltozó IN [REVERSE] alsóhatár..felsőhatár LOOP-ciklus
feltétel LOOP-ciklus
FOR-Ciklus: a FOR-ciklus ciklusmagja egy LOOP-ciklust tartalmaz, és ez a ciklusmag meghatározott számú feldolgozására használatos. Az elemek bejárása egyesével növekvő, illetve a REVERSE kulcsszó esetén egyesével csökkenő. A ciklusváltozó deklarációja autómatikus, és csak a cikluson belül van értéke. WHILE-ciklus: ez egy elöltesztelő ciklus. A LOOP-ciklust addig hajtja végre, amíg a ciklusfejben lévő feltétel teljesül.
… DECLARE létszám NUMBER; részleg Részleglista.loc %TYPE; … BEGIN SELECT COUNT(*), loc INTO létszám, részleg FROM részleglista GROUP BY loc … END; /
SET serveroutput ON DECLARE c NUMBER; BEGIN c:=&a + &b DBMS_OUTPUT.PUT_LINE (c ); IF MOD(c,2)=1 THEN DBMS_OUTPUT.PUT_LINE (c || ' páratlan'); ELSE DBMS_OUTPUT.PUT_LINE (c || ' páros'); END IF; END; /
… BEGIN … összfiz:=0; FOR lindex IN (SELECT ROWNUM AS sorszám, ename, sal, loc FROM emp, dept WHERE UPPER(loc)=UPPER('&részleg') AND emp.deptno=dept.deptno) LOOP név:=lindex.ename; fizetés:=lindex.sal; összfiz:=összfiz+fizetés; END LOOP; END; /
Adatok kiírása a képernyőre
SET
serveroutput ON
DBMS_OUTPUT.PUT_LINE[(kifejezés)];
DBMS_RANDOM.VALUE(AlsóHatár, FelsőHatár)
A DBMS_OUTPUT csomag képernyőre való kiírását engedélyezi.
SET serveroutput ON VARIABLE sorok_száma NUMBER VARIABLE átlag NUMBER
DECLARE v_létszám NUMBER; v_átlag NUMBER(7,2); BEGIN SELECT COUNT(*), ROUND(AVG(sal)) INTO v_létszám, v_átlag FROM emp; Egy PL/SQL programon belül egy kifejezés DBMS_OUTPUT.PUT_LINE('létszám : ' || v_létszám || ' átlagfizetés: ' || képernyőre kiírása. v_átlag); Kifejezés: karakter típusú kifejezés. :sorok_száma:=v_létszám; :átlag:=v_átlag; END; /
Véletlen számok generálása: ahol az AlsóHatár és FelsőHatár egyaránt Binary_integer típusú számok.
SELECT dbms_random.value(-5,+7) FROM dual;
Kurzorok
DECLARE
FOR
CURSOR kurzornév IS (SQL)Select-utasítás
ciklusváltozó IN kurzor LOOP utasítás; [utasítás;] … END LOOP;
SQL%Kurzorattribútum
KurzorNeve%Kurzorattribútum
Kurzorattribútumok
Rejtett kurzorok: Ekkor a kurzor helyén állhat egy deklarált (explicit) kurzor neve, de állhat egy lekérdezés (rejtett kurzor) is.
Az Oracle minden PL/SQL-ben kiadott SELECT, INSERT, DELETE és UPDATE utasításhoz implicit kurzort rendel. Ezen név nélküli kurzorok tulajdonságainak lekérdezése. Explicit kurzor esetén
DROP TABLE dolgozó; CREATE TABLE dolgozó AS SELECT * FROM emp; SET serveroutput ON DECLARE CURSOR egydolgozó IS SELECT empno, ename, sal FROM dolgozó WHERE UPPER(job)='CLERK' FOR UPDATE NOWAIT; azonosító dolgozó.empno%TYPE; név dolgozó.ename%TYPE; fizetés dolgozó.sal%TYPE; BEGIN OPEN egydolgozó; LOOP FETCH egydolgozó INTO azonosító, név, fizetés; EXIT WHEN egydolgó %NOTFOUND; fizetés:=fizetés*1,2;
%FOUND
TRUE, ha a legutóbbi SQL-utasítás legalább egy sort megvizsgált vagy feldolgozott
%NOTFOUND
ha nincs több feldolgozandó sor, akkor a visszaadott értéke TRUE
%ROWCOUNT
a megvizsgált, feldolgozott sorok számát adja vissza
%ISOPEN
a kurzor megnyitását ellenőrzi; ha a kurzor UPDATE dolgozó SET sal=fizetés nincs megnyitva, akkor értéke FALSE WHERE CURRENT OF egydolgozó; (implicitnél mindig ez)
ROWID
FOR UPDATE
Explicit kurzor. Lehetvé teszi adattáblák soronkénti feldolgozását. A programozó saját maga deklarálhatja. Az OPEN megnyitó, a FETCH léptető, és a CLOSE lezáró utasításokkal vezérelhetjük.
[OF oszlop] [NOWAIT]
Egy 18 karakteres pszeudooszlop, egy fizikai adattáblasor logikai címét tartalmazza
DBMS_OUTPUT.PUT_LINE(név|| ' ' ||fizetés);
END LOOP; CLOSE egydolgozó; END; For Update záradék: Ha a PL/SQL-ben sorok értékeit módosítani / vagy törölni szeretnénk. Ez az SQL UPDATE utasításban az aktuális sor azonosítása: CURRENT OF kurzor NOWAIT - ne várakozzon a zárolt sorokra, hanem végezzen más feladatot, és próbálkozzon újra
Kivételkezelés (Exception)
RAISE
[kivételnév]
pragma EXCEPTION_INIT (kivételnév, hibakód);
A felhasználó által definiált kivételek: A kivételt deklarálni kell (EXCEPTION típussal), majd a RAISE utasítás kiváltja
DECLARE kivételnév EXCEPTION; … BEGIN … IF Logikaikif THEN RAISE kivételnév; … END IF EXCEPTION WHEN Kivételnév THEN utasítások; END; /
A felhasználó által definiált kivételeket társíthatjuk a rendszerkivételekhez az alprogram deklaráló részében. Kivételnév - egy előzőleg deklarált kivétel hibakód - egy már létező Oracle-hiba sorszám
A PL/SQL előredefiniált automatikus rendszerkivételei: Azeket a futtatórendszer váltja ki, és a STANDARD csomagban találhatók. No_Data_Found - a SELECT INTO nem ad vissza sorokat Too_Many_Rows - a SELECT INTO több sorral tér vissza OTHERS - minden más meg nem nevezett hiba esetén
Példa(részlet) EXCEPTION WHEN No_Data_Found THEN üzenet; WHEN Too_Many_Rows THEN üzenet; WHEN OTHERS THEN üzenet; END;
Alprogramok
PROCEDURE
név [(paraméter [, paraméter]…)] IS [lokális deklarációk (a DECLARE kulcsszó nélkül)] BEGIN végrehajtható utasítások [EXCEPTION kivételkezelés] END [név];
Az eljárás szintaktikája. Paraméterek adattípusai: az a megkötés, hogy nem tartalmazhatnak korlátozásokat (pl hosszúság, pontosság) Használható a NUMBER, INTEGER, REAL, CHAR, VARCHAR2, stb. Használhatók a hivatkozott (%TYPE, %ROWTYPE) típusok is.
FUNCTION
név [(paraméter [, paraméter]…)] RETURN adattípus IS [lokális deklarációk (a DECLARE kulcsszó nélkül)] BEGIN végrehajtható utasítások RETURN visszatérési érték [EXCEPTION kivételkezelés] END [név];
A függvény szintaktikája. Lehetőleg ne használjuk az OUT és IN OUT paramétereket!
paraméter_név [IN | OUT | IN OUT] adattípus [{DEFAULT | :=} kifejezés]
A paraméter szintaktikája mindkét esetben. IN - bemenő paramétert jelöl. Az értéke nem változtatható meg az alprogramban. OUT - kimenő paramétert jelöl. Az alprogramban csak kaphat de nem adhat át értéket IN OUT - be és kimenő paramétert jelöl. Az alprogramban kaphat és adhat is értéket. Adattípus - az eddig megismert típusok, de nem lehetnek megkötéseik (pl hosszúság) Kifejezés - kezdőérték megadására szolgál. Csak az IN vagy az IN OUT paraméterek esetén használható.
A névvel ellátott paraméterezhető blokkot alprogramnak nevezzük!
PROCEDURE Bolygók(név IN varchar2, sugár IN INTEGER, térfogat OUT REAL, sorrend IN OUT INTEGER)
Tárolt alprogramok CREATE OR REPLACE PROCEDURE Összead (Egyik IN NUMBER, Másik IN NUMBER) IS Eredmény NUMBER(3); Kiírás VARCHAR2(20); BEGIN Eredmény:=Egyik+Másik; END; /
OR REPLACE PROCEDURE eljárásnév [(paraméter [, paraméter] …)] {IS | AS} [lokális deklarációk (A DECLARE kulcsszó nélkül)] blokk
CREATE
Tárolt alprogramok létrehozása. CREATE OR REPLACE
FUNCTION Fizetés (Újfizetés IN NUMBER, Régifizetés IN NUMBER) RETURN BOOLEAN IS BEGIN IF Újfizetés>Régifizetés THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; / SHOW ERRORS
OR REPLACE FUNCTION függvénynév [(paraméter [, paraméter]…)] RETURN adattípus {IS | AS} [lokális deklarációk (A DECLARE kulcsszó nélkül)] blokk
PROCEDURE eljárásnév; FUNCTION függvénynév;
DROP
RAISE_APPLICATION_ERROR(hibakód, üzenet)
DESC
user_source
EXECUTE
[felhasználó.]TároltEljárásNeve
Tárolt alprogramok törlése.
Tárolt alprogramok hibakezelése. Hibakód - tartománya: -20000 … -20999 üzenet - karaktersorozat, és rövidebb 2048 bájtnál
hiba_1 EXCEPTION; hiba_2 EXCEPTION; BEGIN … IF .. THEN RAISE hiba_1 … EXCEPTION WHEN hiba_1 THEN RAISE_APPLICATION_ERROR(-201 01, 'Kivétel az 1. alprogramban'); WHEN hiba_2 THEN RAISE_APPLICATION_ERROR(-201 02, 'Kivétel az 2. alprogramban'); END;
SET linesize 65 Tárolt alprogramok lekérdezése. DESC user_source SET linesize 400 Tárolt eljárások futtatása EXECUTE Összead(3,4) (közvetlenül SQL*Pluskörnyezetből) Triggerek
A Triggert indító események
A Triggerek alkalmazása
- a DELETE, INSERT, UPDATE DML-utasítások - a CREATE, ALTER, DROP DDL-utasítások - Rendszeresemények (adatbázis indítás, leállítás és hibaüzenetek) - Felhasználói események (ki és belépés)
Elsődleges cél az adatbázis integritásának védelme, az adatbázisban történt változások feljegyzése Triggereket használhatunk: - felhasználói előírások érvényesítésére - érvénytelen tranzakciók megelőzésére - származtatott (kiszámított) oszlopértékek automatikus generálására - adattáblák szinkronizálására - események (törlés, beszúrás, módosítás) naplóására
A Triggerek jellemzői
CREATE
[OR REPLACE] TRIGGER triggernév [BEFORE | AFTER | INSTEAD OF] triggerelő_esemény [OR triggerelő_esemény]… ON {adattábla | nézettábla} [FOR EACH ROW [WHEN feltétel] ] [DECLARE lokális változó deklarációja] BEGIN utasítások [kivételkezelés] END;
A tárolt alprogramok és triggerek lekérdezése: A triggerek felépítése és lekérdezése: A trigger teljes forráskódja:
A korrelációs nevek használata
DROP
TRIGGER TriggerNeve;
Azonos eseményekre tüzelő triggerek
ALTER
TRIGGER Triggerneve [DISABLE | ENABLE] [ALL TRIGGERS];
A triggerelő esemény meghatározása: Ha egy trigger több műveletet is elindíthat, akkor a feldolgozásnál szükség lehet az indító művelet meghatározására. Erre a célra szolgálnak a trigger törzsében a DELETING, INSERTING, UPDATING függvények.
- a trigger időzítése (BEFORE, AFTER, INSTEAD OF) - A trigger által figyelt esemény (DELETE, INSERT, UPDATE) - A trigger típusa (utasításszintű, sorszintű) - A trigger által felügyelt (a triggerhez rendelt) objektum (adattábla, nézettábla)
A trigger szintaktikus alakja: triggerelő_esemény - egy DML utasításhoz kapcsolódik. Alakja: {DELETE | INSERT | UPDATE [OF oszlop]} OF - megadhatjuk, hogy melyik oszlop módosítására legyen érzékeny a trigger trigger tözse - tartalmazhat kivételkezelést FOR EACH ROW - ez teszi a triggert sorszintűvé. Minden olyen soron végrehajtódik, melyet a triggert kiváltó DML-utasítás érint. INSTEAD OF - ezek a triggerek csak sorszintűek lehetnek, ezért a FOR EACH ROW elhagyható. Mindíg az őt elindító DML-utasítás helyett aktiválódik, ezért elő kell írni a megfelelő DML-műveletet. BEFORE, AFTER - ezek a triggerek csak adattáblára, míg az INSTEAD OF triggerek csak nézettáblára definiálhatók. Az általuk felügyelt táblára vonatkozó triggerelő esemény hatására mindíg aktivizálódnak akkor is, ha az esemény egyetlen sort sem kezel OLD,NEW - a sorszintű triggerekben, valamint az e triggerekből hívott tárolt alprogramokban hivatkozhatunk a módosított sorok eredeti és új értékeire WHEN - az ez utáni feltétel a sorszintű triggerek működését az e feltétel által kijelölt sorokra korlátozza, ahol a feltételben az OLD és a NEW korrelációs nevekkel minősített adatokra kell hivatkozni.
CREATE OR REPLACE TRIGGER NeNyúljHozzá BEFORE DELETE OR INSERT OR UPDATE ON emp BEGIN IF TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:00' AND '16:30' THEN RAISE_APPLICATION_ERR OR(-20111, 'Csak munkaidőben szabad az adatbázis!'); END IF; END; / SHOW ERRORS
SELECT object_type, object_name FROM user_objects WHERE object_type IN ('PROCEDURE','FUNCTION','TRIGGER'); SELECT description FROM user_triggers; SELECT line, text FROM user_source WHERE UPPER(type)='TRIGGER' AND UPPER(name)='Triggerneve';
DELETE műveletnél - az utasításbeli adatra :OLD értékként kell hivatkozni, a :NEW értéke NULL INSERT műveletnél - az utasításbeli adatra :NEW értékként kell hivatkozni, az :OLD értéke NULL UPDATE műveletnél - az utasításbeli adatra :NEW értékként, a tárolt adatra pedig :OLD értékként kell hivatkozni. Trigger törlése Az azonos eseményekre tüzelő triggerek közül a legrégebbi aktivizálódik, ezért ügyelni kell arra, hogy az azonos fejrészű (description) triggerek közül a régebbieket töröljük. Tárolt trigger engedélyezése, tiltása: ENABLE - engedélyezés DISABLE - letiltás ALL TRIGGERS - egy tábla összes triggerére vonatkozik … IF DELETING THEN RAISE_APPLICATION_ERROR(-20211, 'Csak munkaidőben lehet törölni!'); ELSEIF INSERTING THEN …; ELSEIF UPDATING THEN …; …
