Domaći

1. Domaći Matrične operacije u MySql-u korišćenjem ugrađenih procedura

2. Ograničenja U MySql-u je ograničenje u brojukolonaod 4096 (2^12) kolonapotabeli, aliefikasnimaksimummožebitimnogomanjizadatutabelu. Tačan limit zavisiodnekolikofaktora. 1. Svakatabela (bezobzirana storage engine) imamaksimalnuveličinuvrsteod 65,535 bytes. Storage engine možedodatnodaredukujeiograniči limit zamaksimalnudimenzijuvrste. 2. Maksimalnaveličinavrstezavisiioddefinicijetipapolja. Na primer, utf8 skupkarakterazahteva do 3 bytes pokarakteru, takoda se za CHAR(255) CHARACTER SET utf8 kolonu, naserverumoradaobezbediti 255 × 3 = 765 byte povrednosti. Imajućiovo u viduondatabela ne možedasadrživišeod 65,535/765=85 takvihkolona. • VARCHAR(255) CHARACTER SET utf8 kolonazauzima 2 byte zasmeštajdužinevrednosti, takodasvakavrednost ne možebitivećaod 767 byte. • BLOB i TEXT kolumnebrojeodjedan do četiri plus osambajtovasvakipremareduveličinegranicejersunjihovisadržajismešteniodvojeno.

3. Svakatablicaima. FRM datotekukojasadržitablicudefinicija. Server koristisledećeizrazzaproveruinformacije o nekojtabelismeštenoj u datoteci a vezanuzagornjugranicuod 64KB: if (info_length+(ulong) create_fields.elements*FCOMP+288+ n_length+int_length+com_length > 65535L || int_count > 255) • Deopodatakasmeštenih u .FRM datotekukoja se proveravapremaizrazu ne možedaporsteizvangraniceod 64KB granice, pa akodefinicijatabeleprevazilaziovugranicu, ne može se dodavativišekolona.

4. Uskladištene procedure -nastavak-

5. DECLARE HANDLER syntax DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement handler_action: CONTINUE | EXIT | UNDO condition_value: mysql_error_code | SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION - MySQL error code (broj) ili SQLSTATE vrednost (jestring od 5-karaktera). - Listu kodova možete naći na: http://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html

6. U našem primeru: DECLARE exit HANDLER FOR NOT FOUND SET p_poruka = 'Korisniknijepronadjen!'; DECLARE exit HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_poruka = 'Greska'; END;

7. Vršimodeklaraciju handler-a zaodređeniuslov/uzrok. • Kada dođe do zadatoguzroka, handler izvršavazadate naredbe. • Trenutnopostoje 2vrste handler-a: • CONTINUEiEXIT, • a osimnjih, tu je joši handler UNDO, kojiće bitipodržan u nekojodidućihverzijaMySQL-a.

8. AkozaodređeniuslovdefinišemoCONTINUE handler, ondaće izvršavanjebloka, u kojem se taj handlernalazi, biti nastavljeno bez prekida. • AkozaodređeniuslovdefinišemoEXIT handler, ondaće izvršavanjebloka, u kojem se taj handler nalazi, bitiprekinuto. • AkozaodređeniuslovdefinišemoUNDO handler, ondaće izvršavanjebloka, u kojem se taj handler nalazi,bitiprekinutoisveizmeneuzrokovane tom proceduromće bitiponištene (ROLLBACK).

9. Uslov (condition) može bitinavedenkaooznakanekeMySQLgreškei to u oblikukao: • SQLSTATEvrednost • predefinisanaoznakazanekeodgeneralnihuslova: • NOT FOUND, SQLWARNING ili SQLEXCEPTION • ručno (proizvoljno) deklarisanuslov, koji je vezanzaodređenostanje (SQLSTATE) ilinekuMySQLgresku • SQLWARNINGobuhvatasve SQLSTATE kôdove, kojizapočinjusa 01. • NOT FOUND obuhvatasve SQLSTATE kôdove, kojizapočinjusa 02. • SQLEXCEPTIONobuhvatasve SQLSTATE kôdove, kojinisuobuhvaćenisa SQLWARNING ili NOTFOUND.

