Bazy danych 1
1 Indeksy, Rowid, Rownum
 Index [5] przechowuje wartość indeksowanej kolumny lub kolumn oraz fizyczny identyfikator wiersza
RowID zawierającego indeksowaną wartość, z wyjątkiem tabel IOT (tabel indeksowych), które wykorzystują
jako logiczny identyfikator wiersza RowID klucz główny. Gdy już zostanie odnaleziona odpowiednia, pasująca
pozycja w indeksie, identyfikator wiersza RowID zapisany w indeksie wskaże dokładną lokalizacje wiersza
w tabeli (...)
Typy indeksów [5]:
" indeksy unikatowe (ang. unique index) - bazuje na B-drzewie, służy do przyspieszenia wyszukiwania
i zapewnienia unikalności,
" indeks nieunikatowy (ang. non-unique index) - bazuje na B-drzewie, służy do przyspieszenia wyszuki-
wania, domyślny typ indeksu,
" indeksy z odwróconym kluczem (ang. reverse key index) - specjalny typ indeksu, w którym wszystkie
bajty w klucz są odwrócone,
" indeksy funkcyjne (ang. function-based index) - bazuje na w indeksie przechowywana jest transformacja
kolumny lub kolumn, a nie same kolumny,
" indeks bitmapowy (ang. bitmap index) - przechowuje on dla każdej możliwej wartości w indeksowanej
kolumnie jeden łańcuch bitów, długość tego łańcucha bitów jest taka sama jak liczba wierszy w indek-
sowanej tabeli.
 Rowid [6] to niepowtarzalny identyfikator generowany przez system, tworzony dla każdego rekordu w
bazie danych.
Rownum [13] - zwraca numer oznaczający kolejność w jakiej wiersz został pobrany z tabeli. Przykład:
SELECT id, nazwisko, imie, rownum
FROM student
WHERE rownum < 5;
Tworzenie indeksu:
CREATE [ UNIQUE | BITMAP ] INDEX [ schema. ] index
ON { cluster_index_clause
| table_index_clause
| bitmap_join_index_clause
}
[ UNUSABLE ] ;
Uproszczona składnia tworzenia indeksu:
CREATE INDEX nazwa_indeksu ON tabela (kolumny);
np. CREATE INDEX student_index ON student (nazwisko, imie);
Usuwanie indeksu:
DROP INDEX [ schema. ] index [ FORCE ] ;
Uproszczona składnia usuwania indeksu:
DROP INDEX nazwa_indeksu;
np. DROP INDEX student_index;
1
2 Zapytania hierarchiczne
Jeżeli tabela zawiera hierarchiczne dane [15] to możliwe jest wyświetlenie ich zgodnie z tym porządkiem
używając frazy hierarchical query clause.
{ CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ]
| START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]...
}
" START WITH  określa wiersz (wiersze) będące korzeniem hierarchii,
" CONNECT BY  określa związek pomiędzy wierszem nadrzędnym i podrzędnym w hierarchii,
 NOCYCLE  umożliwia zwracanie wyników zapytań, w których występują pętle,
 PRIOR  operator służący do określania wiersza nadrzędnego, musi wystąpić co najmniej raz w
