Podstawy PL/SQL

Budowa bloku PL/SQL

Podstawową jednostką języka PL/SQL jest blok.

Blok PL/SQL przed wykonaniem musi zostać skompilowany, nie kompiluje się jednak do postaci wykonalnej (.exe, .dll, .lib), którą można uruchomić z poziomu systemu operacyjnego.

W PL/SQL nie można bezpośrednio używać żadnych instrukcji DDL.

Każdy blok PL/SQL może zawierać trzy sekcje: 


DECLARE
  /* Sekcja deklaracyjna */
BEGIN
  /* Sekcja wykonywalna */
EXCEPTION
  /* Sekcja obsługi błędów */
END;

Sekcja deklaracyjna i sekcja obsługi wyjątków są opcjonalne, zatem najprostszy blok PL/SQL może wyglądać w następujący sposób: 


BEGIN
  /* Sekcja wykonywalna */
END;

Przykład

Napisać blok PL/SQL, który będzie wstawiał do tabeli grupy_cen nowy rekord. 

BEGIN
  INSERT INTO grupy_cen VALUES (30, 10000, 30000);
END;

Bloki PL/SQL mogą być w sobie zagnieżdżane.

Budowa bloku PL/SQL

W PL/SQL istnieją trzy podstawowe rodzaje bloków.

Bloki anonimowe

Nie są przechowywane na serwerze bazy danych ani w postaci kodu, ani w postaci skompilowanej. Przed każdym wykonaniem muszą być kompilowane. 


DECLARE
  /* Sekcja deklaracyjna */
BEGIN
  /* Sekcja wykonywalna */
EXCEPTION
  /* Sekcja obsługi błędów */
END;

Procedury i funkcje

Procedury i funkcje (podprogramy składowane) są to bloki PL/SQL, które mają swoją nazwę. Są one kompilowane tylko jeden raz podczas tworzenia. Na serwerze bazy danych jest przechowywany zarówno kod procedury, jak i postać skompilowana. Wykonanie procedury lub funkcji nie wymaga ponownej kompilacji.

Do procedur i funkcji mogą być przekazywane parametry. Parametry mogą być również przekazywane z procedur i funkcji do środowiska, które je wywołuje. 


CREATE OR REPLACE PROCEDURE procedura1
IS
  /* Sekcja deklaracyjna */
BEGIN
  /* Sekcja wykonywalna */
EXCEPTION
  /* Sekcja obsługi błędów */
END;

Wyzwalacze

Wyzwalacze są to bloki PL/SQL, które nie mogą być wywoływane bezpośrednio poleceniem SQL. Ich wykonanie jest bezpośrednio zależne od zajścia pewnego zdarzenia, z którym zostały powiązane.

Do wyzwalaczy nie można bezpośrednio przekazać parametrów.

Przed wersją 7.3 wyzwalacze były przechowywane w bazie danych tylko w postaci kodu. Od wersji 7.3 włącznie przechowywana jest także postać skompilowana wyzwalaczy, co ma wpływ na wydajność. 


CREATE OR REPLACE TRIGGER trigger1
  BEFORE INSERT ON tabela1
DECLARE
  /* Sekcja deklaracyjna */
BEGIN
  /* Sekcja wykonywalna */
EXCEPTION
  /* Sekcja obsługi błędów */
END;

Deklarowanie zmiennych

W PL/SQL niektóre typy danych są inne niż w SQL:

  • CHAR(L) – maksymalna długość wynosi 32768 znaków zamiast 255 w SQL
  • VARCHAR2(L) – maksymalna długość wynosi 32768 znaków zamiast 4000 w SQL
  • BOOLEAN – typ, który może przyjmować tylko trzy wartości: TRUE, FALSE, NULL. Nie miał swojego odpowiednika w SQL

Zmienne są deklarowane w sekcji deklaracyjnej bloku PL/SQL.

Składnia deklaracji zmiennej jest następująca: 


nazwa_zmiennej typ_zmiennej [CONSTANT] [NOT NULL] [:= wartość];

Zamiast operatora przypisania może pojawić się słowo DEFAULT.

Zamiast jawnego podawania typu zmiennej można odwołać się do typu zmiennej wcześniej zadeklarowanej lub do typu kolumny bazodanowej poprzez użycie nazwy tej zmiennej (nazwy kolumny) i operatora %TYPE.

Jeżeli zmienna nie zostanie zainicjowana, to jej wartość wynosi NULL.

Przykład poprawnych deklaracji: 


DECLARE
  v_1 number(9);
  v_2 number CONSTANT := 3.14;
  v_3 varchar2(10) := 'Ala';
  v_4 v_1%TYPE;
  v_5 klienci.imie%TYPE;
  v_6 char (4) NOT NULL := 'Ala';
  v_7 v_1%TYPE;
BEGIN
  ...

Przykład niepoprawnych deklaracji: 


DECLARE
  v_1 number(2) := 123;
  v_2,v_3 varchar2(10);
  v_4 number(3) NOT NULL;
  v_5 varchar2;
BEGIN
  ...

