PL/SQL

Część 1 – Bloki PL/SQL

Piotr Medoń

Cele

• Zapoznanie się z podstawowymi typami PL/SQL

• Zapoznanie się z blokiem PL/SQL

• Zapoznanie się z instrukcjami sterującymi wykonaniem

2

Blok PL/SQL

• Struktura bloku PL/SQL

DECLARE deklaracja zmiennych BEGIN instrukcje PL/SQL EXCEPTION obsługa wyjątków END;

Elementy wymagalne

3

Blok PL/SQL

• Blok „Hello World”

BEGIN DBMS_OUTPUT.put_line(‘Hello World!’); END;

4

Blok PL/SQL

• Bloki PL/SQL można zagnieżdżać

DECLARE … BEGIN .. BEGIN DECLARE … BEGIN .. END; .. END; .. END;

5

Blok PL/SQL

• Deklarowanie zmiennych wykonujemy w sekcji deklaracji zmiennych.

DECLARE ln_liczba NUMBER; ls_tekst VARCHAR2(200); BEGIN NULL; END;

6

Zmienne

• Zmienne służą do chwilowego przechowywania danych

DECLARE ln_liczba NUMBER; ls_tekst VARCHAR2(200); BEGIN NULL; END;

7

Zmienne

DECLARE ls_tekst VARCHAR2(200); BEGIN ls_tekst := ‘Ala ma kota’; ls_tekst := ‘Tosia ma widelec’; dbms_output.put_line (ls_tekst); END;

8

Zmienne

DECLARE ls_tekst VARCHAR2(200); ls_tekst1 VARCHAR2(200); BEGIN ls_tekst := ‘Ala’; ls_tekst1 := ls_tekst || ‘ ma kota’ dbms_output.put_line (ls_tekst); END;

9

Zmienne

--PONIŻSZY KOD ZAKOŃCZY SIĘ BŁĘDEM DECLARE ls_tekst VARCHAR2(200); BEGIN ls_tekst := ‘Ala’; ls_tekst1 := ls_tekst || ‘ ma kota’ dbms_output.put_line (ls_tekst); END;

10

Zmienne

DECLARE ln_number NUMBER; BEGIN ln_number :=12; ln_number := ln_number + 15; dbms_output.put_line (ln_number); END;

11

Zmienne

DECLARE ln_number NUMBER; ln_number1 NUMBER; BEGIN ln_number := 12; ln_number1 := ln_number + 4; ln_number1 := ln_number + 2; ln_number1 := ln_number1 * 2; dbms_output.put_line (ln_number); END;

12

Podstawowe typy danych

• Tekstowy (VARCHAR2)

• Numeryczny stałoprzecinkowy oraz zmiennoprzecinkowy (NUMBER)

• Data (DATE)

13

Podstawowe typy danych

• VARCHAR2

– Przechowuje dane tekstowe do 4000 znaków

– Wartości w różnych zmiennych mogą mieć różną ilość znaków

Deklaracja:

nazwa_zmiennej VARCHAR2(maksymalna długość);

np.

IMIE VARCHAR2(80);

14

Podstawowe typy danych

• VARCHAR2 • Maksymalna długość zmiennej tekstowej zawsze musi być

określona podczas deklaracji • Tekst w bazie Oracle zapisujemy w apostrofach: 'Ala ma

kota' • Aby włączyć znak apostrofu do tekstu, należy wykorzystać

konstrukcję podwójnego apostrofu: 'Tytuł tej książki to ''Pan Tadeusz''' • Każdy podwójny apostrof powyższego tekstu zamieni się na

pojedynczy apostrof. • Baza Oracle nie rozpoznaje pustego ciągu '' od wartości

NULL

15

Podstawowe typy danych

• NUMBER (zmiennoprzecinkowy)

– Przechowuje dane numeryczne do 38 znaczących liczb

– Wartości w różnych rekordach mogą mieć różną ilość znaków

Deklaracja:

nazwa_zmiennej NUMBER;