10. U našemslučajusmozauslov NOT FOUND definisali handler EXIT. • To znači, daće uslov NOT FOUND bitiispunjen, ukoliko SELECT naredba u proceduri ne pronađe zadatogkorisnika. • Pošto tajuslovbudeispunjen, handler EXIT će bitiautomatskipozvan, pričemu će u izlaznuvarijablup_porukabiti smešten odgovarajućitekstinakon toga prekinutidaljeizvršavanje tog bloka.

11. Cursor • Da bi bili u mogućnosti da koristimo SELECT naredbu koja vraća više od jedne vrste,moramo da kreiramo cursor. • Cursorje objekat koji omogućava pristup rezultujućem skipu SELECT naredbe. • Koristimo cursor da bi se iterativno kretali kroz vrste rezultujućeg skupa i da bi izvršavali akcije nad svakom vrstom pojedinačno. • Trenutno MySQLdozvoljava samo da se krećemo u rezultujućem skupu od prve do poslednje vrste rezultata dobijenog SELECT naredbom. • Ne možemo ići od poslednjeg do prvog reda, niti možemo skočiti direkto na neki red rezultujućeg skupa. Definisanje Cursor-a DECLARE cursor_name CURSOR FOR SELECT_statement; Deklaracija cursor-amora da sledi posle deklaracije svih promenljivih. U suprotnom dolazi do greške 1337.

12. MySQLjezik za ugrađene procedure podržavatri naredbe za operacije sa cursor-om : OPEN • Inicijalizacije rezultujućeg skupa za cursor. Moramo da otvorimo kursor pre preuzimanja bilo kojeg reda iz kursora. Sintaksa je: OPEN cursor_name;FETCH • Preuzimanje sledećeg reda iz kursora i pomeranje kursor pokazivača na sledeći red u skupu rezultata.Sintaksa je: FETCH cursor_name INTO variable list; Lista promenljivih (variable list) mora da sadrži kompatibilan tip podataka za svaku kolonu vraćenu SELECT naredbom u cursor deklaraciji. CLOSE • Deaktivira cursor i oslobađa memoriju koju je zauzeo cursor. Sintaksa je: CLOSE cursor_name ; Treba da zatvorimo cursor kada završimo preuzimanja iz njega, ili kada trebamo da ponovo otvorimo cursor zbog promene vrednosti rezultujućeg skupa.

13. Primer 1. Preuzimanje vrednosti jedne vrste iz kursora OPEN cursor1; FETCH cursor1 INTO l_kupac_ime,l_kupac_prezime,l_kupac_adresa; CLOSE cursor1; Primer 2. Preuzimanje vrednosti kopletnog rezultujućeg skupa DECLARE c_dept CURSOR FOR SELECT department_id FROM departments; OPEN c_dept; dept_cursor: LOOP FETCH c_dept INTO l_dept_id; /*Uradi nešto sa preuzetomvrstom*/ END LOOP dept_cursor; CLOSE c_dept;

14. Iako se poslednji programski kod može činiti razumnim i potpunim, postoji problem! • Ako pokušavamo preuzeti podatke iz reda nakon poslednjeg koje je kursor preuzeo, MySQL će prikazati grešku ERROR 1329 (02000): No data to FETCH. • Da bi izbegli tu grešku, deklarišemo error handler koji će uhvatiti grešku "no data to fetch“ i lokalnoj promenljivoj dodeliti neku vrednost. • Onda ispitujemo tu promenljivu kako bi utvrdili da li smo došli do poslednjeg reda koji treba da hvata cursor.

15. DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_poslednji_red_preuzet=1; Ovaj rukovodilac događajima daje instrukcijuMySQL-uda mogu biti dva scenarija za "no data to fetch " : • Postavi vrednostu promenljivoj l_poslednji_red_preuzet =1. • Dozvoli programu da nastavi dalje sa izvršenjima. Naš program sada može da proveri vrednost l_poslednji_red_preuzet. Ako je postavljen na 1, onda znamo da je zadnji red je preuzet, i tada možemo prekinuti petlju i zatvoriti pokazivač. Vrlo je važno da po završetku vratimo promenljivu na 0.

