SQL
J
ZYK ZAPYTAC

dr inż. Piotr Muryjas
Instytut Informatyki
Politechnika Lubelska
BAZA DANYCH ECDL  PRZYKA
AD ROZWI
ZANIA
STUDENCI
Id_student VARCHAR2 (7)
Nazwisko VARCHAR2 (25)
Imie VARCHAR2 (15)
EGZAMINATORZY
EGZAMINY Data_ur DATE
Id_egzaminator VARCHAR2 (4)
Nr_egz NUMBER (7) Miejsce VARCHAR2 (15)
Nazwisko VARCHAR2 (25)
Id_student VARCHAR2 (7) PESEL VARCHAR2 (11)
Imie VARCHAR2 (15)
przeprowadza
zdaje
Id_przedmiot NUMBER (3) Kod_poczta VARCHAR2 (5)
Kod_poczta VARCHAR2 (5)
Id_egzaminator VARCHAR2 (4) Miasto VARCHAR2 (15)
Miasto VARCHAR2 (15)
Data_egz DATE Ulica VARCHAR2 (30)
Ulica VARCHAR2 (30)
Id_osrodek NUMBER (5) Numer VARCHAR2 (8)
Numer VARCHAR2 (8)
Zdal VARCHAR2(1) Tel VARCHAR2 (12)
Tel VARCHAR2 (12)
Fax VARCHAR2 (12)
Fax VARCHAR2 (12)
E_mail VARCHAR2 (30)
E_mail VARCHAR2 (30)
Nr_ECDL VARCHAR2 (9)
Data_ECDL DATE
jest miejscem
jest tematem
OSRODKI
Id_osrodek NUMBER (5)
PRZEDMIOTY
Nazwa_o VARCHAR2 (30)
Id_przedmiot NUMBER (3)
Kod_poczta VARCHAR2 (5)
Nazwa_p VARCHAR2 (40)
Miasto VARCHAR2 (15)
Opis VARCHAR2 (200)
Ulica VARCHAR2 (30)
Numer VARCHAR2 (8)
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 2
INSTRUKCJA SELECT
SELECT kol1 [alias1], kol2 [alias2],...,koln, wyrażenie [alias] FROM tabela
gdzie: kol1, kol2,...,koln - nazwy kolumn tabeli, oddzielone przecinkiem; lista kolumn może być
zastąpiona symbolem "*", jeśli wszystkie kolumny na liście
wyrażenie  dowolne wyrażenie oparte na kolumnach podanej tabeli
tabela - nazwa tabeli
Przykłady:
1/ SELECT Id_egzaminator Identyfikator, Nazwisko, Imie FROM egzaminatorzy;
2/ SELECT Id_osrodek, Nazwa_o nazwa FROM osrodki;
3/ SELECT * FROM przedmioty;
ELIMINOWANIE WIERSZY O POWTARZAJ
CEJ SI
WARTOŚCI POLA
SELECT DISTINCT kol1, kol2,...,koln FROM tabela
Klauzula DISTINCT musi pojawić się jako pierwsze słowo kluczowe po słowie SELECT.
Przykłady:
1/ SELECT DISTINCT Id_przedmiot, Id_student FROM egzaminy;
2/ SELECT DISTINCT Id_osrodek FROM egzaminy;
SORTOWANIE WYŚWIETLANYCH INFORMACJI
SELECT kol1, kol2,...,koln FROM tabela ORDER BY koli
gdzie: koli - nazwa kolumny, według której odbywa się sortowanie
Porządek sortowania - rosnący (ASCENDING) - domyślny, malejący (DESCENDING).
ORDER BY musi być ostatnią klauzulą w instrukcji SELECT.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 3
SELECT kol1, kol2,...,koln FROM tabela
ORDER BY koli DESC
Przykłady:
1/ SELECT Id_osrodek, Nazwa_o FROM osrodki ORDER BY Miasto;
2/ SELECT Id_student, Nazwisko, Imie FROM studenci ORDER BY Nazwisko;
Sortowanie informacji według kilku kolumn, zgodnie z ogólną postacią polecenia:
SELECT kol1, kol2,...,koln FROM tabela
ORDER BY koli, kolj
lub
SELECT kol1, kol2,...,koln FROM tabela
ORDER BY koli DESC, kolj
Przykłady:
1/ SELECT Id_egzaminator, Nazwisko, Imie FROM egzaminatorzy ORDER BY Nazwisko, Imie;
2/ SELECT Id_student, Id_przedmiot, Data_egz FROM egzaminy ORDER BY Id_student, Data_egz
DESC;
DEFINIOWANIE WARUNKÓW WYSZUKIWANIA
SELECT kol1, kol2,...,koln FROM tabela WHERE warunek
gdzie: warunek - warunek lub warunki, które muszą spełniać wybrane i wyświetlane rekordy
tabeli
Operatory SQL:
" (NOT) BETWEEN ... AND ...
" (NOT) IN (lista wartości)
" (NOT) LIKE
" IS (NOT) NULL
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 4
SELECT kol1, kol2,...,koln FROM tabela
WHERE koli BETWEEN wyrażenie_1 AND wyrażenie_2
SELECT kol1, kol2,...,koln FROM tabela
WHERE koli IN (wartość_1, wartość_2,...,wartość_n | polecenie SELECT)
SELECT kol1, kol2,...,koln FROM tabela
WHERE koli NOT LIKE "A%" AND kolj LIKE "k_"
SELECT kol1, kol2,...,koln FROM tabela
WHERE koli IS NULL
Przykłady:
1/ SELECT Id_student, Id_przedmiot, Data_egz FROM egzaminy WHERE Data_egz BETWEEN
'20-04-98' AND '20-05-98' ORDER BY Id_student, Data_egz DESC;
2/ SELECT Id_przedmiot, Id_egzaminator, Data_egz FROM egzaminy WHERE Id_student IN
('0000050', '0000055');
3/ SELECT * FROM egzaminatorzy WHERE Id_egzaminator IN (SELECT DISTINCT
Id_egzaminator FROM egzaminy);
4/ SELECT * FROM przedmioty WHERE Id_przedmiot NOT IN (SELECT DISTINCT
Id_przedmiot FROM egzaminy);
5/ SELECT Nazwisko, Imie FROM studenci WHERE Nazwisko LIKE 'M%';
6/ SELECT * FROM studenci WHERE E_mail LIKE 'p_' ;
7/ SELECT Id_student, Nazwisko, Imie FROM studenci WHERE Nr_ECDL IS NULL;
8/ SELECT * FROM studenci WHERE Id_student = Nr_ECDL;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 5
TWORZENIE KRYTERIÓW ZA
OŻONYCH
Wymaga stosowania operatorów AND i OR.
SELECT kol1, kol2,...,koln FROM tabela
WHERE warunek1 AND | OR warunek2 [AND | OR warunek2 ...]
Przykłady:
1/ SELECT Id_student, Id_przedmiot, Data_egz FROM egzaminy WHERE Id_osrodek = 5 AND
Data_egz BETWEEN '20-04-98' AND '20-05-98';
2/ SELECT Id_student FROM egzaminy WHERE Zdal = 'N' AND Id_przedmiot = 7;
3/ SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz > '20-04-98' AND
(Id_egzaminator = '0036' OR Id_egzaminator = '0040');
4/ SELECT * FROM egzaminy WHERE Id_osrodek = 3 AND Data_egz LIKE '%04-98';
HIERARCHIA OPERATORÓW
OPERATOR OPERACJA
=, !=, <, >, <=, >=, IS NULL, LIKE, porównanie
BETWEEN, IN
NOT negacja
AND koniunkcja
OR alternatywa
" wszystkie operatory porównania i operatory SQL mają jednakowy, najwyższy
priorytet
" operator NOT
" AND
" OR
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 6
Przykłady:
1/ SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz > '20-04-98' AND
Id_egzaminator = '0036' OR Id_egzaminator = '0040';
GRUPOWANIE DANYCH
Wykonuje się w celu uzyskania informacji zbiorczych dla zbioru rekordów.
SELECT wyrażenie_grup FROM tabela [WHERE warunek]
SELECT kol1, kol2,...,koln, wyrażenie_grup FROM tabela GROUP BY koli, kolj
gdzie: koli - nazwa kolumny według której przebiega grupowanie wierszy,
wyrażenie_grup - nazwa funkcji grupowej.
Rodzaje wyrażeń grupujących - funkcji grupowej:
" AVG([ DISTINCT ] kol | wyrażenie)
" COUNT( [ DISTINCT ] kol | wyrażenie)
" MAX( [ DISTINCT ] kol | wyrażenie)
" MIN( [ DISTINCT ] kol | wyrażenie)
" SUM([ DISTINCT ] kol | wyrażenie)
Przykłady:
1/ SELECT COUNT(*) FROM egzaminy WHERE Id_egzaminator = '0036';
2/ SELECT MAX(Data_egz) FROM egzaminy WHERE Id_osrodek = 5;
3/ SELECT Id_osrodek, COUNT(*) FROM egzaminy GROUP BY Id_osrodek;
4/ SELECT Id_przedmiot, COUNT(*) FROM egzaminy GROUP BY Id_przedmiot;
5/ SELECT MIN(Data_egz), Id_student FROM egzaminy GROUP BY Id_student;
6/ SELECT Id_egzaminator, Id_osrodek, COUNT(*) FROM egzaminy GROUP BY
Id_egzaminator, Id_osrodek ORDER BY Id_osrodek;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 7
WYBÓR GRUP DANYCH WEDA
UG ZADANEGO KRYTERIUM
SELECT kol1, kol2,...,koln, wyrażenie_grup FROM tabela
GROUP BY koli, kolj
HAVING warunek_grup
gdzie: warunek_grup - warunek, jaki muszą spełniać poszczególne zdefiniowane grupy
Przykłady:
1/ SELECT COUNT(*) FROM egzaminy GROUP BY Id_osrodek HAVING Id_osrodek = 5;
2/ SELECT Id_egzaminator, COUNT(*) FROM egzaminy GROUP BY Id_egzaminator HAVING
COUNT(*) > 10;
3/ SELECT Id_przedmiot, COUNT(*) FROM egzaminy GROUP BY Id_przedmiot HAVING
COUNT(*) > 8;
4/ SELECT Id_student, COUNT(*) FROM egzaminy WHERE Id_osrodek = 1 GROUP BY
Id_student HAVING COUNT(*) > 2 ORDER BY Id_student;
WYSZUKIWANIE DANYCH W WIELU TABELACH
Typy operacji złączenia tabel:
" równozłączenie (equi-join) - oparte na operatorze równości
" non equi-join - oparte jest na operatorze relacji różnym od równości np. <, >, <=, >=
lub operatora SQL BETWEEN
SELECT tab1.kol1,...,tab1.koln, tab2.kol1,...,tab2.koln
FROM tabela1 tab1, tabela2 tab2
WHERE tab1.koli = tab2.kolj
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 8
SELECT tab1.kol1,...,tab1.koln, tab2.kol1,...,tab2.koln
FROM tabela1 tab1
INNER JOIN tabela2 tab2
ON tab1.koli = tab2.kolj
Przykłady:
1/ SELECT egzaminy.Id_student, Id_przedmiot, Nazwisko, Imie FROM egzaminy, studenci
WHERE egzaminy.Id_student = studenci.Id_student;
2/ SELECT egzaminy.Id_student, Nazwisko, Imie, egzaminy.Id_osrodek, Nazwa_o FROM
egzaminy, studenci, osrodki WHERE egzaminy.Id_student = studenci.Id_student AND
egzaminy.Id_osrodek = osrodki.Id_osrodek ORDER BY egzaminy.Id_student;
Liczba związków łączących n tabel: n-1
Szczególny przypadek złączenia tabel (tzw. złączenie unarne):
SELECT tab1.kol1,...,tab1.koln, tab2.kol1,...,tab2.koln
FROM tabela1 tab1, tabela1 tab2
WHERE tab1.koli = tab2.kolj
Złączenie rozszerzające:
SELECT tab1.kol1,...,tab1.koln, tab2.kol1,...,tab2.koln
FROM tabela1 tab1, tabela1 tab2
WHERE tab1.koli = tab2.koli(+)
SELECT tab1.kol1,...,tab1.koln, tab2.kol1,...,tab2.koln
FROM tabela1 tab1
LEFT JOIN tabela2 tab2
ON tab1.koli = tab2.kolj
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 9
SELECT tab1.kol1,...,tab1.koln, tab2.kol1,...,tab2.koln
FROM tabela1 tab1
RIGHT JOIN tabela2 tab2
ON tab1.koli = tab2.kolj
Przykłady:
1/ SELECT o.Id_osrodek, Nazwa_o, COUNT(e.Id_osrodek) FROM osrodki o, egzaminy e
WHERE o.Id_osrodek = e.Id_osrodek(+) GROUP BY o.Id_osrodek, Nazwa_o;
2/ SELECT p.Id_przedmiot, Nazwa_p, COUNT(DISTINCT e.Id_student) FROM przedmioty p,
egzaminy e WHERE p.Id_przedmiot = e.Id_przedmiot(+) GROUP BY p.Id_przedmiot, Nazwa_p;
3/ SELECT o.Id_osrodek, Nazwa_o, NVL(TO_CHAR(MIN(e.Data_egz)),'Brak danych') Data
FROM osrodki o, egzaminy e
WHERE o.Id_osrodek = e.Id_osrodek(+) GROUP BY o.Id_osrodek, Nazwa_o;
INNE SPOSOBY A

