PL/SQL

Zaawansowane tematy PL/SQL

Piotr Medoń

Cele

• Omówienie transakcji bazodanowych

• Omówienie obsługi wyjątków

• Zarządzanie perspektywami

• Tworzenie i usuwanie sekwencji

• Budowa wyzwalaczy

2

Transakcje bazodanowe

• Transakcja zaczyna się wraz z pierwszą instrukcją DML (INSERT lub UPDATE lub DELETE)

• Transakcja kończy się i jest zatwierdzana, gdy użytkownik wyda polecenie COMMIT, lub wyda instrukcje DDL (np. CREATE TABLE, TRUNCATE TABLE …)

• Transakcja kończy się i jest wycofywana, gdy użytkownik wyda polecenie ROLLBACK, rozłączy się z bazą danych, lub nastąpi awaria bazy danych

3

Transakcje bazodanowe

• Zmiany danych wykonane za pomocą instrukcji DML są widoczne dla sesji użytkownika od razu

• Zmiany są widoczne dla sesji innych użytkowników dopiero w momencie zatwierdzenia transakcji

4

INSERT COMMIT

Drugi użytkownik widzi wynik operacji INSERT dopiero po operacji COMMIT

Transakcje bazodanowe

• Baza danych w przypadku wycofania transakcji przywraca oryginalne dane. To znaczy, że po sekwencji instrukcji:

INSERT…

UPDATE …

DELETE …

ROLLBACK;

nie pozostanie żaden ślad w systemie!

5

Transakcje bazodanowe

• Planowanie transakcji bazodanowej

– Transakcje zatwierdzamy instrukcją COMMIT, gdy zapisane dane są spójne,

– Pisząc kod mamy na uwadze, że równolegle z jego wykonaniem wykonywać się będą inne sesje i transakcje,

– Pisząc kod zawsze musimy wziąć pod uwagę możliwość wystąpienia awarii.

6

Obsługa wyjątków

7

Obsługa wyjątków

• Wyjątki pojawiają się, gdy z powodu niepoprawnej sytuacji program nie może być dalej przetwarzany.

Wykonywany program

Zaistnienie okoliczności uniemożliwiającej dalszą pracę programu

WYJĄTEK

Przykład braku obsługi wyjątków

DECLARE

a NUMBER := 0;

b NUMBER;

c NUMBER;

BEGIN

b := 20;

c := b / a;

DBMS_OUTPUT.PUT_LINE(c);

END;

Po wystąpieniu wyjątku przetwarzanie programu zostaje przerwane

Przykład sekcji obsługi wyjątków DECLARE a NUMBER := 0; b NUMBER; c NUMBER; BEGIN b := 20; c := b / a; DBMS_OUTPUT.PUT_LINE(c); EXCEPTION WHEN ZERO_DIVIDE THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE(‘Nie można dzielić przez 0’); END;

Sekcja obsługi wyjątków pozwala przechwycić sterowanie w przypadku wystąpienia błędu

Konstrukcja sekcji obsługi wyjątków

EXCEPTION WHEN wyjątek THEN obsługa_wyjątku1; WHEN wyjątek THEN obsługa_wyjątku1; WHEN OTHERS THEN obsługa pozostałych wyjątków END;

11

• Sekcje obsługi wyjątków budujemy bezpośrednio przed instrukcją END kończącą blok instrukcji PL/SQL

Obsługa wyjątków

• Sekcje obsługi wyjątków możemy budować w blokach anonimowych, procedurach, funkcjach i wyzwalaczach bazodanowych

12

DECLARE … BEGIN … EXCEPTION WHEN NO_DATA_FOUND THEN … END;

PROCEDURE test IS … BEGIN … EXCEPTION WHEN NO_DATA_FOUND THEN … END;

Obsługa wyjątków

• Sekcje obsługi wyjątków możemy budować również w blokach PL/SQL zawartych w innych blokach PL/SQL

13

BEGIN … BEGIN SELECT … INTO … FROM … WHERE ….; EXCEPTION WHEN NO_DATA_FOUND THEN … END; … END;

Obsługa wyjątków • Wyjątki dzielimy na wyjątki systemu i wyjątki

