Price J. - Oracle Database 12c i SQL. Programowanie.pdf

dareks_ EBooki Infornatyka

Użytkownik dareks_ wgrał ten materiał 6 lata temu. Od tego czasu zobaczyło go już 532 osób, 266 z nich pobrało dokument.

Komentarze i opinie (0)

Transkrypt ( 25 z dostępnych 524 stron)

STRONA 1

O R A C L E Oracle Database 12c iSQL Programowanie Jason Price HeliorTtf Oracle Press

STRONA 2

Spis treści W p row ad zen ie .................................................................................................................................................... 19 1 W p r o w a d z e n ie ....................................................................................................................................................23 C zym jest relacyjna baza d a n y ch ? ....................................................................................................................23 W stęp d o S Q L ...................................................................................................................................................... 24 U żyw anie SQ L*Plus ............................................................................................................................................25 Urucham ianie S Q L *P lu s ...........................................................................................................................25 Urucham ianie SQ L*Plus z wiersza poleceń ........................................................................................26 W ykon yw an ie instrukcji SELECT za p om ocą SQL*Plus ....................................................................26 SQL D eveloper ....................................................................................................................................................27 T w orzen ie schematu bazy danych sklepu ..................................................................................................... 30 Zawartość skryptu ..................................................................................................................................... 30 Urucham ianie skryptu .............................................................................................................................. 31 Instrukcje D D L używane d o tw orzenia schematu bazy danych sklepu ........................................ 32 D odawanie, m odyfikow anie i usuwanie w ie r s z y ......................................................................................... 38 D odaw anie wiersza d o tabeli ................................................................................................................. 38 M odyfikow an ie istniejącego w iersza w tabeli ......................................................................................39 Usuwanie wiersza z tabeli .......................................................................................................................40 Łączenie z bazą danych i rozłączanie ............................................................................................................ 40 Kończenie pracy SQ L*Plus ............................................................................................................................... 40 W p row a d zen ie d o O racle PL/SQ L.................................................................................................................. 41 Podsum owanie ....................................................................................................................................................41 2 P ob ieran ie in form acji z ta bel bazy d a n y c h ................................................................................................ 43 W ykon yw an ie instrukcji SELECT dla jednej tabeli .......................................................................................43 Pobieranie wszystkich kolumn z tabeli ...........................................................................................................44 W ykorzystanie klauzuli W HERE d o wskazywania w ierszy d o pobrania ................................................ 44 Identyfikatory w ie rs z y .........................................................................................................................................44 N um ery w ierszy ...................................................................................................................................................45 W ykon yw an ie działań arytm etycznych ..........................................................................................................45 W ykon yw an ie obliczeń na datach .........................................................................................................46 Korzystanie z kolumn w obliczeniach ...................................................................................................47 Kolejność w ykonyw ania działań ............................................................................................................ 48 U żyw anie aliasów kolumn .................................................................................................................................48 Łączenie wartości z kolumn za p om ocą konkatenacji ............................................................................... 49 W artości null .........................................................................................................................................................49 W yśw ietlanie unikatowych w ie r s z y ................................................................................................................. 50 Porów nyw anie w a rto ś ci......................................................................................................................................51 O perator < > ..............................................................................................................................................51 O perator > ................................................................................................................................................. 52 O perator < = ..............................................................................................................................................52 O perator A N Y .............................................................................................................................................52 O perator ALL ..............................................................................................................................................52

STRONA 3

e Oracle Database 12c i SQL. Programowanie Korzystanie z operatorów SQL .........................................................................................................................53 O perator LIK E ............................................................................................................................................. 53 O perator IN .................................................................................................................................................54 O perator B E TW E EN .................................................................................................................................. 55 O peratory logiczne ............................................................................................................................................. 55 O perator A N D ............................................................................................................................................55 O perator O R .............................................................................................................................................. 56 Następstwo operatorów ........................................................................................................................... 56 Sortow anie w ierszy za p om ocą klauzuli O RD ER BY .................................................................................. 57 Instrukcje SELECT wykorzystujące d w ie tabele ............................................................................................58 U żyw anie aliasów tabel .....................................................................................................................................59 Iloczyny kartezjańskie ........................................................................................................................................ 60 Instrukcje SELECT wykorzystujące w ięcej niż dw ie ta b e le .........................................................................60 W arunki złączenia i typy złączeń ....................................................................................................................61 N ierów n ozłączen ia ....................................................................................................................................61 Złączenia zew n ętrzn e ...............................................................................................................................62 Złączenia własne ....................................................................................................................................... 65 W ykon yw an ie złączeń za p om ocą składni SQL/92 .....................................................................................66 W ykon yw an ie złączeń w ew nętrznych dw óch tabel z wykorzystaniem składni SQL/92 .......... 66 U praszczanie złączeń za p om ocą słowa kluczow ego U SIN G ......................................................... 67 W ykon yw an ie złączeń w ew nętrznych obejm ujących w ięcej niż d w ie tabele (SQL/92) .......... 67 W ykon yw an ie złączeń w ew nętrznych z użyciem wielu kolumn (S Q L/92 ).................................. 68 W ykon yw an ie złączeń zew nętrznych z użyciem składni SQL/92 ..................................................68 W ykon yw an ie złączeń własnych z użyciem składni SQL/92 .......................................................... 69 W ykon yw an ie złączeń krzyżowych z użyciem składni SQL/92 ......................................................70 Podsum owanie .................................................................................................................................................... 70 3 SQ L*Plus .............................................................................................................................................................. 71 Przeglądanie struktury tabeli .............................................................................................................................71 Edycja instrukcji SQL ..........................................................................................................................................72 Zapisywanie, odczytyw anie i uruchamianie plików ....................................................................................73 Form atowanie kolumn ....................................................................................................................................... 76 Ustawianie rozmiaru stro n y ...............................................................................................................................77 Ustawianie rozmiaru w ie rs z a ............................................................................................................................ 78 Czyszczenie form atow ania kolumny .............................................................................................................. 78 U żyw anie zm iennych .........................................................................................................................................79 Zm ien n e tym czasow e ...............................................................................................................................79 Zm ien n e zd efin iow an e ............................................................................................................................ 81 T w orzen ie prostych ra p o rtó w ........................................................................................................................... 83 U żyw anie zm iennych tym czasowych w skrypcie ...............................................................................83 U żyw anie zm iennych zdefiniow anych w skrypcie ............................................................................ 84 Przesyłanie wartości d o zm iennej w skrypcie .....................................................................................84 D odaw anie nagłówka i stopki ................................................................................................................ 85 O bliczan ie sum p ośred n ich ..................................................................................................................... 86 Uzyskiwanie pom ocy od SQ L*Plus ................................................................................................................ 87 Autom atyczne generow anie instrukcji SQL ..................................................................................................88 Kończenie połączenia z bazą danych i pracy SQ L*Plus ............................................................................ 88 Podsum owanie .................................................................................................................................................... 89 4 Proste fu n k c je ......................................................................................................................................................91 Typy funkcji .......................................................................................................................................................... 91 Funkcje je d n o w ie rs z o w e ....................................................................................................................................91 Funkcje z n a k o w e ....................................................................................................................................... 92 Funkcje n u m eryczn e................................................................................................................................. 98

STRONA 4

Funkcje konw ertujące ............................................................................................................................103 Funkcje w yrażeń regularnych ...............................................................................................................112 Funkcje ag reg u ją ce............................................................................................................................................117 A V G ()..........................................................................................................................................................118 C O U N T () ...................................................................................................................................................119 M AX() i M I N ()............................................................................................................................................119 S T D D E V ()...................................................................................................................................................120 SU M () .........................................................................................................................................................120 VARIANCEO ..............................................................................................................................................120 G rupow anie w ie rs z y ......................................................................................................................................... 120 G rupow anie w ierszy za p om ocą klauzuli G R O U P B Y ....................................................................120 N iepraw id łow e użycie funkcji agregujących .................................................................................... 123 Filtrowanie grup w ierszy za p om ocą klauzuli H A V IN G ................................................................. 124 Jednoczesne używanie klauzul W HERE i G R O U P BY ....................................................................124 Jednoczesne używanie klauzul WHERE, G R O U P BY i H A V IN G ..................................................125 Podsum owanie ..................................................................................................................................................125 5 Składow anie o ra z p rzetw arzan ie dat i czasu .......................................................................................... 127 Proste przykłady składowania i pobierania d a t .......................................................................................... 127 Konw ertow anie typ ów DataG odzina za p om ocą funkcji T O _C H A R () i T O _ D A T E ()........................ 128 Konwersja daty i czasu na napis za p om ocą funkcji T O _C H A R () ................................................ 128 Konwersja napisu na w yrażenie DataG odzina za p om ocą funkcji T O _D A TE () ....................... 132 Ustawianie dom yślnego formatu d a t y ..........................................................................................................134 Jak O racle interpretuje lata dw ucyfrow e? ...................................................................................................135 U życie formatu YY ..................................................................................................................................135 U życie formatu R R ..................................................................................................................................136 Funkcje operujące na datach i godzinach ...................................................................................................137 A D D _M O N T H S () .................................................................................................................................... 138 LAST_D AY() ..............................................................................................................................................138 M O N T H S _B E T W E E N ()...........................................................................................................................138 N E X T _D A Y ()..............................................................................................................................................139 R O U N D ()...................................................................................................................................................139 S YS D A TE ....................................................................................................................................................140 T R U N C () ....................................................................................................................................................140 Strefy czasow e ...................................................................................................................................................140 Funkcje operu jące na strefach czasow ych ........................................................................................141 Strefa czasow a bazy danych i strefa czasow a sesji ...........................................................................141 U zyskiwanie przesunięć strefy czasow ej ............................................................................................142 U zyskiwanie nazw stref c za s o w y c h ..................................................................................................... 143 Konw ertow anie w yrażenia DataG odzina z jednej strefy czasow ej na inną .............................. 143 Datowniki (znaczniki czasu) ...........................................................................................................................143 Typy d a to w n ik ó w .....................................................................................................................................144 Funkcje operujące na znacznikach c za s u .......................................................................................... 147 Interwały czasow e .............................................................................................................................................151 Typ INTERVAL YEAR T O M O N T H ..................................................................................................... 152 Typ INTERVAL D A Y T O S E C O N D ...................................................................................................... 153 Funkcje operu jące na interwałach ...................................................................................................... 155 Podsum ow anie ..................................................................................................................................................156 6 P od zapytan ia .................................................................................................................................................... 157 R odzaje p od za p y ta ń ......................................................................................................................................... 157 Pisanie podzapytań je d n o w ie rs zo w y c h ........................................................................................................157 Podzapytania w klauzuli W H E R E .........................................................................................................157 U życie innych operatorów jedn ow ierszow ych .................................................................................158 Spis treści 7

