Zobrazení zdroje stránky PL/SQL

'''PL/SQL''' (Procedural Language/Structured Query Language) je procedurální nadstavba jazyka [[SQL]] firmy [[Oracle Corporation|Oracle]] založená na programovacím jazyku [[Ada (programovací jazyk)|Ada]].

Tato nadstavba se rozšířila a její deriváty převzaly i jiné relační databáze. [[Sybase]] a [[Microsoft SQL Server]] mají [[Transact-SQL]], [[PostgreSQL]] má [[PL/pgSQL]] a [[IBM DB2]] má [[SQL PL]]. Existuje též projekt [[Fyracle]], jehož cílem je umožnit spouštění PL/SQL v relační databázi [[Firebird]].

== Úvod ==

PL/SQL přidává k jazyku SQL konstrukce [[Procedurální programování|procedurálního programování]]. Výsledkem je [[strukturální jazyk]], mocnější než samotné SQL. Základním stavebním kamenem v PL/SQL je blok. Program v PL/SQL se skládá z bloků, které mohou být vnořeny jeden do druhého. Obyčejně každý blok spouští jednu logickou akci v programu. Blok má následující strukturu:

<syntaxhighlight lang="plsql">
DECLARE
/* Deklarace obsahuje proměnné, typy a lokální subprogramy. */
BEGIN
/* Výkonná sekce: zde běží procedury a SQL kód. */
/* Toto je jediná sekce, která je v bloku povinná. */
EXCEPTION
/* Oblast zpracování výjimek: zde se zpracovávají chybové události. */
END;
</syntaxhighlight>

Pouze výkonná sekce je povinná, ostatní jsou doporučené. Jediné příkazy jazyka SQL, které jsou ve výkonné sekci povolené, jsou SELECT, INSERT, UPDATE, DELETE a několik dalších pro manipulaci s daty a pro kontrolu transakcí. Definiční příkazy jazyka SQL jako CREATE, DROP nebo ALTER nejsou povoleny. PL/SQL není citlivé na velikost písmen a mohou být použity komentáře ve stylu jazyka C.

== Prvky jazyka ==

=== Proměnné a konstanty ===

==== Deklarace proměnných ====
Proměnné můžeme rozdělit do několika skupin, dle různých kritérií, nejčastějším dělením je podle datového typu na číselné (NUMBER, PLS_INTEGER, SIMPLE_INTEGER..), stringové (CHAR, VARCHAR2..), datumové (DATE, TIMESTAMP..), typové proměnné (RECORD) a proměnné typu LOB (large object). Na rozdíl například od jazyka Java zde dochází k implicitní datové konverzi mezi jednotlivými typy (pokud to hodnota dovoluje). Dále lze rozdělit datové typy na nativní (PLS_INTEGER, DOUBLE_FLOAT..), které jsou počítány v hardware a na ty, které jsou interpretované (NUMBER) a jsou počítány pomocí vnitřní logiky databáze.

Například definujeme proměnnou part_no jako čtyřmístné číslo a in_stock jako logickou proměnnou:
<syntaxhighlight lang="plsql">
part_no NUMBER(4);
in_stock BOOLEAN;
</syntaxhighlight>

==== Přiřazování hodnot proměnným ====
Hodnoty můžeme přiřazovat třemi způsoby, prvním způsob je pomocí operátoru (<code>:=</code>):
<syntaxhighlight lang="plsql">
in_stock := FALSE;
</syntaxhighlight>

Druhý způsob je vybrání hodnoty z databáze a její přímé přiřazení do proměnné (v tomto případě je onou proměnnou <code>bonus</code>), např.:
<syntaxhighlight lang="plsql">
SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;
</syntaxhighlight>

Třetí způsob přiřazuje hodnotu proměnné pomocí podprogramu:
<syntaxhighlight lang="plsql">
DECLARE
my_sal REAL(7,2);
PROCEDURE adjust_salary (emp_id INT, salary IN OUT REAL) IS ..
BEGIN
SELECT AVG(sal) INTO my_sal FROM emp;
adjust_salary(7788, my_sal);
</syntaxhighlight>

==== Deklarace konstant ====
Konstanty se deklarují stejně jako proměnné, ale navíc je použito klíčové slovo CONSTANT:
<syntaxhighlight lang="plsql">
credit_limit CONSTANT REAL := 5000.00;
</syntaxhighlight>

=== Kurzory ===
Databázové systémy jako např. Oracle používají tzv. pracovní oblasti pro vykonávání SQL příkazů a pro ukládaní procesních informací. Pro přístup k těmto informacím se používá konstrukce nazývaná '''kurzor''', která umožňuje pojmenovat jednotlivé pracovní oblasti a přistupovat k nim. Existují dva druhy kurzorů: implicitní a explicitní. PL/SQL implicitně deklaruje kurzor pro všechny SQL příkazy, které manipulují s daty (i pro ty, které vrací jen jeden řádek). Explicitně můžeme např. deklarovat kurzor na jednotlivý řádek SQL příkazu, který vrací řádků více:
<syntaxhighlight lang="plsql">
DECLARE
CURSOR c1 IS
SELECT empno, ename, job FROM emp WHERE deptno = 20;
</syntaxhighlight>

