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 způsoby, prvním způsob je pomocí operátoru (:=), např.:
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í 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
Konstanty se deklarují stejně jako proměnné a 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. „work areas“ 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ž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í s 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é
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
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
Podmíněné řízení toku
Občas je nezbytné vybírat mezi více akcemi a to 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');
-- 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
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; -- exit loop if condition is true
END LOOP;
Sekvenční řízení toku
Jazyk obsahuje 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 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
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
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, -- nested record
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; -- 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