zdefiniowane przez użytkownika

• Wyjątki systemu pojawiają się, gdy baza danych Oracle odmawia wykonania niektórych operacji. Przykłady wyjątków systemu:

Kod wyjątku Opis

ZERO_DIVIDE Dzielenie przez zero

NO_DATA_FOUND Nie znaleziono danych

TOO_MANY_ROWS Pobrano zbyt dużo wierszy

INVALID_NUMBER Podana wartość nie jest poprawną liczbą

Obsługa wyjątków

• W celu obsłużenia wyjątków, czyli sytuacji niepoprawnych, budujemy sekcje obsługi wyjątków

• Kod obsługi wyjątku powinien: – W miarę możliwości przywrócić poprawny stan

systemu: wycofać transakcję poleceniem ROLLBACK, zamknąć otwarte kursory, oznaczyć nieprzetworzone lub przetworzone dane.

– Zawiadomić operatora o błędzie i udostępnić ilość informacji wystarczającą do diagnozy błędu (kod błędu, dokładną lokalizację błędu, informacje na temat przetwarzanego rekordu)

Obsługa wyjątków

• W sekcji obsługi błędów możemy wykorzystać następujące funkcje:

– Funkcja zwracająca kod błędu:

sqlcode

– Funkcja zwracająca komunikat błędu:

sqlerrm

– Funkcja zwracająca miejsca podniesienia wyjątku:

dbms_utility.format_error_backtrace

16

Przykład stosowania funkcji zwracających informacje o wyjątkach

EXCEPTION WHEN OTHERS THEN dbms_output.put_line (‘Kod: ‘ || sqlcode); dbms_output.put_line (‘Komunikat: ‘ || sqlerrm); dbms_output.put_line (‘Wyjatek podniesiony: ‘ || dbms_utility.format_error_backtrace); END;

17

Obsługa wyjątków

• Instrukcja RAISE umożliwia podniesienie wyjątku ręcznie - przez programistę.

• Na przykład poniższa instrukcja podnosi wyjątek NO_DATA_FOUND:

RAISE NO_DATA_FOUND;

18

Obsługa wyjątków

• Instrukcja RAISE bez nazwy wyjątku, użyta w bloku obsługi wyjątków podnosi wyjątek, który wywołał blok obsługi wyjątków

EXCEPTION WHEN OTHERS THEN

Dbms_output.put_line(‘Tu był wyjątek, ale podniesiemy go dalej’);

RAISE;

END;

19

Wyjątki zdefiniowane przez użytkownika

• Wyjątki zdefiniowane przez użytkownika są elementem logiki programu

• Wyjątki definiuje się w sekcji deklaracji

• Wyjątki takie są podnoszone tylko za pomocą funkcji RAISE.

e_budzet_zostal_przekroczony EXCEPTION;

20

Wyjątki zdefiniowane przez użytkownika

• Przkład wykorzystania wyjątków zdefiniowanych przez użytkownika

DECLARE e_budzet_zostal_przekroczony EXCEPTION; BEGIN; -- IF ln_koszty>ln_budzet THEN RAISE e_budzet_zostal_przekroczony; END; … EXCEPTION WHEN e_budzet_zostal_przekroczony THEN ...

21

Propagacja obsługi wyjątku

• Gdy wyjątek nie jest obsługiwany przez sekcję obsługi wyjątków wykonywanego aktualnie bloku, jest propagowany do kolejnych bloków zewnętrznych, aż zostanie znaleziona odpowiednia sekcja obsługi wyjątków.

22

23

Propagacja obsługi wyjątku

BEGIN

…

BEGIN

c:=a/0;

EXCEPTION WHEN NO_DATA_FOUND THEN …

END;

…

EXCEPTION WHEN ZERO_DIVIDE THEN …

END;

24

Propagacja obsługi wyjątku

BEGIN … BEGIN …

EXCEPTION WHEN NO_DATA_FOUND THEN … END; … BEGIN …

EXCEPTION WHEN ZERO_DIVIDE THEN … END; … EXCEPTION WHEN NO_DATA_FOUND THEN … END;

25

Propagacja obsługi wyjątku