CZENIA TABEL
Operator UNION
SELECT kol1, kol2,...,koln FROM tabela1
UNION
SELECT kol1, kol2,...,koln FROM tabela2
gdzie: kol1, kol2,...,koln - nazwy kolumn tabel, mogą być takie same, jeśli nie są - to wartości
odpowiadających sobie pól muszą być tego samego typu
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 10
SELECT kol1, kol2,...,koln FROM tabela1
UNION
SELECT kol1, kol2,...,koln FROM tabela2
GROUP BY 2
Przykład:
SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Id_student = '0000050'
UNION
SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Id_student = '0000060';
Operator INTERSECT
SELECT kol1, kol2,...,koln FROM tabela1
INTERSECT
SELECT kol1, kol2,...,koln FROM tabela2
Przykład:
SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz BETWEEN '20-04-98'
AND '30-04-98'
INTERSECT
SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz BETWEEN '10-04-98'
AND '25-04-98';
Operator MINUS
SELECT kol1, kol2,...,koln FROM tabela1
MINUS
SELECT kol1, kol2,...,koln FROM tabela2
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 11
Przykład:
1/ SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz BETWEEN '20-04-98'
AND '30-04-98'
MINUS
SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz BETWEEN '10-04-98'
AND '25-04-98';
2/ SELECT Id_osrodek, Nazwa_o FROM osrodki
MINUS
SELECT DISTINCT o.Id_osrodek, Nazwa_o FROM osrodki o, egzaminy e
WHERE o.Id_osrodek = e.Id_osrodek;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 12
PODZAPYTANIA
SELECT kol1, kol2,...,koln FROM tabela1 WHERE koli =
(SELECT kolm FROM tabela2 [WHERE warunek])
Podzapytania:
" wewnętrzna instrukcja SELECT wykonywana jest w pierwszej kolejności i tylko jeden
raz (z wyjątkiem podzapytań skorelowanych)
" umożliwiają wykonanie skomplikowanych wyborów danych
" pozwalają definiować warunki w sposób dynamiczny
Przykłady:
1/ SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz = (SELECT
MIN(Data_egz) FROM egzaminy);
2/ SELECT Id_student, Nazwisko, Imie FROM studenci WHERE Id_student IN (SELECT
DISTINCT Id_student FROM egzaminy);
3/ SELECT Id_przedmiot, Nazwa_p FROM przedmiotyWHERE Id_przedmiot NOT IN (SELECT
DISTINCT Id_przedmiot FROM egzaminy);
Rodzaje podzapytań:
" zwracające jedną wartość (przykład 1)
" zwracające wiele wartości (przykład 2 i 3)
Przykłady:
1/ SELECT * FROM studenci WHERE Miasto = (SELECT Miasto FROM studenci WHERE
PESEL = '49887400000');
2/ SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz IN (SELECT Data_egz
FROM egzaminy WHERE Id_student = '0000049') AND Id_student <> '0000049';
3/ SELECT Id_egzaminator, Id_osrodek FROM egzaminy WHERE (Id_egzaminator, Data_egz) IN
(SELECT Id_egzaminator, MAX(Data_egz) FROM egzaminy GROUP BY Id_egzaminator);
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 13
OPERATORY W PODZAPYTANIACH - ANY, ALL
ANY - porównywanie z dowolną wartością zwracaną przez podzapytanie
ALL - porównywanie z każdą wartością zwracaną przez podzapytanie
Przykłady:
1/ SELECT Id_student FROM egzaminy WHERE Data_egz < ANY (SELECT DISTINCT
Data_egz FROM egzaminy WHERE Id_przedmiot = 1);
2/ SELECT * FROM egzaminy WHERE Data_egz > ALL (SELECT DISTINCT Data_egz FROM
egzaminy WHERE Id_przedmiot = 1);
PODZAPYTANIA A GRUPOWANIE DANYCH
SELECT koli, wyraż_grup FROM tabela1
GROUP BY koli HAVING koli =
(SELECT kolm FROM tabela2 WHERE warunek)
SELECT koli, wyraż_grup FROM tabela1
GROUP BY koli HAVING koli =
(SELECT kolm FROM tabela2 GROUP BY kolm)
Przykłady:
1/ SELECT Id_przedmiot FROM egzaminy GROUP BY Id_przedmiot HAVING
MAX(Data_egz) > (SELECT MAX(Data_egz) FROM egzaminy WHERE Id_przedmiot = 3);
2/ SELECT Id_egzaminator, COUNT(*) FROM egzaminy GROUP BY Id_egzaminator HAVING
COUNT(*) = (SELECT MAX(COUNT(*)) FROM egzaminy GROUP BY Id_egzaminator);
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 14
PODZAPYTANIA A SORTOWANIE DANYCH
SELECT koli, wyraż_grup FROM tabela1
GROUP BY koli HAVING koli =
(SELECT kolm FROM tabela2 WHERE warunek)
ORDER BY kolj
W podzapytaniu nie może wystąpić klauzula ORDER BY. Można ją umieszczać wyłącznie jako
ostanią w nadrzędnej instrukcji SELECT.
Przykład:
SELECT Id_przedmiot FROM egzaminy GROUP BY Id_przedmiot HAVING MAX(Data_egz) >
(SELECT MAX(Data_egz) FROM egzaminy WHERE Id_przedmiot = 3) ORDER BY Id_przedmiot;
ZAGNIEŻDŻANIE PODZAPYTAC

SELECT kol1, kol2, ..., koli FROM tabela1 WHERE koli =
(SELECT kolj FROM tabela2 WHERE kol =
SELECT kolm FROM tabela3 WHERE warunek)
Przykłady:
1/ SELECT Id_egzaminator, Nazwisko, Imie FROM egzaminatorzy WHERE Id_egzaminator IN
(SELECT Id_egzaminator FROM egzaminy GROUP BY Id_egzaminator HAVING COUNT(*)
= (SELECT MAX(COUNT(*)) FROM egzaminy GROUP BY Id_egzaminator));
2/ SELECT Id_student, Nazwisko, Imie FROM studenci WHERE Id_student IN (SELECT
Id_student FROM egzaminy GROUP BY Id_student, Zdal HAVING Zdal = 'N' AND
COUNT(*) = (SELECT MAX(COUNT(*)) FROM egzaminy GROUP BY Id_student, Zdal
HAVING Zdal = 'N'));
Zagnieżdżanie podzapytań może odbywać się bez żadnych ograniczeń !!!
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 15
UWAGI DOTYCZ
CE TWORZENIA PODZAPYTAC

" Wewnętrzne zapytania należy ujmować w nawiasy i muszą one wystąpić po prawej
stronie warunku w zapytaniu nadrzędnym.
" Konieczna jest zgodność kolejności, ilości i typu kolumn występujących w podzapytaniu
z ilością i typem kolumn, znajdujących się w zapytaniu nadrzędnym.
" Podzapytania nie mogą zawierać klauzuli sortowania ORDER BY. Może ona wystąpić
jedynie w zapytaniu głównym.
" Zapytania zagnieżdżone wykonywane są w kolejności od najbardziej wewnętrznego do
zapytania głównego.
" Tworzenie kolejnych podzapytań wymaga stosowania operatorów SQL oraz operatorów
ANY lub ALL.
PODZAPYTANIA SKORELOWANE
" Elementem klauzuli WHERE lub HAVING podzapytania jest kolumna występująca w
zapytaniu bezpośrednio nadrzędnym.
" Podzapytanie skorelowane jest wykonywane wielokrotnie tzn. dla każdego rekordu
zapytania nadrzędnego.
" W podzapytaniu występuje odwołanie do wartości pól rekordu z zapytania
nadrzędnego.
Przykłady:
1/ SELECT Id_egzaminator, Id_student, Id_przedmiot, Data_egz FROM egzaminy e1 WHERE
Data_egz = (SELECT MAX(Data_egz) FROM egzaminy WHERE Id_przedmiot =
e1.Id_przedmiot);
2/ SELECT Id_student, Id_osrodek, Id_przedmiot, Data_egz, Zdal FROM egzaminy e1
WHERE Data_egz = (SELECT MAX(Data_egz) FROM egzaminy WHERE Id_przedmiot =
e1.Id_przedmiot AND Id_osrodek = e1.Id_osrodek) AND Zdal = 'Y' ORDER BY Id_osrodek,
Id_przedmiot, Data_egz;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 16
OPERATOR EXISTS W PODZAPYTANIACH SKORELOWANYCH
Operator EXISTS pozwala przeprowadzać kontrolę spełnienia określonego warunku użytego
w podzapytaniu skorelowanym. Możliwe jest także zaprzeczenie warunku w postaci NOT EXISTS.
Rezultat zastosowania operatora to wartość TRUE lub FALSE, w zależności od spełnienia bądz
nie
określonego warunku.
Przykłady:
1/ SELECT Id_przedmiot, Nazwa_p FROM przedmioty p WHERE NOT EXISTS
(SELECT Id_przedmiot FROM egzaminy e WHERE p.Id_przedmiot = e.Id_przedmiot);
2/ SELECT Id_student, Nazwisko, Imie FROM studenci s WHERE NOT EXISTS (SELECT
Id_student FROM egzamin e WHERE e.Id_student = s.Id_student);
3/ SELECT Id_egzaminator, Nazwisko, Imie FROM egzaminatorzy eg WHERE EXISTS
(SELECT Data_egz FROM egzaminy e WHERE e.Id_egzaminator = eg.Id_egzaminator);
EFEKTYWNOŚĆ WYKONYWANIA PODZAPYTAC

