Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Systemy baz danych - wykład V dr inż. Robert Perliński Procedury i funkcje Pakiety Instytut Informatyki Teoretycznej i Stosowanej Politechnika Częstochowska Podsumowanie Źródła Systemy baz danych - wykład IV 20 marca 2017 1/38 Plan wykładu Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje 1 Wprowadzenie do bloków nazwanych 2 Parametry 3 Procedury i funkcje 4 Pakiety Pakiety Podsumowanie Źródła 5 Podsumowanie 6 Źródła Systemy baz danych - wykład IV 2/38 Rodzaje bloków w PL/SQL Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Bloki anonimowe Bloki nazwane: Parametry Procedury i funkcje Pakiety procedury, funkcje, pakiety, wyzwalacze - procedury wyzwalane. Podsumowanie Źródła Systemy baz danych - wykład IV 3/38 Cechy bloków anonimowych Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Cechy bloków anonimowych: kompilowane przy każdym ich użyciu, nie są przechowywane w systemie bazodanowym, nie można ich wywołać z poziomu innej aplikacji, Pakiety w sposób jawny nie mogą zwracać żadnej wartości, Podsumowanie nie mogą przyjmować żadnych parametrów. Źródła Systemy baz danych - wykład IV 4/38 Cechy bloków nazwanych Systemy baz danych wykład IV dr inż. Robert Perliński Cechy bloków nazwanych: kompilowane tylko raz, w postaci skompilowanej trwale przechowywane przez bazę, Wprowadzenie do bloków nazwanych mogą być wywoływane przez inne aplikacje, Parametry funkcje obowiązkowo muszą zwracać wartości, Procedury i funkcje procedury i funkcje mogą (nie muszą) przyjmować parametry, Pakiety wykonywane na żądanie użytkownika lub w wyniku zajścia zdarzenia, Podsumowanie Źródła można wyróżnić dwie części: specyfikację i ciało, część deklaracyjna procedur, funkcji i pakietów nie rozpoczyna się od słowa kluczowego DECLARE. Systemy baz danych - wykład IV 5/38 Parametry Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Parametry: umożliwiają przekazywanie wartości między środowiskiem wywołującym a programem - w obie strony, w deklaracji programu występują parametry formalne, dla których nie podaje się rozmiaru typu, definicja parametru formalnego: nazwa [tryb] typ [{:=|DEFAULT} wartosc] Pakiety Podsumowanie Źródła w wywołaniu programu występuje parametr aktualny, parametry aktualne mogą być: literałem, zmienną albo wyrażeniem. Systemy baz danych - wykład IV 6/38 Tryby przekazania parametru Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie Źródła IN (domyślnie) - parametr przekazuje stałą wartość ze środowiska wywołującego do procedury; parametr formalny traktowany wewnątrz procedury jak stała; parametr aktualny to literał, wyrażenie, stała lub zainicjowana zmienna; można mu przypisać domyślną wartość. OUT - przekazuje wartość z procedury do środowiska wywołującego; parametr aktualny musi być zmienną; wewnątrz procedury traktowany jako niezainicjowana zmienna; nie można przypisać mu domyślnej wartości. IN OUT - przekazuje wartość ze środowiska wywołującego do procedury i prawdopodobnie przekazuje inną wartość z procedury do środowiska wywołującego; parametr aktualny musi być zmienną; wewnątrz jest traktowany jak zainicjalizowana zmienna; nie można przypisać mu domyślnej wartości. Systemy baz danych - wykład IV 7/38 Przekazywanie parametrów aktualnych - notacje Systemy baz danych wykład IV dr inż. Robert Perliński Notacja pozycyjna (taka sama kolejność parametrów aktualnych jak parametrów formalnych): Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje mojaProcedura(1, 2, 3); Notacja imienna (dowolna kolejność, używamy powiązania z nazwą parametru formalnego): mojaProcedura(p1 => 1, p3 => 3, p2 => 2); Pakiety Podsumowanie Źródła Notacja mieszana: Systemy baz danych - wykład IV mojaProcedura(1, p3 => 3, p2 => 2); 8/38 Wykorzystanie wartości domyślnych I Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Definiujemy domyślne wartości dla parametrów. Mamy elastyczność dzięki wykorzystaniu notacji pozycyjnej i imiennej w przekazywaniu argumentów aktualnych. Wartość domyślna przekazywana przez słowo kluczowe DEFAULT albo operator := Parametry Procedury i funkcje Pakiety Podsumowanie Źródła CREATE OR REPLACE PROCEDURE dodaj (p1 NUMBER := 100, p2 NUMBER DEFAULT 200) IS p3 NUMBER; BEGIN p3 := p1 + p2; DBMS_OUTPUT.PUT_LINE('Wynik: '|| p3); END; / Systemy baz danych - wykład IV 9/38 Wykorzystanie wartości domyślnych II Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety EXECUTE EXECUTE EXECUTE EXECUTE EXECUTE dodaj; dodaj (); dodaj (p2 => 100); dodaj (1,2); dodaj (1); Podsumowanie Źródła Systemy baz danych - wykład IV 10/38 Tworzenie procedur Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie Źródła CREATE [OR REPLACE] PROCEDURE nazwa [( par1 [ IN | OUT | IN OUT ] typ [, par2 [ IN | OUT | IN OUT ] typ, ...] )] {IS|AS} [deklaracje;] BEGIN instrukcje; [EXCEPTION obsluga_wyjatkow;] END [nazwa]; Systemy baz danych - wykład IV 11/38 Tworzenie procedur - przykład Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie Źródła CREATE OR REPLACE PROCEDURE wyliczanka (p_ile NUMBER) AS v_iter NUMBER := 0; BEGIN IF p_ile > 0 THEN FOR v_iter IN 1..p_ile LOOP dbms_output.put(v_iter); END LOOP; dbms_output.put_line(' '); wyliczanka(p_ile-1); END IF; END wyliczanka; Systemy baz danych - wykład IV 12/38 Inne operacje na procedurach Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Usuwanie procedury: DROP PROCEDURE wyliczanka; Parametry Wyświetlanie informacji o procedurze (nazwy parametrów, możliwa wartość NULL, typy parametrów): Procedury i funkcje DESC[RIBE] wyliczanka; Pakiety Podsumowanie Źródła Kod procedury dostępny w perspektywach użytkownika: SELECT text FROM user_source WHERE name = 'WYLICZANKA' and type = 'PROCEDURE'; Systemy baz danych - wykład IV 13/38 Wywołanie procedury Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje BEGIN wyliczanka(5); END; CALL wyliczanka(5); Pakiety Podsumowanie EXEC[UTE] wyliczanka(5); Źródła Systemy baz danych - wykład IV 14/38 Tworzenie i wywoływanie procedur - przykład I Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych CREATE OR REPLACE PROCEDURE WITAJ(imie VARCHAR2 DEFAULT 'Jan', wiek NUMBER := '12') AS BEGIN DBMS_OUTPUT.PUT_LINE('Witaj '||imie||'. Masz '||wiek||' lat.'); END; / Parametry Procedury i funkcje Pakiety Podsumowanie EXECUTE WITAJ('Robert', 12); CALL WITAJ('Maria', 24); CALL WITAJ(imie=>'Maciek', wiek=>32); EXECUTE WITAJ(wiek=>89, imie=>'Teresa'); Źródła EXEC WITAJ(imie=>'Michał'); EXEC WITAJ(wiek=>45); CALL WITAJ(); Systemy baz danych - wykład IV 15/38 Tworzenie i wywoływanie procedur - przykład II Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie Źródła CREATE OR REPLACE PROCEDURE placa_pracownika ( p_nr_akt IN pracownicy.nr_akt%TYPE, p_nazwisko OUT pracownicy.nazwisko%TYPE, p_placa OUT pracownicy.placa%TYPE) IS BEGIN SELECT nazwisko, placa INTO p_nazwisko, p_placa FROM pracownicy WHERE nr_akt = p_nr_akt; END placa_pracownika; / Systemy baz danych - wykład IV 16/38 Wywoływanie procedur Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie DECLARE v_nazwisko pracownicy.nazwisko%TYPE; v_placa pracownicy.placa%TYPE; BEGIN placa_pracownika(9120, v_nazwisko, v_placa); DBMS_OUTPUT.PUT_LINE('Pracownik '|| v_nazwisko || ' zarabia '|| v_placa ); END; Źródła Systemy baz danych - wykład IV Pracownik RYBAK zarabia 1800 17/38 Tworzenie funkcji Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie Źródła CREATE [OR REPLACE] FUNCTION nazwa [( par1 [ IN | OUT | IN OUT ] typ [, par2 [ IN | OUT | IN OUT ] typ, ...] )] RETURN typ {IS|AS} [deklaracje;] BEGIN instrukcje; RETURN wyrazenie; [EXCEPTION obsluga_wyjatkow;] END [nazwa]; Systemy baz danych - wykład IV 18/38 Tworzenie funkcji - przykład Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie Źródła CREATE OR REPLACE FUNCTION mail (p_nr NUMBER) RETURN VARCHAR2 AS v_mail VARCHAR2(50); BEGIN SELECT Lower(nazwisko)||'@'||Lower(nazwa)|| '.firma.pl' INTO v_mail FROM pracownicy NATURAL JOIN dzialy WHERE nr_akt = p_nr; RETURN v_mail; END mail; Systemy baz danych - wykład IV 19/38 Wywołanie funkcji Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry DECLARE v_mail VARCHAR2(50); BEGIN v_mail := mail(9120); dbms_output.put_line(v_mail); END; Procedury i funkcje Pakiety SELECT mail(nr_akt) FROM pracownicy; Podsumowanie Źródła VARIABLE b_mail VARCHAR2(50); EXECUTE :b_mail := mail(9120); PRINT b_mail; Systemy baz danych - wykład IV 20/38 Wywołanie funkcji użytkownika w SQL Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety W klauzulach SELECT, ORDER BY, GROUP BY. W wyrażeniach warunkowych klauzul WHERE i HAVING. W klauzuli VALUES instrukcji INSERT. W klauzuli SET instrukcji UPDATE. Podsumowanie Źródła Systemy baz danych - wykład IV 21/38 Ograniczenia wywołania funkcji użytkownika w SQL Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie Źródła Funkcje muszą być przechowywane w bazie danych. Używają tylko parametrów w trybie IN oraz typów danych dostępnych w SQL. Nie mogą zawierać parametrów o typach z PL/SQL. Zwracają poprawny typ danych z SQL (nie z języka PL/SQL). Funkcje wywoływane w zapytaniu SELECT nie moga zawierać instrukcji DML. Instrukcje UPDATE albo DELETE na tabeli T nie mogą wywoływać czy zawierać funkcji z operacjami DML na tej samej tabeli T. Systemy baz danych - wykład IV 22/38 Zalety funkcji i procedur użytkownika Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Zwiększają bezpieczeństwo i integralność danych. Zwiększają wydajność. Zwiększają czytelność kodu. Potrafią rozszerzyć SQL w tych miejscach, gdzie czynności w SQL są zbyt złożone, skomplikowane albo wręcz niemożliwe do zrealizowania. Podsumowanie Źródła Potrafią manipulować na danych. Systemy baz danych - wykład IV 23/38 Pakiety Systemy baz danych wykład IV dr inż. Robert Perliński Pakiet Parametry - zbiór logicznie powiązanych zmiennych, stałych, kursorów, wyjatków, procedur, funkcji, itp., tworzących jeden nazwany, przechowywany trwale w bazie obiekt. Procedury i funkcje Składowe pakietu: Wprowadzenie do bloków nazwanych Pakiety Podsumowanie Źródła specyfikacja (publiczny interfejs) - wszystkie deklaracje dostępne z zewnątrz pakietu dla innych aplikacji, ciało (prywatna implementacja) - ukryte, opcjonalne. Systemy baz danych - wykład IV 24/38 Zalety używania pakietów Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Budowa modułowa: hermetyzacja (kapsułkowanie) odpowiednich konstruktorów. Przechowywanie razem logicznie powiązanej funkcjonalności. Procedury i funkcje Prostsze projektowanie i implementacja dzięki osobnej kompilacji specyfikacji i ciała pakietu. Pakiety Ukrywanie informacji. Podsumowanie Zwiększona funkcjonalność. Parametry Źródła Większa wydajność. Można przeciążać funkcje i procedury pakietu. Systemy baz danych - wykład IV 25/38 Tworzenie pakietu Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Specyfikacja pakietu: CREATE [OR REPLACE] PACKAGE nazwa {IS | AS} publiczne_deklaracje; specyfikacja_podprogramow; END [nazwa]; Parametry Procedury i funkcje Pakiety Podsumowanie Źródła Ciało pakietu: CREATE [OR REPLACE] PACKAGE BODY nazwa {IS | AS} prywatne_deklaracje; definicje_podprogramow; [BEGIN instrukcje_inicjalizujace;] END [nazwa]; Systemy baz danych - wykład IV 26/38 Specyfikacja pakietu - przykład Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie Źródła CREATE OR REPLACE PACKAGE PLACE AS FUNCTION suma RETURN NUMBER; FUNCTION suma_w_dziale(p_id NUMBER) RETURN NUMBER; FUNCTION srednia RETURN NUMBER; FUNCTION srednia_w_dziale(p_id NUMBER) RETURN NUMBER; PROCEDURE podwyzka(p_kwota NUMBER); PROCEDURE podwyzka_w_dziale(p_kwota NUMBER, p_id NUMBER); END PLACE; Systemy baz danych - wykład IV 27/38 Ciało pakietu - przykład Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie Źródła CREATE OR REPLACE PACKAGE BODY PLACE AS FUNCTION suma RETURN NUMBER AS v_sum NUMBER; BEGIN SELECT Sum(placa+Nvl(dod_funkcyjny,0)+Nvl(prowizja,0)) INTO v_sum FROM pracownicy; RETURN v_sum; END suma; FUNCTION suma_w_dziale(p_id NUMBER) RETURN NUMBER AS v_sum NUMBER; BEGIN SELECT Sum(placa+Nvl(dod_funkcyjny,0)+Nvl(prowizja,0)) INTO v_sum FROM pracownicy WHERE id_dzialu = p_id; RETURN v_sum; END suma_w_dziale; FUNCTION srednia RETURN NUMBER AS v_ave NUMBER; BEGIN SELECT Avg(placa) INTO v_ave FROM pracownicy; RETURN v_ave; END srednia; ... END PLACE; Systemy baz danych - wykład IV 28/38 Przeładowywanie nazw Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie Pozwala na utworzenie dwóch lub więcej podprogramów (procedur lub funkcji) o tej samej nazwie. Wymaga, żeby parametry formalne podprogramu różniły się co do liczby, kolejności, albo rodziny typu. Dostarcza sposób na rozszerzenie funkcjonalności bez utraty istniejącego kodu. Wystarczy dodać parametr do istniejącego podprogramu. Źródła Systemy baz danych - wykład IV 29/38 Przeładowanie nazw Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych PROCEDURE podwyzka(p_kwota NUMBER) AS BEGIN UPDATE pracownicy SET placa = placa + p_kwota; END podwyzka; Parametry Procedury i funkcje Pakiety Podsumowanie Źródła PROCEDURE podwyzka(p_kwota NUMBER, p_komu NUMBER) AS BEGIN UPDATE pracownicy SET placa = placa + p_kwota WHERE nr_akt = p_komu; END podwyzka; Systemy baz danych - wykład IV 30/38 Korzystanie z pakietu Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie BEGIN dbms_output.put_line(place.suma()); END; SELECT id_dzialu, place.suma_w_dziale(id_dzialu) FROM pracownicy; Źródła Systemy baz danych - wykład IV 31/38 Usuwanie pakietu Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Usuwanie całego pakietu (specyfikacji i ciała): DROP PACKAGE PLACE; Parametry Procedury i funkcje Usuwanie tylko ciała pakietu: Pakiety Podsumowanie DROP PACKAGE BODY PLACE; Źródła Systemy baz danych - wykład IV 32/38 Przeglądanie pakietu Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Wyświetlenie specyfikacji i ciała pakietu PLACE: SELECT text FROM user_source WHERE name = 'PLACE' AND type = 'PACKAGE' ORDER BY line; Procedury i funkcje Pakiety Podsumowanie Źródła SELECT text FROM user_source WHERE name = 'PLACE' AND type = 'PACKAGE BODY' ORDER BY line; Systemy baz danych - wykład IV 33/38 Pakiety dostarczone przez Oracle I Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie Źródła Dostarczone razem z serwerem Oracle. Rozszerzają funkcjonalność bazy danych. Pozwalają na dostęp do niektórych cech SQL, które normalnie są zarezerwowawne dla PL/SQL. Przykładowe pakiety: DBMS_OUTPUT - pozwala na wysyłanie wiadomości z własnych podprogramów PL/SQL. Szczególnie przydatne w debugowaniu kodu. UTL_FILE - pozwala na odczyt i zapis w plikach tekstowych systemu operacyjnego. UTL_MAIL - pozwala na tworzenie i wysyłanie wiadomości email. Systemy baz danych - wykład IV 34/38 Pakiety dostarczone przez Oracle II Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie Źródła DBMS_ALERT - pozwala na asynchroniczne informowanie o zdarzeniach bazy danych. DBMS_LOCK - udostępnia interfejs do usługi zarządzania blokadami Oracle (utworznie blokady, nadanie jej nazwy, zmiana jej rodzaju, usunięcie). HTF (hypertext functions), HTP (hypertext procedures) pozwalają na generowanie znaczników HTML, np. HTF.ANCHOR generuje znacznik <a>. DBMS_SCHEDULER - umożliwia tworzenie harmonogramu i automatyczne uruchamianie bloków kodu PL/SQL, procedur składowanych i zwenętrznych czy innych programów wykonywalnych. Systemy baz danych - wykład IV 35/38 Fazy przetwarzania instrukcji SQL Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Parsowanie - sprawdzanie składni instrukcji i zatwierdzenie jej poprawności, sprawdzenie poprawności wszystkich referencji i posiadania dostatecznych uprawnień. Wiązanie - dołączenie do instrukcji wartości zmiennych dowiązanych, jeśli takie w niej występują. Wykonanie - ostatnia faza dla instrukcji nie zwracających danych. Pobranie - (dla zapytań) wybranie wierszy, które mają być zwracane, posortowanie ich i zwrócenie jeden po drugim. Podsumowanie Źródła Dla wbudowanych instrukcji SQL parsowanie i wiązanie są wykonywane w czasie kompilacji. Dla dynamicznego SQLa wszystkie fazy są wykonywane w czasie działania programu. Systemy baz danych - wykład IV 36/38 Podsumowanie Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie Źródła PL/SQL umożliwia: definiowanie funkcji i procedur specyficznych dla danego projektu, zmniejszenie przesyłanych danych przez sieć poprzez przeniesienie części logiki biznesowej na stronę serwera i bazy danych, przenoszenie kodu pomiędzy różnymi platformami, tworzenie własnych i wykorzystanie istniejących pakietów i bibliotek. Systemy baz danych - wykład IV 37/38 Źródła Systemy baz danych wykład IV dr inż. Robert Perliński Wprowadzenie do bloków nazwanych Parametry Procedury i funkcje Pakiety Podsumowanie Źródła W wykładzie wykorzystano materiały: http://www.techonthenet.com/oracle/ M. Lentner, Oracle 9i Kompletny podręcznik użytkownika, PJWSTK - W-wa, 2003 http://docs.oracle.com/cd/B28359_01/appdev. 111/b28370/triggers.htm http://docs.oracle.com/cd/B28359_01/appdev. 111/b28370/create_trigger.htm Systemy baz danych - wykład IV 38/38