STRONA 5

Podzapytania w klauzuli H A V IN G ....................................................................................................... 159 Podzapytania w klauzuli FROM (widoki w b u d ow ane) ...................................................................160 Błędy, które m ożna napotkać .............................................................................................................. 160 Pisanie podzapytań w ielow ierszow ych ........................................................................................................ 161 U życie operatora IN z podzapytaniem w ie lo w ie rszo w y m ............................................................. 161 U życie operatora A N Y z podzapytaniem w ielow ierszow ym ........................................................ 162 U życie operatora ALL z podzapytaniem w ielow ierszow ym ......................................................... 163 Pisanie podzapytań w ielokolu m now ych ......................................................................................................163 Pisanie podzapytań skorelowanych .............................................................................................................. 163 Przykład podzapytania s k o re lo w a n e g o ...............................................................................................163 U życie operatorów EXISTS i N O T EXISTS z podzapytaniem skorelowanym .............................164 Pisanie zagnieżdżonych podzapytań ............................................................................................................166 Pisanie instrukcji UPDATE i DELETE zaw ierających podzapytania .......................................................167 Pisanie instrukcji UPDATE zaw ierającej p o d za p y ta n ie...................................................................167 Pisanie instrukcji DELETE zaw ierającej podzapytanie .................................................................... 168 Przygotow yw anie p od za p y ta ń ........................................................................................................................168 Podsum owanie .................................................................................................................................................. 169 7 Z apytan ia z a a w a n s o w a n e ..............................................................................................................................171 O peratory zestawu ...........................................................................................................................................171 Przykładow e ta b e le ................................................................................................................................. 171 O perator U N IO N ALL ........................................................................................................................... 172 O perator U N IO N ....................................................................................................................................173 O perator IN TE RSE C T..............................................................................................................................174 O perator M IN U S .....................................................................................................................................174 Łączenie operatorów zestawu .............................................................................................................. 175 U życie funkcji TRANSLATE() .......................................................................................................................... 176 U życie funkcji DECODE() ...............................................................................................................................177 U życie w yrażenia CASE ...................................................................................................................................178 Proste w yrażenia CASE .......................................................................................................................... 179 Przeszukiwane w yrażenia CASE ...........................................................................................................179 Zapytania h ierarch iczn e...................................................................................................................................181 Przykładow e dane ...................................................................................................................................181 Zastosow anie klauzul C O N N E C T BY i START W IT H ..................................................................... 182 U życie pseudokolum ny LEVEL .............................................................................................................183 Form atowanie w yników zapytania h ierarch iczn ego........................................................................183 R ozpoczynanie od w ęzła innego niż głów ny ....................................................................................184 U życie podzapytania w klauzuli START W IT H ................................................................................ 185 Poruszanie się po d rzew ie w górę .......................................................................................................185 Elim inowanie w ę złó w i gałęzi z zapytania h ierarch iczn ego...........................................................185 U m ieszczanie innych w arunków w zapytaniu hierarch icznym .....................................................186 Zapytania hierarchiczne wykorzystujące rekurencyjne podzapytania p rzy g oto w y w a n e 187 Klauzule R O LLU P i CUBE ...............................................................................................................................190 Przykładow e ta b e le ................................................................................................................................. 190 U życie klauzuli R O L L U P ........................................................................................................................192 Klauzula CUBE .........................................................................................................................................194 Funkcja G R O U P IN G ().............................................................................................................................195 Klauzula G R O U P IN G SETS ...................................................................................................................197 U życie funkcji G R O U P IN G _ID () ......................................................................................................... 198 Kilkukrotne użycie kolumny w klauzuli G R O U P B Y ........................................................................199 U życie funkcji G R O U P _ID () ................................................................................................................ 200 U życie CROSS A PPLY i O U TER A PPLY .......................................................................................................201 CROSS A PPLY ..........................................................................................................................................201 O U TE R A P P L Y ..........................................................................................................................................202 8 Oracle Database 12c i SQL. Programowanie

STRONA 6

LATERAL ..............................................................................................................................................................202 Podsum owanie ................................................................................................................................................. 203 8 A n a liza d a n y c h ..................................................................................................................................................205 Funkcje a n a lity czn e.......................................................................................................................................... 205 Przykładowa tabela .................................................................................................................................205 U życie funkcji klasyfikujących ............................................................................................................. 206 U życie odw rotnych funkcji rankingowych ........................................................................................212 U życie funkcji o k n a .................................................................................................................................212 Funkcje raportujące ............................................................................................................................... 218 U życie funkcji LAG() i LEAD() ..............................................................................................................220 U życie funkcji FIRST i L A S T ................................................................................................................. 221 U życie funkcji regresji liniowej ........................................................................................................... 221 U życie funkcji hipotetycznego rankingu i rozkładu ........................................................................ 222 U życie klauzuli M O D E L ..................................................................................................................................223 Przykład zastosowania klauzuli M O D E L ............................................................................................223 Dostęp d o kom órek za p om ocą zapisu pozycyjn ego i s y m b o lic zn e g o .......................................224 U zyskiwanie dostępu d o zakresu kom órek za p om ocą BETWEEN i A N D .................................225 Sięganie d o wszystkich kom órek za p om ocą A N Y i IS A N Y ..........................................................225 Pobieranie bieżącej wartości wym iaru za p om ocą funkcji CU RREN TV() ..................................226 U zyskiwanie dostępu d o kom órek za p om ocą pętli FO R ............................................................. 227 Obsługa wartości N U LL i brakujących ...............................................................................................227 M odyfikow an ie istniejących k o m ó re k ................................................................................................ 229 U życie klauzul PIV O T i U N P IV O T ................................................................................................................230 Prosty przykład klauzuli P IV O T ............................................................................................................ 230 Przestawianie w oparciu o w iele kolumn .......................................................................................... 231 U życie kilku funkcji agregujących w przestawieniu ........................................................................ 232 U życie klauzuli U N P IV O T .....................................................................................................................233 Zapytania o określoną liczbę w ie r s z y ........................................................................................................... 234 U życie klauzuli FETCH FIRST ...............................................................................................................234 U życie klauzuli OFFSET ........................................................................................................................ 235 U życie klauzuli PE R C E N T ......................................................................................................................236 U życie klauzuli W IT H T IE S ....................................................................................................................236 O dnajdyw anie w zo rcó w w danych ..............................................................................................................237 O dnajdyw anie w zo rcó w form acji typu V w danych z tabeli all_sales2 ..................................... 237 O dnajdyw anie form acji typu W w danych ztabeli all_sales3 ........................................................240 O dnajdyw anie form acji typu V w tabeli all_sales3 ......................................................................... 241 Podsum owanie ................................................................................................................................................. 242 9 Z m ien ia n ie zaw a rtości t a b e li....................................................................................................................... 243 W staw ianie w ierszy za p om ocą instrukcji IN S E R T.................................................................................... 243 Pom ijanie listy kolumn ...........................................................................................................................244 Określanie wartości N U LL dla k o lu m n y .............................................................................................244 U m ieszczanie pojedynczych i podw ójnych cu dzysłow ów w wartościach kolumn ................. 245 K opiow anie w ierszy z jednej tabeli d o innej .................................................................................... 245 M odyfikow an ie w ierszy za p om ocą instrukcji U P D A T E ...........................................................................245 Klauzula R ETU RNING ..................................................................................................................................... 246 Usuwanie w ierszy za p om ocą instrukcji DELETE.......................................................................................246 Integralność bazy danych ............................................................................................................................... 247 W ym uszanie w ię z ó w klucza g łó w n e g o ..............................................................................................247 W ym uszanie w ię z ó w kluczy o b c y c h ...................................................................................................247 U życie wartości d om yśln ych ...........................................................................................................................248 Scalanie w ierszy za p om ocą instrukcji M E R G E .......................................................................................... 249 Spis treści 9

STRONA 7

