PL/SQLmst.webd.pl/_sggw/plsql/wyk_ad_1.pdf · relacja (tabela) id_osoby imie_osoby data_urodzenia 1...

PL/SQL

Część 1 – Oracle SQL

Piotr Medoń

Cele

• Różnice w budowie zapytań bazodanowych pomiędzy bazami SQL Server oraz Oracle

• Standardowe funkcje Oracle SQL

2

Wprowadzenie do SQL

SQL (Structured Query Language)

• DDL (Data Definition Language) służy do tworzenia, modyfikowania i usuwania obiektów bazodanowych np. CREATE TABLE

• DML (Data Manipulation Language) służy do modyfikowania oraz przeglądania danych (INSERT, UPDATE, DELETE, SELECT)

3

Relacja (Tabela)

ID_OSOBY IMIE_OSOBY DATA_URODZENIA

1 ADAM 01-01-1990

2 ANDRZEJ 31-05-1975

3 ANNA 04-03-1970

4 BEATA 20-10-1095

KARTOTEKA

Nazwa relacji

(tabeli)

Atrybut (kolumna) liczbowy

Atrybut (kolumna) tekstowy

Atrybut (kolumna) daty

Krotki (Rekordy)

4

Budowa zapytania SQL

SELECT kolumny

FROM nazwy_tabel

WHERE warunki i złączenia;

5

Różnice pomiędzy bazami SQL Server i Oracle

SQL Server Oracle

SELECT nie wymaga FROM np. SELECT GETDATE()

SELECT zawsze wymaga FROM W każdej instalacji bazy danych Oracle istnieje tabela o nazwie DUAL, która zawsze zawiera jeden element np. SELECT SYSDATE FROM DUAL

6


SQL Server Oracle

Kolumny mogą być autonumerowane (właściwość IDENTITY)

Nie istnieje właściwość kolumny umożliwiająca autonumerowanie. Istnieją sekwencje – obiekty generujące kolejne liczby, bezpieczne w korzystaniu przez wiele wątków. Na przykład, aby wstawić wartość do tabeli faktury należy wykonać polecenie: INSERT INTO faktury (ID_FAKTURY, KWOTA) VALUES(faktury_sekwencja.NEXTVAL, 1000);

7


SQL Server Oracle

Brak Oracle’owej konwencji zewnętrznego łączenia tabel

Istnieją dwie konwencje zewnętrznego łączenia tabel ISO: SELECT p.imie, d.nazwa_dzialu FROM pracownicy p LEFT OUTER JOIN dzial d ON p.id_dzialu = d.id_dzialu oraz Oracle’owa: SELECT p.imie, d.nazwa_dzialu FROM pracownicy p, dzial d WHERE p.id_dzialu = d.id_dzialu (+)

8


SQL Server Oracle

Możliwe łączenia tabel: INNER JOIN CROSS JOIN LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN

Możliwe łączenia tabel: INNER JOIN JOIN … USING NATURAL JOIN CROSS JOIN LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN

9


SQL Server Oracle

Jeżeli kolumny, po których łączymy dwie tabele mają te same nazwy w obu tabelach, możemy użyć łączenie „JOIN USING”.

Poniższe zapytania są równoważne:

SELECT p.imie, d.nazwa_dzialu FROM pracownicy p JOIN dzial d ON p.id_dzialu = d.id_dzialu

SELECT p.imie, d.nazwa_dzialu FROM pracownicy p JOIN dzial d USING (dzial_id)

10


SQL Server Oracle

Za pomocą NATURAL JOIN łączymy tabele używając wszystkich kolumn o takiej samej nazwie.

Poniższe zapytania będą równoważne, jeżeli jedyną kolumną o wspólnej nazwie w tabelach pracownicy oraz dzial będzie id_dzialu:

SELECT p.imie, d.nazwa_dzialu FROM pracownicy p JOIN dzial d ON p.id_dzialu = d.id_dzialu

SELECT p.imie, d.nazwa_dzialu FROM pracownicy p NATURAL JOIN dzial d

11


SQL Server Oracle

Wyrażenie TOP: Select top 5 * from pracownicy

Nie ma wyrażenia TOP Istnieje pseudokolumna ROWNUM oznaczająca numer wiersza zapytania, np. Select * from pracownicy where rownum <= 5; W bazie Oracle wyniki zapytania są sortowane na końcu wykonania zapytania lub podzapytania. Aby uzyskać informację o 5 najlepiej zarabiających pracownikach należy skorzystać z konstrukcji podzapytania: SELECT * FROM ( SELECT * FROM pracownicy ORDER BY zarobki DESC) WHERE ROWNUM <= 5;

