7. CVIČENÍ

PL/SQL

Než začnete pracovat s následujícími příklady, vytvořte si tabulku OSOBY a naplňte ji daty : 


CREATE TABLE osoby (
  os_cislo  NUMBER(5),
  prijmeni  VARCHAR2(30),
  jmeno     VARCHAR2(30)
);

INSERT INTO osoby VALUES (1, 'Otta', 'Max');
INSERT INTO osoby VALUES (2, 'Pesicka', 'Lada');
INSERT INTO osoby VALUES (3, 'Rohlik', 'Ondra');

COMMIT;

SET SERVEROUTPUT ON

Kurzory

Kurzor je abstraktní datový typ umožňující procházet záznamy vybrané dotazem, který je s kurzorem spojen. Nad kurzorem jsou definovány následující operace :

Kromě těchto operací obsahuje kurzor navíc následující stavové proměnné :

Deklaraci a typické použití kurzoru ukazuje následující příklad : 

DECLARE
  tmp  osoby%ROWTYPE;
  CURSOR  plist IS
    SELECT * FROM osoby;

BEGIN
  OPEN plist;

  LOOP
    FETCH plist INTO tmp;
    EXIT WHEN plist%NOTFOUND;
    dbms_output.put_line(plist%ROWCOUNT||'. '||tmp.jmeno||' '||tmp.prijmeni);
  END LOOP;

  CLOSE plist;
END;
/

Kurzor může mít i parametry : 

DECLARE
  num INTEGER;
  CURSOR osoba (name IN VARCHAR2) IS
    SELECT  os_cislo
      FROM  osoby
     WHERE  jmeno = name;

BEGIN
  OPEN osoba('Max');

  FETCH osoba INTO num;
  IF osoba%FOUND THEN
    dbms_output.put_line('Osobni cislo : '||num);
  END IF;

  CLOSE osoba;
END;
/

S kurzory lze pracovat i jednodušším způsobem tak, že je spojíme s příkazem FOR - LOOP. V cyklu jsou postupně vybrány všechny záznamy kurzoru. Všimněte si, že proměnná cyklu není deklarována a její struktura odpovídá struktuře řádky vybrané kurzorem : 

DECLARE
  CURSOR  plist(num IN INTEGER) IS
    SELECT * FROM osoby
     WHERE os_cislo > num;

BEGIN
  FOR p IN plist(1) LOOP
    dbms_output.put_line(p.jmeno||' '||p.prijmeni);
  END LOOP;
END;
/

Možné je i takovéto použití : 

BEGIN
  FOR p IN (SELECT * FROM osoby) LOOP
    dbms_output.put_line(p.jmeno||' '||p.prijmeni);
  END LOOP;
END;
/

Deklaraci kurzoru lze provést i následujícím způsobem, pokud jej chcete předat jako parametr procedury nebo funkce. 

DECLARE
  tmp   osoby%ROWTYPE;
  TYPE  t_crsr IS REF CURSOR RETURN osoby%ROWTYPE;
  plist t_crsr;

BEGIN
  OPEN plist FOR SELECT * FROM osoby;

  LOOP
    FETCH plist INTO tmp;
    EXIT WHEN plist%NOTFOUND;
    dbms_output.put_line(plist%ROWCOUNT||'. '||tmp.jmeno||' '||tmp.prijmeni);
  END LOOP;

  CLOSE plist;
END;
/

Předchozí deklarace specifikovala i řádkový typ, se kterým bude kurzor pracovat. Lze ovšem nadeklarovat i tzv. slabě typovaný kurzor : 

DECLARE
  tmp   osoby%ROWTYPE;
  TYPE  t_crsr IS REF CURSOR;
  plist t_crsr;

BEGIN
  OPEN plist FOR SELECT * FROM osoby;

  LOOP
    FETCH plist INTO tmp;
    EXIT WHEN plist%NOTFOUND;
    dbms_output.put_line(plist%ROWCOUNT||'. '||tmp.jmeno||' '||tmp.prijmeni);
  END LOOP;

  CLOSE plist;
END;
/

Uložené procedury

Způsob vytvoření uložené procedury ukazuje následující příklad : 

CREATE OR REPLACE PROCEDURE vypis_osoby AS
  tmp   osoby%ROWTYPE;
  TYPE  t_crsr IS REF CURSOR;
  plist t_crsr;

BEGIN
  OPEN plist FOR SELECT * FROM osoby;

  LOOP
    FETCH plist INTO tmp;
    EXIT WHEN plist%NOTFOUND;
    dbms_output.put_line(plist%ROWCOUNT||'. '||tmp.jmeno||' '||tmp.prijmeni);
  END LOOP;

  CLOSE plist;
END;
/

Proceduru můžete zavolat různými způsoby : 

SET SERVEROUTPUT ON

EXEC vypis_osoby;

BEGIN
  vypis_osoby;
END;
/

Procedury můžou mít parametry, které mohou být : 

CREATE OR REPLACE PROCEDURE najdi_osoby(id IN INTEGER) AS
BEGIN
  FOR p IN (SELECT * FROM osoby WHERE os_cislo > id) LOOP
    dbms_output.put_line(p.jmeno||' '||p.prijmeni);
  END LOOP;
END;
/

Volání procedury s parametrem : 

EXEC najdi_osoby(1);

Procedura s vstupně-výstupním parametrem : 

CREATE OR REPLACE PROCEDURE inc(a IN OUT INTEGER) AS
BEGIN
  a := a + 1;
END;
/

Volání procedury s vstupně-výstupním parametrem : 

DECLARE
  a INTEGER;
BEGIN
  a := 9;
  inc(a);
  dbms_output.put_line('Vysledek : '||a);
END;
/

Uložené funkce

Vytvoření uložené funkce : 

