PL/SQL v příkladech / Procedury a funkce

Mezi pojmenované bloky zařazujeme procedury a funkce. Ty lze volat opakovaně. Deklarativní sekci uvnitř obou druhů metod značí klíčové slovo IS, které je povinné, další členění na výkonnou část a část pro zpracování výjimek je totožné s bloky anonymními.

Procedura

DECLARE
  /* */ -- prázdný komentář jen pro optické oddělení interních metod
  PROCEDURE mp_VypisAktualniRok
  IS
    l_Rok CHAR(4):=TO_CHAR(SYSDATE, 'YYYY'); -- lokální proměnná
  BEGIN
    dbms_output.put_line(l_Rok); -- 2016
  END;
  /* */
BEGIN
  -- volání bezparametrické procedury
  mp_VypisAktualniRok;
  -- znovupoužití procedury
  mp_VypisAktualniRok;
END;

Funkce

Hlavní rozdíl mezi procedurou a funkcí je v návratové hodnotě. Procedura pouze vykoná danou logiku (dá se přirovnat k void metodám v jiných jazycích), funkce navíc vrací hodnotu předem definovaného typu.

DECLARE
  l_AktualniRok CHAR(4);
  /* */
  FUNCTION mf_VratAktualniRok
    RETURN CHAR -- návratová hodnota
  IS
  BEGIN
    RETURN TO_CHAR(SYSDATE, 'YYYY');
  END;
  /* */
BEGIN
  -- přiřazení výsledku bezparametrické funkce do proměnné
  l_AktualniRok:=mf_VratAktualniRok;
  dbms_output.put_line(l_AktualniRok); -- 2016
END;

Parametry

Vstupní

Jak procedury, tak i funkce lze zobecnit přidáním vstupních parametrů. Můžou tak provádět stále stejný kód, ale pokaždé nad jinými daty.

