Plan wykładu PL/SQL PL/SQL - historia

TWORZENIE APLIKACJI BAZODANOWYCH

Małgorzata Krętowska

Wydział Informatyki

Politechnika Białostocka

Wykład 2: Wprowadzenie do PL/SQL: bloki

anonimowe, zmienne, kursory

Plan wykładu2

� Wprowadzenie do PL/SQL

� Bloki

� Podstawowe składowe języka

� Zmienne i stałe

� Kursory

PL/SQL

� PL/SQL (Procedural Language SQL)

� Proceduralne (a czasem obiektowe) rozszerzenieprogramistyczne języka SQL, stworzone,udostępnione przez Oracle i przeznaczone doobsługi narzędzi tej firmy

� Wywodzi się z języka ADA

� Jest chroniony prawami autorskimi,

� Jest językiem trzeciej generacji 3GL,

3

PL/SQL - historia

� PL/SQL 1.0 wprowadzono w 1991 roku, wersja była ograniczona i brakowało w niej wielu funkcji, wypuszczona z wersją serwera bazodanowego 6.0,

� Kolejna wersja 2.3 udostępniała obsługę procedur i funkcji składowanych,

� Wersja 8.0 – wprowadzenie modelu obiektowo-relacyjnego,

� Wersje 8.1, 9.0, 9.2, 10.0 to kolejne udoskonalenia języka,

� Najnowsza wersja to 11.0

4

Bloki

� Podstawowa jednostka w PL/SQL

� Program składa się przynajmniej z jednego bloku

� Bloki mogą być zagnieżdżone

� Bloki mogą być wykonywane tylko raz, bez zapisywania (bloki anonimowe)

� Bloki można zapisywać w bazie w celu ich późniejszego wykorzystania (bloki nazwane)

� Bloki obsługują wszystkie instrukcje DML, a także DDL

5

Struktura bloku

[DECLARE ]

deklaracje zmiennych, wyjątki

BEGIN

wyrażenia SQL i PL/SQL

[EXCEPTION ]

obsługa wyjątków

END;

6

[] – opcjonalnieBlok nie może być pusty.

Sekcja deklaracji

� Jest to sekcja opcjonalna

� Umieszczamy w niej zmienne i ich typy, stałe, kursory,wyjątki definiowane przez użytkownika, wszystko todo czego odwołujemy się w kolejnej sekcji bloku

� Możliwość dokonania inicjalizacji

� Przykład

DECLARE

v_zmienna NUMBER;

7

Sekcja wykonawcza

� Obowiązkowa

� Rozpoczyna się od słowa kluczowego BEGIN

� Musi posiadać co najmniej jedną instrukcję

� Może zawierać dowolną liczbę bloków PL/SQL, zagnieżdżonych

� Może zawierać polecenia SQL lub PL/SQL

8

Sekcja wyjątków

� Opcjonalna

� Rozpoczyna się od słowa kluczowego EXCEPTION

� Pozwala na przechwytywanie błędów, które wystąpiły w sekcji wykonawczej

9

Typy bloków

� Bloki anonimowe

� Bloki nazwane

� Funkcje

� Procedury

10

Bloki anonimowe

� Nie posiadają nazwy i nie są przechowywane w bazie danych

� Można w nich wywoływać inne programy, ale ich nigdzie nie można wywołać

� Są kompilowane za każdym razem gdy program jest uruchamiany

� Struktura bloku anonimowego – jak wcześniej.

11

Przykład

� SET SERVEROUTPUT ON; - na zewnątrz bloku anonimowego umożliwia wypisywanie komunikatów

� DBMS_OUTPUT.PUT_LINE (‘komunikat’); - wypisywanie komunikatu

� DBMS_OUTPUT.NEW_LINE; - przejście do nowej linii

12

Uruchamianie bloku13

Oracle SQL Developer: Uruchamianie: Run Script (F5); Wyniki: Script Output


Oracle SQL*Plus: Uruchamianie: znak „/” na końcu kodu


Oracle SQL*Plus: Jeżeli kod bloku jest zapisany w pliku (ze znakiem „/” na końcu):Uruchamianie: start ścieżka dostępu do pliku

