Ć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
WHERE id_prac =numer;
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.
/*CREATE OR REPLACE PROCEDURE podnies_place (suma REAL) IS
BEGIN
UPDATE prac
SET placa_pod = placa_pod + suma;
END;
/*/
BEGIN
podnies_place(100);
END;
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:
/*CREATE OR REPLACE FUNCTION podatek(pracid IN NUMBER) RETURN NUMBER IS
ret_val NUMBER;
BEGIN
SELECT placa_pod+nvl(placa_dod,0) INTO ret_val FROM PRAC WHERE ID_PRAC=pracid;
ret_val:=ret_val*12;
IF ret_val>50000 THEN
ret_val:=ret_val*0.4;
ELSIF ret_val>30000 THEN
ret_val:=ret_val*0.3;
ELSE
ret_val:=ret_val*0.19;
END IF;
RETURN ret_val;
END podatek;
/*/
ACCEPT idprac PROMPT 'Wprowadz ID_PRAC np. 180'
SELECT nazwisko, ROUND(podatek('&idprac'),2) FROM prac
where id_prac = '&idprac';
LABORATORIUM z BAZ DANYCH
ORACLE