Zsbd PL/SQL

1
ZsbdPL/SQL

• Wyklad 3
• Prowadzacy dr Pawel Drozda

2
Program wykladu

• Wprowadzenie do PL\SQL
• Zmienne
• Typy danych
• Bloki, podbloki
• Funkcje SQL w PL/SQL
• Instrukcje warunkowe, petle
• Zlozone typy danych
• Rekordy
• Tabele
• Kursory

3
Co to jest PL/SQL i po co?

• Rozszerzenie SQL-a do jezyka proceduralnego
• Standardowy jezyk Oracle dostepu do danych w
  relacyjnych bazach
• Ulatwia wykonywanie powtarzajacych sie operacji
  jak równiez operacji opartych o okreslone warunki
• np. jesli chcemy dac podwyzke zalezna od dzialu
  w którym pracownik jest zatrudniony

4
Podstawowe elementy PL/SQL

• Kod zorganizowany w wykonywalne bloki
• Zawiera elementy jezyka proceduralnego
• Zmienne, stale typy danych
• Instrukcje warunkowe, petle
• Bloki danych uzywanie wiele razy procedury,
  funkcje, pakiety
• Obsluga wyjatków

5
Zalety PL/SQL

• Integracja elementów proceduralnych z SQL-em
• Zwiekszona wydajnosc
• Mozliwosc podzialu kodu na moduly
• Integracja z narzedziami Oracle
• Obsluga wyjatków

6
Struktura bloku

• DECLARE (opcja) deklaracja zmiennych
• BEGIN (konieczne) zawiera instrukcje SQL i
  PL/SQL
• EXCEPTION (opcja) dzialania gdy pojawia sie
  bledy
• END (konieczne) konczy blok

DECLARE
BEGIN
EXCEPTION
END
7
Typy bloków

• Anonimowy Procedura Funkcja

DECLARE BEGIN Instrukcje SQL i
PL/SQL EXCEPTION END
FUNCTION name RETURN datatype IS BEGIN Instrukcje
SQL i PL/SQL EXCEPTION END
PROCEDURE name IS BEGIN Instrukcje SQL i
PL/SQL EXCEPTION END
8
Przyklad

• DECLARE
• name VARCHAR(30)
• BEGIN
• SELECT last_name
• INTO name
• FROM employees
• WHERE employee_id 111
• DBMS_OUTPUT.PUT_LINE(Nazwisko dla numeru
  111 name)
• END

9
Zmienne w PL/SQL

• Zaczynaja sie od litery
• Moga zawierac cyfry oraz ,_ i
• Maksymalnie 30 znaków
• Niedozwolone slowa kluczowe
• Przyklad deklaracji
• DECLARE
• data DATE
• numer NUMBER NOT NULL 10
• miasto VARCHAR2(20)Olsztyn
• ilosc CONSTANT NUMBER100

10
Deklaracja zmiennych z TYPE

• Jesli zmienna odnosi sie do zmiennej wczesniej
  zadeklarowanej lub do kolumny z tabeli
• Zmienna z atrybutem TYPE przyjmuje typ danych
  odnosnika
• Ulatwienie unikniecia blednego typu danych
• Gdy zmienia sie typ kolumny zmiana typu
  zmiennej
• Przyklad
• DECLARE
• name employees.last_nameTYPE
• sal NUMBER(5,2)
• addition salTYPE 100

11
Typy danych obiekty

• CLOB duze ilosci tekstu
• BLOB duze obiekty binarne. Dane nie sa
  interpretowane w bazie danych
• BFILE pliki zawierajace dane binarne. Nie sa
  skladowane w bazie w bazie tylko wskazane
  miejsce, gdzie dany plik sie znajduje
• NCLOB zawiera obiekty opisujace znaki w róznych
  jezykach

12
Konwersja typów danych (1)

• Ukryta gdy operacja laczy ze soba rózne typy
  danych
• Przyklad
• DECLARE
• Numer number(5) 200
• Inny VARCHAR2(10) 200
• Razem NumerTYPE
• BEGIN
• Razem Numer Inny
• End
• Powyzsza konwersja stosowana pomiedzy
• Znakami i datami
• Numerami i znakami

13
Konwersja typów danych (2)

• Jawna stosowane wbudowane funkcje
• TO_CHAR przeksztalca do znaków
• TO_DATE przeksztalca do daty
• TO_NUMBER przeksztalca do liczby
• TO_TIMESTAMP zmienia ciag znaków do typu
  timestamp