Szybkość wykonywania podzapytań zależy w głównej mierze od:
" wielkości tabel,
" ilości zwracanych wierszy,
" istnienia indeksów dla pól użytych w warunkach.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 17
POLECENIA J
ZYKA DDL
" CREATE
" ALTER
" DROP
Język DDL przeznaczony jest do:
" definiowania nowych tabel, perspektyw i indeksów
" modyfikowania struktury istniejących tabel
" usuwania tabel, perspektyw i indeksów
" definiowania użytkowników BD
" definiowania praw do wykonywania operacji lub zbiorów praw (tzw. ról)
" usuwania użytkowników BD, praw lub ról
ZASADY DEFINIOWANIA TABEL
Nazwa tabeli:
" musi rozpoczynać się od litery
" może zawierać cyfry, znak podkreślenia, znaki specjalne $ i #; nie może w niej
wystąpić znak spacji
" nie może być dłuższa niż 30 znaków
" musi być jednoznaczna
" duże i małe litery nie są rozróżniane
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 18
TWORZENIE TABEL - CREATE TABLE
Polecenie tworzy nową pustą tabelę.
CREATE TABLE tabela
(kolumna_1 typ_kol1 [option1],
kolumna_2 typ_kol2 [option2],...,
kolumna_n typ_koln [optionn],
definicje_war_integralności)
Opis każdej kolumny może ponadto zawierać pewne opcje, dotyczące:
a) domyślnej wartości początkowej - DEFAULT war_pocz
b) możliwości przyjmowania bądz
nie wartości NULL (domyślnie przyjmuje się NULL)
c) definiowania klucza głównego tabeli - PRIMARY KEY
d) określania unikalnych wartości w kolumnie - UNIQUE
e) kontroli wartości wprowadzanych do kolumny - CHECK (warunek)
f) informacje o utworzonej tabeli można uzyskać wydając polecenie
DESC nazwa_tabeli
Przykłady:
1/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1) PRIMARY KEY,
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL);
2/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1),
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL,
PRIMARY KEY(Id_przedmiot), UNIQUE(Nazwa_p, Opis));
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 19
DEFINIOWANIE TABEL W OPARCIU O WZORZEC INNEJ TABELI
CREATE TABLE tabela [(kol1, kol2, ..., koln)]
AS SELECT zapytanie
Przykłady:
1/ CREATE TABLE kopia_przedmioty AS
SELECT Id_przedmiot, Nazwa_p FROM przedmioty;
2/ CREATE TABLE kopia_osrodki (Nr_osr, Nazwa_osr) AS
SELECT Id_osrodek, Nazwa_o FROM osrodki WHERE Miasto = 'Lublin';
Uwagi dotyczące tworzenia tabel wg wzorca:
" Utworzona tabela zawiera kolumny wskazane na liście polecenia SELECT.
" Jeśli nie określono nazw kolumn w tworzonej tabeli (przykład 1), domyślnie
otrzymają one nazwy kolumn pochodzące ze wzorca.
" Jeśli podano listę nazw kolumn w tworzonej tabeli, ilość kolumn na tej liście musi być
równa ilości kolumn na liście polecenia SELECT.
" Na liście polecenia SELECT nie mogą wystąpić wyrażenia, jeśli nie podano nazwy
kolumny w tworzonej tabeli (konieczne jest opisanie wyrażenia za pomocą aliasu).
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 20
DEFINIOWANIE WI
ZÓW INTEGRALNOŚ CI - CONSTRAINT
" występowanie wartości różnych od NULL
" występowanie unikalnych wartości w kolumnie (kolumnach) - UNIQUE
" wskazanie kolumny będącej kluczem podstawowym:
CONSTRAINT nazwa_więzu PRIMARY KEY (koli, kolj) lub
CONSTRAINT nazwa_więzu PRIMARY KEY
" wskazanie klucza obcego jako związku z inna tabelą:
CONSTRAINT nazwa_więzu FOREIGN KEY (koli) REFERENCES
tabela(kolumna)
" usuwanie wartości pól z tabel pozostających w związku FOREIGN KEY:
CONSTRAINT nazwa_więzu FOREIGN KEY (koli) REFERENCES
tabela(kolumna) ON DELETE CASCADE
" spełnianie przez wartości kolumny określonego warunku:
CONSTRAINT nazwa_więzu CHECK (warunek)
Więzy integralności mogą być definiowane na poziomie:
" kolumny (NOT NULL, PRIMARY KEY, UNIQUE, CHECK)
" tabeli (CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE)
Przykłady:
1/ CREATE TABLE osrodki
(Id_osrodek NUMBER(5) CONSTRAINT osr_Id_osrodek_nn NOT NULL,
Nazwa_o VARCHAR2(30) CONSTRAINT osr_nazwa_nn NOT NULL,
Kod VARCHAR2(5),
Miasto VARCHAR2(15),
Ulica VARCHAR2(30),
Numer VARCHAR2(8),
CONSTRAINT osr_Id_osrodek_pk PRIMARY KEY(Id_osrodek));
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 21
2/ CREATE TABLE egzaminy
(Nr_egz NUMBER(7)
CONSTRAINT egin_nr_egz_nn NOT NULL,
Id_student VARCHAR2(7)
CONSTRAINT egin_Id_student_nn NOT NULL,
Id_przedmiot NUMBER(1)
CONSTRAINT egin_Id_przedmiot_nn NOT NULL,
Id_egzaminator VARCHAR2(4)
CONSTRAINT egin_Id_egzaminator_nn NOT NULL,
Data_egz DATE,
Id_osrodki NUMBER(5)
CONSTRAINT egin_Id_osrodek_nn NOT NULL,
Zdal VARCHAR2(1)
CONSTRAINT egin_zdal_check CHECK(Zdal IN ('Y', 'N')),
CONSTRAINT egin_nr_egz_pk PRIMARY KEY (Nr_egz),
CONSTRAINT egin_nr_egz_check CHECK(Nr_egz > 0),
CONSTRAINT egin_Id_student_fk FOREIGN KEY (Id_student) REFERENCES
studenci (Id_student),
CONSTRAINT egin_Id_przedmiot_fk FOREIGN KEY (Id_przedmiot) REFERENCES
przedmioty (Id_przedmiot),
CONSTRAINT egin_Id_egzaminator_fk FOREIGN KEY (Id_egzaminator)
REFERENCES egzaminatorzy (Id_egzaminator),
CONSTRAINT egin_Id_osrodek_fk FOREIGN KEY (Id_osrodek) REFERENCES
osrodki (Id_osrodek) ON DELETE CASCADE);
MODYFIKACJA DEFINICJI TABEL - ALTER TABLE
Modyfikacja definicji tabeli obejmuje:
" dodanie nowych kolumn
" zmiany typu, rozmiaru, wartości domyślnej kolumn lub warunku NOT NULL
" dodanie/usunięcie więzów integralności (kolumn lub tabeli)
" aktywację/deaktywację więzów integralności
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 22
ALTER TABLE tabela
ADD (kol1 typ_kol1 [opt1], kol2 typ_kol2 [opt2], ...)
MODIFY (kolumna kol_typ [opt])
ALTER TABLE tabela
ADD CONSTRAINT nazwa_więzu definicja_więzu
DROP CONSTRAINT nazwa_więzu [CASCADE]
DROP PRIMARY KEY [CASCADE]
DROP UNIQUE(kol1, kol2) [CASCADE]
ENABLE CONSTRAINT nazwa_więzu
DISABLE CONSTRAINT nazwa_więzu
Przykłady:
1/ ALTER TABLE egzaminatorzy
ADD (Pensja NUMBER(7) DEFAULT 100,
Data_ur DATE NOT NULL);
2/ ALTER TABLE egzaminatorzy
ADD CONSTRAINT egz_pensja CHECK(Pensja > 0);
3/ ALTER TABLE egzaminatorzy
ADD (Miejsce_pracy VARCHAR2(40))
MODIFY (Pensja NUMBER(8,2));
4/ ALTER TABLE egzaminatorzy
MODIFY (Pensja DEFAULT 500);
5/ ALTER TABLE osrodki
DROP CONSTRAINT osr_nazwa_uk;
6/ ALTER TABLE osrodki
DROP PRIMARY KEY CASCADE;
7/ ALTER TABLE osrodki
DISABLE CONSTRAINT osr_Id_osrodek_pk;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 23
OGRANICZENIA MODYFIKACJI TABEL
" Konieczne jest posiadanie praw ALTER TABLE do tabeli lub ALTER ANY TABLE.
" Dodanie nowej kolumny, dla której musi być spełniony warunek NOT NULL
możliwe jest, gdy tabela nie zawiera żadnych rekordów; jeśli tabela zawiera rekordy
należy wprowadzić dane do wstawionej kolumny a następnie zmienić jej własność na
NOT NULL.
" Modyfikacja kolumn poprzez zmianę typów danych i zmniejszenie rozmiaru możliwa
jest, jeśli dla wszystkich rekordów wartości w tych kolumnach są równe NULL.
" Zawsze możliwe jest zwiększenie rozmiaru typu danych znakowych lub
numerycznych.
" Zmiana wartości domyślnej w kolumnie przy pomocy parametru DEFAULT nie
wpływa na wartości już przechowywane w BD.
" Modyfikacja istniejących więzów może polegać jedynie na wprowadzeniu warunku
NOT NULL, przy założeniu, że dane zgromadzone w BD we wskazanej kolumnie nie
są równe NULL.
" Nie można usunąć więzów definiujących związki między tabelami opartych na
indeksach typu PRIMARY KEY lub UNIQUE bez usunięcia odpowiedniego klucza
obcego; konieczne jest użycie opcji CASCADE.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 24
AKTYWACJA/DEAKTYWACJA WI
ZÓW INTEGRALNOŚ CI
" definicja więzów integralności przechowywana jest w BD
" domyślnie więzy integralności są aktywne i umożliwiają kontrolę poprawności
wprowadzania danych
" więzy integralności mogą nie być sprawdzane, jeśli będą nieaktywne
" aktywacja/deaktywacja więzów może nastąpić na etapie definiowania (CREATE
TABLE) lub modyfikowania tabeli (ALTER TABLE)
Aktywacja więzów:
ENABLE PRIMARY KEY | UNIQUE(kol1,...) | CONSTRAINT nazwa_więzu
Przykłady:
1/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1) PRIMARY KEY,
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL)
ENABLE PRIMARY KEY;
2/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1) PRIMARY KEY,
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL);
ALTER TABLE przedmioty ENABLE PRIMARY KEY;
3/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1)
CONSTRAINT prz_Id_przedmiot_pk PRIMARY KEY DISABLE,
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL);
ALTER TABLE przedmioty ENABLE CONSTRAINT prz_Id_przedmiot_pk;
4/ ALTER TABLE egzaminy ENABLE CONSTRAINT egin_Id_student_fk;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 25
Uwagi dotyczące aktywacji więzów:
" Aktywacja więzów dla tabeli zawierających rekordy może nastąpić tylko wówczas,
gdy wszystkie rekordy spełniają podane warunki integralności.
" Aktywacja więzów typu PRIMARY KEY lub UNIQUE powoduje utworzenie
odpowiednich indeksów (przykład 3) - wymagane jest posiadanie prawa CREATE
INDEX lub CREATE ANY INDEX.
" Aktywacja więzów definiujących związek tabeli z innymi tabelami wymaga istnienia
aktywnych więzów w tabelach powiązanych (przykład 4).
Deaktywacja więzów:
DISABLE PRIMARY KEY | UNIQUE(kol1,...) | CONSTRAINT nazwa_więzu
Przykłady:
1/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1) PRIMARY KEY,
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL)
DISABLE PRIMARY KEY;
2/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1) PRIMARY KEY,
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL);
ALTER TABLE przedmioty DISABLE PRIMARY KEY;
3/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1)
CONSTRAINT prz_Id_przedmiot_pk PRIMARY KEY,
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL);
ALTER TABLE przedmioty DISABLE CONSTRAINT prz_Id_przedmiot_pk CASCADE;
4/ ALTER TABLE studenci DISABLE CONSTRAINT st_Id_stud_pk CASCADE;
5/ ALTER TABLE egzaminatorzy ADD (CONSTRAINT egz_pensja CHECK(Pensja > 500))
DISABLE CONSTRAINT egz_pensja;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 26
Uwagi dotyczące deaktywacji więzów:
" Deaktywacja więzów nie powoduje usunięcia ich definicji z BD.
" Definicja więzów i ich deaktywacja w tej samej instrukcji dla tabeli zawierających
rekordy nie wymusza kontroli danych istniejących w tabeli.
" Deaktywacja więzów typu PRIMARY KEY lub UNIQUE powoduje usunięcie z BD
odpowiednich indeksów (przykład 4) - wymagane jest posiadanie prawa DROP
INDEX lub DROP ANY INDEX.
" Deaktywacja więzów definiujących związek tabeli z innymi tabelami wymaga
usunięcia aktywnych więzów w tabelach powiązanych przy użyciu opcji CASCADE
(przykład 4).
USUNI
CIE TABEL - DROP TABLE
DROP TABLE tabela [CASCADE CONSTRAINTS]
Uwagi dotyczące usuwania tabel:
" Konieczne jest posiadanie prawa DROP TABLE do danej tabeli lub prawa DROP
ANY TABLE.
" Usuwane są wszystkie indeksy skojarzone z tabelą.
" Wszystkie perspektywy oparte na tabeli tracą sens, jednak ich definicja nie jest
usuwana z BD.
" Jeśli dla usuwanej tabeli zdefiniowane są związki oparte na indeksach typu
PRIMARY KEY lub UNIQUE, należy je również usunąć używając opcji CASCADE
CONSTRAINTS; w przeciwnym razie tabela nie zostanie usunięta, a SZRBD
ORACLE sygnalizuje błąd wykonania instrukcji.
Przykłady:
1/ DROP TABLE egzaminatorzy;
2/ DROP TABLE osrodki CASCADE CONSTRAINTS;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 27
INNE POLECENIA J
ZYKA DDL
COMMENT - wprowadzenie komentarza dotyczącego tabeli, perspektywy lub kolumny
Przykłady:
1/ COMMENT ON TABLE egzaminatorzy IS 'Lista egzaminatorów ECDL';
2/ COMMENT ON COLUMN przedmioty.Opis IS 'Szczegółowy opis modułu';
RENAME - zmiana nazwy obiektu bazy danych (tabeli, perspektywy)
Przykład:
RENAME TABLE egzaminatorzy TO egzaminator;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 28
POLECENIA J
ZYKA DML
" INSERT
" UPDATE
" DELETE
Język DML przeznaczony jest do:
" wstawiania nowych rekordów do tabel
" modyfikowania istniejących rekordów
" usuwania rekordów z tabel
WSTAWIANIE REKORDÓW - INSERT
Wstawienie jednego rekordu:
INSERT INTO tabela [(kol1, kol2,..., koln)]
VALUES (wart1, wart2,..., wartn)
gdzie: tabela - nazwa tabeli, w której wstawiany będzie nowy rekord
kol1,.., koln - nazwy kolumn tabeli
wart1,..., wartn - wartości wstawiane odpowiednio do kolumn kol1,..., koln
Wstawienie wielu rekordów:
INSERT INTO tabela1 [(kol1, kol2,..., koln)]
SELECT koli, kolj,...,kolo
FROM tabela2
[WHERE warunek]
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 29
Przykłady:
1/ INSERT INTO studenci
VALUES ('0000049', 'Szafrańska', 'Agnieszka', '17-04-77', 'Lublin', '17047701583', '20145',
'Lublin', 'Daszyńskiego', '19/119', NULL, NULL, NULL, NULL, NULL);
2/ INSERT INTO osrodki (Id_osrodek, Nazwa_o, Miasto, Ulica)
VALUES (1, 'CKMP', 'Lublin', 'Bursaki');
3/ INSERT INTO przedmioty SELECT * FROM kopia_przedmioty;
4/ INSERT INTO przedmioty (Id_przedmiot, Nazwa_p) SELECT Id_przedmiot, Nazwa_p FROM
kopia_przedmioty;
5/ INSERT INTO egzaminy (Nr_egz, Id_student, Id_przedmiot, Id_egzaminator, Data_egz,
Id_osrodek) VALUES (1, '0000049', 1, '0036', '20-08-98', 1);
Uwagi dotyczące wstawianie rekordów:
" Wymagane posiadanie prawa INSERT do danej tabeli lub INSERT ANY TABLE.
" Wstawianie wartości typu znakowego lub daty wymaga umieszczenia ich pomiędzy
apostrofami (przykład 1).
" Jeśli polecenie INSERT zawiera listę kolumn, to ich liczba musi być równa liczbie
wstawianych wartości w klauzuli VALUES lub liczbie kolumn na liście polecenia
SELECT (przykład 2).
" Pominięcie listy kolumn w poleceniu INSERT wymaga podania wartości dla każdej
kolumn (przykład 1).
" Wstawianie rekordów przy pomocy polecenia SELECT musi odbywać się przy
zachowaniu zgodności typu wstawianych danych i typu odpowiednich kolumn.
" Brak listy kolumn w poleceniu INSERT i skojarzonym z nim poleceniu SELECT
wymaga istnienia wewnętrznej zgodności kolejności kolumn, określonej na etapie
definiowania tabel (przykład 3).
" Pominięcie kolumny na liście polecenia INSERT powoduje przypisanie wartości NULL
lub wartości określonej parametrem DEFAULT na etapie definiowania tabeli
(przykład 5).
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 30
MODYFIKACJA REKORDÓW - UPDATE
UPDATE tabela SET kol1 = wart1, kol2 = wart2,..., koln = wartn
[ WHERE warunek ]
UPDATE tabela1 SET kol1 = (SELECT koli FROM tabela2)
[ WHERE warunek ]
UPDATE tabela1 SET (kol1, kol2) = (SELECT koli, kolj FROM tabela2)
[ WHERE warunek ]
gdzie: tabela - nazwa tabeli, w której modyfikowane będą rekordy
kol1, kol2,..., koln - nazwy kolumn tabeli modyfikowanej
wart1,..., wartn - wartości podstawiane odpowiednio do kolumn kol1,..., koln
warunek - warunek, jaki muszą spełniać modyfikowane rekordy
Przykłady:
1/ UPDATE studenci SET Nazwisko = 'Nowak' WHERE Id_student = '0000049';
2/ UPDATE studenci st SET Nr_ECDL = Id_student WHERE 7 = ANY (SELECT COUNT(*)
FROM egzaminy e GROUP BY Id_student, Zdal HAVING e.Id_student = st.Id_student AND
Zdal = 'Y');
3/ UPDATE egzaminy SET Id_osrodek = (SELECT Id_osrodek FROM osrodki WHERE Nazwa_o
= 'WSPA' ) WHERE Id_osrodek IN (SELECT Id_osrodek FROM osrodki WHERE Nazwa_o =
'CKMP') AND Data_egz > '16-08-98';
Uwagi dotyczące modyfikacji rekordów:
" Wymagane posiadania prawa UPDATE do tabeli lub UPDATE ANY TABLE.
" Klauzula WHERE ogranicza ilość modyfikowanych rekordów.
" Polecenie UPDATE może zawierać zapytania zagniedżone lub skorelowane do
określania wartości wstawianych do kolumn.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 31
USUWANIE REKORDÓW - DELETE
DELETE FROM tabela [ WHERE warunek ]
gdzie: tabela - nazwa tabeli, z której usuwane będą rekordy
warunek - warunek, jaki muszą spełniać usuwane rekordy
Uwagi dotyczące usuwania rekordów:
" Wymagane posiadanie prawa DELETE do danej tabeli lub DELETE ANY TABLE.
" Klauzula WHERE ogranicza ilość usuwanych rekordów.
Przykład:
DELETE FROM studenci WHERE Nr_ECDL IS NOT NULL;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 32
PERSPEKTYWY
Perspektywa (ang. view):
" to obiekt podobny do okna dynamicznego, przez które użytkownicy mogą widzieć
daną tabelę
" wirtualna tabela tzn. nie istniejąca fizycznie w bazie danych (przechowywana jest
wyłącznie definicja perspektywy), przez użytkownika widziana jest jak "normalna"
tabela
" definiowana jest w oparciu o bazową tabelę lub inną perspektywę
" nie zawiera danych fizycznych, lecz operuje na danych z tabeli bazowej
Perspektywy definiuje się dla:
" ograniczenia dostępu do wszystkich danych tabeli; każdy użytkownik widzi tylko
część danych, które go interesują
" ułatwienia dostępu do danych w tabeli, poprzez uproszczenie zapytań
" uniknięcia konieczności znajomości całej struktury tabel w bazie danych
" umożliwienia widzenia informacji w różny sposób przez wielu użytkowników
" zapewnienia kontroli poprawności wprowadzanych i modyfikowanych danych
Rodzaje perspektyw:
1/ perspektywy proste - oparte wyłącznie na jednej tabeli bazowej i nie zawierające
w swojej definicji funkcji i klauzuli GROUP BY
2/ perspektywy złożone - oparte na kilku tabelach i/lub zawierające
w swojej definicji funkcje i klauzulę GROUP BY
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 33
TWORZENIE PERSPEKTYW - CREATE VIEW
CREATE [FORCE | NO FORCE] VIEW nazwa_perspektywy [kol1, kol2, ...]
AS SELECT zapytanie
CREATE VIEW nazwa_perspektywy [kol1, kol2, ..., alias_wyrażenia]
AS SELECT zapytanie WITH CHECK OPTION | WITH READ ONLY
CREATE OR REPLACE VIEW nazwa_perspektywy
AS SELECT zapytanie WITH CHECK OPTION CONSTRAINT nazwa_więzu
Uwagi dotyczące tworzenia perspektyw:
" Wymagane jest posiadanie praw CREATE VIEW lub CREATE ANY VIEW.
" Perspektywę można utworzyć niezależnie od faktu istnienia tabeli bazowej lub
posiadania praw do schematu danych użytkownika BD (klauzula FORCE).
" Definicja perspektywy może zawierać listę kolumn, których ilość musi być zgodna
z ilością kolumn na liście polecenia SELECT.
" Definicja perspektywy musi zawierać nazwy kolumn, jeśli lista polecenia SELECT
zawiera funkcje lub wyrażenia, dla których nie występują aliasy na tych pozycjach.
" Polecenie SELECT w definicji perspektywy nie może zawierać klauzuli ORDER BY.
" Definicja perspektywy może ograniczać rodzaje operacji wykonywanych na tabeli
bazowej lub innej perspektywie (WITH READ ONLY - tylko odczyt rekordów).
" Na etapie definicji perspektywy możliwe jest określenie warunku, jaki będą musiały
spełniać dane wprowadzane przez perspektywę do tabeli bazowej (klauzula WITH
CHECK OPTION); użycie CONSTRAINT pozwala nazwać powyższy warunek.
" Modyfikacja definicji perspektywy wymaga użycia klauzuli OR REPLACE.
" Zaleca się stosowanie listy kolumn w poleceniu SELECT definiującym perspektywę.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 34
Przykłady:
1/ CREATE VIEW student_form_we AS SELECT Id_student, Nazwisko, Imie, Data_ur, Miejsce,
PESEL, Kod, Miasto, Ulica, Numer FROM studenci;
2/ CREATE VIEW osr_lublin AS SELECT Id_osrodek, Nazwa_o, Miasto, Ulica, Numer FROM
osrodki WHERE Miasto = 'Lublin' WITH CHECK OPTION CONSTRAINT o_lublin;
3/ CREATE VIEW egzaminy_ecdl AS SELECT Nr_egz, Nazwisko, Imie, Nazwa_p FROM
egzaminy e, studenci st, przedmioty p WHERE e.Id_student = st.Id_student AND e.Id_przedmiot
= p.Id_przedmiot;
4/ CREATE VIEW l_egzamin (Id_egzaminator, l_egz) AS SELECT Id_egzaminator, COUNT(*)
FROM egzaminy GROUP BY Id_egzaminator;
5/ CREATE VIEW l_prob AS SELECT Id_student, COUNT(*) Proby FROM egzaminy GROUP
BY Id_student;
6/ CREATE VIEW egzaminy_lublin AS SELECT Nr_egz, Id_student, Id_przedmiot,
Id_egzaminator, Data_egz, Id_osrodek, Zdal FROM egzaminy WHERE Id_osrodek = ANY
(SELECT Id_osrodek FROM osrodki WHERE Miasto = 'Lublin') WITH CHECK OPTION;
USUWANIE PERSPEKTYW - DROP VIEW
DROP VIEW nazwa_perspektywy
Uwagi dotyczące usuwania perspektyw:
" Wymagane jest posiadanie praw do schematu danych użytkownika lub prawa
DROP ANY VIEW.
" Usunięcie perspektywy, na której oparto inne perspektywy nie powoduje
automatycznego ich usunięcią z BD; tracą one jednak sens.
Przykład:
DROP VIEW student_form_we;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 35
MODYFIKACJA TABEL POPRZEZ PERSPEKTYWY
1. Odbywa się poprzez zastosowanie poleceń INSERT, UPDATE i DELETE
2. Usuwanie rekordów przez perspektywę jest możliwe tylko wówczas, gdy:
" definicja perspektywy nie zawiera w instrukcji SELECT klauzul DISTINCT,
GROUP BY, HAVING
" modyfikowalna jest perspektywa, na której oparto definicję danej perspektywy
" perspektywa oparta jest tylko na jednej tabeli
" klauzula WHERE nie zawiera podzapytania skorelowanego
3. Modyfikacja rekordów możliwa jest, jeśli:
" spełnione są w/w warunki
" dane wprowadzane to tabeli poprzez perspektywę spełniają warunek podany
w definicji perspektywy, o ile użyto klauzuli WITH CHECK OPTION
" na liście polecenia SELECT nie występują wyrażenia, stałe lub funkcje grupowe
4. Wstawienie rekordów możliwe jest, jeśli:
" spełnione są w/w warunki
" definicja perspektywy zawiera kolumny, które muszą przyjmować wartości
różne od NULL
Przykłady:
1/ INSERT INTO student_form_we
VALUES ('0000049', 'Szafrańska', 'Agnieszka', '17-04-77', 'Lublin', '17047701583', '20145',
'Lublin', 'Daszyńskiego', '19/119');
2/ INSERT INTO osr_lublin VALUES (3, 'LBS', '20950', 'Lublin', 'Plac Wolności', '1');
3/ INSERT INTO egzaminy_lublin VALUES (10, '0000049', 1, '0036', '20-09-98', 3);
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 36
INDEKSY
Rodzaje indeksów:
" PRIMARY KEY - indeks podstawowy (jednoznaczna identyfikacja rekordów)
" UNIQUE - indeks unikalny (niepowtarzalność danych w kolumnie lub kolumnach)
" NON UNIQUE - indeks zwiększający szybkość dostępu do rekordów
Przypadki, w których powinno tworzyć się indeksy:
" przyspieszenie dostępu do danych w przypadku dużej liczby wierszy
" tworzenie indeksów dla kolumn, dla których powinno zapewnić się wartości
niepowtarzalne
" tworzenie indeksów dla kolumn często używanych w klauzulach WHERE,
szczególnie tych, które używane są jako kolumny łączące
Ograniczenia wynikające z istnienia indeksów:
" indeksy spowalniają wykonywanie operacji typu INSERT, UPDATE i DELETE
" jeśli to możliwe, tworzenie indeksu dla tabel powinno być wykonywane po
wstawieniu rekordów
" max liczba kolumn w definicji indeksu wynosi 16
" dana kombinacja kolumn może pojawić się w definicji indeksu tylko jeden raz
" każdy nowy indeks tabeli wydłuża czas przetwarzania danych
" wartości NULL nie są indeksowane
Sposoby tworzenia indeksów:
" na etapie definiowania tabeli (indeks typu PRIMARY KEY, UNIQUE)
" przy pomocy osobnych instrukcji (indeks typu UNIQUE, NON UNIQUE)
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 37
TWORZENIE INDEKSÓW - CREATE INDEX
CREATE [UNIQUE] INDEX nazwa_indeksu ON tabela (kol1,..., koln)
Uwagi dotyczące tworzenia indeksów:
" Utworzenie indeksu wymaga posiadania prawa własności danej tabeli lub prawa
typu INDEX do danej tabeli lub prawa CREATE ANY INDEX.
" Dodatkowo konieczna jest odpowiednia ilość przestrzeni BD lub posiadanie prawa
UNLIMITED TABLESPACE.
" Polecenie nie tworzy indeksu typu PRIMARY KEY.
Przykłady:
1/ CREATE INDEX i_osrodki_nazwa ON osrodek (nazwa_o);
2/ CREATE UNIQUE INDEX i_przedmiot_nazwa ON przedmiot (nazwa_p);
USUWANIE INDEKSÓW - DROP INDEX
DROP INDEX nazwa_indeksu
gdzie: nazwa_indeksu - nazwa indeksu, który zostanie usunięty
Przykład:
DROP INDEX i_osrodki_nazwa;
Uwaga:
Polecenie DROP INDEX pozwala usunąć tylko te indeksy, które zostały utworzone przy
pomocy instrukcji CREATE INDEX !!!
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 38
SEKWENCJE
Sekwencja - obiekt bazy danych, umożliwiający generowanie unikalnych
kolejnych wartości liczbowych
Sekwencje tworzone są w celu automatycznego generowania unikalnych wartości dla
kluczy typu PRIMARY KEY lub UNIQUE
Sekwencja składa się z dwóch tzw. pseudokolumn:
CURRVAL - informacja o bieżącym numerze przechowywanym w sekwencji
NEXTVAL - inkrementacja sekwencji i generowanie nowej wartości
Pseudokolumny wykorzystywane są:
" na liście kolumn instrukcji SELECT wyświetlającej informacje z tabel
" na liście kolumn instrukcji SELECT użytej w poleceniu INSERT
" jako element klauzuli VALUES polecenia INSERT
" jako element klauzuli SET w poleceniu UPDATE
Pseudokolumn nie wolno używać w:
" podzapytaniach występujących w instrukcjach SELECT, UPDATE, DELETE
" definicjach perspektyw
" poleceniu SELECT zawierającym operator DISTINCT, klauzule GROUP BY lub
ORDER BY
" operacji złączenia tabel za pomocą operatorów UNION, INTERSECT lub MINUS
" warunkach definiowanych po klauzuli WHERE
" definicji więzów integralności typu CHECK
" nadawaniu wartości domyślnych pól w poleceniach CREATE TABLE lub ALTER
TABLE
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 39
TWORZENIE SEKWENCJI - CREATE SEQUENCE
CREATE SEQUENCE nazwa_sekwencji
INCREMENT BY liczba_całkowita
START WITH liczba_całkowita
MAXVALUE liczba_całkowita | NOMAXVALUE
MINVALUE liczba_całkowita | NOMINVALUE
CYCLE | NOCYCLE
CACHE liczba_całkowita | NOCACHE
Uwagi dotyczące tworzenia sekwencji:
" Definicja sekwencji wymaga posiadania prawa CREATE SEQUENCE lub CREATE
ANY SEQUENCE.
" W definicji sekwencji można określić pierwszą generowaną liczbę (START WITH),
krok inkrementacji (INCREMENT BY), minimalną (MINVALUE) i maksymalną
generowaną wartość całkowitą (MAXVALUE), sposób zachowania się sekwencji w
momencie osiągnięcia wartości granicznej (CYCLE) oraz ilość kolejnych wartości
sekwencji przechowywanych w pamięci (CACHE).
Przykłady:
1/ CREATE SEQUENCE s_przedmioty
MINVALUE 1
MAXVALUE 7
INCREMENT BY 1
START WITH 1
NOCYCLE;
2/ CREATE SEQUENCE s_egzaminy
MINVALUE 1
NOMAXVALUE
INCREMENT BY 1
START WITH 1
NOCYCLE;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 40
WYKORZYSTANIE SEKWENCJI
Przykłady:
1/ INSERT INTO przedmioty VALUES (s_przedmioty.NEXTVAL, 'Edytory tekstu', NULL);
2/ SELECT s_przedmioty.CURRVAL, s_przedmioty.NEXTVAL, s_przedmioty.NEXTVAL FROM
sys.dual;
3/ UPDATE egzaminy SET Nr_egz = s_egzaminy.CURRVAL;
Uwagi dotyczące wykorzystania sekwencji:
" Dane sekwencja może być wykorzystywana jednocześnie przez wiele tabel i przez
wielu użytkowników.
" Każdemu użytkownikowi przydzielana jest niepowtarzalna wartość generowana
przez sekwencję.
" Przed pierwszym odwołaniem do CURRVAL należy wykonać odwołanie do
NEXTVAL.
" Odwołanie do NEXTVAL powoduje umieszczenie nowej wartości w CURRVAL.
" Wielokrotne odwołania do pseudokolumny NEXTVAL w tym samym poleceniu SQL
zwraca zawsze tą samą wartość.
" Wartości generowane przez sekwencje nie podlegają anulowaniu w sytuacji
wycofania transakcji.
" Jednoczesne występowanie odwołań do CURRVAL i NEXTVAL zawsze prowadzi do
wyświetlenia tych samych wartości, niezależnie od porządku odwołań.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 41
MODYFIKACJA SEKWENCJI - ALTER SEQUENCE
ALTER SEQUENCE nazwa_sekwencji
INCREMENT BY liczba_całkowita
MAXVALUE liczba_całkowita | NOMAXVALUE
MINVALUE liczba_całkowita | NOMINVALUE
CYCLE | NOCYCLE
CACHE liczba_całkowita | NOCACHE
Uwagi dotyczące modyfikacji sekwencji:
" Możliwa przy posiadaniu prawa ALTER w odniesieniu do danej sekwencji lub
ALTER ANY SEQUENCE.
" Dotyczy zmiany kroku inkrementacji (INCREMENT BY), maksymalnej
(MAXVALUE) i minimalnej (MINVALUE) wartości generowanej, liczby wartości
przechowywanych w pamięci (CACHE) oraz sposobu zachowania się w momencie
osiągnięcia wartości granicznej (CYCLE).
" Nie może prowadzić do wewnętrzych sprzeczności np. nowa wartość MAXVALUE
nie może być mniejsza od ostatnio wygenerowanej wartości.
" Nie wymusza ponownego generowania wartości od przyjętej wartości początkowej
(sekwencję należy usunąć i zdefiniować od nowa).
" Prowadzi do generowania nowych wartości tylko dla operacji zachodzących
w przyszłości.
Przykłady:
1/ ALTER SEQUENCE s_egzaminy INCREMENT BY 10;
2/ ALTER SEQUENCE s_przedmioty MAXVALUE 9;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 42
USUWANIE SEKWENCJI - DROP SEQUENCE
DROP SEQUENCE nazwa_sekwencji
Uwagi dotyczące usuwania sekwencji:
" Możliwe przy posiadaniu prawa ALTER ANY SEQUENCE lub prawa własności
sekwencji.
Przykład:
DROP SEQUENCE s_przedmioty;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 43
INNE OBIEKTY BD ORACLE
Obiekty BD ORACLE:
" funkcje - podprogramy, których rezultatem jest jedna wartość
" procedury - podprogramy, których rezultatem jest wiele wartości
" triggery - procedury obsługi zdarzeń
" pakiety - zbiory deklaracji i definicji funkcji, procedur, zmiennych, stałych,
kursorów, wyjątków i typów
DEFINIOWANIE FUNKCJI - CREATE FUNCTION
CREATE [OR REPLACE] FUNCTION nazwa_fun (arg [IN | OUT | IN OUT] typ)
RETURN typ_rezultat IS | AS polecenia_PL_SQL
Uwagi dotyczące definiowania funkcji:
" Wymagane jest posiadanie prawa systemowego CREATE PROCEDURE lub
CREATE ANY PROCEDURE.
" Definicja funkcji przechowywana jest w BD jako samodzielny obiekt.
" Funkcja jest zbiorem instrukcji języka PL/SQL.
" Możliwa jest redefinicja funkcji bez konieczności jej usuwania, ponownego
definiowania i przyznawania praw do niej poprzez użycie opcji OR REPLACE.
" Rodzaj argumentu funkcji określa się za pomocą opcji IN (argument wejściowy),
OUT (argument wyjściowy) lub IN OUT (argument typu wejście-wyjście).
" Definicja funkcji musi zawierać typ argumentu oraz typ rezultatu, jednak musi to
być nazwa tzw. typu bazowego np. NUMBER, VARCHAR2 zamiast NUMBER(7)
lub VARCHAR2(25).
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 44
Przykład:
CREATE FUNCTION zlicz_egz (k_osr IN NUMBER) RETURN NUMBER
IS
l_egz NUMBER(3);
BEGIN
SELECT COUNT(Id_osrodek) INTO l_egz FROM egzaminy WHERE Id_osrodek = k_osr;
RETURN l_egz;
END;
REKOMPILACJA FUNKCJI - ALTER FUNCTION
ALTER FUNCTION nazwa_fun COMPILE
Uwagi dotyczące rekompilacji funkcji:
" Możliwa do przeprowadzenia przez użytkownika, który utworzył funkcję lub
wymagane jest posiadanie prawa systemowego ALTER ANY PROCEDURE.
" Dotyczy funkcji, których definicja przechowywana jest w BD.
" Skrócenie czasu uruchamiania aplikacji.
" Pozwala uniknąć błędów wykonania na etapie uruchamiania aplikacji.
Przykład:
ALTER FUNCTION zlicz_egz COMPILE;
USUNI
CIE FUNKCJI - DROP FUNCTION
DROP FUNCTION nazwa_fun
Uwagi dotyczące usuwania funkcji:
" Możliwa do przeprowadzenia przez użytkownika, który utworzył funkcję lub
wymagane jest posiadanie prawa systemowego DROP ANY PROCEDURE.
" Usuwanie dotyczy funkcji, będących obiektami BD.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 45
DEFINIOWANIE PROCEDURY - CREATE PROCEDURE
CREATE [OR REPLACE] PROCEDURE nazwa_proc
(arg [IN | OUT | IN OUT] typ) IS | AS polecenia_PL_SQL
Uwagi dotyczące definiowania procedury:
" Wymagane jest posiadanie prawa systemowego CREATE PROCEDURE we własnym
schemacie lub CREATE ANY PROCEDURE.
" Definicja procedury przechowywana jest w BD jako samodzielny obiekt.
" Procedura jest zbiorem instrukcji języka PL/SQL.
" Możliwa jest redefinicja procedury bez konieczności jej usuwania, ponownego
definiowania i przyznawania praw do niej poprzez użycie opcji OR REPLACE
(wymagane jest posiadanie prawa systemowego REPLACE ANY PROCEDURE).
Przykład:
CREATE PROCEDURE akt_ECDL (k_EKUK IN VARCHAR2) AS BEGIN
UPDATE studenci SET Nr_ECDL = Id_student WHERE Id_student = k_EKUK;
END;
REKOMPILACJA PROCEDUR - ALTER PROCEDURE
ALTER PROCEDURE nazwa_proc COMPILE
Uwagi dotyczące rekompilacji procedur:
" Możliwa do przeprowadzenia przez użytkownika, który utworzył procedurę lub
wymagane jest posiadanie prawa systemowego ALTER ANY PROCEDURE.
" Dotyczy procedur, których definicja przechowywana jest w BD.
" ORACLE przeprowadza jednocześnie kontrolę wszystkich innych obiektów BD,
które wykorzystują kompilowaną procedurę.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 46
USUNI
CIE PROCEDURY - DROP PROCEDURE
DROP PROCEDURE nazwa_proc
Uwagi dotyczące usuwania procedur:
" Możliwa do przeprowadzenia przez użytkownika, który utworzył procedurę lub
wymagane jest posiadanie prawa systemowego DROP ANY PROCEDURE.
" Usuwanie dotyczy procedur, będących obiektami BD.
DEFINIOWANIE TRIGGERÓW - CREATE TRIGGER
CREATE [OR REPLACE] TRIGGER nazwa_triggera BEFORE | AFTER
DELETE | [OR] INSERT | [OR] UPDATE [OF COLUMN kol1, kol2,...]
ON tabela [FOR EACH ROW [WHEN warunek]] polecenia_PL_SQL
Uwagi dotyczące tworzenia triggerów:
" Wymagane jest posiadanie prawa systemowego CREATE TRIGGER we własnym
schemacie lub CREATE ANY TRIGGER.
" Triggery mogą być skojarzone z operacjami usuwania (DELETE), wstawiania
(INSERT), modyfikowania rekordów (UPDATE) lub poszczególnych kolumn
(UPDATE OF) we wskazanej tabeli (ON tabela).
" Klauzule BEFORE i AFTER wskazują moment uruchomienia triggera.
" Wykonanie instrukcji triggera wymaga posiadania praw do tych operacji.
" Możliwa jest redefinicja triggerów (OR REPLACE) bez konieczności ich usuwania.
" Trigger skojarzony z operacją jest uruchamiany tylko jeden raz; możliwe jest także
wykonanie instrukcji triggera dla każdego rekordu (FOR EACH ROW), który
dodatkowo spełnia podany warunek (WHEN warunek).
" Aktywacja triggera następuje w momencie jego definicji, możliwa jest deaktywacja
triggera w instrukcji ALTER TRIGGER lub ALTER TABLE.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 47
TYPY TRIGGERÓW
FOR EACH ROW
BEFORE Trigger uruchamiany raz przed Trigger uruchamiany przed wystąpieniem
wystąpieniem zdarzenia zdarzenia dla każdego rekordu
AFTER Trigger uruchamiany raz po Trigger uruchamiany po wystąpieniem
wystąpieniu zdarzenia zdarzenia dla każdego rekordu
Przykłady:
1/ CREATE TRIGGER mod_egzaminy
BEFORE DELETE OR UPDATE OR INSERT ON egzaminy
BEGIN
IF (TO_CHAR(SYSDATE, 'DY') = 'SAT' OR (TO_CHAR(SYSDATE, 'DY') = 'SUN' THEN
raise_application_error(-20501, 'Wprowadzanie danych w weekend zabronione');
END IF;
IF (TO_CHAR(SYSDATE, 'HH24') < 8 OR (TO_CHAR(SYSDATE, 'HH24') >= 16 THEN
raise_application_error(-20502, 'Wprowadzanie danych po godzinach pracy zabronione');
END IF;
END;
2/ CREATE TRIGGER mod_egzaminy
BEFORE INSERT ON egzaminy FOR EACH ROW
BEGIN
UPDATE egzaminy SET :new.Nr_egz = s_egzaminy.NEXTVAL;
END;
3/ CREATE TRIGGER kontr_przedmioty
BEFORE UPDATE ON przedmioty FOR EACH ROW
DECLARE
max_mod NUMBER(1);
min_mod NUMBER(1);
BEGIN
SELECT MAX(Id_przedmiot) MIN(Id_przedmiot) INTO max_mod min_mod
FROM przedmioty;
IF (:new.Id_przedmiot > max_mod OR :new.Id_przedmiot < min_mod) THEN
raise_application_error(-20502, 'Błędny numer modulu ');
END IF ;
END;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 48
MODYFIKACJA TRIGGERÓW - ALTER TRIGGER
ALTER TRIGGER nazwa_triggera ENABLE | DISABLE
Uwagi dotyczące modyfikacji triggerów:
" Dotyczy wyłącznie aktywacji i deaktywacji triggerów.
" Możliwa do przeprowadzenia przez użytkownika, który utworzył trigger lub
wymagane jest posiadanie prawa systemowego ALTER ANY TRIGGER.
" Możliwa także przy pomocy polecenia ALTER TABLE.
Przykład:
ALTER TRIGGER mod_egzaminy DISABLE;
USUNI
CIE TRIGGERÓW - DROP TRIGGER
DROP TRIGGER nazwa_triggera
Uwagi dotyczące usuwania triggerów:
" Możliwa do przeprowadzenia przez użytkownika, który utworzył trigger lub
wymagane jest posiadanie prawa systemowego DROP ANY TRIGGER.
" Usuwanie dotyczy triggerów, będących obiektami BD.
DEFINIOWANIE PAKIETÓW
Definicja pakietu obejmuje:
" specyfikację pakietu - deklaracja wszystkich obiektów wchodzących w skład pakietu
" definicję wszystkich obiektów wymienionych w specyfikacji pakietu
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 49
SPECYFIKACJA PAKIETÓW- CREATE PACKAGE
CREATE [OR REPLACE] PACKAGE nazwa_pakietu
IS | AS polecenia_PL_SQL
Uwagi dotyczące definiowania specyfikacji pakietu:
" Wymagane jest posiadanie prawa systemowego CREATE PROCEDURE we
własnym schemacie lub CREATE ANY PROCEDURE.
" Specyfikacja pakietu przechowywana jest w BD jako samodzielny obiekt.
" Deklaracje obiektów są zbiorem instrukcji języka PL/SQL.
" Możliwa jest redefinicja specyfikacji bez konieczności jej usuwania, ponownego
definiowania i przyznawania praw do obiektów poprzez użycie opcji OR REPLACE.
" Obiekty zadeklarowane w specyfikacji są typu public; można do nich odwoływać się
na zewnątrz pakietu lub z obiektów wchodzących w skład pakietu.
Przykład:
CREATE PACKAGE osrodki_akt AS
PROCEDURE usun_osrodek(nrosr NUMBER);
FUNCTION dod_osrodek RETURN NUMBER;
PROCEDURE mod_osrodek(nrosr NUMBER);
END osrodek_akt;
DEFINIOWANIE OBIEKTÓW PAKIETU- CREATE PACKAGE BODY
CREATE [OR REPLACE] PACKAGE BODY nazwa_pakietu IS | AS
polecenia_PL_SQL
Uwagi dotyczące definiowania obiektów pakietu:
" Wymagane jest posiadanie prawa systemowego CREATE PROCEDURE we własnym
schemacie lub CREATE ANY PROCEDURE.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 50
" Definicja obiektów pakietu przechowywana jest w BD jako samodzielny obiekt.
" Należy zdefiniować wszystkie obiekty zadeklarowane w specyfikacji pakietu.
" Definicje obiektów pakietu są zbiorem instrukcji języka PL/SQL.
" Redefinicja obiektów pakietu bez konieczności usuwania, ponownego definiowania
i przyznawania praw wymaga użycia opcji OR REPLACE.
" Obiekty zadeklarowane wewnątrz pakietu są typu private; można do nich odwoływać
się tylko z innych obiektów pakietu.
Przykład:
CREATE PACKAGE BODY osrodki_akt AS
PROCEDURE usun_osr (nrosr NUMBER) IS
BEGIN
DELETE FROM osrodki WHERE osrodki.Id_osrodek = nrosr;
END;
FUNCTION dod_osr (nazwa VARCHAR2) RETURN NUMBER IS
num NUMBER(5);
BEGIN
SELECT s_osrodki.NEXTVAL INTO num FROM sys.dual;
INSERT INTO osrodki VALUES (num, nazwa, NULL, NULL, NULL, NULL);
END;
PROCEDURE mod_osr (nrosr NUMBER, Kod VARCHAR2, Miasto VARCHAR2) IS
BEGIN
UPDATE osrodki SET (Kod = mod_osr.kod, Miasto = mod_osr.Miasto)
WHERE osrodek.Id_osrodek = mod_osr.nrosr;
END;
END osrodek_akt;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 51
REKOMPILACJA PAKIETU- ALTER PACKAGE
ALTER PACKAGE nazwa_pakietu COMPILE [PACKAGE | BODY]
Uwagi dotyczące rekompilacji pakietu:
" Wymagane jest posiadanie prawa systemowego ALTER ANY PROCEDURE lub
prawa własności pakietu.
" Rekompilacja pakietu może dotyczyć jego specyfikacji i definicji jego obiektów
(COMPILE PACKAGE) lub tylko definicji obiektów (COMPILE BODY).
" Rekompilacja dotyczy wszystkich obiektów równocześnie; nie jest możliwe użycie
instrukcji ALTER FUNCTION lub ALTER PROCEDURE do rekompilacji
pojedycznych funkcji lub procedur wchodzących w skład pakietu.
" Rekompilacja specyfikacji pakietu (COMPILE PACKAGE) odbywa się niezależnie
od rekompilacji definicji obiektów.
" Rekompilacja definicji obiektów pakietu poprzedzona jest kompilacją innych
obiektów, od których zależą obiekty pakietu.
" Błąd rekompilacji specyfikacji pakietu unieważnia odwołania do obiektów pakietu
występujące w innych obiektach zewnętrznych.
ZALETY STOSOWANIA PAKIETÓW
" przejrzysta organizacja struktury kodu z
ródłowego aplikacji
" efektywne nadawanie praw do zbioru obiektów
" prosta modyfikacja definicji obiektów pakietu
" duża szybkość wykonywania funkcji i procedur, będących obiektami pakietu
" możliwość definiowania zmiennych globalnych, wykorzystywanych w obiektach
pakietu
" możliwość definiowania różnych obiektów o tych samych nazwach
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 52
FUNKCJE J
ZYKA SQL
Rodzaje funkcji:
1. Funkcje skalarne lub operujące na jednym rekordzie danych:
" numeryczne
" znakowe
" daty
" konwersji
2. Funkcje agregujące lub operujące na zbiorach rekordów danych:
" grupowe
" inne
FUNKCJE NUMERYCZNE
Funkcje, których argumentami i rezultatem są wartości numeryczne.
Nazwa funkcji Opis
ABS(n) wartość bezwzględna z n
CEIL(n) najmniejsza liczba całkowita większa lub równa n
COS(n) cosinus z n (w radianach)
COSH(n) cosinus hiperboliczny z n
EXP(n) e do potęgi n (e = 2.71828183)
FLOOR(n) największa liczba całkowita mniejsza lub równa n
LN(n) logarytm naturalny z n
LOG(m, n) logarytm przy podstawie m z n
MOD(m,n) reszta z dzielenia m przez n
POWER(m,n) m do potęgi n
ROUND(n,[m]) wartość n zaokrąglona do m miejsc dziesiętnych
SIN(n) sinus z n (w radianach)
SINH(n) sinus hiperboliczny z n
SQRT(n) pierwiastek kwadratowy
TAN(n) tangens z n (w radianach)
TANH(n) tangens hiperboliczny
TRUNC(n,[m]) wartość n zawierająca m miejsc dziesiętnych (bez zaokrąglania)
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 53
FUNKCJE ZNAKOWE
Funkcje, których argumentami są ciągi znaków, a rezultatem są wartości numeryczne lub ciągi
znaków.
Nazwa funkcji Opis
Funkcje o rezultatach znakowych
CHR(n) Wyświetla znak o podanym kodzie n
CONCAT(cz1, cz2) Konkatenacja ciągów znaków cz1 i cz2
INITCAP(cz) Zamiana pierwszych litery wyrazów ciągu na
duże litery
LOWER(cz) Zamiana znaków w ciągu na małe litery
LPAD(cz1, n [, cz2]) Uzupełnienie z lewej strony ciągu znaków cz1
o ciąg cz2 do całkowitej długości n znaków
LTRIM(cz1 [, cz2]) Usunięcie z lewej strony w ciągu znaków cz1
znaków określonych przez cz2
REPLACE(cz1, cz2, [, cz3]) Zastąpienie w ciągu znaków cz1, ciągu cz2
poprzez ciąg znaków cz3
RPAD(cz1, n [,cz2]) Uzupełnienie z prawej strony ciągu znaków cz1
o ciąg cz2 do całkowitej długości n znaków
RTRIM(cz1 [,cz2]) Usunięcie z prawej strony w ciągu znaków cz1
znaków określonych przez cz2
SUBSTR(cz, m [,n]) Wycięcie z ciągu znaków cz podciągu znaków
zaczynającego się od pozycji m; n - liczba znaków
wycinanych
UPPER(cz) Zamiana znaków w ciągu na duże litery
Funkcje o rezultatach numerycznych
ASCII(znak) Podaje kod ASCII wskazanego znaku
INSTR(cz1, cz2 [,n [,m]]) Przeszukanie ciągu znaków cz1, począwszy od
pozycji n w celu znalezienia m-tego wystąpienia
ciągu cz2
LENGTH(cz) Długość ciągu znaków
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 54
FUNKCJE DATY
Funkcje, których argumenty i rezultaty są typu daty.
Nazwa funkcji Opis
Funkcje o rezultatach typu daty
ADD_MONTHS(d, n) Dodanie do miesiąca daty d liczby miesięcy określonej
przez n
LAST_DAY(d) Wyznaczenie ostatniego dnia miesiąca określonego
w dacie d
NEW_TIME(d, s1, s2) Wyznaczenie czasu i daty w strefie czasowej s2
odpowiadającego czasowi i dacie d w strefie s1
NEXT_DAY(d, cz) Wyznaczenie najbliższej daty dnia, którego nazwę
podano w ciągu znaków cz w stosunku do daty d
ROUND(d [,fmt]) Wyznaczenie zaokrąglenia daty d w oparciu o format
określony przez fmt np. do początku roku
SYSDATE Wyznaczenie daty systemowej
TRUNC(d [, fmt]) Wycięcie z daty d informacji według formatu fmt.
Funkcje o rezultatach numerycznych
MONTHS_BETWEEN(d1, d2) Różnica między datami d1 i d2 wyrażona w miesiącach
FUNKCJE KONWERSJI
Funkcje dokonujące konwersji danych między różnymi typami danych.
Nazwa funkcji Opis
CHARTOROWID(ciąg) Konwersja typu VARCHAR2 lub CHAR na typ ROWID
TO_CHAR(d [,fmt]) Konwersja daty d do postaci ciągu znaków wg formatu fmt
TO_CHAR(n [,fmt]) Konwersja danych numerycznych n do postaci ciągu znaków
wg podanego formatu fmt
TO_DATE(ciąg [,fmt]) Konwersja ciągu znaków ciąg do postaci daty wg formatu fmt
TO_NUMBER(ciąg [,fmt]) Konwersja ciągu znaków ciąg do postaci danej numerycznej
wg formatu fmt
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 55
FUNKCJE GRUPOWE
Funkcje umożliwiające wykonanie operacji matematycznych na grupach rekordów.
Nazwa funkcji Opis
AVG(DISTINCT|ALL n) Wyznaczenie wartości średniej z n;
n - wyrażenie lub nazwa kolumny
COUNT(*| [DISTINCT|ALL] wyr) Zliczenie ilości rekordów; wyr - nazwa kolumny lub
wyrażenie
MAX([DISTINCT|ALL] wyr) Wartość maksymalna z wyrażenia wyr
MIN([DISTINCT|ALL] wyr) Wartość minimalna z wyrażenia wyr
STDDEV([DISTINCT|ALL] x) Standardowe odchylenie od x
SUM([DISTINCT|ALL] n) Suma wartości n
VARIANCE([DISTINCT|ALL] x) Wariancja z x
INNE FUNKCJE
Funkcje umożliwiające wykonanie operacji matematycznych na grupach rekordów.
Nazwa funkcji Opis
NVL(wyr1, wyr2) Funkcja zwraca wartość wyrażenia wyr1, jeśli jest ono
różne od NULL; jeśli wyr1 = NULL to funkcja zwraca
wartość wyr2
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 56
ZARZ
DZANIE TRANSAKCJAMI
Transakcja to zbiór akcji, wykonujących się wszystkie albo żadna.
" transakcja rozpoczyna się wraz z pierwszą instrukcją DML lub DDL modyfikującą
BD
" transakcja kończy się w sposób jawny w momencie zatwierdzenia zmian (COMMIT),
ich wycofania (ROLLBACK) lub odłączenia się od BD
" transakcja zatwierdzana jest w sposób niejawny przed i po zakończeniu każdej
instrukcji DDL
" przebieg transakcji można oznaczać tzw. punktami zachowania (SAVEPOINT)
ZATWIERDZANIE TRANSAKCJI - COMMIT
COMMIT [WORK]
Przykład:
INSERT INTO przedmiotyVALUES (2, 'Użytkowanie komputerów');
COMMIT;
Uwagi dotyczące zatwierdzania transakcji:
" Wydanie polecenia nie wymaga posiadania żadnych praw.
" Zatwierdzenie transakcji powoduje usunięcie wszystkich punktów zachowania
i zwalnia założone przez transakcję blokady oraz utrwala w BD zmiany dokonane
przez użytkownika.
" Zaleca się używanie polecenia COMMIT do jawnego kończenia transakcji.
" Każda niezatwierdzona transakcja w sytuacji awaryjnej zostanie wycofana przez
SZBD.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 57
WYCOFANIE TRANSAKCJI - ROLLBACK
ROLLBACK [WORK] [TO punkt_zachowania]
Przykład:
BEGIN
SAVEPOINT ins_mod_1;
INSERT INTO modul VALUES (2, 'Użytkowanie komputerów');
SAVEPOINT ins_mod_2;
INSERT INTO modul VALUES (4, 'Edytory tekstu');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK TO ins_mod_1;
END;
Uwagi dotyczące wycofania transakcji:
" Wydanie polecenia nie wymaga posiadania żadnych praw.
" Polecenie służy do wycofania akcji przeprowadzonych w ramach transakcji.
" Użycie ROLLBACK powoduje zakończenie transakcji, anulowanie wszystkich zmian
przeprowadzonych przez transakcję, usunięcie wszystkich punktów zachowania
i zwolnienie blokad założonych przez transakcję.
" Użycie ROLLBACK TO punkt_zachowania powoduje anulowanie tylko tych zmian,
które zostały wprowadzone po punkcie zachowania, usunięcie wszystkich punktów
zachowania zdefiniowanych po danym punkcie oraz zwolnienie blokad, które
wystąpiły do danego punktu zachowania; blokady założone po danym punkcie
zostaną zwolnione po zakończeniu lub wycofaniu transakcji.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 58
PUNKTY ZACHOWANIA TRANSAKCJI - SAVEPOINT TO
SAVEPOINT punkt_zachowania
Uwagi dotyczące definiowania punktów zachowania transakcji:
" Wydanie polecenia nie wymaga posiadania żadnych praw.
" Punkty zachowania pozwalają elastycznie sterować przebiegiem transakcji.
" Nazwa punktu zachowania może powtarzać się wewnątrz transakcji; dotychczasowy
punkt o tej samej nazwie zostaje przesunięty do nowego miejsca.
" Maksymalna liczba punktów zachowania dla użytkownika - 255; domyślnie  5.
DEFINIOWANIE TRANSAKCJI - SET TRANSACTION
SET TRANSACTION [READ ONLY] | [READ WRITE]
Przykład:
SET TRANSACTION READ ONLY;
SELECT * FROM egzaminatorzy;
COMMIT;
Uwagi dotyczące definiowania transakcji:
" Jawny sposób rozpoczęcia transakcji (instrukcja musi wystąpić jako pierwsza).
" Pozwala uzyskać spójne dane z wielu tabel.
" Transakcja READ ONLY umożliwia wykonywanie tylko polecenia SELECT i SET
ROLE a jest zatwierdzana poleceniem COMMIT lub poleceniem DDL.
" Dla transakcji READ ONLY użytkownik dysponuje obrazem BD jaki istniał
momencie wykonania polecenia SET TRANSACTION.
" Klauzula READ WRITE umożliwia wykonywanie innych operacji DML.
" Inni użytkownicy BD mogą wykonywać swoje transakcje wybierania lub
modyfikowania bez żadnych przeszkód.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 59
ADMINISTRACJA BAZ
DANYCH
W SZRBD ORACLE
" Zarządzanie prawami dostępu do obiektów BD
" Zarządzanie prawami systemowymi
" Zarządzanie rolami
SA
OWNIK BAZY DANYCH
Słownik Bazy danych zawiera informacje o:
" obiektach, których właścicielem jest dany użytkownik (perspektywa USER_xxx)
" obiektach, do których użytkownik posiada prawa dostępu i których jest właścicielem
(perspektywa ALL_xxx)
" wszystkich tabelach i perspektywach dostępnych dla użytkownika (perspektywa
DICTIONARY)
" kolumnach definiujących obiekty dostępne dla użytkownika (perspektywa
DICT_COLUMNS)
" definicjach wszystkich więzów integralności dostępnych dla użytkownika
(perspektywa CONSTRAINT_DEFS)
" opisach kolumn wchodzących w skład definicji więzów integralności i dostępnych dla
użytkownika (perspektywa CONSTRAINT_COLUMNS)
LISTA PERSPEKTYW TYPU ALL_XXX
PERSPEKTYWA OPIS
Lista tabel, perspektyw, sekwencji dostępnych dla
ALL_CATALOG
użytkownika
ALL_CONSTRAINTS
Więzy integralności tabel dostępnych dla użytkownika
ALL_INDEXES
Lista indeksów tabel dostępnych dla użytkownika
ALL_OBJECTS Lista obiektów dostępnych dla użytkownika
ALL_SEQUENCES
Opis sekwencji dostępnych dla użytkownika
ALL_TABLES
Opisy tabel dostępnych dla użytkownika
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 60
LISTA PERSPEKTYW TYPU USER_XXX
PERSPEKTYWA OPIS
USER_CATALOG Lista tabel, perspektyw, sekwencji, których właścicielem jest
użytkownik
USER_INDEXES Opisy indeksów utworzonych przez użytkownika
USER_OBJECTS Lista obiektów utworzonych przez użytkownika
USER_SEQUENCES Opisy sekwencji zdefiniowanych przez użytkownika
USER_TAB_COLUMNS Definicje kolumn tabel i perspektyw utworzonych przez
użytkownika
USER_TABLES Opisy tabel, których właścicielem jest użytkownik
RODZAJE PRAW DOST
PU DO BAZY DANYCH
" Prawa do wykonywania określonych operacji w ramach danego zadania
" Prawa do poszczególnych obiektów i operacji wykonywanych na tych obiektach
" Prawa systemowe (nadrzędne w stosunku do praw do obiektów)
SPOSOBY NADAWANIA PRAW
" nadawanie pojedynczych praw do obiektów i operacji wykonywanych na nich
" nadawanie zbioru praw do obiektów lub operacji
Rola - zbiór praw i/lub innych ról, umożliwiających elastyczne administrowanie
systemem uprzywilejowań w bazie danych
Rola może składać się z:
" pojedynczych praw do obiektów i operacji
" pojedynczych praw systemowych
" innych ról
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 61
TWORZENIE ROLI - CREATE ROLE
CREATE ROLE nazwa_roli [ NOT IDENTIFIED | IDENTIFIED BY password ]
Przykłady:
1/ CREATE ROLE r_dane_we_ecdl ;
2/ CREATE ROLE r_egzamin_we IDENTIFIED BY ECDL ;
Uwagi dotyczące tworzenia ról:
" Zdefiniowanie roli wymaga posiadania prawa systemowego CREATE ROLE.
" Rola utworzona poleceniem CREATE ROLE nie zawiera żadnych praw; konieczne
jest użycie polecenia GRANT w celu zdefiniowania praw wchodzących w skład roli.
" Użytkownik definiujący role staje się automatycznie jej administratorem i otrzymuje
od SZRBD rolę ADMIN OPTION.
" Własność ADMIN OPTION pozwala wykonywać następujące operacje: przydzielać
daną rolę innym użytkownikom lub rolom, odwoływać uprzednio przyznaną rolę,
zmieniać własności roli, usuwać rolę.
" Na etapie tworzenia roli można określić, czy użytkownik, który dokona aktywacji roli
powinien być identyfikowany przez hasło (IDENTIFIED BY) czy też nie (NOT
IDENTIFIED - domyśle ustawienie).
" Istnieją predefiniowane role: CONNECT, RESOURCE, DBA,
EXP_FULL_DATABASE, IMP_FULL_DATABASE.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 62
MODYFIKACJA ROLI - ALTER ROLE
ALTER ROLE nazwa_roli NOT IDENTIFIED | IDENTIFIED BY password
Przykład:
ALTER ROLE r_dane_we_ecdl IDENTIFIED BY ecdl ;
Uwagi dotyczące modyfikacji ról:
" Wymaga posiadania prawa systemowego ALTER ANY ROLE lub uprawnienia
ADMIN OPTION.
" Dotyczy zmiany sposobu autoryzacji użytkownika w momecie aktywacji roli.
AKTYWACJA ROLI - SET ROLE
SET ROLE rola IDENTIFIED BY password | ALL [EXCEPT rola1,...] | NONE
Przykład:
SET ROLE r_dane_we_ecdl IDENTIFIED BY ecdl ;
Uwagi dotyczące aktywacji ról:
" Aktywacja roli wymaga uprzedniego przyznania roli od jej właściciela.
" Przyznane role mogą być aktywowane lub deaktywowane (NONE) w bieżącej sesji
pracy przy pomocy polecenia SET ROLE.
" Aktywacja może dotyczyć wszystkich ról tj. przyznanych bezpośrednio przez ich
właścicieli lub ról wchodzących w skład innych ról.
" Role wymienione w opcji EXCEPT muszą być rolami przyznanymi bezpośrednio
przez ich właścicieli.
" Aktywacja ról przy pomocy opcji ALL nie jest możliwa, jeśli dla ról zdefiniowano
hasła.
" Istnieją ograniczenia na maksymalną liczbę aktywnych ról w bieżącej sesji pracy.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 63
USUWANIE ROLI - DROP ROLE
DROP ROLE nazwa_roli
Przykład:
DROP ROLE r_dane_we_ecdl ;
Uwagi dotyczące usuwania ról:
" Polecenie usuwa z bazy danych definicję roli.
" Rola może zostać usunięta przez jej właściciela lub wymagane jest posiadanie prawa
systemowego DROP ANY ROLE.
" Wykonanie polecenia DROP ROLE powoduje usunięcie danej roli z definicji innych
ról oraz anulowanie praw użytkowników do tej roli.
NADAWANIE PRAW DO OBIEKTÓW BAZY DANYCH - GRANT
GRANT ALL [PRIVILEGES] |
[ALTER], [INDEX], [REFERENCES], [SELECT], [INSERT], [EXECUTE],
[DELETE], [UPDATE] [(lista_kolumn)]
ON tabela| obiekt TO lista_użytkowników | lista_ról | PUBLIC
[WITH GRANT OPTION]
Przykłady:
1/ GRANT ALL ON egzaminy TO marekm WITH GRANT OPTION;
2/ GRANT SELECT, UPDATE, INSERT ON egzaminy TO elam;
3/ GRANT INSERT ON r_dane_we_ecdl TO agas;
4/ GRANT ALTER, INDEX, REFERENCES ON egzaminatorzy TO piotrm;
5/ GRANT UPDATE (Nazwa_p, Opis), REFERENCES (Id_przedmiot) ON przedmiotyTO alak;
6/ GRANT SELECT, UPDATE ON osrodki TO PUBLIC;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 64
Uwagi dotyczące nadawania praw użytkownikom i rolom do obiektów:
" Polecenie dotyczy nadawania i przekazywania (WITH GRANT OPTION) praw do
następujących obiektów: tabel, perspektyw, sekwencji, procedur, funkcji, pakietów
oraz definiowania praw wchodzących w skład ról.
" Polecenie nadaje wszystkim (PUBLIC) lub wybranym użytkownikom
(lista_użytkowników) i rolom (lista_ról) prawa do poszczególnych obiektów oraz do
wykonywania operacji na tych obiektach.
" Użytkownik może nadawać prawa tylko do obiektów, których jest właścicielem lub
jeśli otrzymał prawo z opcją WITH GRANT OPTION.
" Prawa mogą dotyczyć pojedynczych kolumn (lista_kolumn) lub całych tabel
i perspektyw i związane są z wykonywaniem operacji typu INSERT, UPDATE lub
opcją REFERENCES.
" Użytkownik może korzystać z nadanego prawa lub roli natychmiast po ich
przyznaniu.
LISTA PRAW DO OBIEKTÓW
PRAWO TABELE PERSPEKTYWY SEKWENCJE PROCEDURY,
FUNKCJE
+ +
ALTER
+ +
DELETE
+
EXECUTE
+
INDEX
+ +
INSERT
+
REFERENCES
+ + +
SELECT
+ +
UPDATE
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 65
NADAWANIE PRAW SYSTEMOWYCH - GRANT
GRANT prawo_sys | rola_sys TO lista_użytkowników | lista_ról | PUBLIC
[WITH ADMIN OPTION]
Przykłady:
1/ GRANT CREATE TABLE, CREATE VIEW TO alak ;
2/ GRANT r_dane_we_ecdl TO r_egzamin_we ;
3/ GRANT r_dane_we_ecdl TO agas WITH ADMIN OPTION ;
Uwagi dotyczące nadawania praw systemowych i definiowania ról:
" Polecenie dotyczy nadawania i przekazywania (WITH ADMIN OPTION) praw
systemowych i praw do ról użytkownikom oraz definiowania ról za pomocą innych
ról.
" Wymagane jest posiadanie odpowiedniego prawa z opcją WITH ADMIN OPTION
lub prawa GRANT ANY PRIVILEGE.
" Przydzielanie roli możliwe jest przez jej właściciela bądz
konieczne jest posiadanie
prawa do roli z opcją WITH ADMIN OPTION lub prawa GRANT ANY ROLE.
" Nie jest możliwe wprowadzenie cyklicznych praw dla ról.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 66
LISTA WYBRANYCH PRAW SYSTEMOWYCH
PRAWO OPIS
Modyfikacja indeksów w dowolnym schemacie
ALTER ANY INDEX
Modyfikacja ról w BD
ALTER ANY ROLE
Modyfikacja sekwencji w BD
ALTER ANY SEQUENCE
Modyfikacja tabel lub perspektyw
ALTER ANY TABLE
Definiowanie komentarzy tabeli, perspektywy lub kolumny
COMMENT ANY TABLE
w dowolnym schemacie
Tworzenie indeksów dla dowolnej tabeli w dowolnym
CREATE ANY INDEX
schemacie
Tworzenie sekwencji w dowolnym schemacie
CREATE ANY SEQUENCE
Tworzenie tabel w dowolnym schemacie
CREATE ANY TABLE
Tworzenie perspektywyw dowolnym schemacie
CREATE ANY VIEW
Tworzenie ról
CREATE ROLE
Tworzenie sekwencji we własnym schemacie
CREATE SEQUENCE
Tworzenie tabel we własnym schemacie
CREATE TABLE
Tworzenie perspektyw we własnym schemacie
CREATE VIEW
Usuwanie rekordów w tabeli lub perspektywie w dowolnym
DELETE ANY TABLE
schemacie
Usuwanie indeksów w dowolnym schemacie
DROP ANY INDEX
Usuwanie ról
DROP ANY ROLE
Usuwanie sekwencji w dowolnym schemacie
DROP ANY SEQUENCE
Usuwanie tabel w dowolnym schemacie
DROP ANY TABLE
Usuwanie perspektyw w dowolnym schemacie
DROP ANY VIEW
Prawo wykonywania procedur, funkcji zdefiniowanych w BD
EXECUTE ANY
i pakietach oraz odwoływania się do obiektów typu public
PROCEDURE
w pakietach
Nadawanie dowolnych praw systemowych
GRANT ANY PRIVILEGE
Nadawanie dowolnej roli z BD
GRANT ANY ROLE
Wstawianie rekordów do tabel i perspektyw w dowolnym
INSERT ANY TABLE
schemacie
Odwoływanie się do sekwencji w dowolnym schemacie
SELECT ANY SEQUENCE
Odwoływanie się do tabel i perspektyw w dowolnym
SELECT ANY TABLE
schemacie
Wykorzystywanie nieograniczonego obszaru pamięci BD
UNLIMITED TABLESPACE
Modyfikacja rekordów w tabelach i perspektywach
UPDATE ANY TABLE
w dowolnym schemacie
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 67
LISTA RÓL SYSTEMOWYCH
ROLA PRAWA I ROLE
CONNECT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE
LINK, CREATE SEQUENCE, CREATE SESSION, CREATE
SYNONYM, CREATE TABLE, CREATE VIEW
RESOURCE CREATE CLUSTER, CREATE PROCEDURE, CREATE
SEQUENCE, CREATE TABLE, CREATE TRIGGER
DBA wszystkie prawa systemowe z opcją WITH ADMIN OPTION,
role EXP_FULL_DATABASE i IMP_FULL_DATABASE
EXP_FULL_DATABASE SELECT ANY TABLE, BACKUP ANY TABLE
EXP_IMP_DATABASE BECOME USER
ANULOWANIE PRAW DO OBIEKTÓW BAZY DANYCH - REVOKE
REVOKE ALL [PRIVILEGES] |
[ALTER], [INDEX], [REFERENCES], [SELECT], [INSERT], [EXECUTE],
[DELETE], [UPDATE] [(lista_kolumn)] ON tabela
FROM lista_użytkowników | lista_ról | PUBLIC [CASCADE CONSTRAINTS]
Przykłady:
1/ REVOKE ALL ON egzaminy FROM marekm;
2/ REVOKE UPDATE, INSERT ON egzaminy FROM elam;
3/ REVOKE INSERT ON r_dane_we_ecdl FROM agas;
4/ REVOKE ALTER, INDEX ON egzaminatorzy FROM piotrm CASCADE CONSTRAINTS;
5/ REVOKE UPDATE ON przedmioty FROM alak;
6/ REVOKE UPDATE ON osrodki FROM PUBLIC;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 68
Uwagi dotyczące anulowania praw do obiektów nadanych użytkownikom i rolom:
" Polecenie służy do anulowania praw nadanych uprzednio poleceniem GRANT przez
danego użytkownika.
" Anulowanie praw natychmiast uniemożliwia wykonywanie dalszych operacji,
których prawa te dotyczyły.
" Klauzula PUBLIC może być użyta tylko do anulowania praw tym użytkownikom,
którym prawa te nadano przy pomocy tej samej klauzuli.
" Klauzula CASCADE CONSTRAINTS usuwa prawa, pozwalające odwoływać się do
kolumn innych tabel (opcja REFERENCES w instrukcji GRANT) jednocześnie
usuwając istniejące więzy integralności oparte na tych kolumnach.
" Anulowanie praw, które zostały przekazane innym użytkownikom lub przypisane
rolom powoduje ich anulowanie dla wszystkich użytkowników lub usunięcie
z definicji wszystkich ról.
" Anulowanie praw do danego obiektu, pochodzących od wielu użytkowników wymaga
odwołania tych praw przez każdego z nich.
ANULOWANIE PRAW SYSTEMOWYCH - REVOKE
REVOKE prawo_sys | rola_sys FROM lista_użytkowników | lista_ról | PUBLIC
Przykłady:
1/ REVOKE CREATE VIEW FROM alak;
2/ REVOKE r_dane_we_ecdl FROM r_egzamin_we;
3/ REVOKE r_dane_we_ecdl FROM agas;
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 69
Uwagi dotyczące anulowania praw systemowych i ról:
" Polecenie umożliwia anulowanie przyznanych innym użytkownikom praw
systemowych i ról oraz anulowanie ról użytych w definicjach innych ról.
" Wymagane jest posiadanie odpowiedniego prawa z opcją WITH ADMIN OPTION
lub w przypadku ról prawa systemowego GRANT ANY ROLE.
" Anulowanie praw systemowych natychmiast uniemożliwia wykonywanie dalszych
operacji, których prawa te dotyczyły.
" Klauzula PUBLIC może być użyta tylko do anulowania praw tym użytkownikom,
którym prawa te nadano przy pomocy tej samej klauzuli.
" Użytkownik korzystający z roli, która została mu odebrana w czasie sesji pracy, może
kontynuować pracę do chwili zakończenia sesji.
�Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 70