Úvod PL/SQL

Co to je PL/SQL?

PL/SQL je procedurální rozšíření jazyka SQL pocházející od firmy Oracle.
Jazyk SQL je neprocedurální, takže uživatel jeho pomocí definuje pouze to, jaká data požaduje, ale postup, jakým jsou data získána, je ponechán na databázovém serveru. Pro uživatele je tento způsob výhodný, protože mu umožňuje používat tento jazyk a přitom být odstíněn od složitosti zpracování dat. Pokud však požaduje při zpracování dat větší flexibilitu, musí použít jiný nástroj. Tím je v tomto případě jazyk PL/SQL.

Pro a proti PL/SQL

Výhody PL/SQL

• zvýšení výkonu aplikací psaných s pomocí PL/SQL - místo interpretace nepřeloženého kódu se používají překompilované programy
• omezení množství dat, která jsou přenášena mezi serverem a klientem po síti - manipulaci s daty je prováděna přímo na serveru, nedochází tedy k přenosu mezi serverem a klientem
• možnost vícenásobného použití jednou napsaného kódu ve více aplikacích - jednou napsané procedury funkce lze uchovávat v balících (packages) a volat z více aplikací

Nevýhody PL/SQL

• nutná orientace pouze na Oracle, nemožnost přenosu na jiný databázový server bez kompletního přepsání aplikační logiky
• dlouhotrvající opravy chyb v PL/SQL

Základy PL/SQL

Bloková struktura programu

PL/SQL je jazyk s blokovou strukturou. V jednoduchém programu je možné pro usnadnění programování a zvýšení čitelnosti zdrojového textu sdružovat související příkazy do bloků. Zde je příklad:

DECLARE
… množina příkazů …
BEGIN
… množina příkazů …
EXCEPTION
… množina příkazů …
END;

Tento program v jazyce PL/SQL obsahuje čtyři bloky. V bloku DECLARE se deklarují proměnné, konstanty a uživatelské datové typy, které se budou používat v těle programu. První klíčové slovo BEGIN označuje začátek těla hlavního programu. V bloku EXCEPTION jsou příkazy pro ošetření chybových stavů programu. Poslední END ukončuje tělo hlavního programu.

Komentáře

V jazyce PL/SQL existují dva způsoby zápisu komentářů:

1. do konce řádky '--' (dvě pomlčky) - ignoruje se vše od tohoto místa až do konce řádky; nedoporučuje se
2. víceřádkový, jako v C - ignoruje se vše mezi '/*' a '*/'

Deklarace konstant a proměnných

Každá deklarovaná konstanta nebo proměnná musí mít svůj datový typ (je možné použí kterýkoliv ze standardních datových typů). Deklarace konstanty obsahuje před určením datového typu klíčové slovo CONSTANT.
Při deklaraci proměnné lze určit její inicializační hodnotu a požadavek na to, že nesmí mít hodnotu NULL. Přiřazení hodnoty do proměnné se provádí pomocí operátoru ':=' .

DECLARE
-- deklarace promennych
cislo NUMBER(4);
dat_nar DATE;
pocet_zaznamu INTEGER := 0;
pocet_zaznamu2 INTEGER DEFAULT 0; /* DEFAULT lze použít místo := */
jmeno VARCHAR2 NOT NULL := 'PL/SQL';
-- deklarace konstant
max_pocet CONSTANT INTEGER := 100;

Datové typy

Zde je uveden pouze výčet používaných datových typů. Většina z nich je známá. Pro bližší seznámení s nimi je nutno nahlédnout do dokumentace.

• skalární datové typy - BINARY_INTEGER, DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NATURAL, NUMBER, NUMERIC, POSITIVE, REAL, SMALLINT, CHAR, CHARACTER, LONG, LONG RAW, RAW, ROWID, STRING, VARCHAR, VARCHAR2, DATE, BOOLEAN
• složené datové typy - RECORD, TABLE

Atributy proměnných

V deklaraci konstant a proměnných nemusíme uvádět přímo jejich datový typ, ale můžeme se odkazovat na datový typ sloupce nebo řádku tabulky. Velkou výhodou tohoto přístupu je, že se nemusí měnit deklarace procedur a funkcí vždy, když se změní definice tabulky.
Atributy jsou následující:

• %TYPE - atribut poskytne datový typ sloupce tabulky (na sloupec se lze odkazovat pomocí plné identifikace: jméno_vlastníka.tabulka.sloupec)
• %ROWTYPE - takto deklarovaná proměnná obsahuje záznam, který reprezentuje jeden řádek tabulky (nebo kurzoru - viz část o kurzrech); k jednotlivým částem záznamu se potom lze dostat pomocí zápisu: jméno_proměnné.jméno_sloupce

