Złożone typy danych i kursory

Rekordy

Typ rekordowy grupuje w sobie pewną ilość powiązanych logicznie ze sobą zmiennych. Świetnie nadaje się do odwzorowania jednego wiersza tabeli w kodzie PL/SQL. 


TYPE typ_rekordowy IS RECORD (
zmienna1 typ1,
zmienna2 typ2
...
)

Przykład

Zdefiniować typ danych do przechowywania informacji o osobie: nazwiska, imienia i daty urodzenia. Zadeklarować kilka zmiennych nowego typu. 

DECLARE
  TYPE t_osoba IS RECORD (
nazwisko varchar2(64) NOT NULL,
imie varchar2(40) NOT NULL,
data_ur date);
  v_ojciec t_osoba;
  v_matka t_osoba;
BEGIN
  ...

Inna metoda deklaracji zmiennej rekordowej polega na referencji do istniejącej zmiennej lub do tabeli: 


zmienna tabela%ROWTYPE;

Dostęp do pól rekordu uzyskuje się przy pomocy notacji kropkowej. Nie można użyć zmiennej rekordowej bezpośrednio po słowie kluczowym VALUES w instrukcji INSERT, ani po słowie INTO w instrukcji SELECT.

Przykład

Zdefiniować zmienną do przechowywania informacji o towarze zawartych w tabeli towary. Następnie wczytać do tej zmiennej informacje o towarze "Szafka". 

DECLARE
  v_tow towary%ROWTYPE;
BEGIN
  SELECT tow_id, tow_nazwa, tow_jednostka, tow_cena, tow_podatek, tow_tow_id
    INTO v_tow.tow_id, v_tow.tow_nazwa, v_tow.tow_jednostka, v_tow.tow_cena, v_tow.tow_podatek, v_tow.tow_tow_id
    FROM towary
   WHERE tow_nazwa = 'Szafka';
   ...

Tablice

Zmienne typu tablicowego przechowują szereg wartości innego, jednorodnego typu w postaci ciągu pojedynczych elementów. Do poszczególnych pozycji w zmiennej tablicowej uzyskujemy dostęp poprzez podanie nazwy tej zmiennej i numeru pozycji (indeksu) dla konkretnego elementu. 


TYPE typ_tablicowy IS TABLE OF typ_składowy
INDEX BY BINARY_INTEGER

Zmienne typu tablicowego w PL/SQL nie mają ograniczonej wielkości, można do nich wkładać elementy na dowolnej pozycji z zakresu liczb binary_integer. Dopiero przypisanie wartości do jakiegoś elementu tablicy tworzy ten element.

Odwołania do poszczególnych pozycji tablicy odbywają się poprzez podanie indeksu w okrągłych nawiasach.

Przykład 


DECLARE
  TYPE t_nazwiska IS TABLE OF varchar2(100)
  INDEX by binary_integer;
  v_nazwiska t_nazwiska;
  v1 varchar2(100);
BEGIN
  v_nazwiska(0) := ‘Kowalski’;
  v_nazwiska(1000) := ‘NOWAK’;
  v_nazwiska(-157230) := ‘Misiak’;
  v_1 := v_nazwiska(1000);
  ...

Atrybuty zmiennych tablicowych

Zmienne tablicowe posiadają szereg atrybutów, które pozwalają łatwiej operować na ich zawartości. Poniżej zostały one pokrótce scharakteryzowane.

  • EXIST(n) – Sprawdza, czy na pozycji n w tablicy znajduje się jakiś element. Zwraca TRUE lub FALSE.
  • COUNT – Zwraca ilość elementów znajdujących się w tablicy.
  • FIRST, LAST – Zwracają indeks pierwszego i ostatniego elementu w tablicy.
  • NEXT(n), PRIOR(n) – Zwracają indeks następnego i poprzedniego elementu w tablicy, względem zadanej pozycji n.
  • DELETE, DELETE(n), DELETE(m,n) – usuwa elementy z tablicy. W zależności od parametrów usuwa całą tablicę, n-ty element, lub elementy od m do n.

Przykład

Napisać funkcję, która z zadanej tablicy liczb usunie wszystkie liczby większe niż 1000, natomiast liczby ujemne zamieni na zero. Funkcja powinna zwrócić ilość elementów, jakie pozostaną w tablicy po jej przetworzeniu. 