DECLARE … EXCEPTION WHEN NO_DATA_FOUND WHEN ZERO_DIVIDE END;

PROCEDURE sprawdz_samochod EXCEPTION WHEN INVALID_NUMBER

PROCEDURE sprawdz_podwozie EXCEPTION WHEN ZERO_DIVIDE

PROCEDURE sprawdz_kolo EXCEPTION WHEN TOO_MANY_ROWS WHEN INVALID_NUMBER

FUNCTION cisnienie_w_kole EXCEPTION WHEN INVALID_NUMBER END;

26

Spis obiektów bazodanowych

Obiekt Opis

Tabela Obiekt przechowujący dane

Perspektywa Obiekt reprezentujący zapytanie

Sekwencja Generator kolejnych liczb

Synonim Umożliwia nadawanie dodatkowych nazw obiektom

Indeks Przyspiesza wykonanie niektórych zapytań

Procedura składowana

Obiekt wykonujący pewne operacje

Funkcja składowana Obiekt wykonujący pewne operacje, zwracający wynik pracy

Pakiet składowany Kontener na procedury i funkcje

Wyzwalacz Kod uruchamiany podczas operacji DML

27

Tworzenie tabel

• Nazwy tabel i kolumn:

• Muszą zaczynać się od litery

• Muszą mieć długość od 1 do 30 znaków

• Dozwolonymi znakami są A–Z, a–z, 0–9, _, $, oraz #

• Małe i duże litery nie są rozróżniane

• Nie mogą być żadnym słowem kluczowym

• Nie mogą się dublować

28

Tworzenie tabel

• Tabelę tworzymy za pomocą instrukcji:

29

CREATE TABLE nazwa_tabeli ( kolumna typ [, kolumna typ [,...]] );

CREATE TABLE pracownicy ( id NUMBER, imie VARCHAR2(60), data_zatrudnienia DATE );

Na przykład:

Tworzenie tabel

• Domyślną wartość w kolumnie można zdefiniować wykorzystując słowo kluczowe DEFAULT:

30

CREATE TABLE pracownicy ( id NUMBER DEFAULT 1, imie VARCHAR2(60) DEFAULT ‘MARIUSZ’, data_zatrudnienia DATE DEFAULT SYSDATE);

Kopiowanie tabel

• Do kopiowania tabeli służy konstrukcja: CREATE TABLE AS SELECT, np.

CREATE TABLE pracownicy_kopia AS

SELECT * FROM pracownicy;

• Do tworzenia tabeli o strukturze identycznej z inną tabelą służy konstrukcja:

CREATE TABLE pracownicy_kopia AS

SELECT * FROM pracownicy WHERE 1=0;

31

Edycja tabel

• Instrukcja ALTER TABLE służy do modyfikacji tabeli. Za jej pomocą można:

– Dodać nową kolumnę

– Zmodyfikować kolumnę

– Usnąć kolumnę

32

Edycja tabel

• Dodawanie kolumny do tabeli

• Na przykład:

33

ALTER TABLE table ADD (column datatype [DEFAULT expr] [, column datatype]...);

ALTER TABLE pracownicy ADD (drugie_imie VARCHAR2(60));

Edycja tabel

• Modyfikowanie kolumny w tabeli

• Można modyfikować typ oraz wartość domyślną kolumny

34

ALTER TABLE table MODIFY (column datatype [DEFAULT expr] [, column datatype]...);

Edycja tabel

• Modyfikowanie kolumny – przykład zmiany typu kolumny

• Modyfikowanie kolumny – przykład zmiany wartości domyślnej kolumny

35

ALTER TABLE pracownicy MODIFY (drugie_imie VARCHAR2(160));

ALTER TABLE pracownicy MODIFY (drugie_imie DEFAULT ‘ADAM’);

Edycja tabel

• Modyfikacja domyślnej wartości kolumny nie wpływa na wartości już obecne w tej kolumnie.

• Zmiana typu danych jest możliwa, gdy wszystkie wartości w tej kolumnie są NULL

• Zawsze można zwiększyć precyzję wartości numerycznej lub wydłużyć kolumny tekstowe