14
Podbloki widocznosc zmiennych

• Gdy zmienne maja te sama nazwe widoczna zmienna
  z bloku wewnetrznego
• Dostep do zmiennej zewnetrznej nadanie etykiety
  blokowi zewnetrznemu
• Przyklad
• begin ltltzewgtgt
• DECLARE name varchar(20)Tomasz'
• BEGIN
• DECLARE
• name varchar(20)Michal'
• BEGIN
• dbms_output.PUT_LINE(zew.name)
• END
• END
• end zew

15
Przyklad

• begin ltltzewgtgt
• DECLARE
• zarobki NUMBER(7,2)4000
• dodatek NUMBER(7,2) zarobki0.2
• info VARCHAR2(40)dostaje
• BEGIN
• DECLARE
• zarobki NUMBER(7,2)40000
• dodatek NUMBER(7,2) 0
• wszystko NUMBER(7,2)zarobkidodatek
• BEGIN
• ()info Dyrektor nie info
• zew.dodatekzarobki0.3
• END
• () info Administrator info
• END
• end zew

• Pytania
• Wartosc info z ()
• Wartosc wszystko z ()
• Dodatek z ()
• Zew.dodatek z ()
• Dodatek z ()
• Info z ()

16
Operatory

• Logiczne
• AND, OR, NOT
• Arytmetyczne
• ,-,,/
• Laczenia

• Problem NULL
• Porównanie zawierajace NULL zwraca zawsze NULL
• Uzycie NOT do NULL zwraca NULL
• Gdy w warunku wystepuje NULL przypisane
  operacje nie sa wykonywane

17
Funkcje w PL/SQL

• Brak mozliwosci uzycia w poleceniach PL/SQL
  (DECODE, AVG, SUM, MIN, MAX, etc)
• salary AVG(employees.salary) - niedozwolone
• Moga byc uzyte tylko w instrukcjach SQL
  osadzonych w PL/SQL
• SELECT AVG(salary)
• INTO srednia
• FROM employees
• WHERE department_id 50
• Funkcje odnoszace sie do jednego wiersza moga byc
  uzywane w PL\SQL (LENGTH, CURRENT_DATE,
  MONTHS_BETWEEN)

18
Konwencja kodowania

• SQL, PL/SQL, Typy zmiennych wielkie litery
• Zmienne, stale, nazwy kolumn, nazwy tabel male
  litery
• Stosowanie wciec dla czytelnosci kodu
• Przyklad
• DECLARE
• dept_no NUMBER(4)
• location_id NUMBER(4)
• BEGIN
• SELECT department_id,
• location_id
• INTO dept_no , location_id
• FROM departments
• WHERE department_name Sales
• END

19
Nazewnictwo zmiennych

• Co sie stanie?
• DECLARE
• department_name VARCHAR2(20)SALES
• BEGIN
• DELETE FROM departments
• WHERE department_name department_name
• END
• Nalezy unikac nazywania zmiennych tak samo jak
  nazwy kolumn problemy w klauzuli WHERE
• Po SELECT oraz INTO nie ma problemu

20
Polecenie SELECT w PL/SQL

• Skladnia
• SELECT kolumny INTO zmienne FROM tabela WHERE
  warunki
• Cechy
• Slowo INTO jest obowiazkowe
• Liczba i typy kolumn zgadzaja sie z liczba i
  typami zmiennych
• Otrzymane wyniki zapytania zapisywane sa w
  zmiennych
• Zapytanie musi zwrócic dokladnie jeden wiersz

21
Przyklad polecenie SELECT

• DECLARE
• imie employees.first_nameTYPE
• nazwisko employees.last_nameTYPE
• BEGIN
• SELECT first_name, last_name
• INTO imie, nazwisko
• FROM employees
• WHERE employee_id 111
• END

22
DML w PL/SQL

• Polecenia DML odpowiadaja dokladnie poleceniom
  SQL (INSERT, UPDATE, DELETE, MERGE)
• Przyklad
• MERGE INTO zatrudnienie z
• USING archiwum a
• ON (z.id_prac a.ident)
• WHEN MATCHED
• THEN SET
• z.imie a.imie
• z.nazwisko a.nazwisko
• WHEN NOT MATCHED
• THEN
• INSERT VALUES (a.ident,a.imie, a.nazwisko)

