Ćwiczenie 8

I. KURSORY

Każde zapytanie SQL umieszczone w programie PL/SQL może zwrócić zero, jedną bądź wiele krotek. Aby efektywnie przetworzyć krotki zwrócone przez zapytanie korzystamy z kursorów. Kursor jest obiektem związanym z zapytaniem. Programista może:

• otworzyć kursor (zidentyfikować zbiór wynikowy)

• pobrać daną do kursora (odczytać kolejną krotkę z wyniku zapytania i wpisać ją do kursora)

• zamknąć kursor (zwolnić obszar pamięci przydzielony kursorowi)

Deklarowanie kursora

• Nazwa kursora nie jest zmienną, lecz identyfikatorem. Do kursora nie można przypisać wartości

• Parametry są widoczne tylko wewnątrz kursora, nie można związać z nimi żadnych ograniczeń

DECLARE CURSOR nazwa [ ( lista parametrów) ]

[ RETURN typ zwracany ] IS zapytanie SQL;

parametr [ IN ] typ [ { := | DEFAULT } wartość ]

np: DECLARE CURSOR c_pracownicy (zespol NUMBER DEFAULT 10) IS

SELECT nazwisko, etat, zatrudniony, placa_pod

FROM prac

WHERE id_zesp = zespol;

Otwieranie kursora

Otwarcie kursora powoduje wykonanie związanego z nim zapytania i zidentyfikowanie zbioru wynikowego, zawierającego krotki spełniające kryteria wyszukiwania.

OPEN nazwa_kursora [ (lista parametrów aktualnych) ]

np: DECLARE

CURSOR c_prac (zespol NUMBER, posada VARCHAR2)

IS SELECT * FROM prac

WHERE id_zesp = zespol AND etat = posada;

BEGIN

...

