Zsbd PL/SQL część 3

1. ZsbdPL/SQL część 3 Wykład 5 Prowadzący: dr Paweł Drozda

2. Program wykładu • Pakiety • Wyzwalacze dr P. Drozda

3. Pakiety • Obiekt logiczny schematu bazy danych grupujący logicznie powiązane elementy PL/SQL – typy, zmienne, podprogramy (procedury, funkcje) • Skład pakietu: • Specyfikacja • Zawartość/ciało • Specyfikacja zawiera deklaracje typów, zmiennych, stałych, kursorów podprogramów, do których można się odwoływać z zewnątrz pakietu (zadeklarowane elementy są publiczne) • Zawartość zawiera instrukcje dla kursorów i podprogramów zadeklarowanych w specyfikacji oraz może definiować dodatkowe elementy niewidoczne na zewnątrz • Pozwala wiele elementów załadować na raz dr P. Drozda

4. Zalety pakietów • Zgrupowane logicznie powiązane elementy w jednym miejscu • Ukrycie informacji • Z zewnątrz widoczna specyfikacja • Konstrukcje w zawartości niedostępne do wglądu • Kompilacja oddzielnie specyfikacji i zawartości • Trwałość danych publicznych – zmienne, kursory – przez całą sesję użytkownika • Lepsza wydajność – cały pakiet wczytywany jest raz dla wszystkich użytkowników • Przeciążanie procedur, funkcji dr P. Drozda

5. Elementy deklaracji pakietów Specyfikacja Zmienna; Procedura A deklaracja; Funkcja B deklaracja; … Publiczny Zawartość Zmienna1; Procedura C deklaracja … Procedura A deklaracja BEGIN …; END; Funkcja B deklaracja BEGIN … END; Prywatny dr P. Drozda

6. Widoczność elementów w pakietach Specyfikacja Zmienna; Procedura A deklaracja; Funkcja B deklaracja; … • Zmienna widoczna wszędzie • Zmienna1 widoczna tylko w obrębie Zawartości • Zmienna2 widoczna tylko w procedurze A • Procedura C widoczna tylko wewnątrz Zawartości – może być wykorzystana w procedurze A i funkcji C Zawartość Zmienna1; Procedura C deklaracja … Procedura A deklaracja BEGIN Zmienna2; …; END; Funkcja B deklaracja BEGIN … END; dr P. Drozda

7. Tworzenie pakietów – składnia • CREATE [OR REPLACE] PACKAGE nazwa AS|IS deklaracja typów publicznych; deklaracja zmiennych; specyfikacja podprogramów; END [nazwa]; CREATE [OR REPLACE] PACKAGE BODY nazwa AS|IS odpowiednie deklaracje, tworzenie zawartości podprogramów; • Zmienne domyślnie inicjowane na NULL • Wszystkie elementy zadeklarowane widoczne są dla użytkowników mających prawa dostępu do pakietu dr P. Drozda

8. Przykład – tworzenie pakietu CREATE OR REPLACE PACKAGE pracownicy IS numer NUMBER:= 111; CURSOR nazwisko IS SELECT last_name FROM employees; PROCEDURE nazwisko (id number); FUNCTION zarobki (id number) RETURN NUMBER; END pracownicy; dr P. Drozda

9. Przykład ciąg dalszy CREATE PACKAGE BODY Pracownicy IS Jakaszmienna VARCHAR2(2); PROCEDURE nazwisko (id number) IS nameemployees.last_name%TYPE; BEGIN SELECT last_name INTO name FROM employees WHERE employee_id = id; DBMS_OUTPUT.PUT_LINE(last_name); END nazwisko; … END Pracownicy; dr P. Drozda

10. Przykład pakietu bez zawartości CREATE PACKAGE stale IS km2mile CONSTANT NUMBER := 1.6093; mile2km CONSTANT NUMBER := 0.6214; jard2metr CONSTANT NUMBER := 0.9144; metr2jard CONSTANT NUMBER := 1.0936; END stale; CREATE OR REPLACE FUNCTION zamiana(km NUMBER) RETURN NUMBER IS BEGIN RETURN (km*stale.km2mile); END; dr P. Drozda

11. Wywołanie elementów pakietu • Z linii komend • EXECUTE nazwapakietu.nazwaElementu; • EXECUTE Pracownicy.nazwisko(100); • Gdy ze schematu innego użytkownika • EXECUTE HR.Pracownicy.nazwisko(100); • Wewnątrz bloku • Tak samo jak wywołanie normalne elementów – tylko poprzedzone nazwą pakietu do którego należą; gdy pakiet z innego schematu – dodatkowo na początku nazwa schematu dr P. Drozda

12. Przeglądanie, usuwanie pakietów • Informacje o pakietach znajdują się w user_source • SELECT text FROM user_source WHERE type=‘PACKAGE’ | ‘PACKAGE BODY’; • DROP PACKAGE nazwa; • DROP PACKAGE BODY nazwa; dr P. Drozda

13. Reguły tworzenia pakietów • Specyfikacja pakietu tworzona przed zawartością • Odwołanie w podprogramie do innego elementu (np. w procedurze do funkcji) możliwe tylko, gdy ta funkcja została wcześniej zadeklarowana (jeśli publiczna – nie ma problemu, jeśli prywatna trzeba pilnować) • Umieszczanie w specyfikacji tylko niezbędnych elementów (widocznych dla wszystkich) dr P. Drozda