Transakcje b a z o d a n o w e .................................................................................................................................. 251 Zatw ierdzanie i w ycofyw anie transakcji ............................................................................................251 R ozpoczynanie i kończenie transakcji ................................................................................................252 Punkty z a c h o w a n ia ................................................................................................................................. 252 A C ID — właściwości transakcji ............................................................................................................254 Transakcje w spółbieżne .........................................................................................................................254 B lokow anie transakcji ............................................................................................................................ 255 Poziom y izolacji transakcji ....................................................................................................................256 Przykład transakcji SERIALIZABLE .......................................................................................................256 Zapytania retrospektywne ...............................................................................................................................257 Przyznawanie uprawnień d o używania zapytań retrospektywnych ............................................257 Zapytania retrospektywne w oparciu o czas .....................................................................................258 Zapytania retrospektywne z użyciem SCN ....................................................................................... 259 Podsum owanie ..................................................................................................................................................260 10 U żytkow n icy, u praw n ien ia i r o l e ................................................................................................................ 261 Bardzo krótkie w p row adzen ie d o przechow yw ania danych ..................................................................261 U ży tk o w n ic y .......................................................................................................................................................262 T w orzen ie konta użytkownika .............................................................................................................262 Zm ienianie hasła użytkownika .............................................................................................................263 Usuwanie konta użytkownika ..............................................................................................................263 Uprawnienia system ow e ................................................................................................................................. 263 Przyznawanie uprawnień system owych użytkownikowi ............................................................... 263 Sprawdzanie uprawnień system owych przyznanych użytkownikow i ........................................ 264 Zastosow anie uprawnień system owych ............................................................................................. 265 O dbieranie uprawnień system owych .................................................................................................265 Uprawnienia o b ie k to w e .................................................................................................................................. 266 Przyznawanie użytkownikowi uprawnień obiektow ych ................................................................ 266 Sprawdzanie przekazanych uprawnień ............................................................................................. 267 Sprawdzanie otrzym anych uprawnień obiektow ych ...................................................................... 268 Zastosow anie uprawnień o b ie k to w y c h .............................................................................................. 269 Synonim y ..................................................................................................................................................270 Synonim y publiczne ...............................................................................................................................270 O dbieranie uprawnień obiektow ych ..................................................................................................271 R o le ...................................................................................................................................................................... 271 T w orzen ie ról ...........................................................................................................................................271 Przyznawanie uprawnień roli ............................................................................................................... 272 Przyznawanie roli użytkownikowi .......................................................................................................272 Sprawdzanie ról przyznanych użytkownikowi ................................................................................. 272 Sprawdzanie uprawnień system owych przyznanych roli .............................................................. 273 Sprawdzanie uprawnień obiektow ych przyznanych roli ...............................................................274 Zastosow anie uprawnień przyznanych roli ....................................................................................... 275 Aktywacja i deaktywacja ról ..................................................................................................................276 O dbieranie roli ........................................................................................................................................ 276 O dbieranie uprawnień r o li....................................................................................................................276 Usuwanie roli ...........................................................................................................................................277 O bserw acja ........................................................................................................................................................277 U prawnienia w ym agane d o przeprow adzania obserwacji ............................................................277 Przykłady obserwacji ..............................................................................................................................277 Perspektywy zapisu obserwacji ............................................................................................................279 Podsum owanie ..................................................................................................................................................279 10 Oracle Database 12c i SQL. Programowanie

STRONA 8

11 T w o rzen ie ta bel, sekw encji, in dek sów i p e r s p e k ty w ............................................................................281 T a b e le .................................................................................................................................................................. 281 T w orzen ie tabeli ......................................................................................................................................281 Pobieranie informacji o tabelach .........................................................................................................282 Uzyskiwanie informacji o kolumnach w tabeli .................................................................................283 Zm ienianie tabeli .................................................................................................................................... 284 Zm ienianie nazw y tabeli ....................................................................................................................... 291 D odaw anie kom entarza d o tabeli ....................................................................................................... 291 O bcinanie tabeli ..................................................................................................................................... 292 Usuwanie tabeli .......................................................................................................................................292 Typy BIN AR Y_FLO AT i BIN ARY_D O U B LE .......................................................................................292 U życie kolumn DEFAULT O N N U L L ..................................................................................................293 Kolumny n ie w id o c z n e ............................................................................................................................294 S e k w e n c je ........................................................................................................................................................... 296 T w orzen ie sekwencji .............................................................................................................................. 296 Pobieranie informacji o sek w en cja ch ................................................................................................. 298 U żyw anie sekwencji ............................................................................................................................... 298 W ypełnianie klucza głów n ego z użyciem sekwencji ......................................................................300 Określanie dom yślnej wartości kolum ny za p om ocą sekwencji ...................................................300 Kolumny typu ID ENTITY ....................................................................................................................... 301 M odyfikow an ie sekwencji .....................................................................................................................301 Usuwanie sekwencji ............................................................................................................................... 302 Indeksy ................................................................................................................................................................302 T w orzen ie indeksu typu B -drzew o ..................................................................................................... 303 T w orzen ie indeksów opartych na funkcjach .................................................................................... 303 Pobieranie informacji o indeksach ...................................................................................................... 304 Pobieranie informacji o indeksach kolumny .................................................................................... 304 M odyfikow an ie indeksu ........................................................................................................................ 305 Usuwanie indeksu ................................................................................................................................... 305 T w orzen ie indeksu b itm a p o w e g o ........................................................................................................305 P ersp ek tyw y....................................................................................................................................................... 306 T w orzen ie i używanie p ersp ek ty w ...................................................................................................... 307 M odyfikow an ie p ersp ek tyw y................................................................................................................313 Usuwanie perspektyw y ......................................................................................................................... 313 U żyw anie niew idocznych kolumn w perspektywach .....................................................................313 Archiw a m ig a w e k ..............................................................................................................................................314 Podsum owanie ................................................................................................................................................. 316 12 W p ro w a d ze n ie d o p rogram ow an ia w PL/SQL ........................................................................................317 Bloki .....................................................................................................................................................................317 Z m ienne i t y p y ...................................................................................................................................................319 Logika w arunkowa ............................................................................................................................................319 Pętle .....................................................................................................................................................................320 Proste pętle ............................................................................................................................................... 320 Pętle W H ILE .............................................................................................................................................321 Pętle FOR ................................................................................................................................................. 321 Kursory ................................................................................................................................................................322 Krok 1. — deklarow anie zm iennych przechow ujących wartości kolumn .................................322 Krok 2. — deklaracja kursora ...............................................................................................................322 Krok 3. — otw arcie k u rsora.................................................................................................................. 323 Krok 4. — pobieranie w ierszy z kursora ............................................................................................323 Krok 5. — zam knięcie kursora............................................................................................................. 323 Pełny przykład — product_cursor.sql ................................................................................................ 324 Spis treści 11

STRONA 9

1 2 O ra c le D atab ase 12c i SQ L. P ro g ra m o w a n ie Kursory i pętle FOR ................................................................................................................................ 325 Instrukcja O PE N -F O R ............................................................................................................................ 325 Kursory bez ograniczenia ...................................................................................................................... 327 W yjątki ................................................................................................................................................................ 328 W yjątek ZERO _D IVID E .........................................................................................................................330 W yjątek D U P _V A L _O N _IN D E X ......................................................................................................... 330 W yjątek IN V A LID _N U M B E R ............................................................................................................... 330 W yjątek OTHERS ....................................................................................................................................331 P ro c e d u ry ............................................................................................................................................................331 T w orzen ie p ro ce d u ry..............................................................................................................................332 W yw oływ a n ie p ro c e d u ry ...................................................................................................................... 333 U zyskiwanie informacji o procedurach ............................................................................................. 334 Usuwanie p ro ced u ry ...............................................................................................................................335 Przeglądanie b łędów w procedurze ................................................................................................... 335 Fu n k cje................................................................................................................................................................ 335 T w orzen ie funkcji ....................................................................................................................................336 W yw oływ a n ie funkcji ............................................................................................................................ 336 U zyskiwanie informacji o funkcjach ................................................................................................... 337 Usuwanie funkcji .....................................................................................................................................337 P a k iety ..................................................................................................................................................................337 T w orzen ie specyfikacji pakietu ............................................................................................................338 T w orzen ie treści pakietu ........................................................................................................................338 W yw oływ a n ie funkcji i procedur z pakietu ......................................................................................339 U zyskiwanie informacji o funkcjach i procedurach w pakiecie ................................................... 340 Usuwanie pakietu ....................................................................................................................................340 W yzw ala cze .......................................................................................................................................................340 Kiedy uruchamiany jest w yzw alacz .................................................................................................... 340 Przygotow ania d o przykładu w y z w a la c z a ..........................................................................................341 T w orzen ie w yzw alacza .......................................................................................................................... 341 Urucham ianie w yzw alacza ...................................................................................................................343 U zyskiwanie informacji o w yzw alaczach ...........................................................................................343 W łączan ie i w yłączanie w yzw alacza .................................................................................................. 345 Usuwanie w yzw alacza ........................................................................................................................... 345 Rozszerzenia PL/SQL ....................................................................................................................................... 345 Typ SIMPLE_INTEGER ........................................................................................................................... 345 Sekw encje w PL/SQL ..............................................................................................................................346 G enerow an ie natyw nego kodu m aszynow ego z PL/SQL ..............................................................347 Klauzula W IT H ........................................................................................................................................ 347 Podsum owanie ..................................................................................................................................................348 13 O b iek ty bazy d a n y c h ...................................................................................................................................... 349 W p row ad zen ie d o o b ie k tó w .......................................................................................................................... 349 U ruchom ienie skryptu tw orzącego schem at bazy danych o b je c t_s ch e m a ..........................................350 T w orzen ie typ ów obiektow ych ..................................................................................................................... 350 Uzyskiwanie informacji o typach obiektow ych za p om ocą DESCRIBE ................................................351 U życie typów obiektow ych w tabelach bazy danych ...............................................................................352 O biekty kolum now e ...............................................................................................................................352 Tabele obiek tow e ....................................................................................................................................354 Identyfikatory obiek tów i odw ołania obiek tow e ..............................................................................357 Porów nyw anie wartości o b ie k tó w .......................................................................................................359 U życie obiek tów w PL/SQ L............................................................................................................................ 361 Funkcja get_products() ........................................................................................................................... 361 Procedura display_product() ................................................................................................................ 362 Procedura in sert_produ ct()....................................................................................................................363