W PL/SQL zasięg zmiennych, a więc obszar kodu, w którym zachowują swoją wartość rozciąga się od momentu zadeklarowania zmiennej do końca bloku, w którym zmienna ta została zadeklarowana.

Instrukcja SELECT i inne instrukcje DML w PL/SQL

Instrukcje DML: INSERT, UPDATE i DELETE w PL/SQL stosuje się podobnie jak w SQL, z tą różnicą, że zamiast podawania wartości stałych można stosować zmienne lub nawet wyrażenia.

Przykład

Napisać blok PL/SQL, który wstawi nowe zamówienie dla Jana Kowalskiego wraz z dwoma pozycjami obejmującymi komputer i pięć procesorów. Identyfikatory zamówienia i pozycji powinny zostać zaczerpnięte z sekwencji. 

DECLARE
  v_id_klienta   number (9) := 1000;
  v_id_komputera number (9) := 340;
  v_id_procesora number (9) := 510;
BEGIN
  INSERT INTO zamowienia (zam_id, zam_numer, zam_data_zamowienia,
                          zam_data_realizacji, zam_opis, zam_klt_id)
       VALUES (seq_zamowienia.nextval, 200, sysdate, null,
               'Nowe zamówienie na komputery', v_id_klienta);
  INSERT INTO pozycje_zamowien(pzm_id, pzm_lp, pzm_ilosc,
                pzm_zam_id, pzm_tow_id)
       VALUES (seq_pozycje_zamowien.nextval, 1, 1,
               seq_zamowienia.currval, v_id_komputera);
  INSERT INTO pozycje_zamowien(pzm_id, pzm_lp, pzm_ilosc,
                pzm_zam_id, pzm_tow_id)
       VALUES (seq_pozycje_zamowien.nextval, 2, 5,
               seq_zamowienia.currval, v_id_procesora);
  COMMIT;
END;

Inaczej jest w przypadku instrukcji SELECT. Jeżeli używamy jej w zwykłym SQL, to wynik zwrócony przez tą instrukcję jest wyświetlany na konsoli. W PL/SQL nie ma konsoli, a dane zwrócone przez zapytanie nie powinny zostać pokazane użytkownikowi, ale przetworzone przez kolejne instrukcje kodu. Aby to umożliwić, instrukcja SELECT w PL/SQL została wzbogacona o kolejną klauzulę, która pozwala przekierować wartości zwrócone przez zapytanie do wskazanych zmiennych. 


SELECT kol_1, kol_2, kol_3, ..., kol_n
  INTO zmienna_1, zmienna_2, zmienna_3, ..., zmienna_n
  FROM tabela
 WHERE ...

Przykład

Napisać blok PL/SQL, który wstawi nowe zamówienie dla Jana Kowalskiego wraz z dwoma pozycjami obejmującymi komputer i pięć procesorów. Identyfikatory zamówienia i pozycji powinny zostać zaczerpnięte z sekwencji. Identyfikatory klienta i towarów powinny zostać odczytane z bazy. 

DECLARE
  v_id_klienta   number (9);
  v_id_komputera number (9);
  v_id_procesora number (9);
BEGIN
  SELECT klt_id
    INTO v_id_klienta
    FROM klienci
   WHERE klt_imie = 'Jan'
     AND klt_nazwisko = 'Kowalski';
  INSERT INTO zamowienia (zam_id, zam_numer, zam_data_zamowienia,
                          zam_data_realizacji, zam_opis, zam_klt_id)
       VALUES (seq_zamowienia.nextval, 200, sysdate, null,
               'Nowe zamówienie na komputery', v_id_klienta);
  SELECT tow_id
    INTO v_id_komputera
    FROM towary
   WHERE tow_nazwa = 'Komputer';
  INSERT INTO pozycje_zamowien(pzm_id, pzm_lp, pzm_ilosc,
                pzm_zam_id, pzm_tow_id)
       VALUES (seq_pozycje_zamowien.nextval, 1, 1,
               seq_zamowienia.currval, v_id_komputera);
  SELECT tow_id
    INTO v_id_procesora
    FROM towary
   WHERE tow_nazwa = 'Procesor Pentium III 700 MHz';
  INSERT INTO pozycje_zamowien(pzm_id, pzm_lp, pzm_ilosc,
                pzm_zam_id, pzm_tow_id)
       VALUES (seq_pozycje_zamowien.nextval, 2, 5,
               seq_zamowienia.currval, v_id_procesora);
  COMMIT;
END;

Jeżeli korzystamy z instrukcji SELECT w PL/SQL, to instrukcja ta MUSI zwracać dokładnie jeden wiersz. Jeżeli zwróci mniej lub więcej, to instrukcja ta kończy się niepowodzeniem.

Instrukcje sterujące

Instrukcja decyzyjna

Instrukcja decyzyjna IF pozwala na selektywne wykonywanie pewnych instrukcji w oparciu o warunek logiczny określony wyrażeniem.

Pełna składnia instrukcji IF: 


IF wyrażenie_logiczne1 THEN
  ciąg_instrukcji1
ELSIF wyrażenie_logiczne2 THEN
  ciąg_instrukcji2