v_etat := `PROFESOR';

OPEN c_prac(10, 'ASYSTENT');

OPEN c_prac(50, v_etat);

Pobieranie z kursora

Dane z kursora są pobierane pojedynczo. Każde wykonanie polecenia FETCH powoduje odczytanie wszystkich wartości z bieżącej krotki i przesunięcie znacznika kursora na kolejną krotkę. Na liście zmiennych musi się znajdować taka sama liczba zmiennych jak liczba atrybutów w kursorze. Odpowiednie zmienne i atrybuty muszą

się zgadzać co do typu.

FETCH nazwa_kursora INTO lista zmiennych | rekord;

np: DECLARE

CURSOR c_prac IS SELECT nazwisko, etat FROM prac;

v_nazwisko PRAC.NAZWISKO%TYPE;

v_etat PRAC.ETAT%TYPE;

BEGIN

OPEN c_prac;

FETCH c_prac INTO v_nazwisko, v_etat;

Zamykanie kursora

Zamknięcie kursor powoduje, że kursor staje się nieaktywny a zbiór wynikowy związany z kursorem staje się niezdefiniowany. Zamknięty kursor można powtórnie otworzyć, np. z innymi parametrami. Każde odwołanie się do zamkniętego (lub jeszcze nie otwartego) kursora powoduje błąd INVALID_CURSOR.

CLOSE nazwa_kursora;

Atrybuty kursora

• %FOUND - wartością atrybutu jest TRUE jeśli ostatnia operacja FETCH odczytała krotkę z kursora. W przeciwnym wypadku (tzn. kiedy odczyt się nie udał) atrybut przyjmuje wartość FALSE. Przed pierwszym odczytem atrybut ma wartość NULL

• %NOTFOUND - wartością atrybutu jest FALSE jeśli ostatnia operacja FETCH odczytała krotkę z kursora. W przeciwnym wypadku (tzn. kiedy odczyt się nie udał) atrybut przyjmuje wartość TRUE. Przed pierwszym odczytem atrybut ma wartość NULL

• %ROWCOUNT - wartością atrybutu jest liczba odczytanych z kursora krotek. Przed pierwszym odczytem atrybut ma wartość 0

• %ISOPEN - wartością atrybutu jest TRUE jeśli kursor jest otwarty i FLASE jeśli kursor jest zamknięty.

Atrybuty kursora niejawnego

• Każde polecenie DML (INSERT, UPDATE, DELETE, SELECT FOR UPDATE) powoduje utworzenie kursora niejawnego (ang. implicit cursor). Dla takiego kursora można sprawdzać wartości następujących atrybutów:

• SQL%ROWCOUNT: liczba wierszy zmodyfikowanych przez polecenie

• SQL%FOUND: TRUE jeśli ostatnie polecenie zmodyfikowało jakiekolwiek wiersze

• SQL%NOTFOUND: TRUE jeśli ostatnie polecenie nie zmodyfikowało żadnych wierszy

• SQL%ISOPEN: zawsze FALSE (kursor niejawny jest zamykany natychmiast po zakończeniu polecenia)

Przykład użycia kursora:

DECLARE

CURSOR c_prac (zespol NUMBER DEFAULT 10) IS

SELECT nazwisko, etat FROM prac

WHERE id_zesp = zespol ORDER BY placa_pod DESC;

v_nazwisko PRAC.NAZWISKO%TYPE;

v_etat PRAC.ETAT%TYPE;

BEGIN

OPEN c_prac(30);

LOOP

FETCH c_prac INTO v_nazwisko, v_etat;

EXIT WHEN c_prac%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_nazwisko || ' , ' || v_etat);

END LOOP;

CLOSE c_prac;

END;

/

Pętla FOR z kursorem

• Zmienna sterująca pętlą jest deklarowana automatycznie jako zmienna typu kursor%ROWTYPE

• Kursor jest otwierany automatycznie

• W każdym przebiegu pętli jedna krotka jest pobierana z kursora i umieszczana w zmiennej sterującej pętlą

• Po pobraniu ostatniej krotki kursor jest automatycznie zamykany

np: DECLARE

CURSOR c1 (minplaca NUMBER) IS

SELECT * FROM prac WHERE placa_pod > minplaca;

BEGIN

FOR c1_rec IN c1(300) LOOP

DBMS_OUTPUT.PUT_LINE(c1_rec.nazwisko ||

' zarabia ' || c1_rec.placa_pod || ' i pracuje jako ' || c1_rec.etat);

END LOOP;

END;

/

Pętla FOR z podzapytaniem

• Zmienna sterująca pętlą jest deklarowana automatycznie jako zmienna typu podzapytanie%ROWTYPE

• Kursor jest otwierany automatycznie

• W każdym przebiegu pętli jedna krotka jest pobierana z kursora i umieszczana w zmiennej sterującej pętlą

• Po pobraniu ostatniej krotki kursor jest automatycznie zamykany

• Kursor oparty na podzapytaniu nie może być parametryzowany ani wykorzystywany wielokrotnie

np: BEGIN

FOR c1_rec IN ( SELECT * FROM prac WHERE Placa_pod >2000 ) LOOP

DBMS_OUTPUT.PUT_LINE(c1_rec.nazwisko ||

` zarabia ` || c1_rec.placa_pod || ` i pracuje jako ` || c1_rec.etat);

END LOOP;

END;

/

II. PODPROGRAMY

Podprogramy to:

• Procedury (wykonują określone akcje)

• funkcje (wykonują obliczenia i zwracają wartości)

• pakiety (zbierają w całość logicznie powiązane procedury, funkcje, zmienne i kursory):

Są przechowywane w bazie danych w postaci skompilowanej i źródłowej (źródło dostępne poprzez USER_SOURCE). Mogą być współdzielone przez wielu użytkowników.

Każdy podprogram składa się ze specyfikacji zawierającej słowo PROCEDURE lub FUNCTION, nazwę i listę parametrów w nawiasach oraz ciała, które jest normalnym blokiem PL/SQL z wyjątkiem części deklaracyjnej, która nie zawiera słowa DECLARE.

Definiowanie procedury

Nazwa procedury musi być unikalna w ramach schematu (lub pakietu). Między słowami kluczowymi IS i BEGIN umieszczamy deklaracje wszystkich zmiennych i kursorów lokalnych. Między słowami kluczowymi BEGIN i END umieszczamy kod PL/SQL, który wykonuje dana procedura

Składnia procedury:

CREATE OR REPLACE PROCEDURE nazwa_procedury [ (arg1[, arg2, ...]) ] IS

[deklaracje]

BEGIN

instrukcje

[EXCEPTION

obsługa wyjątków]

END [nazwa_procedury];

Składnia funkcji:

FUNCTION nazwa_funkcji [ (arg1 [arg2, ...]) ]

RETURN nazwa_typu IS

[deklaracje]

BEGIN

instrukcje

[EXCEPTION

obsługa wyjątków]

END [nazwa_funkcji];

parametr ::= nazwa [ IN | OUT | IN OUT] typ [{:= | DEFAULT} wyrażenie]

Parametry podprogramów

Przy definiowaniu podprogramów każdy zadeklarowany parametr musi być przekazany w jednym z następujących trybów:

IN (domyślnie) - przekazuje wartość do podprogramu; parametr formalny zachowuje się jak stała, nie można go zmieniać; parametr aktualny (wywołania) może być wyrażeniem, stałą literałem lub zmienną zainicjalizowaną

OUT - zwraca wartość do wywołującej jednostki programu. Wewnątrz programu parametr zachowuje się jak zmienna, która nie została zainicjalizowana

IN OUT - kombinacja powyższych trybów umożliwiająca przekazywanie wartości do podprogramu oraz zwracanie wartości na zewnątrz

Tworzenie podprogramów

Procedury i funkcje tworzy się w sekcji deklaracyjnej dowolnego bloku PL/SQL, po wszystkich deklaracjach. Możliwe jest użycie deklaracji zapowiadających funkcje i procedury zdefiniowane w dalszej części programu.

Przykład procedury:

CREATE OR REPLACE PROCEDURE sprawdz_asystentow

(p_id_zesp IN NUMBER, p_ilu_asystentow OUT NUMBER) IS

CURSOR c_asystenci IS

SELECT *FROM prac WHERE id_zesp =p_id_zesp AND etat ='ASYSTENT';

v_starszy_asystent prac%ROWTYPE;

BEGIN

SELECT COUNT(*) INTO p_ilu_asystentow

FROM prac WHERE id_zesp =p_id_zesp AND etat ='ASYSTENT';

FOR cur_rec IN c_asystenci LOOP

IF (ROUND (MONTHS_BETWEEN (SYSDATE, cur_rec.zatrudniony)/12))>5 THEN

DBMS_OUTPUT.PUT_LINE('Asystent '||cur_rec.nazwisko ||'pracuje ponad 5 lat');

END IF;

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('W zespole '||p_id_zesp ||'nie ma asystentow ');

END sprawdz_asystentow;

/

Wywołanie procedury:

DECLARE

p_in NUMBER :=&numer_zespolu;

p_out NUMBER;

BEGIN

sprawdz_asystentow(p_in, p_out);

DBMS_OUTPUT.PUT_LINE(`Liczba asystentow:` ||p_out);

END;

/

Działanie procedury przerywa instrukcja RETURN; a działanie funkcji: RETURN wyrażenie;

W PL/SQL możliwe jest przeciążanie nazw procedur i funkcji będących częściami jednego pakietu. DECLARE

PROCEDURE podnies_place (suma REAL, numer INTEGER) IS

BEGIN

UPDATE prac

SET placa_pod = placa_pod + suma

END;

BEGIN

podnies_place(100, 120);

END;

/

Zadanie do samodzielnego wykonania:

Zad. 1. Zdefiniuj przeciążoną funkcję podnies_place (ile REAL), która podnosi place wszystkim pracownikom o 100 zł.

np.

Wywołanie procedur i funkcji (PL/SQL)

Wszędzie, gdzie można umieścić funkcję wbudowaną SQL, można umieścić funkcję pl/SQL zdefiniowaną przez użytkownika.

Aby funkcja mogła być wywoływana z poziomu SQL, musi ona posiadać odpowiedni poziom czystości

• funkcja wywoływana z instrukcji SELECT nie może modyfikować żadnych wartości w bazie danych

• funkcja wywoływana z instrukcji INSERT, UPDATE, DELETE nie może odczytywać i modyfikować żadnej tabeli, której dotyczy instrukcja

• funkcja wywoływana z instrukcji SELECT, INSERT, UPDATE, DELETE

nie może zawierać instrukcji sterujących sesją i transakcjami oraz instrukcji DDL

Poziom czystości deklaruje się za pomocą dyrektywy

RESTRICT_REFERENCES:

•RNDS, RNPS - Reads No Database/Package State

•WNDS, WNPS - Writes No Database/Package State

•TRUST - brak kontroli czystości funkcji

Definiowanie funkcji niezależnej:

Np.

CREATE or REPLACE FUNCTION polowa(v_ile IN NUMBER)

RETURN NUMBER

IS

BEGIN

RETURN (v_ile * 0.5);

END polowa;

/

Wywołanie funkcji z SQL*Plus:

SQL> VARIABLE x NUMBER;

SQL> EXECUTE :x :=polowa(100);

SQL> PRINT x;

lub

SQL> SELECT nazwisko, placa_pod, polowa(placa_pod)

FROM prac

WHERE id_prac >150;

Podprogramy składowane w bazie danych

Procedury i funkcje przestają istnieć po zakończeniu macierzystego programu. Aby umożliwić innym aplikacjom korzystanie z nich należy umieścić je w bazie danych. Umożliwia to instrukcja CREATE [ OR REPLACE] , która tworzy funkcję lub procedurę składowaną. (REPLACE oznacza usunięcie wcześniej istniejącego podprogramu).

Uwaga: Każdy podprogram pisany interaktywnie można umieścić w pliku tekstowym i zapamiętać w bazie danych poleceniem save prog.sql, gdzie prog jest nazwą pliku z tekstem podprogramu. Z poziomu SQL*Plus uruchamia się go poleceniem start prog lub @prog,

Wywołanie z poziomu SQL*Plus podprogramów składowanych w bazie danych umożliwia polecenie EXECUTE, a uzyskiwanie informacji o ich parametrach polecenie DESCRIBE. Do wykrywania błędów pomocne jest polecenie SHOW ERRORS, a do oglądania ich treści - perspektywa słownika danych o nazwie USER_SOURCE.

Np. SQL> CREATE OR REPLACE

PROCEDURE Podnies_place(suma NUMBER)

IS

BEGIN

UBDATE prac SET placa_pod = placa_pod + suma;

END;

/

SQL>SHOW ERRORS

...

SQL> LIST 3

SQL> CHANGE /UBDATE/UPDATE

SQL> /

.....

SQL> DESCRIBE podnies_place

SQL> EXECUTE podnies_place(50);

...

SQL> SELECT line, text

FROM user_source

WHERE name = `PODNIES_PLACE';

SQL> DROP PROCEDURE podnies_place;

Zadanie do samodzielnego wykonania:

Zad. 2. Zdefiniować funkcję podatek o parametrze umożliwiającym wprowadzenie numeru pracownika (wartości id_prac z tabeli prac), która oblicza podatek należny fiskusowi w wysokościach:

40% * rocz_zar gdy rocz_zar>50000

30% * rocz_zar gdy 50000>=rocz_zar>30000

19%* rocz_zar w pozostał przypadkach

gdzie rocz_zar = 12*placa_pod + placa_dod.

Uwaga: przy obliczaniu rocznych zarobków użyć funkcji nvl dla placa_dod;

Odp:

LABORATORIUM z BAZ DANYCH

ORACLE

---