Robert Chwastek Bazy danych [ Bazy danych Robert Chwastek Niniejszy tekst zawiera fragmenty wyk�adu pod nazw� "Bazy Danych", kt�ry prowadz� dla student�w 3-go roku specjalno�ci Telekomunikacja w Akademii G�rniczo-Hutniczej w Krakowie. Wszystkie nazwy producent�w i produkt�w s� w�asno�ci� odpowiednich firm i zosta�y u�yte jedynie w celach informacyjnych. Copyright (c) Robert Chwastek, Krak�w 1996. Bazy danych Robert Chwastek ] Spis tre�ci: 1. POJ�CIA PODSTAWOWE 5 1.1. DEFINICJA BAZY DANYCH 5 1.2. SYSTEM ZARZ�DZANIA BAZ� DANYCH 6 1.3. TRANSAKCJE 6 1.4. J�ZYKI STOSOWANE W BAZACH DANYCH 7 2. TYPY DANYCH 8 2.1. SPIS TYP�W DANYCH 8 2.2. TYP NUMERYCZNY 8 2.3. KONWERSJE TYP�W 10 2.4. WARTO�CI PUSTE 10 3. MODEL RELACYJNY 12 3.1. STRUKTURY DANYCH W MODELU RELACYJNYM 12 3.2. ROZKAZY J�ZYKA SQL 13 3.3. SK�ADNIA ROZKAZ�W SQL 14 3.3.1. Definicje podstawowe 14 3.3.2. Rozkaz CREATE TABLE 75 3.3.3. Rozkaz DROP 16 3.3.4. Rozkaz INSERT 17 3.3.5. Rozkaz DELETE 18 3.3.6. Rozkaz CREATE SEQUENCE 19 3.3.7. RozkazSELECT 21 3.3.8. Rozkaz UPDATE 22 3.3.9. Rozkaz RENAME 23 3.3.10. Rozkaz ALTER TABLE 24 3.3.11. Rozkaz CREATE INDEX 25 3.3.12. Rozkaz CREATE VIEW 27 3.3.13. Rozkaz COMMIT 28 3.3.14. Rozkaz ROLLBACK. 28 3.3.15. Rozkaz SAVEPOINT. 28 3.3.16. Rozkaz SET TRANSACTION 29 3.4. OPERACJE RELACYJNE 30 3.4.1. Selekcja 30 3.4.2. Projekcja 31 3.4.3. Produkt 32 3.4.4. Po��czenie 32 3.4.5. Operacje mnogo�ciowe 33 3.4.6. Grupowanie 34 3.4.7. Kolejno�� klauzul w rozkazie SELECT 35 3.5. PODZAPYTANIA 35 3.6. WIDOKI (PERSPEKTYWY) 37 3.7. TRANSAKCJE 38 3.8. NORMALIZACJA RELACJI 38 3.8.1. Cele normalizacji 38 3.8.2. Pierwsza posta� normalna 39 3.8.3. Definicje pomocnicze 40 3.8.4. Druga posta� normalna 44 3.8.5. Trzecia posta� normalna 46 3.8.6. Czwarta posta� normalna 47 3.8.7. Pi�ta posta� normalna 48 3.8.8. Podsumowanie 48 4. WARUNKI I WYRA�ENIA 50 4.1. OPERATORY 50 4.1.1. Operatory arytmetyczne 50 4.1.2. Operatory znakowe 50 4.1.3. Operatory por�wnania 57 4.1.4. Operatory logiczne 52 4.1.5. Operatory mnogo�ciowe 53 4.1.1. [ Bazy danych Robert Chwastek ] 4.2. WYRA�ENIA 53 4.3. WARUNKI 55 5. STANDARDOWE FUNKCJE J�ZYKA SQL 57 5.1. FUNKCJE NUMERYCZNE 57 5.2. FUNKCJE ZNAKOWE 57 5.3. FUNKCJE GRUPOWE 59 5.4. FUNKCJE KONWERSJI 60 5.5. FUNKCJE OPERACJI NA DATACH 61 5.6. INNE FUNKCJE 63 5.7. FORMATY ZAPISU DANYCH 64 5.7.7. Formaty numeryczne 64 5.7.2. Formaty dat 64 6. PROGRAMOWANIE PROCEDURALNE - PL/SQL 66 6.1. WPROWADZENIE 66 6.2. STRUKTURA BLOKU 67 6.3. PROCEDURY I FUNKCJE 67 6.4. KURSORY 68 6.5. REKORDY 70 6.6. OBS�UGA B��D�W 71 6.6.7. Informacje podstawowe 71 6.6.2. Wyj�tkipredefiniowane 73 6.6.3. Obs�uga wyj�tk�w 73 6.6.4. Wyj�tki zdefiniowane przez u�ytkownika 74 6.7. ROZKAZY J�ZYKA PL/SQL 75 6.Z7. Rozkaz OPEN 75 6.7.2. Rozkaz CLOSE 76 6.7.3. RozkazFETCH 76 6.7.4. RozkazSELECT... INTO 77 6.7.5. RozkazIF 77 6.7.6. Rozkaz WOP 78 6.7.7. RozkazEXIT 80 6.7.8. RozkazGOTO 80 7. LITERATURA 81 Bazy danych Robert Chwastek 1. Poj�cia podstawowe 1.1. Definicja bazy danych W pewnym uproszczeniu przez baz� danych rozumiemy uporz�dkowany zbi�r danych, a przez system bazy danych - baz� danych wraz z oprogramowaniem umo�liwiaj�cym operowanie na niej. Baza danych jest przechowywana na no�nikach komputerowych. Precyzuj�c definicj� bazy danych mo�na powiedzie�, �e baza danych jest abstrakcyjnym, informatycznym modelem wybranego fragmentu rzeczywisto�ci (ten fragment rzeczywisto�ci bywa nazywamy mini�wiatem). Fragment rzeczywisto�ci mo�e by� rozumiany jako: � rzeczywisto�� fizyczna - taka, kt�r� postrzegamy naszymi organami percepcji � rzeczywisto�� konceptualna - istniej�ca najcz�ciej w wyobra�ni pewnych os�b; przyk�adem tej rzeczywisto�ci mo�e by� projekt nowego samolotu firmy Boeing, kt�ry istnieje tylko w wyobra�ni konstruktor�w. Poprawne (z punktu widzenia cz�owieka) operowanie na bazie danych wi��e si� z w�a�ciw� interpretacj� danych, kt�re zosta�y w niej zapisane. W zwi�zku z tym konieczny jest opis semantyki (znaczenia) danych, przechowywanych w bazie. System bazy danych s�u�y wi�c do modelowania rzeczywisto�ci (fragmentu). W systemach baz danych rzeczywisto�� opisuje si� za pomoc� modelu danych. Przez model danych rozumiemy zbi�r abstrakcyjnych poj�� umo�liwiaj�cych reprezentacj� okre�lonych w�asno�ci tego �wiata. Zbi�r poj�� u�yty do opisu w�asno�ci konkretnego fragmentu �wiata rzeczywistego, istotnych z punktu widzenia danego zastosowania tworzy schemat bazy danych. Baza danych jest modelem logicznie sp�jnym s�u��cym okre�lonemu celowi. W zwi�zku z tym baza danych nie mo�e (nie powinna) przyj�� stanu, kt�ry nie jest nigdy osi�galny w modelowanej rzeczywisto�ci. Z bazy danych korzysta pewna �ci�le okre�lona grupa u�ytkownik�w. Szczeg�lnymi u�ytkownikami s� projektanci bazy danych, kt�rzy defniuj� jej struktur� i przygotowuj� niezb�dne programy zwane aplikacjami. Baza danych jest wype�niania danymi i przetwarzana. Grupy os�b wype�niaj�ce i przetwarzaj�ce baz� danych mog� by� roz��czne. Do wype�niania i przetwarzania bazy danych s�u�� najcz�ciej wykonane w tym celu aplikacje. Mo�na wi�c powiedzie�, �e ka�da baza danych posiada: � �r�d�o danych � u�ytkownik�w � zwi�zki z reprezentowan� rzeczywisto�ci� Baza danych to dane i tzw. schemat bazy danych. Dane opisuj� cechy (w�asno�ci) modelowanych obiekt�w. Nie jest jednak mo�liwa ich interpretacja bez u�ycia schematu. Schemat jest opisem struktury (formatu) przechowywanych danych oraz wzajemnych powi�za� mi�dzy nimi. Bazy danych Robert Chwastek 1.2. System zarz�dzania baz� danych System zarz�dzania baz� danych (SZBD) jest to zestaw program�w umo�liwiaj�cych tworzenie i eksploatacj� bazy danych. System zarz�dzania baz� danych jest oprogramowaniem og�lnego przeznaczenia. System bazy danych sk�ada si� z bazy danych, systemu zarz�dzania baz� danych i ewentualnie z zestawu aplikacji wspomagaj�cych prac� poszczeg�lnych grup u�ytkownik�w. 1.3. Transakcje Transakcja jest sekwencj� instrukcji po wykonaniu kt�rej sp�jna baza danych nadal zachowuje sw� sp�jno�� (zgodno�� z modelowan� rzeczywisto�ci�). Transakcja jest operacj� atomow� tzn. system zarz�dzania baz� danych mo�e wykona� wszystkie instrukcje wchodz�ce w sk�ad transakcji albo �adnej. W rzeczywisto�ci system zarz�dzania baz� danych wykonuje po kolei instrukcje wchodz�ce w sk�ad transakcji i w przypadku niepowodzenia kt�rejkolwiek z nich wycofuje instrukcje uprzednio wykonane. Podsumowuj�c podane wcze�niej informacje, system bazy danych przedstawia nast�puj�cy rysunek: A Transakcje (zapytania) Z B D Modu� zarz�dzania transakcjami Modu� zarz�dzania dost�pem do danych System bazy danych [ Bazy danych Robert Chwastek ] 1.4. J�zyki stosowane w bazach danych J�zyki, kt�re stosuje si� do projektowania i wype�niania bazy danych mo�na podzieli� na cztery r�ne grupy: � j�zyk definiowania danych (Data Definition Language - DDL), kt�ry umo�liwia definiowanie struktury danych przechowywanych w bazie, czyli tworzenie schematu implementacyj nego � j�zyk manipulowania danymi (Data Manipulation Language - DML), kt�ry umo�liwia wype�nianie, modyfikowanie i usuwanie informacji z bazy danych. � j�zyk sterowania danymi (Data Control Language - DCL), kt�ry umo�liwia sterowanie transakcjami (np. zatwierdzanie lub wycofywanie) � j�zyk zapyta� (Query Language), kt�ry umo�liwia pobieranie z bazy informacji zgodnych z podanymi warunkami � Bazy danych Robert Chwastek 2. Typy danych 2.1. Spis typ�w danych Typ char(size) character varchar(size) dat� long long varchar raw(size) long raw rowid number Opis Ci�g znak�w o zmiennej d�ugo�ci nie wi�kszej ni� podany rozmiar. Dla tego typu maksymalny rozmiar mo�e wynosi� 255. W przypadku nie podania rozmiaru domy�lnie przyjmowana jest warto�� 1 Synonim do char W aktualnej wersji ORACLE'a jest to synonim do char, konieczne jest jednak podanie rozmiaru. W przysz�ych wersjach zak�ada si�, �e char b�dzie ci�giem znak�w o sta�ej d�ugo�ci, natomiast varchar o zmiennej. Poprawne daty z zakresu 1 stycznia 4712 p.n.e. do 31 grudnia 4712 n.e. Domy�lny format wprowadzania to" DD-MON-YY np.: '01-JAN-89' Ci�g znak�w o zmiennej d�ugo�ci nie wi�kszej ni� 65535 znak�w. Mo�na zdefiniowa� tylko jedn� kolumn� typu long w jednej tabeli. synonim do long Ci�g bajt�w o podanej d�ugo�ci. Specyfikacja rozmiaru jest konieczna. Rozmiar maksymalny dla tego typu to 255. Warto�ci do p�l tego typu s� wstawiane jako ci�gi znak�w w notacji szesnastkowej. Ci�g bajt�w o zmiennej d�ugo�ci. Pozosta�e w�asno�ci jak dla typu long. Warto�ci do p�l tego typu s� wstawiane jako ci�gi znak�w w notacji szesnastkowej. Unikalna warto�� identyfikuj�ca wiersz. Podany typ jest pseudotypem, tzn. kolumna tego typu nie mo�e by� utworzona w tabeli i nie jest w niej przechowywana, ale obliczana na podstawie informacji o fizycznym po�o�eniu wiersza na dysku, w pliku itp. Warto�� typu rowid mo�e by� przekonwertowana do typu znakowego za pomoc� funkcji ROWIDTOCHAR. Typ numeryczny. Jego warto�ci mog� si� zmienia� w zakresie od 1.0 * 10~129 do 9.99 * 10124. Mo�liwe jest ograniczenie podanego zakresu przez specyfikacj� precyzji i skali w spos�b opisany poni�ej. 2.2. Typ numeryczny Typ numeryczny jest u�ywany do przechowywania liczb zar�wno zmienno jak i sta�oprzecinkowych. Dla kolumn numerycznych typ mo�na wyspecyfikowa� na jeden z trzech sposob�w: � number � number (precyzja) � number (precyzja, skala) 8 [ Bazy danych Robert Chwastek ] Precyzja okre�la ca�kowit� liczb� cyfr znacz�cych i mo�e si� zmienia� od 1 do 38. Skala okre�la liczb� cyfr po prawej stronie kropki dziesi�tnej i mo�e si� zmienia� w zakresie od -84 do 127. W momencie definiowania kolumny numerycznej dobrym zwyczajem jest podawanie zar�wno precyzji jak i skali, poniewa� wymusza to automatyczn� kontrol� wprowadzanych warto�ci, a wi�c zwi�ksza szans� na zachowanie sp�jno�ci bazy danych. Je�li warto�� przekracza maksymaln� precyzj�, to generowany jest b��d. Je�li warto�� przekracza skal�, to jest zaokr�glana. Je�li skala jest ujemna, to warto�� jest zaokr�glana do podanej liczby miejsc po lewej stronie kropki dziesi�tnej; np. specyfikacja (10, -2) oznacza zaokr�glenie do setek. Czasami specyfikuje si� r�wnie� skal� wi�ksz� ni� precyzj�. Oznacza to wtedy, �e wprowadzane liczby musz� mie� po kropce dziesi�tnej tak� liczb� zer jaka jest r�nica mi�dzy skal� a precyzj�; np: number(4, 5) b�dzie wymaga� jednego zera po kropce dziesi�tnej. Liczby mo�na r�wnie� zapisywa� w formacie zmiennoprzecinkowym. Sk�ada si� ona wtedy z u�amka dziesi�tnego, bezpo�rednio po kt�rym znajduje si� litera E i wyk�adnik pot�gi liczby 10 przez jaki trzeba pomno�y� ten u�amek. Dla przyk�adu zapis 9.87E-2 oznacza 9.87 * 10~2. Inne systemy baz danych posiadaj� kilka r�nych typ�w numerycznych, kt�re w ORACLE'u implementowane s� jako number. Mo�liwe jest stosowanie nazw tych typ�w. Nazwy te oraz spos�b implementacji za pomoc� typu number przedstawia tabela: Bazy danych Robert Chwastek ] Specyfikacja Typ Precyzja Skala number number 38 null number(*) number 38 null number(*, s) number 38 t/5 number(p) number P 0 number(p,s) number P t/5 decimal number 38 0 decimal(*) number 38 0 decimal(*, s) number 38 t/5 decimal(p) number P 0 decimal(p, s) number P t/5 integer number 38 0 smallint number 38 0 float number 38 null float(*) number 38 null float(b) number b null real number 63 binary (18 decimal) null double precision number 38 null 2.3. Konwersje typ�w W systemie zarz�dzania baz� danych mo�liwe s� konwersje danych jednego typu do danych innego typu. Dane te musz� spe�nia� pewne warunki, aby konwersja taka by�a mo�liwa, np. chc�c przekonwertowa� ci�g znak�w do liczby, ci�g ten powinien sk�ada� si� z cyfr. Poni�sza tabela przedstawia funkcje s�u��ce do wykonywania konwersji pomi�dzy poszczeg�lnymi typami w systemie ORACLE: Z typu char number dat� Do typu char number dat� zb�dna TO_NUMBER TO_DATE TO_CHAR zb�dna TO_DATE TO_CHAR niemo�liwa zb�dna 2.4. Warto�ci puste Pola tabeli mog� przyjmowa� warto�ci puste, pod warunkiem, �e nie zosta�o to zabronione przez projektanta bazy danych. Warto�� pusta (NULL) nie jest r�wna warto�ci 0 i w wyniku obliczenia dowolnego wyra�enia, kt�rego argumentem jest NULL otrzymuje si� r�wnie� warto�� pust� (NULL). Funkcja NVL pozwala dokona� konwersji warto�ci aktualnej (do niej samej) lub warto�ci pustej do warto�ci domy�lnej. Dzia�anie funkcji NVL ilustruje przyk�ad: I 10 I [ Bazy danych Robert Chwastek ] NVL(COMM, 0) zwr�ci warto�� COMM, jesli nie jest to warto�� pusta lub 0 je�li COMM ma warto�� NULL. Wi�kszo�� funkcji grupuj�cych ignoruje warto�� NULL. Np. zapytanie, kt�rego zadaniem jest obliczenie �redniej z pi�ciu nast�puj�cych warto�ci: 1000, NULL, NULL, NULL i 2000 zwr�ci 1500 poniewa� (1000 + 2000)/2 = 1500. Jedyne operatory por�wnania, kt�re mo�na u�y� do warto�ci pustej to IS NULL i IS NOT NULL. Je�li zostanie u�yty jakikolwiek inny operator por�wnania do warto�ci pustej, to wynik jest nieokre�lony. Poniewa� NULL reprezentuje brak warto�ci, wi�c nie mo�e on by� r�wny ani nier�wny jakiejkolwiek innej warto�ci, r�wnie� innemu NULL. ORACLE traktuje warunki, kt�rych wynik jest nieznany jako fa�szywe. Tak wi�c warunek COMM = NULL jest nieznany, w zwi�zku z czym rozkaz SELECT z takim warunkiem nie zwr�ci nigdy �adnego wiersza. Jednak w takiej sytuacji ORACLE nie zg�osi informacji o wyst�pieniu b��du. 11 Bazy danych Robert Chwastek 3. Model relacyjny Zgodnie z teori� model danych w relacyjnych bazach danych sk�ada si� z trzech podstawowych element�w: � relacyjnych struktur danych � operator�w relacyjnych umo�liwiaj�cych tworzenie, przeszukiwanie i modyfikacj� bazy danych � wi�z�w integralno�ci jawnie lub niejawnie okre�laj�cych warto�ci danych 3.1. Struktury danych w modelu relacyjnym Podstawow� struktur� danych jest relacja b�d�ca podzbiorem iloczynu kartezja�skiego dw�ch wybranych zbior�w reprezentuj�cych dopuszczalne warto�ci. W bazach danych relacja przedstawiana jest w postaci tabeli. Relacja jest zbiorem krotek posiadaj�cych tak� sam� struktur�, lecz r�ne warto�ci. Ka�da krotka odpowiada jednemu wierszowi tablicy. Ka�da krotka posiada co najmniej jeden atrybut odpowiadaj�cy pojedynczej kolumnie tablicy. Ka�da relacja (tablica) posiada nast�puj�ce w�asno�ci: � krotki (wiersze) s� unikalne � atrybuty (kolumny) s� unikalne � kolejno�� krotek (wierszy) nie ma znaczenia � kolejno�� atrybut�w (kolumn) nie ma znaczenia � warto�ci atrybut�w (p�l) s� atomowe Przyk�adow� tabel� wraz z jej elementami przedstawia rysunek: Imi� Pesel Nazwisko Wykszta�cenie 72030403987 Ma�gorzata Albinos WT 65081002987 Damian J�drzej ek SO 44101202034 Barbara Bibicka P 70010101231 Piotr Burzy�ski * WT 55121201223 Mateusz^/ Manicki ST \ X Krotka (wiersz) | Pole Atrybut (kolumna) | Warto�� I Dokumentacja system�w zarz�dzania bazami danych pos�uguje si� najcz�ciej terminologi� tabela, wiersz i kolumna, a nie terminologi� relacyjn�. Wynika to z tego, �e 12 [ Bazy danych Robert Chwastek operacje na relacjach s� opisywane za pomoc� matematycznych operacji na zbiorach i relacjach, kt�re s� �cis�e, ale trudno zrozumia�e dla przeci�tnego u�ytkownika. Natomiast pos�ugiwanie si� tabelami, wierszami i kolumnami jest mniej formalne i �cis�e, ale bardziej przejrzyste. W dalszej cz�ci tego wyk�adu b�dzie b�dzie stosowana zar�wno jedna jak i druga terminologia. Tabela mo�e reprezentowa�: � zbi�r encji wraz z atrybutami � zbi�r powi�za� pomi�dzy encjami wraz z ich atrybutami � zbi�r encji wraz z atrybutami i ich powi�zania z innymi encjami (wraz z atrybutami) Ka�dy wiersz w tabeli reprezentuje pojedyncz� encj�, powi�zanie lub encj� wraz z powi�zaniami. W tabeli nie powinny powtarza� si� dwa identyczne wiersze -zabezpieczenie przed tym powt�rzeniem jest realizowane poprzez pola kluczowe. Wiersze w odr�nieniu od kolumn s� dynamiczne - dzia�anie bazy danych polega na dopisywaniu, modyfikacji i usuwaniu wierszy. W raz utworzonej tabeli rzadko dopisuje si� lub kasuje kolumny - poniewa� ka�da z kolumn reprezentuje pewn� w�asno�� modelowanej rzeczywisto�ci. W przypadku projektowania tabeli w bazie danych nale�y stosowa� si� do nast�puj�cych wskaz�wek: � U�ywaj nazw opisowych do nazwania kolumn tabeli. Kolumny nie powinny mie� znaczenia ukrytego, ani reprezentowa� kilku atrybut�w (z�o�onych w pojedyncz� warto��). � B�d� konsekwentny w stosowaniu liczby pojedynczej lub mnogiej przy nazywaniu tabeli. � Tw�rz tylko te kolumny, kt�re s� niezb�dne do opisania modelowanej encji lub powi�zania - tabele z mniejsz� ilo�ci� kolumn s� �atwiejsze w u�yciu. � Utw�rz kolumn� p�l kluczowych dla ka�dej tabeli. � Unikaj powtarzania informacji w bazie danych (normalizacja). 3.2. Rozkazy j�zyka SQL Poni�sza tabela zawiera spis podstawowych rozkaz�w j�zyka SQL wraz z kr�tkim opisem. Operacje relacyjne b�d� wyja�nione dok�adniej w dalszej cz�ci wyk�adu. Rozkaz Typ Opis ALTER TABLE CREATE INDEX CREATE SEQUENCE CREATE TABLE DDL DDL DDL DDL Dodaje kolumn� do tabeli, redefiniuje kolumn� w istniej�cej tabeli lub redefiniuje ilo�� miejsca zarezerwowan� dla danych Tworzy indeks dla tabeli Tworzy obiekt s�u��cy do generowania kolejnych liczb -sekwencj�. Sekwencji mo�na u�y� do generowania unikalnych identyfikator�w w tabelach Tworzy tabel� i definiuje jej kolumny oraz alokacj� przestrzeni dla danych 13 Bazy danych Robert Chwastek J CREATE VIEW DDL Definiuje widok dla jednej lub wi�kszej ilo�ci tabel lub innych widok�w DELETE DML Usuwa wszystkie lub wyr�nione wiersze z tabeli DROP obiekt DDL Usuwa indeks, sekwencje, tablic�, widok lub inny obiekt INSERT DML Dodaje nowy wiersz (lub wiersze) do tabeli lub widoku RENAME DDL Zmienia nazw� tabeli, widoku lub innego obiektu SELECT DML Wykonuje zapytanie. Wybiera wiersze i kolumny z jednej lub kilku tabel UPDATE DML Zmienia dane w tabeli COMMIT DML Ko�czy transakcj� i na sta�e zapisuje zmiany ROLLBACK DML Wycofuje zmiany od pocz�tku transakcji lub zaznaczonego punktu. SAVEPOINT DML Zaznacza punkt, do kt�rego mo�liwe jest wykonanie rozkazu ROLLBACK SET DDL Zaznacza aktualn� transakcj� jako read-only (tylko do TRANSACTION odczytu). 3.3. Sk�adnia rozkaz�w SQL 3.3.1. Definicje podstawowe � Identyfikator (nazwa) - ci�g liter, cyfr i znak�w podkre�lenia rozpoczynaj�cy si� liter� lub znakiem podkre�lenia. R�ne systemy baz danych umo�liwiaj� stosowanie innych znak�w wewn�trz identyfikator�w (np. znak '$', lub '!'). Stosowanie tych znak�w nie jest jednak zalecane ze wzgl�du na p�niejsze problemy zwi�zane z przeno�no�ci� napisanych w ten spos�b aplikacji. � S�owa zarezerwowane - identyfikatory zastrze�one posiadaj�ce specjalne znaczenie w j�zyku SQL. Spis wszystkich s��w zarezerwowanych w j�zyku SQL przez tw�rc�w ORACLE'a przedstawia tabela: access add all alter and any as asc audit between by char check cluster column comment compress connect create current dat� dba decimal default delete desc distinct drop else exclusive exists file float for from grant graphic group having identified if immediate in increment index install initial insert integer intersect into lis level like lock |long maxextents minus mod� modify noaudit nocompress not nowait null number of offline on online option or order pctfree prior privileges public raw 14 [ Bazy danych Robert Chwastek J rename resource revoke row rowid | rownum rows select session set share size smallint start successful synonym sysdate table then to trigger uid imion unique update | user validate values varchar | vargraphic view whenever where | with � Liczby - mog� by� ca�kowite lub rzeczywiste. Liczba ca�kowita nie posiada kropki dziesi�tnej. W systemie ORACLE liczby mo�na zapisywa� w formacie zwyk�ym lub wyk�adniczym. Format wyk�adniczy sk�ada si� z liczby oraz wyk�adnika liczby 10, przez kt�ry nale�y pomno�y� t� liczb� oddzielonego liter� 'e' lub 'E\ Przyk�ady: 7E2 = 7 * 102 25e-03 = 25 * 10"3 Dodatkowo w systemie ORACLE liczb� ca�kowit� mo�na zako�czy� liter� 'K' lub liter� 'M\ Litera 'K' oznacza, �e ca�a liczba ma by� pomno�ona przez 1024 (1 KB), natomiast litera 'M', �e liczb� nale�y pomno�y� przez 1048576 (1 MB). Przyk�ady: 256K = 256 * 1024 1M = 1 * 1048576 � Rozkazy j�zyka SQL ko�cz� si� �rednikiem 3.3.2. Rozkaz CREATE TABLE Rozkaz CREATE TABLE s�u�y do tworzenia struktury tabeli (bez danych) i posiada dodatkowe opcje umo�liwiaj�ce: � okre�lenie sposobu alokacji przestrzeni do przechowywania danych � okre�lenie rozmiaru tabeli � przydzielenie tabeli do okre�lonego klastra � za�adowanie danych b�d�cych wynikiem podanego zapytania, do tabeli Rozkaz CREATE TABLE posiada nast�puj�c� sk�adni�: CREATE TABLE [user.]table ( {column_element | table_constraint} [, {column_element | table_constraint} ] ... ) [ PCTFREE n ] [ PCTUSED n ] [ INITTRANS n ] [ MAXTRANS n ] [ TABLESPACE tablespace ] [ STORAGE storage ] [ CLUSTER cluster (column [, column] ...) ] [ AS �uery ] Parametry: � user - w�a�ciciel tabeli, je�li nie zostanie podany, to w�a�cicielem staje si� osoba tworz�ca tabel�. Tabele dla innych u�ytkownik�w mo�e tworzy� tylko administrator systemu zarz�dzania baz� danych (DBA) 15 [ Bazy danych Robert Chwastek ] � table - nazwa tabeli, powinna by� prawid�owym identyfikatorem. Wszystkie obiekty danego u�ytkownika powinny mie� unikalne nazwy � column_element - definiuje kolumn� i opcjonalne ograniczenia na warto�ci w tej kolumnie. Tabela musi zawiera� co najmniej jedn� kolumn� (jak to wynika ze sk�adni) � table_constraints - okre�la ograniczenia jakie musi spe�nia� ca�a tabela � tablespace - okre�la obszar, w kt�rym nale�y umie�ci� tabel� � storage - okre�la przysz�y spos�b alokacji pami�ci � cluster - okre�la klaster (kt�rego w�a�cicielem musi by� w�a�ciciel tabeli), do kt�rego nale�y przydzieli� tabel� � �uery - jest poprawnym zapytaniem takim samym jak zdefiniowane w rozkazie SELECT. Je�li podane jest zapytanie, to mo�na poda� tylko nazwy kolumn - typy i rozmiary s� kopiowane z odpowiednich kolumn okre�lonych w zapytaniu. Mo�liwe jest r�wnie� pomini�cie nazw kolumn, ale tylko wtedy, gdy nazwy te s� unikalne i dobrze zdefiniowane w zapytaniu. Liczba wyspecyfikowanych kolumn musi by� taka sama jak liczba kolumn w zapytaniu. Przyk�ady: CREATE TABLE pracownicy( nr_pracownika NUMBER NOT NULL PRIMARY KEY, imi� CHAR(15) NOT NULL CHECK (imi� = UPPER(imi�)), nazwisko CHAR(25) NOT NULL CHECK (nazwisko = UPPER(nazwisko)), nr_wydzialu NUMBER (3) NOT NULL ) ; 3.3.3. Rozkaz DROP Rozkaz drop s�u�y do kasowania obiekt�w r�nego rodzaju. Og�lna posta� tego rozkazu jest nast�puj�ca: DROP object_type [user.]object Poni�ej przedstawione s� r�ne postacie rozkazu drop s�u��ce do kasowania poszczeg�lnych typ�w obiekt�w: � DROP CLUSTER [user .] cluster [INCLUDING TABLES ]- kasowanie klastra. W przypadku podania klauzuli INCLUDING TABLES zostan� skasowane wszystkie tabele przydzielone uprzednio do kasowanego klastra. Je�li klauzula INCLUDING TABLES nie zostanie podana, to przed skasowaniem klastra musz� by� skasowane wszystkie nale��ce do niego tabele. Jest to zabezpieczenie przed omy�kowym skasowaniem klastra zawieraj�cego tabele, kt�re s� potrzebne. � DROP [PUBLIC] DATABASE LINK 1 ink - usuwanie po��czenia. Je�li po��czenie jest publiczne to skasowa� je mo�e tylko administrator (DBA). � DROP INDEX [user. ] index - kasowanie indeksu. � DROP [PUBLIC] ROLLBACK SEGMENT segment - kasowanie segmentu wycofywania (rollback). Mo�na usun�� tylko te segmenty wycofywania, kt�re nie s� u�ywane w danym momencie. Kasowanie segment�w wycofywania mo�e wykonywa� tylko administrator bazy danych. I 16 I [ Bazy danych Robert Chwastek ] � DROP SEQUENCE [user. ] se�uence - kasowanie sekwencji. � DROP [PUBLIC] SYNONYM [user .] synonym - usuwanie synonimu. Synonim publiczny mo�e zosta� usuni�ty tylko przez administratora (DBA). Poszczeg�lni u�ytkownicy mog� usuwa� tylko te segmenty, kt�rych s� w�a�cicielami. � DROP TABLE [user. ] table - usuwanie tabeli. W momencie usuni�cia tabeli automatycznie kasowane s� skojarzone z ni� indeksy zar�wno utworzone przez w�a�ciciela tabeli jak i przez innych u�ytkownik�w. Widoki i synonimy wskazuj�ce na tabel� nie s� kasowane automatycznie, ale staj� si� nieprawid�owe. � DROP TABLESPACE tablespace [INCLUDING CONTENTS] -usuwanie obszaru danych. Rozkaz ten mo�e by� wykonany tylko przez administratora (DBA). W przypadku podania klauzuli INCLUDING CONTENTS obszar danych zostanie skasowany nawet wtedy, gdy zawiera dane. Je�li klauzula INCLUDING CONTENTS nie zosta�a podana, a obszar zawiera dane, to nie zostanie skasowany. � DROP VIEW [user. ] view - usuwanie widoku. Po usuni�ciu widoku, inne widoki lub synonimy, kt�re odwo�ywa�y si� do widoku skasowanego, nie zostan� skasowane, ale staj� si� nieprawid�owe. 3.3.4. Rozkaz INSERT Rozkaz insert dodaje nowe wiersze do tabeli lub do tabel przynale��cych do widoku. Aby doda� wiersze do tabeli nale�y by� w�a�cicielem tabeli, administratorem (DBA) lub posiada� uprawnienia dopisywania do tej tabeli. Sk�adnia rozkazu: INSERT INTO [user.]table [ (column [, column] ...) ] { VALUES (value [, value] ...) | query } Parametry: � user - nazwa w�a�ciciela tabeli � table - nazwa tabeli, do kt�rej dopisywane s� wiersze � column - nazwa kolumny wewn�trz tabeli lub widoku � value - pojedyncza warto�� odpowiadaj�ca odpowiedniej pozycji na li�cie kolumn. Warto�� mo�e by� dowolnym wyra�eniem. Je�li wprowadzana warto�� nie jest r�wna NULL to musi by� zgodna z typem warto�ci kolumny, do kt�rej zostanie dopisana. � �uery - prawid�owy rozkaz SELECT, kt�ry zwraca tak� ilo�� warto�ci jak podana w li�cie okre�laj�cej kolumny. Zapytanie nie mo�e mie� klauzuli ORDER FOR ani FOR UPDATE. Opis: Rozkaz INSERT u�yty z klauzul� VALUES zawsze dodaje dok�adnie jeden wiersz. Do p�l wyspecyfikowanych w li�cie kolumn (lub do wszystkich kolumn) wstawiane s� podane warto�ci. Kolumny nie wyspecyfikowane na li�cie kolumn przyjmuj� warto�ci puste NULL (w zwi�zlu z tym nie mog� by� uprzednio zadeklarowane jako NOT NULL). Je�li u�yje si� rozkazu SELECT zamiast klauzuli VALUES, to mo�liwe jest dodanie wi�kszej ilo�ci wierszy (wszystkich zwr�conych przez zapytanie). Po wykonaniu 17 I [ Bazy danych Robert Chwastek ] zapytania kolumny b�d�ce jego rezultatem s� dopasowywane i wpisywane do kolumn podanych na li�cie kolumn (lub do wszystkich kolumn, je�li ich nie wyspecyfikowano). Zapytanie mo�e odwo�ywa� si� r�wnie� do tabeli, do kt�rej dopisywane s� wiersze. W przypadku, gdy lista kolumn nie jest podana, to warto�ci s� dopasowywane do poszczeg�lnych kolumn na podstawie ich wewn�trznego porz�dku. Porz�dek ten nie musi by� taki sam jak kolejno�� kolumn przy tworzeniu tabeli. �aden wiersz nie zostanie dopisany, je�li zapytanie nie zwr�ci �adnych wierszy. Przyk�ady: INSERT INTO pracownicy VALUES (50, VAN', "KOWALSKI', 3); INSERT INTO ksi��ki (tytu�, autor, miejsce) SELECT 'Pan Tadeusz', autor_nr, miejsce_nr FROM autorzy, miejsca WHERE nazwisko = 'Mickiewicz' AND miejsce = 'lewa polka' } 3.3.5. Rozkaz DELETE Rozkaz DELETE s�u�y do usuwania wierszy z tabeli. Sk�adnia: DELETE [FROM] [user.Jtable [alias] [WHERE condition] Parametry: user - nazwa u�ytkownika table - nazwa tabeli lub widoku, z kt�rego nale�y usun�� wiersze alias - nazwa aliasu odnosz�cego si� do tabeli, kt�ry jest u�ywany w rozkazie DELETE z powi�zanymi zapytaniami condition - warunek jaki musz� spe�nia� wiersze, kt�re nale�y usun��. Warunek ten mo�e odwo�ywa� si� do tabeli, na kt�rej przeprowadza si� operacj� i zawiera� powi�zane z nim zapytania. Konieczne jest jednak, by warunek, dla ka�dego z wiersza podanej tabeli, by� obliczany do warto�ci TRUE lub FALSE. Opis: zatrzymywana przez t� tabel� i indeks. Ca�a przestrze� zwolniona przez skasowane wiersze i elementy indeks�w jest Przyk�ady: Skasowanie wszystkich wierszy w tabeli pracownicy: DELETE FROM pracownicy ; Skasowanie wszystkich wierszy zawieraj�cych ksi��ki, kt�rych autor oznaczony jest numerem 2: DELETE FROM ksi��ki WHERE autor = 2 ; I 18 I Bazy danych Robert Chwastek ] 3.3.6. Rozkaz CREATE SEQUENCE Tworzy obiekt (nazywany sekwencj�), za pomoc� kt�rego wielu u�ytkownik�w mo�e generowa� unikalne liczby ca�kowite. Sekwencj� mog� by� u�yte do generacji kluczy pierwotnych w spos�b automatyczny. Do utworzenia sekwencji konieczne s� przynajmniej uprawnienia RESOURCE w conajmniej jednej przestrzeni tabel. Sk�adnia: [MAXVALUE n [MINYALUE n CREATE SECjUENCE [user . ] se�uence [INCREMENT BY n] [START WITH n] NOMAXVALUE] NOMINYALUE] [CYCLE | NOCYCLE] [CACHE n | NOCACHE] [ORDER | NOORDER] Parametry: � user - nazwa u�ytkownika � se�uence - nazwa tworzonej sekwencji, musi by� poprawnym identyfikatorem i by� unikalna w obr�bie danego u�ytkownika. � INCREMENT BY - okre�la r�nic� mi�dzy kolejno generowanymi liczbami. Je�li liczba ta jest jest ujemna, to b�d� generowane liczby w porz�dku malej�cym, w przeciwnym wypadku - w porz�dku rosn�cym. Domy�lnie przyjmowana jest warto�� 1. Dozwolona jest ka�da liczba r�na od 0. � START WITH - pierwsza liczba, kt�ra powinna by� wygenerowana przez sekwencj�. Domy�ln� warto�ci� jest MINVALUE dla sekwencji rosn�cych i MAXVALUE dla sekwencji malej�cych. Utworzona sekwencja nie jest zainicjalizowana i pierwsz� warto�� otrzymuje si� po jednokrotnym odczytaniu pseudokolumny NEXTVAL. � MINVALUE - okre�la minimaln� warto�� jak� mo�e wygenerowa� sekwencja. Domy�lnie dla sekwencji rosn�cych jest to 1, natomiast dla malej�cych warto�� ta wynosi -10e27 + 1. Podanie NOMINVALUE powoduje, �e sekwencja nie b�dzie sprawdza� warto�ci minimalnej. � MAXVALUE - okre�lenie maksymalne warto�ci, jak� mo�e wygenerowa� sekwencja. Warto�ciami domy�lnymi s� -1 i 10e27 - 1 odpowiednio dla sekwencji malej�cej i rosn�cej. Wyspecyfikowanie NOMAXVALUE powoduje, �e sekwencja nie b�dzie sprawdza� warto�ci maksymalnej. � CYCLE, NOCYCLE - domy�ln� warto�ci� jest NOCYCLE, kt�re powoduje, �e �adne dodatkowe numery nie zostan� wygenerowane po osi�gni�ciu ko�ca sekwencji. W tym wypadku ka�da pr�ba generacji kolejnego numeru spowoduje zg�oszenie b��du. W przypadku podania klauzuli CYCLE po osi�gni�ciu warto�ci maksymalnej sekwencja powr�ci do warto�ci minimalnej (dla sekwencji rosn�cych) lub po osi�gni�ciu warto�ci minimalnej powr�ci do maksymalnej (dla sekwencji malej�cych) rozpoczynaj�c kolejny cykl generacji numer�w. 19 [ Bazy danych Robert Chwastek ] � CACHE, NOCACHE - klauzula CACHE w��cza wykonywanie pre-alokacji numer�w sekwencji i przechowywanie ich w pami�ci, co skutkuje zwi�kszeniem szybko�ci generacji kolejnych liczb. Klazula NOCACHE wy��cza t� mo�liwo��. Domy�lnie przyjmowane jest CACHE 20. Warto�� podana w CACHE musi by� mniejsza ni� MAXVALUE - MINVALUE. � ORDER, NOORDER - klauzula ORDER gwarantuje, �e kolejne liczby b�d� generowane w porz�dku jakim otrzymane zosta�y przez system polecenia ich generacji. Klauzula NOORDER wy��cz� t� w�asno��. Kolejno�� generacji numer�w w sekwencji jest wa�na w aplikacjach, w kt�rych wa�na jest kolejno�� (czasowa) wykonywanych operacji. Zwykle nie jest ona wa�na w aplikacjach, kt�re wykorzystuj� sekwencje tylko do generacji kluczy pierwotnych. Opis: Sekwencje mog� by� u�ywane do generacji kluczy pierwotnych dla jednej tabeli lub wielu tabel i wielu u�ytkownik�w. Aby mie� dost�p do sekwencji, kt�rej w�a�cicielem jest inny u�ytkownik, nale�y mie� uprawnienia SELECT do tej sekwencji. Sekwencja mo�e posiada� synonim. Numery w sekwencjach s� generowane niezale�nie od tabel, dlatego mog� by� u�ywane jako liczby unikalne dla kilku r�nych tabel i u�ytkownik�w. Jest jednak mo�liwe, �e niekt�re numery z sekwencji zostan� pomini�te, poniewa� zosta�y one wygenerowane i u�yte w transakcji, kt�ra nast�pnie zosta�a wycofana. Dodatkowo jeden u�ytkownik mo�e nie zdawa� sobie sprawy, �e inni u�ytkownicy korzystaj� z tej samej sekwencji (co r�wnie� skutkuje pomini�ciem numer�w dla tego u�ytkownika). Dost�p do sekwencji zapewniaj� dwie pseudokolumny: NEXTVAL i CURRVAL. Pseudokolumna NEXTVAL jest u�ywana do generacji nast�pnej warto�ci z podanej sekwencji. Sk�adnia jest nast�puj�ca: se�uence.NEXTVAL gdzie se�uence jest nazw� sekwencji. Pseudokolumna CURRVAL pozwala na odczytanie aktualnej warto�ci sekwencji. Aby mo�liwe by�o u�ycie CURRVAL konieczne jest wcze�niejsze u�ycie NEXTVAL w aktualnej sesji dla danej sekwencji. Sk�adnia tego rozkazu jest nast�puj�ca: se�uence.CURRVAL gdzie se�uence jest nazw� sekwencji. Pseudokolumny NEXTVAL i CURRVAL mog� by� u�ywane w: � w klauzuli SELECT i rozkazie SELECT (z wyj�tkiem widok�w) � li�cie warto�ci rozkazu INSERT � wyra�eniu SET w rozkazie UPDATE Pseudokolumn NEXTVAL i CURRVAL nie mo�na u�ywa� w: podzapytaniach w li�cie select dla widok�w � ze s�owem kluczowym DISTINCT � z klauzulami ORDER BY, GROUP BY i HAVING w rozkazie SELECT � z operatorem ustawienia (UNION, INTERSECT, MINUS) 20 Bazy danych Robert Chwastek ] Przyk�ady: CREATE SEQUENCE eseq INCREMENT BY 10 ; INSERT INTO pracownicy VALUES (eseq.NEXTVAL, 'Jan', 'Kowalski', 3) ; 3.3.7. Rozkaz SELECT Rozkaz SELECT s�u�y do wy�wietlania wierszy i kolumn z jednej lub kilku tabel. Mo�e by� u�ywany jako osobny rozkaz lub (z pewnymi ograniczeniami) jako zapytanie lub podzapytanie w innych poleceniach. Aby odczyta� dane z okre�lonej tabeli trzeba by� jej w�a�cicielem, mie� uprawnienia SELECT dla tej tabeli lub by� administratorem bazy (DBA). Sk�adnia: SELECT [ALL | DISTINCT] {* | table.* | expr [c_alias] } [r { table.* | expr [c_alias] } ] ... FROM [user.]table [t_alias] [, [user.jtable [t_alias]] ... [ WHERE condition ] [ CONNECT BY condition [START WITH condition] ] [ GROUP BY expr [. Expr] ... [HAVING condition] ] [ {UNION | INTERSECT | MINUS} SELECT ...] [ ORDER BY {expr | position} [ASC | DESC] [, {expr | position} [ASC | DESC]] ] ... [ FOR UPDATE OF column [, column] ... [NOWAIT] ] Parametry: � ALL - ustawiane domy�lnie, oznacza, �e wszystkie wiersze, kt�re spe�niaj� warunki rozkazu SELECT powinny zosta� pokazane. � DISTINCT - okre�la, �e wiersze powtarzaj�ce si� powinny zosta� usuni�te przed zwr�ceniem ich na zewn�trz. Dwa wiersze traktuje si� jako r�wne je�li wszystkie warto�ci dla ka�dej z kolumn zwracanych rozkazem SELECT s� sobie r�wne. � * - oznacza, �e wszystkie kolumny ze wszystkich wymienionych tabel powinny zosta� pokazane. � table.* - oznacza, �e wszystkie kolumny z podanej tabeli powinny zosta� pokazane � expr - wyra�enie, zostanie opisane w dalszej cz�ci wyk�adu � c_alias - jest inn� nazw� dla kolumny (aliasem) i powoduje, �e nazwa ta zostanie u�yta jako nag��wek kolumny podczas wy�wietlania. W �aden spos�b nie jest zmieniana rzeczywista nazwa kolumny. Aliasy kolumn nie mog� by� u�ywane w dowolnym miejscu zapytania. � [user.]table - okre�la kt�re tabele i widoki nale�y pokaza�. Je�li u�ytkownik nie jest podany to domy�lnie przyjmowany jest u�ytkownik aktualny (wykonuj�cy rozkaz SELECT). 21 [ Bazy danych Robert Chwastek ] � t_alias - pozwala okre�li� inn� nazw� dla tabeli w celu obliczenia zapytania. Najcz�ciej jest u�ywane w zapytaniach powi�zanych. W tym wypadku inne odwo�ania do tabeli wewn�trz zapytania musz� pos�ugiwa� si� wyspecyfikowanym aliasem. � condition - warunek, jaki musz� spe�nia� wiersze, aby zosta�y zwr�cone przez zapytanie. Warunki zostan� opisane dik�adniej w dalszej cz�ci wyk�adu. � position - identyfikuje kolumn� bazuj�c na jej tymczasowym po�o�eniu w rozkazie SELECT, a nie na nazwie. � ASC, DESC - okre�la, �e zwracane wiersze powinny by� posortowane w kolejno�ci rosn�cej lub malej�cej (odpowiednio). � column - nazwa kolumny nale��ca do jednej z tabel podanych w klauzuli FROM. � NOWAIT - okre�la, �e ORACLE powinien zwr�ci� sterowanie do u�ytkownika, zamiast czeka� na mo�liwo�� zablokowania wiersza, kt�ry zosta� uprzednio zablokowany przez innego u�ytkownika. Opis: U�ycie nazwy tabeli przed nazw� kolumny i nazwy u�ytkownika przed nazw� tabeli jest najcz�ciej opcjonalne, to jednak dobrym zwyczajem jest podawanie nazw w pe�ni kwalifikowanych z dw�ch powod�w: � je�li dwie tabele maj� kolumny o tej samej nazwie, to nie wiadomo, kt�ra powinna by� u�yta w rozkazie SELECT � ORACLE wykonuje znacznie mniej oblicze�, je�li nazwy te s� podane i nie trzeba ich szuka�. Pozosta�e operacje wykonywane przez rozkaz SELECT zostan� opisane w dalszej cz�ci wyk�adu. Przyk�ady: SELECT imi�, nazwisko FROM pracownicy ; SELECT tytu�, autorzy.imi�, autorzy.nazwisko, miejsca.miejsce FROM ksi��ki, autorzy, miejsca WHERE ksi��ki.autor = autorzy.autor_nr AND ksi��ki.miejsce = miejsca.miejsce_nr } 3.3.8. Rozkaz UPDATE Rozkaz UPDATE s�u�y do zmiany danych zapisanych w tabeli. Warunkiem wykonania tego polecenia jest bycie w�a�cicielem tabeli, administratorem (DBA) lub posiadanie uprawnie� UPDATE dla tej tabeli. Sk�adnia: UPDATE [user.Jtable [alias] SET column = expr [, column = expr] ... [ WHERE condition ] 22 Bazy danych Robert Chwastek ] lub UPDATE [user.Jtable [alias] SET (column [, column] ...) = (query) [, column [, column] ...) = (query) ] ... [ WHERE condition ] Parametry: � user - nazwa w�a�ciciela tabeli. � table - nazwa istniej�cej tabeli. � alias - dodatkowa nazwa u�ywana do dost�pu do tabeli w pozosta�ych klauzulach rozkazu. � column - kolumna wewn�trz tabeli. Nawiasy nie s� potrzebne je�li lista kolumn zawiera tylko jedn� kolumn�. � expr - wyra�enie - zostanie opisane w dalszej cz�ci wyk�adu � �uery - rozkaz SELECT bez klauzul ORDER BY i FOR UPDATE, cz�sto skorelowany ze zmienian� tabel�. � condition - poprawny warunek. Warunek musi zwraca� warto�� TRUE lub FALSE. Warunki b�d� opisane w dalszeuj cz�ci wyk�adu Opis: Klauzula SET okre�la, kt�re kolumny zostan� zmienione i jakie nowe warto�ci maj� by� w nich zapisane. Klauzula WHERE okre�la warunki jakie musz� spe�nia� wiersze, w kt�rych nale�y wymieni� warto�ci podanych wcze�niej kolumn. Je�li klauzula WHERE nie jest podana, to zmieniane s� wszystkie wiersze w tabeli. Rozkaz UPDATE dla ka�dego wiersza, kt�ry spe�nia warunki klauzuli WHERE oblicza warto�ci wyra�e� znajduj�cych si� po prawej stronie operatora '=' i przypisuje te warto�ci do pola okre�lanego przez nazw� kolumny z lewej strony. Je�li klauzula SET posiada podzapytanie, to musi ono zwr�ci� dok�adnie jeden wiersz dla ka�dego ze zmienianych wierszy. Ka�da warto�� jest przypisywana zgodnie z kolejno�ci� na li�cie kolumn. Je�li zapytanie (w przypadku klauzuli postaci SET value = �uery) nie zwr�ci wierszy to odpowiednie pola s� ustawiane na NULL. Zapytanie mo�e odwo�ywa� si� do zmienianej tabeli. Jest ono obliczane oddzielnie dla ka�dego zmienianego wiersza a nie dla ca�ego rozkazu UPDATE. Przyk�ady: UPDATE pracownicy SET nr_wydzia�u = 4 WHERE nr wydzia�u = 3 3.3.9. Rozkaz RENAME Rozkaz RENAME zmienia nazw� tabeli, widoku lub synonimu. Zmiany mo�e dokona� w�a�ciciel tabeli, widoku lub synonimu. 23 [ Bazy danych Robert Chwastek Sk�adnia: RENAME old TO new Parametry: old - aktualna nazwa tabeli, widoku lub synonimu new - ��dana nazwa tabeli, widoku lub synonimu Opis: Wszystkie pozwolenia, kt�re posiada� obiekt o starej nazwie, przechodz� na obiekt o nowej nazwie. Za pomoc� tego rozkazu nie mo�na zmienia� nazw kolumn. Zmiana nazwy kolumny mo�e by� dokonana za pomoc� trzech rozkaz�w: CREATE TABLE, DROP TABLE i RENAME w nast�puj�cy spos�b: CREATE TABLE temporary (new_column_name) AS SELECT old_column_name FROM table ; DROP TABLE table ; RENAME temporary TO table ; Przyk�ady: RENAME wydzia�y TO jednostki ; 3.3.10. Rozkaz ALTER TABLE Rozkaz s�u��cy do zmieniania tabeli. Wykonuje nast�puj�ce operacje: � dodaje kolumny i warunki � modyfikuje definicje kolumn jak typy i warunki � usuwa warunki � modyfikuje przysz�y spos�b alokacji przestrzeni � zapisuje, �e operacja BACKUP zosta�a wykonana dla tej tabeli. Aby wykona� t� operacj� trzeba by� w�a�cicielem tabeli, mie� uprawnienia ALTER dla tabeli lub by� administratorem (DBA). Sk�adnia: ALTER TABLE [user.]table [ADD ( {column_element | table_constraint} [r {column_element | table_constraint}] ...) ] [MODIFY (column_element [,column_element] ���)] [DROP CONSTRAINT constraint] ... [PCTFREE integer] [PCTUSED integer] [INITTRANS integer] [MAXTRANS integer] [STORAGE storage] [BACKUP] Parametry: � [user.]table - w�a�ciciel i tabela, kt�r� trzeba zmieni�. Je�li nazwa u�ytkownika nie jest podana, to domy�lnie przyjmowany jest u�ytkownik, kt�ry wywo�a� rozkaz. 24 [ Bazy danych Robert Chwastek ] � ADD/MODIFY column_element - dodaje lub modyfikuje definicj� kolumny, ograniczenia kolumny lub warto�ci domy�lne okre�lonej kolumny. � ADD table_constraint - dodaje ograniczenia na warto�ci w tabeli. � DROP constraint - usuwa podan� kolumn� lub ograniczenie. � BACKUP - zmienia zawarto�� s�ownika danych (Data Dictionary) tak, �e zostaje zapisana informacja o wykonaniu backup'u tabeli, kt�ry nast�pi� w czasie wykonywania rozkazu ALTER TABLE. Opis: Je�li u�yta zostanie klauzula ADD w celu dodania nowej kolumny do istniej�cej tabeli, to warto�� ka�dego pola w tej kolumnie b�dzie r�wna NULL. W zwi�zku z tym mo�liwe jest dodanie kolumny z warunkiem NOT NULL tylko do kolumn, kt�re nie maj� wierszy. Klauzula MODIFY mo�e zosta� u�yta do zmiany nast�puj�cych atrybut�w kolmny: � rozmiar � typ danych � NOT NULL Zmiana typu lub zmniejszenie rozmiaru mo�liwe jest tylko wtedy, gdy wszystkie warto�ci w kolumnie s� r�wne NULL. Mo�liwe jest na�o�enie ograniczenia NOT NULL na istniej�c� kolumn� tylko wtedy, gdy nie zawiera ona warto�ci pustych. Je�li zmieniany jest rozmiar kolumny zadeklarowanej jako NOT NULL i w klauzuli MODIFY nie poda si� NOT NULL, to kolumna nadal pozostaje z warunkiem NOT NULL. W przypadku widok�w z zapytaniem wybieraj�cym wszystkie kolumny tabeli (SELECT * FROM ...), widok mo�e nie pracowa� poprawnie, je�li do tabeli, z kt�r� jest powi�zany zosta�a dodana nowa kolumna. Przyk�ady: ALTER TABLE pracownicy ADD (p�aca NUMBER(7, 2)) ALTER TABLE pracownicy MODIFY (p�aca NUMBER(9, 2)) 3.3.11. Rozkaz CREATE INDEX Rozkaz tworzy nowy indeks dla tabeli lub klastra. Indeks zapewnia bezpo�redni dost�p do wierszy w tabeli w celu zredukowania czasu wykonywania operacji. Indeks zawiera informacj� o ka�dej warto�ci, kt�ra jest zapisana w indeksowanej kolumnie. Indeks mo�e utworzy� w�a�ciciel tabeli, u�ytkownik posiadaj�cy uprawnienia INDEX dla danej tabeli lub administrator (DBA). Sk�adnia: CREATE [UNIQUE] INDEX index ON {table(column [ASC|DESC][, column [ASC|DESC]]...) | 25 I [ Bazy danych Robert Chwastek CLUSTER cluster} [INITTRANS n] [MAXTRANS n] [TABLESPACE tablespace] [STORAGE storage] [PCTFREE n] [NOSORT] Parametry: � UNIQUE - zak�ada, �e tabela nie ma nigdzie dw�ch wierszy zawieraj�cych te same warto�ci we wszystkich indeksowanych kolumnach. W aktualnej wersji ORACLE'a je�li indeks typu UNIQUE nie zostanie utworzony dla tabeli, to tabela mo�e zawiera� powtarzaj�ce si� wiersze. � indeks - nazwa tworzonego indeksu. Nazwa ta musi by� inna od ka�dego innego obiektu bazy danych danego u�ytkownika. � table - nazwa istniej�cej tabeli, dla kt�rej tworzy si� indeks. � column - nazwa kolumny w tabeli. � ASC, DESC - zosta�y dodane w systemie ORACLE w celu zapewnienia kompatybilno�ci z systemem DB2, ale zawsze s� tworzone w porz�dku rosn�cym. � CLUSTER cluster - okre�la klaster, dla kt�rego tworzony jest indeks � NOSORT - wskazuje ORACLE'owi, �e wiersze przechowywane w bazie s� ju� posortowane, w zwi�zku z czym nie jest konieczne sortowanie podczas tworzenia indeksu. Opis: Indeksy s� tworzone w celu przyspieszenia operacji: � dost�pu do danych w posortowanych wed�ug kolumn indeksowanych � wyszukiwania wierszy, zawieraj�cych dane z indeksowanych kolumn. Nale�y jednak zwr�ci� uwag�, �e indeks spowalnia wstawianie, usuwanie i zmiany warto�ci w indeksowanych kolumnach, poniewa� jego zawarto�� musi ulec zmianie w momencie zmiany zawarto�ci tabeli. Do jednego indeksu wstawionych mo�e by� co najwy�ej 16 kolumn. Jeden element indeksu jest konkatenacj� warto�ci tych kolumn w poszczeg�lnych wierszach. W momencie wyszukiwania mo�e by� u�yty ca�y element indeksu lub pewna jego cz�� pocz�tkowa. Dlatego kolejno�� kolumn w indeksie jest wa�na. Je�li wi�c indeks zostanie utworzony na podstawie trzech kolumn A, B, C w takiej kolejno�ci, to zostanie on u�yty do wyszukiwania konkatenacji kolumn A, B, C, kolumn A i B lub tylko kolumny A. Nie b�dzie natomiast u�ywany w przypadku wyszukiwania po��czenia kolumn B i C lub pojedynczej kolumny B lub C. Mo�liwe jest utworzenie dowolnej ilo�ci indeks�w dla jednej lub kilku tabel. Nale�y jednak pami�ta�, �e opr�cz spowolnienia operacji modyfikacji tabeli, indeksy zajmuj� r�wnie� do�� du�� ilo�� miejsca na dysku. Przyk�ady: CREATE INDEX i_prac_imie ON pracownicy (imi�) ; I 26 I Bazy danych Robert Chwastek ] 3.3.12. Rozkaz CREATE VIEW Rozkaz s�u��cy do tworzenia widoku, czyli logicznej tabeli bazuj�cej na jednej lub wielu tabelach. Utworzy� widok mo�e w�a�ciciel tabel, u�ytkownik posiadaj�cy do nich co najmniej uprawnienia SELECT lub administrator. Sk�adnia: CREATE VIEW [user.]view [(alias [, alias] ...)] AS query [ WITH CHECK OPTION [CONSTRAINT constraint] ] Parametry: � user - w�a�ciciel tworzonego widoku � view - nazwa tworzonego widoku � �uery - identyfikuje kolumny i wiersze tabel, na kt�rych bazuje widok. Zapytanie mo�e by� dowolnym poprawnym rozkazem SELECT nie zawieraj�cym kluzul ORDER BY ani FOR UPDATE. � WITH CHECK OPTION - informuje, �e wstawienia i zmiany wykonywane poprzez widok, s� niedozwolone je�li spowoduj� wygenerowanie wierszy, kt�re b�d� niedost�pne dla widoku. Klauzula WITH CHECK OPTION mo�e by� u�yta w widoku bazuj�cym na innym widoku. � CONSTRAINT - nazwa do��czona do warunku WITH CHECK OPTION. Opis: Widok jest logicznym oknem dla jednej lub kilku tabel. Widok ma nast�puj�ce w�a�ciwo�ci: � widok nie przechowuje danych - jest on przeznaczony do pokazywania danych zawartych w innych tabelach. � widok mo�e by� u�yty w rozkazie SQL w dowolnym miejscu, w kt�rym mo�liwe jest u�ycie tabeli z zastrze�eniem, �e mo�na wykonywa� selekcj� z widoku tylko wtedy, gdy zapytanie na kt�rym bazuje widok zawiera: ? po��czenie ? klauzule GROUP BY, CONNECT BY lub START WITH ? klauzul� DISTINCT, pseudokolumny lub wyra�enia na li�cie kolumn Mo�liwa jest zmiana danych zawartych w widoku, kt�ry posiada pseudokolumny lub wyra�enia dot�d dop�ki rozkaz UPDATE nie odwo�uje si� do pseudokolumny lub wyra�enia. Widoki s� u�ywane do: � utworzenia dodatkowego poziomu zabezpieczenia tabeli poprzez ograniczenie dost�pu do okre�lonych kolumn lub wierszy tabeli bazowej � ukrycia z�o�ono�ci danych - na przyk�ad widok mo�e by� u�yty do operacji na wielu tabelach tak, by wydawa�o si�, �e operacje wykonywane s� na jednej tabeli. 27 [ Bazy danych Robert Chwastek ] � pokazywania danych z innej perspektywy - dla przyk�adu widok mo�e zosta� u�yty do zmiany nazwy kolumny bez zmiany rzeczywistych danych zapisanych w tabeli. � zapewnienia poziomu integralno�ci. Przyk�ady: CREATE VIEW bibl AS SELECT ksi��ki.tytu�, autorzy.imi�, autorzy.nazwisko, miejsca.miejsce FROM ksi��ki, autorzy, miejsca WHERE ksi��ki.autor = autorzy.autor_nr AND ksi��ki.miejsce = miejsca.miejsce_nr WITH CHECK OPTION CONSTRAINT chkopt } 3.3.13. Rozkaz COMMIT Sk�adnia: COMMIT [WORK] Opis: Rozkaz COMMIT i COMMIT WORK wykonuj� t� sam� operacj� polegaj�c� na zako�czeniu aktualnej transakcji i sta�ym zapisaniu wszystkich dokonanych zmian w bazie danych. 3.3.14. Rozkaz ROLLBACK Sk�adnia: ROLLBACK [ WORK ] [TO [ SAVEPOINT ] savepoint ] Parametry: � WORK - opcjonalne, wprowadzone tylko dla kompatybilno�ci ze standardem ANSI � SAVEPOINT - opcjonalne, nie zmienia dzia�ania rozkazu ROLLBACK � savepoint - nazwa punktu zaznaczonego podczas wykonywania aktualnej transakcji. Opis: Rozkaz ROLLBACK wycofuje wszystkie zmiany a� do podanego punktu (w przypadku klauzuli TO) lub pocz�tku transakcji (bez klauzuli TO). Przyk�ady: ROLLBACK ; ROLLBACK TO SAVEPOINT SP5 ; 3.3.15. Rozkaz SAVEPOINT Sk�adnia: SAVEPOINT savepoint I 28 I Bazy danych Robert Chwastek ] Parametry: � savepoint - nazwa punktu w aktualnej transakcji zaznaczanego przez wykonywany rozkaz Opis: Rozkaz SAVEPOINT jest u�ywany w po��czeniu z ROLLBACK do wycofywania fragment�w wykonywanej transakcji. Nazwy punkt�w musz� by� unikalne w jednej transakcji. Systemy zarz�dzania bazami danych wprowadzaj� najcz�ciej ograniczenia na liczb� punkt�w, kt�re mo�na zaznaczy� w jednej transakcji. Przyk�ady: UPDATE pracownicy SET placa_podstawowa = 2000 WHERE nazwisko = ^Kowalski' } SAVEPOINT Kow_plac; UPDATE pracownicy SET placa_podstawowa = 1500 WHERE nazwisko = 'Nowak' } SAVEPOINT Now_plac; SELECT SUM(placa_podstawowa) FROM pracownicy; ROLLBACK TO SAVEPOINT Kow_plac; UPDATE pracownicy SET placa_podstawowa = 1300 WHERE nazwisko = 'Nowak' COMMIT; 3.3.16. Rozka