STRONA 10

Procedura update_product_price() ..................................................................................................... 363 Funkcja g e t_p ro d u ct()............................................................................................................................. 364 Procedura u p d ate_p rod u ct()................................................................................................................. 364 Funkcja g e t_p ro d u ct_ref()......................................................................................................................365 Procedura delete_produ ct() .................................................................................................................. 365 Procedura product_lifecycle() ...............................................................................................................366 Procedura produ ct_lifecycle2() ............................................................................................................ 367 D ziedziczenie t y p ó w ........................................................................................................................................368 Urucham ianie skryptu tw orzącego schem at bazy danych object_schem a2 ............................. 368 D zied ziczen ie a try b u tó w ....................................................................................................................... 369 U życie podtypu zam iast typu n adrzędnego ............................................................................................... 370 Przykłady S Q L .......................................................................................................................................... 370 Przykłady PL/SQL .................................................................................................................................... 371 O biekty N O T SU B STITU TAB LE ........................................................................................................... 371 Inne przydatne funkcje obiek tów ................................................................................................................. 372 Funkcja IS O F() ........................................................................................................................................372 Funkcja TREAT() .......................................................................................................................................375 Funkcja SYS_TYPEID() ............................................................................................................................378 Typy o biek tow e N O T INSTANTIABLE .........................................................................................................378 Konstruktory defin iow an e przez u żytk ow n ik a............................................................................................379 Przesłanianie m etod ......................................................................................................................................... 382 U ogóln ion e w yw oływ an ie .............................................................................................................................. 384 U ruchom ienie skryptu tw orzącego schem at bazy danych object_schem a3 ............................. 384 D ziedziczenie a try b u tó w .......................................................................................................................384 Podsum ow anie ................................................................................................................................................. 385 14 K olekcje ............................................................................................................................................................. 387 Podstaw ow e inform acje o kolekcjach ..........................................................................................................387 Uruchom ienie skryptu tw orzącego schem at bazy danychcollectio n _sch em a .................................... 387 T w orzen ie kolekcji ........................................................................................................................................... 388 T w orzen ie typu V A R R A Y ....................................................................................................................... 388 T w orzen ie tabeli zagnieżdżonej ..........................................................................................................388 U życie kolekcji d o definiow ania kolumny w tabeli ..................................................................................389 U życie typu VAR R AY d o zdefiniow ania kolumny w tabeli ............................................................ 389 U życie typu tabeli zagnieżdżonej d o zdefiniow aniakolumny w tabeli ....................................... 389 Uzyskiwanie informacji o kolekcjach ........................................................................................................... 389 U zyskiwanie informacji o tablicy VAR R AY ........................................................................................389 U zyskiwanie informacji o tabeli zagn ieżdżonej ............................................................................... 390 U m ieszczanie elem en tów w kolekcji ........................................................................................................... 392 U m ieszczanie elem en tów w tablicy V A R R A Y ................................................................................... 392 U m ieszczanie elem en tów w tabeli zagnieżdżonej ......................................................................... 392 Pobieranie elem en tów z kolekcji .................................................................................................................. 392 Pobieranie elem en tów z tablicy V A R R A Y .......................................................................................... 393 Pobieranie elem en tów z tabeli zagn ieżdżonej .................................................................................393 U życie funkcji TABLE() d o interpretacji kolekcji jako seriiw ie r s z y .........................................................394 U życie funkcji TABLE() z typem V A R R A Y .......................................................................................... 394 U życie funkcji TABLE() z tabelą zagn ieżdżon ą .................................................................................395 M odyfikow an ie elem en tów kolekcji ............................................................................................................ 395 M odyfikow an ie elem en tów tablicy V A R R A Y .................................................................................... 396 M odyfikow an ie elem en tów tabeli za g n ie ż d ż o n e j............................................................................396 U życie m etody mapującej d o porów nyw ania zawartościtabel zagnieżdżonych ................................397 U życie funkcji CAST d o konwersji kolekcji z jed n ego typuna inny .......................................................399 U życie funkcji CAST() d o konwersji tablicy V AR R AY natabelę z a g n ie ż d ż o n ą ...........................399 U życie funkcji CAST() d o konwersji tabeli zagn ieżdżonej na tablicę V A R R A Y ..........................400 Spis treści 13

STRONA 11

14 Oracle Database 12c i SQL. Programowanie U życie kolekcji w PL/SQL ...............................................................................................................................400 M anipu low anie tablicą V AR R AY .........................................................................................................400 M anipu low anie tabelą zagn ieżdżoną .................................................................................................402 M etod y operu jące na kolekcjach w PL/SQL .....................................................................................403 Kolekcje w ielo p o zio m o w e ............................................................................................................................. 411 U ruchom ienie skryptu tw orzącego schem at bazy danych collection_schem a2 ...................... 412 Korzystanie z kolekcji w ie lo p o z io m o w y c h ........................................................................................ 412 Rozszerzenia kolekcji w p row a d zon e w O racle Database 1 0 g ...............................................................414 U ruchom ienie skryptu tw orzącego schem at bazy danych collection_schem a3 ...................... 414 Tablice asocjacyjne .................................................................................................................................415 Zm ienianie rozmiaru typu elem entu ..................................................................................................415 Zw iększanie liczby elem en tów w tablicy V A R R A Y ..........................................................................416 U życie tablic VAR R AY w tabelach ty m cza so w ych ...........................................................................416 U życie innej przestrzeni tabel dla tabeli składującej tabelę za g n ie ż d ż o n ą ................................416 Obsługa tabel zagnieżdżonych w standardzie AN SI ....................................................................... 417 Podsum ow anie ..................................................................................................................................................424 15 D u że o b iek ty .................................................................................................................................................... 425 Podstaw ow e inform acje o dużych obiektach (L O B ) .................................................................................425 Przykładow e p lik i.............................................................................................................................................. 425 Rodzaje dużych o b ie k tó w ...............................................................................................................................426 T w orzen ie tabel zawierających du że o b ie k ty .............................................................................................427 U życie dużych ob iek tów w SQL ...................................................................................................................428 U życie obiek tów CLO B i BLOB .......................................................................................................... 428 U życie obiek tów BFILE ..........................................................................................................................430 U życie dużych ob iek tów w PL/SQ L..............................................................................................................431 A P P E N D () ..................................................................................................................................................433 CLOSE() ..................................................................................................................................................... 433 C O M PA RE () .............................................................................................................................................. 434 C O PY() .......................................................................................................................................................435 CREATETEM PORARY() ..........................................................................................................................435 ERASE().......................................................................................................................................................436 FILECLOSE() .............................................................................................................................................436 FILECLOSEALL() ......................................................................................................................................437 FILEEXISTS() .............................................................................................................................................. 437 F ILE G E TN A M E ().......................................................................................................................................437 FILEISOPEN() ............................................................................................................................................438 FILEOPEN() ............................................................................................................................................... 438 FREETEMPORARY() ................................................................................................................................439 G ETCHU NKSIZE() .................................................................................................................................. 439 G ETLENGTH() ......................................................................................................................................... 439 G ET_STO RAG E_LIM IT() .........................................................................................................................440 INSTR() .......................................................................................................................................................440 ISOPEN() ...................................................................................................................................................441 ISTEM PORARY() ......................................................................................................................................441 LO AD FRO M FILE() .................................................................................................................................. 442 LO AD B LO BFRO M FILE() ....................................................................................................................... 443 LO AD C LO BFRO M FILE() ....................................................................................................................... 443 O PE N () .......................................................................................................................................................444 R E A D ()........................................................................................................................................................445 SUBSTR() ...................................................................................................................................................445 TRIM () ........................................................................................................................................................446

STRONA 12