Příklad: (pokud má tabulka Zamestnanci dva sloupce: jmeno, datum_narozeni)

DECLARE
jmeno_zam zemestnanci.jmeno%TYPE;
dat_nar zamestnanci.datum_narozeni %TYPE;

zamest zamestnanci%ROWTYPE;
-- zamest.jmeno, zamest.datum_narozeni
-- řádek z tabulky zamestnanci se do proměnné zamest načte
-- příkazem FETCH - více viz dále

Řídící struktury

Jako každý procedurální programovací jazyk, obsahuje i PL/SQL má implementovány řídící struktury, které lze považovat v podstatě za "klasiku", takže jejich popis nebude příliš rozsáhlý.

• IF-THEN-ELSE-END IF - klasické větvení v programu

  Jsou tři možnosti zápisu:

  IF-THEN-END IF
  IF-THEN-ELSE-END IF
  IF-THEN-ELSIF-END IF

  IF acct_balance >= debit_amt THEN
    UPDATE accounts SET bal = bal - debit_amt
    WHERE account_id = acct;
  ELSE
    INSERT INTO temp
    VALUES (acct, acct_balance,'Insufficient funds');
  END IF;
  

• LOOP-END LOOP - provádí se cyklus, pokud není proveden příkaz pro přerušení cyklu. Cyklus lze přerušit následujícími příkazy:
  • EXIT - ukončí provádění cyklu
  • EXIT WHEN podminka -skončí, pokud je splněna podmínka
    

• FOR-IN [REVERSE]-LOOP-END LOOP - provádí pro všechny hodnoty z daného rozsahu; pokud je uvedeno REVERSE, provádí se v pořadí od největšího k nejmenšímu prvku.

  FOR i IN REVERSE 1..3 LOOP ­­ od hodnoty 3 k 2, 1
  -- příkazy zde se provedou 3krát
  END LOOP;

• WHILE-LOOP-END LOOP -opakuje cyklus, dokud je splněna podmínka uvedená za While.

WHILE prod_cena < 4000 LOOP
SELECT pcena INTO prod_cena
FROM zamestnanci WHERE cis_zam = czam;
END LOOP; 

• GOTO - předá řízení na návěští označené znaky << a >>

IF rating > 90 THEN
    GOTO calc_raise;
   END IF;
   
<<calc_raise>>
   IF job_title = 'SALESMAN' THEN
    raise := commission * 0.25;
   ELSE
    raise := salary * 0.10;
   END IF;

Kurzory

V procedurách a funkcích napsaných v PL/SQL můžeme použít kurzory - pojmenované výsledky SQL dotazu. Oracle si vytváří kurzor pro každý SQL příkaz - takový kurzor se nazývá implicitní. Pokud ho vytvoříme přímo, jedná se o explicitní kurzor. Vytvořit ho můžeme následujícím způsobem:

DECLARE
CURSOR jmeno_kurzoru IS
SELECT sl1, sl2 FROM table1 WHERE podminka
[FOR UPDATE OF sl1, sl2]

FOR UPDATE se uvádí v případě, že je potřeba data v kurzoru měnit.

S takto nadeklarovaným kurzorem můžeme provádět následující operace:

• OPEN jméno_kurzoru - provede se SQL dotaz z deklarace kurzocu a ukazatel pozice se nastaví před první záznam v kurzoru
• FETCH-INTO - načte aktivní řádek kurzoru a uloží ho do proměnné
• CLOSE - provede uzavření kurzoru, pokud je uvedena část FOR UPDATE, dojde k uložení změněných dat do tabulky

DECLARE
CURSOR kurzor IS
SELECT jmeno, prijmeni
FROM zamestnanci WHERE jmeno LIKE 'N%';

radek_kurzoru kurzor%ROWTYPE;
BEGIN
OPEN kurzor;
FETCH kurzor INTO radek_kurzoru;
CLOSE kurzor;
END;

Pokud potřebujeme projít všechny řádky kurzoru, můžeme použít řídící strukturu FOR, která otevře kurzor, projde všechny jeho řádky (a načte je do proměnné s atributem řádku kurzoru) a na konci ho zavře.

DECLARE
CURSOR kurzor IS
SELECT jmeno, prijmeni
FROM zamestnanci WHERE jmeno LIKE 'N%';

