Uskladitene procedure 1 Uskladitene procedure u My SQLu

Uskladištene procedure 1

Uskladištene procedure u My. SQL-u Zašto se koriste ? • Iako predstavljaju novu mogućnost u okviru My. SQL-a, odavno postoje u ostalim RDBMS • Uskladištene procedure su brze. Efekat brzine se postiže pre svega kroz smanjenje mrežnog saobraćaja. • Naročito su pogodne za ponavljajuće zadatke koji zahtevaju proveru, iteraciju, sa malo ili bez interakcije sa korisnikom • Ako promenite jezik za pristup bazi podataka ne bi trebalo da bude problema jer je logika u bazi podataka a ne aplikaciji. • Sintaksa uskladištenih procedura My. SQL-a je bliska SQL: 2003 standardu, tako da se lako mogu primeniti i na drugim RDBMS 2

Uskladištene procedure u My. SQL-u Važno !!! S obzirom da su uskladištene procedure uvedene u verziji 5, neophodno je prvo proveriti koja verzija je instalirana na računaru, da biste bili sigurni da ih uopšte možete koristiti. show variables like 'version'; Ili SELECT VERSION(); 3

Uskladištene procedure u My. SQL-u Uskladištena procedura (stored procedure) je procedura (potprogram ili metod u programskim jezicima) koja je smeštena u bazi podataka. My. SQL podržava dve vrste ovakvih procedura: q uskladištene procedure koje ne vraćaju vrednost q funkcije koje vraćaju vrednosti na isti način kao i funkcije ugrađene u My. SQL. Uskladištena procedura ima naziv, listu parametara i sadrži jednu ili više SQL naredbi 4

Uskladištene procedure u My. SQL-u Primer : DELIMITER // DROP PROCEDURE IF EXISTS `test`. `SP_proba` // CREATE PROCEDURE `SP_proba`() BEGIN select * from test ; telo procedure (glavni blok) END // DELIMITER ; 5

Uskladištene procedure u My. SQL-u Pozivanje uskladištene procedure : call SP_proba(); • Naziv uskladištene procedure nije case senzitivan. • U jednoj bazi sve uskladištene procedure moraju imati različite nazive, što znači da preklapanje (overloading) procedura nije moguće • Naziv uskladištene procedure može sadržati maksimalno 64 znaka uključujući i praznine (space) 6

Uskladištene procedure u My. SQL-u Koje My. SQL naredbe su dozvoljene u telu uskladištene procedure? q INSERT q UPDATE q DELETE q SELECT q DROP q CREATE q REPLACE 7

Uskladištene procedure u My. SQL-u Koje My. SQL naredbe su dozvoljene u telu uskladištene procedure? q Bilo koja SQL DML naredba. Primer 1: DELIMITER $$ DROP PROCEDURE IF EXISTS `test`. `SP_brisanje_test` $$ CREATE PROCEDURE `test`. `SP_brisanje_test` () BEGIN DELETE FROM test; END $$ DELIMITER ; U slučaju greške 1175 prilikom izvršenja delete from test Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor -> Query Editor and reconnect. 8

Uskladištene procedure u My. SQL-u Primer 2: DELIMITER $$ DROP PROCEDURE IF EXISTS `test`. `SP_brisanje_tabele_test` $$ CREATE PROCEDURE `zaposleni`. `SP_brisanje_tabele_test` () BEGIN DROP TABLE test; END $$ DELIMITER ; 9

Uskladištene procedure u My. SQL-u Koje My. SQL naredbe nisu dozvoljene u telu uskladištene procedure ? • Naredbe koje manipulišu uskladištenim rutinama (procedurama i funkcijama) q q q CREATE PROCEDURE / CREATE FUNCTION ALTER PROCEDURE / ALTER FUNCTION DELETE PROCEDURE / DELETE FUNCTION CREATE TRIGGER ALTER TRIGGER DELETE TRIGGER • Naredba USE 10

