ZSBDPL/SQL

Wykład 3

Prowadzący: dr Paweł Drozda

Program wykładu

dr P. Drozda

Wprowadzenie do PL\SQL Zmienne Typy danych Bloki, podbloki Funkcje SQL w PL/SQL Instrukcje warunkowe, pętle Złożone typy danych

Rekordy Tabele Kursory

Co to jest PL/SQL i po co?

dr P. Drozda

Rozszerzenie SQL-a do języka proceduralnego

Standardowy język Oracle dostępu do danych w relacyjnych bazach

Ułatwia wykonywanie powtarzających się operacji jak również operacji opartych o określone warunkinp. jeśli chcemy dać podwyżkę zależną od działu w którym pracownik jest zatrudniony

Podstawowe elementy PL/SQL

dr P. Drozda

Kod zorganizowany w wykonywalne bloki Zawiera elementy języka

proceduralnego: Zmienne, stałe typy danych Instrukcje warunkowe, pętle Bloki danych używanie wiele razy –

procedury, funkcje, pakiety Obsługa wyjątków

Zalety PL/SQL

dr P. Drozda

Integracja elementów proceduralnych z SQL-em

Zwiększona wydajność Możliwość podziału kodu na moduły Integracja z narzędziami Oracle Obsługa wyjątków

Struktura bloku

dr P. Drozda

DECLARE (opcja)– deklaracja zmiennych

BEGIN (konieczne) – zawiera instrukcje SQL i PL/SQL

EXCEPTION (opcja) – działania gdy pojawią się błędy

END; (konieczne) – kończy blok

DECLARE…

BEGIN…

EXCEPTION…

END;

Typy bloków

dr P. Drozda

Anonimowy Procedura Funkcja

[DECLARE]

BEGINInstrukcje SQL i PL/SQL

[EXCEPTION]

END;

FUNCTION nameRETURN datatypeIS

BEGINInstrukcje SQL i PL/SQL

[EXCEPTION]

END;

PROCEDURE nameIS

BEGINInstrukcje SQL i PL/SQL

[EXCEPTION]

END;

Przykład

dr P. Drozda

DECLARE name VARCHAR(30);

BEGINSELECT last_name INTO name FROM employeesWHERE employee_id = 111;DBMS_OUTPUT.PUT_LINE(‘Nazwisko

dla numeru 111 ’ || name);END;

Zmienne w PL/SQL

dr P. Drozda

Zaczynają się od litery Mogą zawierać cyfry oraz $,_ i # Maksymalnie 30 znaków Niedozwolone słowa kluczowe Przykład deklaracji:

DECLAREdata DATE;numer NUMBER NOT NULL :=10;miasto VARCHAR2(20):=‘Olsztyn’;ilosc CONSTANT NUMBER:=100;

Deklaracja zmiennych z %TYPE

dr P. Drozda

Jeśli zmienna odnosi się do zmiennej wcześniej zadeklarowanej lub do kolumny z tabeli

Zmienna z atrybutem %TYPE przyjmuje typ danych odnośnika

Ułatwienie uniknięcia błędnego typu danych Gdy zmienia się typ kolumny – zmiana typu

zmiennej Przykład

DECLAREname employees.last_name%TYPE;sal NUMBER(5,2);addition sal%TYPE :=100;

Typy danych – obiekty

dr P. Drozda

CLOB – duże ilości tekstu BLOB – duże obiekty binarne. Dane nie

są interpretowane w bazie danych BFILE – pliki zawierające dane binarne.

Nie są składowane w bazie – w bazie tylko wskazane miejsce, gdzie dany plik się znajduje

NCLOB – zawiera obiekty opisujące znaki w różnych językach

Konwersja typów danych (1)

dr P. Drozda

Ukryta – gdy operacja łączy ze sobą różne typy danychPrzykładDECLARENumer number(5) :=200;Inny VARCHAR2(10) :=‘200’;Razem Numer%TYPE;BEGINRazem := Numer + Inny;End;

Powyższa konwersja stosowana pomiędzy: Znakami i datami Numerami i znakami

Konwersja typów danych (2)

dr P. Drozda

Jawna – stosowane wbudowane funkcje TO_CHAR – przekształca do znaków TO_DATE – przekształca do daty TO_NUMBER – przekształca do liczby TO_TIMESTAMP – zmienia ciąg znaków do

typu timestamp