DECLARE
  l_Vysledek NUMBER;
  /* */
  FUNCTION mf_Vynasob(
      p_A NUMBER -- vstupní parametry
    , p_B NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    RETURN (p_A*p_B);
  END;
  /* */
BEGIN
  -- předání parametrů do volání funkce
  -- a uložení výsledku do proměnné
  l_Vysledek:=mf_Vynasob(
      p_A => 10
    , p_B => 2
  );
  dbms_output.put_line(l_Vysledek); -- 20
END;

Při volání metod není nutné parametry explicitně vyjmenovávat. Lze se spolehnout na pořadí v definici, pokud se ale tato změní, program s největší pravděpodobností přestane fungovat. Vstupnímu parametru je také možné nadefinovat výchozí hodnotu přes operátor přiřazení.

DECLARE
  l_Vysledek NUMBER;
  /* */
  FUNCTION mf_Umocni(
      p_Zaklad NUMBER
    , p_Exponent NUMBER:=2 -- výchozí hodnota
  ) RETURN NUMBER
  IS
  BEGIN
    RETURN (POWER(p_Zaklad,p_Exponent)); -- Oracle funkce umocnění
  END;
  /* */
BEGIN
  -- sekvenční předání parametrů
  l_Vysledek:=mf_Umocni(8, 3);
  dbms_output.put_line(l_Vysledek); -- 512
  -- návratová hodnota fce jako vstupní parametr fce jiné
  -- a vynechání druhého parametru (použití výchozí hodnoty)
  dbms_output.put_line(mf_Umocni(8)); -- 64
END;

Výstupní

Procedurální jazyk PL/SQL dovoluje definovat takzvané výstupní parametry, které rozdíl mezi procedurou a funkcí do značné míry stírají. Používají se všude tam, kde nestačí jedna návratová hodnota.

DECLARE
  l_Den CHAR(2);
  l_Mesic CHAR(2);
  l_Rok CHAR(4);
  /* */
  PROCEDURE mp_RozdelDatum(
      p_Datum DATE
    , o_Den OUT CHAR
    , o_Mesic OUT CHAR
    , o_Rok OUT CHAR
  )
  IS
  BEGIN
    -- do OUT parametru je povolené přiřazovat
    o_Den:=TO_CHAR(p_Datum, 'DD');
    o_Mesic:=TO_CHAR(p_Datum, 'MM');
    o_Rok:=TO_CHAR(p_Datum, 'YYYY');
  END;
  /* */
BEGIN
  mp_RozdelDatum(
      p_Datum => SYSDATE -- 5.4.2016
    , o_Den => l_Den
    , o_Mesic => l_Mesic
    , o_Rok => l_Rok
  );
  dbms_output.put_line(l_Den||'/'||l_Rok||'/'||l_Mesic); -- 05/2016/04
END;

Čistě z důvodu přehlednosti doporučuji používat funkci, pokud požadujeme vrácení jedné hodnoty, při dvou a více potom proceduru s OUT parametry.

DECLARE
  l_Den CHAR(2);
  l_Mesic CHAR(2);
  l_Rok CHAR(4);
  /* */
  FUNCTION mf_RozdelDatum(
      p_Datum DATE
    , o_Den OUT CHAR
    , o_Mesic OUT CHAR
  ) RETURN CHAR
  IS
  BEGIN
    o_Den:=TO_CHAR(p_Datum, 'DD');
    o_Mesic:=TO_CHAR(p_Datum, 'MM');
    RETURN TO_CHAR(p_Datum, 'YYYY'); -- rok jako návratová hodnota
  END;
  /* */
BEGIN
  -- možné, nicméně nepřehledné a nelogické
  l_Rok:=mf_RozdelDatum(
      p_Datum => SYSDATE -- 5.4.2016
    , o_Den => l_Den
    , o_Mesic => l_Mesic
  );
  dbms_output.put_line(l_Den||'/'||l_Rok||'/'||l_Mesic); -- 05/2016/04
END;

Vstupně/výstupní

Kombinací předchozích dvou typů získáme IN OUT parametr. Proměnná, která se takovému parametru předává, interně zkopíruje svoji hodnotu do metody a po dokončení zpracování výslednou hodnotu nakopíruje zpátky ven do proměnné. Vynucení předání pouze odkazu do paměti (tedy zamezení „kopírování“ hodnoty proměnné) zprostředkovává klauzule NOCOPY před definicí datového typu.

DECLARE
  l_Datum DATE:=TO_DATE('01.01.2016', 'DD.MM.YYYY');
  /* */
  PROCEDURE mp_PrictiMesic(
      b_Datum IN OUT DATE -- binární parametr
  )
  IS
  BEGIN
    b_Datum:=ADD_MONTHS(b_Datum, 1);
  END;
  /* */
BEGIN
  mp_PrictiMesic(l_Datum);
  dbms_output.put_line(TO_CHAR(l_Datum, 'DD.MM.YYYY')); -- 01.02.2016
END;

Přetěžování

Podobně jako v ostatních jazycích, tak i v PL/SQL lze metody přetížit, tzn. definovat několik se stejným názvem lišícím se pouze počtem nebo datovým typem parametrů.

DECLARE
  /* */
  FUNCTION mf_VratObvod(
      p_A NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    RETURN (p_A*4);
  END;
  /* */
  FUNCTION mf_VratObvod(
      p_A NUMBER
    , p_B NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    RETURN ((p_A+p_B)*2);
  END;
  /* */
BEGIN
  -- čtverec
  dbms_output.put_line(mf_VratObvod(10)); -- 40
  -- obdelník
  dbms_output.put_line(mf_VratObvod(10, 5)); -- 30
END;

Stored procedures

U rozsáhlejších aplikací doporučuji většinu byznys logiky soustředit do packagí (více v některém z dalších dílů). Na uložené procedury a funkce jde nicméně u mnoha převážně starších projektů stále narazit, a tak je vhodné se o nich alespoň v rychlosti zmínit.

CREATE OR REPLACE FUNCTION sf_VratAktualniRok
  RETURN CHAR
AS
BEGIN
  RETURN TO_CHAR(SYSDATE, 'YYYY');
END;
/

SELECT sf_VratAktualniRok
  FROM dual
;
-- 2016