sokspyr

Laboratorium IOR – SZBD Oracle
Robert Sokołowski, Marcin Spyra
Informatyka SSM 08.12.2016


3. Rozszerzenia obiektowe Oracle

3.1. Uruchomić narzędzie SQLDeveloper:

3.2. Połączyć się z bazą orcl jako użytkownik sys:

3.3. Uruchomić wskazany przez prowadzącego skrypt user.sql, tworzący nowego użytkownika.

3.4. Połączyć się z bazą orcl jako użytkownik ora1. W tym celu utworzyć nowe połączenie:

3.5. Uruchomić wskazany przez prowadzącego skrypt PRAC_ZESP_TEM_WYPL.sql, tworzący nowe tabele.

3.6. Używając polecenia: CREATE OR REPLACE TYPE … AS OBJECT (…) …;
zdefiniować typ obiektowy o nazwie book_type, reprezentujący książkę, który może być dziedziczony przez inne typy (opcja NOT FINAL). W ramach typu

zdefiniować 3 atrybuty:
bookno NUMBER,
title VARCHAR2(30),
genre VARCHAR2(20)
oraz 3 funkcje o nazwach: get_bookno, show_super i show.

ROZWIĄZANIE:

CREATE OR REPLACE TYPE book_type AS OBJECT(
bookno NUMBER,
title VARCHAR2(30),
genre VARCHAR2(20),
MEMBER FUNCTION get_bookno RETURN NUMBER,
STATIC FUNCTION show_super(book_obj in book_type) RETURN VARCHAR2,
MEMBER FUNCTION show RETURN VARCHAR2) NOT FINAL;


3.7. Używając polecenia: CREATE OR REPLACE TYPE BODY … AS … END;)
zdefiniować implementacje zadeklarowanych w poprzednim punkcie metod.

ROZWIĄZANIE:

CREATE OR REPLACE TYPE BODY book_type AS
  MEMBER FUNCTION get_bookno RETURN NUMBER IS
  BEGIN
    RETURN (bookno);
  END;

  STATIC FUNCTION show_super(book_obj in book_type) RETURN VARCHAR2 IS
  BEGIN
    RETURN ('Id: ' || TO_CHAR(book_obj.bookno) || ', Title: ' || book_obj.title);
  END;

  MEMBER FUNCTION show RETURN VARCHAR2 IS
  BEGIN
    RETURN book_type.show_super( SELF );
  END;
END;


3.8. Wyświetlić opis typu book_type:
SELECT type, text
FROM user_source
WHERE name = 'BOOK_TYPE’;

ROZWIĄZANIE:

SELECT type, text
FROM user_source
WHERE name = 'BOOK_TYPE';


3.9. Utworzyć tabele obiektową o nazwie book_obj_table, będącą specjalnym rodzajem tabeli, w której każdy wiersz reprezentuje jeden obiekt typu book_type:

CREATE TABLE … OF …;

ROZWIĄZANIE:

CREATE TABLE book_obj_table OF book_type;


3.10. Wstawić do tabeli 3-4 przykładowe obiekty i wyświetlić jej zawartość używając kolejno fraz:
SELECT * FROM…;
SELECT value(t) FROM … t; --SQLPLUS

ROZWIĄZANIE:

INSERT INTO book_obj_table VALUES(1, 'Ksiazka 1', 'gatunek 1');
INSERT INTO book_obj_table VALUES(2, 'Ksiazka 2', 'gatunek 2');
INSERT INTO book_obj_table VALUES(3, 'Ksiazka 3', 'gatunek 3');

SELECT * FROM book_obj_table;
SELECT value(t) FROM book_obj_table  t;

Zaobserwować różnice. Które zapytanie zwraca wiersz, jako instancję klasy?

Pierwsze zapytanie zwraca wiersz. Drugie zapytanie zwraca jako instancje klasy.


3.11. Wyświetlić (SQLPLUS) zawartość niejawnej kolumny sys_nc_rowinfo$ tabeli book_obj_table.

ROZWIĄZANIE:

SELECT sys_nc_rowinfo$ FROM book_obj_table;


3.12. Wyświetlić (SQLPLUS) zawartość niejawnej kolumny sys_nc_oid$ tabeli book_obj_table.

ROZWIĄZANIE:

SELECT sys_nc_oid$ FROM book_obj_table;


3.13. Utworzyć tabelę o nazwie book_col_table i kolumnach: store_id NUMBER oraz books BOOK_TYPE, w której druga kolumna jest typu obiektowego.

ROZWIĄZANIE:

CREATE TABLE book_col_table (
store_id NUMBER,
books book_type
);


