Procedury i funkcje

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:

20. Aktywacja wyzwalacza:

21. Wyniki:

23. Cechy MySQL w wersjach:

24. 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

25. 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.

26. 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.

27. 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.

28. Przykład:

29. 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:

30. Przykład z wycofaniem:

32. Przykład z zakleszczeniem:

36. Zakleszczanie bez klucza głównego:

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

40. 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.

41. 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:

42. Wybrane informacje o tabelach:

45. Informacje z komentarzami dla tabel bazy biuro:

46. Informacje z komentarzami dla tabel bazy mysql:

47. Wszystkie informacje dla tabeli personel bazy biuro:

48. Informacje o schematach:

49. Rozszerzone informacje o kolumnach:

50. Cd.