36

Edycja tabel

• Usuwanie kolumny z tabeli

• Na przykład:

37

ALTER TABLE nazwa_tabeli DROP (nazwa_kolumny);

ALTER TABLE pracownicy DROP (drugie_imie);

Zmiana nazwy tabeli

• Usuwanie kolumny z tabeli

• Na przykład:

38

RENAME nazwa_tabeli TO nowa_nazwa_tabeli;

RENAME pracownicy TO mistrzowie;

Czyszczenie tabel

• Za pomocą instrukcji TRUNCATE TABLE usuwamy wszystkie dane z tabeli i zwalniamy zajmowany przez nie obszar

• Po wykonaniu tej operacji tabela dalej istnieje, ale jest pusta

39

TRUNCATE TABLE nazwa_tabeli;

TRUNCATE TABLE mistrzowie;

Usuwanie tabel

• Tabelę usuwamy poleceniem DROP TABLE

• Na przykład:

40

DROP TABLE nazwa_tabeli;

DROP TABLE mistrzowie;

Tabele - podsumowanie

• Poznaliśmy 7 instrukcji do tworzenia modyfikacji i usuwania tabel:

41

Instrukcja Zadanie

CREATE TABLE Tworzenie tabeli

ALTER TABLE Modyfikacja struktury tabeli

RENAME Zmiana nazwy tabeli

TRUNCATE Czyszczenie tabeli

DROP TABLE Usuwanie tabeli

Perspektywy

42

• Perspektywy

– Reprezentują podzbiór danych z bazy danych

– Nie przechowują danych

– Bazują na tabelach lub widokach

– Za ich pomocą można pobierać i modyfikować dane w tabelach

Perspektywy

Imie Pobory Data_zatrudnienia

Adam 2600 2000-01-01

Bartosz 4200 2005-09-15

Beata 3800 2007-08-01

Celina 6000 2003-05-01

43

Tabela PRACOWNICY

Perspektywa PRACOWNICY_NA_LITERE_B

Imie Zatrudniony

Bartosz 2005-09-15

Beata 2007-08-01

Perspektywy

• Perspektywy służą do:

– Upraszczania skomplikowanych zapytań

– Ograniczania dostępu do danych

– Uniezależnienia aplikacji od struktury bazy danych

44

Perspektywy

• Instrukcja tworzenia perspektywy

45

CREATE [OR REPLACE] VIEW nazwa_widoku [(alias[, alias]...)] AS zapytanie;

Perspektywy

• Tworzenie perspektywy – przykład

46

CREATE OR REPLACE VIEW PRACOWNICY_NA_LITERE_B AS SELECT imie, data_zatrudnienia AS zatrudniony FROM pracownicy WHERE imie LIKE ‘B%’;

• Pobieranie danych z perspektywy – przykład

SELECT * FROM pracownicy_na_litere_b;

Perspektywy

47

Imie Zatrudniony

Bartosz 2005-09-15

Beata 2007-08-01

Perspektywy

• Tworzenie perspektywy, wykorzystanie aliasów – przykład

48

CREATE OR REPLACE VIEW PRACOWNICY_NA_LITERE_B (imie, zatrudniony) AS SELECT imie, data_zatrudnienia FROM pracownicy WHERE imie LIKE ‘B%’ [WITH READ ONLY];

Perspektywy

• Tworzenie perspektywy wykorzystującej dwie tabele

49

CREATE OR REPLACE VIEW PRACOWNICY_NA_LITERE_B AS SELECT p.imie, p.data_zatrudnienia, d.nazwa_dzialu FROM pracownicy p JOIN dzialy d ON d.dzial_id = p.dzial_id WHERE imie LIKE ‘B%’;

Perspektywy

• Można wykonywać operacje DML (INSERT, UPDATE, DELETE) na perspektywach pod warunkiem, że: – Nie wykorzystują konstrukcji DISTINCT ani GROUP BY – Nie wykorzystują funkcji agregujących – SUM, COUNT,

MIN, MAX etc – Nie wykorzystują pseudokolumny ROWNUM – Modyfikowane kolumny nie są oparte na funkcjach