==== Kurzorové proměnné ====
Stejně jako kurzory ukazují kurzorové proměnné na řádek víceřádkového výsledku dotazu, ale oproti kurzorům nejsou vázány na jeden konkrétní typ dotazu, ale je možno je přiřadit k jakémukoliv dotazu, který se liší pouze v typu. Kurzorová proměnná se chová jako klasická proměnná jazyka PL/SQL. Následující příklad otevře kurzorovou proměnnou generic_cv pro zvolený databázový dotaz
<syntaxhighlight lang="plsql">
PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,choice NUMBER) IS
BEGIN
IF choice = 1 THEN
  OPEN generic_cv FOR SELECT * FROM emp;
ELSIF choice = 2 THEN
  OPEN generic_cv FOR SELECT * FROM dept;
ELSIF choice = 3 THEN
  OPEN generic_cv FOR SELECT * FROM salgrade;
END IF;
...
END;
</syntaxhighlight>

=== Atributy ===
Proměnné a kurzory jazyka PL/SQL mají atributy, které určují typ struktury na kterou ukazují bez opakování její definice. Indikátorem atributu je značka procenta (<code>%</code>).

Atribut <code>%TYPE</code> reprezentuje typ proměnné nebo sloupec v databázi. Kód
<syntaxhighlight lang="plsql">
my_title books.title%TYPE;
</syntaxhighlight>
zajišťuje, že proměnná <code>my_title</code> bude vždy stejného typu jako sloupec ''title'' v tabulce ''books''. Pokud se změní typ sloupce v tabulce změní se za běhu i typ proměnné <code>my_title</code>.

Atribut <code>%ROWTYPE</code> reprezentuje typ proměnné nebo řádek v databázi. Například:
<syntaxhighlight lang="plsql">
DECLARE
dept_rec dept%ROWTYPE;
</syntaxhighlight>

=== Řízení toku programu ===

==== Podmíněné řízení toku ====
Občas je nezbytné vybírat mezi více akcemi podle okamžitých okolností, pro tyto účely slouží konstrukce IF-THEN-ELSE:
<syntaxhighlight lang="plsql">
DECLARE
acct_balance NUMBER(11,2);
acct CONSTANT NUMBER(4) := 3;
debit_amt CONSTANT NUMBER(5,2) := 500.00;
BEGIN
SELECT bal INTO acct_balance FROM accounts
  WHERE account_id = acct
  FOR UPDATE OF bal;
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');
       -- vkládáme účet, stav účtu a zprávu
END IF;
COMMIT;
END;
</syntaxhighlight>

Další možností je použití příkazu CASE:
<syntaxhighlight lang="plsql">
CASE
WHEN shape = 'square' THEN area := side * side;
WHEN shape = 'circle' THEN
  BEGIN
    area := pi * (radius * radius);
    DBMS_OUTPUT.PUT_LINE('Value is not exact because pi is irrational.');
  END;
WHEN shape = 'rectangle' THEN area := length * width;
ELSE
  BEGIN
    DBMS_OUTPUT.PUT_LINE('No formula to calculate area of a' || shape);
    RAISE PROGRAM_ERROR;
  END;
END CASE;
</syntaxhighlight>

==== Iterativní řízení toku ====
Další možností řízení toku je řízení za pomocí cyklů. Lze použít tyto konstrukce: LOOP, FOR-LOOP, WHILE-LOOP a EXIT-WHEN.

<syntaxhighlight lang="plsql">
LOOP
/* kód */
END LOOP;


FOR num IN 1..500 LOOP
INSERT INTO roots VALUES (num, SQRT(num));
END LOOP;


DECLARE
salary emp.sal%TYPE := 0;
mgr_num emp.mgr%TYPE;
last_name emp.ename%TYPE;
starting_empno emp.empno%TYPE := 7499;
BEGIN
SELECT mgr INTO mgr_num FROM emp
  WHERE empno = starting_empno;
WHILE salary <= 2500 LOOP
  SELECT sal, mgr, ename INTO salary, mgr_num, last_name
    FROM emp WHERE empno = mgr_num;
END LOOP;
INSERT INTO temp VALUES (NULL, salary, last_name);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  INSERT INTO temp VALUES (NULL, NULL, 'Not found');
  COMMIT;
END;


LOOP
...
total := total + salary;
EXIT WHEN total > 25000; -- je-li splněna podmínka, vyskočíme z cyklu
END LOOP;
</syntaxhighlight>