CREATE OR REPLACE FUNCTION secti(a IN INTEGER, b IN INTEGER) RETURN INTEGER AS
BEGIN
  RETURN (a + b);
END;
/

Volání funkce v DML příkazu : 

SELECT secti(2, 3) FROM dual;

Volání fuknce v PL/SQL bloku : 

DECLARE
  a INTEGER;
  b INTEGER;
BEGIN
  a := 5;
  b := secti(a, 2);
  dbms_output.put_line('Vysledek : '||b);
END;
/

Následující příklad ukazuje dva různé způsoby přiřazení skutečných parametrů formálním parametrům při volání procedurá a funkcí : 

CREATE OR REPLACE FUNCTION spoj(a IN VARCHAR2, b IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
  RETURN (a||' a '||b);
END;
/

V prvním volání funkce SPOJ je přiřazení provedeno na základě pozice, v druhém volání na základě jména : 

DECLARE
  tmp  VARCHAR2(100);
BEGIN
  tmp := spoj('Prvni', 'Druhy');
  dbms_output.put_line('Vysledek : '||tmp);

  tmp := spoj(b => 'Prvni', a => 'Druhy');
  dbms_output.put_line('Vysledek : '||tmp);
END;
/

Uložené balíky procedur a funkcí

Uložené balíky procedur a funkcí slouží ke sdružení logicky spolu souvisejících procedur a funkcí. Mohou obsahovat i globální proměnné, jejichž platnost je omezena délkou aktuálního spojení s databází. Definice balíku představuje definici rozhraní balíku a těla balíku :

Rozhraní 
CREATE OR REPLACE PACKAGE arithmetic AS
  usage INTEGER := 0;

  FUNCTION  add(a IN INTEGER, b IN INTEGER) RETURN INTEGER;
  FUNCTION  sub(a IN INTEGER, b IN INTEGER) RETURN INTEGER;
  PROCEDURE inc(a IN OUT INTEGER);
END;
/
Tělo 
CREATE OR REPLACE PACKAGE BODY arithmetic AS

  FUNCTION  add(a IN INTEGER, b IN INTEGER) RETURN INTEGER IS
  BEGIN
    usage := usage + 1;
    RETURN (a + b);
  END;

  FUNCTION  sub(a IN INTEGER, b IN INTEGER) RETURN INTEGER IS
  BEGIN
    usage := usage + 1;
    RETURN (a - b);
  END;

  PROCEDURE inc(a IN OUT INTEGER) AS
  BEGIN
    usage := usage + 1;
    a := a + 1;
  END;
END;
/

Příklad použití balíku : 

DECLARE
  a INTEGER;
  b INTEGER;
BEGIN
  a := 6;
  b := arithmetic.add(a, 3);
  arithmetic.inc(b);
  dbms_output.put_line('Vysledek : '||b);
  dbms_output.put_line('Pouzito '||arithmetic.usage||' krat.');
END;
/

Dynamické SQL

Pokud potřebujete dynamicky za běhu měnit DML nebo DDL příkazy (např. až za běhu se rozhodne se kterou tabulkou bude příkaz pracovat), použijete tzv. dynamické SQL. Příkaz lze sestavit jako řetězec znaků a ten předat ke zpracování. Následující příklad ukazuje použití dynamického SQL v kurzoru : 

DECLARE
  stmt  VARCHAR2(100);
  core  VARCHAR2(80);
  cond1 VARCHAR2(20);
  cond2 VARCHAR2(20);
  tmp   osoby%ROWTYPE;
  TYPE t_cur IS REF CURSOR;
  list  t_cur;

BEGIN
  dbms_output.new_line;

  core := 'SELECT * FROM osoby WHERE ';
  cond1 := 'os_cislo > 1';
  cond2 := 'prijmeni = '||chr(39)||'Pesicka'||chr(39);

  stmt := core||cond1;
  dbms_output.put_line('------------------------------------------');
  dbms_output.put_line(stmt);

  OPEN list FOR stmt;
  LOOP
    FETCH list INTO tmp;
    EXIT WHEN list%NOTFOUND;
    dbms_output.put_line(list%ROWCOUNT||'. '||tmp.jmeno||' '||tmp.prijmeni);
  END LOOP;
  CLOSE list;

  stmt := core||cond2;
  dbms_output.put_line('------------------------------------------');
  dbms_output.put_line(stmt);

  OPEN list FOR stmt;
  LOOP
    FETCH list INTO tmp;
    EXIT WHEN list%NOTFOUND;
    dbms_output.put_line(list%ROWCOUNT||'. '||tmp.jmeno||' '||tmp.prijmeni);
  END LOOP;
  CLOSE list;
END;
/

V PL/SQL nelze přímo volat DDL příkazy. Lze je však volat dynamicky : 

CREATE OR REPLACE PROCEDURE zrus_tabulku(jmeno IN VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE '||jmeno;
END;
/

Řízení přístupu k uloženým procedurám a funkcím

Stejně jako u tabulek a pohledů lze řídit přístup k uloženým procedurám, funkcím a balíkům pomocí příkazu GRANT. Nelze řídit přístup k jednotlivým procedurám a funkcím v balíku : 

GRANT EXECUTE ON secti TO PUBLIC;

REVOKE EXECUTE ON spoj FROM jarda;

GRANT EXECUTE ON arithmetic TO PUBLIC;

Pokud chceme, aby se procedura spouštěla s právy volajícího, použijeme klauzule AUTHID CURRENT_USER : 

CREATE OR REPLACE PROCEDURE nove_heslo(pwd IN VARCHAR2) AUTHID CURRENT_USER IS
BEGIN
  EXECUTE IMMEDIATE 'ALTER USER '||user||' IDENTIFIED BY '||pwd;
END;
/