CREATE OR REPLACE FUNCTION modyfikuj_tablice RETURN number
IS
  TYPE t_liczby IS TABLE OF number
  INDEX BY BINARY_INTEGER;	-- definicja typu
  v_liczby_tab t_liczby;		-- deklaracja zmiennej tablicowej
  v_index binary_integer;	-- deklaracja zmiennych pomocniczych
  v_koniec binary_integer;
BEGIN
  -- wywołanie hipotetycznej procedury, która inicjalizuje
  -- tablice wstawiajc tam w dowolny sposób dowolna ilość liczb,
  -- np. przeczytanych z pliku lub tabeli bazodanowej
  tab_init(v_liczby_tab);
  -- znalezienie najmniejszego indeksu zajętego w tablicy
  v_index := v_liczby_tab.FIRST;
  -- znalezienie największego indeksu zajętego w tablicy
  v_koniec := v_liczby_tab.LAST;
  -- przetwarzanie wszystkich elementów zawartych w tablicy
  LOOP
    -- wykonanie operacji
    IF v_liczby_tab(v_index) > 1000 THEN
      v_liczby_tab.DELETE(v_index);
    ELSIF v_liczby_tab(v_index) < 0 THEN
      v_liczby_tab(v_index) := 0;
    END IF;
    -- jeżeli przetworzylimy ostatni element, to koczymy pętlę
    EXIT WHEN v_index = v_koniec;
    -- znalezienie indeksu kolejnego elementu
    v_index := v_liczby_tab.NEXT(v_index);
  END LOOP;
  -- zwrócenie ilości elementów w przetworzonej tablicy
  RETURN v_liczby_tab.COUNT;
END;

Kursory

Kursory są konstrukcją PL/SQL służącą do sekwencyjnego przetwarzania zbioru wielu wierszy zwróconych przez zapytanie z bazy danych.

Korzystanie z kursora

Przetwarzanie każdego kursora można podzielić na cztery etapy:

Deklaracja

Kursory deklaruje się w sekcji deklaracyjnej bloku PL/SQL.


DECLARE
CURSOR nazwa_kursora IS
    wyrażenie_select;
BEGIN

wyrażenie_select jest to dowolne zapytanie zwracające pewną ilość kolumn i wierszy.

Otwarcie

Otwarcia kursora dokonujemy w sekcji wykonywalnej bloku PL/SQL. Ten sam kursor może być otwarty tylko jeden raz.


OPEN nazwa_kursora;

Pobieranie wierszy

Przetworzenie wszystkich danych (lub niekoniecznie wszystkich) wybranych przez zapytanie odbywa się poprzez sekwencyjne wywoływanie instrukcji FETCH.


FETCH nazwa_kursora INTO lista_zmiennych;

Każda kolejna instrukcja FETCH ściąga jeden, kolejny wiersz ze zbioru utworzonego przez zapytanie. Każdy wiersz jest ściągnięty tylko jeden raz i nie ma możliwości powtórnego ściągnięcia tego samego wiersza.

Zamknięcie

Zamknięcie kursora powoduje zwolnienie zasobów zarezerwowanych na jego otwarcie. Kursor przestaje być dostępny i może być ponownie otworzony.


CLOSE nazwa_kursora;

Atrybuty kursora

Atrybuty kursora opisują pewne jego cechy i mogą być wykorzystane np. do określenia, kiedy należy zaprzestać pobierania kolejnych wierszy.

Do atrybutów kursora odwołujemy się w następujący sposób: nazwa_kursora%nazwa_atrybutu

Istnieją cztery atrybuty kursora:

  • FOUND – Zwraca TRUE, jeśli ostatnio wykonana komenda FETCH zwróciła wiersz, w przeciwnym wypadku FALSE. Jeżeli kursor jest nie otwarty, odwołanie się powoduje błąd ORA-01001 i zgłoszenie wyjątku: invalid_cursor
  • NOTFOUND – Zwraca TRUE, jeśli ostatnio wykonana komenda FETCH nie zwróciła już poprawnego wiersza, w przeciwnym wypadku FALSE. Jeżeli kursor jest nie otwarty, odwołanie się powoduje błąd ORA-01001 i zgłoszenie wyjątku: invalid_cursor
  • ISOPEN – Zwraca TRUE bądź FALSE w zależności od tego, czy kursor jest zamknięty, czy otwarty.
  • ROWCOUNT – Zwraca ilość wierszy już ściągniętych z kursora.