Podbloki – widoczność zmiennych

dr P. Drozda

Gdy zmienne mają tę samą nazwę – widoczna zmienna z bloku wewnętrznego

Dostęp do zmiennej zewnętrznej – nadanie etykiety blokowi zewnętrznemu

Przykład:begin <<zew>>

DECLARE name varchar(20):=‘Tomasz'; BEGIN

DECLARE name varchar(20):=‘Michal';BEGINdbms_output.PUT_LINE(zew.name);END;

END;end zew;

Przykładbegin <<zew>>

DECLARE

zarobki NUMBER(7,2):=4000;

dodatek NUMBER(7,2) := zarobki*0.2;

info VARCHAR2(40):=‘dostaje’;

BEGIN

DECLARE

zarobki NUMBER(7,2):=40000;

dodatek NUMBER(7,2) := 0;

wszystko NUMBER(7,2):=zarobki+dodatek;

BEGIN

(*)info := ‘Dyrektor nie’|| info;

zew.dodatek:=zarobki*0.3;

END;

(**) info:= ‘Administrator ’|| info;

END;

end zew;

Pytania: Wartość info z (*) Wartość wszystko z (**) Dodatek z (*) Zew.dodatek z (*) Dodatek z (**) Info z (**)

dr P. Drozda

Operatory

Logiczne AND, OR, NOT

Arytmetyczne +,-,*,/

Łączenia ||

Problem NULL Porównanie

zawierające NULL zwraca zawsze NULL

Użycie NOT do NULL zwraca NULL

Gdy w warunku występuje NULL – przypisane operacje nie są wykonywane

dr P. Drozda

Funkcje w PL/SQL

Brak możliwości użycia w poleceniach PL/SQL (DECODE, AVG, SUM, MIN, MAX, etc) salary = AVG(employees.salary); - niedozwolone

Mogą być użyte tylko w instrukcjach SQL osadzonych w PL/SQLSELECT AVG(salary) INTO sredniaFROM employeesWHERE department_id = 50;

Funkcje odnoszące się do jednego wiersza mogą być używane w PL\SQL (LENGTH, CURRENT_DATE, MONTHS_BETWEEN)

dr P. Drozda

Konwencja kodowania

dr P. Drozda

SQL, PL/SQL, Typy zmiennych – wielkie litery Zmienne, stałe, nazwy kolumn, nazwy tabel – małe litery Stosowanie wcięć dla czytelności kodu Przykład:

DECLAREdept_no NUMBER(4);location_id NUMBER(4);

BEGINSELECT department_id,

location_idINTO dept_no , location_id FROM departmentsWHERE department_name = ‘Sales’;

END;

Nazewnictwo zmiennych

dr P. Drozda

Co się stanie?DECLARE

department_nameVARCHAR2(20):=‘SALES’;BEGIN

DELETE FROM departmentsWHERE department_name = department_name;

END; Należy unikać nazywania zmiennych tak

samo jak nazwy kolumn – problemy w klauzuli WHERE

Po SELECT oraz INTO – nie ma problemu

Polecenie SELECT w PL/SQL

dr P. Drozda

SkładniaSELECT kolumny INTO zmienne FROM tabela

[WHERE warunki]; Cechy:

Słowo INTO jest obowiązkowe Liczba i typy kolumn zgadzają się z liczbą i

typami zmiennych Otrzymane wyniki zapytania zapisywane są w

zmiennych Zapytanie musi zwrócić dokładnie jeden wiersz

Przykład – polecenie SELECT

dr P. Drozda

DECLAREimie employees.first_name%TYPE;nazwisko employees.last_name%TYPE;

BEGINSELECT first_name, last_nameINTO imie, nazwiskoFROM employeesWHERE employee_id = 111;

END;

DML w PL/SQL

dr P. Drozda

Polecenia DML odpowiadają dokładnie poleceniom SQL (INSERT, UPDATE, DELETE, MERGE)

PrzykładMERGE INTO zatrudnienie zUSING archiwum aON (z.id_prac = a.ident)WHEN MATCHED THEN SET

z.imie = a.imie;z.nazwisko = a.nazwisko;

WHEN NOT MATCHED THENINSERT VALUES (a.ident,a.imie, a.nazwisko);

Instrukcje warunkowe – IF

dr P. Drozda

