1 / 68

Procedury i funkcje

Procedury i funkcje. Tworzenie:. CREATE PROCEDURE nazwa ([proc_parameter[,...]]) [characteristic ...] routine_body CREATE FUNCTION nazwa ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body. proc_parameter: [ IN | OUT | INOUT ] param_name type func_parameter:

teal
Download Presentation

Procedury i funkcje

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. Procedury i funkcje Tworzenie: CREATE PROCEDUREnazwa ([proc_parameter[,...]]) [characteristic ...] routine_body CREATE FUNCTIONnazwa ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type func_parameter: param_name type

  2. Prosty przykład: CREATE PROCEDURE przecena (IN nazwa varchar (64)) UPDATE nieruchomosc SET czynsz = czynsz*1.2 WHERE miasto = nazwa;

  3. Wywołanie procedury: CALL przecena('Augustów');

  4. Schemat bazy Biuro Nieruchomości:

  5. Zadanie: Zrealizować procedurę, która będzie zwiększać płacę pracowników o wartość podaną przy wywołaniu jako dana wejściowa dla stanowiska też podanego przy wywołaniu. CREATE PROCEDURE podwyzka (IN ile int(4), IN jaki varchar (15)) UPDATE personel SET pensja = pensja+ile WHERE stanowisko = jaki; CALL podwyzka(50, 'asystent');

  6. Przykład procedury losującej uczniów do klas: DELIMITER | CREATE PROCEDURE UtworzUczniow(IN r INT, IN kl CHAR, IN chlopcy INT, IN dziewczynki INT) BEGIN DECLARE dz INT; DECLARE ch INT; DECLARE rchar CHAR(11); CASE r WHEN 1 THEN SET rchar='1992-01-01'; WHEN 2 THEN SET rchar='1991-01-01'; WHEN 3 THEN SET rchar='1990-01-01'; ELSE SELECT 'ERROR - ZLY ROK'; END CASE;

  7. SET dz=0; WHILE (dz < dziewczynki) DO INSERT INTO Uczniowie VALUES (0, (SELECT nazwisko FROM __Nazwiska ORDER BY rand() LIMIT 1), (SELECT imie FROM __Imiona WHERE plec='K' ORDER BY rand() LIMIT 1), (SELECT rchar - INTERVAL rand()*365+1 DAY),'K', (SELECT id_klasy FROM Klasy WHERE rok=r AND klasa=kl)); SET dz=dz+1; END WHILE;

  8. SET ch=0; WHILE (ch < chlopcy) DO INSERT INTO Uczniowie VALUES (0, (SELECT nazwisko FROM __Nazwiska ORDER BY rand() LIMIT 1), (SELECT imie FROM __Imiona WHERE plec='M' ORDER BY rand() LIMIT 1), (SELECT rchar - INTERVAL rand()*365+1 DAY),'M', (SELECT id_klasy FROM Klasy WHERE rok=r AND klasa=kl)); SET ch=ch+1; END WHILE; END| DELIMITER ;

  9. Nazwiska: CREATE TABLE __Nazwiska ( id_nazwiska INT UNSIGNED NOT NULL auto_increment, nazwisko char(30) NOT NULL, UNIQUE id(id_nazwiska), PRIMARY KEY (id_nazwiska) ); INSERT INTO __Nazwiska VALUES (0,'Ancypo'),(0,'Arciuch'),(0,'Awdziej'),(0,'Chwiedzko'),(0,'Cychowska'), (0,'Czaplejewicz'),(0,'Filonowicz'),(0,'Filon'),(0,'Kuzbiel'),(0,'Gryszkiewicz'), (0,'Jackiewicz'),(0,'Jurgiel'),(0,'Kazimierowicz'),(0,'Kostera'),(0,'Kostro'), (0,'Nowak'),(0,'Krawiel'),(0,'Krupowicz'),(0,'Mizer'),(0,'Mrozowicz'), (0,'Mucus'),(0,'Niedzwiedz'),(0,'Ostasiewicz'),(0,'Surowiec'),(0,'Anisko'), (0,'Antoniuk'),(0,'Borowik'),(0,'Chodziutko'),(0,'Gieniusz'),(0,'Grygucis'), (0,'Gryszko'),(0,'Jurgiel'),(0,'Karpowicz'),(0,'Kargul'),(0,'Pawlak'), (0,'Marcinkiewicz'),(0,'Miller'),(0,'Sidor'),(0,'Siemianczuk'),(0,'Misiukiewicz'), (0,'Szymczyk'),(0,'Taudul'),(0,'Zapolnik'),(0,'Klej'),(0,'Kucharewicz'), (0,'Chlus'),(0,'Cilulko'),(0,'Puszko'),(0,'Loszczyk'),(0,'Makarewicz'), (0,'Solniczek'),(0,'Szczesnowicz'),(0,'Szyszko'),(0,'Werda'),(0,'Sietejko'), (0,'Sawon'),(0,'Smola'),(0,'Sloma'),(0,'Sacharczuk'),(0,'Miszkin'), (0,'Baszko'),(0,'Bilkiewicz'),(0,'Czaplejewicz'),(0,'Osial'),(0,'Borys'), (0,'Salak'),(0,'Zajczyk'),(0,'Lazarewicz'),(0,'Rudnik'),(0,'Recko'), (0,'Szczesiul'),(0,'Luba'),(0,'Mroczko'),(0,'Abramowicz'),(0,'Beczko'), (0,'Bleczko'),(0,'Butkiewicz'),(0,'Daszkilewicz'),(0,'Galuzyn'),(0,'Gryc'), (0,'Gudel'),(0,'Toczko'),(0,'Malkiewicz'),(0,'Potapa'),(0,'Pupek'), (0,'Prycz'),(0,'Koleda'),(0,'Gniedziejko'),(0,'Juchniewicz'),(0,'Lengiewicz'), (0,'Turko'),(0,'Kulak'),(0,'Dudziuk'),(0,'Trochanowicz'),(0,'Stefanczuk'), (0,'Lopata'),(0,'Loskot'),(0,'Matuk'),(0,'Kijek'),(0,'Woronko'), (0,'Romanowicz'),(0,'Pylko'),(0,'Misarko'),(0,'Waluszko'),(0,'Toloczko'), (0,'Pigiel'),(0,'Stupak'),(0,'Zdanuk'),(0,'Mackiewicz'),(0,'Hecman') ;

  10. Imiona: CREATE TABLE __Imiona ( id_imienia INT UNSIGNED NOT NULL auto_increment, imie char(20) NOT NULL, plec ENUM('K','M'), UNIQUE id(id_imienia), PRIMARY KEY (id_imienia) ); INSERT INTO __Imiona VALUES (0,'Lukasz','M'),(0,'Piotr','M'),(0,'Raroslaw','M'),(0,'Jacek','M'),(0,'Andrzej','M'), (0,'Maciej','M'),(0,'Zbigniew','M'),(0,'Leszek','M'),(0,'Kamil','M'),(0,'Pawel','M'), (0,'Wojciech','M'),(0,'Bartosz','M'),(0,'Jakub','M'),(0,'Marcin','M'),(0,'Rafal','M'), (0,'Adam','M'),(0,'Daniel','M'),(0,'Robert','M'),(0,'Boromir','M'),(0,'Slawomir','M'), (0,'Mateusz','M'),(0,'Marek','M'),(0,'Przemyslaw','M'),(0,'Mariusz','M'),(0,'Grzegorz','M'), (0,'Marzena','K'),(0,'Sylwia','K'),(0,'Edyta','K'),(0,'Urszula','K'),(0,'Malgorzata','K'), (0,'Justyna','K'),(0,'Magdalena','K'),(0,'Agnieszka','K'),(0,'Joanna','K'),(0,'Anna','K'), (0,'Monika','K'),(0,'Ewa','K'),(0,'Paulina','K'),(0,'Marta','K'),(0,'Jolanta','K'), (0,'Katarzyna','K'),(0,'Natalia','K'),(0,'Agata','K'),(0,'Elzbieta','K'),(0,'Celina','K'), (0,'Beata','K'),(0,'Karolina','K'),(0,'Dorota','K'),(0,'Izabela','K'),(0,'Wioletta','K') ;

  11. Wywołania: call UtworzUczniow(3,'a',10,19); call UtworzUczniow(3,'b',6,24); call UtworzUczniow(3,'c',19,11); call UtworzUczniow(3,'d',14,13); call UtworzUczniow(3,'e',6,25); call UtworzUczniow(3,'f',7,20): call UtworzUczniow(2,'a',9,22); call UtworzUczniow(2,'b',18,8); call UtworzUczniow(2,'c',22,8); call UtworzUczniow(2,'d',13,17); call UtworzUczniow(2,'e',7,23); call UtworzUczniow(2,'f',3,26); call UtworzUczniow(1,'a',6,25); call UtworzUczniow(1,'b',20,10); call UtworzUczniow(1,'c',20,8); call UtworzUczniow(1,'d',16,15); call UtworzUczniow(1,'e',7,24); call UtworzUczniow(1,'f',7,25);

  12. Przykład funkcji: mysql> CREATE FUNCTION Witajcie (s CHAR(20)) RETURNS CHAR(50) -> RETURN CONCAT('Witajcie, ',s,'!'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT Witajcie('kochani'); +---------------------+ | Witajcie('kochani') | +---------------------+ | Witajcie, kochani! | +---------------------+ 1 row in set (0.00 sec)

  13. IF: IF warunek THEN instrukcje [ELSEIF warunek THEN instrukcje] ... [ELSE instrukcje] END IF

  14. Wyzwalacze: CREATE TRIGGER nazwa trigger_time trigger_event ON tabela FOR EACH ROW trigger_stmt Wyzwalacz nazwa związany z tabelą tabela. trigger_time - określenie czasowe wykonania, może być:BEFORElubAFTERinstrukcji, która go aktywuje trigger_stmt – instrukcje wykonywane po aktywacji wyzwalacza trigger_event – instrukcje, które aktywują wyzwalacz

  15. trigger_event – instrukcje, które aktywują wyzwalacz: • INSERT: wyzwalacz jest aktywowany gdy tylko nowy wiersz jest wstawiany do tabeli; np. przez INSERT, LOAD DATA, czy REPLACE. • UPDATE: wyzwalacz jest aktywowany gdy tylko wiersz jest uaktualniany; np. przez UPDATE. • DELETE: wyzwalacz jest aktywowany gdy tylko wiersz jest usuwany z tabeli; np. przez DELETE czy REPLACE • Jednakże DROP TABLE nie aktywuje wyzwalacza

  16. Przykład:

  17. Aktywacja wyzwalacza:

  18. Wyniki:

  19. Cechy MySQL w wersjach:

  20. Transakcje: Transakcje w systemach zarządzania bazą danych stanowi sekwencje powiązanych ze sobą instrukcji, które muszą być traktowane jak pojedyncza jednostka. Rozumiemy to tak, że albo wszystkie zadania wchodzące w skład transakcji zostaną wykonane, albo żadne z nich. Transakcja jest niepodzielna, ponieważ przy równoczesnym dostępie do zasobów przez wielu użytkowników lub trwających procesach, w czasie awarii może dojść do niespójności bazy danych. Ważną cechą transakcji jest to, że są one nie widoczne dla innych sesji jeżeli nie zostaną wykonane. Nikt nie może odczytać danych z tabel, jeżeli jesteśmy w czasie ich aktualizacji

  21. MySQL oferuje transakcje zgodne z tzw. właściwościami ACID: Atomicity ( niepodzielność) – nie ma możliwości podziału transakcji, albo wszystkie zmiany zostaną zachowane w bazie danych albo żadna z nich Consistency (spójność) – oznacza, że operacje przekształcają bazę danych z jednego stanu prawidłowego w inny. Nie powinny być możliwe etapy, kiedy dane są niespójne. Isolation (izolacja) – oznacza, że transakcje nie kolidują ze sobą, gdy są wykonywane. Każda transakcja powinna być wykonywana tak, jakby w danej chwili tylko ona mogła wykonywać operacje odczytu i aktualizacji. Durability (trwałość) – oznacza, że po wykonaniu transakcji w bazie danych, efekty transakcji są stałe.

  22. START TRANSACTION [WITH CONSISTENT SNAPSHOT] | BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET AUTOCOMMIT = {0 | 1} Transakcje rozpoczynamy wydając polecenie START TRANSACTION lub jego synonimy BEGIN WORK oraz BEGIN. Zalecane jest używanie tej pierwszej postaci, gdyż jest ona zgodna z norma SQL-99.

  23. MySQL może pracować w dwóch trybach. Pierwszy, zwany autocommit, powoduje, że każde wydawane polecenie jest traktowana jako transakcja. Oznacza to, ze wynik działania tego polecenia jest automatycznie zatwierdzany w bazie danych i nie ma możliwości jego wycofania. Aby wyłączyć działanie trybu autocommit należy wydąć polecenie: SET AUTOCOMMIT = 0; Wówczas można samodzielnie wydawać polecenia START TRANSACTION, COMMIT oraz ROLLBACK. Pierwsze z nich rozpoczyna nową transakcje, drugie zatwierdza wszystkie zmiany wprowadzone w ramach bieżącej transakcji. Ostatnie natomiast polecenie wycofuje wszystkie zmiany wprowadzone w ramach bieżącej transakcji.

  24. Przykład:

  25. Po wykonaniu instrukcji SET AUTOCOMMIT = 0, MySQL zakłada, ze od tej pory wszystkie transakcje będą przez użytkownika jawnie kończone poleceniem COMMIT. Należy o tym zawsze pamiętać, bo gdy o tym zapomnimy i zakończymy sesje (np. zamykając aplikacje kliencką), MySQL automatycznie wycofa wszystkie zmiany wprowadzone w czasie całej sesji! Ponowny powrót do trybu autocommit spowoduje polecenie: SET AUTOCOMMIT = 1; Sprawdzamy jaki tryb zatwierdzania transakcji obowiązuje:

  26. Przykład z wycofaniem:

  27. Przykład z zakleszczeniem:

  28. Zakleszczanie bez klucza głównego:

  29. Informacje o statusie tabeli i jej powiązaniach:

  30. INFORMATION_SCHEMA: Zapewnia dostęp do metadanych bazy danych. Metadane to dane o danych (definicja struktury danych), takie jak nazwa bazy danych lub tabeli, typ danych kolumny lub przywileje dostępu. Nazywany czasami słownikiem danych lub katalogiem systemowym. INFORMATION_SCHEMAto ''baza informacyjna'', gdzie magazynuje się informacje o wszystkich bazach danych, które serwer MySQL utrzymuje. Zawiera kilka tabel (tymczasowych), które są tylko do odczytu. Każdy użytkownik MySQL ma prawo dostępu do tych tabel, ale tylko do wierszy związanych z obiektami, do których użytkownik posiada właściwe przywileje dostępu. Struktura tabel INFORMATION_SCHEMA stosuje się do standardu ANSI/ISO SQL:2003 część 11 ''Schematy''. Jest dostępny od wersji MySQL 5.0.2.

  31. mysql> show tables; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | ENGINES | | EVENTS | | FILES | | GLOBAL_STATUS | | GLOBAL_VARIABLES | | KEY_COLUMN_USAGE | | PARTITIONS | | PLUGINS | | PROCESSLIST | | REFERENTIAL_CONSTRAINTS | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | | SESSION_STATUS | | SESSION_VARIABLES | | STATISTICS | | TABLES | | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS | | USER_PRIVILEGES | | VIEWS | +---------------------------------------+ 27 rows in set (0.05 sec) Pełna lista tabel Information_schema:

  32. Wybrane informacje o tabelach:

  33. Informacje z komentarzami dla tabel bazy biuro:

  34. Informacje z komentarzami dla tabel bazy mysql:

  35. Wszystkie informacje dla tabeli personel bazy biuro:

  36. Informacje o schematach:

  37. Rozszerzone informacje o kolumnach:

  38. Cd.

More Related