Przykład

Napisać funkcję, która zwróci opisy wszystkich niezrealizowanych zamówień „sklejone” w postaci jednego napisu, w którym poszczególne opisy będą rozdzielone znakami „ $^$ ” 

CREATE OR REPLACE FUNCTION opisy RETURN varchar2
IS
  CURSOR c_opisy IS
    SELECT zam_opis
      FROM zamowienia
     WHERE zam_data_realizacji IS NULL;
  v_napis varchar2(32760);
  v_opis zamowienia.zam_opis%TYPE;
BEGIN
  OPEN c_opisy;
  LOOP
    FETCH c_opisy INTO v_opis;
    EXIT WHEN c_opisy%NOTFOUND;
    v_napis := v_napis || v_opis || ' $^$ ';
  END LOOP;
  CLOSE c_opisy;
  RETURN substr(v_napis, 1, length(v_napis)-3);
END;

Pętla kursorowa

Przetwarzanie kursorów można uprościć dzięki tzw. pętlom kursorowym. 


FOR zmienna_kursorowa IN nazwa_kursora LOOP
  ciąg_instrukcji
END LOOP;

Zmienna zmienna_kursorowa jest zmienną typu rekordowego. Nie powinna być deklarowana. Stosując pętle kursorowe nie trzeba pamiętać o otwieraniu, ściąganiu i zamykaniu kursora. Pętla wykona się tyle razy, ile wierszy zostanie znalezionych przez zapytanie kursora, a przy każdym wykonaniu się pętli zmienna_kursorowa będzie zawierać kolejny wiersz zwrócony z zapytania.

Przykład

Napisać funkcję, która zwróci opisy wszystkich niezrealizowanych zamówień „sklejone” w postaci jednego napisu, w którym poszczególne opisy będą rozdzielone znakami "$^$" 

CREATE OR REPLACE FUNCTION opisy RETURN varchar2
IS
  CURSOR c_opisy IS
    SELECT zam_opis
      FROM zamowienia
     WHERE zam_data_realizacji IS NULL;
  v_napis varchar2(32760);
BEGIN
  FOR zamowienie IN c_opisy LOOP
    v_napis := v_napis || zamowienie.zam_opis || ' $^$ ';
  END LOOP;
  RETURN substr(v_napis, 1, length(v_napis)-3);
END;

Parametryzowanie kursorów

Kursory można parametryzować poprzez zmienne lub poprzez parametry.

Parametryzowanie kursorów

Zakres wierszy obejmowanych przez kursor można uzależnić od pewnego parametru odwołując się przy deklaracji kursora w klauzuli WHERE zapytania do pewnej zadeklarowanej wcześniej zmiennej.

W czasie wykonywania się bloku zmienna ta może przybierać różne wartości, a zatem kursor będzie definiowany przez inne zapytanie.

Przykład 


DECLARE
  v1 klienci.klt_typ%TYPE;
  CURSOR c_klt IS
    SELECT * FROM klienci WHERE klt_typ = v1;
BEGIN
  v1 := ‘IND’;
  OPEN c_klt;
  ...
  v1 := ‘GSP’;
  OPEN c_klt;
  ...
END;

Parametryzowanie poprzez parametry

Ten sposób parametryzacji jest bardziej czytelny i polega na zdefiniowaniu parametrów formalnych podczas deklarowania kursora, a następnie wprowadzeniu parametrów aktualnych podczas otwierania tego kursora. 


DECLARE
  CURSOR nazwa_kursora(parametr1 typ1{, parametr2 typ2}) IS
    wyrażenie_select
BEGIN
OPEN nazwa_kursora(wartość1{,wartość2});
  ...
END;

Przykład 


DECLARE
  CURSOR c_klt(cup_typ varchar2) IS
    SELECT * FROM klienci WHERE klt_typ = cup_typ;
BEGIN
  OPEN c_klt(‘IND’);
  ...
  OPEN c_klt(‘GSP’);
  ...
END;