...
ELSIF wyrażenie_logiczne3 THEN
  ciąg_instrukcji3
ELSE
  ciąg_instrukcji4
END IF;

Klauzule ELSIF i ELSE są opcjonalne i jeżeli nie ma takiej potrzeby, mogą zostać pominięte.

Podczas wykonywania instrukcji IF obliczane jest wyrażenie wyrażenie_logiczne1. Jeżeli jest ono prawdziwe, to wykonywane są instrukcje ciąg_instrukcji1, a następnie sterowanie przechodzi do pierwszej instrukcji po klauzuli END IF. W przypadku, gdy wyrażenie_logiczne1 jest fałszywe, lub ma wartość NULL, obliczane są kolejne warunki logiczne, aż któryś z nich okaże się prawdziwy. Jeżeli żaden z nich nie będzie prawdziwy, wykonywane są instrukcje następujące po klauzuli ELSE (ciąg_instrukcji4).

Przykład

Użycie instrukcji IF może zakończyć się trudnym do wykrycia błędem w przypadku, gdy nie pamięta się o możliwości wystąpienia wartości NULL. Poniżej, po prawej i lewej stronie, wbrew pozorom, nie znajduje się kod równoważny. 

DECLARE
  v1 number;
  v2 number;
  v3 varchar2(100);
BEGIN
  ...						...
  IF v1 > v2 THEN				IF v1 <= v2 THEN
    v3 := 'v1 większe';		  v3 := 'v1 mniejsze, równe';
  ELSE						ELSE
    v3 := 'v1 mniejsze, równe';	  v3 := 'v1 większe';
  END IF;					END IF;
  ...						...
END;

Pętla LOOP

Najprostsza pętla składa się z ciała instrukcji, które mają być powtarzane, zamkniętego między ogranicznikami. 


LOOP i END LOOP.
LOOP
  ciąg_instrukcji
END LOOP;

Za każdym razem, gdy wykonanie osiągnie instrukcję END LOOP sterowanie jest przekazywane z powrotem do instrukcji poprzedzonej przez klauzulę LOOP.

Aby wyjść z takiej pętli należy wykonać instrukcję EXIT, która powoduje natychmiastowe opuszczenie pętli (bez wykonania się bieżącego cyklu). Jeżeli do instrukcji EXIT dodamy klauzulę WHEN wraz z odpowiednim warunkiem, to pętla zostanie opuszczona tylko w przypadku prawdziwości tego warunku; 


EXIT [WHEN warunek];

Przykład

Napisać blok PL/SQL, który wstawi do tabeli grupy_cen 50 wierszy. 

DECLARE
  v1 binary_integer;
BEGIN
  v1 := 0;
  LOOP
    v1 := v1 + 1;
    EXIT WHEN v1 > 50;
    INSERT INTO grupy_cen
         VALUES (30, 100000, 500000);
  END LOOP;
END;

Pętla WHILE

Pętla WHILE jest nieco zmodyfikowaną pętlą LOOP. Do zakończenia nie wymaga ona wpisania instrukcji EXIT, chociaż instrukcja ta może się w niej pojawić. Warunek wykonywania się pętli jest określany w jej nagłówku po słowie WHILE. 


WHILE warunek_logiczny LOOP
  ciąg_instrukcji
END LOOP;

Pętla wykonuje się cyklicznie, dopóki warunek warunek_logiczny jest prawdziwy.

Przykład

Napisać blok PL/SQL, który wstawi do tabeli grupy_cen 50 wierszy. 

DECLARE
  v1 binary_integer;
BEGIN
  v1 := 0;
  WHILE v1 < 50 LOOP
    v1 := v1 + 1;
    INSERT INTO grupy_cen VALUES (30, 100000, 500000);
  END LOOP;
END;

Pętla FOR

Pętla FOR posiada dodatkowo przed słowem kluczowym LOOP frazę sterującą określającą liczbę iteracji do wykonania.

Składnia pętli FOR: 


FOR zmienna IN [REVERSE] zakres_dolny..zakres_górny LOOP
  ciąg_instrukcji
END LOOP;

Podczas każdej kolejnej iteracji zmienna sterująca pętli FOR przybiera kolejną wartość z zakresu określonego przez zakres_dolny i zakres_górny.

Jeżeli użyjemy słowa REVERSE, to zmienna sterująca będzie przybierać wartości z określonego zakresu malejąco.

Zmienna sterująca pętlą nie musi być deklarowana, a jej zasięg jest tylko w zakresie pętli FOR, którą steruje.

Przykład

Napisać blok PL/SQL, który wstawi do tabeli grupy_cen 50 wierszy. 

DECLARE
  v1 number;
  v2 number;
BEGIN
  v1 := 11;
  v2 := 60;
  FOR v3 IN v1..v2 LOOP –- Pętla wykona się 50 razy
    v2 := 15; -- Przypisanie do v2 wartości 15
    INSERT INTO grupy_cen
         VALUES (30, 100000, 500000);
  END LOOP; -- Zmiana wartości v1 i v2 nie ma wpływu na pętlę
END;