Oracle - základy

Na začátek musím říct, že následující texty jsou pouze výtah ze cvičení DB2 na FAV ZČU v Plzni a autor původních textů je Ing. Martin Zíma. Tímto mu děkuji za perfektně připravené materiály na jednotlivá cvičení. Já jsem pouze vytahal základy, občas přidal vlastní komentáře a snažil jsem se z toho udělat celkový přehled. Pokud budete chtít další informace, určitě se podívejte na oficiální stránky cvičení: cvičení DB2.

Pokud se chcete v problému pořádně vyznat, tak si nastudujte oficiální stránky cvičení. Pokud si myslíte, že se v tom už docela vyznáte a potřebujete si to jen rychle zvopakovat na zápočtový test z DB2, pak právě pro Vás je určen náslecující článek.

Rychlý přehled

Základní vychytávky, které lze použít v příkazu SELECT:

where id is null, is not null
where not exists in (select * from ..)
select nvl(k.PIN,0) - null nahradí nulou
obdoba left join: ... from osoby o, karty k where o.id_karty = k.id(+)

v order by nelze použít agragační fce, ale ze použít alias:
order by pocet desc; nebo order by 2 desc;

union - sloučení 2 selectů
where - nesmí zde být agregační dotaz
having - musí zde být agregační dotaz
př. group by s.JMENO having count(c.ID_OSOBY) >= all (select count(id) ..)

základní agregační fce: avg, min, max, sum, count(id nebo *)

SELECT

Zajímavé sql dotazy typu select:

- hodnota null nelze testovat pouhým porovnáním, ale musí se použít is:

select  count(id) from osoby where  id_karty is null;

- pokud je vazba na kartu od osoby, ale my to potřebujem zjistit z opačného konce:
pozn. lze použít i "not in"

select  count(*) from  karty   where
   not exists (select  * from  osoby where  osoby.ID_KARTY = karty.ID_KARTY);

- místo left join lze použít k načtení osoby a pinu její karty tohle:

select o.ID_OSOBY, k.PIN from osoby o, karty k where o.ID_KARTY = k.ID_KARTY;

- docela zajímavá je možnost zápisu left join v Oracle:

Identifikační číslo osoby a PIN její karty včetně osob bez karty (u nich nastavte PIN na nulu).

- fce nvl nastaví 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 = k.ID_KARTY(+);

ekvivalentní zápis:

select  o.ID_OSOBY, nvl(k.PIN, 0) as pin from  osoby o left join karty k on o.ID_KARTY = k.ID_KARTY;

- Jména skupin s počtem členů seřazená podle počtu členů:

select s.JMENO, count(c.ID_OSOBY) as pocet from skupiny s, clenove_skupin c
where s.ID_SKUPINY = c.ID_SKUPINY group by s.JMENO order by pocet desc;

- v order by nelze použít agregační fce typu count, místo pocet lze napsat: order by 2 (pořadí param. v selectu)

- další zajímavé parametry:
union - slouží ke sloučení 2 selectů - musí mít stejný počet sloupců pro výběr a musí být stejný název sloupců,
nebo jejich pojmenování (např. user_name as jmeno)
where - nesmí zde být agregační dotaz
having - musí zde být agregační dotaz

př:
- Jméno skupiny, která má nejvíce členů.

select  s.JMENO as skupina from  skupiny s, clenove_skupin c
      where  s.ID_SKUPINY = c.ID_SKUPINY
      group  by s.JMENO
      having  count(c.ID_OSOBY) >= all (select  count(id_osoby) from  clenove_skupin group  by id_skupiny);

PL/ SQL

základní blok:

DECLARE (misto DECLARE lze napr. CREATE OR REPLACE PROCEDURE vloz_hodnotu(d IN NUMBER) AS
   a NUMBER;          
   b NUMBER;
   c NUMBER;
 BEGIN
   SELECT x, y INTO a, b FROM result_table WHERE sample_id = 1; 
   c := a / b;
   IF c > 1 THEN  
      INSERT ...  
   ELSE 
      INSERT ...
   END IF;  
   COMMIT;     
EXCEPTION 
   WHEN ZERO_DIVIDE THEN
       INSERT ....
       COMMIT;

   WHEN 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ání procedury:
Oracle - přímo: EXEC procedure_name;
MySql5 - přímo i nepřímo: CALL procedure_name;

nepřímo:
Oracle - v jiné proceduře: procedure_name; (jen samotný název)

user exception:
- volani v kodu procedury: RAISE neplatna_hodnota;
- deklarace v EXCEPTION: WHEN neplatna_hodnota then ....

- výpis to hodí až po skončení 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čí zpracování SQL bloku - volání v kódu:
raise_application_error(-20005, 'Plat neni vyplnen !', TRUE);

cyklus:

LOOP    ... 
  IF I > 100 THEN 
       EXIT; 
  END IF;
  END LOOP;

nebo

WHILE podmínka LOOP
...
END LOOP;

nebo

FOR i IN REVERSE 1..10 LOOP   příkaz; END LOOP;

typ záznam:

DECLARE  
   TYPE TZam IS RECORD (
     os_cislo NUMBER(5),
     prijmeni VARCHAR2(30), 
     jmeno VARCHAR2(30) ); 

zam1 TZam; 
BEGIN 
   SELECT os_cislo, prijmeni, jmeno INTO zam1 FROM zamestnanci WHERE os_cislo = 123;

typování proměnný:

os_cislo zamestnanci.os_cislo%TYPE,
zam1 zamestnanci%ROWTYPE;

Balíky procedur a fcí

INTERFACE:

CREATE OR REPLACE PACKAGE arithmetic AS 
      usage INTEGER; 
      FUNCTION add(a IN INTEGER, b IN INTEGER) RETURN INTEGER; 
      FUNCTION  sub(a IN INTEGER, b IN INTEGER) RETURN INTEGER; 
      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          usage := usage + 1; 
      RETURN (a + b); 
  END; 
 ...

volání:

a := 6;
b := 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řít: OPEN
3. načíst další data: FETCH
4. zavřít: close

- deklarace silně typovaného kurzoru:

TYPE t_crsr IS REF CURSOR RETURN osoby%ROWTYPE;

- deklarace slabě typovaného 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ší záznam, FALSE pokud FETCH nic nevybral. Před prvním FETCH je NULL.
%NOTFOUND - BOOLEAN; opak %FOUND
%ISOPEN - BOOLEAN; TRUE pokud byl kurzor otevřen příkazem OPEN, jinak FALSE.
%ROWCOUNT - INTEGER; počet řádků

deklarace kurzoru s parametrem:

CURSOR osoba (name IN VARCHAR2) IS
    SELECT os_cislo
      FROM osoby
     WHERE jmeno = name;

- nebo průchod prvků lze udělat takto:

FOR p IN plist(1) LOOP
    dbms_output.put_line(p.jmeno||' '||p.prijmeni);
END LOOP;

Dynamické sql

- lze jen o uložení sql dotazu do proměnné a kurzor pochopí, že ho má provést

  TYPE t_cur IS REF CURSOR;
  list  t_cur; ...
  core := 'SELECT * FROM osoby WHERE ';
  cond1 := 'os_cislo > 1';
  stmt := core||cond1;
  OPEN list FOR stmt;
  LOOP ...

Oracle - v PL/SQL nelze přímo volat DDL příkazy. Lze je však volat dynamicky pomocí 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éno triggeru
BEFORE | AFTER
DELETE | INSERT | < UPDATE [OF jméno sloupce]>
ON jméno tabulky
[REFERENCING < OLD AS jméno | NEW AS jméno >]
[FOR EACH ROW]
[WHEN podmínka]
BEGIN
 < ... PL/SQL blok ... > 
END;

- přístup k vkládaným/měněným/mazaným datům lze udělat přes :new.slupec, nebo :old.sloupec

- práce s triggery:

zakázat:
ALTER TRIGGER jméno DISABLE;

povolit:
ALTER TRIGGER jméno ENABLE;

smazat:
DROP TRIGGER jméno;

Sekvence

Sekvence = generátor "IDček", protože Oracle neumí AUTO_INCREMENT, tak se musí použít tohle:

CREATE SEQUENCE jméno
   [INCREMENT BY] <celé číslo>   /* default 1 */
   [START WITH]   <celé číslo>   /* default 1 */
   [MAXVALUE]     <celé číslo>   /* default 10^27 */
   [MINVALUE]     <celé číslo>   /* default 1 */
   [CYCLE | NOCYCLE]             /* default NOCYCLE */

Význam jednotlivých klauzulí je následující :

    * INCREMENT BY - hodnota o kterou má být čítač zvětšen (může být i záporná)
    * START WITH - hodnota od které má čítač začít (hodí se chceme-li sekvenci použít pro generování klíče do

tabulky, ve které jsou již data)
    * MAXVALUE - nejvyšší povolená hodnota čítače
    * NOCYCLE - při překročení MAXVALUE nastane chyba
    * CYCLE - při překročení MAXVALUE začne čítač znova od MINVALUE

ukázka:

CREATE SEQUENCE seq_osoba_id MAXVALUE 99999 CREATE TRIGGER tri_osoby_insert 
 BEFORE INSERT ON osoby 
 FOR EACH ROW BEGIN 
  SELECT seq_osoba_id.NEXTVAL INTO :new.id FROM DUAL; 
END;
/

OPTIMALIZACE DOTAZU

- Oracle používá 2 základní techniky optimalizace:

- RBO (tj. Rule Based Optimization)
- CBO (Cost Based Optimization) - od verze 9i SŘBD Oracle je toto preferováno

- vysvětlení plánu: explain plan for select NAZEV ...
- zlepšení se dosáhne po přidání indexů, pokud naopak odeberu statistiky:

ANALYZE TABLE mistnosti DELETE STATISTICS; - tak se zhorší průchodnost a doba odezvy

.. více jsem tohle nestudoval, protože to je nuda a navíc to asi nikdy nikdo z nás dělat nebude.
Případně to lze najít v manuálu. Pokud však budete mít tabulku správně navrženou, tak
si optimalizaci zařídí Oracle sám.

VNOŘENÉ SQL

Asi jediná důležitá věc je tahle poznámka:

Princip vnořeného SQL spočívá v tom, že umožňuje do některého z vyšších programovacích jazyků vkládat příkazy SQL označené standardním prefixem. Ty jsou pak pomocí prekompilátoru přeloženy na volání funkcí knihovny, která realizuje spojení s databázovým serverem, překlad požadavků na server a odpovědí serveru. Soubor vytvořený prekompilátorem lze již přeložit překladačem konkrétního programovacího jazyka a připojit potřebné knihovny.

Navíc to lze využít asi je v kombinaci s C/C++.

Praktické využití:

asi se to moc v dnešní době použít nedá. Je dobré vědět, že něco takového existuje, ale asi je lepší se tomu vyhnout.

Vývoj aplikace by měl být asi následující:

1. vytvořím si databázi, tabulky, uložené procedury a triggery. Je dobré si dát pozor, aby uložené procedury a triggery nebyly nějaká obrovská monstra. Měly by být velice jednoduché a průhledné, aby neobsahovaly žádné chyby
a nemohly tam být žádné další chyby vneseny. Pokud budete mít chybu v aplikaci, tak jí určitě relativně rychle odhalíte a opravíte. Pokud však bude chyba v datové vrstvě, tak Vám to může způsobit opravdu HODNĚ problémů a
způsobené škody budou s časem exponenciálně růst. Z tohoto důvodu mají i někteří vývojáři trošku revoluční názory, že delší uložené procedury a triggery by se neměly používat vůbec. Asi jediné použití triggerů je v
kombinaci s generátory/sekvencemi na SŘBD Oracle, nebo Firebird.

2. vytvořím si datovou vrstvu pro komunikaci s databází, odkud se budou volat SQL dotazy na databázi standardním
způsobem.

Např. v php takto:

$result = mysql_query('SELECT * WHERE 1=1');
$row = mysql_fetch_assoc($result); // načtení výsledků do proměnných

3. vytvořím si aplikační vrstvu, která bude s datovou vrstvou komunikovat

Pokud se budete držet tohoto postupu, tak máte jistotu, že vývoj bude probíhat v pořádku.

Co bylo v zápočtovém testu z DB2 ?

Byly 2 varianty - zelená a růžová. Složitostí byly obě dvě velice podobné. Test byl na 60 minut a musim říct, že to bylo docela ďábelský . Jednotlivé sql dotazy typu SELECT byly hodně dlouhý a relativně dost složitý. Všechno se to dalo vymyslet, ale chtělo to trošku cviku v této oblasti. Nemusíte to umět všechno přesně, drobné chyby budou tolerovány. Tak a tady už je to zadání:

Zelená varianta

1. máme 3 tabulky: vězeň, dozorce a trestný čin - jedná se o čísleníky.
a) nakreslete ERA model a proveďte rozklad vazem M:N - potřebujem rozklad vazby vězeň - trestný čin a dozorce - vězeň. Jeden věžeň mohl spáchat více tresných činů a na jednoho vězně může zároveň dohlížet více dozorců.
b) napište DDL příkaz na vytvoření vazební tabulky vězeň-trestný čin (stačí atributy: ID PK, RC_VEZEN FK, KOD_TRESTNEHO_CINU) a zajistěte referenční integritu - musíte nadefinovat primární a cizí klíče.