Primer 3 CREATE TABLE `test`. `test 2` ( `idtest 2` INT NOT NULL AUTO_INCREMENT , `ime` CHAR(20) NULL , `god` YEAR NULL , PRIMARY KEY (`idtest 2`) ); INSERT INTO `test`. `test 2` (`ime`, `god`) VALUES (‘pera', 2000); INSERT INTO `test`. `test 2` (`ime`, `god`) VALUES (‘mika', 1999); INSERT INTO `test`. `test 2` (`ime`, `god`) VALUES (‘laza', 2001); INSERT INTO `test`. `test 2` (`ime`, `god`) VALUES ('zika', 2000); 11

Primer 3. SP površina kruga • delimiter // create procedure povrsinakruga (in r double, out a double) begin set a = r * pi(); end // delimiter ; call povrsinakruga(10, @a); select @a; 12

Primer 4. SF obim kruga delimiter // create function obimkruga (r double) returns double deterministic begin declare c double; set c = 2 * r * pi(); return c; end // delimiter ; 13

• Primer: DELIMITER $$ DROP PROCEDURE IF EXISTS `zaposleni`. `SP_klijent` $$ /* brisanje procedure */ CREATE PROCEDURE `SP_klijent`(in klijent integer) /* naziv proc. i lista parametara */ BEGIN /* pocetak bloka */ DECLARE promenljiva CHAR(10); /* deklarisanje promenljivih*/ IF klijent = 17 THEN /* pocetak IF naredbe */ SET promenljiva = ‘Tom'; /* naredba dodeljivanja */ ELSE SET promenljiva = ‘Tim'; /* naredba dodeljivanja */ END IF; /* kraj IF naredbe */ INSERT INTO klijent(klijent, ime, adresa, kontakt. Osoba, kontakt. Telefon) VALUES (klijent, promenljiva, null, null); /* SQL naredba */ END $$ DELIMITER ; 14

Osnovna podešavanja • • • CREATE DATABASE db 5; USE db 5; CREATE TABLE t (s 1 INT); INSERT INTO t VALUES (5); DELIMITER // ili DELIMITER $$ CREATE PROCEDURE p 1 () SELECT * FROM t; // 15

IN – ulazni parametar CREATE PROCEDURE p 5(in p INT) SET @x = p // Query OK, 0 rows affected (0. 00 sec) CALL p 5(12345)// Query OK, 0 rows affected (0. 00 sec) SELECT @x// +-------+ | @x | +-------+ | 12345 | +-------+ 1 row in set (0. 00 sec) 16

Uskladištene procedure u My. SQL-u Parametri In DELIMITER $$ DROP PROCEDURE IF EXISTS `test`. `SP_parametar_in` $$ CREATE PROCEDURE `test`. `SP_parametar_in` (in p year) BEGIN SELECT * FROM test 2 WHERE god = p ; END $$ DELIMITER ; Poziv. Call SP_parametar_in(1999) 17

OUT parametar CREATE PROCEDURE p 6 (OUT p INT) SET p = -5 // CALL p 6(@y)// SELECT @y// +------+ | @y | +------+ | -5 | +------+ 18

Greške CALL pi(); • Error 1064 (42000): You have a syntax error. CALL pi (); • Error 1305 (42000): PROCEDURE does not exist. • Error. . . 19

Korišćenje ugrađenih f-ja DELIMITER // CREATE PROCEDURE p 3 () SELECT CURRENT_DATE, RAND() FROM t// call p 3() 20

21

22

23

DECLARE naredba DECLARE se koristi za definisanje lokalnih promenljivih u BEGIN. . END iskazu CREATE PROCEDURE p 8 () BEGIN DECLARE a INT; DECLARE b INT; SET a = 5; SET b = 5; INSERT INTO t VALUES (a); SELECT s 1 * a FROM t WHERE s 1 >= b; END; // /* komentar */ Primetimo da u ovom slučaju promenljive ne počinju sa (@). I moraju se deklarisati na početku. 24

DEFAULT klauzula CREATE PROCEDURE p 10 () BEGIN DECLARE a, b INT DEFAULT 5; INSERT INTO t VALUES (a); SELECT s 1 * a FROM t WHERE s 1 >= b; END; // CALL p 10() // +----+ | s 1 * a | +----+ | 25 | +----+ 2 rows in set (0. 00 sec) Query OK, 0 rows affected (0. 00 sec) 25

IF THEN ELSE CREATE PROCEDURE p 12 (IN parameter 1 INT) BEGIN DECLARE variable 1 INT; SET variable 1 = parameter 1 + 1; IF variable 1 = 0 THEN INSERT INTO t VALUES (17); END IF; IF parameter 1 = 0 THEN UPDATE t SET s 1 = s 1 + 1; ELSE UPDATE t SET s 1 = s 1 + 2; END IF; END; // CALL p 12(0)// Query OK, 2 rows affected (0. 28 sec) SELECT * FROM t// +------+ | s 1 | +------+ |6| +------+ 2 rows in set (0. 01 sec) 26

CASE CREATE PROCEDURE p 13 (IN parameter 1 INT) BEGIN DECLARE variable 1 INT; SET variable 1 = parameter 1 + 1; CASE variable 1 WHEN 0 THEN INSERT INTO t VALUES (17); WHEN 1 THEN INSERT INTO t VALUES (18); ELSE INSERT INTO t VALUES (19); END CASE; END; // 27

CALL p 13(1)// Query OK, 1 row affected (0. 00 sec) SELECT * FROM t// +------+ | s 1 | +------+ |6| | 19 | Šta dobijamo pozivom: CALL p 13(NULL) +------+ 3 rows in set (0. 00 sec) 28

CALL p 13(NULL)// Query OK, 1 row affected (0. 00 sec) SELECT * FROM t// +------+ | s 1 | +------+ |6| | 19 | +------+ 4 rows in set (0. 00 sec) 29

WHILE. . . END WHILE CREATE PROCEDURE p 14 () BEGIN DECLARE v INT; SET v = 0; WHILE v < 5 DO INSERT INTO t VALUES (v); SET v = v + 1; END WHILE; END; // CALL p 14()// Query OK, 1 row affected (0. 00 sec) 30

CALL procedure p 14. kaže "one row affected" umesto "five rows affected“ kao što očekujemo. Nije nikakva greška već se broji samo poslednji INSERT. select * from t; // +------+ | s 1 | +------+. . |0| |1| |2| |3| |4| +------+ 9 rows in set (0. 00 sec) 31

REPEAT. . . END REPEAT CREATE PROCEDURE p 15 () BEGIN DECLARE v INT; SET v = 0; REPEAT INSERT INTO t VALUES (v); SET v = v + 1; UNTIL v >= 5 END REPEAT; END; // 32

CALL p 15()// Query OK, 1 row affected (0. 00 sec) SELECT COUNT(*) FROM t// +-----+ | COUNT(*) | +-----+ | 14 | +-----+ 1 row in set (0. 00 sec) 33

LOOP. . . END LOOP: sa IF i LEAVE CREATE PROCEDURE p 16 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; // 34

CALL p 16()// Query OK, 1 row affected (0. 00 sec) SELECT COUNT(*) FROM t// +-----+ | COUNT(*) | +-----+ | 19 | +-----+ 1 row in set (0. 00 sec) 35

Funkcije u My. SQL-u • Funkcije su programi koji – kada se pozovu vraćaju vrednost, – moraju uvek da vrate vrednost, – uvek vraćaju samo jednu vrednost. • Mogu biti pozvane iz SQL naredbe. • Ograničenje Funkcije ne mogu da pristupe tabelama baze podataka ! 37

Funkcije u My. SQL-u • Primer: DELIMITER $$ DROP FUNCTION IF EXISTS `zaposleni`. `F_IF` $$ CREATE FUNCTION `F_IF`(parametar VARCHAR(10)) RETURNS varchar(10) BEGIN declare izlaz VARCHAR(10) default "Nije A"; if parametar = "A" then set izlaz : = "Jeste A"; end if; return izlaz; END $$ DELIMITER ; 38

Функција – factorial(n) DELIMITER $$ CREATE FUNCTION `factorial`(n DECIMAL(3, 0)) RETURNS decimal(20, 0) DETERMINISTIC BEGIN DECLARE factorial DECIMAL(20, 0) DEFAULT 1; DECLARE counter DECIMAL(3, 0) ; SET counter = n; factorial_loop: REPEAT SET factorial = factorial * counter; SET counter = counter - 1; UNTIL counter = 1 END REPEAT; RETURN factorial; END Прoбајте да направите и позовете функцију рекурзивно!!! 39

• позив функције factorial delimiter // insert into t values (factorial(6)) // select s 1, factorial (s 1) from t // update t set s 1=factorial(s 1) where factorial(s 1)<5// 40

Rekurzivna procedura factorial DELIMITER $$ DROP PROCEDURE IF EXISTS testdb. factorial_proc$$ CREATE PROCEDURE testdb. factorial_proc ( IN n BIGINT, OUT res BIGINT ) BEGIN SET max_sp_recursion_depth=10; IF n >= 2 THEN CALL testdb. factorial_proc (n-1, res); SELECT n * res INTO res; ELSE SELECT n INTO res; END IF; END$$ DELIMITER ; 42

позив рекурзивне процедуре CALL testdb. factorial_proc (5, @res); CALL testdb. factorial_proc (5, @res 1); select @res * @res 1; 43

• То може бити или једна линија коментар или више линија. • kоментар у једној линији се користе са - - карактера • коментар у вишеструким линијама између / * и * /. drop function pozdrav // create function pozdrav() returns varchar(20) comment ´ Ova funkcija vraca string´ begin -- komentar u jednoj liniji /* ovo je komentar u vise linija */ return “Pozdrav svima"; end // 49

Zadatak 1. • Kreirati proceduru ‘pop_tabele’ kojom se popunjava tabela ‘korisnici’ sa brojem korisnika koji je određen ulaznim parametrom. Npr. Pozivom procedure sa: call pop_tabele(5, @poruka); tabela ‘korisnici’ će imati 5 generisanih korisnika, a porukom se obaveštavamo o broju unetih korisnika. • Ime, prezime i email se dobijaju spajanjem stringova naredbom CONCAT() a iznos se slučajno generiše naredbom RAND() 50

Rešenje: delimiter $$ CREATE TABLE `korisnici` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `ime` varchar(45) NOT NULL DEFAULT '', `prezime` varchar(45) NOT NULL DEFAULT '', `iznos` double(10, 2) DEFAULT NULL, `email` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`) ) ENGINE=Inno. DB AUTO_INCREMENT=8 DEFAULT CHARSET=utf 8 COMMENT='Test tabela za ugradjenu proceduru'$$ 51

DELIMITER $$ DROP PROCEDURE IF EXISTS `test`. `pop_tabele` $$ CREATE PROCEDURE `test`. `pop_tabele` (IN p_broj_korisnika INT, OUT p_poruka VARCHAR(255)) COMMENT 'Ova procedura sluzi za popunjavanje tabele ''korisnici'' ' BEGIN -- deklaracija potrebnih varijabli DECLARE v_counter INT DEFAULT 0; START TRANSACTION; -- sastavimo OUT poruku SET p_poruka = CONCAT('Broj unesenih korisnika: ', v_counter); -- prikazimo sadrzaj poruke: SELECT p_poruka; END $$ DELIMITER ; call pop_tabele(5, @poruka); -- petlja za unos podataka WHILE v_counter < p_broj_korisnika DO -- povecaj brojac za 1 SET v_counter = v_counter + 1; INSERT INTO korisnici (id, ime, prezime, iznos, email) VALUES ( NULL, CONCAT('ime_', v_counter), CONCAT('prezime_', v_counter), RAND() * (10/RAND()) * 1000, CONCAT('email', MOD(v_counter, 5), '@domena', MOD(v_counter, 7), '. com') ); END WHILE; -- potvrdimo unos i oznacimo kao kraj transakcije COMMIT; 52