Transcript 3. Uskladistene procedure

Uskladištene procedure
1
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
2
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();
3
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 funkcije
ugrađene u MySQL.
Uskladištena procedura ima naziv, listu parametara i sadrži
jednu ili više SQL naredbi
4
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
(glavni blok)
END //
DELIMITER ;
5
Uskladištene procedure u MySQL-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 MySQL-u
Koje MySQL naredbe su dozvoljene u telu
uskladištene procedure?
 INSERT
 UPDATE
 DELETE
 SELECT
 DROP
 CREATE
 REPLACE
7
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š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 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 ;
9
Uskladištene procedure u MySQL-u
Koje MySQL naredbe nisu dozvoljene u telu
uskladištene procedure ?
• Naredbe koje manipulišu uskladištenim rutinama
(procedurama i funkcijama)






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`.`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);
11
Primer 3. SP površina kruga
• delimiter //
create procedure povrsinakruga (in r
double, out a double)
begin
set a = r * 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,kontaktOsoba,kontaktTelefon) VALUES
(klijent,promenljiva,null,null,null);
/* SQL naredba */
END $$
DELIMITER ;
14
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; //
15
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)
16
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)
17
OUT parametar
CREATE PROCEDURE p6 (OUT p INT)
SET p = -5 //
CALL p6(@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 p3 ()
SELECT CURRENT_DATE, RAND()
FROM t//
call p3()
20
21
22
23
DECLARE naredba
DECLARE se koristi za definisanje lokalnih 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.
24
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)
25
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)
26
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; //
27
CALL p13(1)//
Query OK, 1 row affected (0.00 sec)
SELECT * FROM t//
+------+
| s1 |
+------+
|6|
|6|
| 19 |
Šta dobijamo pozivom: CALL p13(NULL)
+------+
3 rows in set (0.00 sec)
28
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)
29
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)
30
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)
31
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; //
32
CALL p15()//
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 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; //
34
CALL p16()//
Query OK, 1 row affected (0.00 sec)
SELECT COUNT(*) FROM t//
+----------+
| COUNT(*) |
+----------+
| 19 |
+----------+
1 row in set (0.00 sec)
35
Уграђене функције
• Функције изгледају као процедуре, а
једина разлика у синтакси је CREATE
FUNCTION уместо PROCEDURE и што
нам је потребна клаузула RETURNS да
укаже на тип податка који се враћа као
резултат позива функцује
• Рекурзивне функције не раде у
верзијама од 5.0. MySQL их је избацио
из сигурносних разлога али ради на
томе да се оне врате у некој наредној
верзији.
36
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 !
37
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 ;
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 s1, factorial (s1) from t //
update t set s1=factorial(s1)
where factorial(s1)<5//
40
РЕКУРЗИВНЕ ПРОЦЕДУРЕ
• Рекурзија у ускладиштене процедуре је
дозвољена, али подразумевано онемогућена.
• Да бисте омогућили рекурзије, поставите
max_sp_recursion_depth серверску променљиву
на вредност већу од нуле.
• Рекурзија у ускладиштеним процедурама
повећава захтеве за слободним простором на
стеку.
• Ако повећате вредност max_sp_recursion_depth,
може бити неопходно да се повећа величину
стека повећањем вредности thread_stack при
покретању сервера.
41
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, @res1);
select @res;
select @res * @res1;
43
Карактеристике
• Када се праве угњеждене ф-је и процедуре
могу се навести бројне карактеристике које
нам омогућавају да кажемо MySQL-у неке
важне информације у вези са тим како ће
процедура да функционише.
Четири тренутне карактеристике су:
LANGUAGE SQL
[NOT] DETERMINISTIC
SQL SECURITY {DEFINER | INVOKER}
COMMENT ´string´
44
LANGUAGE
• У будућности ће бити могуће писати рутине не
само у ANSI SQL, већ и у низу других различитих
програмских језика. То ће отворити врата за
ускладиштене процедуре програмиране од стране
програмера који имају вештине у другим
програмским језицима.
• Али, за сада смо ограничени на ANSI SQL, стога
можемо само да користимо LANGUAGE SQL
карактеристику овако.
drop function helloworld //
create function helloworld() returns varchar(20)
language SQL
return "Hello World“;//
45
DETERMINISTIC
DETERMINISTIC карактеристика се
користи само унутар функције. Функција
је детерминистичка ако враћа исту
вредност сваки пут за исти скуп
параметара.
drop function helloworld //
create function helloworld() returns
varchar(20) deterministic
return "Hello World"; //
46
SQL SECURITY
• SQL SECURITY карактеристика може се
подесити било definer или invoker. То
значи да када се процедура покрене ће
SQL ће се покренути против безбедносне
дозволе било definer (корисник који је
креирао рутину) или invoker (корисник који
позива рутину).
drop function helloworld //
create function helloworld() returns varchar(20)
sql security definer
return "Hello World"; //
47
COMMENT
• Као што име сугерише коментар
карактеристика се користи за додавање
коментара на ту функцију. Овај коментар
је у виду стринга у затвореним
наводницима.
drop function pozdrav //
create function pozdrav() returns varchar(20)
comment ´Ova funkcija vraca string´
return “Zdravo svima"; //
48
• То може бити или једна линија коментар или више
линија.
• 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=InnoDB AUTO_INCREMENT=8
DEFAULT CHARSET=utf8
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