Oskar Skibski, grupa nr 4, sala 2043

Bazy danych 2015/16

Laboratorium 11 (21.12.2015) - PL/SQL / PODSTAWY

Na dzisiejszych zajęciach nauczymy się PL/SQLa na przykładach.

Bloki, funkcje i procedury

Blok składa sie z deklaracji i operacji. W deklaracjach możemy używać typów znanych z SQL (VARCHAR2(80), DATE, NUMBER(10), ...). PL/SQL udostępnia podstawowe konstrukcje znane z innych języków jak IF/ELSE, WHILE, FOR a także LOOP:

DECLARE
  data DATE := SYSDATE;
  i INT := 0;
  napis VARCHAR2(80);
BEGIN
  WHILE 10 > i LOOP
    dbms_output.put_line('while, i = ' || i);
    i := i+1;
  END LOOP;
  
  FOR i IN REVERSE 1..5 LOOP
    IF MOD(i, 2) = 0 THEN
      dbms_output.put_line('for, i: parzysta');
    ELSE
      dbms_output.put_line('for, i: nieparzysta');
    END IF;
  END LOOP;

  --ile jest rowne i? 1 czy 5?
  dbms_output.put_line('i: ' || i || '!');
  
  LOOP
    i:=i+1;
    IF i > 12 THEN EXIT; END IF;
    dbms_output.put_line('loop, i: wciaz mniejsze niz 12 (' || i || ')');
  END LOOP;
END;
/

Funkcja dbms_output.put_line służy do wypisywania tekstu na wyjście. Aby nam działała należy wywołać w SQL*Plus komendę set serveroutput on. (Uwaga! Do tego tekstu nie będziemy mieć dostępu z PHP!) Znak || służy do konkatenacji. Należy pamiętać, że każdą deklarację bloku musimy zakończyć znakiem / w nowej linii.

PL/SQL nie byłby jednak PL/SQLem gdyby nie pozwalał na ciekawsze rzeczy. Po pierwsze typy zmiennych możemy pobierać z bazy danych, np. typ pola zawierającego pensję pracownika w tabeli emp to emp.sal%TYPE. Ponadto zmienna może być typu wierszowego, który także możemy wyciągnąć z bazy danych - np. emp%ROWTYPE. A po co nam to? Otóż w PL/SQL w nasze procedury i funkcje możemy wplatać SQL na różne sposoby. Sposób najprostszy:

DECLARE
  pensja emp.sal%TYPE;
BEGIN
  SELECT sal INTO pensja
  FROM emp WHERE empno = 7839;
  ...
END;
/

Ta operacja przypisze nam na zmienną pensja wartość komórki sal pracownika z numerem 7839. Inny przykład:

DECLARE
  row emp%ROWTYPE;
BEGIN
  SELECT * INTO row
  FROM emp WHERE ename = 'KING';
  ...
END;
/

Oczywiście zapytanie musi zwracać maksymalnie jeden wiersz, w innym wypadku skrypt zakończy się błędem. Do pól odwołujemy się tak jak już się nauczyliśmy - przez kropkę: row.empno.

Możemy także iterować po zapytaniach, najłatwiej robić to pętlą FOR.

CREATE PROCEDURE wypisz_pensje(departament NUMBER) IS 
  zarabia VARCHAR2(10) := ' zarabia ';
BEGIN
  FOR row IN (SELECT * FROM emp) LOOP
    IF row.deptno IS NOT NULL AND row.deptno = 10 THEN
      dbms_output.put_line(row.ename || zarabia || row.sal);
    END IF;
  END LOOP;
END;
/

Jak widać powyżej procedura jest blokiem z lekko zmodyfikowanym nagłówkiem. Należy pamiętać o tym, że w typach nie możemy podawać wielkości (piszemy VARCHAR2 zamiast VARCHAR2(10)). Po nazwie parametru możemy też dodać typ - IN | OUT | IN OUT - który definiuje czy parametr jest wejściowy czy wyjściowy. Procedurę uruchamiamy w SQL*Plus za pomocą komendy EXECUTE:

EXECUTE wypisz_pensje(10);

Procedury kasujemy jak tabele - komendą DROP.

Funkcje różnią się od procedury tym, że mogą zwrócić wartość:

CREATE OR REPLACE FUNCTION policz_pracownikow(departament NUMBER) RETURN NUMBER IS 
  retval NUMBER;
BEGIN
  SELECT COUNT(*) asd INTO retval 
  FROM emp WHERE deptno = departament;
  
  RETURN retval;
END;
/

Dodane OR REPLACE pozwala zastąpić funkcję jeżeli już taka istnieje (bardzo przydatne).

Użycie funkcji w SQL*Plus nie jest już takie wygodne. Musimy zdefiniować wartość i na nią przypisać wynik funkcji. Możemy ją potem też wypisać.

VARIABLE zmienna NUMBER;
EXECUTE :zmienna := policz_pracownikow(10);
PRINT :zmienna;

Ponadto jedną z operacji może być operacja zmiany danych w bazie - DELETE, INSERT, UPDATE. Funkcje te nie mogą być zbytnio poprzeplatane, ponieważ często przy jednej operacji blokowana jest cała tabela i kolejna "czeka" na zwolnienie jej. Należy także kończyć takie procedury/funkcje poleceniem COMMIT.