sekcji warunku (ang. condition),
" CONNECT BY ROOT  operator rozszerzający funkcjonalność warunku CONNECT BY [PRIOR] po-
przez zwracanie nie tylko bezpośredniego wiersza nadrzędnego (rodzica), ale wszystkie wiersze nadrzędne
(przodków) w hierarchii,
" SYS CONNECT BY PATH  zwraca ścieżkę od korzenia dla każdego wiersza zwróconego przez warunek
CONNECT BY,
" ORDER SIBLINGS BY  służy do ustalania porządku dla rodzeństwa.
Pseudokolumny zwiÄ…zane z zapytaniami hierarchicznymi:
" LEVEL  zwraca poziom wiersza w drzewie (1 dla korzenia, itd.),
" CONNECT BY ISCYCLE  zwraca 1, jeżeli aktualny wiersz posiada wiersz podrzędny (potomka),
który jest także jego wierszem nadrzędnym (przodkiem),
" CONNECT BY ISLEAF  zwraca 1, jeżeli aktualny wiersz jest liściem drzewa zdefiniowanym poprzez
warunek CONNECT BY.
Przykład:
CREATE TABLE katalog(
id_katalog NUMBER(2) CONSTRAINT katalog_pk PRIMARY KEY,
nazwa VARCHAR2(30),
nadrzedny NUMBER(6)
CONSTRAINT katalog_nadrzedny_fk REFERENCES katalog (id_katalog)
);
SELECT LEVEL, CONNECT_BY_ISLEAF, LTRIM(SYS_CONNECT_BY_PATH(nazwa,  \ ), \ ) "Pelna_sciezka"
FROM katalog
START WITH nazwa =  C:
CONNECT BY NOCYCLE PRIOR id_katalog = nadrzedny;
3 Duże obiekty (LOB od ang. Large OBject)
Wewnętrzne typy LOB:
" CLOB  służy do przechowywania danych znakowych zapisanych za pomocą zastawu znaków bazy
danych określony przez parametr NLS CHARACTERSET,
" NCLOB  służy do przechowywania danych znakowych zapisanych za pomocą zestawu znaków naro-
dowych bazy danych określony przez parametr NLS NCHAR CHARACTERSET,
" BLOB  dane binarne o nieokreślonej strukturze, które nie są interpretowane przez bazę danych.
Zewnętrzne typy LOB:
2
" BFILE  umożliwia odczytywanie dużych plików binarnych składowanych poza bazą danych, w bazie
danych przechowywany jest tylko wskaz
nik do pliku zewnętrznego.
Pakiet DMBS LOB:
APPEND FILECLOSEALL FREETEMPORARY LOADCLOBFROMFILE
CLOSE FILEEXISTS GETCHUNKSIZE LOADFROMFILE
COMPARE FILEGETNAME GETLENGTH OPEN
CONVERTTOBLOB FILEISOPEN GETOPTIONS READ
CONVERTTOCLOB FILEOPEN GET_STORAGE_LIMIT SETOPTIONS
COPY FRAGMENT_DELETE INSTR SUBSTR
CREATETEMPORARY FRAGMENT_INSERT ISOPEN TRIM
ERASE FRAGMENT_MOVE ISTEMPORARY WRITE
FILECLOSE FRAGMENT_REPLACE LOADBLOBFROMFILE WRITEAPPEND
Przykład:
CREATE TABLE test_clob(
id NUMBER(6) PRIMARY KEY,
text CLOB);
INSERT INTO test_clob VALUES(1, EMPTY_CLOB());
DECLARE
i CONSTANT PLS_INTEGER := 1024;
c CLOB;
t VARCHAR2(1024);
r INTEGER;
BEGIN
DBMS_RANDOM.INITIALIZE(1);
DBMS_OUTPUT.ENABLE;
SELECT text
INTO c
FROM test_clob
WHERE id = 1;
FOR j IN 1..i LOOP
t := DBMS_RANDOM.STRING ( x , i) ;
IF(j = 1)THEN
DBMS_OUTPUT.PUT_LINE( CLOB.[1-1024] =  || t); END IF;
DBMS_LOB.WRITE(c, i, 1 + (j - 1) * i, t);
END LOOP;
DBMS_OUTPUT.PUT_LINE( CLOB.LENGTH =  || DBMS_LOB.GETLENGTH(c));
r := 1024;
DBMS_LOB.READ(c, r, 1, t);
DBMS_OUTPUT.PUT_LINE( CLOB.[1-1024] =  || t);
END;
/
4 XML
 Extensible Markup Language (XML) [13] jest językiem znaczników o ogólnym zastosowaniu. UML umoż-