WRITEO .....................................................................................................................................................447 W RITEAPPENDO ..................................................................................................................................... 447 Przykładow e procedury PL/SQL ..........................................................................................................448 Typy LO N G i L O N G R A W .............................................................................................................................462 Przykładow e tabele .................................................................................................................................462 W staw ianie danych d o kolumn typu LO N G i LO N G R A W ..........................................................462 Przekształcanie kolumn LO N G i LO N G R A W w d u że obiekty ....................................................463 N o w e właściwości dużych ob iek tów w O racle Database 10g ...............................................................463 N iejaw na konwersja m iędzy obiektam i CLO B i N C LO B ...............................................................464 U życie atrybutu :new , gdy obiekt LOB jest używany w w yzw alaczu .........................................464 N o w e właściwości dużych ob iek tów w O racle Database 11g ...............................................................465 Szyfrow anie danych LOB ......................................................................................................................465 Kom presja danych L O B ......................................................................................................................... 469 Usuwanie powtarzających się danych LOB ..................................................................................... 469 N o w e właściwości dużych ob iek tów w O racle Database 12c ...............................................................469 Podsum ow anie ................................................................................................................................................. 470 16 O p tym a liza cja SQL ......................................................................................................................................... 471 Podstaw ow e inform acje o optym alizacji SQ L ........................................................................................... 471 N ależy filtrować w iersze za p om ocą klauzuli WHERE .............................................................................471 N ależy używać złączeń tabel zam iast w ielu zapytań ............................................................................... 472 W ykonu jąc złączenia, należy używ ać w pełni kwalifikowanych odw ołań d o kolumn .................... 473 N ależy używać w yrażeń CASE zam iast w ielu zapytań .............................................................................473 N ależy dod ać indeksy d o tabel ......................................................................................................................474 Kiedy tw orzyć indeks typu B -d rz e w o ................................................................................................. 475 Kiedy tw orzyć indeks bitm apow y ....................................................................................................... 475 N ależy stosować klauzulę WHERE zam iast H A V IN G ............................................................................... 475 N ależy używać U N IO N ALL zam iast U N IO N ........................................................................................... 476 N ależy używać EXISTS zam iast IN ................................................................................................................477 N ależy używać EXISTS zam iast D ISTINCT ................................................................................................. 477 N a leży używać G R O U P IN G SETS zam iast CUBE ..................................................................................... 478 N a leży stosować zm ien n e d o w ią z a n e ..........................................................................................................478 N ieiden tyczn e instrukcje SQL ............................................................................................................. 478 Identyczne instrukcje SQL korzystające z e zm iennych d o w ią za n y c h .........................................478 W ypisyw anie listy i wartości zm iennych dow iązanych ..................................................................479 U życie zm iennej dow iązanej d o składowania wartości zw rócon ej przez funkcję PL/SQL ... 480 U życie zm iennej dow iązanej d o składowania w ierszy z R E FC U R SO R ...................................... 480 Porów nyw anie kosztu w ykonania zapytań ................................................................................................. 480 Przeglądanie planów w ykonania .........................................................................................................481 Porów nyw anie planów w ykonania .....................................................................................................485 Przesyłanie w skazów ek d o op tym a liza tora ................................................................................................. 486 D odatkow e narzędzia optym alizujące ........................................................................................................ 487 O racle Enterprise M anager ................................................................................................................... 487 Autom atic Database Diagnostic M onitor .......................................................................................... 488 SQL Tuning Advisor ............................................................................................................................... 488 SQL Access Advisor ............................................................................................................................... 488 SQL Perform ance Analyzer ................................................................................................................. 488 Database Replay ..................................................................................................................................... 488 Real-Tim e SQL M o n ito rin g ................................................................................................................... 488 SQL Plan M a n a g em en t...........................................................................................................................489 Podsum owanie ................................................................................................................................................. 489 Spis treści 15

STRONA 13

16 Oracle Database 12c i SQL. Programowanie 17 XM L i b aza danych O ra cle ........................................................................................................................... 491 W p row ad zen ie d o XML .................................................................................................................................. 491 G enerow anie XML z danych relacyjn ych .................................................................................................... 492 XMLELEMENT() .......................................................................................................................................492 XM LATTRIBU TESO .................................................................................................................................. 494 XMLFOREST() ...........................................................................................................................................494 X M LA G G () .................................................................................................................................................495 XM LC O LA TTV AL0 .................................................................................................................................. 497 X M L C O N C A T ()........................................................................................................................................ 498 XMLPARSE() .............................................................................................................................................498 XM LPI() ..................................................................................................................................................... 499 X M LC O M M E N T () ................................................................................................................................... 499 XMLSEQUENCE() ................................................................................................................................... 500 XM LSERIALIZEO....................................................................................................................................... 501 Przykład zapisywania danych XML d o pliku w PL/SQL ..................................................................501 XM LQ U ERY() ............................................................................................................................................502 Zapisywanie XML w bazie danych ............................................................................................................... 506 Przykładow y plik XML ........................................................................................................................... 506 T w orzen ie przykładow ego schematu XML ....................................................................................... 506 Pobieranie informacji z przykładow ego schematu XML ................................................................ 508 Aktualizow anie informacji w przykładow ym schem acie XML ......................................................511 Podsum owanie ..................................................................................................................................................514 A T yp y danych O ra cle ....................................................................................................................................... 515 Typy w O racle S Q L ...........................................................................................................................................515 Typy w O racle PL/SQ L.....................................................................................................................................517 S k o r o w id z .......................................................................................................................................................... 519

STRONA 14

0 autorze Jason P rice jest n iezależn ym konsu ltantem i b yłym k ierow n ik iem projektu w O racle C orporation. Brał udział w pracach nad w ielom a produktam i Oracle, w tym bazą danych, serw erem aplikacji i kilkom a apli kacjam i C R M . Jest certyfikow an ym adm inistratorem i program istą baz danych O racle i posiada ponad 15-letnie d ośw iadczen ie w b ran ży oprogram ow ania. N apisał w iele książek na tem at O racle, Javy i .N ET. U zyskał tytuł licencjata (z w y różn ien iem ) w d ziedzinie fizyki na brytyjskim U n iversity o f Bristol. Podziękowania D ziękuję wspaniałym ludziom z M cG ra w -H ill Education/Professional. D ziękuję też Scottowi M ik olaitisow i 1N id h i Choprze.

STRONA 15

1 8 O ra c le D atab ase 12c i SQ L. P ro g ra m o w a n ie

STRONA 16

Wprowadzenie W spółcześnie dostęp do system ów zarządzania bazam i danych jest realizow any z użyciem standardow ego języka Structured Q u ery Language (stru kturalnego języka zapytań), czyli SQL. SQ L u m ożliw ia m ięd zy in n ym i pobieranie, wstawianie, m odyfikow anie i usuwanie in form acji z b azy danych. T a książka pozw ala dobrze opanow ać język SQL, a ponadto zaw iera w iele praktycznych przykładów. W szystkie skrypty i p ro gram y prezentow ane w książce są dostępne online (w ięcej in form acji na ten tem at znajduje się w p od ro z dziale „P ob ieran ie przyk ład ów ” ). D zięk i tej książce: ■ O panujesz stan dardow y S Q L, a także je g o rozszerzen ia opracow an e p rzez O racle C orp oration , u m ożliw iające w ykorzystanie specyficznych w łaściw ości b azy danych Oracle. ■ Poznasz język PL/SQL (Procedu ral Language/SQ L), k tóry w y w o d zi się z SQ L i u m ożliw ia pisanie p ro g ra m ów zaw ierających instrukcje SQL. ■ D ow iesz się, jak używać S Q L*Plus do urucham iania instrukcji SQ L, skryptów i raportów . SQ L*Plus jest n arzędziem u m ożliw iającym interakcję z bazą danych. ■ D ow iesz się, jak w ykon yw ać zapytania, wstawiać, m od yfik ow ać i usuwać dane z b azy danych. ■ O panujesz tw orzen ie tabel, sekwencji, indeksów , perspektyw i k on t u żytkow ników . ■ D ow iesz się, jak w ykon yw ać transakcje zaw ierające w iele instrukcji SQL. ■ O panujesz defin iow an ie typów obiektow ych i tw orzen ie tabel obiektow ych do obsługi danych za awansowanych. ■ N auczysz się w ykorzystyw ać duże obiekty do obsługi p lików m ultim edialnych zawierających obra zy, m u zykę i film y. ■ D o w iesz się, jak w ykon yw ać skom plikow ane obliczenia za p om ocą funkcji analitycznych. ■ O panujesz w ysoko w ydajne techniki optym alizacyjne, znacząco przyspieszające w ykon yw an ie in strukcji SQL. ■ Poznasz m ożliw ości obsługi X M L w bazie danych Oracle. ■ D ow iesz się, jak w ykorzystyw ać najnow sze m ożliw ości języka SQ L w p row a dzon e w O racle D ata base 12c. Książka zaw iera 17 ro zd zia łó w i dodatek. Rozdział 1. „Wprowadzenie" W tym rozdziale znajduje się opis relacyjnych baz danych, w p row adzen ie do SQ L i kilka przykładow ych zapytań. N auczym y się w nim rów n ież używać SQL*Plus i SQ L D eveloper do w ykonyw ania zapytań, a tak że krótko o m ó w im y PL/SQL. Rozdział 2. „Pobieranie informacji z tabel bazy danych" D ow iesz się, jak pobrać in form acje z jednej lub kilku tabel, korzystając z instrukcji SELECT. N au czysz się rów n ież używać w yrażeń arytm etycznych do w ykonyw ania obliczeń. Poznasz klauzulę WHERE, u m ożliw ia jącą filtrow an ie w ierszy, a także dow iesz się, jak je sortować.

STRONA 17