12


SQL Server Oracle

Domyślnie nie rozpoznaje różnic dużych i małych liter podczas porównywania danych tekstowych (case insensitive). Poniższe zapytania są równoważne: SELECT * FROM pracownicy p WHERE p.imie= ‘Adam’ SELECT * FROM pracownicy p WHERE p.imie= ‘ADAM’

Zawsze rozpoznaje różnice dużych i małych liter. Aby wykonać wyszukiwanie ignorujące różnie małych i dużych liter należy wykonać: SELECT * FROM pracownicy p WHERE UPPER(p.imie) = ‘ADAM’

13

Podstawowe typy danych

• Tekstowy (VARCHAR2)

• Numeryczny (NUMBER)

• Data (DATE)

14

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

15

Tekstowe funkcje PL/SQL

• CONCAT (tekst1, tekst2)

– Służy do łączenia dwóch wartości tekstowych w jeden.

• konkatenacja za pomocą ||

– Służy do łączenia wartości tekstowych w jeden.

CONCAT(‘labo’, ‘ratorium’) zwróci ‘laboratorium’

‘Ala’ || ‘ ma’ || ‘ kota’ zwróci ‘Ala ma kota’

16


• UPPER (tekst)

– Zamienia wszystkie litery parametru tekstowego na duże

• LOWER (tekst)

– Zamienia wszystkie litery parametru tekstowego na małe

UPPER(‘DoktRYna’) zwróci ‘DOKTRYNA’

LOWER(‘InfoRMATYKA’) zwróci ‘informatyka’

17


• INITCAP (tekst)

– Zamienia wszystkie pierwsze litery każdego słowa na duże

– Zamienia pozostałe litery parametru tekstowego na małe

INITCAP(‘konferenCJA pRASOWA‘) zwróci ‘Konferencja Prasowa’

18


• LTRIM(tekst[, znaki_do_usunięcia])

– Usuwa wszystkie wyspecyfikowane znaki z lewej strony parametru tekstowego

np.

LTRIM (‘babcABC TESTa','abc‘) zwróci: 'ABC TESTa'

Domyślnymi znakami do usunięcia są spacje:

LTRIM (‘ TEST ') zwróci: ‘TEST ’

19


• RTRIM(tekst[, znaki_do_usunięcia])

– Usuwa wszystkie wyspecyfikowane znaki z prawej strony parametru tekstowego

np.

RTRIM (‘abc TESTcaba','abc‘) zwróci: abc TEST'

Domyślnymi znakami do usunięcia są spacje:

RTRIM (‘ TEST ‘) zwróci: ‘ TEST’

20


• TRIM([[LEADING|TRAILING|BOTH] [znak] FROM ] tekst)

– Usuwa wyspecyfikowany znaki z lewej, z prawej lub z obu stron parametru tekstowego

np.

TRIM(BOTH ‘a‘ FROM 'abcaa‘) zwróci: ‘bc'

Domyślnym znakiem do usunięcia jest pojedyncza spacja:

TRIM(‘ TEST ‘) zwróci: ‘TEST’

21


• LENGTH (tekst)

– Zwraca długość parametru tekstowego (ilość znaków)

LENGTH(‘abcde’) zwróci 5

22


• INSTR(tekst, fragment [, odkąd_przeglądać[, które_wystąpienie]]) – Wyszukuje zadany fragment w tekście i zwraca

jego pozycję

– Parametr tekst jest wartością tekstową do przeszukania

– Parametr fragment jest poszukiwaną wartością tekstową

INSTR(‘Litwo, ojczyzno’,’czy’)

INSTR(‘Litwo, ojczyzno’,’o’)

23


• INSTR(tekst, fragment [, odkąd_przeglądać[, które_wystąpienie]]) – Parametr odkąd_przeglądać jest wskazuje miejsce, od

którego funkcja zacznie przeglądać tekst w poszukiwaniu zadanego fragmentu. Parametr ten jest parametrem opcjonalnym, gdy ominięty – domyślną wartością jest 1.

– Gdy Parametr odkąd_przeglądać jest ujemny, miejsce, od którego funkcja zacznie przeglądać tekst wyznaczane jest od końca, a tekst szukany jest do początku.

INSTR(‘Litwo, ojczyzno’,’o’,6) INSTR(‘Litwo, ojczyzno’,’o’,-5)

24


• INSTR(tekst, fragment [, odkąd_przeglądać[, które_wystąpienie]])

– Parametr które_wystąpienie wskazuje numer wystąpienia fragmentu w tekście. Parametr ten jest parametrem opcjonalnym, gdy ominięty – domyślną wartością jest 1.

INSTR(‘Litwo, ojczyzno’,’o’,1,3)

INSTR(‘Litwo, ojczyzno’,’o’,-1,2)

25


• SUBSTR(tekst, odkąd_wyciąć[, ilość_znaków]) – zwraca fragment tekstu – parametr tekst jest źródłową wartością tekstową – parametr odkąd_wyciąć wskazuje początkową pozycję np. SUBSTR(‘abcdefgh’,3,4) zwróci ‘cdef’ – Parametr ilość_znaków jest parametrem opcjonalnym,

gdy ominięty - funkcja zwraca cały tekst do końca np. SUBSTR(‘abcdefgh’,3) zwróci ‘cdefgh’ – gdy parametr odkąd_wyciąć jest ujemny, pozycja ta

jest liczona od końca np. SUBSTR(‘abcdefgh’,-5,2) zwróci ‘de’

26


• LPAD(tekst, długość[, tekst2])

– Zwraca parametr tekst lewostronnie, uzupełniony do długości zadanej w parametrze długość, ciągami znaków parametru tekst2

lpad('ABC',10,'123') zwraca ‘1231231ABC’

– Domyślnym parametrem tekst2 jest spacja

lpad(‘test',10) zwraca ‘ test’

27


• RPAD(tekst, długość[, tekst2])

• Zwraca parametr tekst lewostronie uzupełniony do długości długość ciągami znaków parametru tekst2

rpad('ABC',10,'123') zwraca ABC1231231

• Domyślnym parametrem tekst2 jest spacja

lpad(‘test',10) zwraca ‘test ’

28

Funkcje konwertujące

• TO_CHAR(liczba[, format[, ustawienie_NLS]])

– Konwertuje liczbę na tekst zgodnie z zadanym formatem

Liczba Format Wynik

-12345 99999999 -12345

0 99.99 .00

12.134 999.99 12,134

29


• TO_NUMBER(tekst[,format[,ustawienie_NLS]])

Tekst Format Wynik

12345- 99999999MI -12345

0 99.99 0

12.134 999.9999 12.134

30


• Formaty liczb

Format Przykład Znaczenie

9 9999 Oznacza cyfrę

0 9900 Oznacza cyfrę lub zero. Przykład zawsze zwróci co najmniej 2

cyfry

. 999.99 Oznacza znak ‘.’ dziesiętny

, 999,999,999.99 Oznacza znak ‘,’ grupujący

D 999D99 Oznacza znak dziesiętny zgodny z ustawieniami sesji

G 999G999G999D99 Oznacza znak grupujący zgodny z ustawieniami sesji

31


• Formaty liczb

Format Przykład Znaczenie

S 99900D00S Znak liczby + lub -

MI 99900D00MI Minus w przypadku liczby ujemnej, spacja w przypadku

liczby dodatniej

L, C 99900L Oznacza kod waluty

FM FM99900D00 Usuwa spacje z początku przekonwertowanego ciągu

RN RN Liczba pisana cyframi rzymskimi

32


• TO_CHAR(data[, format[, ustawienie_NLS]])

– Konwertuje parametr daty na tekst zgodnie z formatem

Przykład Wynik

TO_CHAR(SYSDATE,’YYYY’) 2012

TO_CHAR(SYSDATE,’MM’) 03

TO_CHAR(SYSDATE,’YYYY-MM’) 2012-03

TO_CHAR(SYSDATE,'YYYY !@#$%^&*()-=_+ MM') 2012 !@#$%^&*()-=_+ 03

33


• TO_DATE(tekst[, format[, ustawienie_NLS]])

– Konwertuje parametr tekstowy zgodny z formatem na datę

Przykład Wynik

TO_DATE(‘2012-03-07’,’YYYY-MM-DD’) 01-03-2012 00:00:00

34


• Formaty dat

Format Znaczenie Przykład

YYYY Rok 4-cyfrowy 2012

YYY, YY, Y 3, 2 lub1 najmniej znacząca cyfra roku 012, 12, 2

Q Kwartał (1-4) 1

MM Numer miesiąca (’01’-’12’; Styczeń = ’01’). 03

MON Skrócona nazwa miesiąca MAR

MONTH Długa nazwa miesiąca MARZEC

Month Długa nazwa miesiąca Marzec

RM Miesiąc rzymski (Styczeń = ‘I’) III 35


• Formaty dat


WW Tydzień roku 16

W Tydzień miesiąca 1

D Dzień tygodnia 1-7 (niedziela= 1) 7

DAY Nazwa dnia SOBOTA

DD Dzień miesiąca 01-31 03

DDD Dzień roku (001-366). 071

DY Skrócona nazwa dnia SO

36


• Formaty dat


HH Godzina 01-12 04

HH12 Godzina 01-12 04

HH24 Godzina 00-23 16

MI Minuta(00-59) 25

SS Sekunda (00-59). 43

SSSSS Sekunda dnia liczona od północy (0-86399). 59126

37


• TO_CHAR(data[, format[, ustawienie_NLS]])

– Parametr ustawienie_NLS jest parametrem niewymagalnym, zawiera informację o ustawieniach językowych, z którymi funkcja ma być wykonana

to_char(sysdate, 'MONTH', ‘NLS_DATE_LANGUAGE=''Polish''')

-> zwróci ‘LUTY’

to_char(sysdate, 'MONTH‘)

to_char(sysdate, 'MONTH', ‘NLS_DATE_LANGUAGE=''English''')

-> zwrócą ‘FEBRUARY ‘

38

Funkcje dodatkowe

• USER

• Funkcja bezparametrowa zwracająca nazwę użytkownika bazodanowego bieżącej sesji

SELECT USER FROM DUAL;

39

Funkcje dodatkowe

• NVL(parametr1, parametr2) • Zwraca wartość parametru1, lub w przypadku

gdy jest on NULL, zastępuje go parametrem2

np. NVL(ls_adres_zamieszkania,ls_adres_zameldowania)

parametr1 Wynik

Nie NULL parametr1

NULL parametr2

40

Funkcje dodatkowe

• COALESCE(parametr1, parametr2[, parametr3...])

• Zwraca wartość pierwszego parametru, który nie jest NULL

• np. COALESCE(ls_nr_komorkowy, ls_nr_stacjonarny, ls_nr_sluzbowy, ls_nr_do_zony)

41

Funkcje dodatkowe

• NVL2(parametr1, wartość_gdy_nie_null, wartość_gdy_null)

• Zwraca wartość_gdy_nie_null lub wartość_gdy_null w zależności od parametru parametr1

NVL2(ld_data_konca,’Zakonczono’,’Trwa’)

parametr1 Wynik

Nie NULL wartość_gdy_nie_null

NULL wartość_gdy_null

42

Funkcje dodatkowe

• NULLIF(parametr1,parametr2)

• Zwraca NULL, gdy parametry są równe. W przeciwnym wypadku zwraca parametr1.

NULLIF(ls_drugie_imie,’brak’)

43

Funkcje dodatkowe

• DECODE(parametr0, szukana1, wartość1,

[, szukana2, wartość2]…[, wartość_domyślna])

• parametr0 jest wartością porównywaną

• szukana1 jest wartością, do której następuje porównanie

• wartość1 jest wartością zwracaną, gdy parametr0=szukana1

• Wartość_domyślna jest niewymagalną wartością zwracaną, gdy parametr0 nie jest żadną z szukanych

DECODE(ls_plec,’M’,’Mezczyzna’,’K’,’Kobieta’,’Nieznana’);

44

Funkcje dodatkowe

• Funkcje dodatkowe NVL, NVL2, DECODE, COALESCE, NULLIF są przeciążone i zwracają:

– datę, gdy parametrami są daty

– liczbą, gdy parametrami są liczby

– tekst, gdy parametrami są zmienne tekstowe

45

Funkcje matematyczne

• ABS(x)

• Zwraca wartość bezwzględną parametru x:

• ABS(-1.4) zwraca 1.4

46


• SIGN(x)

• Zwraca wartość wskazującą znak parametru x

Warunek Wynik

x<0 -1

x=0 0

x>0 1

47


• CEIL(x)

• Zwraca najmniejszą całkowitą wartość większą od x

CEIL(2.4) zwraca 3

CEIL(-1.6) zwraca -1

• FLOOR(x)

• Zwraca największą całkowitą wartość mniejszą od x

FLOOR(2.4) zwraca 2

FLOOR(-1.6) zwraca -2

48


• ROUND(x[,ilość_miejsc_dziesiętnych]) • Zaokrągla liczbę x do żądanej ilość miejsc

dziesiętnych ROUND(126.1251, 2) zwraca 126.13 ROUND(126.1251, -1) zwraca 130 • Domyślną wartością parametru

ilość_miejsc_dziesiętnych jest 0 ROUND(2.4) zwraca 2 ROUND(-1.6) zwraca -2

49


• TRUNC(x[,ilość_miejsc_dziesiętnych]) • Obcina liczbę x do żądanej ilości miejsc dziesiętnych

(obcina końcówkę) TRUNC(126.1251, 2) zwraca 125.12 TRUNC(-126.1251, -1) zwraca -120 • Domyślną wartością parametru

ilość_miejsc_dziesiętnych jest 0, co oznacza usunięcie części ułamkowej

TRUNC(2.4) zwraca 2 TRUNC(-1.6) zwraca -1

50


• GREATEST(x1, x2[, x3 …])

• Zwraca największą spośród liczb

GREATEST(11,12,13,10) zwraca 13

• LEAST(x1, x2[, x3 …])

• Zwraca najmniejszą spośród liczb

LEAST(8,4,5,6) zwraca 4

51


• MOD(n,m)

• Zwraca resztę z dzielenia liczb n i m

IF MOD(ln_wiek,10) = 0 THEN DBMS_OUTPUT.PUT_LINE(‘Okragla rocznica!’); END IF;

52


• SQRT(n), POWER(n,m)

• SIN(x), COS(x), TAN(x)

• ASIN(x), ACOS(x), ATAN(x)

• SINH(x), COSH(x), TANH(x)

• LN(x), EXP(x)

53

Funkcje daty

• SYSDATE

• Funkcja bezparametrowa

• Zwraca bieżącą datę

BEGIN dbms_output.put_line(‘Dzisiejsza data: ‘ || TO_CHAR(SYSDATE)); END;

54

Funkcje daty

• ADD_MONTHS(data, ilość_miesięcy)

• Zwraca datę późniejszą o ilość_miesięcy

Przykład Wynik

add_months(DATE ‘2012-01-01', 3) 2012-04-01

add_months(DATE '2012-01-01', -2) 2011-11-01

add_months(DATE '2012-01-31', 1) 2012-02-29

add_months(DATE '2012-02-28', 1) 2012-03-28

add_months(DATE '2012-02-29', 1) 2012-03-31

55

Funkcje daty

• MONTHS_BETWEEN

• Zwraca ilość miesięcy pomiędzy datami

MONTHS_BETWEEN(DATE '2012-01-15',DATE '2011-02-15')

MONTHS_BETWEEN(DATE '2012-02-01',DATE '2012-01-15')

56

Funkcje daty

• LAST_DAY(data)

• Zwraca ostatni dzień miesiąca

Przykład Wynik

LAST_DAY(DATE ‘2012-01-01') 2012-01-31

LAST_DAY(DATE '2012-02-15') 2012-02-29

57

Funkcje daty

• ROUND(data[, stopień])

• Zaokrągla datę do odpowiedniego stopnia zaokrąglenia

Przykład Wynik

ROUND(DATE ‘2012-02-12', ‘MM’) 2012-02-01

ROUND(DATE '2012-02-21', ‘MM’) 2011-03-01

ROUND(DATE '2012-02-31',’YYYY’) 2012-01-01

ROUND(TO_DATE(‘2012-01-12 15:14:13’, ’YYYY-MM-DD HH24:MI:SS’))

2012-01-13

58

Funkcje daty

• TRUNC(data[, stopień])

• Obcina datę do odpowiedniego stopnia zaokrąglenia

Przykład Wynik

TRUNC(DATE ‘2012-02-12', ‘MM’) 2012-02-01

TRUNC(DATE '2012-02-21', ‘MM’) 2011-02-01

TRUNC(DATE '2012-02-31',’YYYY’) 2012-01-01

TRUNC(TO_DATE(‘2012-01-12 15:14:13’, ’YYYY-MM-DD HH24:MI:SS’))

2012-01-12

59

Funkcje daty

Stopień zaokrąglenia/obcięcia Parametr stopień

Rok YYYY, YY

Kwartał Q

Miesiąc MONTH, MM

Dzień DD

Pierwszy dzień tygodnia DAY, DY, D

Godzina HH, HH12, HH24

Minuta MI

• Możliwe stopnie zaokrąglenia i obcięcia dat:

60

PL/SQLmst.webd.pl/_sggw/plsql/wyk_ad_1.pdf · relacja (tabela) id_osoby imie_osoby data_urodzenia 1...

Documents

Transcript of PL/SQLmst.webd.pl/_sggw/plsql/wyk_ad_1.pdf · relacja (tabela) id_osoby imie_osoby data_urodzenia 1...