1 / 25

Zsbd PL/SQL część 3

Zsbd PL/SQL część 3. Wykład 5 Prowadzący: dr Paweł Drozda. Program wykładu. Pakiety Wyzwalacze. 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

orinda
Download Presentation

Zsbd PL/SQL część 3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related