@ ścieżka dostępu do pliku

Podstawowe składowe języka

� Identyfikatory

� Ograniczniki

� Literały

� Słowa zarezerwowane

� Komentarze

16

Identyfikatory

� Służą do nazywania zmiennych, kursorów, funkcji, procedur, etc.,

� Zasady tworzenia identyfikatorów:

� <30 znaków

� Rozpoczynają się literą

� Mogą zawierać znaki $,#,_ i cyfry (oprócz pierwszej pozycji),

� Nie mogą zawierać znaków przestankowych, odstępów i myślników,

� Nie mogą zawierać słów zarezerwowanych,

� Wyjątek to identyfikatory w cudzysłowach - można w nich stosować prawie wszystko.

17

Ograniczniki

� +,-,*,/ - operatory matematyczne

� >,<,<>,=,!= - operatory porównania

� --, /*, */ - komentarze

� <<, >> - etykiety

� % - atrybuty (TYPE, etc.)

� ’ – ogranicznik łańcuchów znaków

� ” – ogranicznik identyfikatorów

� := - przypisanie, inicjalizacja zmiennej

� || - łączenie łańcuchów znaków

18

Literały

� Wartości, które nie są reprezentowane przez identyfikatory, ani nie są wyliczane na podstawie innych wartości:

� Znakowe: np. ’abc’

� Liczbowe: 1, 2, 456

� Logiczne: TRUE, FALSE

� Związane z datą ’23-12-2013’

19

Deklaracja zmiennych i stałych

DECLAREv_zmienna NUMBER(5);v_nr NUMBER(3) NOT NULL := 10;c_stala CONSTANT NUMBER(2) DEFAULT 13;v_sprawdz BOOLEAN NOT NULL := TRUE;

20

identyfikator typ_danych [NOT NULL] [:=wart_pocz | DEFAULT wart_pocz];

Zmienne:

identyfikator CONSTANT typ_danych [:=wart_pocz | DEFAULT wart_pocz];

Stałe:

Typy zmiennych

� Typy skalarne – typ o pojedynczej wartości

� Znaki i łańcuchy znaków

� Liczby

� Typy logiczne

� Data i czas

� Typy złożone – typ złożony z kilku odrębnych wartości (np. rekord, kolekcja (tablice, listy) , instancja typu obiektowego)

� Wskaźniki – logiczny wskaźnik danej wartości lub kursora

� LOB – identyfikator typu wielkoobiektowego (ang. largeobject)

21

Typy znakowe

