Na za=E8=E1tek mus=EDm =F8=EDct, =9Ee n=E1sleduj=EDc=ED texty jsou p= ouze v=FDtah ze cvi=E8en=ED DB2 na FAV Z=C8U v Plzni a autor p=F9vodn=ED= ch text=F9 je Ing. Martin Z=EDma. T=EDmto mu d=ECkuji za per= fektn=EC p=F8ipraven=E9 materi=E1ly na jednotliv=E1 cvi=E8en=ED. J=E1 js= em pouze vytahal z=E1klady, ob=E8as p=F8idal vlastn=ED koment=E1=F8e a s= na=9Eil jsem se z toho ud=EClat celkov=FD p=F8ehled. Pokud budete cht=ED= t dal=9A=ED informace, ur=E8it=EC se pod=EDvejte na ofici=E1ln=ED str=E1= nky cvi=E8en=ED: cvi=E8en=ED DB2.
Pokud se chcete v probl=E9mu po=F8=E1dn=EC vyznat, tak si nastudujte = ofici=E1ln=ED str=E1nky cvi=E8en=ED. Pokud si mysl=EDte, =9Ee se v tom u= =9E docela vyzn=E1te a pot=F8ebujete si to jen rychle zvopakovat na z=E1= po=E8tov=FD test z DB2, pak pr=E1v=EC pro V=E1s je ur=E8en n=E1slecuj=ED= c=ED =E8l=E1nek.
Z=E1kladn=ED vychyt=E1vky, kter=E9 lze pou=9E=EDt v p=F8=EDkazu SE= LECT:
where id is null, is not null
where not exists in (select * from ..)
select nvl(k.PIN,0) - null nahrad=ED nulou
obdoba left join: ... from osoby o, karty k where o.id_karty =3D k.id=
(+)
v order by nelze pou=9E=EDt agraga=E8n=ED fce, ale ze pou=9E=ED=
t alias:
order by pocet desc; nebo order by 2 desc;
union - slou=E8en=ED 2 select=F9
where - nesm=ED zde b=FDt agrega=E8n=ED dotaz
having - mus=ED zde b=FDt agrega=E8n=ED dotaz
p=F8. group by s.JMENO having count(c.ID_OSOBY) >=3D=
all (select count(id) ..)
z=E1kladn=ED agrega=E8n=ED fce: avg, min, max, sum, count(=
id nebo *)
Zaj=EDmav=E9 sql dotazy typu select:
- hodnota null nelze testovat pouh=FDm porovn=E1n=EDm, ale mus=ED se pou=
=9E=EDt is:
select count(id) from osoby where id_karty is<=
/b> null;
- pokud je vazba na kartu od osoby, ale my to pot=F8ebujem zjistit z =
opa=E8n=E9ho konce:
pozn. lze pou=9E=EDt i "not in"
select count(*) from karty where
<=
b>not exists (select * from osoby where osoby.ID_K=
ARTY =3D karty.ID_KARTY);
- m=EDsto left join lze pou=9E=EDt k na=E8ten=ED osoby a pinu jej=ED = karty tohle:
select o.ID_OSOBY, k.PIN from osoby o, karty k where = ; o.ID_KARTY =3D k.ID_KARTY;
- docela zaj=EDmav=E1 je mo=9Enost z=E1pisu left join v Oracle:
=
Identifika=E8n=ED =E8=EDslo osoby a PIN jej=ED karty v=E8etn=EC osob bez=
karty (u nich nastavte PIN na nulu).
- fce nvl nastav=ED hodnotu pin na 0, pokud je null
select o.ID_OSOBY, nvl(k.PIN, 0) as pin from osoby o, karty =
k where o.ID_KARTY =3D k.ID_KARTY(+);
ekvivalentn=ED z=E1pis:
select o.ID_OSOBY, nvl(k.PIN, 0) as pin from osob=
y o left join karty k on o.ID_KARTY =3D k.ID_KARTY;
- Jm=E9na skupin s po=E8tem =E8len=F9 se=F8azen=E1 podle po=E8tu =E8l= en=F9:
select s.JMENO, count(c.ID_OSOBY) as pocet from skupiny s=
, clenove_skupin c
where s.ID_SKUPINY =3D c.ID_SKUPINY group by s.=
JMENO order by pocet desc;
- v order by nelze pou=9E=EDt agrega=E8n=ED fce typu count, m=EDsto p= ocet lze napsat: order by 2 (po=F8ad=ED param. v selectu)
- dal=9A=ED zaj=EDmav=E9 parametry:
union - slou=9E=ED ke slou=E8en=ED 2 select=F9 - mus=ED m=EDt ste=
jn=FD po=E8et sloupc=F9 pro v=FDb=ECr a mus=ED b=FDt stejn=FD n=E1zev sl=
oupc=F9,
nebo jejich pojmenov=E1n=ED (nap=F8. user_name as jmeno)
where - nesm=ED zde b=FDt agrega=E8n=ED dotaz
having - mus=ED zde b=FDt agrega=E8n=ED dotaz
p=F8:
- Jm=E9no skupiny, kter=E1 m=E1 nejv=EDce =E8len=F9.
select s.JMENO as skupina from skupiny s, clenove=
_skupin c
where s.ID_SKUPINY =3D c.ID_SKUPINY
=
group by s.JMENO
having count(c.ID_OSOBY) >=3D=
all (select count(id_osoby) from clenove_skupin group =
by id_skupiny);
z=E1kladn=ED blok:
DECLARE (misto DECLARE lze napr. CREATE OR REPLACE PROCEDURE = vloz_hodnotu(d IN NUMBER) AS
<= /pre>
a NUMBER;
b NUMBE= R;
c NUMBER;
BEGIN
SELECT x, y INTO a, b FROM resu= lt_table WHERE sample_id =3D 1;
c :=3D a / b;
IF c >= 1 THEN
INSERT ...
ELSE
INSERT ...
END IF;
COMMIT;
EXCEPTION
WHEN ZERO_D= IVIDE THEN
INSERT ....
COMMIT;
WHE= N NO_DATA_FOUND THEN
dbms_output.put_line('Zaznam nenalezen'= ); // zazny zaznam
WHEN DUP_VAL_ON_INDEX THEN
= dbms_output.put_line('Duplicitni zaznam'); // pokud tam budou 2 zaznamy= s timto ID
WHEN OTHERS THEN ROLLBACK;
END;- vol=E1n=ED procedury:
Oracle - p=F8=EDmo: EXEC procedure_name;
MySql5 - p=F8=EDmo i nep=F8=EDmo: CALL procedure_name;nep=F8=EDmo:
Oracle - v jin=E9 procedu=F8e: procedure_name; (jen samotn=FD n=E1= zev)user exception:
- volani v kodu procedury: RAISE neplatna_hodnota;
- deklarace v EXCEPTION: WHEN neplatna_hodnota then ....- v=FDpis to hod=ED a=9E po skon=E8en=ED SQL BLOKU:
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Nastala chyba ! Blizsi info o chybe := ');
dbms_output.put_line('Cislo chyby : '||to_char(SQLCODE)); dbms_output.put_line('Popis chyby : '||SQLERRM);- ukon=E8=ED zpracov=E1n=ED SQL bloku - vol=E1n=ED v k=F3du:
raise_application_error(-20005, 'Plat neni vyplnen !', TRUE);
cyklus:LOOP ...
IF I > 100 THEN
= ; EXIT;
END IF;
END LOOP;nebo
WHILE podm=EDnka LOOP
...
END LOOP;nebo
FOR i IN REVERSE 1..10 LOOP p=F8=EDkaz; END LOOP;
typ z=E1znam:
DECLARE
<= /pre>
TYPE TZam IS RECORD (
os_cislo NU= MBER(5),
prijmeni VARCHAR2(30),
jmeno VARCHAR2(30) = );
zam1 TZam;
BEGIN
SELECT os_cislo, prijmeni= , jmeno INTO zam1 FROM zamestnanci WHERE os_cislo =3D 123;typov=E1n=ED prom=ECnn=FD:
os_cislo zamestnanci.os_cislo%TYPE,
zam1 zamestnanci%ROWTYPE;Bal=EDky procedur a fc=ED
INTERFACE:
CREATE OR REPLACE PACKAGE arithmetic AS
usage INT= EGER;
FUNCTION add(a IN INTEGER, b IN INTEGER) RETURN INTEGE= R;
FUNCTION sub(a IN INTEGER, b IN INTEGER) RETURN INT= EGER;
PROCEDURE inc(a IN OUT INTEGER);
END;
/=BODY:
CREATE OR REPLACE PACKAGE BODY arithmetic AS
FUNCTION= add(a IN INTEGER, b IN INTEGER) RETURN INTEGER IS
BEGIN &= #160; usage :=3D usage + 1;
RETURN (a + b);=
END;
...vol=E1n=ED:
a :=3D 6;
b :=3D arithmetic.add(a, 3);
arithmetic.inc(b);
dbms_output.put_line('Vysledek : '||b);KURZORY
DECLARE
tmp osoby%ROWTYPE;
CURSOR plist IS SELECT * FROM osoby;
= BEGIN
OPEN plist; =
LOOP
FETCH plist= INTO tmp;
EXIT WHEN plist%NOTFOUND;=
dbms_output.put_line(plist%ROWCOUNT= ||'. '||tmp.jmeno||' '||tmp.prijmeni);
END= LOOP;
CLOSE plist;
END;
/1. DEFINICE CURSORU: CURSOR plist IS select * from osoby;
2. otev=F8=EDt: OPEN
3. na=E8=EDst dal=9A=ED data: FETCH
4. zav=F8=EDt: close- deklarace siln=EC typovan=E9ho kurzoru:
TYPE t_crsr IS REF CURSOR RETURN osoby%ROWTYPE;
- deklarace slab=EC typovan=E9ho kurzoru:
TYPE t_crsr IS REF CURSOR;
plist t_crsr;
BEGIN
OPEN plist FOR SELECT * FROM osoby;- vlastnosti kurzoru:
%FOUND - BOOLEAN; TRUE pokud FETCH vybral dal=9A=ED z=E1znam, = FALSE pokud FETCH nic nevybral. P=F8ed prvn=EDm FETCH je NULL.
%NOTFOUND - BOOLEAN; opak %FOUND
%ISOPEN - BOOLEAN; TRUE pokud byl kurzor otev=F8en p=F8=EDkazem = OPEN, jinak FALSE.
%ROWCOUNT - INTEGER; po=E8et =F8=E1dk=F9deklarace kurzoru s parametrem:
CURSOR osoba (name IN VARCHAR2) IS
SELECT os_cislo
FROM osoby
WHERE jmeno =3D name;
- nebo pr=F9chod prvk=F9 lze ud=EClat takto:
FOR p IN plist(1) LOOP
dbms_output.put_line(p.jmeno||' '||p.prijmeni);
= END LOOP;Dynamick=E9 sql
- lze jen o ulo=9Een=ED sql dotazu do prom=ECnn=E9 a kurzor pochop=ED= , =9Ee ho m=E1 prov=E9st
TYPE t_cur IS REF CURSOR; list t_cur; ... core :=3D 'SELECT * FROM osoby WHERE '; cond1 :=3D 'os_cislo > 1'; stmt :=3D core||cond1; OPEN list FOR stmt; LOOP ...Oracle - v PL/SQL nelze p=F8=EDmo volat DDL p=F8=EDkazy. Lze je v=9Aa= k volat dynamicky pomoc=ED direktivy EXECUTE IMMEDIATE :
CREATE OR REPLACE PROCEDURE zrus_tabulku(jmeno IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'DROP TABLE '||jmeno;TRIGERY
CREATE [OR REPLACE] TRIGGER jm=E9no triggeru
BEFORE | AF= TER
DELETE | INSERT | < UPDATE [OF jm=E9no sloupce]>
ON = jm=E9no tabulky
[REFERENCING < OLD AS jm=E9no | NEW AS jm=E9no &= gt;]
[FOR EACH ROW]
[WHEN podm=EDnka]
BEGIN
&l= t; ... PL/SQL blok ... >
END;- p=F8=EDstup k vkl=E1dan=FDm/m=ECn=ECn=FDm/mazan=FDm dat=F9m lze ud=EC= lat p=F8es :new.slupec, nebo :old.sloupec
- pr=E1ce s triggery:
zak=E1zat:
ALTER TRIGGER jm=E9no DISABLE;
povolit:
ALTER TRIGGER jm=E9no ENABLE;
smazat:
DROP TRIGGER jm=E9no;Sekvence
Sekvence =3D gener=E1tor "ID=E8ek", proto=9Ee Oracle neum=ED AUTO_INC= REMENT, tak se mus=ED pou=9E=EDt tohle:
CREATE SEQUENCE jm=E9no
[INCREMENT BY] <cel=E9 =E8=EDslo> /* defa= ult 1 */
[START WITH] <cel=E9 =E8=EDslo> = 0; /* default 1 */
[MAXVALUE] <cel=E9 =E8=EDslo>= /* default 10^27 */
[MINVALUE] <cel=E9 =E8=EDslo>= /* default 1 */
[CYCLE | NOCYCLE] = /* default NOCYCLE */V=FDznam jednotliv=FDch klauzul=ED je n=E1sleduj=EDc=ED :
* INCREMENT BY - hodnota o kterou m=E1 b=FDt =E8= =EDta=E8 zv=ECt=9Aen (m=F9=9Ee b=FDt i z=E1porn=E1)
* START WITH - hodnota od kter=E9 m=E1 =E8=EDt= a=E8 za=E8=EDt (hod=ED se chceme-li sekvenci pou=9E=EDt pro generov=E1n=ED= kl=ED=E8e do
tabulky, ve kter=E9 jsou ji=9E data)
* MAXVALUE - nejvy=9A=9A=ED povolen=E1 hodnota= =E8=EDta=E8e
* NOCYCLE - p=F8i p=F8ekro=E8en=ED MAXVALUE na= stane chyba
* CYCLE - p=F8i p=F8ekro=E8en=ED MAXVALUE za=E8= ne =E8=EDta=E8 znova od MINVALUEuk=E1zka:
CREATE SEQUENCE seq_osoba_id MAXVALUE 99999 CREATE TRIGGER tr= i_osoby_insert
BEFORE INSERT ON osoby
FOR EACH ROW BEGIN <= br /> SELECT seq_osoba_id.NEXTVAL INTO :new.id FROM DUAL;
END;
/OPTIMALIZACE DOTAZU
- Oracle pou=9E=EDv=E1 2 z=E1kladn=ED techniky optimalizace:
- RBO (tj. Rule Based Optimization)
- CBO (Cost Based Optimization) - od verze 9i S=D8BD Oracle je toto pref= erov=E1no
- vysv=ECtlen=ED pl=E1nu: explain plan for select NAZEV ...
- zlep=9Aen=ED se dos=E1hne po p=F8id=E1n=ED index=F9, pokud naopak odeb= eru statistiky:
ANALYZE TABLE mistnosti DELETE STATISTICS; - tak se zhor=9A=ED pr=F9chod= nost a doba odezvy
.. v=EDce jsem tohle nestudoval, proto=9Ee to je nuda a nav=EDc to asi n= ikdy nikdo z n=E1s d=EClat nebude.
P=F8=EDpadn=EC to lze naj=EDt v manu=E1lu. Pokud v=9Aak budete m=EDt tab= ulku spr=E1vn=EC navr=9Eenou, tak
si optimalizaci za=F8=EDd=ED Oracle s=E1m.VNO=D8EN=C9 SQL
Asi jedin=E1 d=F9le=9Eit=E1 v=ECc je tahle pozn=E1mka:
Princip vno=F8en=E9ho SQL spo=E8=EDv=E1 v tom, =9Ee umo=9E=F2uje do n=EC= kter=E9ho z vy=9A=9A=EDch programovac=EDch jazyk=F9 vkl=E1dat p=F8=EDkaz= y SQL ozna=E8en=E9 standardn=EDm prefixem. Ty jsou pak pomoc=ED prekompi= l=E1toru p=F8elo=9Eeny na vol=E1n=ED funkc=ED knihovny, kter=E1 realizuj= e spojen=ED s datab=E1zov=FDm serverem, p=F8eklad po=9Eadavk=F9 na serve= r a odpov=ECd=ED serveru. Soubor vytvo=F8en=FD prekompil=E1torem lze ji=9E= p=F8elo=9Eit p=F8eklada=E8em konkr=E9tn=EDho programovac=EDho jazyka a = p=F8ipojit pot=F8ebn=E9 knihovny.Nav=EDc to lze vyu=9E=EDt asi je v kombinaci s C/C++.
Praktick=E9 vyu=9Eit=ED:
asi se to moc v dne=9An=ED dob=EC pou=9E=EDt ned=E1. Je dobr=E9 v=ECd= =ECt, =9Ee n=ECco takov=E9ho existuje, ale asi je lep=9A=ED se tomu vyhn= out.
V=FDvoj aplikace by m=ECl b=FDt asi n=E1sleduj=EDc=ED:
1. vytvo=F8=EDm si datab=E1zi, tabulky, ulo=9Een=E9 procedury = a triggery. Je dobr=E9 si d=E1t pozor, aby ulo=9Een=E9 procedury a trigg= ery nebyly n=ECjak=E1 obrovsk=E1 monstra. M=ECly by b=FDt velice jednodu= ch=E9 a pr=F9hledn=E9, aby neobsahovaly =9E=E1dn=E9 chyby
=
a nemohly tam b=FDt =9E=E1dn=E9 dal=9A=ED chyby vneseny. Pokud budete m=ED= t chybu v aplikaci, tak j=ED ur=E8it=EC relativn=EC rychle odhal=EDte a = oprav=EDte. Pokud v=9Aak bude chyba v datov=E9 vrstv=EC, tak V=E1m to m=F9= =9Ee zp=F9sobit opravdu HODN=CC probl=E9m=F9 a
zp=F9soben=E9 =9Akody budou s =E8asem exponenci=E1ln=EC r=F9st. Z tohoto= d=F9vodu maj=ED i n=ECkte=F8=ED v=FDvoj=E1=F8i tro=9Aku revolu=E8n=ED n= =E1zory, =9Ee del=9A=ED ulo=9Een=E9 procedury a triggery by se nem=ECly = pou=9E=EDvat v=F9bec. Asi jedin=E9 pou=9Eit=ED trigger=F9 je v
kombinaci s gener=E1tory/sekvencemi na S=D8BD Oracle, nebo Firebird.2. vytvo=F8=EDm si datovou vrstvu pro komunikaci s datab=E1z=ED= , odkud se budou volat SQL dotazy na datab=E1zi standardn=EDm
zp=F9sobem.Nap=F8. v php takto:
$result =3D mysql_query('SELECT * WHERE 1=3D1');
$row =3D mysql_fetch_assoc($result); // na=E8ten=ED v=FDsledk=F9 do prom= =ECnn=FDch3. vytvo=F8=EDm si aplika=E8n=ED vrstvu, kter=E1 bude s datovo= u vrstvou komunikovat
Pokud se budete dr=9Eet tohoto postupu, tak m=E1te jistotu, =9Ee v=FD= voj bude prob=EDhat v po=F8=E1dku.
Co bylo v z=E1po=E8tov=E9m testu z DB2 ?
Byly 2 varianty - zelen=E1 a r=F9=9Eov=E1. Slo=9Eitost=ED byly ob=EC = dv=EC velice podobn=E9. Test byl na 60 minut a musim =F8=EDct, =9Ee to b= ylo docela =EF=E1belsk=FD . Jednotliv=E9 sql d= otazy typu SELECT byly hodn=EC dlouh=FD a relativn=EC dost slo=9Eit=FD. = V=9Aechno se to dalo vymyslet, ale cht=EClo to tro=9Aku cviku v t=E9to o= blasti. Nemus=EDte to um=ECt v=9Aechno p=F8esn=EC, drobn=E9 chyby budou = tolerov=E1ny. Tak a tady u=9E je to zad=E1n=ED:
Zelen=E1 varianta
1. m=E1me 3 tabulky: v=ECze=F2, dozorce a trestn=FD =E8in - je= dn=E1 se o =E8=EDslen=EDky.
a) nakreslete ERA model a prove=EFte rozklad vazem M:N - pot=F8e= bujem rozklad vazby v=ECze=F2 - trestn=FD =E8in a dozorce - v=ECze=F2. J= eden v=EC=9Ee=F2 mohl sp=E1chat v=EDce tresn=FDch =E8in=F9 a na jednoho = v=ECzn=EC m=F9=9Ee z=E1rove=F2 dohl=ED=9Eet v=EDce dozorc=F9.
b) napi=9Ate DDL p=F8=EDkaz na vytvo=F8en=ED vazebn=ED tabulky v= =ECze=F2-trestn=FD =E8in (sta=E8=ED atributy: ID PK, RC_VEZEN FK, KOD_TR= ESTNEHO_CINU) a zajist=ECte referen=E8n=ED integritu - mus=EDte nadefino= vat prim=E1rn=ED a ciz=ED kl=ED=E8e.2. a) a b) napi=9Ate dotazy typu SELECT na vybr=E1= n=ED n=EC=E8eho. Jedn=E1 se o pokro=E8il=E9 selekty a o v=FDb=ECr z v=ED= ce tabulek z=E1rove=F2 - a=9E ze 4 tabulek. Je zde t=F8eba pou=9E=EDt ag= rega=E8n=ED funkce, having apod. Kdy=9E pochop=EDte cv. =E8. 2 z DB2, ta= k budete v pohod=EC.
3. Vytvo=F8en=ED a pou=9Eit=ED kurzoru:
a) vytvo=F8te kurzor, kter=FD na=E8te v=9Aechny v=ECzn=EC, kter=FD= sp=E1chali dan=FD trestn=FD =E8in - asi. Sta=E8=ED jen definice kurzoru= v z=E1kladn=ED variant=EC typu:CURSOR plist IS SELECT * FR= OM osoby
. Pozor: ten select nen=ED =FApln=EC trivi=E1ln=ED.
= b) napi=9At=EC ulo=9Eenou funkci, kter=E1 pou=9Eije kurzor a vyp= =ED=9Ae v=9Aechny v=ECzn=EC - s vyu=9Eit=EDm dbms_output.put_line('text'= );
c) zavolejte ulo=9Eenou funkci4. Triggery
<= /div>
a) vytvo=F8te trigger na vkl=E1d=E1n=ED nov=E9ho dozorce, automa= ticky vypl=F2te DATUM - dle syst=E9mu a USER - aktu=E1ln=EC p=F8ihl=E1=9A= en=FD u=9Eivatel a asi je vhodn=FD vygenerovat cislo_dozorce. Nap=F8. ja= ko maximum ze v=9Aech =E8=EDsel+1, nebo s vyu=9Eit=EDm SEKVENCE.
b) vytvo=F8te trigger, kter=FD bude reagovat na p=F8id=E1n=ED tr= estu k v=ECz=F2ovi a automaticky mu zv=ECt=9A=ED datum propu=9At=ECn=ED = o d=E9lku trestu v tabulce trest=F9. Je pot=F8eba asi vy=F8e=9Ait konver= zi mezi typem Date a Number, ve kter=E9m je po=E8et m=ECs=EDc=F9 trestu.= K tomu je asi vhodn=FD se pod=EDvat na podm=EDnky - IF ... END IF;Srandovn=ED hra=E8ky
Srandovn=ED hra=E8ky