2. a) a b) napište dotazy typu SELECT na vybrání něčeho. Jedná se o pokročilé selekty a o výběr z více tabulek zároveň - až ze 4 tabulek. Je zde třeba použít agregační funkce, having apod. Když pochopíte cv. č. 2 z DB2, tak budete v pohodě.

3. Vytvoření a použití kurzoru:
a) vytvořte kurzor, který načte všechny vězně, který spáchali daný trestný čin - asi. Stačí jen definice kurzoru v základní variantě typu: CURSOR plist IS SELECT * FROM osoby. Pozor: ten select není úplně triviální.
b) napiště uloženou funkci, která použije kurzor a vypíše všechny vězně - s využitím dbms_output.put_line('text');
c) zavolejte uloženou funkci

4. Triggery
a) vytvořte trigger na vkládání nového dozorce, automaticky vyplňte DATUM - dle systému a USER - aktuálně přihlášený uživatel a asi je vhodný vygenerovat cislo_dozorce. Např. jako maximum ze všech čísel+1, nebo s využitím SEKVENCE.
b) vytvořte trigger, který bude reagovat na přidání trestu k vězňovi a automaticky mu zvětší datum propuštění o délku trestu v tabulce trestů. Je potřeba asi vyřešit konverzi mezi typem Date a Number, ve kterém je počet měsíců trestu. K tomu je asi vhodný se podívat na podmínky - IF ... END IF;