20 Oracle Database 12c i SQL. Programowanie Rozdział 3. „SQL*Plus" W tym rozdziale u żyjem y S Q L*Plu s do p rzejrzen ia struktury tabeli, edytow ania instrukcji SQ L, zapisy wania i urucham iania skryptów , form atow an ia k olu m n w yników . N auczysz się rów n ież używać zm ien nych i generow ać raporty. Rozdział 4. „Proste funkcje" W tym rozd zia le p ozn asz kilka fu nkcji w b u d ow a n ych do bazy danych Oracle. Funkcja m oże p rzyjm o wać param etry w ejściow e i zw raca param etr w yjściow y. Funkcje um ożliw iają m ięd zy in n ym i obliczanie średnich i p ierw iastków kw adratow ych. Rozdział 5. „Składowanie oraz przetwarzanie dat i czasu" D o w iesz się, w jaki sposób baza danych O racle przetw arza oraz składuje daty i czas. P ozn asz ró w n ież datow niki u m ożliw iające składow anie określonej daty i czasu, a także in terw a ły czasowe um ożliw iające składowanie okresu. Rozdział 6. „Podzapytania" D ow iesz się, w jaki sposób m ożna um ieścić instrukcję SELECT w zew nętrznej instrukcji SQ L. W ew n ętrzn ą instrukcję S Q L nazyw am y podzapytaniem . Poznasz różne rodzaje podzapytań i zobaczysz, jak um ożliwiają one tw orzen ie złożon ych instrukcji z prostych składników. Rozdział 7. „Zapytania zaawansowane" D ow iesz się, jak w ykonyw ać zapytania zawierające zaawansowane operatory i funkcje, takie jak: operatory zestawu łączące w iersze zwracane przez kilka zapytań, funkcja TRANSLATE() konwertująca znaki w jedn ym napisie na znaki w in n ym napisie, funkcja DECODE() wyszukująca w artość w zestaw ie w artości, w yrażenie CASE w ykonujące logik ę if- th e n - e ls e oraz klauzule ROLLUP i CUBE zw racające w iersze zaw ierające sum y cząstkowe. N o w e w O racle Database 12c klauzule CROSS APPLY i OUTER APPLY łączą w iersze z dw óch w y rażeń SELECT, a LATERAL zw raca w bu dow any w id o k danych. Rozdział 8. „Analiza danych" Poznasz funkcje analityczne um ożliwiające wykonyw anie złożonych obliczeń, takich jak wyszukanie naj lepiej sprzedającego się produktu w poszczególn ych m iesiącach, najlepszych sprzedaw ców itd. D ow iesz się, jak w ykonyw ać zapytania o dane uporządkow ane hierarchicznie. Poznasz rów n ież klauzulę MODEL w y konującą obliczenia m iędzyw ierszow e oraz klauzule PIVOT i UNPIVOT, które um ożliw iają poznanie ogólnych tren d ów w dużych ilościach danych. N o w e w O racle Database 12c klauzule to MATCH_RECOGNIZE u m ożli w iająca odn a lezien ie w zo rca w danych i FETCH_FIRST u m ożliw iająca w ykon an ie zapytań zw racających N pierw szych w ierszy wyniku. Rozdział 9. „Zmienianie zawartości tabeli" D ow iesz się, jak wstawiać, m o d yfik o w a ć i usuwać w iersze za p om ocą instrukcji INSERT, UPDATE i DELETE oraz jak utrw alić w yn ik transakcji, korzystając z instrukcji COMMIT lub w ycofać w yn ik i transakcji za p o m ocą instrukcji ROLLBACK. D o w iesz się rów n ież, w jaki sposób baza danych O racle obsługuje kilka trans akcji w ykon yw an ych w tym sam ym m om encie. Rozdział 10. „Użytkownicy, uprawnienia i role" D ow iesz się, czym są użytkow nicy b azy danych oraz jak upraw nienia i role um ożliw iają określenie czyn ności, k tóre m o że w ykon ać użytkow nik w bazie danych. Rozdział 11. „Tworzenie tabel, sekwencji, indeksów i perspektyw" Poznasz tabele i sekwencje, które generują serie liczb, a także indeksy, które przypom inają indeksy w książ kach i u m ożliw ia ją szybkie uzyskanie dostępu do w ierszy. D ow iesz się rów n ież czegoś o perspektywach, które są w stępnie zdefin iow a n ym i zapytaniam i jednej lub kilku tabel. D o zalet perspektyw m o żem y zali

STRONA 18

Wprowadzenie 21 czyć to, że u m ożliw iają one ukrycie złożon ości przed użytkow nikiem , a także im plem entu ją kolejn y p o zio m zabezpieczeń, zezwalając na przeglądanie jedyn ie ograniczon ego zestawu danych z tabeli. Poznasz rów n ież archiwa m igaw ek. W archiw um m igaw ek są składowane zm ian y dokonane w tabeli w pew nym okresie. N ow ością w O racle Database 12c jest m ożliw ość definiow ania w id oczn ych i n iew idoczn ych k o lum n w tabeli. Rozdział 12. „Wprowadzenie do programowania w PL/SQL" W tym rozdziale poznasz język PL/SQL, zb u d ow an y na podstaw ie SQ L i u m ożliw iający pisanie p rogra m ó w składow anych w bazie danych oraz zaw ierających instrukcje SQ L. Język ten posiada standardowe konstrukty program istyczne. Rozdział 13. „Obiekty bazy danych" D ow iesz się, jak tw orzyć typy obiektow e w bazie danych, które m ogą zaw ierać atrybuty i m etody. Z a p o m ocą tych ty p ó w obiektow ych zd efin iu jem y obiek ty k olu m n ow e i tabele obiektów , a także nauczysz się m anipulow ać obiektam i za p om ocą SQ L i PL/SQL. Rozdział 14. „Kolekcje" D ow iesz się, jak tw orzyć typy kolekcji, które m ogą zaw ierać w iele elem entów . U żyjem y kolekcji do d efi niow ania k olu m n w tabelach. N auczysz się rów n ież m anipulow ać kolekcjam i za p om ocą SQ L i PL/SQL. Rozdział 15. „Duże obiekty" Poznasz duże obiekty, które m ogą przech ow yw ać do 128 terabajtów danych znakow ych i binarnych lub w skazyw ać na p lik zew n ętrzn y, oraz starsze ty p y LONG, obsługiw ane p rzez O racle Database 12c w celu zachow ania kom patybilności z w cześniejszym i w ersjam i. Rozdział 16. „Optymalizacja SQL" T en ro zd zia ł zaw iera kilka w skazów ek pozw alających skrócić czas w ykon yw a n ia zapytań. D o w ie sz się rów n ież czegoś o optym alizatorze Oracle, a także m ożliw ości przesyłania w skazów ek do optym alizatora. W p row ad zo n e zostaną też zaawansowane narzędzia do optym alizacji. Rozdział 17. „XML i baza danych Oracle" Extensible M arkup Language (X M L ) jest językiem zn aczników ogóln ego przeznaczenia. X M L u m ożliw ia przesyłanie ustrukturyzowanych danych w internecie i m oże być używ any do k odow ania danych i innych dokum en tów . Z tego rozd zia łu d ow iesz się, jak gen erow ać k od X M L na podstaw ie danych relacyjnych oraz jak zapisać k od X M L w bazie danych. Dodatek A D odatek zaw iera opis ty p ó w danych dostępnych w O racle SQ L i PL/SQL. Docelowa grupa czytelników T a książka jest odpow iedn ia dla następujących czytelników : ■ program istów , k tórzy chcą pisać w SQ L i PL/SQL, ■ adm inistratorów baz danych wym agających dogłębnej znajom ości SQ L, ■ u żytk ow n ik ów biznesowych, k tórzy muszą pisać zapytania SQ L w celu pobrania in form acji z bazy danych organizacji, ■ k ierow n ik ów technicznych lub konsultantów, k tó rzy potrzebują w p row a dzen ia do SQ L i PL/SQL. N ie jest k on ieczn a w cześniejsza zn ajom ość S Q L i PL/SQL. W szystko, co jest potrzebne do biegłego opanow ania S Q L i PL/SQL, m ożn a znaleźć w tej książce.

STRONA 19

22 Oracle Database 12c i SQL. Programowanie Pobieranie przykładów W szystkie skrypty, program y i inne pliki używane w tej książce m ożna pobrać z serwera FTP W ydaw nictw a H elion , p od adresem: ftp://ftp.helion.pl/przyklady/ord12p.zip. P liki są um ieszczone w archiw um Z IP — po jego rozpakow aniu tw o rzo n y jest katalog sql_book, w k tórym znajdują się następujące podkatalogi: ■ p lik i zaw ierający przykładow e pliki używane w rozdziale 14., ■ S Q L zaw ierający skrypty S Q L używ ane w całej książce, w tym skrypty tw orzące i um ieszczające dane w przykładow ych tabelach, ■ X M L zaw ierający pliki X M L używane w rozdziale 17. M a m nadzieję, że spodoba C i się ta książka!

STRONA 20

