Robert Chwastek
Bazy danych
Bazy danych
Robert Chwastek
!" # $ % & "' w Krakowie. ( # ) * Copyright (c) Robert Chwastek, Kraków 1996.
2
Bazy danych
Robert Chwastek
+ #, 1.
E ............................................................................................................................................ 5 1.1. 1.2. 1.3. 1.4.
2.
TYPY DANYCH ............................................................................................................................................................... 8 2.1. 2.2. 2.3. 2.4.
3.
DEFINICJA BAZY DANYCH ............................................................................................................................................... 5 S ........................................................................................................................... 6 TRANSAKCJE .................................................................................................................................................................. 6 J ........................................................................................................................ 7 SPIS TYPÓW DANYCH ...................................................................................................................................................... 8 TYP NUMERYCZNY ......................................................................................................................................................... 8 KONWERSJE TYPÓW ..................................................................................................................................................... 10 W ......................................................................................................................................................... 10
MODEL RELACYJNY .................................................................................................................................................. 12 3.1. STRUKTURY DANYCH W MODELU RELACYJNYM ........................................................................................................... 12 3.2. R SQL ................................................................................................................................................ 13 3.3. S SQL ......................................................................................................................................... 14 3.3.1. Definicje podstawowe ............................................................................................................................................ 14 3.3.2. Rozkaz CREATE TABLE........................................................................................................................................ 15 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. Rozkaz SELECT ..................................................................................................................................................... 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. .............................................................................................................................................................. 32 3.4.5. .......................................................................................................................................... 33 3.4.6. Grupowanie ........................................................................................................................................................... 34 3.4.7. .................................................................................................................. 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. Pierwsz ..................................................................................................................................... 39 3.8.3. Definicje pomocnicze............................................................................................................................................. 40 3.8.4. ......................................................................................................................................... 44 3.8.5. ....................................................................................................................................... 46 3.8.6. Czwarta pos ...................................................................................................................................... 47 3.8.7. ........................................................................................................................................... 48 3.8.8. Podsumowanie....................................................................................................................................................... 48
4.
IA.......................................................................................................................................... 50 4.1. OPERATORY ................................................................................................................................................................. 50 4.1.1. Operatory arytmetyczne......................................................................................................................................... 50 4.1.2. Operatory znakowe ................................................................................................................................................ 50 4.1.3. Operatory porównania .......................................................................................................................................... 51 4.1.4. Operatory logiczne ................................................................................................................................................ 52 4.1.5. ! ........................................................................................................................................ 53
3
Bazy danych Robert Chwastek 4.2. W ................................................................................................................................................................. 53 4.3. 5.
WARUNKI..................................................................................................................................................................... 55
STANDARDOWE FUNKC ............................................................................................................. 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.1. 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. O ....................................................................................................................................................... 71 6.6.1. Informacje podstawowe......................................................................................................................................... 71 6.6.2. "! #$ ......................................................................................................................................... 73 6.6.3. % ! &.................................................................................................................................................. 73 6.6.4. "! #$ '! ............................................................................................................... 74 6.7. R PL/SQL .......................................................................................................................................... 75 6.7.1. Rozkaz OPEN ........................................................................................................................................................ 75 6.7.2. Rozkaz CLOSE....................................................................................................................................................... 76 6.7.3. Rozkaz FETCH ...................................................................................................................................................... 76 6.7.4. Rozkaz SELECT ... INTO....................................................................................................................................... 77 6.7.5. Rozkaz IF ............................................................................................................................................................... 77 6.7.6. Rozkaz LOOP ........................................................................................................................................................ 78 6.7.7. Rozkaz EXIT .......................................................................................................................................................... 80 6.7.8. Rozkaz GOTO ........................................................................................................................................................ 80
7.
LITERATURA................................................................................................................................................................ 81
4
Bazy danych
1. 1.1.
Robert Chwastek
Definicja bazy danych
W pewnym uproszczeniu przez - danych, a przez system bazy danych " - ) * # * . - ) / ) - - - # 0 # - # 1* 2 # ) -/ rozumiany jako: #/ " #/ " # - 3 -4 # ) -/ - 3 * . 0 1 - )
# * ( jest opis semantyki (znaczenia) danych, przechowywanych w bazie. System bazy danych ) # 0 1* ( - #/ modelu danych. Przez model danych rozumiemy - - / ) # # # * 5- / ) # # rzeczywistego, istotnych z punktu widzenia danego zastosowania tworzy schemat bazy danych. Baza danych jest modelem logicznie spójnym ) # celowi. W - ) 0 1 / #* 5 - ## # ) * + ) - - aplikacjami*
* & -
- -/ *
- ) # tym celu aplikacje. 6) / ) ) - , 3 ) # * - * 0 #1 - * ) - ) * Schemat jest opisem struktury (formatu) przechowywanych danych oraz wzajemnych 7 *
5
Bazy danych
1.2.
Robert Chwastek
danych (SZBD) jest to zestaw programów )
- * + - jest oprogramowaniem ogólnego przeznaczenia. System bazy danych - -
) *
1.3.
Transakcje
$ - #/ 0#/ #1* $
* - ) / - ) * ( # - przypadku niepowodzenia którejkolwiek z nich wycofuje instrukcje uprzednio wykonane. . # - ,
6
Bazy danych
1.4.
Robert Chwastek
8 - ) / ) , 0 9 " 91 ) definiowanie struktury danych przechowywanych w bazie, czyli tworzenie schematu implementacyjnego 0 6 9 " 691 )
- * 0 : 9 " :91 ) transakcjami (np. zatwierdzanie lub wycofywanie) 7 0; 9 1 ) - - zgodnych z podanymi warunkami
7
Bazy danych
2.
Typy danych
2.1.
Spis typów danych
Typ char(size)
character varchar(size)
date long long varchar raw(size)
long raw
rowid
number
2.2.
Robert Chwastek
Opis : # ) *
) / Synonim do char W aktualnej wersji ORACLE’a jest to synonim do char, konieczne jest * ( ) - # ? * Poprawne daty z zakresu 1 stycznia 4712 p.n.e. do 31 grudnia 4712 n.e. # @ "6A"BB *, CD>"8%"EFG : # ) H==!= * 6) / - * synonim do long : - #* + * I *D K 10-129 do 9.99 * 10124* 6) - )*
Typ numeryczny
$ ) - * ) / trzech sposobów: number number (precyzja) number (precyzja, skala) 8
Bazy danych
Robert Chwastek
. # - ) / > !E* + # - ) / -84 do 127. W momencie definiowania kolumny numerycznej dobrym zwyczajem jest )
# # - * 8# #/ -* 8# #/ * 8# #/ - 4 * 0>D "D - )/ * 9.87E-2 oznacza 9.87 * 10-2. N - ) AI%:9MG -* 6) * - - - ,
9
Bazy danych
Robert Chwastek
Specyfikacja number number(*) number(*, s) number(p) number(p,s) decimal decimal(*) decimal(*, s) decimal(p) decimal(p, s) integer smallint float float(*) float(b) real double precision
2.3.
Typ number number number number number number number number number number number number number number number number number
Precyzja 38 38 38 p p 38 38 38 p p 38 38 38 38 b 63 binary (18 decimal) 38
Skala null null s 0 s 0 0 s 0 s 0 0 null null null null null
Konwersje typów
( - ) * / - - ) * / - / * .) - ) AI%:9M, Z typu char number date
2.4.
Do typu
char
number
date
- TO_CHAR TO_CHAR
TO_NUMBER - )
TO_DATE TO_DATE -
. - / # ) - - * ( #/ 0J991 # D - ) J99 ) #/ 0J991* 2 O9 / # 0 1 # # # * O9 , 10
Bazy danych
Robert Chwastek
O90:A66 D1 #/ :A66 #/ - D # :A66 #/ J99* ( #/ #/ J99* * - # #, >DDD J99 J99 J99 =DD ) 0>DDD P =DD* 8 ) )/ # N+ J99 N+ A$ J99* 8# ) # # * . ) J99 - # ) -/ # ) J99* AI%:9M * $
:A66 R J99 +M9M:$
) * 8 AI%:9M - *
11
Bazy danych
3.
Robert Chwastek
Model relacyjny
5 - podstawowych elementów: relacyjnych struktur danych )
bazy danych # - # #
3.1.
Struktury danych w modelu relacyjnym
. - - nu 7 - - #* ( bazach danych relacja przedstawiana jest w postaci tabeli. Relacja jest zbiorem krotek ) #* S )
- * S ) - - * S ) 0 - 1 #, 0 1 - 0 1 #/ 0 1 #/ - 0 1 # - 0 1 . - ,
! "
#
'()*)+)*,-'
.
$ %
&
/"
&
3
4
01)-2))(,-'
++2)2()()*+
!
'))2)2)2(*2
!
5
&
112(2()2((*
6 %
!"
&78 / "
- #
- * ( ) 12
Bazy danych
Robert Chwastek
- # ) *
- # - * ( # - - jedna jak i druga terminologia. $ - ) /, zbiór encji wraz z atrybutami - 7 - - - 0 - 1 S ) - - * ( - / " zabezpieczenie przed tym powtórzeniem jest realizowane poprzez pola kluczowe. Wiersze
) " - * (
- - " ) ) #/ #* ( - - ) / , J) - * S / / - 0)
#/1* 3 - - tabeli. $ - " - #
)* J
) - * Unikaj powtarzania informacji w bazie danych (normalizacja).
3.2.
QL
.) - +;9 * A - # # * Rozkaz ALTER TABLE
Typ DDL
CREATE INDEX CREATE SEQUENCE
DDL DDL
CREATE TABLE
DDL
Opis - - - #/ Tworzy indeks dla tabeli $ - ) - " * + ) )/ unikalnych identyfikatorów w tabelach $ - przestrzeni dla danych 13
Bazy danych
Robert Chwastek
CREATE VIEW
DDL
DELETE DROP obiekt INSERT RENAME SELECT
DML DDL DML DDL DML
UPDATE COMMIT ROLLBACK
DML DML DML
SAVEPOINT
DML
SET TRANSACTION
DDL
3.3. 3.3.1.
- # - innych widoków J - ) - J - - -
Dodaje nowy wiersz (lub wiersze) do tabeli lub widoku 5 - - - Wykonuje zapytanie. Wybiera wiersze i kolumny z jednej lub kilku tabel Zmienia dane w tabeli S7 ( - punktu. Zaznacza punkt, do którego mozliwe jest wykonanie rozkazu ROLLBACK 5 " 0 odczytu).
! Definicje podstawowe
N 0 1 " # - # * I ) - ) 0* CTG - CUG1* + 3 - ## napisanych w ten sposób aplikacji. + " ) +;9* + +;9
ORACLE’a przedstawia tabela: access as check create delete exists graphic in integer lock noaudit of order
add asc cluster current desc file group increment intersect long nocompress offline pctfree
all audit column date distinct float having index into maxextents not on prior
alter between comment dba drop for identified install is minus nowait online privileges 14
and by compress decimal else from if initial level mode null option public
any char connect default exclusive grant immediate insert like modify number or raw
Bazy danych
rename rows smallint then update view
resource select start to user whenever
Robert Chwastek
revoke session successful trigger validate where
row set synonym uid values with
rowid share sysdate union varchar
rownum size table unique vargraphic
9- " -/ - * 9- * ( AI%:9M - ) / * 2 - - >D ) )/ - CG - CMG* . , 7E2 = 7 * 102 25e-03 = 25 * 10-3 AI%:9M - ) 7/ CSG - C6G* 9 CSG ) - -/ ) >D S1 C6G ) - ) )/ >DLE=VH 0> 61* . , 256K = 256 * 1024 1M = 1 * 1048576 I +;9 7 # 3.3.2.
Rozkaz CREATE TABLE
I :IM%$M $%9M )
- 0- 1 ) , # - # - - # - - I :IM%$M $%9M , 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 query ] Parametry: " # - # # -
- * $ - ) )
/ - 0%1
15
Bazy danych
Robert Chwastek
- " - -/ * ( - ) / W " # * $ - / 0 1 - W " # / - - " # - ) #/ - " # - " # 0 # -/ # - 1 ) / - query - jest poprawnym zapytaniem takim samym jak zdefiniowane w rozkazie +M9M:$* 8# ) / " # * 6) ) - * 9- -/ sama jak liczba kolumn w zapytaniu. . , CREATE TABLE pracownicy( nr_pracownika NUMBER NOT NULL PRIMARY KEY, imie CHAR(15) NOT NULL CHECK (imie = UPPER(imie)), nazwisko CHAR(25) NOT NULL CHECK (nazwisko = UPPER(nazwisko)), nr_wydzialu NUMBER (3) NOT NULL ); 3.3.3.
Rozkaz DROP
I ) - ) * A / , DROP object_type [user.]object .) ) ) poszczególnych typów obiektów: DROP CLUSTER [user.]cluster [INCLUDING TABLES] - kasowanie * ( N:9JN& $%9M+
- * 8# N:9JN& $%9M+ -/ ) - * 8 - - -* DROP [PUBLIC] DATABASE LINK link " * 8# - / ) 0%1* DROP INDEX [user.]index - kasowanie indeksu. DROP [PUBLIC] ROLLBACK SEGMENT segment - kasowanie segmentu
0 - 1* 6) / ) * S ) / tylko administrator bazy danych. 16
Bazy danych
Robert Chwastek
DROP SEQUENCE [user.]sequence - kasowanie sekwencji. DROP [PUBLIC] SYNONYM [user.]synonym - usuwanie synonimu. Synonim - ) / 0%1* . ) / # * DROP TABLE [user.]table " - * ( -
# - ) * (
- * DROP TABLESPACE tablespace [INCLUDING CONTENTS] - usuwanie - * I ) -/ 0%1* ( przypadku podania klauzuli INCLUDING CONTENTS obszar danych zostanie * 8# N:9JN& :A$M$+ - * DROP VIEW [user.]view " * . - * 3.3.4.
Rozkaz INSERT
I - - - )
* %- / - ) -/ # - 0%1 - / - * + , INSERT INTO [user.]table [ (column [, column] ...) ] { VALUES (value [, value] ...) | query } Parametry: " # - - " - " - - ? " #/ # * ( #/ ) -/ )* 8# #/ J99 -/ # * X " +M9M:$ #/ # # # * 5 ) / AIMI 2AI 2AI UPDATE. Opis:
I N+MI$ ) O%9JM+ * # 0 - 1 #* S # # J99 0 -/ A$ J991* 8# ) +M9M:$ O%9JM+ )
# 0 1* . 17
Bazy danych
Robert Chwastek
- # 0 - # 1* 5 ) / ) - * ( # * . -/ #/
- * Y # ) * . , INSERT INTO pracownicy VALUES (50, ‘JAN’, ‘KOWALSKI’, 3); INSERT INTO ksiazki (tytul, autor, miejsce) SELECT 'Pan Tadeusz', autor_nr, miejsce_nr FROM autorzy, miejsca WHERE nazwisko = 'Mickiewicz' AND miejsce = 'lewa polka' ; 3.3.5.
Rozkaz DELETE I M9M$M ) - *
+ , DELETE [FROM] [user.]table [alias] [WHERE condition] Parametry: " )
- " - - ) / " - ) M9M$M " / ) /* ( ) / - / * S - ) - - - # $IJM - 2%9+M* Opis:
: 7
- * . , Skasowanie wszystkich wierszy w tabeli pracownicy: DELETE FROM pracownicy ; + )
numerem 2: DELETE FROM ksiazki WHERE autor = 2 ; 18
Bazy danych
3.3.6.
Robert Chwastek
Rozkaz CREATE SEQUENCE
$ - 0 1 ) ) / - * + -/ ) - *
przynajmniej uprawnienia RESOURCE w conajmniej jednej przestrzeni tabel. + , CREATE SEQUENCE [user.]sequence [INCREMENT BY n] [START WITH n] [MAXVALUE n | NOMAXVALUE] [MINVALUE n | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE n | NOCACHE] [ORDER | NOORDER] Parametry: " ) X "
-/ -/ - - ) * N:IM6M$ B " # ) - * 8# - - - " * # #/ >* ) - ) D* +$%I$ (N$' " - -/ * # # 6NO%9JM 6%ZO%9JM * J
#/ MZ$O%9* 6NO%9JM " # #/ ) / * # > #/
">D* . A6NO%9JM ) - / # * 6%ZO%9JM " # # ) / * ( # # "> >DH * Jeden element # * ( ) -/ ) - #/ * #/ ) * 8#
% : # ) do wyszukiwania konkatenacji kolumn A, B, C, kolumn A i B lub tylko kolumny A. Nie - ) : pojedynczej kolumny B lub C. 6)
# - - * ) / ) - ) #/ ) #/ * . , CREATE INDEX i_prac_imie ON pracownicy (imie) ; 26
Bazy danych
3.3.12.
Robert Chwastek
Rozkaz CREATE VIEW
I )
- - - * J
/ ) # - ) co najmniej uprawnienia SELECT lub administrator. + , CREATE VIEW [user.]view [(alias [, alias] ...)] AS query [ WITH CHECK OPTION [CONSTRAINT constraint] ] Parametry: " #
view - nazwa tworzonego widoku X " - - * 5 ) -/ +M9M:$ AIMI B ani FOR UPDATE. (N$' :'M:S A.$NA " )
# - * S (N$' :'M:S A.$NA ) -/ ) - * :A+$I%N$ " (N$' :'M:S A.$NA* Opis:
Widok jest logicznym oknem dla jednej lub kilku tabel. W
# #, widok nie przechowuje danych - jest on przeznaczony do pokazywania danych zawartych w innych tabelach. ) -/ ) +;9 )
) - ) ) ) / gdy zapytanie na którym bazuje widok zawiera: klauzule GROUP BY, CONNECT BY lub START WITH N+$N:$ - ) #
6) tóry posiada pseudokolumny - ) J.%$M ) * ( ) ,
- - # - - - )# " ) -/ )
- - ) - * 27
Bazy danych
Robert Chwastek
" ) / ) zmiany nazwy kolumny bez zmiany rzeczywistych danych zapisanych w tabeli. #* . , CREATE VIEW bibl AS SELECT ksiazki.tytul, autorzy.imie, autorzy.nazwisko, miejsca.miejsce FROM ksiazki, autorzy, miejsca WHERE ksiazki.autor = autorzy.autor_nr AND ksiazki.miejsce = miejsca.miejsce_nr WITH CHECK OPTION CONSTRAINT chkopt ; 3.3.13.
Rozkaz COMMIT
+ , COMMIT [WORK] Opis:
I :A66N$ :A66N$ (AIS a 7 bazie danych. 3.3.14.
Rozkaz ROLLBACK
+ , ROLLBACK [ WORK ] [TO [ SAVEPOINT ] savepoint ] Parametry: (AIS " - # %+N +%OM.AN$ " IA99%:S savepoint - nazwa punktu zaznaczonego podczas wykonywania aktualnej transakcji. Opis: I IA99%:S ) 0 klauzuli TO) lub poczatku transakcji (bez klauzuli TO). . , ROLLBACK ; ROLLBACK TO SAVEPOINT SP5 ; 3.3.15.
Rozkaz SAVEPOINT
+ , SAVEPOINT savepoint 28
Bazy danych
Robert Chwastek
Parametry: savepoint - nazwa punktu w aktualnej transakcji zaznaczanego przez wykonywany rozkaz Opis: I +%OM.AN$ ) IA99%:S * -/
* + - # - ) / * . , 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.
Rozkaz SET TRANSACTION
+ , SET TRANSACTION { READ ONLY } Parametry: IM% A9B " / Opis:
I ) - / 7* ) ) N+MI$ J.%$M M9M$M* I +M$ $I%+%:$NA / -* 29
Bazy danych
3.4.
Robert Chwastek
Operacje relacyjne
A - - * ( ) , " ) - 0 1 # warunki; " ) - - 0 1 " ) 0 1 # # 7 * 3.4.1.
Selekcja
Operacja ) - 0 1 #
* A ) podzbiorem poziomym.
$ %
! "
#
'()*)+)*,-'
.
&
/"
&
3
4
01)-2))(,-'
++2)2()()*+
!
'))2)2)2(*2
!
5
4
112(2()2((*
( +;9 ) +M9M:$ ('MIM* . , SELECT * FROM osoby; spowoduje wybranie wszystkich krotek (wierszy) z relacji (tabeli) ludzie. ( - C( G C+AG 0# 1 ) /, SELECT * FROM osoby ; ( -/ )* . - - / - # 0# " +$ - # " +A1 ) / AI / -, SELECT * FROM osoby ; 30
Bazy danych
Robert Chwastek
)7 # * 3.4.2.
Projekcja
. ) - # - - +M9M:$ * A ) podzbiorem pionowym.
! "
#
'()*)+)*,-'
.
$ %
&
/"
&
3
4
01)-2))(,-'
++2)2()()*+
!
'))2)2)2(*2
!
5
4
112(2()2((*
&9 : '()*)+)*,-'
&
01)-2))(,-'
4
++2)2()()*+
!
'))2)2)2(*2
4
112(2()2((*
. ) / +M9M:$, !" # $%%& ' A -/ +M9M:$* N / . - #
) /, SELECT Pesel, Nazwisko FROM osoby 31
Bazy danych
Robert Chwastek
; 3.4.3.
Produkt
. 0 7 1 - * A ) - - ) )
) * ( # ) * . 7 *
;(
;2
/
/
/
/
5 7 0 - 1 )
+M9M:$* . ) / , SELECT * FROM R1, R2; Operacja znajd 7 ) -/ ) - - #* 3.4.4.
A - # * ( - *
32
Bazy danych
Robert Chwastek
69
/ 2
/
%
(
2
!
2
(
3 % # 7 #"78 : #
# 7 $ % 7
3.8.5.
$
# ) - ) ) ) * %- / - )# ) / )#/ * . ,
/
% %
))2
)2)
))(
)()
))*
)2)
% # $ % % %
/ %
)2)
.
-
)()
&" *
)*)
! 21
3.8.6.
#
I - Z B 5 - I ) Z Y Z = R i podzbiór Y jest nietrywialnie
# ) Z* Dana relacja R jest w czwartej postaci normalnej wtedy i tylko wtedy, gdy jest w
# )#/ - B Z - )#/ - Z* `
)/ ) - ^. @ # )# funkcjonalnej jest w trzeciej postaci normalnej, ale nie jest w czwartej postaci normalnej, ) - ^ @ ^( @ ) - ^ @ * ) )#/ atrybutami. 8 # )#/ * + ) # )#/ ) / - / )# * 47
Bazy danych
Robert Chwastek
( ^. @ ) / , ^@ ^( @ - / # )#/ ,
$ %
6%"
/.
6%"
.
$%
3%
$%
3
$%
/"
& $ %
&
6%"
3
%
3.8.7.
I wtedy i tylko wtedy, )# KI\I1, ..., Rm] )#/ )# - klucza. ) # ) - - - ) -
/ 0
-/ 1 -
) / - - ) * ( ) / - ) * 3.8.8.
Podsumowanie
. ) # # - * 48
Bazy danych
Robert Chwastek
. - / - * J 7
- - * 6 - / )#/ - ) / - #/ *
49
Bazy danych
4.
Robert Chwastek
"
( ) *
4.1.
Operatory
+ +;9 * 4.1.1.
Operatory arytmetyczne
A nego. Jednak -/ ) %$M* + - , Operator ()
*, /
Opis 5 #/ 7* (
* Operatory jednoargumentowe zachowania i zmiany znaku. 6)
+, -
Dodawanie, odejmowanie
+, -
4.1.2.
SELECT (X+Y)/(Y+Z) ...
... WHERE NR = -1 ... WHERE -PLACA < 0 SELECT 2*X+1 ... WHERE X > Y/2 SELECT 2*X+1 ... WHERE X > Y-Z
Operatory znakowe
8 nkatenacji. I - * ) / ) :'%I -/ ) ) < >= 1000 ... WHERE PLACA < 1000 ... WHERE PLACA >= 1000 ... WHERE PLACA , = ALL
# - ALL ((14900, 300), (3000, 0)) * 6 -/ jednym z operatorów: =, !=, >, *DV1 0 >H" 1 0 >H1 7 7 7 - ) godzina minuta
Inne funkcje
Przeznaczenie GREATEST(expr [, 5
#* ( ) expr] ...) konwertowane do typu ) wykonaniem porównania. 5 LEAST(expr [,
#* ( ) expr] ...) konwertowane do typu ) wykonaniem porównania. NVL (expr1, expr2) 8# [> J99 zwraca expr2, w przeciwnym wypadku zwraca expr1.
63
SELECT GREATEST (‘Harry’, ‘Harriot’, ‘Harold’) ”GREATEST” FROM DUAL
SELECT LEAST (‘Harry’, ‘Harriot’, ‘Harold’) ”LEAST” FROM DUAL
SELECT ENAME NVL(TO_CHAR(COMM), ‘NOT APPLICABLE’) ”COMMISION” FROM EMP WHERE DEPTNO = 30
Bazy danych
UID
Robert Chwastek
Zwraca unikalny identyfikator ) * 5 )
USER
5.7.
SELECT USER, UID FROM DUAL SELECT USER, UID FROM DUAL
Formaty zapisu danych
2 ) , - # 4 ) # * 2 ) $AW:'%I $AW%$M* 5.7.1.
Formaty numeryczne
2 ) $AW:'%I # #/ * J) - * 8 #/ )
#/ CKG* .) - ) / numerycznego: Element 9 0 $ B MI PR
9999 0999 $9999 B9999 9999MI 9999PR
, (przecinek) . (kropka) V
9,999 99.99 999V99
E
9.999EEEE
DATE
DATE
5.7.2.
Opis 9- CFG # #/ # . . #/ CTG (# 0 1 (# C"C # (# #/ CaG ‘>’ (# (# 6) #/ >Dn - po ‘V’ (# - 0 / M1 Dla dat przechowywanych w postaci numerycznej. (# C66QQBBG
Formaty dat
2 ) $AW:'%I # * 6 -/ ) ) $AW%$M # * Format standardowy, to ‘DD-MON-YY’. 64
Bazy danych
Robert Chwastek
Elementy formatu dat przedstawia tabela: Element SCC lub CC YYYY lub SYYYY YYY, YY lub Y Y,YYY SYEAR lub YEAR BC lub AD B.C. lub A.D Q MM MONTH MON WW W DDD DD D DAY DY AM lub PM A.M. lub P.M. HH lub HH12 HH24 MI SS SSSS / ., ”...”
Opis ( 4 C+G C"C : C+G ‘-‘ Ostatnie 3, 2 lub 1 cyfra roku Rok z przecinkiem na podanej pozycji Rok przeliterowany. ‘S’ powoduje poprzedzenie daty przed C"C 5 :Q% 0 Q 1 Znak BC/AD z kropkami S 0> ">"="=1 0 7 V 1 7 0>"!HH1 7 0>"!>1 7 0>"V1 F Trzyliterowy skrót nazwy dnia ( 3 ( 3 Godzina (1-12) Godzina (1-24) Minuta (0-59) Sekunda (0-59) + 0D"EH!FF1 Znaki przestankowe umieszczane w wyniku :
# ) )/, 26 " ^2 6@ Q liczb zerami; $' " # - * LTH dla liczby 4; +. " ) +.$' - $'+. " +. $'*
65
Bazy danych
6. 6.1.
Robert Chwastek
Programowanie proceduralne - PL/SQL Wprowadzenie
I +;9
- # # -
* A +;9 .9Q+;9* PL/SQL pozwala wykorz / +;9* ( 0%9$MI :IM%$M IM%6M1 niektóre rozkazy kontroli danych jak CONNECT, GRANT i REVOKE. S .9Q+;9 +;9 z 7 * 6) +;9 +;9 0 * 1 * S ) .9Q+;9 7 # * PL/SQL pozwala na definiowa * 5 )
7 - 7 3 * 8 -/ )* S ) * $ +;9G* 5 *, premia NUMBER(7, 2); ( #/ ^,R@ *, podatek := cena * stopa; ) #/ # ) +M9M:$ - 2M$:'
# , SELECT placa INTO placa_aktualna FROM pracownicy WHERE nazwisko = ‘Nowak’ ; .9Q+;9 pola. N ) #/ * - ) :A+$%$ # * , stopa_premii CONSTANT NUMBER(3, 2) := 0.10; ( - - * 8 0 1* 6) ) c$B.M * * 5 / - 0 ) )/ ) - 1, tytul books.tytul%TYPE 6) )
- * ( ) )/ cIA($B.M* 66
Bazy danych
Robert Chwastek
( .9Q+;9 ) / 7, R UR a b bR aR* 6 / ) , 0 1*
6.2.
Struktura bloku S .9Q+;9 - * ,
DECLARE deklaracje BEGIN rozkazy wykonywalne EXCEPTION - END; S ) - ) / - * - -/ ))* ( .9Q+;9 - 0 * - 1* ) - * 6) / ) - * A- - ) * 5 - # -
/ * - - * N - - - - 0- 1* N - / - ) - 7
- * - - ) ) - * - - )) ) - - *
6.3.
Procedury i funkcje
PL/SQL w wersji 2.0 pozwala na definiowanie funkcji i procedur. + , PROCEDURE name [ (parameter [, parameter] ... ) ] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name]; + , var_name [IN | OUT | IN OUT] datatype [{ := | DEFAULT } value] 67
Bazy danych
Robert Chwastek
A # ) / 7 *
) * N$0=1* . # " * .IA:MJIM 7 - # * * . - - * : N+ 7 M 0 )
/ 1* : #/ 0 N+ M&N1 - * ( ) M:9%IM* : #/
0 M&N MZ:M.$NA - M1 .9Q+;9* ( # / * - , PROCEDURE zwieksz (prac_id INTEGER, kwota REAL) IS placa_aktualna REAL; BEGIN UPDATE pracownicy SET placa_podstawowa = placa_podstawowa + kwota WHERE prac_id = id_pracownika; END zwieksz; 5 - #* ) IM$JI* FUNCTION name [ (argument [, argument] ... ) ] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name]; ( / IM$JI* I IM$JI 7
* ( / IM$JI
-/ )* ( ) / * 8# 7 IM$JI .9Q+;9 * ( IM$JI ) / ) ) *
6.4.
Kursory
W celu wykonania rozkazu SQL system tworzy pewien obszar roboczy nazywany * (
* .9Q+;9 / 7 / * .9Q+;9 ) dwóch typów kursorów: 68
Bazy danych
Robert Chwastek
" ) ) -
/ 7
/ 0 # 2AI14 " .9Q+;9
operacji. 8# )
- ) * S # - .9Q+;9 * + , DECLARE 6 ( %7 %" FROM pracownicy ( 89:::' ... BEGIN ... + *
) / A.M sposób: OPEN prac_kursor; ( ) 2M$:'* S ) 2M$:' * I 2M$:' ) -/ ) -, FETCH prac_kursor INTO prac_nazw, prac_wydz; . 7 - * :9A+M *, CLOSE prac_kursor; S ) - # * - CcG -# * 5 - , cA$2AJ " $IJM # 2M$:' 7 powodu braku wierszy c2AJ " $IJM # 2M$:' 7 %ROWCOUNT - liczba wierszy w kursorze (po otwarciu kursora) cN+A.M " $IJM #
- ) - rsora: LOOP FETCH prac_kursor INTO prac_nazw, prac_wydz; IF prac_kursor%ROWCOUNT > 10 THEN ;; , 5! 5 ' J) , prac_rek.nazwisko := UPPER(prac_rek.nazwisko); . # ) e jest na dwa sposoby: # 0 - / - - 4 70
Bazy danych
Robert Chwastek
# +M9M:$ *** N$A - 2M$:' *** N$A J) , DECLARE prac_rek1 pracownicy%ROWTYPE; prac_rek2 pracownicy%ROWTYPE; BEGIN SELECT nazwisko, imie, wydzial, placa_podstawowa INTO prac_rek1 FROM pracownicy WHERE wydzial = 30; prac_rek2 := prac_rek1; ... END; .9Q+;9 2AI * + , DECLARE CURSOR prac_kursor IS SELECT nazwisko, imie, wydzial, placa_podstawowa FROM pracownicy; BEGIN FOR pracownik IN prac_kursor LOOP suma := suma + pracownik.placa_podstawowa; ... END LOOP; Niejawnie deklarowanym rekordem jest tu zmienna o nazwie pracownik. Zmienna ta jest - nazwa_kursora%ROWTYPE.
6.6. 6.6.1.
$ % Informacje podstawowe
) * (# ) / , - - # * )
) - ) / - * ( - ^. @ 7 * ( 0:PP 8 ? .9Q+;91 # - - * 8 - - /
- / * ( - * ( 0
1 ) * .
-/, ^A @ ^? - @* ( .9Q+;9 ) ) / # - .9Q+;9* 71
Bazy danych
Robert Chwastek
. ) ^. ) @ -
/ ) - * & -
0 1 *
- 0 - 1* (
* ( ) -/ I%N+M* ( - 7 -
- -
* 8# - * . ^ @ 05MIAWNONM1 - - 3 :Q5, DECLARE cz_wsk NUMBER(3,1); BEGIN ... SELECT cena / zysk FROM akcje ?@ ';;0%5 % 2 ,) -- ZERO_DIVIDE INSERT INTO informacje (nazwa, c_z) VALUES (‘ABC’, cz_wsk); COMMIT EXCEPTION WHEN ZERO_DIVIDE THEN INSERT INTO informacje (nazwa, c_z) VALUES (‘ABC’, NULL); COMMIT; ... WHEN OTHERS THEN ROLLBACK; END; . * 5
- - - ) * ( # -/ # ) , BEGIN SELECT ... ;;%%! &- A&
BC SELECT ... ;;%%! &- A&
BC SELECT ... ;;%%! &- A&
BC END; 72
Bazy danych
Robert Chwastek
. - - - #/ ) * $ - ) #
/
, BEGIN SELECT ... SELECT ... SELECT ... EXCEPTION WHEN NO_DATA_FOUND THEN ===D;%&* B&- 3A&
BC END; ( #/ * 5 ) ) - -)* 8# - - ) - - * 6.6.2.
* '
$ .9Q+;9 - * ( # * (- ), :JI+AIW%9IM%BWA.M " -
) otwartego; J.WO%9WAWNMZ " - N+MI$ - J.%$M -
w indeksie zadeklarowanym jako UNIQUE; NO%9NW:JI+AI " - kursora (np. nie otwartego); NO%9NWJ6MI " - numerycznego z tekstu, który nie reprezentuje liczby; AW%$%W2AJ " +M9M:$ / ) 0* +M9M:$ *** N$A14 +$AI%&MWMIIAI " - # 4 $AAW6%BWIA(+ " +M9M:$ ) 0* +M9M:$ *** N$A14 O%9JMWMIIAI " # lub pola; ZERO_DIVIDE - próba dzielenia przez zero; 6.6.3.
(
%- -)/ 0^ /@1 -
* # - .9Q+;9 MZ:M.$NA 7 - * S ) 73
Bazy danych
Robert Chwastek
- ('M $'M # - * 2 - 7 - ) - * A A$'MI+ 0 1 / -
0 * #1* A #/ MZ:M.$NA , EXCEPTION WHEN ... THEN ;;%&* ,) WHEN ... THEN ;;%&* ,) WHEN ... THEN ;;%&* ,) WHEN OTHERS THEN ;;%&* (% % B ,)3 END; 8# - ) ) / ('M AI, WHEN over_limit OR under_limit OR VALUE_ERROR THEN ... ) )/ A$'MI+ #* + A$'MI+
/ * ) / ) ) ) -/ - - * ( -
* - - * 6.6.4.
* ' %
8 .9Q+;9 )
*( -/ # I%N+M* ( - ) )
MZ:M.$NA* , DECLARE overflow EXCEPTION; result NUMBER(5); BEGIN ... END; ) / ) .9Q+;9 - 0 7
1 #
* ( ) ) # * ( ) -/ ) ) rozkazach SQL. 74
Bazy danych
Robert Chwastek
) - * 6) / ) - * Jak podano w# * ( ) -/
* +) I%N+M* J) , DECLARE brak_czesci EXCEPTION; liczba_czesci NUMBER(4); BEGIN ... IF liczba_czesci < 1 THEN RAISE brak_czesci; END IF; ... EXCEPTION WHEN brak_czesci THEN ;;%&* &- A& -1 C END; 6) ) 0 I%N+M1 , RAISE INVALID_NUMBER; : #/ - - * . ) -/
- )) - - * ( ) ) I%N+M -
* ) / ) - - - #* .- # -
*
6.7. 6.7.1.
!& ! Rozkaz OPEN
Rozkaz OPEN wykonuje zapytanie skojarzone z jawnie zadeklarowanym kursorem - - - * S ) * + , OPEN cursor_name [(input_parameter [, input_parameter] ... )] ; Parametry: cursor_name - nazwa kursora uprzednio zadeklarowanego, który nie jest aktualnie otwarty. W " ) .9Q+;9 * 8
# ) 0 # klauzuli WHERE). 75
Bazy danych
Robert Chwastek
. A.M -/ ) #/ * N #/ A.M -/ # * . A.M ) -/ celu pobrania ich z kursora. . 0 A.M1 0 1 ) - / -, ( #/ ) A.M * ( -/ # ) ) -/ CRbG* + - ) - ) # , DECLARE CURSOR prac_kur(nazw CHAR, wydz NUMBER) IS ... BEGIN OPEN prac_kur(‘Kowalski’, 10); ... OPEN prac_kur(wydz => 15, nazw => ‘Nowak’); END; 6) ) / # )
/ )
/ * 6.7.2.
Rozkaz CLOSE
I :9A+M )
* S ) / * I :9A+M - - * + , CLOSE cursor_name ; Parametry: cursor_name - nazwa aktualnie otwartego kursora. 6.7.3.
Rozkaz FETCH
I 2M$:' - 0 +M9M:$ 1* A * 5 # kolumn w aktualnym wierszu. + , FETCH cursor_name INTO { record_name | variable_name [, variable_name] ... } ; Parametry: 76
Bazy danych
Robert Chwastek
cursor_name - nazwa aktualnie otwartego kursora. ? - W " * ( # -/ * )
/ * ( -/ - * W " - 0 ) - %ROWTYPE). . , ... OPEN prac_kursor; ... LOOP FETCH prac_kursor INTO prac_rek; EXIT WHEN prac_kursor%NOTFOUND; ... END LOOP; 6.7.4.
Rozkaz SELECT ... INTO
Rozkaz SELECT ... INTO odczytuje informacje z bazy danych i zapisuje je do * ( .9Q+;9 0 +;91 +M9M:$ N$A* %- - +M9M:$
0 ) / / 1* + +M9M:$ N$A, SELECT select_list_item [, select_list_item] ... INTO { record_name | variable_name [, variable_name] ... } rest_of_select_statement ; Parametry: Zobacz opis rozkazu FETCH. . , SELECT nazwisko, placa*12 INTO pnazw, plac_sum FROM pracownicy WHERE pracownik_nr = 12345; 6.7.5.
Rozkaz IF
Rozkaz IF pozwala na warunkowe wykonywanie rozkazów. + , IF plsql_condition THEN seq_of_statements [ELSEIF plsql_condition THEN seq_of_statements] ... [ELSE seq_of_statements] END IF; Parametry: X W " 0 ) - # 1 77
Bazy danych
Robert Chwastek
XWW " -/ 0-3 1 Opis: I N2 )/ -
0 - 1* 8# $'M ) M9+MN2 M9+M - M N2 - ) M N2* ( M9+MN2* 8# $'M N2 7* 8#
) M9+MN2* 8# ) M9+M 0# 1* ( - J99 * . , IF liczba_czesci < 20 THEN ilosc_zamawianych := 50; ELSEIF liczba_czesci < 30 THEN ilosc_zamawianych := 20; ELSE ilosc_zamawianych := 5; END IF; INSERT INTO zamowienia VALUES(typ_czesci, ilosc_zamawianych); 6.7.6.
Rozkaz LOOP
I 9AA. )
.9*Q+;9* , ('N9M 2AI 2AI + , [] [ { WHILE plsql_condition } | { FOR {numeric_loop_param | cursor_loop_param } } ] LOOP seq_of_statements END LOOP [ label_name ] ; + W W , index IN [REVERSE] integer_expr .. integer_expr + W W , record_name IN { cursor_name [(parameter [, parameter] ...)] | ( select_statement ) }
78
Bazy danych
Robert Chwastek
Parametry: - W " * 6) ) MZN$ # /
#* . )
# , label_name.index XWW " - X W " .9*Q+;9* ( ('N9M - ) * A
#/ $IJM* ( * [ " 2AI* # * W[ " ) - * ( )
- # 2AI* IMOMI+M " / [ 0 1* W " * ( # 2AI
* "
0# parametrami). W " ) * .9*Q+;9
- 0 1* Opis:
N - ) * $ 7 MZN$* . , LOOP ... IF (x > 10) THEN EXIT loop1; ... END LOOP loop1; . ('N9M /
* ( - ) * ( ) / * . , WHILE x < 10 LOOP ... x := x - y; ... END LOOP; . 2AI / # #/ *
) * N ) -/ - * . , FOR i IN 1 .. n LOOP silnia := silnia * n; 79
Bazy danych
Robert Chwastek
END LOOP; . 2AI ) / ) 0 - 1* : )
* . , DECLARE CURSOR prac_kursor IS select * FROM pracownicy; prac_rek prac_kursor%ROWTYPE; BEGIN ... FOR prac_rek IN prac_kursor LOOP suma := suma + prac_rek.placa_podstawowa; END LOOP; ... END; 6.7.7.
Rozkaz EXIT
I MZN$ ) # * I , -
* + , EXIT [label_name] [WHEN plsql_condition] ; Parametry: - W " / #* 8#
MZN$ # - )) wykonywanej. X W " ) MZN$ * 6 -/
.9*Q+;9* (# -
#/ $IJM* 6.7.8.
Rozkaz GOTO
I &A$A ) hmiastowego przekazania sterowania od rozkazu * + , > + &A$A, GOTO label_name ; Opis: I &A$A ) - - - - * 5 -
) - - *
) ) * 8# &A$A ) 2AI
automatycznie.
80
Bazy danych
7.
Robert Chwastek
Literatura
1. Wojciech Cellary, Zbyszko Królikowski “Wprowadzenie do projektowania baz danych dBase III”, Wydawnictwa Naukowo -Techniczne, Warszawa 1988 2. ORACLE SQL Language Reference Manual 3. ORACLE PL/SQL User’s Guide and Reference 4. ORACLE SQL*Plus User’s Guide and Reference
81