Składnia IF (podobnie jak wszędzie)IF warunek THEN instrukcja;[ELSEIF warunek THEN instrukcja;][ELSE instrukcja;]END IF;Przykład:

DECLAREsal NUMBER(4);premia NUMBER(4);

BEGIN IF sal > 5000 THEN premia = sal*0.1; ELSEIF sal > 3000 THEN premia= sal*0.2;

ELSE premia = sal*0.3;END IF;

END;

Instrukcje warunkowe CASE

dr P. Drozda

SkładniaCASE pole

WHEN warunek1 THEN instrukcje1 [WHEN warunek2 THEN instrukcje2]

…[ELSE instrukcje]

END;Przykład

ocena_slownie = CASE ocenaWHEN 5 THEN ‘bardzo dobry’WHEN 4 THEN ‘dobry’WHEN 3 THEN ‘dostateczny’WHEN 2 THEN ‘niedostateczny’WHEN 1,0 THEN ‘osioł’ELSE ‘nie ma takiej oceny’

END;

Inny przykład CASE

dr P. Drozda

CASE WHEN ocena = 5 THEN ‘ bardzo dobry’

…WHEN ocena IN (1,0) THEN ‘osioł’ELSE ‘nie ma takiej oceny’

END;

Pętla LOOP

dr P. Drozda

Wykonuje się do póki warunek nie jest spełniony Składnia pętli

LOOPinstrukcje;…EXIT WHEN warunek;instrukcje;

END LOOP; Przykład

DECLARE suma NUMBER(4):=0;i NUMBER(3):=1;

LOOPsuma:=suma+i;EXIT WHEN suma >100;i:=i+1;

END LOOP;

Pętla WHILE

dr P. Drozda

Składnia WHILEWHILE warunek LOOP

instrukcje;END LOOP;

Przykład:DECLARE

x NUMBER(4):=20;BEGINWHILE x>0 LOOP

x:=x-1;DBMS_OUTPUT.PUT_LINE(x);

END LOOP;END;

Pętla FOR

dr P. Drozda

Składnia FORFOR licznik IN dolnagr..gornagr [REVERSE]LOOPInstrukcje;END LOOP;

Licznik nie musi być deklarowany Jeśli REVERSE licznik przechodzi od

górnej granicy do dolnej Granice FOR nie powinny być NULL

FOR przykład

dr P. Drozda

DECLAREzarobki NUMBER(7):=2000;BEGIN

FOR i IN 1..10 LOOPzarobki :=zarobki + i*1000;INSERT INTO EMP VALUES (ident.nextval, ‘kozak’,zarobki);END LOOP;

END;

Kontrola wykonywania pętli

dr P. Drozda

CONTINUE wymusza porzucenie obecnej iteracji w pętli

i rozpoczęcie nowej Może zwiększyć wydajność bloku

EXIT wymusza opuszczenie obecnej pętli i

przejście do kolejnej instrukcji Konieczne w podstawowej pętli LOOP

CONTINUE, EXIT - przykład

dr P. Drozda

DECLAREi NUMBER(4):=0;suma NUMBER(10):=0;

BEGINWHILE TRUE LOOP

i:=i+1;IF MOD(i,2)=0 THEN CONTINUE;END IF;suma:=suma + i;IF i=10 THEN EXIT;END IF;

END LOOP;END;

Złożone typy danych

dr P. Drozda

Rekordy %ROWTYPE Tabele INDEX BY Tabele nested tables Tabele VARRAY

Rekordy

dr P. Drozda

Zawierają dane logicznie ze sobą powiązane np. wiersz bądź część wiersza tabeli

Mogą zawierać pola o różnych typach danych

Zapisują tylko jeden wiersz (np. z tabeli) Muszą mieć co najmniej jedno pole Składnia rekordu:

TYPE nazwa IS RECORD(definicjapole1, definicjapole2,…);

Przykład definicji rekordu

dr P. Drozda

TYPE pracownik IS RECORD(imie VARCHAR(20),id NUMBER(5),nazwisko VARCHAR(20),zarobki employees.salary%TYPE,miejsce departments%ROWTYPE);

wystapienie pracownik;odwołanie do pola np.wystapienie.id – odwołanie do identyfikatora

%ROWTYPE

dr P. Drozda

Odwołuje się do wszystkich kolumn jednej tabeli Przed %ROWTYPE należy dodać nazwę tabeli do