radek_kurzoru kurzor%ROWTYPE;
BEGIN
FOR radek_kurzoru IN kurzor LOOP
-- příkazy zde uvedené se provedou pro každý řádek
-- kurzoru
END LOOP;
END;

Pokud se pro řádek kurzoru používat atribut %ROWTYPE, musí mít všechny sloupce v kurzoru jednoduchá jména - pokud se v dotazu pro kurzor vyskytuje výraz, je nutné jeho jméno nahradit aliasem.

DECLARE
CURSOR kur IS
SELECT oddeleni, sum( hruba_mzda )*1.1 as poc_zam
FROM zamestnanci
GROUP BY oddeleni

kur_zaznam kur%ROWTYPE

Výjimky

Dojde-li v programu k chybě, řízení je předáno do části, kde se zpracovávají výjimky. Existují dva druhy výjimek: předdefinované a definované uživatelem. Mezi předdefinované patří např. výjimka vyvolaná při dělení nulou. Uživatelsky definované výjimky se deklarují jako EXCEPTION a vyvolávají se pomocí příkazu RAISE jmeno_vyjimky. Blok programu, kde se zpracovávají výjimky začíná slovem EXCEPTION a výjimka se odchytává pomocí výrazu WHEN jmeno_vyjimky THEN; pro zpracování všech zbývajících výjimek můžeme použít klauzuli WHEN OTHERS, kam se předá řízení programu pro výjimky, které nejsou ošetřeny předtím.

DECLARE
prijm zamestnanci.prijmeni%TYPE;
myException EXCEPTION;
BEGIN
 SELECT prijmeni INTO prijm
 FROM zamestnanci
 WHERE id_zam = 10;

 if prijmeni IS NULL then RAISE myException;
 ...
 ...
EXCEPTION
 WHEN myException THEN
  BEGIN
  ...
  END;
 WHEN OTHERS
  BEGIN
  -- zde skončí všechny ostatní výjimky
  END;
END;
END;

Procedury a funkce

Procedura PL/SQL se vytvoří příkazem CREATE PROCEDURE -

CREATE PROCEDURE proc (parametry) IS
-- deklarace proměnných
BEGIN
-- kód procedury
EXCEPTION
-- ošetření výjimek
END proc;

Parametry se uvádějí jako:
nazev_parametru [IN | OUT | IN OUT] datový_typ
IN - vstupní parametr - při volání má hodnotu
OUT - výstupní parametr - vrací hodnotu po skončení volání
IN OUT - vstupní i výstupní parametr

Funkce PL/SQL se vytváří příkazem CREATE FUNCTION:

CREATE FUNCTION fce (parametry)
RETURN typ_návr_hodnoty IS
-- deklarace
BEGIN
-- kód funkce
RETURN (navratova_hodnota);
END fce;

Parametry funkce mohou být pouze IN.

Packages

Procedury a funkce, které spolu souvisí, je možno pro další použití vkládat do tzv. packages (balíčků). Zde jsou obsaženy všechny typy, konstanty, proměnné, výjimky, kurzory, procedury a funkce, ke kterým je možno přistupovat z ostatních aplikací (pokud mají pro danému package patřičná přístupová práva).
Existují standardní package dodávané firmou Oracle - např. DBMS_OUTPUT, DBMS_LOCK apod. Kromě toho je možné vytvářet vlastní.
Tvorba package se skládá ze dvou kroků:

1. vytvoření interface package - zde se určí, které z objektů obsažených uvnitř package je možné využívat z ostatních aplikací - budou pro ně viditelné:

   CREATE PACKAGE jmeno_pack AS
   -- deklarace
   END jmeno_pack;

2. vytvoření těla (body) package - zde se provádí implementace procedur a funkci deklarovaných v interface:

CREATE PACKAGE BODY emp_actions AS
-- implementace deklarovaných částí
END emp_actions; 

Práva spojená s PL/SQL

Pokud chceme pracovat s PL/SQL, tzn. vytvářet a spouštět procedury, funkce a package, potřebujeme právo CREATE PROCEDURE nebo CREATE ANY PROCEDURE pro možnost vytvoření objektu a právo EXECUTE PROCEDURE nebo EXECUTE ANY PROCEDURE pro spuštění procedury (objektu).
Práva obsahující ANY slouží ke zpřístupnění všech objektů, zatímco právo bez části ANY je přiděleno jednomu konkrétnímu objektu.
Pokud má uživatel právo ke spuštění určité procedury, nemusí mít právo přístupu k tabulkám, které tato procedura používá.

Zdroje