PL/SQL

PL/SQL (Procedural Language/Structured Query Language) je procedurální nadstavba jazyka SQL firmy Oracle založená na programovacím jazyku 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í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:

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;

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:

part_no NUMBER(4);
in_stock BOOLEAN;

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 (:=):

in_stock := FALSE;

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 bonus), např.:

SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;

Třetí způsob přiřazuje hodnotu proměnné pomocí podprogramu:

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);

Deklarace konstant

Konstanty se deklarují stejně jako proměnné, ale navíc je použito klíčové slovo CONSTANT:

credit_limit CONSTANT REAL := 5000.00;

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:

DECLARE
CURSOR c1 IS
SELECT empno, ename, job FROM emp WHERE deptno = 20;

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

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;

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 (%).

Atribut %TYPE reprezentuje typ proměnné nebo sloupec v databázi. Kód

my_title books.title%TYPE;

zajišťuje, že proměnná my_title 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é my_title.

Atribut %ROWTYPE reprezentuje typ proměnné nebo řádek v databázi. Například:

DECLARE
dept_rec dept%ROWTYPE;

Ří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:

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;

Další možností je použití příkazu CASE:

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;

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.

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;

Sekvenční řízení toku

Jazyk obsahuje příkaz GOTO:

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;

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é.

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;

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ř.:

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;

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

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;

Záznamy

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));

Objektové typy

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
);

Zpracování chyb

PL/SQL podporuje výjimky, uživatelsky definovanou obsluhu chybových stavů.

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;