ROZDZIAŁ 1 Wprowadzenie W tym rozdziale pozn am y in form acje na temat: ■ relacyjnych baz danych, ■ strukturalnego języka zapytań (S Q L — Structured Q uery Language), używ anego w pracy z bazam i danych, ■ SQ L*Plus — interaktyw nego narzędzia tekstowego do uruchamiania instrukcji SQ L, u tw orzon ego przez O racle, ■ SQ L D eveloper — graficznego narzędzia do tw orzen ia baz danych, ■ PL/SQ L — u tw orzon ego p rzez O racle p rocedu raln ego język a p rogram ow an ia, k tó ry u m ożliw ia tw orzen ie program ów . Czym jest relacyjna baza danych? Założenia relacyjnych baz danych zostały opracow ane w 1970 roku p rzez dr. E.F. C odda. O pisał on teorię relacyjnych baz danych w artykule A R elation al M o d e l o f D ata f o r Large Shared Data Banks, opu blikow a n ym w „C om m u n ication s o f the Association fo r C om p u tin g M ach in ery” (t. 13, nr 6, czerw iec 1970). Podstaw owe założenia relacyjnego m odelu baz danych są stosunkowo łatwe do zrozum ienia. R elacyjna baza danych jest zbiorem pow iązanych inform acji, um ieszczonych w tabelach. D ane w tabeli są p rzech o w yw ane w w iersza ch i uporządkow ane w k olu m nach. Tabele są przechow yw ane w schem atach baz da nych, czyli obszarach, w których użytkownicy m ogą przechow ywać swoje tabele. U żytk ow n ik m oże p rzy dzielać u p raw n ien ia, dzięki k tórym inne osoby będą m o g ły uzyskać do nich dostęp. D an e często p orządku je się w tabelach — cen y akcji czy rozk ład y ja zd y pociągów . W jednej z p rzy k ładow ych tabel w tej książce będą zapisywane in form acje o klientach fikcyjnego sklepu: ich im iona, na zwiska, daty urodzenia (dob — ang. date o f b irth ) i n um ery telefonów . first_name last_name dob phone Jan Nikiel 65/01/01 800-555-1211 Lidia Stal 68/02/05 800-555-1212 Stefan Brąz 71/03/16 800-555-1213 Grażyna Cynk 800-555-1214 Jadwiga Mosiądz 70/05/20 Taka tabela m oże być przechowyw ana: ■ jako tabela w bazie danych, ■ jako plik H T M L na stronie internetow ej, ■ na karcie w pudełku.

STRONA 21

24 Oracle Database 12c SQL N a leży pam iętać, że in form acje tw orzące bazę danych są czym ś in n ym n iż system w ykorzystyw a n y do uzyskiw ania do n ich dostępu. O p rogra m ow a n ie używ ane do pracy z bazą danych n azyw am y syste m em za rzą d zan ia bazą danych. Baza danych O racle jest właśnie takim system em , a inne p rogram y tego typu to M icro so ft S Q L Server, D B2 i M yS Q L. W każdej b azie danych m usi istnieć jakiś sposób w p row a dzan ia i p obieran ia in form acji, najlepiej korzystający z popularnego języka zrozu m ia łego dla w szystkich b az danych. System y zarządzania bazą danych im plem en tu ją taki stan dardow y język n azyw an y stru k tu ra ln ym ję z y k ie m zapytań , czyli SQ L (S tructu red Q uery Language). U m o ż liw ia on pobieran ie, dodaw anie, m od yfik ow a n ie i usuwanie in fo r m acji z b azy danych. Wstęp do SQL Strukturalny język zapytań (S Q L — Structured Q uery Language) jest standardow ym język iem zaprojek tow an ym do pracy z relacyjnym i bazam i danych. % W ed łu g A m erican National Standards Institute, „es kju e l" jest praw idłow ym sposobem u.,.,,,,., odczytyw ania skrótu SQL. Często jednak słyszy się rów nież angielskie słow o sequel („sikłel"). P ierw sza im p lem en ta cja S Q L opartego na p rzełom ow ej p racy dr. E.F. C od d a została opracow ana przez IB M w p oło w ie lat 70. Firm a p row adziła projekt badaw czy o nazw ie System R i podczas jego reali zacji opracow ano SQL. W 1979 roku firm a Relational Software Inc. (dziś znana jako O racle C orp oration ) opublikow ała pierw szą kom ercyjn ą w ersję SQL. S Q L został uznany za standard p rzez A m erica n N a tio n a l Standards Institute (A N S I) w 1986 roku, ale im plem entacje różn ych firm różnią się od siebie. W SQ L jest w ykorzystyw ana prosta składnia, której z łatw ością m ożn a się nauczyć. Proste przykłady zastosowania jej zostaną zaprezentow ane w tym rozdziale. W y ró żn ia m y pięć ty p ó w instrukcji SQL: ■ Z a p ytan ia pobierają w iersze p rzech ow yw an e w tabelach b azy danych. D o utw orzenia zapytania w ykorzystu jem y instrukcję SELECT. ■ Instru kcje D M L (D ataM anipulation Language) służą do m odyfikow ania zawartości tabel. Istnieją trzy takie instrukcje: ■ INSERT dodaje w iersze do tabeli. ■ UPDATE zm ien ia wiersze. ■ DELETE usuwa wiersze. ■ In stru kcje D D L (Data Definition Language) definiują struktury danych, takie jak tabele tw orzące bazę danych. W y ró żn ia m y pięć podstaw ow ych ty p ó w instrukcji D D L: ■ CREATE tw o rzy strukturę bazy danych, na p rzykład instrukcja CREATE TABLE służy do tw orzenia tabeli, a CREATE USERjest w ykorzystyw ana do tw orzen ia użytkow nika b azy danych. ■ ALTER m odyfikuje strukturę bazy danych, na przykład ALTER TABLE służy do m odyfikacji tabeli. ■ DROP usuwa strukturę b azy danych, na przykład DROP TABLE służy do usuwania tabeli. ■ RENAME zm ien ia n azw ę tabeli. ■ TRUNCATE usuwa w szystkie w iersze z tabeli. ■ Instru kcje T C ( Transaction Control) albo trwale zapisują zm iany w prow adzone w wierszach, albo je cofają. W y ró żn ia m y trzy instrukcje T C : ■ COMMIT trw ale zapisuje zm ian y w p row adzon e do wierszy. ■ ROLLBACK cofa zm ian y d okonane w wierszach. ■ SAVEPOINT tw o rzy punkt zachowania, do k tórego m ożn a cofnąć zm iany. ■ In stru k cje D C L ( D ata Control Language) służą do nadaw ania u praw nień dostępu do struktur bazy danych. Istnieją dw ie instrukcje D C L:

STRONA 22

Rozdział 1. Wprowadzenie 25 ■ GRANT daje u żytkow n ikow i dostęp do wskazanej struktury bazy danych. ■ REVOKE odbiera u żytkow n ikow i praw o dostępu do wskazanej struktury b azy danych. O racle dostarcza program SQ L*Plus um ożliw iający w p row adzen ie instrukcji SQ L i uzyskanie rezu l tatów ich działania z bazy danych. SQ L*Plus u m ożliw ia rów n ież w ykonanie skryptów zaw ierających in strukcje SQ L oraz poleceń SQ L*Plus. Jest w iele sposobów uruchamiania instrukcji SQ L i pobierania w y n ik ó w z b azy danych. M ożn a to ro bić za p om ocą oprogram ow an ia O racle Form s and Reports lub p ro g ra m ó w napisanych w innych ję zy kach, takich jak Java i C#. W ięcej in form acji na tem at w ykon yw an ia instrukcji SQ L w program ach pisa nych w języku Java m ożn a znaleźć w m ojej książce O racle9i JD B C P ro g ra m m in g (O ra cle Press, 2002). W ięcej in fo rm a cji na tem at w ykon yw a n ia instrukcji S Q L w program ach pisanych w język u C # m ożn a znaleźć w m ojej książce M asterin g C# Database P ro g ra m m in g (Sybex, 2003). Używanie SQL*Plus Z k olejn ego p od rozd zia łu dow iesz się, jak u ru chom ić p rogram SQ L*Plu s i przesłać zapytanie do b azy danych. Uruchamianie SQL*Plus Jeżeli używasz W in dow s 7, m ożesz uruchomić SQL*Plus, wybierając Wszystkie program y/Oracle/Application D evelopm ent/SQ L Plus. Jeśli używ asz system u U n ix lub Linux, S Q L*Plu s uruchom isz, wpisując sqlplus z w iersza poleceń. Rysunek 1.1 prezentuje SQ L*Plus u ru chom ion y w system ie W in d o w s 7. Rysunek 1.1. Oracle Database 12c SQL*Plus uruchomiony w Windows 7 N a rysunku przedsta w ion o proces łączenia się użytkow nika scott z bazą danych. U żytk ow n ik scott jest tw o rzo n y w w ielu bazach danych Oracle, a jego hasło w m ojej bazie danych to oracl e. N a zw a hosta, znajdująca się po znaku @, in form u je program SQ L*Plus o tym , gd zie została urucho m ion a baza danych. Jeżeli oprog ra m ow an ie działa na kom pu terze lokaln ym , zw yk le nazw a hosta jest pom ijana (czyli w pisujem y scott/oracle) — w takim przypadku SQ L*Plus próbuje połączyć się z bazą da nych na tym sam ym kom pu terze, na k tórym jest uruchom iony. Jeżeli baza danych nie jest uruchom iona na kom pu terze lokalnym , należy uzyskać nazw ę hosta od jej adm inistratora (D B A ). Jeśli kon to scott nie istnieje lub jest zablokow ane, należy poprosić adm inistratora o inną nazw ę użyt k ow n ika i hasło. W przykładach z p ierw szej części rozd zia łu m ożn a korzystać z dow oln ego konta użyt kow nika.

STRONA 23