SQL lub PL/SQL – W przypadku perspektyw zawierających łączenie kilku

tabel obowiązują dodatkowe kryteria

50

Perspektywy

• Wszystkie operacje DML wykonane na perspektywie zmieniają jedną tabelę, na której ta perspektywa jest oparta

51

UPDATE pracownicy_na_litere_b SET zatrudniony = TRUNC(SYSDATE)

Imie Pobory Data_zatrudnienia

Adam 2600 2000-01-01

Bartosz 4200 2012-03-15

Beata 3800 2012-03-15

Celina 6000 2003-05-01

Perspektywy

• Za pomocą wyrażenia WITH READ ONLY uniemożliwimy operacje DML na perspektywie

52

CREATE OR REPLACE VIEW PRACOWNICY_NA_LITERE_B AS SELECT imie, data_zatrudnienia AS zatrudniony FROM pracownicy WHERE imie LIKE ‘B%’ WITH READ ONLY;

Perspektywy

• Perspektywy można usuwać wykorzystując instrukcję DROP VIEW

• Usuwając perspektywę nie usuwamy żadnych danych

53

DROP VIEW pracownicy_na_litere_b;

Perspektywy - podsumowanie

• Nauczyliśmy się:

– Tworzyć perspektywy

– Wykorzystywać dyrektywę READ ONLY

– Wykonywać operacje DML na perspektywach

– Usuwać perspektywę

54

Sekwencje

• Sekwencje służą do generowania kolejnych liczb naturalnych

• Wykorzystywane są do tworzenia unikatowych identyfikatorów

• Są bezpieczne do wykorzystania przez równoległe wątki, żaden z wątków nie pobierze tej samej liczby

55

Sekwencje

• Składnia tworzenia sekwencji:

56

CREATE SEQUENCE nazwa_sekwencji [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];

Sekwencje

• Tworzenie sekwencji - przykład:

57

CREATE SEQUENCE pracownik_id_seq INCREMENT BY 2 START WITH 10 MAXVALUE 9999 NOCACHE NOCYCLE;

Sekwencje

• Wartości z sekwencji odczytujemy za pomocą pseudokolumn NEXTVAL i CURRVAL

– Pseudokolumna NEXTVAL pobiera kolejną wartość sekwencji

– Pseudokolumna CURRVAL pobiera ostatnią wartość sekwencji pobraną w sesji

– Próba pobrania wartości CURRVAL bez wcześniejszego pobrania wartości NEXTVAL kończy się wyjątkiem

58

Sekwencje

• Pobieranie wartości z sekwencji - przykłady

59

SELECT pracownik_id_seq.NEXTVAL FROM DUAL;

SELECT pracownik_id_seq.CURRVAL FROM DUAL;

INSERT INTO pracownicy VALUES( pracownik_id_seq.NEXTVAL, …)

Sekwencje

• Pobieranie wartości z sekwencji - przykłady

60