23
Instrukcje warunkowe IF

• Skladnia IF (podobnie jak wszedzie)
• IF warunek THEN instrukcja
• ELSEIF warunek THEN instrukcja
• ELSE instrukcja
• END IF
• Przyklad
• DECLARE
• sal NUMBER(4)
• premia NUMBER(4)
• BEGIN
• IF sal gt 5000 THEN premia sal0.1
• ELSEIF sal gt 3000 THEN premia sal0.2
• ELSE premia sal0.3
• END IF
• END

24
Instrukcje warunkowe CASE

• Skladnia
• CASE pole
• WHEN warunek1 THEN instrukcje1
• WHEN warunek2 THEN instrukcje2
• ELSE instrukcje
• END
• Przyklad
• ocena_slownie CASE ocena
• WHEN 5 THEN bardzo dobry
• WHEN 4 THEN dobry
• WHEN 3 THEN dostateczny
• WHEN 2 THEN niedostateczny
• WHEN 1,0 THEN osiol
• ELSE nie ma takiej oceny
• END

25
Inny przyklad CASE

• CASE
• WHEN ocena 5 THEN bardzo dobry
• WHEN ocena IN (1,0) THEN osiol
• ELSE nie ma takiej oceny
• END

26
Petla LOOP

• Wykonuje sie do póki warunek nie jest spelniony
• Skladnia petli
• LOOP
• instrukcje
• EXIT WHEN warunek
• instrukcje
• END LOOP
• Przyklad
• DECLARE
• suma NUMBER(4)0
• i NUMBER(3)1
• LOOP
• sumasumai
• EXIT WHEN suma gt100
• ii1
• END LOOP

27
Petla WHILE

• Skladnia WHILE
• WHILE warunek LOOP
• instrukcje
• END LOOP
• Przyklad
• DECLARE
• x NUMBER(4)20
• BEGIN
• WHILE xgt0 LOOP
• xx-1
• DBMS_OUTPUT.PUT_LINE(x)
• END LOOP
• END

28
Petla FOR

• Skladnia FOR
• FOR licznik IN dolnagr..gornagr REVERSE
• LOOP
• Instrukcje
• END LOOP
• Licznik nie musi byc deklarowany
• Jesli REVERSE licznik przechodzi od górnej
  granicy do dolnej
• Granice FOR nie powinny byc NULL

29
FOR przyklad

• DECLARE
• zarobki NUMBER(7)2000
• BEGIN
• FOR i IN 1..10
• LOOP
• zarobki zarobki i1000
• INSERT INTO EMP VALUES
• (ident.nextval, kozak,zarobki)
• END LOOP
• END

30
Kontrola wykonywania petli

• CONTINUE
• wymusza porzucenie obecnej iteracji w petli i
  rozpoczecie nowej
• Moze zwiekszyc wydajnosc bloku
• EXIT
• wymusza opuszczenie obecnej petli i przejscie do
  kolejnej instrukcji
• Konieczne w podstawowej petli LOOP

31
CONTINUE, EXIT - przyklad

• DECLARE
• i NUMBER(4)0
• suma NUMBER(10)0
• BEGIN
• WHILE TRUE LOOP
• ii1
• IF MOD(i,2)0 THEN CONTINUE
• END IF
• sumasuma i
• IF i10 THEN EXIT
• END IF
• END LOOP
• END

32
Zlozone typy danych

• Rekordy
• ROWTYPE
• Tabele INDEX BY
• Tabele nested tables
• Tabele VARRAY

33
Rekordy

• Zawieraja dane logicznie ze soba powiazane np.
  wiersz badz czesc wiersza tabeli
• Moga zawierac pola o róznych typach danych
• Zapisuja tylko jeden wiersz (np. z tabeli)
• Musza miec co najmniej jedno pole
• Skladnia rekordu
• TYPE nazwa IS RECORD(
• definicjapole1, definicjapole2,)

34
Przyklad definicji rekordu

• TYPE pracownik IS RECORD(
• imie VARCHAR(20),
• id NUMBER(5),
• nazwisko VARCHAR(20),
• zarobki employees.salaryTYPE,
• miejsce departmentsROWTYPE)
• wystapienie pracownik
• odwolanie do pola np.
• wystapienie.id odwolanie do identyfikatora

35
ROWTYPE

• Odwoluje sie do wszystkich kolumn jednej tabeli
• Przed ROWTYPE nalezy dodac nazwe tabeli do
  której ma odwolywac sie zmienna