Triggery

Przeanalizujmy przykład triggera (z zajęć Pana Walenia):

CREATE OR REPLACE TRIGGER sprawdzWypozyczenia 
BEFORE INSERT OR UPDATE ON wypozyczenia
FOR EACH ROW
BEGIN
    IF :NEW.data_wypozyczenia IS NOT NULL AND :NEW.data_zwrotu IS NOT NULL THEN
      IF :NEW.data_wypozyczenia > :NEW.data_zwrotu THEN
        raise_application_error(-20000,'Data zwrotu musi być większa niż data wypożyczenia');
      END IF;
    END IF;
END;
/

Jak widzimy definiując trigger musimy określić parę rzeczy:

• jakich zdarzeń dotyczy - INSERT OR UPDATE ON wypozyczenia; w liście zdarzeń może występować także DELETE; ponadto przy aktualizacji możemy wyszczególnić których pól pisząc UPDATE OF sal, job
• czy ma być uruchomiony przed czy po tym zdarzeniu - BEFORE; alternatywą jest oczywiście AFTER
• czy ma być uruchomiony raz czy dla każdego wiersza - FOR EACH ROW; możemy także wyszczególnić dla których wierszy pisząc na przykład FOR EACH ROW WHEN NEW.sal > 5000

Treść bloku PL/SQL w trigerze różni się od zwykłego paroma rzeczami. Po pierwsze w bloku można użyć dodatkowych specjalnych konstrukcji IF INSERTING, IF UPDATING i IF DELETING. Po drugie, jeżeli przetwarzanie jest dokonywane wiersz po wierszu wówczas w bloku predefiniowana jest zmienna wierszowa :NEW z nowymi wartościami (przy INSERT i UPDATE) oraz odpowiadająca zmienna :OLD z wartościami starymi (przy UPDATE i DELETE).

Użyta w przykładzie funkcja raise_application_error przerywa zmianę w bazie danych, wykonuje ROLLBACK i zwraca błąd z podanym komunikatem.

Autoincrement w Oracle

Często w bazie danych zachodzi potrzeba utworzenia sztucznego klucza (np. idosoba). Bardzo wygodnie jest wówczas wymusić na bazie aby sama nadawała kolejne numery id. Bez wsparcia ze strony bazy danych może dojść do różnych anomali:

• jeżeli nowemu wierszowi będziemy nadawać pierwsze wolne id (możemy zrobić to atomowo), to po skasowaniu ostatniego wiersza kolejny dostanie to samo id; może to spowodować dużo problemów i nie jest to dobre rozwiązanie
• jeżeli najwyższe dotychczas nadane id będziemy gdzieś przechowywać, to pojawia się problem z atomowością operacji - może się bowiem zdarzyć, że zanim zwiększymy nasz licznik równoległy skrypt może odczytać starą wartość i wstawimy do bazy dwa rekordy z tym samym id

W niektórych systemach zarządzania bazą danych (np. MySQL) kolumnie można ustawić typ AUTO_INCREMENT. Wówczas wstawiając nowy wiersz baza sama dba o to, żeby nadać kolejne niewykorzystany numer. W Oraclu nie jest to niestety takie łatwe...

Aby uzyskać ten sam efekt użyjemy sekwencji i triggera. Najpierw tworzymy sekwencję:

CREATE SEQUENCE idosoba_seq
START WITH 8000
INCREMENT BY 1;

Następnie tworzymy trigger który automatycznie przypisze kolejny element sekwencji przy wstawianiu nowego wiersza.

CREATE TRIGGER idosoba_trigger
BEFORE INSERT ON osoba
FOR EACH ROW
BEGIN
  SELECT idosoba_seq.nextval INTO :NEW.idosoba FROM dual;
END;
/

Powyższy trigger nadpisuje id nowowstawianego wiersza.

Warto wiedzieć także, że możemy nie używać triggera, bezpośrednio w zapytaniu INSERT odwoływać się do sekwencji. Rozwiązanie to jednak nie jest takie ładne, trigger pozwala nam zapomnieć w ogóle o konieczności wstawiania sztucznego id.

Kursory

Jeżeli chcemy przetwarzać tabelę wiersz po wierszu to możemy użyć kursorów:

CREATE OR REPLACE FUNCTION dostajesz_awans RETURN NUMBER IS 
  licznik INT := 0;
  CURSOR empcur IS (SELECT * FROM emp) FOR UPDATE OF sal;
BEGIN
  FOR row IN empcur LOOP
    IF row.deptno = 10 THEN 
      UPDATE emp SET sal = row.sal * 2
      WHERE CURRENT OF empcur;
      licznik := licznik + 1;
    END IF;
  END LOOP;
  
  COMMIT;
  
  RETURN licznik;
END;
/

Na kursory można zatem patrzyć jak na alias do zapytania, jednak możemy używać ich też w bardziej tradycyjny sposób:

CREATE OR REPLACE FUNCTION dep_list RETURN NUMBER IS 
  emprec EMP%ROWTYPE;
BEGIN
  OPEN empcur;
  LOOP
    FETCH empcur INTO emprec;
    EXIT WHEN empcur%NOTFOUND;
    
    -- przetwarzanie wiersza
  END LOOP;
  CLOSE empcur;
  
  COMMIT;
  
  RETURN empcur;
END;
/