INSERT INTO faktury(faktura_id, … VALUES (faktura_id_seq.NEXTVAL, ….); INSERT INTO linie_faktur (linia_faktury_id, faktura_id, ….) VALUES (linia_faktury_id_seq.NEXTVAL, faktura_id_seq.CURRVAL …)

Sekwencje

• Sekwencję można usunąć wykorzystując instrukcję DROP SEQUENCE:

61

DROP SEQUENCE nazwa_sekwencji;

Sekwencje - podsumowanie

• Nauczyliśmy się:

– Tworzyć sekwencje

– Pobierać wartości z sekwencji

– Usuwać sekwencje

62

Wyzwalacze

INSERT INTO EMPLOYEES VALUES

(100, 'Steven', 'King', 'SKING', '515.123.4567', TO_DATE('06/17/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'AD_PRES', 24000, 90);

63

Wyzwalacze (Trigger)

• Wyzwalacz jest nazwanym blokiem PL/SQL skojarzonym z tabelą

• Jest blokiem składowanym w bazie danych

• Jest uruchamiany automatycznie po instrukcji INSERT, UPDATE lub DELETE na tabeli

CREATE OR REPLACE TRIGGER nazwa_wyzwalacza BEFORE INSERT ON employees BEGIN … END;

64

Wyzwalacze

INSERT, UPDATE lub DELETE

TRIGGER …………… …………… …………… ……………

65

Wyzwalacze (Trigger)

• Wyzwalacze mogą być zdefiniowane, aby uruchamiać się na zdarzenia:

– INSERT

– UPDATE

– DELETE

CREATE OR REPLACE TRIGGER nazwa_wyzwalacza BEFORE INSERT OR UPDATE OR DELETE ON employees BEGIN … END;

66

Wyzwalacze (Trigger)

• Wyzwalacze mogą być zdefiniowane, aby uruchamiać się przed lub po zdarzeniu:

- BEFORE -AFTER

CREATE OR REPLACE

TRIGGER nazwa BEFORE INSERT ON employees

CREATE OR REPLACE TRIGGER nazwa AFTER INSERT ON employees

67

Wyzwalacze (Trigger)

• Wyzwalacze mogą być zdefiniowane, aby uruchamiać się raz dla instrukcji lub dla każdego wiersza, którego instrukcja dotyczy.

- STATEMENT -ROW CREATE OR REPLACE

TRIGGER nazwa BEFORE INSERT ON employees BEGIN

CREATE OR REPLACE TRIGGER nazwa BEFORE INSERT ON employees FOR EACH ROW BEGIN

68

Wyzwalacze (Trigger)

BEFORE STATEMENT

AFTER STATEMENT

BEFORE ROW

AFTER ROW

69

Kolejność wyzwalaczy

IMIĘ POBORY

ANDRZEJ 100

ANNA 110

BEATA 120

UPDATE PRACOWNICY SET POBORY = 200;

70

Kolejność wyzwalaczy

IMIĘ POBORY

ANDRZEJ 100

ANNA 110

BEATA 120

UPDATE PRACOWNICY SET POBORY = 200;

1 BEFORE STATEMENT

2 BEFORE ROW

4 BEFORE ROW

6 BEFORE ROW

3 AFTER ROW

5 AFTER ROW

7 AFTER ROW

8 AFTER STATEMENT

71

Wyzwalacze (Trigger)

CREATE OR REPLACE TRIGGER nazwa_wyzwalacza

BEFORE INSERT OR UPDATE OR DELETE ON employees

BEGIN

IF DELETING THEN …

IF INSERTING THEN …

IF UPDATING THEN …

END;

Wykorzystanie instrukcji DELETING INSERTING UPDATING

72

Wyzwalacze (Trigger)

• Pseudokolumny :NEW i :OLD umożliwiają odczytanie wartości kolumn rekordu sprzed i po zmianie

• Mogą być wykorzystywane tylko w wyzwalaczach BEFORE - FOR EACH ROW i AFTER - FOR EACH ROW

• W wyzwalaczach BEFORE - FOR EACH ROW pseudokolumna :NEW umożliwia zmianę wartości zapisywanej do tabeli

73

Wyzwalacze (Trigger)

Operacja Wartość dla OLD Wartość dla NEW

Wstawianie (INSERT) NULL Wartość wstawiana

Aktualizacja (UPDATE) Wartość przed aktualizacją Wartość po aktualizacji

Usuwanie (DELETE) Wartość przed usunięciem NULL

Wartości w rekordach :NEW oraz :OLD

74

Wyzwalacze (Trigger)

CREATE OR REPLACE TRIGGER trigger_name

AFTER DELETE OR INSERT OR UPDATE ON PRACOWNICY

FOR EACH ROW

BEGIN

INSERT INTO log_table

VALUES (USER, SYSDATE, :OLD.salary, :NEW.salary);

END;

Wykorzystanie pseudokolumn :NEW i :OLD

75

Wyzwalacze (Trigger)

CREATE OR REPLACE TRIGGER trigger_name

BEFORE UPDATE ON pracownicy

FOR EACH ROW

BEGIN

:NEW.imie := UPPER(:NEW.imie);

END;

Wykorzystanie pseudokolumny :NEW do zmiany wartości kolumny

76

Wyzwalacze (Trigger)

INSERT, UPDATE lub DELETE

TRIGGER …………… …………… …………… ……………

77