Základy 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ší. Výjimkou je ŠRBD DB2 společnosti IBM, který podporuje jak vlastní procedurální jazyk SQL PL, tak je plně kompatibilní s jazykem PL/SQL včetně datových typů. Základním stavebním kamenem PL/SQL je tzv. PL/SQL blok, který může být buď tělem triggeru, procedury a funkce, nebo samostatný.

Ještě než začneme vytvářet první PL/SQL blok, založíme si pro tyto případy jednoduchou tabulku LIDE a naplníme ji testovacími daty:

CREATE TABLE lide
(
  c_cloveka NUMBER       PRIMARY KEY,
  jmeno     VARCHAR2(30) NOT NULL,
  prijmeni  VARCHAR2(30) NOT NULL,
  plat      NUMBER       DEFAULT NULL
);

INSERT INTO lide (c_cloveka, jmeno, prijmeni) VALUES (1, 'Martin', 'Zíma');
INSERT INTO lide (c_cloveka, jmeno, prijmeni) VALUES (2, 'Martin', 'Kryl');
INSERT INTO lide (c_cloveka, jmeno, prijmeni) VALUES (3, 'Max', 'Otta');

COMMIT;

PL/SQL blok

Struktura PL/SQL bloku je následující:

DECLARE

deklarace konstant a proměnných;

BEGIN

výkonné příkazy bloku;

EXCEPTION

výkonné příkazy obsluh přerušení;

END;

/

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. Symbol lomítko na konci bloku označuje, že tady blok končí a např. v konzolovém klientovi sqlplus je blok následně vykonán.

Deklarativní část

deklarace proměnných a konstant
datové typy jsou základní (typy SQL) i složené
- typ záznam
- kurzor
lze definovat i proměnnou typu EXCEPTION

Datové typy podporované v jazyce PL/SQL

V jazyce PL/SQL lze použít kromě datových typů jazyka SQL též další speciální datové typy. Následující výčet ukazuje některé z nich:

datové typy z SQL
- NUMBER
- CHAR - maximálně 32 KB
- VARCHAR2 - maximálně 32 KB
- DATE
- BLOB - maximálně 128 TB
speciální PL/SQL datové typy
- PLS_INTEGER, BINARY_INTEGER - znamínkové celé číslo na 8 B
- BINARY_FLOAT - znamínkové reálné číslo na 4 B
- BINARY_DOUBLE - znamínkové reálné číslo na 8 B
- BOOLEAN - pro logické hodnoty, nabývá hodnot TRUE a FALSE
složené datové typy
- RECORD - typ záznam
- VARRAY - pole proměnné délky

Výkonná část

příkazy jazyka SQL
řídící konstrukce
- větvení programu
- cykly
přiřazovací příkaz
práce s kurzory

Příkazy jazyka SQL

dovoleny příkazy DML, TCL
příkazy INSERT, UPDATE, DELETE jsou bez omezení
příkaz SELECT má dvě možnosti
- získání dat konstrukcí INTO
- zpracování dat kurzorem

Příkaz `SELECT` – konstrukce `INTO`

jen v případě, že dotaz poskytuje maximálně jednu n-tici hodnot
dotazovaná data lze uložit do deklarovaných proměnných

DECLARE
  jm VARCHAR2(30);
  pr VARCHAR2(30);
BEGIN
  SELECT jmeno, prijmeni INTO jm, pr
    FROM lide
   WHERE c_cloveka = 1;

  INSERT INTO lide (c_cloveka, jmeno, prijmeni)
  VALUES (10, jm, pr);
  COMMIT;
END;
/

Řídící konstrukce programu