np.

KWOTA NUMBER;

16

Podstawowe typy danych

• NUMBER (stałoprzecinkowy)

– Przechowuje dane numeryczne o precyzji zadanej w trakcie deklaracji

Deklaracja: nazwa_kolumny NUMBER(całkowita ilość cyfr, ilość miejsc po przecinku) np. KWOTA NUMBER(12,2), oznacza: 10 cyfr przed przecinkiem, 2 cyfry po przecinku

17

Podstawowe typy danych

• NUMBER (stałoprzecinkowy) c.d.

– Domyślna ilość miejsc po przecinku to 0

np.

NUMBER(10), oznacza:

10 cyfr przed przecinkiem, 0 cyfr po przecinku,

czyli liczby całkowite

18

Podstawowe typy danych

• DATE

– Przechowuje datę i czas z precyzją do sekund

Deklaracja:

nazwa_kolumny DATE

np.

DATA_ZATRUDNIENIA DATE,

19

Podstawowe typy danych

• DATE

– Domyślny format daty jest parametrem sesji

– Stałą datę możemy zapisać w postaci:

DATE ‘2012-03-01’

TO_DATE(‘2012-03-01 15:13:12’,

‘YYYY-MM-DD HH24:MI:SS’)

20

Arytmetyka dat

• Gdy dodajemy do daty liczbę, Oracle traktuje liczbę jak ilość dni