==== Sekvenční řízení toku ====
Jazyk obsahuje příkaz GOTO:
<syntaxhighlight lang="plsql">
IF rating > 90 THEN
GOTO calc_raise; -- skok na návěští
END IF;
...
<<calc_raise>>
IF job_title = 'SALESMAN' THEN -- provádění programu pokračuje zde
amount := commission * 0.25;
ELSE
amount := salary * 0.10;
END IF;
</syntaxhighlight>

=== Modularita ===
Modularita umožňuje rozdělit komplexní problém na sérii menších problémů, dobře definovaných modulů. Těmito moduly mohou být bloky, subprogramy nebo balíčky.

==== Subprogramy ====
Subprogramy můžeme rozdělit na anonymní bloky, funkce a procedury. Funkce je podprogram, který musí obsahovat v deklaraci klauzuli RETURN a musí vracet hodnotu. Funkce může i nemusí obsahovat vstupní parametry. Procedura je podprogram, který v deklaraci neobsahuje klauzuli RETURN (vyskočit z procedury pomocí slova RETURN bez návratu hodnoty je však možné). Procedura může obsahovat vstupní (IN) i výstupní (OUT) parametry. Anonymní kód na rozdíl od funkce a procedury může vrátit hodnotu pouze pomocí vázané proměnné.
<syntaxhighlight lang="plsql">
PROCEDURE award_bonus (emp_id NUMBER) IS
bonus REAL;
comm_missing EXCEPTION;
BEGIN -- jádro úlohy
SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;
IF bonus IS NULL THEN
  RAISE comm_missing;
ELSE
  UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
END IF;
EXCEPTION -- obsluha výjimek
WHEN comm_missing THEN
  ...
END award_bonus;
</syntaxhighlight>

==== Balíčky ====
Balíčky umožňují slučovat proměnné, kurzory a podprogramy. Balíčky mají obvykle dvě části a to část specifikace a tělo balíčku. Př.:

<syntaxhighlight lang="plsql">
CREATE PACKAGE emp_actions AS -- specifikace balíčku
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;
CREATE PACKAGE BODY emp_actions AS -- tělo balíčku
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS
BEGIN
  INSERT INTO emp VALUES (empno, ename, ...);
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
  DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;
</syntaxhighlight>

=== Abstrakce dat ===
Pomocí abstrakce dat můžeme extrahovat klíčové proměnné a ignorovat v danou chvíli nepotřebná data. Pro abstrakci dat slouží tří konstrukce: kolekce, záznamy a objektové typy.

==== Kolekce ====

<syntaxhighlight lang="plsql">
DECLARE
TYPE Staff IS TABLE OF Employee;
staffer Employee;
FUNCTION new_hires (hiredate DATE) RETURN Staff IS
BEGIN ... END;
BEGIN
staffer := new_hires('10-NOV-98')(5);
...
END;
</syntaxhighlight>

==== Záznamy ====
<syntaxhighlight lang="plsql">
DECLARE
TYPE TimeRec IS RECORD (hours SMALLINT, minutes SMALLINT);
TYPE MeetingTyp IS RECORD (
  date_held DATE,
  duration  TimeRec,  -- záznam uvnitř jiného záznamu
  location  VARCHAR2(20),
  purpose   VARCHAR2(50));
</syntaxhighlight>

==== Objektové typy ====
<syntaxhighlight lang="plsql">
CREATE TYPE Bank_Account AS OBJECT (
  acct_number INTEGER(5),
  balance     REAL,
  status      VARCHAR2(10),
  MEMBER PROCEDURE open (amount IN REAL),
  MEMBER PROCEDURE verify_acct (num IN INTEGER),
  MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL),
  MEMBER PROCEDURE deposit (num IN INTEGER, amount IN REAL),
  MEMBER PROCEDURE withdraw (num IN INTEGER, amount IN REAL),
  MEMBER FUNCTION curr_bal (num IN INTEGER) RETURN REAL
);
</syntaxhighlight>

=== Zpracování chyb ===
PL/SQL podporuje výjimky, uživatelsky definovanou obsluhu chybových stavů.

<syntaxhighlight lang="plsql">
DECLARE
...
comm_missing EXCEPTION; -- deklarace výjimky
BEGIN
...
IF commission IS NULL THEN
  RAISE comm_missing;  -- vyvolání výjimky
END IF;
bonus := (salary * 0.10) + (commission * 0.15);
EXCEPTION
WHEN comm_missing THEN ... -- zpracování výjimky
WHEN OTHERS THEN ... -- zpracování všech výše neodchycených výjimek
END;
</syntaxhighlight>

== Externí odkazy ==
* http://infolab.stanford.edu/~ullman/fcdb/oracle/or-plsql.html
* http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/01_oview.htm

{{Programovací jazyky}}
{{SQL}}
{{Aplikační frameworky}}
{{Autoritní data}}
{{Portály|Internet}}

[[Kategorie:SQL]]