PL/SQLmst.webd.pl/_sggw/plsql/wyk_ad_2.pdf · łędy PL/SQL •Naprawa błędów składni –W...
Transcript of PL/SQLmst.webd.pl/_sggw/plsql/wyk_ad_2.pdf · łędy PL/SQL •Naprawa błędów składni –W...
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