której ma odwoływać się zmienna Nazwy kolumn oraz typy danych w

poszczególnych kolumnach przekazane do zmiennej

Przy zmianie typów danych i nazw w tabeli – zmiana też w zmiennej z %ROWTYPE – nie trzeba pilnować typów

Deklaracja:nazwapola nazwatabeli%ROWTYPE;osoba employees%ROWTYPE;odwołanie do pola (np. zarobki)osoba.salary

Przykład wykorzystania %ROWTYPE

dr P. Drozda

Tabela emp(id,name, salary,fire_date)DECLARE

zmiana emp%ROWTYPE;BEGIN

SELECT * INTO zmiana FROM empWHERE id=111;zmiana.fire_date = CURRENT_DATE;UPDATE emp SET ROW zmiana WHERE id=111;

END;

Tabele INDEX BY

dr P. Drozda

Składają się z dwóch pól – identyfikatora (liczba lub ciąg znaków) oraz wartości

Gromadzą dane o tym samym typie – np. imiona z tabeli employees

Mają nieograniczoną wielkość tzn. ogranicza je zakres wartości identyfikatora

Odpowiadają tabelom z „normalnych” języków programowania

Tworzenie tabel – INDEX BY

dr P. Drozda

TYPE nazwa IS TABLE OFtypkolumny INDEX BY PLS_INTEGER,|BINARY_INTEGER|VARCHAR2;

PrzykładDECLARETYPE tabelaimiona IS TABLE OF

employees.first_name%TYPEINDEX BY PLS_INTEGER;imiona tabelaimiona;BEGINimiona(1):=‘Zenek’;END;

Funkcje odwołujące się do tabel INDEX BY

dr P. Drozda

EXISTS – sprawdza czy istnieje dana komórka w tabelinp. IF imiona(1).EXISTS THEN INSERT INTO …;

COUNT – zwraca liczbę elementów z tabelidbms_output.put_line(imiona.COUNT);

FIRST, LAST – zwracają pierwszą/ostatnią wartość z tabeli; gdy tabela pusta – zwranany jest NULL

PRIOR(n), NEXT(n) – zwracają poprzedni/kolejny indeks z tabeli – porównując do n

DELETE – usuwa elementy z tabeli bez argumentu – usuwa wszystko z argumentem n – usuwa n-ty element z argumentami m,n – usuwa wszystkie elementy od m do n

Przykład INDEX BY

dr P. Drozda

DECLARETYPE typpracownik IS TABLE OFemployees%ROWTYPE INDEX BY PLS_INTEGER;pracownik typpracownik;

BEGINFOR i IN 100..105LOOP

SELECT * INTO pracownik(i) FROM employees WHERE employee_id=i;END LOOP;FOR i IN pracownik.FIRST..pracownik.LASTLOOP

DBMS_OUTPUT.PUT_LINE(pracownik(i).last_name, pracownik(i).first_name);END LOOP;

END;

Tabele nested tables

dr P. Drozda

SkładniaTYPE nazwa IS TABLE OF typ_danych;

Nie mają z góry określonej wielkości Maksymalny rozmiar 2GB Odwołania do poszczególnych wartości –

tak jak w tabelach INDEX BY (w kluczach nie może być ujemnych wartości)

Wiersze nie są poustawiane w jakimś porządku – przeszukiwanie rekord po rekordzie (INDEX BY – mają klucz indeksowany)

Przykład – nested tables

dr P. Drozda

DECLARETYPE typ_lokacji IS TABLE OF locations.city%TYPE;miasta typ_lokacji;

BEGINmiasta:=typ_lokacji(‘Gniewkowo’, ’Solec’, ’Pszczólki’, ’Książki’);FOR i IN 1..miasta.count() LOOP

dbms_output(miasta(i));END LOOP;

END;

Tabele VARRAY

dr P. Drozda

Ustalona w deklaracji wielkość tabeli Składnia tworzenia tabeli VARRAY

TYPE nazwa IS VARRAY(n) OF typDanych; Maksymalny rozmiar 2GB

Kursory

dr P. Drozda

Definiowane do poleceń SELECT, które zwracają wiele wierszy do przechowywania danych

Można przetwarzać wiersz po wierszu Zasada działania:

DECLARE

OPEN FETCH CLOSEEMPT

Y?

NO

YES

Składnia deklaracji kursora

dr P. Drozda

