PL/SQL

PL/SQL (Procedural Language/Structured Query Language) je procedurální nadstavba jazyka SQL od 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ěnnou může být jakýkoliv typ jazyka SQL jako třeba CHAR, DATE nebo NUMBER, nebo také jakýkoliv typ jazyka PL/SQL jako BOOLEAN nebo BINARY_INTEGER. 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 zbůsoby, prvním zbůsob je pomocí operátoru (:=) např.:

in_stock := FALSE;

Druhý zbů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í zbůsob přiřazuje hodnotu proměnné pomocí subprogramu

  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); -- assigns a new value to my_sal

Deklarace konstant

Deklarování konstant je jako deklarování proměnných kromě toho, že musím použít klíčové slovo CONSTANT:

credit_limit CONSTANT REAL := 5000.00

Kurzory (Cursors)

Databázové systémy jako např. Oracle používají tkz. „work areas“ pro vykonávaní SQL příkazů a pro ukládaní procesních informací. Pro přístup k těmto informacím se používá konstrukce zvaná kurzor(cursor) která umožnuje pojmenovat jednotlivé „work areas“ 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í z daty, i pro ty které vrací jen jeden řádek. Explicitně můžeme deklarovat kurzor např. na jednotlivý řádek SQL příkazu který vrací řádků více např.:

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

Kurzorové proměnné (Cursor Variables)

Stejně jako kurzory, ukazují kurzorové proměnné na řádek víceřádkového výsledku dotazu, ale oproti kurzorům nejsou vázany na jeden konkrétní typ dotazu ale je možno je příř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 (Attributes)

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 sloupeček 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. Kód : DECLARE

  dept_rec dept%ROWTYPE;

Řízení toku programu(Control Structures)

Podmíněné řízení toku(Conditional Control)

občas je nezbytné vybírat mezi více akcemi a to podle okamžitých okolností, pro tyto účely slouží konstrekce 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');
           -- insert account, current balance, and message
  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(Iterative Control)

další možností řízení toku je řízení za pomocí cyklů, můžeme 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;  -- exit loop if condition is true
  END LOOP;

Sekvenční řízení toku(Sequential Control)

příkaz GOTO

  IF rating > 90 THEN
  GOTO calc_raise;  -- branch to label
  END IF;
  ...
  <<calc_raise>>
  IF job_title = 'SALESMAN' THEN  -- control resumes here
  amount := commission * 0.25;
  ELSE
  amount := salary * 0.10;
  END IF;

Modularita

modularita nám 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

PL/SQL má dva typy subprogramů a to procedury a funkce. Procedury i funkce mohou přebírat parametry a mohou být volány. Př.:

  PROCEDURE award_bonus (emp_id NUMBER) IS
  bonus        REAL;
  comm_missing EXCEPTION;
  BEGIN  -- executable part starts here
  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  -- exception-handling part starts here
  WHEN comm_missing THEN
     ...
  END award_bonus;

Balíčky(Packages)

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

  CREATE PACKAGE emp_actions AS  -- package specification
  PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...);
  PROCEDURE fire_employee (emp_id NUMBER);
  END emp_actions;
  CREATE PACKAGE BODY emp_actions AS  -- package body
  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(Collections)

  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(Records)

  DECLARE
  TYPE TimeRec IS RECORD (hours SMALLINT, minutes SMALLINT);
  TYPE MeetingTyp IS RECORD (
     date_held DATE,
     duration  TimeRec,  -- nested record
     location  VARCHAR2(20),
     purpose   VARCHAR2(50));

Objektové typy(Object Types)

  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(Error Handling)

PL/SQL obsahuje uživatelsky definovanou konstrukci chybových stavů tzv. vyjímky(exceptions)

  DECLARE
  ...
  comm_missing EXCEPTION;  -- declare exception
  BEGIN
  ...
  IF commission IS NULL THEN
     RAISE comm_missing;  -- raise exception
  END IF;
  bonus := (salary * 0.10) + (commission * 0.15);
  EXCEPTION
  WHEN comm_missing THEN ... -- process the exception

3 Reference

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