1 / 52

Uskladištene procedure

Uskladištene procedure. Uskladištene procedure u MySQL -u. Zašto se koriste ? Iako predstavljaju novu mogućnost u okviru MySQL-a, odavno postoje u ostalim RDBMS Uskladištene procedure su brze. Efekat br z ine se postiže pre svega kroz smanjenje mrežnog saobraćaja.

matty
Télécharger la présentation

Uskladištene procedure

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. Uskladištene procedure

  2. Uskladištene procedure u MySQL-u • Zašto se koriste ? • Iako predstavljaju novu mogućnost u okviru MySQL-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 MySQL-a je bliska SQL:2003 standardu, tako da se lako mogu primeniti i na drugim RDBMS

  3. Uskladištene procedure u MySQL-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();

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

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

  6. Uskladištene procedure u MySQL-u Pozivanjeuskladištene procedure : call SP_proba(); • Nazivuskladiš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)

  7. Uskladištene procedure u MySQL-u KojeMySQLnaredbe su dozvoljene u telu uskladištene procedure? • INSERT • UPDATE • DELETE • SELECT • DROP • CREATE • REPLACE

  8. Uskladištene procedure u MySQL-u Koje MySQL naredbe su dozvoljene u telu uskladištene procedure? • 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 prilikom izvršenjadelete from testError 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.

  9. Uskladištene procedure u MySQL-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 ;

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

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

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

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

  14. 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,kontaktOsoba,kontaktTelefon) VALUES (klijent,promenljiva,null,null,null); /* SQL naredba */ END $$ DELIMITER ;

  15. Osnovna podešavanja • CREATE DATABASE db5; • USE db5; • CREATE TABLE t (s1 INT); • INSERT INTO t VALUES (5); • DELIMITER // ili DELIMITER $$ • CREATE PROCEDURE p1 () SELECT * FROM t; //

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

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

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

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

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

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

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

  23. IF THEN ELSE CREATE PROCEDURE p12 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT INTO t VALUES (17); END IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END IF; END; // CALL p12(0)// Query OK, 2 rows affected (0.28 sec) SELECT * FROM t// +------+ | s1 | +------+ | 6 | | 6 | +------+ 2 rows in set (0.01 sec)

  24. CASE CREATE PROCEDURE p13 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; CASE variable1 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; //

  25. CALL p13(1)// Query OK, 1 row affected (0.00 sec) SELECT * FROM t// +------+ | s1 | +------+ | 6 | | 6 | | 19 | +------+ 3 rows in set (0.00 sec) Šta dobijamo pozivom: CALL p13(NULL)

  26. CALL p13(NULL)// Query OK, 1 row affected (0.00 sec) SELECT * FROM t// +------+ | s1 | +------+ | 6 | | 6 | | 19 | | 19 | +------+ 4 rows in set (0.00 sec)

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

  28. CALL procedure p14. 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; // +------+ | s1 | +------+ .... | 0 | | 1 | | 2 | | 3 | | 4 | +------+ 9 rows in set (0.00 sec)

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

  30. CALL p15()// Query OK, 1 row affected (0.00 sec) SELECT COUNT(*) FROM t// +----------+ | COUNT(*) | +----------+ | 14 | +----------+ 1 row in set (0.00 sec)

  31. LOOP ... END LOOP:sa IF i LEAVE CREATE PROCEDURE p16 () 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; //

  32. CALL p16()// Query OK, 1 row affected (0.00 sec) SELECT COUNT(*) FROM t// +----------+ | COUNT(*) | +----------+ | 19 | +----------+ 1 row in set (0.00 sec)

  33. Уграђене функције • Функције изгледају као процедуре, а једина разлика у синтакси је CREATE FUNCTION уместо PROCEDURE и што нам је потребна клаузула RETURNS да укаже на тип податка који се враћа као резултат позива функцује • Рекурзивне функције не раде у верзијама од 5.0. MySQLих је избацио из сигурносних разлога али ради на томе да се оне врате у некој наредној верзији.

  34. Funkcije u MySQL-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 !

  35. Funkcije u MySQL-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 ;

  36. Функција– 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бајте да направите и позовете функцију рекурзивно!!!

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

  38. РЕКУРЗИВНЕ ПРОЦЕДУРЕ • Рекурзија у ускладиштене процедуре је дозвољена, али подразумевано онемогућена. • Да бисте омогућили рекурзије, поставите max_sp_recursion_depth серверску променљиву на вредност већу од нуле. • Рекурзија у ускладиштеним процедурама повећава захтеве за слободним простором на стеку. • Ако повећате вредност max_sp_recursion_depth, може бити неопходно да се повећа величину стека повећањем вредности thread_stack при покретању сервера.

  39. Rekurzivnaprocedura 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 ;

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

  41. Карактеристике • Када се праве угњеждене ф-је и процедуре могу се навести бројне карактеристике које нам омогућавају да кажемо MySQL-у неке важне информације у вези са тим како ће процедура да функционише. Четири тренутне карактеристике су: LANGUAGE SQL [NOT] DETERMINISTIC SQL SECURITY {DEFINER | INVOKER} COMMENT ´string´

  42. LANGUAGE • У будућности ће бити могуће писати рутине не само у ANSISQL, већ и у низу других различитих програмских језика. То ће отворити врата за ускладиштене процедуре програмиране од стране програмера који имају вештине у другим програмским језицима. • Али, за сада смо ограничени на ANSISQL, стога можемо само да користимо LANGUAGE SQL карактеристику овако. drop function helloworld // create function helloworld() returns varchar(20) language SQL return "Hello World“;//

  43. DETERMINISTIC DETERMINISTICкарактеристика се користи само унутар функције. Функција је детерминистичка ако враћа исту вредност сваки пут за исти скуп параметара. drop function helloworld // create function helloworld() returns varchar(20) deterministic return "Hello World"; //

  44. SQL SECURITY • SQL SECURITY карактеристика може се подесити било definer или invoker. То значи да када се процедура покрене ће SQL ће се покренути против безбедносне дозволе било definer (корисник који је креирао рутину) или invoker(корисник који позиварутину). drop function helloworld // create function helloworld() returns varchar(20) sql security definer return "Hello World"; //

  45. COMMENT • Као што име сугерише коментар карактеристика се користи за додавање коментара на ту функцију. Овај коментар је у виду стринга у затвореним наводницима. drop function pozdrav // create function pozdrav() returns varchar(20) comment ´Ova funkcija vraca string´ return “Zdravo svima"; //

  46. То може бити или једна линија коментар или више линија. • 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 //

  47. 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()

More Related