6. CVIČENÍ

PL/SQL

PL/SQL je rozšíření jazyka SQL o procedurální rysy. Je specifické pro produkty firmy Oracle, procedurální rozšíření SQL produktů jiných firem se zpravidla navzájem liší. Základním stavebním kamenem PL/SQL je tzv. PL/SQL blok, který může být buď tělem triggeru, procedry a funkce, nebo samostatný. Struktura PL/SQL bloku je následující :

DECLARE

deklarace konstant a proměnných

BEGIN

výkonné příkazy

EXCEPTION

výkonné příkazy

END;

Sekce EXCEPTION je učena pro ošetření výjimečných situací, zeleně zvýrazněné sekce jsou nepovinné. Pro lepší představu si prohlédněte následující ukázku :

DECLARE
    numerator    NUMBER;
    denominator  NUMBER;
    the_ratio    NUMBER;
    lower_limit  CONSTANT NUMBER := 0.72;
    samp_num     CONSTANT NUMBER := 132;

BEGIN
    SELECT  x, y INTO numerator, denominator
      FROM  result_table
     WHERE  sample_id = samp_num;
    the_ratio := numerator / denominator;
    IF the_ratio > lower_limit THEN
        INSERT INTO ratio VALUES (samp_num, the_ratio);
    ELSE
        INSERT INTO ratio VALUES (samp_num, -1);
    END IF;
    COMMIT;

EXCEPTION
    WHEN ZERO_DIVIDE THEN
        INSERT INTO ratio VALUES (samp_num, 0);
        COMMIT;

    WHEN OTHERS THEN
        ROLLBACK;
END;

Tento příklad ukazuje tzv. anonymní PL/SQL blok. Při použití SQL konzole je PL/SQL blok spuštěn teprve po zadání lomítka na začátku samostatného řádku.

Datové typy podporované v PL/SQL

Datové typy z SQL	Speciální PL/SQL typy	Složené datové typy
NUMBER	BINARY_INTEGER	RECORD
REAL	PLS_INTEGER	TABLE
FLOAT	BOOLEAN	VARRAY
CHAR
VARCHAR
VARCHAR2
DATE
LONG
LONG RAW
RAW
ROWID

Řídicí struktury

Větvení programu - příkaz IF-THEN-ELSE :

IF podmínka THEN
   příkaz_1;
   příkaz_2;
END IF;

IF podmínka THEN
   příkaz_1;
ELSE
   příkaz_2;
END IF;

IF podmínka_1 THEN
   příkaz_1;
ELSIF podmínka_2 THEN
   příkaz_2;
ELSIF podmínka_3 THEN
   příkaz_3;
ELSE
   příkaz_4;
END IF;













Ekvivalentní zápis :

IF podmínka_1 THEN
  příkaz_1;
ELSE
  IF podmínka_2 THEN
    příkaz_2;
  ELSE
    IF podmínka_3 THEN
      příkaz_3;
    ELSE
      příkaz_4;
    END IF;
  END IF;
END IF;

Iterace a smyčky - příkaz LOOP :

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

LOOP
  .
  .
  .
  EXIT WHEN I > 100;
  .
  .
  .
END LOOP;

Iterace a smyčky - příkaz WHILE-LOOP :

WHILE podmínka LOOP
  .
  .
  příkaz;
  .
  .
END LOOP;

Iterace a smyčky - příkaz FOR-LOOP :

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

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

SELECT COUNT(os_cislo) INTO pocet FROM osoby;
FOR i IN 1..pocet LOOP
  ...
END LOOP;

Skoky - příkaz GOTO :

BEGIN
  ...
  GOTO vloz_zaznam;
  ...
  <<vloz_zaznam>>;
  INSERT INTO osoby VALUES ...
  ...
END;

Prázdný příkaz - NULL :

BEGIN
  ...
  IF a > 10 THEN
    -- toto je komentář
    NULL;
  ELSE
    x := a - 10;
  END IF;
  ...
END;

Strukturované datové typy :

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

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

  zam2 := zam1;
  zam2.os_cislo := zam1.os_cislo + 1;
  zam2.prijmeni := 'MARTAN';

  INSERT INTO zamestnanci (os_cislo, prijmeni, jmeno)
                   VALUES (zam2.os_cislo, zam2.prijmeni, zam2.jmeno);
  COMMIT;
END;

Deklarace z výše uvedeného příkladu lze napsat i takto :

DECLARE
  TYPE TZam IS RECORD (
      os_cislo  zamestnanci.os_cislo%TYPE,
      prijmeni  zamestnanci.prijmeni%TYPE,
      jmeno     zamestnanci.jmeno%TYPE );
  zam1 TZam;
  zam2 TZam;
...

Pseudoproměnná %TYPE představuje datový typ sloupce tabulky. V deklaracích lze použít i pseudoproměnnou %ROWTYPE spojenou s tabulkou nebo pohledem. Představuje strukturovaný datový typ RECORD se strukturou shodnou s řádkem dané tabulky. Výše uvedené deklarace lze tedy zjednodušit takto :

DECLARE
  zam1 zamestnanci%ROWTYPE;
  zam2 zamestnanci%ROWTYPE;
...

Zpracování výjimek - sekce EXCEPTION :

DECLARE
  TYPE TZam IS RECORD (
    prijmeni  osoby.prijmeni%TYPE;
    jmeno     osoby.jmeno%TYPE;
  );
  zam Tzam;

BEGIN
  SELECT prijmeni, jmeno INTO zam FROM osoby WHERE os_cislo = 123;
...
  INSERT INTO osoby (os_cislo, prijmeni, jmeno)
             VALUES (123, 'PESICKA', 'Ladislav');
...
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('Osoba nebyla nalezena');

    WHEN DUP_VAL_ON_INDEX THEN
      dbms_output.put_line('Duplicitni osobni cislo');
...
END;

Deklarace uživatelských výjimek :

DECLARE
  zam_plat   NUMBER(5,2);
  novy_plat  NUMBER(5,2);
  nema_plat  EXCEPTION;

BEGIN
  SELECT plat INTO zam_plat FROM osoby WHERE os_cislo = 123;
  IF zam_plat IS NULL THEN
    RAISE nema_plat;
  END IF;
...
  EXCEPTION
    WHEN nema_plat THEN
      UPDATE osoby SET plat = novy_plat WHERE os_cislo = 123;
      COMMIT;
...
END;

Chceme-li odchytit všechny (popř. ostatní) výjimky, použijeme konstrukci EXCEPTION WHEN OTHERS THEN. Bližší informace o chybě jsou uloženy do pseudoproměnných SQLCODE a SQLERRM, které obsahují číselný kód a popis poslední chyby :

DECLARE
...
BEGIN
...
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);

END;

Poznámka : při ladění lze využít standardního balíku DBMS_OUTPUT pro ladicí výpisy. Výstup na konzoli se však musí povolit příkazem SET SERVEROUTPUT ON. Jak provádět ladící výpisy je vidět z uvedených příkazů. Pozor ! Výpis je proveden až po skončení PL/SQL bloku.

Voláním standardní procedury raise_application_error lze vypropagovat výjimku přímo do aplikace volající anonymní PL/SQL blok, proceduru nebo funkci. Voláním této funkce je ukončeno zpracování PL/SQL bloku, procedury nebo funkce. Pro tyto výjimky jsou rezervována čísla chyb od -20000 do -20999 :

DECLARE
...
BEGIN
...
  IF plat IS NULL THEN
    raise_application_error(-20005, 'Plat neni vyplnen !', TRUE);
  END IF;
...
END;

Podrobnější popis PL/SQL naleznete v PL/SQL User's Guide and Reference.