16. Primer 2. (modifikovan)Preuzimanje vrednosti kopletnog rezultujućeg skupa DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_poslednji_red_preuzet =1; SET l_poslednji_red_preuzet =0; OPEN c_dept; dept_cursor: LOOP FETCH c_dept INTO l_dept_id; IF l_poslednji_red_preuzet =1 THEN LEAVE c dept_cursor; END IF; /*Uradi nešto sa preuzetomvrstom*/ END LOOP dept_cursor; CLOSE c_dept; SET l_poslednji_red_preuzet =0;

17. Zadatak 1. Kreirati proceduru ‘pop_transakcije()’ kojom se vrši popunjavanje tabele ‘transakcije’ pomoću cursor-a generisanog sa: SELECT k1.id AS oduzmi, k2.id AS dodaj FROM korisnici k1, korisnici k2 WHERE k1.id <> k2.id; Iznos koji se prebacuje sa računa jednog na račun drugog korisnika dobija se formulom: • RAND() * (MOD(v_broj_transakcija, 33) + 10) Sve transakcije se upisuju u polju ‘opis_transakcije’ i imaju oblik: • 'Prebaci---EUR- > saracunakorisnika---naracunkorisnika---’.

18. Rešenje: DELIMITER $$ DROP PROCEDURE IF EXISTS `test`.`pop_transakcije` $$ CREATE PROCEDURE `test`.`pop_transakcije` (OUT p_poruka VARCHAR(255)) LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER COMMENT 'Ova procedurasluzizapopunjavanjetabela ''transakcije'' ' BEGIN -- deklaracijapotrebnihvarijabli DECLARE v_broj_transakcija INT DEFAULT 0; DECLARE v_oduzmi_id INT; DECLARE v_dodaj_id INT; DECLARE v_iznos DOUBLE(10,2); DECLARE nema_vise_redova BOOLEAN DEFAULT false; -- deklaracija cursor-a DECLARE cur_korisnici CURSOR FOR SELECT k1.id AS oduzmi, k2.id AS dodaj FROM korisnici k1, korisnici k2 WHERE k1.id <> k2.id; DECLARE continue HANDLER FOR SQLSTATE '02000' SET nema_vise_redova = true; DECLARE exit HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_poruka = 'UnosprekinutzbogSQLExceptiona'; END; -- oznacimopocetaktransakcije START TRANSACTION; -- otvori cursor sakorisnicima OPEN cur_korisnici; -- otvoripetljusakorisnicima petlja_sa_korisnicima: LOOP -- pokupivrednostiizcursora FETCH cur_korisnici INTO v_oduzmi_id, v_dodaj_id; -- ako cursor ne sadrzi vise redova, zavrsisapetljom IF nema_vise_redova THEN LEAVE petlja_sa_korisnicima; END IF; -- povecajbrojactransakcijaza 1 SET v_broj_transakcija = v_broj_transakcija + 1; SET v_iznos = RAND() * (MOD(v_broj_transakcija, 33) + 10); -- necemovrsitiproverukoliko je trenutnikorisnikoviznos, -- negocemosamounetipodatkeradidemonstracijefunkcionisanja: UPDATE korisnici SET iznos = iznos - v_iznos WHERE id = v_oduzmi_id; UPDATE korisnici SET iznos = iznos + v_iznos WHERE id = v_dodaj_id; -- unesitransakciju u tabelu INSERT INTO transakcije(id, ovome_oduzmi, ovome_dodaj, iznos, opis_transakcije) VALUES (NULL, v_oduzmi_id, v_dodaj_id, v_iznos, CONCAT('Prebaci ', v_iznos, 'EUR- > saracunakorisnika ', v_oduzmi_id, ' naracunkorisnika ',v_dodaj_id)); -- zatvoripetljusakorisnicima END LOOP petlja_sa_korisnicima; CLOSE cur_korisnici; -- potvrdimounosioznacimokaokrajtransakcije COMMIT; -- sastavimo OUT poruku SET p_poruka = CONCAT( 'Brojobavljenihtransakcija: ', v_broj_transakcija); -- prikazimosadrzajporuke: SELECT p_poruka; END $$ DELIMITER ;