3.14. Wpisać 3-4 zestawy danych do utworzonej w poprzednim punkcie tabeli. Do kolumny books należy wpisać dane poprzez obiekt.

ROZWIĄZANIE:

INSERT INTO "BOOK_COL_TABLE" VALUES ('1', BOOK_TYPE('5', 'Ksiazka1', 'Dramat'));
INSERT INTO "BOOK_COL_TABLE" VALUES ('2', BOOK_TYPE('6', 'Ksiazka2', 'Horror'));
INSERT INTO "BOOK_COL_TABLE" VALUES ('3', BOOK_TYPE('7', 'Ksiazka3', 'Popularnonaukowa'));
INSERT INTO "BOOK_COL_TABLE" VALUES ('4', BOOK_TYPE('8', 'Ksiazka4', 'Fantastyka'));


3.15. Wyświetlić dane wpisane w polu title tabeli book_col_table.

ROZWIĄZANIE:

SELECT p.books.title FROM book_col_table p;


3.16. Utworzyć typ obiektowy book_type2, pozwalający reprezentować serie książek:

ROZWIĄZANIE:

CREATE TYPE book_type2 AS OBJECT (
title VARCHAR2(30),
serie REF book_type2);


3.17. Utworzyć kolejną tabelę obiektową book_obj_table2 typu book_type2.

ROZWIĄZANIE:

CREATE TABLE book_obj_table2 OF book_type2;


3.18. Wstawić dane dotyczące pierwszej książki z serii (REF ustawić na NULL) – np. tytuł pierwszej książki
Trylogii H. Sienkiewicza.

ROZWIĄZANIE:

INSERT INTO book_obj_table2 VALUES('Ogniem i mieczem', NULL);


3.19. Wstawić dane dotyczące kolejnej książki, przy czym ustawić jej REF na książkę dodaną w poprzednim punkcie:
INSERT INTO book_obj_table2 SELECT book_type2 (‘Ogniem i mieczem’, REF(t))
FROM book_obj_table2 t
WHERE t.title = ‘…’;

ROZWIĄZANIE:

INSERT INTO book_obj_table2 SELECT book_type2 ('Potop', REF(t))
FROM book_obj_table2 t
WHERE t.title = 'Ogniem i mieczem';


3.20. Wyświetlić informacje zapisane w tabeli:
SELECT t.title, DEREF(t.serie).title FROM book_obj_table2 t;

ROZWIĄZANIE:

SELECT t.title, DEREF(t.serie).title FROM book_obj_table2 t;


3.21. Dla wcześniej utworzonych tabel wyświetlić pełną listę ich niejawnych kolumn (systemowa tabela słownikowa USER_TAB_COLS).

ROZWIĄZANIE:

SELECT * FROM USER_TAB_COLS WHERE TABLE_NAME = 'BOOK_OBJ_TABLE' OR TABLE_NAME = 'BOOK_OBJ_TABLE2';

Czy sprawdzane tabele mają inny zestaw tych kolumn, czy ten sam?

Sprawdzane tabele mają ten sam zestaw kolumn.


3.22. Utworzyć typ lista_ksiazek_wyp będący tablicą o zmiennej długości (max. 10) typu VARCHAR2(80).

ROZWIĄZANIE:

CREATE TYPE lista_ksiazek_wyp AS VARRAY(10) of varchar2(80);


3.23. Utworzyć typ lista_ksiazek będący tablicą książek.

ROZWIĄZANIE:

CREATE TYPE lista_ksiazek is TABLE of book_type;


3.24. Utworzyć tabelę o nazwie classes zawierającą zagnieżdżoną tablicę książek NESTED TABLE borrows
STORE borrows_tab. Zdefiniować atrybuty:
class_no NUMBER PRIMARY KEY,
branch CHAR(4),
lecture book_type DEFAULT book_type(1,'Dziady',’dramat’),
borrows book_type DEFAULT book_type()

ROZWIĄZANIE:

CREATE TABLE classes
(
class_no NUMBER PRIMARY KEY,
BRANCH CHAR(4),
lecture book_type DEFAULT book_type(1,'Dziady',’dramat’),
borrows lista_ksiazek
)
NESTED TABLE borrows  STORE AS borrows_tab;


3.25. Wpisać przykładowe dane do tabeli zdefiniowanej w poprzednim punkcie:
INSERT INTO classes (class_no, branch, borrows) VALUES
(1, 'a', book_type(
‘…’,
‘…’,
…)
);

ROZWIĄZANIE:

INSERT INTO classes (class_no, branch, borrows) VALUES
(1, 'a', lista_ksiazek(
book_type('7','Ksiazka1','Typ1'),
book_type('8','Ksiazka2','Typ2')
)
);