• Nazwy kolumn oraz typy danych w poszczególnych
  kolumnach przekazane do zmiennej
• Przy zmianie typów danych i nazw w tabeli
  zmiana tez w zmiennej z ROWTYPE nie trzeba
  pilnowac typów
• Deklaracja
• nazwapola nazwatabeliROWTYPE
• osoba employeesROWTYPE
• odwolanie do pola (np. zarobki)
• osoba.salary

36
Przyklad wykorzystania ROWTYPE

• Tabela emp(id,name, salary,fire_date)
• DECLARE
• zmiana empROWTYPE
• BEGIN
• SELECT INTO zmiana FROM emp
• WHERE id111
• zmiana.fire_date CURRENT_DATE
• UPDATE emp SET ROW zmiana WHERE id111
• END

37
Tabele INDEX BY

• Skladaja sie z dwóch pól identyfikatora (liczba
  lub ciag znaków) oraz wartosci
• Gromadza dane o tym samym typie np. imiona z
  tabeli employees
• Maja nieograniczona wielkosc tzn. ogranicza je
  zakres wartosci identyfikatora
• Odpowiadaja tabelom z normalnych jezyków
  programowania

38
Tworzenie tabel INDEX BY

• TYPE nazwa IS TABLE OF
• typkolumny INDEX BY
• PLS_INTEGER,BINARY_INTEGERVARCHAR2
• Przyklad
• DECLARE
• TYPE tabelaimiona IS TABLE OF
• employees.first_nameTYPE
• INDEX BY PLS_INTEGER
• imiona tabelaimiona
• BEGIN
• imiona(1)Zenek
• END

39
Funkcje odwolujace sie do tabel INDEX BY

• EXISTS sprawdza czy istnieje dana komórka w
  tabeli
• np. IF imiona(1).EXISTS THEN INSERT INTO
• COUNT zwraca liczbe elementów z tabeli
• dbms_output.put_line(imiona.COUNT)
• FIRST, LAST zwracaja pierwsza/ostatnia wartosc
  z tabeli gdy tabela pusta zwranany jest NULL
• PRIOR(n), NEXT(n) zwracaja poprzedni/kolejny
  indeks z tabeli porównujac 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

40
Przyklad INDEX BY

• DECLARE
• TYPE typpracownik IS TABLE OF
• employeesROWTYPE INDEX BY PLS_INTEGER
• pracownik typpracownik
• BEGIN
• FOR i IN 100..105
• LOOP
• SELECT INTO pracownik(i) FROM employees WHERE
  employee_idi
• END LOOP
• FOR i IN pracownik.FIRST..pracownik.LAST
• LOOP
• DBMS_OUTPUT.PUT_LINE(pracownik(i).last_name,
  pracownik(i).first_name)
• END LOOP
• END

41
Tabele nested tables

• Skladnia
• TYPE nazwa IS TABLE OF typ_danych
• Nie maja z góry okreslonej wielkosci
• Maksymalny rozmiar 2GB
• Odwolania do poszczególnych wartosci tak jak w
  tabelach INDEX BY (w kluczach nie moze byc
  ujemnych wartosci)
• Wiersze nie sa poustawiane w jakims porzadku
  przeszukiwanie rekord po rekordzie (INDEX BY
  maja klucz indeksowany)

42
Przyklad nested tables

• DECLARE
• TYPE typ_lokacji IS TABLE OF locations.cityTYPE
  
• miasta typ_lokacji
• BEGIN
• miastatyp_lokacji(Gniewkowo, Solec,
  Pszczólki, Ksiazki)
• FOR i IN 1..miasta.count()
• LOOP
• dbms_output(miasta(i))
• END LOOP
• END

43
Tabele VARRAY

• Ustalona w deklaracji wielkosc tabeli
• Skladnia tworzenia tabeli VARRAY
• TYPE nazwa IS VARRAY(n) OF typDanych
• Maksymalny rozmiar 2GB

44
Kursory

• Definiowane do polecen SELECT, które zwracaja
  wiele wierszy do przechowywania danych
• Mozna przetwarzac wiersz po wierszu
• Zasada dzialania

EMPTY?
YES
DECLARE
OPEN
FETCH
CLOSE
NO
45
Skladnia deklaracji kursora