Větvení programu - konstrukce `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;
Základní zápis	Ekvivalentní zápis
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;	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;

Větvení programu - konstrukce `CASE`

Konstrukce IF Konstrukce CASE

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;

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

Iterace a smyčky - konstrukce `LOOP`

Základní zápis	Ekvivalentní zápis
LOOP . . . IF podminka_1 THEN EXIT; END IF; . . . IF podminka_2 THEN CONTINUE; END IF; . . . END LOOP;	LOOP . . . EXIT WHEN podminka_1; . . . CONTINUE WHEN podminka_2; . . . END LOOP;

Iterace a smyčky - konstrukce `WHILE-LOOP`

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

Iterace a smyčky - konstrukce `FOR-LOOP`

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

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

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

Skoky - konstrukce `GOTO`

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

Prázdný příkaz - konstrukce `NULL`

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

Přiřazovací příkaz

promenna := 15;

Zpracování výjimek

zvládá systémové i uživatelem definované výjimečné situace
vhodné pro různé ladící výpisy do specifického výstupu
obsluhou výjimečné situace končí vykonání bloku mezi BEGIN a END, kde ve kterém výjimka nastala

Ladící výpisy a standardní balík `DBMS_OUTPUT`

Během ladění PL/SQL bloku je potřeba využívat ladících výpisů. K tomuto účelu nabídí SŘBD Oracle standardní balík procedur a funkcí DBMS_OUTPUT, ze které běžný uživatel využije pouze tyto procedury:

PUT(znak) - zobrazí daný znak,
NEW_LINE() - odřádkuje,
PUT_LINE(retezec) - zobrazí daný řetězec a za ním odřádkuje.

Fungování uvedených metod závisí na aktiviaci speciálního výstupu. V prostředí konzolového klienta sqlplus daný výstup aktivujeme příkazem

SET SERVEROUTPUT ON

Pokud používáme nástroj Oracle SQL Developer, daný výstup aktivujeme zobrazením okna Dbms Output (z nabídky View/Dbms Output) a propojením tohoto okna s aktivním spojením do databáze. To provedeme kliknutím na tlačítko plus a následným výběrem požadovaného spojení.

Pozor ! Výpis uvedených metod je proveden vždy až po skončení PL/SQL bloku.

Ukázky zpracování výjimečných situací

DECLARE
  jm VARCHAR2(30);
  pr VARCHAR2(30);

BEGIN
  SELECT prijmeni, jmeno INTO pr, jm FROM lide WHERE c_cloveka = 123;
...
  INSERT INTO lide (c_cloveka, prijmeni, jmeno)
  VALUES (1, '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;
/

Kromě systémových výjimečných situací můžeme definovat též uživatelské výjimečné situace, které je možné též v sekci EXCEPTION obsloužit. Uživatelská výjimka je datového typu EXCEPTION.

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

BEGIN
  SELECT plat INTO zam_plat FROM lide WHERE c_cloveka = 1;
  IF zam_plat IS NULL THEN
    RAISE nema_plat;
  END IF;
...
  EXCEPTION
    WHEN nema_plat THEN
      UPDATE lide SET plat = novy_plat WHERE c_cloveka = 1;
      COMMIT;
...
END;
/

Chceme-li odchytit všechny (popř. ostatní) výjimky, použijeme konstrukci 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;
/

Uživatelem definovaná výjimečná situace

Pokud chceme uživateli oznámit, že nastala nějaká chyba (výjimečná situace), použijeme volání procedury RAISE_APPLICATION_ERROR(). Ta dovolí uživateli přidělit chybě číslo výjimky a také stručný popis, co se stalo - maximálně 2048 B. Pro tyto výjimky jsou rezervována čísla chyb z intervalu -20000 až -20999. Poslední nepovinný parametr procedury určuje, zda daná zpráva bude zhrnuta do chybového zásobníku (hodnota TRUE) nebo ne (hodnota FALSE - ta je výchozí).

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

Strukturované datové typy

V PL/SQL blocích se velmi často pracuje s objekty typu RECORD, které obsahují např. celé záznamy uložené v tabulce. Pro získání odpovídající hodnoty je použit příkaz SELECT doplněný o specifickou klauzuli INTO.

DECLARE
  TYPE TClovek IS RECORD (
    c_cloveka NUMBER,
    jmeno VARCHAR2(30),
    prijmeni VARCHAR2(30),
    plat NUMBER
  );

instalace proměnné nového typu

  clovek1 TClovek;
  clovek2 TClovek;

naplnění objektu

BEGIN
  SELECT c_cloveka, jmeno, prijmeni, plat INTO clovek1
    FROM lide
   WHERE c_cloveka = 1;

hluboká kopie objektu

  clovek2 := clovek1;

přístup k položkám

  clovek2.c_cloveka := clovek2.c_cloveka + 10;

vložení objektu do databáze

  INSERT INTO lide (c_cloveka, jmeno, prijmeni)
  VALUES (clovek2.c_cloveka, clovek2.jmeno, clovek2.prijmeni);
  COMMIT;
END;
/

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

DECLARE
  TYPE TClovek IS RECORD 
  (
    c_cloveka lide.c_cloveka%TYPE,
    jmeno     lide.jmeno%TYPE,
    prijmeni  lide.prijmeni%TYPE,
    plat      lide.plat%TYPE
  );
  
clovek1 TClovek;
clovek2 TClovek;

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
  clovek1 lide%ROWTYPE;
  clovek2 lide%ROWTYPE;

Uložené procedury a funkce

Všechny výše uvedené anonymní PL/SQL bloky lze natrvalo uložit do databáze, a to buď ve tvaru procedury nebo funkce. Způsob vytvoření uložené procedury ukazuje následující příklad:

CREATE OR REPLACE PROCEDURE kopiruj_zamestnance AS
  clovek1 lide%ROWTYPE;
  clovek2 lide%ROWTYPE;

BEGIN
  SELECT c_cloveka, jmeno, prijmeni, plat INTO clovek1
    FROM lide
   WHERE c_cloveka = 1;

  clovek2 := clovek1;
  clovek2.c_cloveka := clovek2.c_cloveka + 10;

  INSERT INTO lide (c_cloveka, jmeno, prijmeni)
  VALUES (clovek2.c_cloveka, clovek2.jmeno, clovek2.prijmeni);
  COMMIT;
END;
/

Pokud uložení procedury nebo funkce neproběhlo bez chyb, nelze ji používat a je nutné ji opravit. Ke zjištění, jaká/é chyba/y se vyskytla/y slouží v konzolovém klientovi sqlplus příkaz SHOW ERRORS. Ten vypíše popis poslední chyby, na kterou při ukládání (kompilaci) narazil. Proto je vhodné tento postup opakovat tak dlouho, dokud proces ukládání proběhne bez chyb.

Proceduru můžete zavolat (spustit) různými způsoby:

pomocí direktivy EXEC
```
EXEC kopiruj_zamestnance;
```

v těle jiného PL/SQL bloku

BEGIN
  ...
  kopiruj_zamestnance;
  ...  
END;
/

Uložené procedury a funkce mohou mít parametry, které mohou být:

IN - vstupní
OUT - výstupní
IN OUT - vstupní i výstupní

Procedura se vstupními parametry:

CREATE OR REPLACE PROCEDURE nastav_plat (id IN NUMBER, novy_plat IN NUMBER) AS
  zam_plat  NUMBER(5,2);
  nema_plat EXCEPTION;
BEGIN
  SELECT plat INTO zam_plat FROM lide WHERE c_cloveka = id;
  
  IF zam_plat IS NULL THEN
    RAISE nema_plat;
  END IF;
  
EXCEPTION
  WHEN nema_plat THEN
    UPDATE lide SET plat = novy_plat WHERE c_cloveka = id;
    COMMIT;
END;
/

Volání procedury s parametry:

EXEC nastav_plat(1, 10000);

Procedura se vstupně-výstupním parametrem:

CREATE OR REPLACE PROCEDURE inc(a IN OUT NUMBER) AS
BEGIN
  a := a + 1;
END; 
/

Volání procedury s vstupně-výstupním parametrem:

DECLARE
  a NUMBER;
BEGIN
  a := 9;
  inc(a);
  dbms_output.put_line('Vysledek : '||a);
END;
/

Způsob vytvoření uložené funkce (s parametry) ukazuje následující příklad:

CREATE OR REPLACE FUNCTION secti(a IN NUMBER, b IN NUMBER) RETURN NUMBER AS
BEGIN
  RETURN (a + b);
END; 
/

Stejně jako proceduru lze funkci volat dvěma různými způsoby:

Volání funkce v příkazu SELECT:
```
SELECT secti(2, 3) FROM dual;
```

Volání v těle jiného PL/SQL bloku

DECLARE
  a NUMBER;
  b NUMBER;
BEGIN
  a := 5;
  b := secti(a, 2);
  dbms_output.put_line('Vysledek : '||b);
END;
/

Následující příklad ukazuje dva různé způsoby přiřazení skutečných parametrů formálním parametrům při volání procedur a funkcí:

CREATE OR REPLACE FUNCTION spoj(a IN VARCHAR2, b IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
  RETURN (a||' a '||b);
END;
/

V prvním volání funkce SPOJ je přiřazení provedeno na základě pozice, v druhém volání na základě jména:

DECLARE
  tmp  VARCHAR2(100);
BEGIN
  tmp := spoj('Prvni', 'Druhy');
  dbms_output.put_line('Vysledek : '||tmp);

  tmp := spoj(b => 'Prvni', a => 'Druhy');
  dbms_output.put_line('Vysledek : '||tmp);
END;
/

Pro zrušení (smazání) uložené procedury nebo funkce použijeme odpovídající příkaz DROP PROCEDURE nebo DROP FUNCTION:

DROP PROCEDURE kopiruj_zamestnance;
DROP PROCEDURE nastav_plat;
DROP PROCEDURE inc;

DROP FUNCTION secti;
DROP FUNCTION spoj;

Uložené balíky procedur a funkcí

Uložené balíky procedur a funkcí slouží ke sdružení logicky spolu souvisejících procedur a funkcí. Mohou obsahovat i globální proměnné, jejichž platnost je omezena délkou aktuálního spojení s databází. Definice balíku představuje definici rozhraní balíku a těla balíku:

Rozhraní

V rozhraní lze deklarovat globální proměnné a hlavičky veřejných metod balíku:

CREATE OR REPLACE PACKAGE arithmetic AS
  usage NUMBER;

  FUNCTION  add(a IN NUMBER, b IN NUMBER) RETURN NUMBER;
  FUNCTION  sub(a IN NUMBER, b IN NUMBER) RETURN NUMBER;
  PROCEDURE inc(a IN OUT NUMBER);
END;
/

Tělo

Tělo balíku může obsahovat jak veřejné, tak privátní metody (nejsou z balíku exportovány). Pokud je v rozhraní balíku deklarována globální proměnná, musí tělo obsahovat inicializační část, která začíná klíčovým slovem BEGIN:

CREATE OR REPLACE PACKAGE BODY arithmetic AS

  FUNCTION  add(a IN NUMBER, b IN NUMBER) RETURN NUMBER IS
  BEGIN
    usage := usage + 1;
    RETURN (a + b);
  END;

  FUNCTION  sub(a IN NUMBER, b IN NUMBER) RETURN NUMBER IS
  BEGIN
    usage := usage + 1;
    RETURN (a - b);
  END;

  PROCEDURE inc(a IN OUT NUMBER) IS
  BEGIN
    usage := usage + 1;
    a := a + 1;
  END;
  
BEGIN
  usage := 0;  
END;
/

Příklad použití balíku:

DECLARE
  a NUMBER;
  b NUMBER;
BEGIN
  a := 6;
  b := arithmetic.add(a, 3);
  arithmetic.inc(b);
  dbms_output.put_line('Vysledek : '||b);
  dbms_output.put_line('Pouzito '||arithmetic.usage||' krat.');
END;
/

Zrušení balíku procedur a funkcí je v jazyce PL/SQL reprezentován dvojicí příkazů. DROP PACKAGE pro zrušení celého balíku (tj. rozhraní a tělo) a DROP PACKAGE BODY pro zrušení těla balíku. Rozhraní balíku může existovat bez těla.

DROP PACKAGE BODY arithmetic;

DROP PACKAGE arithmetic;

Řízení přístupu k uloženým procedurám, funkcím a balíkům

Stejně jako u tabulek a pohledů lze řídit přístup k uloženým procedurám, funkcím a balíkům pomocí příkazu GRANT, odebrat příkazem REVOKE. Přidělované/odebírané právo je jen jediné, a to EXECUTE:

GRANT EXECUTE ON secti TO PUBLIC;

REVOKE EXECUTE ON spoj FROM jarda;

GRANT EXECUTE ON arithmetic TO PUBLIC;

Poznámka: Nelze řídit přístup k jednotlivým procedurám a funkcím v balíku.

Pokud chceme, aby se procedura spouštěla s právy volajícího, použijeme klauzule AUTHID CURRENT_USER, jinak se spouští s právy toho, kdo proceduru vytvořil (implicitní klausule AUTHID DEFINER):

CREATE OR REPLACE PROCEDURE nove_heslo(pwd IN VARCHAR2) AUTHID CURRENT_USER IS
BEGIN
  EXECUTE IMMEDIATE 'ALTER USER '||user||' IDENTIFIED BY '||pwd;
END;
/

a takto přidělíme právo spuštění každému uživateli v databázi ...

GRANT EXECUTE ON nove_heslo TO PUBLIC;

... a takto si můžeme změnit svoje heslo:

EXEC ZIMA.nove_heslo ('heslo');

Kontrolní úloha

Vytvořte tabulku CV_HODNOTA, která bude mít sloupce:

id_hodnota (celé číslo, primární klíč)
hodnota (povinná číselná položka)

Vytvořte proceduru nova_hodnota s parametrem (vstupní číselný parametr), která do tabulky CV_HODNOTA zapíše do sloupce hodnota číslo zadané v parametru procedury a do sloupce id_hodnota vygeneruje pořadové číslo (řada 1, 2, ...).

Nápověda: Vložení prvního záznamu tabulky realizujte obsluhou uživatelem definované výjimečné situace.