� CHAR ([(max długość)] – domyślnie 1; typ danych o stałej długości, pojemność określamy w bajtach: maksymalnie 32 767 bajtów w PL/SQL (w SQL –2000 bajtów)

� VARCHAR2 (max długość) - typ danych o zmiennej długości, maksymalnie 32 767 bajtów ( w SQL –4000 bajtów)

22

Typy liczbowe i logiczne

� NUMBER [(precyzja, skala)] – precyzja: wartości od 1 do 38, skala: -84 do 127, identyczny z typem NUMBER bazy danych

� Podtypy: REAL (63 cyfry), DOUBLE PRECISION (126 cyfr), FLOAT (126 cyfr)

� BINARY_DOUBLE, BINARY_FLOAT - od wersji Oracle 10g, typ zmiennoprzecinkowy o podwójnej (pojedynczej) precyzji

� PLS_INTEGER – zakres od -2 147 483 648 do 2 147 483 647. Potrzebuje mniej przestrzeni na dysku niż NUMBER; bardziej efektywne wyliczenia

� BOOLEAN – wartości TRUE, FALSE, NULL

23

Data i czas

� DATE – podstawowy typ daty i czasu; przechowuje rok, miesiąc, dzień, godzinę, minutę, sekundę

� TIMESTAMP [(precyzja)] – rozszerzenie typu DATE o ułamki sekund, w tym celu określa się precyzję 0-9, domyślnie 6

� Przechowywanie okresów czasów:

� INTERVAL YEAR [(precyzja)] TO MONTH – w latach i miesiącach

� INTERVAL DAY [(precyzja_dni)] TO SECOND

[(precyzja_ułamki_sekund)] – w dniach, minutach i sekundach

24

Typy zakotwiczone

v_imie VARCHAR2(15);

v_moje_imie v_imie%TYPE;

v_cena t_ksiazka.cena%TYPE;

ksiazki_record t_ksiazka%ROWTYPE

25

zmienna%TYPEtabela.kolumna%TYPEtabela%ROWTYPE

Instrukcje warunkowa 26

IF warunek THEN

instrukcja-1;

ELSE

instrukcja-2;

END IF;

IF warunek-1 THEN

instrukcja-1;

ELSIF warunek-2 THEN

instrukcja-2;

ELSE

instrukcja-3;

END IF;

IF warunek THEN

instrukcja-1;

END IF;

Instrukcja warunkowa

……...

IF v_cena > 100 THEN

RETURN (v_cena*1.1);

ELSIF v_cena >= 50 THEN


ELSE


END IF;

……….

27

Instrukcja CASE

� Od wersji 9i,

� Alternatywa dla instrukcji warunkowych IF upraszczająca składnię

28

CASE warunek

WHEN kryterium1 THEN operacje1; WHEN kryterium2 THEN operacje2;… [ELSE operacje;] END CASE;

Instrukcja CASE - przykład

DECLARE

selector NUMBER := 0;

BEGIN

CASE selector

WHEN 0 THEN

dbms_output.put_line('Case 0!');

WHEN 1 THEN

dbms_output.put_line('Case 1!');

ELSE

dbms_output.put_line('No match!');

END CASE;

END;

/

29

CASE z wyszukiwaniem

� Domyślnie poszukuje warunku prawdziwego (TRUE)

30

CASE [TRUE| FALSE]

WHEN warunek1 THEN operacje1; WHEN warunek2 THEN operacje2;… [ELSE operacje;] END CASE;

CASE z wyszukiwaniem - przykład

BEGIN

CASE

WHEN 1 = 2 THEN

dbms_output.put_line('Case [1 = 2]');

WHEN 2 = 2 THEN

dbms_output.put_line('Case [2 = 2]');

ELSE

dbms_output.put_line('No match');

END CASE;

END;

/

31

Wynik działania CASE

Pętla prosta32

LOOP

instrukcje;

EXIT [WHEN warunek];

END LOOP;

Przykład

DECLARE

counter NUMBER;

first BOOLEAN;

BEGIN

LOOP

IF NVL(counter,1) >= 1 THEN

IF NOT NVL(first,TRUE) THEN

counter := counter + 1;

ELSE

counter := 1;

first := FALSE;

END IF;

END IF;

dbms_output.put_line('Iteration ['||counter||']');

EXIT WHEN NOT counter < 3;

END LOOP;

END;

/

33

CONTINUE [WHEN]

� Od wersji 11g

� Dodatkowe sterowanie pętlą

� Działanie: natychmiast kończy aktualną iterację i przechodzi do pierwszego działania w pętli

34

CONTINUE

DECLARE

counter NUMBER;

first BOOLEAN;

BEGIN

LOOP

IF NVL(counter,1) >= 1 THEN

IF NOT NVL(first,TRUE) THEN

counter := counter + 1;

ELSE

counter := 1;

first := FALSE;

END IF;

END IF;

EXIT WHEN NOT counter < 3;

IF counter = 2 THEN CONTINUE;

ELSE dbms_output.put_line('Index ['||counter||'].');

END IF;

END LOOP;

END;

35

CONTINUE

IF counter = 2 THEN CONTINUE;

ELSE dbms_output.put_line('Index ['||counter||'].');

CONTINUE WHEN counter = 2;

dbms_output.put_line('Index ['||counter||'].');

36

Instrukcje FOR i WHILE37

WHILE warunek LOOP

instrukcje;

END LOOP;

FOR licznik IN [REVERSE] min..max LOOP

instrukcje;

END LOOP;

Instrukcja FOR - przykład

DECLARE

v_licznik NUMBER(1) :=0;

v_ostatni NUMBER(1);

v_min NUMBER(1) := 1;

v_max NUMBER(1) := 5;

BEGIN

FOR i IN v_min..v_max LOOP

v_licznik := v_licznik + 1;

v_ostatni := i;

END LOOP;

dbms_output.put_line(‘Ostatni indeks:’

|| TO_CHAR(v_ostatni) || ‘. Liczba petli:’

|| TO_CHAR(v_licznik));

END;

38

Nie trzeba deklarować zmiennej i

Instrukcja NULL

DECLARE

i NUMBER(2) :=0;

BEGIN

………

IF i > 10 THEN

dbms_output.put_line(‘i jest większe od 10’);

ELSE

NULL;

END IF;

END;

/

� NULL – oznacza brak akcji

39

Polecenie SELECT INTO

� SELECT INTO przekazuje wartości uzyskane z bazy (lista_zmiennych) do zmiennych występujących w klauzuli INTO

� Polenie musi zwrócić jeden wiersz

40

SELECT lista_zmiennych

INTO nazwa_zmiennej | nazwa_rekordu

FROM tabela

[ WHERE warunki];

SELECT INTO - przykład

DECLARE

bonus NUMBER(8,2);

BEGIN

SELECT salary * 0.10 INTO bonus

FROM employees

WHERE employee_id = 100;

DBMS_OUTPUT.PUT_LINE('bonus = ' ||

TO_CHAR(bonus));

END;

/

41

Pojęcie kursora

� Udostępnia dane zwrócone przez zapytanie

� Otwarcie kursora to pobranie części bieżących danych, co oznacza, że modyfikacje danych po jego otwarciu nie będą widoczne w zbiorze, który zwrócił kursor.

� Dwa typy kursorów

� Niejawne

� Jawne

42

Typy kursorów

� Deklarowane są dlawszystkich instrukcji DML oraz SELECT

� PL/SQL zarządzaautomatycznie kursoremniejawnym

� Parametry kursora: SQL%nazwa_parametru

� Deklarowane przezprogramistę

� Programista zarządza kursorem jawnym

� Parametry kursora: Nazwa_kursora%nazwa_parametru

Niejawne Jawne

43

Atrybuty kursora SQL

Nazwa atrybuty Opis

SQL%ROWCOUNT Liczba wierszy, których dotyczyła ostatnia instrukcja SQL

SQL%FOUND Atrybut logiczny, który przyjmuje wartość TRUE, gdyostatnia instrukcja dotyczyla jednego lub więcej wierszy

SQL%NOTFOUND Atrybut logiczny, który przyjmuje wartość TRUE, gdyostatnia instrukcja dotyczyła zerowej liczby wierszy

SQL%ISOPEN Zawsze przyjmuje wartość FALSE ponieważ PL/SQL zamyka kursory natychmiast po ich wykonaniu

44

Kursory niejawne - przykład

DECLARE

n NUMBER;

BEGIN

SELECT 1 INTO n FROM dual;

dbms_output.put_line('Selected ['||SQL%ROWCOUNT||']');

END;

====================================================================

BEGIN

UPDATE system_user

SET last_update_date = SYSDATE;

IF SQL%FOUND THEN

dbms_output.put_line('Updated ['||SQL%ROWCOUNT||']');

ELSE

dbms_output.put_line('Nothing updated!');

END IF;

END;

45

Kursory niejawne - przykład

DECLARE

v_usuniete VARCHAR2(30);

BEGIN

DELETE

FROM t_z_ksiazka

WHERE id_zamowienia = &par;

v_usuniete := TO_CHAR(SQL%ROWCOUNT)|| ‘rekordow

usunietych.’;

END;

46

Kursory jawne

CURSOR kursor (p_isbn VARCHAR(10)) IS SELECT * FROM t_ksiazkaWHERE isbn = p_isbn;

47

CURSOR nazwa_kursora [(parametr [, parametr] … )] IS instrukcja_select;

parametr ::= nazwa_parametru typ danych [{:= | DEFAULT wyrażenie}]

Sterowanie kursorami jawnymi48

DECLARE OPEN FETCH CLOSEPUSTY?

NIE

TAK

Kursory jawne49

OPEN nazwa_kursora [(parametr [, parametr] ...)];

FETCH nazwa_kursora INTO zmienna [ , zmienna ...];

CLOSE nazwa_kursora;

DECLARE ….. ;

Atrybuty kursora jawnego

Nazwa atrybuty Opis

%ROWCOUNT Liczba wierszy, sprawdzonych w kursorze w danymmomencie

%FOUND Atrybut logiczny, który przyjmuje wartość TRUE, jeśli

instrukcja FETCH zwróci wiersz

%NOTFOUND Atrybut logiczny, który przyjmuje wartość TRUE, jeśli ostatnia instrukcja FETCH nie zwrócila żadnego wiersza

%ISOPEN Atrybut logiczny, który przyjmuje wartość TRUE, jeśli kursor jest otwarty

50

Kursor jawny - przykład

DECLARE

CURSOR k_cursor IS SELECT * FROM t_z_ksiazka

WHERE isbn = 1;

k_record t_z_ksiazka%ROWTYPE;

v_ilosc t_z_ksiazka. ilosc %TYPE := 0;

BEGIN

OPEN k_cursor;

LOOP

FETCH k_cursor INTO k_record;

IF k_cursor%NOTFOUND THEN

EXIT;

END IF;

v_ilosc := v_ilosc + k_record.ilosc;

END LOOP;

dbms_output.put_line(k_cursor%ROWCOUNT);

dbms_output.put_line(v_ilosc);

CLOSE k_cursor;

END;

51

Pętla kursorowa FOR

Niejawne instrukcje OPEN, FETCH, CLOSE

zmienna nazwa_rekordu – zadeklarowana niejawnie

52

FOR nazwa_rekordu IN nazwa_kursora LOOP

instrukcja-1;

instrukcja-2;

…….

END LOOP;

Pętla kursorowa FOR - przykład

DECLARE

CURSOR k_cursor

(p_zam t_zamowienie.id_zamowienia%TYPE) IS

SELECT *

FROM t_z_ksiazka

WHERE id_zamowienia = p_zam;

BEGIN

FOR i IN k_cursor(1) LOOP

dbms_output.put_line(i.isbn);

END LOOP;

END;

53

Pętla kursorowa FOR- przykład

DECLARE

CURSOR a_cursor IS SELECT * FROM t_autor;

======== Metoda 1 ========================BEGIN

FOR i IN a_cursor LOOP

dbms_output.put_line(i.nazwisko);

END LOOP;

END;

======== Metoda 2 ========================BEGIN

FOR i IN (SELECT * FROM t_autor) LOOP

dbms_output.put_line(i.nazwisko);

END LOOP;

END;

54

Klauzula FOR UPDATE

� Służy do blokowania rekordów po otwarciu kursora� Rekordy są dostępne dla innych sesji, ale tylko w trybie

do odczytu� Gdy użyjemy NOWAIT, przy otwieraniu kursora

program zostanie zamknięty, jeśli nie może utworzyć blokady na wyłączność.

55

CURSOR nazwa_kursora [(parametr,… )] IS instrukcja_select[FOR UPDATE [OF (lista_kolumn) [NOWAIT]];

Klauzula WHERE CURRENT OF

DECLARE

CURSOR k_cursor(v_zakres ksiazka.cena%TYPE) IS

SELECT cena FROM ksiazka

WHERE cena > v_zakres

FOR UPDATE;

BEGIN

FOR i IN k_cursor(40) LOOP

UPDATE ksiazka SET cena = cena* 1.1

WHERE CURRENT OF k_cursor;

END LOOP;

COMMIT;

END;

56

Klauzula WHERE CURRENT OF

� Używając kursorów można aktualizować lub usuwać rekordy

� Klauzula FOR UPDATE blokuje wiersze

� Klauzula WHERE CURRENT OF odwołuje się do bieżącego rekordu z kursora jawnego

� Nie wolno zatwierdzać instrukcji z kursora jawnego, jeżeli użyta jest klauzula FOR UPDATE

57

Literatura

� McLaughlin, Michael, Oracle Database 11g PL/SQL programming, Oracle Press, 2008

� www.oracle.com

58

Plan wykładu PL/SQL PL/SQL - historia

Documents

Transcript of Plan wykładu PL/SQL PL/SQL - historia