• CURSOR nazwa IS
• zapytanie SQL (SELECT)
• Przyklad
• DECLARE
• CURSOR dept_30 IS
• SELECT last_name, first_name, salary
• FROM employees
• WHERE department_id 30

46
Otwieranie kursora OPEN

• Aby byla mozliwosc dostepu do danych w kursorze
  nalezy uzyc polecenia OPEN nazwa
• Polecenie alokuje dynamicznie pamiec dla danych z
  otwieranego kursora
• Odczytywanie sa odpowiednie typy i nazwy kolumn
  dla kursora
• Wiersze nie sa przekazywane do zmiennych kursora
  dopiero w momencie uruchomienia polecenia FETCH
  dane sa czytane

47
Przyklad

• DECLARE
• CURSOR dane IS
• SELECT first_name, last_name FROM employees
  WHERE department 50
• imie employees.first_nameTYPE
• nazwisko employees.last_nameTYPE
• BEGIN
• OPEN dane
• FETCH dane INTO imie, nazwisko
• DBMS_OUTPUT.PUT_LINE(imie nazwisko)
• END

48
FETCH, CLOSE

• FETCH
• pobiera z kursora jeden wiersz danych i przesuwa
  sie do nastepnego wiersza
• Po INTO liczba zmiennych i typy musza odpowiadac
  liczbie i typom kolumn pobieranych z kursora
• Kolejnosc wystepowania zmiennych musi odpowiadac
  kolejnosci kolumnom zdefiniowanych w kursorze
• FETCH nazwa INTO zmienne
• CLOSE
• Zamyka dostep do kursora
• Zwalnia pamiec zajmowana przez kursor
• Dobra praktyka zawsze zamykac, gdy niepotrzebny
• CLOSE nazwa

49
Uzycie rekordów w kursorach

• Przy deklaracji zmiennej typ ustawiany jako
  nazwakursoraROWTYPE
• Przy poleceniu FETCH wiersz wpisywany do
  zadeklarowanego rekordu
• Przyklad
• DECLARE
• CURSOR dane
• wiersz daneROWTYPE
• BEGIN
• OPEN dane
• FETCH dane INTO wiersz
• END

50
Przyklad pelna wersja

• DECLARE
• CURSOR dane IS
• SELECT first_name, last_name FROM employees
• WHERE department_id 50
• wiersz daneROWTYPE
• BEGIN
• OPEN dane
• LOOP
• FETCH dane INTO wiersz
• EXIT WHEN daneNOTFOUND
• DBMS_OUTPUT.PUT_LINE(wiersz.first_name, ,
  wiersz.last_name)
• END LOOP
• daneROWCOUNT
• CLOSE dane
• END

51
FOR dla kursora

• Skladnia
• FOR zmienna IN nazwakursora
• LOOP
• instrukcje
• END LOOP
• Otwarcie, FETCH i zamkniecie kursora przez petle
• Zmienna rekord sczytujacy kolejne wiersze
  kursora

52
Przyklad

• DECLARE
• CURSOR dane IS
• SELECT first_name, last_name FROM employees
• WHERE department_id 50
• BEGIN
• FOR wiersz IN dane
• LOOP
• DBMS_OUTPUT.PUT_LINE(wiersz.first_name
  wiersz.last_name)
• END LOOP
• DBMS_OUTPUT.PUT_LINE(daneROWCOUNT)
• END

53
Atrybuty kursora

• ISOPEN zwraca prawde gdy kursor otwarty
• NOTFOUND zwraca prawde gdy zmienna nie zawiera
  danych
• FOUND zwraca prawde gdy zmienna zawiera dane
• ROWCOUNT zwraca liczbe wierszy wyciagnietych
  poprzez kursor do momentu wywolania atrybutu

54
Przyklad bez deklaracji kursora

• BEGIN
• FOR dane IN (SELECT last_name, first_name FROM
  employees)
• LOOP
• END LOOP
• END

55
Kursor parametry

• Do kursora moga zostac dodane parametry
• Sklania
• CURSOR nazwa(parametr typ, ) IS
• SELECT
• BEGIN
• OPEN nazwa(wartosc parametru)

56
Przyklad

• DECLARE
• CURSOR ponumerze (depid NUMBER) IS
• SELECT last_name, first_name, salary FROM
  employees
• WHERE department_iddepid
• BEGIN
• OPEN ponumerze(10)
• CLOSE ponumerze
• OPEN ponumerze(50)
• END