Bazy danych 1
Skrypt tworzący przykładową bazę danych znajduje się w pliku sql/db.sql.
1 Kursory
 Kursor [6] udostępnia podzbiór danych zdefiniowanych przez zapytanie. Dane te są pobierane do pamięci
w momencie otwarcia kursora i przechowywane w niej do czasu jego zamknięcia.
Rodzaje kursorów:
" jawne  tworzony jawnie przez programistÄ™,
" niejawne  tworzony automatycznie tworzone przez Oracle dla poleceń DML: INSERT, UPDATE, DE-
LETE oraz SELECT INTO.
Deklaracja kursora:
DECLARE CURSOR nazwa_kursora [(parametry)]
[RETURN zwracany_typ]
IS Instrukcja_SELECT
[FOR UPDATE [OF (lista_kolumn)[NOWAIT]];
Otwieranie kursora:
OPEN nazwa_kursora[(parametry)];
Pobieranie rekordów za pomocą kursora:
FETCH nazwa_kursora INTO nazwy_zmiennych | rekord_PL/SQL;
Zamknięcie kursora:
CLOSE nazwa_kursora;
 Opcjonalna fraza FOR UPDATE [6] blokuje rekordy po otwarciu kursora. Rekordy wciąż będą dostępne
w innych sesjach, ale w trybie tylko do odczytu.
Atrybuty kursora:
" %FOUND  zwraca TRUE jeżeli instrukcja FETCH zwróciła rekord, FALSE w przeciwnym przypadku,
" %NOTFOUND  zwraca TRUE jeżeli instrukcja FETCH nie zwróciła rekord, FALSE w przeciwnym
przypadku,
" %ISOPEN  zwraca TRUE jeżeli kursor został otwarty, FALSE w przeciwnym przypadku,
" %ROWCOUNT  zwraca liczbę wierszy pobranych do tego momentu przy użyciu kursora.
Poruszanie się po kursorze za pomocą pętli
CREATE OR REPLACE PROCEDURE drukuj_grupy1 IS
CURSOR grupy IS SELECT * FROM grupa;
tmp grupa%ROWTYPE;
BEGIN
OPEN grupy;
LOOP
FETCH grupy INTO tmp;
EXIT WHEN grupy%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(NVL(tmp.nazwa, '?'));
END LOOP;
CLOSE grupy;
END;
/
Przykład 1: Pierwszy przykład poruszanie się po kursorze za pomocą pętli{sql/test loop 1.sql}
1
CREATE OR REPLACE PROCEDURE drukuj_grupy2 IS
CURSOR grupy IS SELECT * FROM grupa;
BEGIN
FOR tmp IN grupy LOOP
DBMS_OUTPUT.PUT_LINE(NVL(tmp.nazwa, '?'));
END LOOP;
END;
/
Przykład 2: Drugi przykład poruszanie się po kursorze za pomocą pętli{sql/test loop 2.sql}
Przykłady użycia kursora
CREATE OR REPLACE PROCEDURE konta_studentow IS
CURSOR wszyscy_studenci_cursor IS
SELECT *
FROM student s
WHERE s.srednia is NOT NULL
ORDER BY s.srednia DESC
FOR UPDATE;
CURSOR grupa_wyrownawcza_cursor IS
SELECT s.id student_id, s.nazwisko, s.konto, p.nazwa
FROM student s, zaliczenie z, przedmiot p
WHERE z.id_student = s.id
AND z.id_przedmiot = p.id
AND z.termin3 < 3.0
FOR UPDATE OF s.konto;
licznik NUMBER(6) := 1;
BEGIN
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE('Stypendia:');
FOR st IN wszyscy_studenci_cursor LOOP
IF ((LICZNIK >= 1) AND (LICZNIK <= 3)) THEN
DBMS_OUTPUT.PUT_LINE('300 zl dla ' || st.nazwisko || ' ' || st.imie);
UPDATE student s
SET s.konto = s.konto + 300
WHERE CURRENT OF wszyscy_studenci_cursor;
END IF;
licznik := licznik + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Grupa wyrownawcza:');
FOR st IN grupa_wyrownawcza_cursor LOOP
DBMS_OUTPUT.PUT_LINE(' -100 zl dla ' || st.nazwisko || ' za przedniot '
|| st.nazwa);
UPDATE student s
SET s.konto = s.konto - 100
WHERE CURRENT OF grupa_wyrownawcza_cursor;
END LOOP;
END;
/
Przykład 3: Pierwszy przykład użycia kursora{sql/test cursor 1.sql}
CREATE OR REPLACE PROCEDURE srednie IS
CURSOR studenci_cursor IS
SELECT *
FROM student;
CURSOR zaliczenia_cursor (s_id NUMBER) IS
SELECT z.termin1, z.termin2, z.termin3, p.waga
FROM student s, zaliczenie z, przedmiot p
WHERE z.id_student = s.id
2
AND z.id_przedmiot = p.id
AND s.id = s_id;
suma_wag NUMBER(6) := 1;
suma_ocen NUMBER(6,3) := 0;
srednia_wazona NUMBER(6,3) := 0;
BEGIN
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE('Srednie wazone');
FOR st IN studenci_cursor LOOP
DBMS_OUTPUT.PUT('Student ' || st.nazwisko);
suma_wag := 0;
suma_ocen := 0;
srednia_wazona := 0;
FOR x IN zaliczenia_cursor(st.id) LOOP
suma_wag := suma_wag + x.waga;
IF((x.termin3 IS NULL) AND (x.termin2 IS NULL)) THEN
suma_ocen := suma_ocen+x.termin1*x.waga;
ELSIF(x.termin3 IS NULL) THEN
suma_ocen := suma_ocen+((x.termin1+x.termin2)/2 )*x.waga;
ELSE
suma_ocen := suma_ocen+((x.termin1+x.termin2+x.termin3)/3)*x.waga;
END IF;
END LOOP;
IF suma_wag > 0 THEN
srednia_wazona := suma_ocen / suma_wag;
ELSE srednia_wazona := 0;
END IF;
DBMS_OUTPUT.PUT_LINE(' ' || srednia_wazona);
UPDATE student s
SET srednia = srednia_wazona
WHERE s.id = st.id;
END LOOP;
END;
/
Przykład 4: Drugi przykład użycia kursora{sql/test cursor 2.sql}
2 WyjÄ…tki
 W momencie wystąpienia błędu czasu wykonania zgłaszany jest wyjątek [6]. Kiedy to się stanie, sterowa-
nie trafia do bloku obsługi wyjątku, który jest odrębną sekcją programu. Pozwala to oddzielić obsługę błędów
od reszty programu, co ułatwia zrozumienie logiki programu. Gwarantuje to także przechwycenie wszystkich
błędów.
Rodzaje wyjątków:
" zdefiniowane przez użytkownika,
" wbudowane.
Deklaracja wyjÄ…tku w sekcji deklaracji:
nazwa_wyjÄ…tku EXCEPTION;
Zgłoszenie wyjątku użytkownika w sekcji wykonawczej:
RAISE nazwa_wyjÄ…tku;
Obsługa wyjątku w sekcji wyjątków:
" obsługa konkretnego wyjątku:
WHEN nazwa_wyjÄ…tku THEN polecenia;
3
" obsługa wszystkich wyjątków:
WHEN OTHERS THEN polecenia;
Przykład użycia wyjątku:
CREATE OR REPLACE FUNCTION pierwiastek (liczba VARCHAR2)
RETURN VARCHAR2
IS
null_exception EXCEPTION;
BEGIN
IF(liczba IS NULL) THEN
RAISE null_exception;
END IF;
RETURN '' || SQRT(TO_NUMBER(liczba));
EXCEPTION
WHEN null_exception THEN
DBMS_OUTPUT.PUT_LINE('liczba nie moze byc NULL');
RETURN NULL;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('wystapil blad');
RETURN NULL;
END;
/
Przykład 5: Przykład użycia wyjątku{sql/test exception.sql}
Wybrane wbudowane wyjÄ…tki Oracle [6]:
" DUP VAL ON INDEX  naruszenie ograniczenia niepowtarzalności,
" INVALID CURSOR  niedozwolona operacja na kursorze,
" INVALID NUMBER  nieudana konwersja na liczbÄ™,
" VALUE ERRO  błąd obcięcia, arytmetyczny lub konwersji,
" NO DATA FOUND  brak żądanych danych,
" TOO MANY ROWS  instrukcja SELECT INTO pasuje do więcej niż jednego wiersza,
" ZERO DIVIDE  dzielenie przez 0.
3 Błędy aplikacji
RAISE_APPLICATION_ERROR(numer_błędu, opis_błędu);
Number błędu mus należeć do przedziału: -20000 do -20999.
Opis błędu może mieć maksymalnie 512 znaków.
Dyrektywa EXCEPTION INIT [6] umożliwia powiązanie wyjątku z wybranym błędem Oracle. Umożliwia
to specyficzną obsługę danego błędu zamiast przetwarzania go w bloku obsługi wyjątków OTHERS. Jeżeli w
czasie wykonania programu wystąpi dany błąd zostanie zgłoszony zdefiniowany przez użytkownika wyjątek.
Wyjątki wbudowane są powiązane z odpowiadającymi im błędami także przy pomocy tej dyrektywy.
CREATE OR REPLACE PROCEDURE wstaw_studenta(p_id NUMBER)
AS
my_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(my_exception, -00001);
BEGIN
INSERT INTO STUDENT(id) VALUES(p_id);
EXCEPTION
WHEN my_exception THEN
4
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE('My_Exception');
END;
/
Przykład 6: Przykład użycia blędu aplikacji{sql/test error.sql}
4 Pakiety
 Pakiet języka PL/SQL [10] jest pewnym nazwanym zbiorem, który może zawierać dowolną liczbę proce-
dur i funkcji. Pakiety mogą także przechowywać inne obiekty, na przykład wyjątki, zmienne czy deklaracje
typów danych.
 W pakiecie można przeciążać procedury i funkcje. Oznacza to, że w pakiecie może znajdować się więcej
niż jedna procedura lub funkcja o takiej samej nazwie, a jej wersje przyjmują różne parametry.
Specyfikacja (nagłówek) pakietu:
CREATE OR REPLACE PACKAGE nazwa_pakietu {AS | IS}
nagłówek_podprogramu1;
nagłówek_podprogramu2;
nagłówek_podprogramu3;
END [nazwa_pakietu];
/
CREATE OR REPLACE PACKAGE dziekanat
AS
PROCEDURE ustaw_srednia(id_student student.id%TYPE,
srednia_student student.srednia%TYPE);
PROCEDURE ustaw_srednia(id_student student.id%TYPE);
END dziekanat;
/
Przykład 7: Przykład użycia pakietu{sql/test package.sql}
Ciało pakietu:
CREATE OR REPLACE PACKAGE BODY nazwa_pakietu {AS | IS}
AS
opcjonalne_podprogramy_prywatne;
ciało_podprogramu1;
ciało_podprogramu2;
ciało_podprogramu3;
BEGIN
kod_inicjujÄ…cy;
END [nazwa_pakietu];
/
CREATE OR REPLACE PACKAGE BODY dziekanat
AS
domyslna_ocena NUMBER(1);
PROCEDURE ustaw_srednia(id_student student.id%TYPE,
srednia_student student.srednia%TYPE) AS
BEGIN
UPDATE student s
SET s.srednia = srednia_student
WHERE s.id = id_student;
END;
PROCEDURE ustaw_srednia(id_student student.id%TYPE) AS
BEGIN
UPDATE student s
5
SET s.srednia = domyslna_ocena
WHERE s.id = id_student;
END;
BEGIN
DBMS_RANDOM.INITIALIZE(32767);
domyslna_ocena := ROUND(DBMS_RANDOM.VALUE(1, 5));
END dziekanat;
/
Przykład 8: Przykład użycia ciala pakietu{sql/test package body.sql}
Użycie:
EXEC dziekanat.ustaw_srednia(500, 5.0);
Usuwanie pakietu:
DROP PACKAGE [BODY] nazwa_pakietu;
np.
DROP PACKAGE dziekanat;
5 Przykładowa treść laboratorium
Skrypty dotyczące przykładowej bazy danych znajdują się w katalogu db:
" db/create.sql,
" db/delete.sql,
" db/drop.sql
" oraz db/insert.sql.
Proszę stworzyć zapytania zgodnie z wymaganiami podanymi przez prowadzącego, np.
1. Procedurę, która wyświetla średnie wynagrodzenie pracowników, a następnie maksymalnie trzem pra-
cownikom, którzy zarabiają najmniejszą kwotę zwiększa pensję odpowiednio o 5%, 10% i 15%.
2. Procedurę, która pracownikom, którzy wypożyczyli ilość książek powyżej średniej, zwiększy pensję o
1% sumy faktur wystawionych przez danego pracownika. W rozwiązaniu powinien zostać użyty kursor
zewnętrzny oraz wewnętrzny.
Podpowiedz: kursor zewnętrzny może zostać użyty do wyznaczenia pracowników, którzy wypożyczyli
ilość książki powyżej średniej, kursor wewnętrzny z parametrem (id pracownika z kursora zewnętrz-
nego) może zostać użyty do wyliczenia sumy faktur wystawionych przez tego pracownika, a następnie
zwiększenia pensji.
3. Procedurę, która przegląda informacje o czytelniku, którego id jest przekazane jako parametr:
" jeżeli ulica lub numer domu ma wartość NULL zgłasza wyjątek: niepoprawny adres,
" jeżeli nazwisko lub imię ma długość <2zgłasza wyjątek: niepoprawne dane,
" jeżeli czytelnik ma przetrzymane książki (nie oddane przez czas dłuższy niż trzy miesiące) zgłasza
wyjątek: przetrzymane książki.
Każdy z powyższych wyjątków powinien mieć zapewnioną inną obsługę polegającą na wyświetleniu
odpowiedniego komunikatu, wszystkie pozostałe wyjątki powinny spowodować wypisanie komunikatu
 Nieznany wyjÄ…tek .
4. itp.
Wszystkie podprogramy powinny znajdować się w pakiecie.
Jeżeli tabele zawierają zbyt mało danych proszę wstawić dodatkowe dane.
Uwaga! Oceniane będą tylko instrukcje, które zostaną wykonane bez błędów przez serwer Oracle.
6
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
7