liwia wymianę ustrukturyzowanych danych w internecie i może być używany do kodowania danych i innych
dokumentów. Zalety XML to między innymi to, że:
" może być odczytywany przez ludzi, jak i przez komputery, a oprócz tego jest składowany jako zwykły
tekst,
" jest niezależny od platformy,
" obsługuje Unicode, co oznacza, że może zawierać informacje napisane w dowolnym ludzkim języku,
" wykorzystuje samoopisujący się format, zawierający strukturę dokumentu, nazwy elementów i wartości
elementów.
3
XMLType [8] - typ wbudowany używany do przechowywania i tworzenia zapytań danych XML owych.
Funkcje zwiÄ…zane z XML[13]:
" XMLELEMENT() -  służy do generowania elementów XML z danych relacyjnych. Należy przesłać
nazwę elementu oraz kolumnę, którą chcemy pobrać do XMLELEMENT().
" XMLATTRIBUTES() -  jest używana w połączeniu z funkcja XMLELEMENT() w celu określenia
atrybutów elementów XML pobieranych przez XMLELEMENT(). ,
" XMLFOREST() -  służy do generowania  drzewa elementów XML.
" XMLAGG() -  generuje las elementów XML z kolekcji elementów XML. Jest zwykle używana do grupo-
wania XML w jednorodną listę elementów pod jednym rodzicem lub do pobierania danych z kolekcji.
" XMLCOLATVAL() -  służy do utworzenia fragmentu XML i póz
niejszego rozszerzenia powstałego
XML.
" XMLCONCAT() -  konkatenuje serię elementów każdego wiesza.
" XMLPARSE() -  służy do parsowania i generowania XML z obliczonego wyniku wyrażenia.
" XMLPI() -  służy do generowania instrukcji przetwarzania XML.
" XMLCOMMENT() -  generuje komentarz XML,
" XMLSEQUENCE() -  generuje obiekt XMLSequenceType będący tabelą o zmiennej długości zawiera-
jÄ…cÄ… elementy XMLType.
" XMLSERIALIZE() - generuje napis lub LOB (duży obiekt) stanowiący reprezentację danych XML na
podstawie obliczonego wyniku wyrażenia.
" XMLQUERY() -  służy do konstruowania XML lub odpytywania XML.
Przykład:
SELECT XMLTYPE(  ||
XMLELEMENT("students",
XMLAGG(
XMLELEMENT("student",
XMLELEMENT("id", id),
XMLELEMENT("nazwisko", nazwisko),
XMLELEMENT("imie",imie)
)
)
)
) FROM student;
Wynik działania:



1
Kowalski
Adam


2
Nowak
Jan

...

4
5 Funkcje i procedury
Ciało funkcji lub procedury może zostać napisane w języku PL/SQL, C lub Java.
Deklaracja w języku C:
C { [ NAME name ] LIBRARY lib_name | LIBRARY lib_name [ NAME name ] }
[ AGENT IN ( argument[, argument ]... ) ]
[ WITH CONTEXT ]
[ PARAMETERS ( parameter[, parameter ]... ) ]
Deklaracja w języku Java:
JAVA NAME string
Tworzenie funkcji posiadającej ciało napisane w języku C:
1. Konfiguracja usług Oracle Net Services do obsługi procedur zewnętrznych [5]
Przykładowa zawartość pliku listener.ora:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\\...\\product\\11.2.0\\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\\...\\liborcl.dll")
)
)
ADR_BASE_LISTENER = C:\...
Przykładowa zawartość pliku tnsnames.ora:
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
5
2. Definiowanie biblioteki współdzielonej języka C [5]
3. Definicja biblioteki języka PL/SQL
CREATE OR REPLACE LIBRARY win_kernel
AS
 C:\WINDOWS\system32\kernel32.dll ;
/
4. Nakładka języka PL/SQL na bibliotekę
CREATE OR REPLACE PACKAGE process_util
AS
FUNCTION get_current_process_id
RETURN PLS_INTEGER;
END process_util;
/
CREATE OR REPLACE PACKAGE BODY process_util
AS
FUNCTION get_current_process_id
RETURN PLS_INTEGER
IS EXTERNAL
LIBRARY win_kernel
NAME "GetCurrentProcessId"
LANGUAGE C
CALLING STANDARD PASCAL;
END process_util;
/
5. Przykład użycia
SET SERVEROUTPUT ON
DECLARE
process_id PLS_INTEGER;
BEGIN
process_id := process_util.get_current_process_id;
DBMS_OUTPUT.PUT_LINE( get_current_process_id =  || process_id);
END;
/
 Oracle [5] bezpośrednio obsługuje język Java jako część bazy danych. Biblioteki tego języka nie używają