3.26. Wyświetlić wpisane dane, wpisując zapytanie (tzw. rozgnieżdżanie kolekcji):

ROZWIĄZANIE:

SELECT e.*, d.*
FROM classes d, TABLE(d.borrows) e;


3.27. Wyświetlić zawartość systemowej tablicy user_nested_tables.

ROZWIĄZANIE:

SELECT * FROM user_nested_tables;


3.28. Spróbować usunąć tabelę borrows_tab poleceniem DROP TABLE.

ROZWIĄZANIE:

DROP TABLE borrows_tab;

Co się stało?

Nie udało się usunąć tabeli.


3.29. Usunąć kolumnę tabeli zagnieżdżonej:

ROZWIĄZANIE:

ALTER TABLE classes DROP COLUMN borrows;

Czy stowarzyszona tabela została usunięta?

Stowarzyszona tabela została usunięta pomyślnie.

3.30. Usunąć wcześniej stworzone typy o nazwie lista_ksiazek_wyp, lista_ksiazek.

ROZWIĄZANIE:

DROP TYPE lista_ksiazek_wyp;
DROP TYPE lista_ksiazek;


3.31. Usunąć wcześniej stworzone tabele o nazwach: book_obj_table, book_obj_table2, szkola.

ROZWIĄZANIE:

DROP TABLE book_obj_table;
DROP TABLE book_obj_table2;
DROP TABLE classes;


3.32. Usunąć stworzony w punkcie 3.6 typ obiektowy o nazwie book_type oraz book_type2.

ROZWIĄZANIE:

DROP TYPE book_type FORCE;
DROP TYPE book_type2;


4. XML w Oracle


4.1. Zadać zapytanie, wyświetlające dane zawarte w tabeli PRACOWNICY w formacie XML:
SELECT nr_prac, XMLElement("PRACOWNICY",nazwisko ) AS "wynik"
FROM pracownicy
WHERE nr_prac < 5;

ROZWIĄZANIE:

SELECT nr_prac, XMLElement("PRACOWNICY",nazwisko ) AS "wynik"
FROM pracownicy
WHERE nr_prac < 5;


4.2. Zadać zapytanie SQL operujące na tabeli PRACOWNICY zwracające wynik w postaci XML:
SELECT xmlgen.getXML(’select * from PRACOWNICY) FROM dual;

ROZWIĄZANIE:

SELECT dbms_xmlgen.getXML('select * from PRACOWNICY') FROM dual;


4.3. Zadać kolejno 3 następujące zapytania, porównując uzyskane efekty (sposób „sformatowania” dokumentu
XML):

ROZWIĄZANIE:

SELECT XMLElement("PRACOWNICY", XMLAttributes(nr_prac,nazwisko)) AS "wynik"
FROM pracownicy
WHERE nr_prac < 5;

SELECT XMLElement
("Pracownicy",XMLAttributes(nr_prac AS "numer pracownika"),
XMLColAttVal (nazwisko AS "Nazwisko pracownika"),
XMLColAttVal (nr_zesp AS "Numer zespolu pracownika")) AS "wynik"
FROM pracownicy
WHERE nr_prac < 5;

SELECT XMLElement("PRACOWNICY",
XMLAttributes(nr_prac AS "id"),
XMLElement("NAZWISKO", nazwisko ),
' pracuje w zespole '||nr_zesp||' i urodzil sie ',
XMLElement("data_ur", data_ur))
AS "wynik"
FROM pracownicy
WHERE nr_prac < 5;


4.4. Zadać zapytanie zwracające ciąg elementów (czyli po prostu „las” elementów), dotyczących pracowników –
ich nazwisk i dat urodzenia oraz inf. o zwolnieniu:

ROZWIĄZANIE:

SELECT XMLForest (nazwisko,
data_ur as "urodzony",
'Tak' as "Zwolniony")
FROM pracownicy;


4.5. Zadać zapytanie, grupujące informacje o pracownikach danego zespołu:

ROZWIĄZANIE:

select XMLElement (name "Zespoly",
XMLAttributes(nr_zesp as "Numer"),
XMLAgg(
XMLElement (name "Pracownicy", nazwisko)
)
) wynik
from pracownicy
group by nr_zesp;


4.6. Zadać zapytanie, zwracające w postaci elementu XML informacje o pracownicyach i ich kierownikach.

ROZWIĄZANIE:

SELECT XMLConcat ( XMLElement("SZEF", S.NAZWISKO ),
XMLElement("PODWLADNY", P.NAZWISKO) )
AS "wynik"
FROM pracownicy p, zespoly z, pracownicy s
WHERE p.nr_zesp = z.nr_zesp and z.nr_prac_kz=s.nr_prac;


