PL/SQL v příkladech / Záchytné body

Záchytné body jsou mechanismem uvnitř PL/SQL bloku, který umožňuje rozdělit prováděnou transakci na dílčí celky, neboli milníky. K těmto milníkům se poté lze jednoduše vrátit, aniž bychom přišli o celou transakci.

Nejdříve si zadefinujme pomocnou metodu, která vrátí plat konkrétního zaměstnance.

CREATE OR REPLACE FUNCTION sf_VratPlat(
    p_Zamestnanec_ID NUMBER
) RETURN NUMBER
IS
  l_Plat NUMBER;
BEGIN
  SELECT salary
    INTO l_Plat
    FROM employees
    WHERE employee_id=p_Zamestnanec_ID
  ;
  RETURN l_Plat;
END;
/

A ještě jednu, která plat aktualizuje.

CREATE OR REPLACE PROCEDURE sp_NastavPlat(
    p_Zamestnanec_ID NUMBER
  , p_Plat NUMBER
)
IS
BEGIN
  UPDATE employees
    SET salary=p_Plat
    WHERE (employee_id=p_Zamestnanec_ID)
  ;
END;
/

V prvním kroku nastavíme počáteční plat na 24000, čímž započne transakce. O tuto hodnotu nechceme přijít, ať už se dále v kódu děje cokoliv. Vytvoříme tedy bod obnovy a pojmenujeme ho vnejsi_savepoint

Druhý krok opět nastaví plat, tentokrát na 25500.

Pro ilustraci následuje anonymní blok, který si před prováděním změn vytvoří „svůj vlastní“ milník s názvem vnitrni_savepoint, nastaví plat na 26000 a vyvolá výjimku. Ta je odchycena a zároveň se provede částečné vrácení změň do stavu před začátkem anonymního bloku.

V poslední fázi se opět vyvolá výjimka a vrácení k záchytnému bodu, což anuluje i aktualizaci platu z druhého kroku.

DECLARE
  L_ZAMESTNANEC_ID CONSTANT NUMBER:=100; -- testovací zaměstnanec
  VYJIMKA EXCEPTION;
BEGIN
  sp_NastavPlat(L_ZAMESTNANEC_ID, 24000);
  dbms_output.put_line(sf_VratPlat(L_ZAMESTNANEC_ID)); -- 24000
    
  SAVEPOINT vnejsi_savepoint;
  sp_NastavPlat(L_ZAMESTNANEC_ID, 25500);
  dbms_output.put_line(sf_VratPlat(L_ZAMESTNANEC_ID)); -- 25500

  /* ZAČÁTEK VNITŘNÍHO BLOKU */
  BEGIN
    SAVEPOINT vnitrni_savepoint;
    sp_NastavPlat(L_ZAMESTNANEC_ID, 26000);
    dbms_output.put_line(sf_VratPlat(L_ZAMESTNANEC_ID)); -- 26000
    RAISE VYJIMKA;
  EXCEPTION
    WHEN VYJIMKA THEN
      ROLLBACK TO SAVEPOINT vnitrni_savepoint;
  END;
  /* KONEC VNITŘNÍHO BLOKU */

  dbms_output.put_line(sf_VratPlat(L_ZAMESTNANEC_ID)); -- 25500
  RAISE VYJIMKA;
EXCEPTION
  WHEN VYJIMKA THEN
    ROLLBACK TO SAVEPOINT vnejsi_savepoint;
    dbms_output.put_line(sf_VratPlat(L_ZAMESTNANEC_ID)); -- 24000
END;