PL/SQL v příkladech / Kompozitní datové typy

Pole

Problematika struktur, které jsou schopné pojmout více hodnot, je díky rozdělení databáze Oracle na SQL část a procedurální PL/SQL nadstavbu poněkud rozsáhlejší. Také jednotlivé verze databáze přináší drobné odlišnosti v použití daných struktur. Omezím se tedy jen na ukázku nejpoužívanějších typů, a to asociativního pole („index-by table“) deklarovaného v PL/SQL a vnořené tabulky („nested table“) deklarované na úrovni SQL.

Asociativní pole

PL/SQL nadstavba bohužel neobsahuje žádné předpřipravené typy pro pole čísel nebo řetězců, je tedy nutné pokaždé nejdřív provést deklaraci typu a až následně deklaraci vlastní proměnné (na použití typů deklarovaných v packagi se zaměřím v některém z dalších článků).

DECLARE
  -- asociativní pole čísel indexované celočíselně
  TYPE Number_TT IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  lt_Cislo Number_TT;
  -- asociativní pole slov indexované řetězcem
  TYPE Dictionary_TT IS TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(4000);
  lt_Slovnik Dictionary_TT;
BEGIN
  lt_Cislo(1):=NULL;
  lt_Cislo(2):=200;
  dbms_output.put_line(lt_Cislo(2)); -- 200
  -- počet prvků v poli
  dbms_output.put_line(lt_Cislo.COUNT); -- 2
  lt_Slovnik('prague'):='praha';
  lt_Slovnik('munich'):='mnichov';
  dbms_output.put_line(lt_Slovnik('prague')); -- praha
  -- existuje prvek na daném indexu?
  IF (lt_Slovnik.EXISTS('vienna')) THEN
    -- neexistuje, nic se nevypíše
    dbms_output.put_line(lt_Slovnik('vienna'));
  END IF;
  -- smazání pole
  lt_Slovnik.DELETE;
  dbms_output.put_line(lt_Slovnik.COUNT); -- 0
END;

Stejně jako u skalární proměnné, i do kompozitního typu lze přiřazovat hodnoty pomocí SQL dotazu. K tomu je určen příkaz BULK COLLECT INTO.

DECLARE
  TYPE Number_TT IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  lt_Employee_ID Number_TT;
BEGIN
  -- načtení všech zaměstnaneckých čísel seřazených vzestupně
  SELECT employee_id
    BULK COLLECT INTO
         lt_Employee_ID
    FROM employees
    ORDER BY employee_id ASC
  ;
  -- existuje alespoň jedno?
  IF (lt_Employee_ID.COUNT>0) THEN
    dbms_output.put_line(lt_Employee_ID(1)); -- 100
  END IF;
END;

Nested table

Dalším velmi používaným druhem pole je takzvaná nested-tabulka. Po deklaraci, která se provádí v SQL části, s ní může pracovat kdokoli s patřičnými právy. Není tedy omezena jen na konkrétní blok.

CREATE OR REPLACE TYPE Number_TT AS TABLE OF NUMBER;
/

DECLARE
  lt_Cislo Number_TT;
  lt_JineCislo Number_TT;
BEGIN
  -- inicializace 
  lt_Cislo:=Number_TT();
  -- alokace tří pozic
  lt_Cislo.EXTEND(3);
  -- naplnění
  lt_Cislo(1):=100;
  lt_Cislo(2):=200;
  lt_Cislo(3):=300;
  dbms_output.put_line(lt_Cislo.COUNT); -- 3
  -- naplnění rovnou při inicializaci
  lt_JineCislo:=Number_TT(100, 200, 300);
  dbms_output.put_line(lt_JineCislo.COUNT); -- 3
END;

Poměrně zdlouhavou práci s inicializací a alokací paměti, která u asociativního pole odpadá, vyvažuje výhoda v možnosti dotazování se pomocí jazyka SQL. Pole stačí obalit do klíčového slova TABLE(), k hodnotě se přistupuje přes VALUE().

DECLARE
  l_Soucet NUMBER;
  l_Prumer NUMBER;
  lt_Cislo Number_TT;
BEGIN
  lt_Cislo:=Number_TT(100, 200, 300);
  SELECT SUM(VALUE(t))
       , AVG(VALUE(t))
    INTO l_Soucet
       , l_Prumer
    FROM TABLE(lt_Cislo) t -- "t" slouží pouze jako alias
  ;
  dbms_output.put_line(l_Soucet); -- 600
  dbms_output.put_line(l_Prumer); -- 200
END;

Rekord

O něco pokročilejším kompozitním typem je rekord suplující klasickou strukturu, jakou známe z ostatních programovacích jazyků. Jde o proměnnou sdružující několik logicky souvisejících prvků.

DECLARE
  -- deklarace typu
  TYPE Employee_T IS RECORD(
      emp_id NUMBER
    , first_name VARCHAR2(20)
    , last_name VARCHAR2(20)
  );
  -- deklarace proměnné daného typu
  l_Employee Employee_T;
BEGIN
  l_Employee.emp_id:=100;
  l_Employee.first_name:='Steven';
  l_Employee.last_name:='King';
  dbms_output.put_line(l_Employee.last_name); -- King
END;

Datový typ rekord se hodí hlavně na dočasné uložení záznamu z tabulky. Pro načtení jednoho řádku se všemi sloupci tabulky slouží typ %ROWTYPE.

DECLARE
  TYPE Employee_T IS RECORD(
      emp_id employees.employee_id%TYPE
    , first_name employees.first_name%TYPE
    , last_name employees.last_name%TYPE
  );
  l_Employee Employee_T;
  l_Job jobs%ROWTYPE; -- typ řádek
BEGIN
  -- počet sloupců a typy se musí shodovat
  SELECT employee_id
       , first_name
       , last_name
    INTO l_Employee
    FROM employees
    WHERE (employee_id=100)
  ;
  dbms_output.put_line(l_Employee.last_name); -- King
  -- všechny sloupce
  SELECT *
    INTO l_Job
    FROM jobs
    WHERE (job_id='IT_PROG')
  ;
  dbms_output.put_line(l_Job.job_title); -- Programmer
END;

A na závěr dva příklady – asociativní pole rekordů a asociativní pole řádků tabulky.

DECLARE
  TYPE Employee_T IS RECORD(
      emp_id employees.employee_id%TYPE
    , first_name employees.first_name%TYPE
    , last_name employees.last_name%TYPE
  );
  -- pole rekordů
  TYPE Employee_TT IS TABLE OF Employee_T INDEX BY PLS_INTEGER;
  -- vlastní proměnná typu pole rekordů
  lt_Employee Employee_TT;
BEGIN
  -- jen vybrané sloupce
  SELECT employee_id
       , first_name
       , last_name
    BULK COLLECT INTO
         lt_Employee
    FROM employees
  ;
  dbms_output.put_line(lt_Employee.COUNT); -- 107
END;

DECLARE
  -- pole řádků
  TYPE Job_TT IS TABLE OF jobs%ROWTYPE INDEX BY PLS_INTEGER;
  -- vlastní proměnná typu pole řádků
  lt_Job Job_TT;
BEGIN
  -- všechny sloupce, všechny řádky
  SELECT *
    BULK COLLECT INTO
         lt_Job
    FROM jobs
  ;
  dbms_output.put_line(lt_Job.COUNT); -- 19
END;