14. Przeciążanie podprogramów • Możliwość zadeklarowania procedury/funkcji z tą samą nazwą więcej niż raz • Konieczność rozróżnienia za pomocą parametrów (ich liczby, rodzin typów lub kolejności) • Możliwość nadpisania lokalnych podprogramów, z pakietu • Nie można przeciążać: • Gdy parametry są w tej samej rodzinie typów (np. NUMBER i DECIMAL) • Gdy są podtypami tej samej rodziny (VARCHAR i STRING są podtypami VARCHAR2) • Gdy funkcje różnią się tylko zwracanym typem dr P. Drozda

15. Przykład przeciążania CREATE OR REPLACE PACKAGE dept IS PROCEDURE add_dept(id NUMBER, name VARCHAR2 :=‘edu’, location VARCHAR); PROCEDURE add_dept(name VARCHAR2); END dept; dr P. Drozda

16. Pakiet wbudowany STANDARD • Zawiera najczęściej wykorzystywane funkcje (np. ROUND, TO_CHAR, NVL, LENGTH, etc.) • Funkcje wywoływane bez konieczności poprzedzania nazwą pakietu • Gdy jakaś funkcja zostanie przeciążona – konieczność odwołania poprzez nazwę pakietu dr P. Drozda

17. Blok inicjalizacyjny pakietu • Na koniec zawartości można dodać blok wykonywany raz • Uruchomiany raz w momencie ładowania pakietu do sesji użytkownika • Służy do dokładniejszej inicjalizacji elementów pakietu • Przykład: CREATE PACKAGE inic IS kasa NUMBER; … CREATE PACKAGE BODY inic IS … BEGIN SELECT salary INTO kasa FROM employees WHERE employee_id =120; END inic; dr P. Drozda

18. Wyzwalacze • Bloki składowane w bazie, które są uruchomiane w momencie wystąpienia jakiegoś zdarzenia • Wyzwalacze definiowane są na perspektywie, tabeli, schemacie użytkownika bądź na całej bazie • Możliwości uruchomienia wyzwalacza: • Wystąpienie DML (INSERT, UPDATE, DELETE) • Wystąpienie DDL (CREATE, ALTER, DROP) • Wystąpienie operacji na bazie danych takich jak SERVERERROR, LOGON, LOGOFF, SHUTDOWN, STURTUP dr P. Drozda

19. Wyzwalacze dla DML • Możliwe instrukcje wywołujące wyzwalacz: • INSERT • UPDATE [OF kolumna] • DELETE • Może być więcej niż jedna instrukcja • INSERT OR DELETE • INSERT OR UPDATE OR DELETE • Zawartość wyzwalacza określa jakie akcje zostaną podjęte w momencie użycia wyzwalacza (może być blokiem, odwołaniem do procedur) dr P. Drozda

20. Tworzenie wyzwalacza – składnia CREATE TRIGGER nazwa BEFORE | AFTER | INSTEAD OF INSERT | UPDATE | DELETE ON nazwaObiektu [REFERENCING OLD AS old /NEW AS new] [FOR EACH ROW WHEN (condition)] [DECLARE] BEGIN … END; dr P. Drozda

21. Wyzwalacz przykład CREATE OR REPLACE TRIGGER godziny BEFORE INSERT ON employees BEGIN IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00') THEN RAISE_APPLICATION_ERROR(-20500,‘nie można nic wstawić przecież nie pracujesz'); END IF; END; dr P. Drozda

22. Odpalenie wyzwalacza raz/wiele • Gdy zapytanie operuje na jednym wierszu – trigger odpalany raz (obojętnie czy z opcją każdego wiersza czy bez opcji) np. INSERT INTO departments(department_id) VALUES (3); • Gdy zapytanie dotyczy wielu wierszy – z opcją FOR EACH ROW wyzwalacz odpalany wiele razy, bez opcji – tylko raz UPDATE employees SET salary=salary*1.2 WHERE department_id=50; dr P. Drozda

23. Przykład – różne instrukcje DML CREATE TRIGGER godziny BEFORE INSERT OR UPDATE OR DELETE ON employees BEGIN IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00') THEN IF INSERTING THEN RAISE_APPLICATION_ERROR(-20500,‘nic nie wstawic'); ELSE IF DELETING THEN RAISE_APPLICATION_ERROR(-20502,‘nie wyrzucaj'); ELSEIF UPDATING THEN RAISE_APPLICATION_ERROR(-20503,‘zostalo zmodyfikowane wczoraj’); END IF; END IF; END; dr P. Drozda

24. Wyzwalacz wierszowy - przykład CREATE TRIGGER widelki BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW BEGIN IF NOT (:NEW.job_id IN (‘AD_PRES’,’AD_VP’)) AND :NEW.SALARY >15000 THEN RAISE APPLICATION_ERROR(-20202, ‘za dużo chcesz zarabiać’); END; dr P. Drozda

25. Przykład z opcją WHEN CREATE TRIGGER zarobki BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW WHEN (NEW.job_id = ‘SA_REP’) BEGIN IF INSERTING THEN :NEW.commission_pct := 0; ELSEIF :OLD.commission_pct THEN :NEW.commission_pct := 0; ELSE :NEW.commission_pct :=:OLD.commission_pct +0.05; END; dr P. Drozda