26 Oracle Database 12c SQL Uruchamianie SQL*Plus z wiersza poleceń P rogra m S Q L*Plu s m ożn a u ru chom ić ró w n ież z w iersza poleceń, za p om ocą w yrażenia sqlpl us. Pełna składnia tego polecenia m a postać: sqlplus [nazwa_użytkownika [/hasło [@nazwa_hosta]]] gdzie: ■ nazwa_użytkownika oznacza nazw ę użytkow nika b azy danych, ■ hasło oznacza hasło użytkow nika b azy danych, ■ nazwa_hosta oznacza bazę danych, z którą chcem y się połączyć. Poniżej przedstaw iono przykłady użycia polecenia sql plus: sqlplus scott/oracle sqlplus scott/oracle@orcl Jeżeli SQ L*Plus używasz w systemie W in dow s, instalator O racle doda autom atycznie katalog progra m u SQ L*Plus do ścieżki system ow ej. Jeśli używ asz systemu U n ix lub Linux, m asz dw a sposoby, b y uru chom ić SQ L*Plus: ■ za p om ocą p olecen ia cd p rzejd ź do katalogu, w k tó rym znajdu je się p lik w y k o n y w a ln y sqlplus, i uruchom sql plus z tego katalogu; ■ dodaj katalog, w k tó rym znajdu je się sqlplus, do system ow ej ścieżki dostępu i uru chom sqlplus. W razie p rob lem ów z ustawianiem system owej ścieżki dostępu należy się skontaktow ać z adm in i stratorem systemu. Z e w zględ ów bezpieczeństw a m ożna ukryć hasło podczas łączenia się z bazą danych. M ożn a na p rzy kład wpisać: sqlplus scott@orcl W takiej sytuacji SQ L*Plus poprosi o w prow adzen ie hasła, które pozostanie niew idoczne. M ożn a rów n ież po prostu wpisać: sqlplus W takim przypadku S Q L*Plu s p op rosi o w p ro w a d zen ie n a zw y u żytkow n ika i hasła. N a zw ę hosta m ożn a dopisać do n azw y u żytkow nika (na przykład scott@ orcl). Wykonywanie instrukcji SELECT za pomocą SQL*Plus Po zalogow an iu się do bazy danych za p om ocą SQ L*Plus m ożn a od razu uruchom ić następujące polece nie SELECT, które zw raca bieżącą datę: SELECT SYSDATE FROM dual; W tej książce instrukcje SQL w yróżn ion e p og ru b ien iem należy w pisyw ać sam odzielnie. u . N i e trzeba w pisyw ać niew yróżnionych instrukcji. SYSDATE jest w budow aną funkcją bazy danych, zwracającą bieżącą datę, dual jest natom iast tabelą za wierającą jeden wiersz. W ięcej na tem at tabeli dual d ow iesz się w k olejn ym rozdziale. Instrukcje SQ L należy kończyć średnikiem (;). Uwaga Rysunek 1.2 przedstawia datę zw rócon ą jako w ynik opisanej w yżej instrukcji SELECT. W program ie SQ L*Plus m ożn a edytować ostatnią instrukcję SQL. W tym celu należy w pisać EDIT. Jest to przydatne, gd y p op ełn im y błąd lub chcem y w p ro w a d zić zm ian ę do instrukcji. W system ie W in d ow s po wpisaniu EDIT jest urucham iany N otatnik, w k tórym edytuje się instrukcje SQL. Po zakończeniu pracy N otatn ika i zapisaniu instrukcji SQ L jest ona przesyłana z p ow rotem do SQ L*Plus, gd zie m ożn a ją uru chom ić pon ow n ie, wpisując ukośnik (/). W system ie Lin u x lub U n ix dom yśln ym edytorem jest ed. A b y zapisać zm iany w instrukcji i opuścić ed, należy wpisać wq.

STRONA 24

Rozdział 1. Wprowadzenie 27 Rysunek 1.2. Wynik instrukcji SELECT SYSDATE F R O M dual; K w e s tia p o lo n iz a c ji Aby uzyskać format daty zaprezentowany na rysunku 1.2, a także polskie komunikaty z bazy danych w i doczne na zrzutach w dalszej części książki, w konsoli W indows 7 należy wcześniej odpowiednio usta w ić wartość zmiennej środowiskowej NLS LANG: set NLS LANG=POLISH POLAND.EE8MSWIN1250 oraz stronę kodową konsoli: chcp 1250 a dopiero potem uruchomić program sqlpl us. Aby polskie znaki w wynikach zapytań wyświetlanych w konsoli wyświetlały się poprawnie, należy w e właściwościach okna konsoli zm ienić czcionkę z Czcionki rastrowe na Lucida Console. R o z w ią z a n ie p r o b le m u z b łę d e m p r z y p r ó b ie s k o rz y s ta n ia z e d y c ji Jeśli przy próbie edycji instrukcji w W indow s pojawi się błąd SP2-0110, należy uruchomić SQL*Plus z uprawnieniami administratora. W W indows 7 można to zrobić, klikając prawym klawiszem myszy skrót do SQL*Plus i wybierając Uruchom jako administrator. Można ustawić to na stałe — aby to zrobić, należy kliknąć prawym klawiszem myszy skrót do SQL*Plus, wybrać Właściwości, a następnie w zakładce Zgod ność zaznaczyć Uruchom ten program jako administrator. M ożesz też ustawić katalog, w którym SQL*Plus ma się uruchamiać. Aby to zrobić, należy kliknąć pra wym klawiszem myszy skrót do SQL*Plus, wybrać Właściwości, a następnie zm ienić katalog w znajdują cym się w zakładce Skrót polu Rozpocznij w:. SQL*Plus będzie używał tego domyślnego katalogu do za pisywania i wczytywania plików. M ożesz na przykład ustawić ten katalog na C:\Moje_pliki_SQL i wtedy SQL*Plus będzie domyślnie zapisywał i wczytywał pliki z tego katalogu. W SQ L*Plus w systemie W in dow s m ożn a wracać do urucham ianych w cześniej poleceń, wciskając na klaw iaturze klaw isze ze strzałkam i w górę i w dół. W ięcej inform acji na temat edytowania instrukcji SQ L w program ie SQ L*Plus znajduje się w rozdziale 3. SQL Developer Instrukcje SQ L m ożn a rów n ież w p row a d zać za p om ocą program u S Q L D eveloper. M a on graficzn y in terfejs użytkow nika, w k tórym m ożn a w pisyw ać instrukcje SQ L, przeglądać tabele b azy danych, urucha m iać skrypty, edytow ać i debugow ać k od PL/SQL i w ykon yw ać inne zadania. M o że łączyć się z bazą da nych O racle w w ersji 9.2.0.1 lub w yższej i jest dostępny dla w ielu system ów operacyjnych. Rysunek 1.3 przedstawia okno program u SQ L D eveloper. B I C:\Windows\system32\cmd.exe- sqlplus SQL> SELECT SYSDATE FROM d u a l; * SYSDATE 14/06/30 SQL> . | < _______________________________________________ ■»»____ ►

STRONA 25

28 Oracle Database 12c SQL C i Oracle SQL Developer : store => 1 B P Ü [De («fit yicw Navigate gun Vetsigning loots 3 ^ 0 9 W X i l 0 * 0 * & ydp b P SĄ Connections * „'.Reports * Q i t « « » F ) ♦ • 1Ä > ifc] M t i ^ la C A VS i/ Cd 0. U 700001 «conde I l store » CamKtom 8 I I C*ecJe Database 12c. 9 IcJ store t. labiés (Httfcd) ® View* 8 Eiitwr■iy View» i, j j Indexes lu 8 l’adcapw 9 {J j Proreciee* S & Eu».tiuta 8 Qj} Queues tu & , Queue* labiés ï Trigger* B L â Gowecitwn Tr^gers 8 S i Types lu J Leauences S 8 MktrrMlml Vir*< B \J!j MetenaUed View» Logs B Q | Sv w v w lu l\A>keSynonyms S 40 fWAhese Iries B ,aMPubfc. DatabaseIr i» S J Drectones lu cjtdtton* B , ] ApptrAtxmTxpre« * jü Java B ÎQ XM. Schémas U ¡y KMLWKeooatOry Rysunek 1.3. Okno programu SQL Developer, uruchomionego w systemie Windows N a leży pobrać wersję program u SQ L D eveloper zawierającą Java Software D evelopm en t K it (S D K ) lub m ieć w cześniej zainstalowaną popraw ną w ersję Java SD K. W ym agan a wersja Java S D K zależy od wersji program u SQ L D eveloper. S zczegóły m ożn a spraw dzić na stronie p rogram u SQ L D eveloper w serwisie w w w .oracle.com . P o p op ra w n ym uruchom ien iu program u S Q L D evelo p er n iezbędne b ęd zie u tw orzen ie połączen ia z bazą danych p o p rzez k likn ięcie p raw ym k law iszem m yszy C onnections i w ybran ie N ew C onnection. SQ L D eveloper w y św ietli okn o d ia lo go w e w k tó rym n ależy w pisać szczegóły połączenia z bazą danych. Rysunek 1.4 pokazuje p rzykładow e okno d ialogow e z w pisanym i szczegółam i połączenia. Rysunek 1.4. Konfigurowanie połączenia z bazą danych w programie SQL Developer SELECT SYSDATE FROM d ual; » ) Sept Output x it Q Q Tas*,completedn 0,187 seconds STS&ATZ 14/06/30 Po utw orzeniu i przetestow an iu połączenia m ożn a używ ać program u SQ L D eveloper do przegląda nia tabel b azy danych i urucham iania instrukcji. Rysunek 1.5 przedstawia szczegółow e in form acje o ta beli customers.

Rozmiar :	55.0 MB
Rozszerzenie:	pdf

dareks_