agenta extproc, ponieważ wchodzą w skład bazy danych Oracle.
Tworzenie funkcji posiadającej ciało napisane w języku Java:
1. Utworzenie klasy:
public class MyClass {
public static String printHelloWorld() {
return "HelloWorld";
}
}
2. Kompilacja
javac *.java
3. Wczytanie klasy do bazy danych:
loadjava -r -f -o -user NAZWA_UŻYTKOWNIKA MyClass.class
6
4. Definiowanie nakładki języka PL/SQL na bibliotekę języka Java, która publikuje klasę języka
CREATE OR REPLACE FUNCTION printHelloWorld
RETURN VARCHAR2
IS LANGUAGE JAVA
NAME  MyClass.printHelloWorld() return String ;
/
5. Wywołanie:
SELECT printHelloWorld FROM DUAL;
6 Przykładowa treść laboratorium
Na podany temat każda grupa (2 osobowa) powinna przygotować klienta w postaci aplikacji umożliwiającej
pełną obsługę bazy danych oraz zaprezentowanie wszystkich instrukcji stworzonych w trakcie lab. 11, 12, 13
(SELECT, funkcji, procedur itd.,).
Utworzone skrypty powinny zapewnić możliwość wykonania wszystkich czynności, które wykonuje się
podczas normalnej działalności firmy lub organizacji. Dla sklepu są to np.
" dodanie pracownika, usunięcie pracownika, modyfikacja danych pracownika, wyświetlenie listy pracow-
ników, . . . ,
" dodanie towaru, usunięcie towaru, modyfikacja danych towaru, wyświetlenie listy towarów, . . . ,
" wystawienie faktury o dowolnej licznie pozycji, wystawienie paragonu, ..,
" . . . .
Klient powinien zostać stworzony w oparciu o podany przykład lub napisany w jednym z wybranych
języków (C, C++, Java, PHP, PSP, itp.).
Całość (lab. 11, 12, 13 i 14) powinna być oddana na podpisanej płycie CD.
Ostatecznym i nieprzekraczalnym terminem oddania całości jest termin ostatniego laboratorium.
Literatura
[1] Thomas Connolly, Carolyn Begg,  Systemy baz danych , Wydawnictwo RM, 2004
[2] Jeffrey D. Ullman, Jennifer Widom,  Podstawowy wykład z systemów baz danych , WNT, 2001
[3] Jeffrey D. Ullman, Jennifer Widom,  Systemy baz danych Pełny wykład , WNT, 2006
[4] Kevin Loney, Bob Bryla,  Oracle Database 10g Podręcznik administratora baz danych , Helion, 2008
[5] Scott Urman, Ron Hardman, Michael McLaughlin,  Oracle Database 10g Programowanie w języku
PL/SQL , Helion, 2008
[6] Ramez Elmasri, Shamkant B. Navathe,  Wprowadzenie do systemów baz danych , Helion, 2005
[7] Jose A. Ramalho,  Oracle 8i , Mikom, 2001
[8]  Oracle®Database SQL Language Reference 11g Release 1 (11.1) B28286-02 (b28286.pdf)
[9]  Oracle®Database SQL Language Quick Reference 11g Release 1 (11.1) B28285-02 (b28285.pdf)
[10] Joe Celko,  SQL Zaawansowane techniki programowania , PWN, 2008
[11] Jason Price,  Oracle Database 11g i SQL Programowanie , Helion, 2009
[12] Oracle®Database PL/SQL Language Reference 11g Release 1 (11.1) B28370-02 (B28370.pdf)
[13] Jason Price, Oracle Database 11g i SQL Programowanie, Helion, 2009
[14]  Oracle Database 11g. Przewodnik dla poczÄ…tkujÄ…cych , I. Abramson, M. Abbey, M. Corey, M. Malcher,
Helion, 2010
[15]  Oracle®Database SQL Reference 11g Release 2 (11.2) , E26088-03
7