DECLARE ld_date DATE := TRUNC(SYSDATE); BEGIN ld_date := ld_date + 1; dbms_output.put_line(‘Jutro jest ‘ || TO_CHAR(ld_date, ‘DD-MM-YYYY’); COMMIT; END;

21

Arytmetyka dat

• Gdy odejmujemy daty od siebie, Oracle zwraca ilość dni pomiędzy datami:

DECLARE ld_birth DATE := DATE ‘1970-01-01’; BEGIN dbms_output.put_line(‘Przeżyłeś już ‘ || TRUNC(SYSDATE) – ld_birth || ‘ dni’); COMMIT; END;

22

Arytmetyka dat

• Możemy dodać do daty wartość niecałkowitą. Część ułamkowa będzie potraktowana jako część dnia

BEGIN dbms_output.put_line(‘Za godzinę będzie godzina ‘ || TO_CHAR( SYSDATE +1/24), ‘hh24:mi’); COMMIT; END;

23

Arytmetyka dat

• Stosuj odpowiednie metody ADD_MONTHS oraz MONTHS_BETWEEN aby przeprowadzać operacje na miesiącach i latach

ld_dzis := TRUNC(SYSDATE); ld_za_miesiac := ADD_MONTHS(ld_dzis, 1); ld_za_rok := ADD_MONTHS(ld_dzis, 12); ln_ilosc_miesiecy := MONTHS_BETWEEN (ld_za_miesiac, ld_za_rok);

24

Blok PL/SQL

• Domyślnie po zadeklarowaniu zmienne przyjmują wartość NULL.

• Możemy zmienić inicjalne wartości za pomocą operatora ‘:=‘

DECLARE ln_liczba NUMBER := 10; ls_tekst VARCHAR2(200) := ‘Test’; ld_data DATE := DATE ‘2012-03-01’; BEGIN NULL; END;

25

Zmienne cd

DECLARE ln_liczba NUMBER := 11; ln_liczba1 NUMBER := 16; BEGIN ln_liczba1 :=10; dbms_output.put_line (ln_liczba + ln_liczba1); END;

26

Zmienne cd

DECLARE ln_wiek NUMBER := 11; ls_imie VARCHAR2(20) := ‘Ala’; BEGIN ln_wiek :=ln_wiek + 1; dbms_output.put_line (ls_imie || ‘ma’ || TO_CHAR(ln_wiek) || ‘lat’); END;

27

Blok PL/SQL

• W bloku PL/SQL można wykonywać instrukcje INSERT/UPDATE/DELETE oraz ROLLBACK/COMMIT

BEGIN INSERT INTO nazwa_tabeli VALUES (‘ABCABC’, sysdate, 1); COMMIT; END;

28

Blok PL/SQL

• W bloku PL/SQL można wykonywać instrukcje SELECT, na przykład wykorzystując klauzulę INTO <zmienne> przed słowem FROM

DECLARE ld_data DATE; ld_za24godziny DATE; BEGIN SELECT SYSDATE, SYSDATE+1 INTO ld_data, ld_za24godziny FROM dual; END;

29

Komentarze

• Fragmenty kodu nie wykonywane przez interpreter bazy danych

• Komentarze zapisujemy: – od znacznika -- (podwójny myślnik) do końca linii – wewnątrz znaczników /* oraz */

null; -- to jest instrukcja, która nic nie robi /* To jest pierwsza linia komentarza To jest druga linia komentarza */

30

Operacje arytmetyczne

Operacja Operator Przykład Wynik

Dodawanie + 2+3 5

Odejmowanie - 4-3 1

Mnożenie * 5*6 30

Dzielenie / 8/4 2

Negacja - -4 -4

• Gdy jeden z parametrów operacji arytmetycznych ma wartość NULL, operacja również zwróci NULL, np:

NULL+6 zwróci NULL

31

Operacja przypisania

• Służy do przypisania wartości zmiennej.

• Operatorem przypisania jest :=

ln_liczba := 5+4;

ls_tekst := ‘Test’;

ls_tekst2 := ‘Te’ || ‘st’;

ld_data := SYSDATE;

32

Instrukcje sterowania wykonaniem

• Instrukcja warunkowa – Wykonuje fragment kodu, gdy zadany warunek jest

spełniony

– Warunek może być spełniony (TRUE), niespełniony (FALSE) lub nieokreślony

– Gdy warunek jest nieokreślony, instrukcja warunkowa uznaje go za niespełniony

IF warunek THEN instrukcje wykonywane warunkowo; END IF;

33

Instrukcje sterowania wykonaniem

• Rozszerzona instrukcja warunkowa

IF warunek THEN instrukcje wykonywane warunkowo; ELSE instrukcje wykonywane, gdy warunek niespełniony END IF;

34

Instrukcje sterowania wykonaniem

• Rozszerzona instrukcja warunkowa

IF warunek THEN instrukcje wykonywane warunkowo; ELSIF warunek2 THEN instrukcje wykonywane warunkowo2; ELSE instrukcje wykonywane, gdy żaden z warunków niespełniony END IF;

35

Przykład IF/ELSIF/ELSE/END IF

DECLARE ls_dzien_tygodnia varchar2 (1); BEGIN ls_dzien_tygodnia := TO_CHAR (SYSDATE, 'D'); IF ls_dzien_tygodnia = ‘7' THEN DBMS_OUTPUT.put_line ('Dzis jest sobota'); ELSIF ls_dzien_tygodnia = ‘1' THEN DBMS_OUTPUT.put_line ('Dzis jest niedziela'); ELSE DBMS_OUTPUT.put_line ('Dzis nie ma weekendu‘); END IF; END;

36

Zagnieżdżone instrukcje warunkowe

IF warunek1 THEN IF warunek2 THEN IF warunek3 THEN instrukcje1; ELSE instrukcje2; END IF; ELSIF warunek4 instrukcje3; END IF; ELSE instrukcje4; END IF;

37

Operatory porównań

• Poniższe operatory zwracają wartość określoną TRUE lub FALSE, gdy oba argumenty nie są wartościami NULL

• Gdy jeden z argumentów jest NULL, operator zwróci wartość nieokreślony

Warunek Operator Przykład

równość = a=b

większe od mniejsze od

> <

a>b a<b

większe równe mniejsze równe

>= <=

a>=b a<=b

nierówność != <>

a!=b a<>b

pomiędzy BETWEEN .. AND a BETWEEN b AND c

38

Warunki

• Wszystkie operatory są przeciążone i działają na typach dat, tekstowych oraz numerycznych

Typ danych Warunek Prawda, gdy

Liczby x>y x jest większe od y

Tekst a>b a jest dalej w alfabecie od b

Daty d1>d2 d1 jest późniejsza niż d2

39

Warunki

• Przykłady porównań Warunek Wynik

3<=5 Prawda

‘g’>’z’ Fałsz

‘Ula’<=‘Ula’ Prawda

‘Test123’>’Test’ Prawda

DATE ‘01-01-2011’ > DATE ‘01-01-2013’ Fałsz

2>NULL Nieokreślony

NULL=NULL Nieokreślony

‘b’<>NULL Nieokreślony

40

Warunki

• Warunki sprawdzające wartość NULL

• Warunki te zawsze zwracają TRUE albo FALSE

IF ld_data_konca IS NOT NULL THEN

dbms_output.put_line(‘Projekt skonczony’);

END IF;

Warunek Operator Przykład

Czy zmienna ma wartość NULL

IS NULL a IS NULL

Czy zmienna nie ma wartości NULL

IS NOT NULL a IS NOT NULL

41

Złożone warunki

• Złożone warunki tworzymy wykorzystując operatory logiczne NOT, AND, OR

• Kolejność wykonywania operatorów jest następująca: NOT, AND, OR

przykład:

IF stan=‘panna’ AND uroda=TRUE OR stan=‘zamężna’ OR stan=‘wdowa’ AND

NOT oszczednosci<10000 THEN …

42

Operacje logiczne w PL/SQL

Operacja Operator Przykład Wynik

Koniunkcja (i) AND TRUE AND TRUE TRUE AND FALSE FALSE AND FALSE TRUE AND NULL FALSE AND NULL NULL AND NULL

TRUE FALSE FALSE NULL FALSE NULL

Alternatywa (lub) OR TRUE OR TRUE TRUE OR FALSE FALSE OR FALSE TRUE OR NULL FALSE OR NULL NULL OR NULL

TRUE TRUE FALSE TRUE NULL NULL

Negacja (nie) NOT NOT TRUE NOT FALSE NOT NULL

FALSE TRUE NULL

43

Instrukcja warunkowa

DECLARE ln_liczba NUMBER := 5; BEGIN IF ln_liczba = 5 THEN dbms_output.put_line (‘Mamy piątkę!’); END IF; END;

44

Instrukcja warunkowa

DECLARE ln_liczba NUMBER := 2; BEGIN ln_liczba := ln_liczba + 2; IF ln_liczba = 5 THEN dbms_output.put_line (‘Mamy piątkę!’); END IF; END;

45

Instrukcja warunkowa

DECLARE ln_wiek NUMBER := 11; ls_akcja VARCHAR2(20):= ‘Urodziny’; BEGIN IF ls_akcja = ‘Urodziny’ THEN ln_wiek :=ln_wiek + 1; END IF; dbms_output.put_line (‘Ola ma’ || TO_CHAR(ln_wiek) || ‘ lat’); END;

46

Instrukcje sterowania wykonaniem

• Pętla LOOP/END LOOP

– Pętla powtarza instrukcje zawarte pomiędzy instrukcjami LOOP oraz END LOOP

LOOP instrukcje; END LOOP;

47

Instrukcje sterowania wykonaniem

• Warunki końca pętli LOOP/END LOOP:

– instrukcja EXIT

LOOP instrukcje1; IF warunek_końca1 THEN EXIT; END IF; instrukcje2; EXIT WHEN warunek_końca2; instrukcje3; END LOOP;

48

Instrukcje sterowania wykonaniem

• Przykład pętli LOOP/END LOOP:

declare ln_liczba NUMBER := 1; begin loop dbms_output.put_line(ln_liczba); exit when ln_liczba > 10; ln_liczba := ln_liczba + 1; end loop; end;

49

Instrukcje sterowania wykonaniem

• Numeryczna pętla FOR

– Pętla powtarza instrukcje zawarte pomiędzy instrukcjami LOOP oraz END LOOP dla każdej wartości zmienne spomiędzy zadanego przydziału

– wartość początkowa musi być mniejsza niż wartość końcowa, w przeciwnym wypadku instrukcje nie wykonają się ani razu

FOR zmienna IN wartość_początkowa..wartość_końcowa LOOP instrukcje; END LOOP;

50

Instrukcje sterowania wykonaniem

• Przykład numerycznej pętli FOR

BEGIN FOR i IN 1 .. 10 LOOP DBMS_OUTPUT.put_line ( 'Petla wykonuje się po raz ' || TO_CHAR(i) ); END LOOP; END;

51

Instrukcje sterowania wykonaniem

• Przykład numerycznej pętli FOR

DECLARE ln_ilosc NUMBER := 15; BEGIN FOR i IN 1 .. ln_ilosc LOOP DBMS_OUTPUT.put_line ( 'Petla wykonuje się po raz ' || TO_CHAR(i) ); END LOOP; END;

52

Instrukcje sterowania wykonaniem

• Przykład numerycznej pętli FOR

DECLARE ln_od NUMBER := 10; ln_do NUMBER := 15; BEGIN FOR i IN ln_od..ln_do LOOP DBMS_OUTPUT.put_line (TO_CHAR(i)); END LOOP; END;

53

Instrukcje sterowania wykonaniem

• Numeryczna pętla FOR

– za pomocą słowa REVERSE możemy zmienić kolejność iterowania na odwrotną.

– również w tym przypadku wartość początkowa musi być mniejsza niż wartość końcowa

FOR zmienna IN REVERSE wartość_początkowa..wartość_końcowa LOOP instrukcje; END LOOP;

54

Instrukcje sterowania wykonaniem

• Przykład numerycznej pętli FOR iterowanej od końca

BEGIN FOR i IN REVERSE 1 .. 5 LOOP DBMS_OUTPUT.put_line ( 'Pozostalo jeszcze ' || i || ' godzin do konca dnia' ); END LOOP; END;

55

Instrukcje sterowania wykonaniem

• Instrukcja CONTINUE

• Powoduje przerwanie bieżącej iteracji pętli i uruchomienie kolejnej iteracji pętli

LOOP instrukcje1; IF warunek1 THEN CONTINUE; END IF; instrukcje2; EXIT WHEN warunek_końca2; END LOOP;

56

Instrukcje sterowania wykonaniem

• Przykład wykorzystania instrukcji CONTINUE

BEGIN FOR i IN 1 .. 10 LOOP CONTINUE WHEN MOD (i, 2) = 1; DBMS_OUTPUT.put_line (TO_CHAR (i) || ' jest parzyste'); END LOOP; END; /

57

Błędy PL/SQL

• Błędy składni

– Blok danych jest napisany niezgodnie ze składnią języka PL/SQL

– Błąd jest generowany przed wykonaniem pierwszej instrukcji

58

Błędy PL/SQL

• Naprawa błędów składni – W przypadku błędu składni interpreter zwraca numery

linii, w których są błędy

– Jeżeli stwierdzimy, ze linia ta jest poprawna należy sprawdzić linię wcześniejszą

– Jeden błąd składni może wygenerować kolejne błędy, dlatego warto naprawić tylko pierwszy błąd i spróbować uruchomić blok ponownie

– Jeżeli mimo powyższych operacji nie pozbyliśmy się błędu, należy zakomentować fragmenty kodu generujące błędy

59

Błędy PL/SQL

• Błędy czasu uruchomienia (logiczne)

– Podczas wykonywania programu interpreter natknął się na błąd

– Interpreter podnosi wyjątek związany z błędem

60

Błędy PL/SQL

• Naprawa błędów logicznych

– Baza danych Oracle umożliwia debug’owanie programów

– Stosuj DBMS_OUTPUT.PUT_LINE, aby wypisać wartości zmiennych

61