4.7. Utworzyć tabelę o nazwie testXML na podstawie zapytania SQL selekcjonującego z tabeli PRACOWNICY
informacje o pracownikach o numerze 1 i 2:

ROZWIĄZANIE:

CREATE TABLE testXML AS
SELECT DBMS_XMLGEN.getXML
('SELECT * FROM pracownicy where nr_prac=1 or nr_prac=2')
AS pracownicy
FROM dual;


4.8. Utworzyć 4-kolumnową tabelę o nazwie PRACOWNICY2, zawierającą atrybut o nazwie prac_id (typ:
NUMBER(3)), 1 atrybut prac_spec typu CLOB, zdolny do przechowania elementu XML (typ
sys.XMLType), atrybut prac_name (typ VARCHAR2(35)) oraz funkcja_id (typ NUMBER(4)).

ROZWIĄZANIE:

CREATE TABLE pracownicy2 (
prac_id NUMBER(3),
prac_spec sys.XMLType,
prac_name VARCHAR(35),
funkcja_id NUMBER(4)
);


4.9. Do utworzonej w poprzednim punkcie tabeli wpisać przykładowe dane:

ROZWIĄZANIE:

INSERT INTO pracownicy2 (prac_id, prac_spec)
VALUES (101, sys.XMLType.createXML
('<Pracownicy nr="100">
<charakter_zatrudnienia>Etatowy</charakter_zatrudnienia >
</Pracownicy >'));


4.10. Wyświetlić zawartość tabeli PRACOWNICY2 („klasyczne” polecenie SELECT):

ROZWIĄZANIE:

SELECT * FROM PRACOWNICY2;


4.11. Wyświetlić (klasycznym poleceniem SELECT) zawartość kolumny prac_spec tabeli PRACOWNICY2,
nadając jej na wydruku nazwę „Info o pracowniku”. Dla tabeli użyć aliasu.

ROZWIĄZANIE:

SELECT prac_spec "Info o pracowniku" FROM PRACOWNICY2 ;


4.12. Wyświetlić zawartość kolumny prac_spec utworzonej tabeli, korzystając z operatora extract (funkcja ta
zwraca fragment dokumentu XML wskazany wyrażeniem XPath.):

ROZWIĄZANIE:

SELECT p.prac_spec.extract
('/Pracownicy/charakter_zatrudnienia/text()').getStringVal() "Rodzaj umowy"
FROM pracownicy2 p;


4.13. Zaktualizować kolumnę prac_spec tabeli PRACOWNICY2, nadając jej funkcją createXML
(sys.XMLType.createXML(…)) wartość:

ROZWIĄZANIE:

UPDATE PRACOWNICY2 SET prac_spec=sys.XMLType.createXML('<Pracownicy nr ="200">
<charakter_zatrudnienia>Umowa-zlecenie</charakter_zatrudnienia>
</Pracownicy>');


4.14. Ponownie wyświetlić polecenie SELECT zawartość tabeli PRACOWNICY2.

ROZWIĄZANIE:

SELECT * FROM PRACOWNICY2;


4.15. Dopisać jeszcze jeden zestaw danych do kolumn prac_id oraz prac_spec tabeli PRACOWNICY2.

ROZWIĄZANIE:

INSERT INTO pracownicy2 (prac_id, prac_spec)
VALUES (102, sys.XMLType.createXML
('<Pracownicy nr="100">
<charakter_zatrudnienia>Stale</charakter_zatrudnienia >
</Pracownicy >'));


4.16. Poleceniem SELECT sprawdzić czy nowe dane zostały dopisane do tabeli PRACOWNICY2.

ROZWIĄZANIE:

SELECT * FROM PRACOWNICY2;


4.17. W celu skrócenia czasu odpowiedzi zapytań dotyczących wartości XMLType, użytkownik może skorzystać z
indeksów na wyrażeniach (function-based indexes):

ROZWIĄZANIE:

CREATE INDEX prac_idx ON pracownicy2 p
(p.prac_spec.extract('//Pracownicy').getStringVal());


4.18. Poleceniem DELETE usunąć dopisane w punkcie 4.15 dane. We frazie WHERE użyć:
…WHERE p.prac_spec.extract
('// Pracownicy/charakter_zatrudnienia /text()').getStringVal() = 'tu wpisać wstawioną
wartość’”);

ROZWIĄZANIE:

DELETE FROM pracownicy2 p WHERE p.prac_spec.extract
('// Pracownicy/charakter_zatrudnienia /text()').getStringVal() = 'Stale';