CURSOR nazwa ISzapytanie SQL (SELECT);

Przykład:DECLARE

CURSOR dept_30 ISSELECT last_name, first_name, salaryFROM employeesWHERE department_id = 30;

Otwieranie kursora – OPEN

dr P. Drozda

Aby była możliwość dostępu do danych w kursorze należy użyć polecenia – OPEN nazwa;

Polecenie alokuje dynamicznie pamięć dla danych z otwieranego kursora

Odczytywanie są odpowiednie typy i nazwy kolumn dla kursora

Wiersze nie są przekazywane do zmiennych kursora – dopiero w momencie uruchomienia polecenia FETCH dane są czytane

Przykład

dr P. Drozda

DECLARECURSOR dane ISSELECT first_name, last_name FROM employees WHERE department = 50;imie employees.first_name%TYPE;nazwisko employees.last_name%TYPE;

BEGINOPEN dane;FETCH dane INTO imie, nazwisko;DBMS_OUTPUT.PUT_LINE(imie || ‘ ’|| nazwisko);

END;

FETCH, CLOSE

dr P. Drozda

FETCH pobiera z kursora jeden wiersz danych i przesuwa

się do następnego wiersza Po INTO liczba zmiennych i typy muszą odpowiadać

liczbie i typom kolumn pobieranych z kursora Kolejność występowania zmiennych musi

odpowiadać kolejności kolumnom zdefiniowanych w kursorze

FETCH nazwa INTO zmienne; CLOSE

Zamyka dostęp do kursora Zwalnia pamięć zajmowaną przez kursor Dobra praktyka zawsze zamykać, gdy niepotrzebny CLOSE nazwa;

Użycie rekordów w kursorach

dr P. Drozda

Przy deklaracji zmiennej – typ ustawiany jako nazwakursora%ROWTYPE;

Przy poleceniu FETCH wiersz wpisywany do zadeklarowanego rekordu

Przykład:DECLARE

CURSOR dane …;wiersz dane%ROWTYPE;

BEGINOPEN dane;FETCH dane INTO wiersz;…

END;

Przykład – pełna wersja

dr P. Drozda

DECLARECURSOR dane IS

SELECT first_name, last_name FROM employeesWHERE department_id = 50;

wiersz dane%ROWTYPE;BEGIN

OPEN dane;LOOP

FETCH dane INTO wiersz;EXIT WHEN dane%NOTFOUND;DBMS_OUTPUT.PUT_LINE(wiersz.first_name, ‘ ‘ ,

wiersz.last_name);END LOOP;dane%ROWCOUNT;CLOSE dane;

END;

FOR dla kursora

dr P. Drozda

SkładniaFOR zmienna IN nazwakursoraLOOPinstrukcje;END LOOP;

Otwarcie, FETCH i zamknięcie kursora przez pętle

Zmienna – rekord sczytujący kolejne wiersze kursora

Przykład

dr P. Drozda

DECLARE CURSOR dane IS SELECT first_name, last_name FROM employeesWHERE department_id = 50;

BEGINFOR wiersz IN daneLOOP

DBMS_OUTPUT.PUT_LINE(wiersz.first_name|| ‘ ‘ || wiersz.last_name);END LOOP;DBMS_OUTPUT.PUT_LINE(dane%ROWCOUNT);

END;

Atrybuty kursora

dr P. Drozda

%ISOPEN – zwraca prawdę gdy kursor otwarty

%NOTFOUND – zwraca prawdę gdy zmienna nie zawiera danych

%FOUND – zwraca prawdę gdy zmienna zawiera dane

%ROWCOUNT – zwraca liczbę wierszy wyciągniętych poprzez kursor do momentu wywołania atrybutu

Przykład bez deklaracji kursora

dr P. Drozda

BEGIN FOR dane IN (SELECT last_name,

first_name FROM employees)LOOP…END LOOP;END;

Kursor – parametry

dr P. Drozda

Do kursora mogą zostać dodane parametry

Skłania:CURSOR nazwa(parametr typ, …) ISSELECT …;BEGIN OPEN nazwa(wartosc parametru);

Przykład

dr P. Drozda

DECLARECURSOR ponumerze (depid NUMBER) ISSELECT last_name, first_name, salary FROM employeesWHERE department_id=depid;

BEGINOPEN ponumerze(10);CLOSE ponumerze;OPEN ponumerze(50);

…END;