Slajd 1 - Wojciech Mościbrodzki
Download
Report
Transcript Slajd 1 - Wojciech Mościbrodzki
Bazy Danych
Programowanie w SQL
Wojciech St. Mościbrodzki
[email protected]
Prawa dostępu do obiektów w mySQL
Główne zadania DBMS:
Identyfikacja i autoryzacja użytkowników
Definiowanie poziomów dostępu
Zarządzanie prawami dostępu do obiektów
Obsługa sytuacji wyjątkowych
Prawa dostępu w mySQL
Prawa dotyczą obiektów bazodanowych:
Połączeń, baz, tablic, triggerów
Użytkowników (w mySQL - w kontekście logowania!)
Polecenia związane z zarządzaniem prawami:
CREATE USER
RENAME USER
DROP USER
GRANT
REVOKE
SHOW GRANTS
SET PASSWORD
GRANT
Polecenie GRANT służy do nadawania:
PRAW
na OBIEKTY
określonym UŻYTKOWNIKOM
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]
PRAWA (przywileje) to operacje dozwolone, np. CREATE, INSERT, SELECT,
EXECUTE, FILE, RELOAD itp.
OBIEKTY to tabele, kolumny, funkcje, procedury
UŻYTKOWNIK jest określony kontekstem: login+hasło+adres(połączenie)
PRAWA (przywileje)
W MySQL definiuje się następujące poziomy praw:
global (odnosi się do całości obiektów serwera) – GRANT <prawo> on *.*
database (tylko dla danej bazy danych) – GRANT <prawo> on moja.*
table (tylko dla jednej tablicy w bazie) – GRANT <prawo> on moja.auto
column (tylko dla określonej kolumny) – GRANT <prawo> on moja.auto(id)
W zależności od poziomu prawa, są one inaczej pamiętane w
systemowej bazie danych:
global level – tablica mysql.user
database level – tablica mysql.db
table level – tablica mysql.tables_priv
column level – tablica mysql.columns_priv
GRANT – SQL vs mySQL
mySQL nadaje prawa w kontekście logowania (nie tylko usera)
mySQL implementuje prawa globalne i bazodanowe
mySQL rozszerza zakres praw
w mySQL usunięcie tabeli nie usuwa praw(!); prawa-zombie
mySQL pozwala na nadanie prawa INSERT do określonych kolumn,
przy jednoczesnym zabraniu go na inne (użyte zostaną wartości
DEFAULT)
Prawa dostępu - mySQL
Najczęściej używane:
Prawa do zarządzania:
ALL, ALTER, DELETE, DROP, INSERT, SELECT, TRIGGER, UPDATE
CREATE USER, CREATE VIEW, CREATE, INDEX, EXECUTE, RELOAD,
SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER,
Pozostałe:
ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES,
EVENT, FILE, LOCK TABLES, PROCESS, REPLICATION CLIENT,
REPLICATION SLAVE,
GRANT SELECT
ON moja.samochod
TO wojmos
WITH GRANT OPTION
Specjalne prawo:
GRANT OPTION – możliwość przekazywania uprawnień
Implementacja kontekstu użytkownika
Prawa użytkownika przypisuje się parze (user, host)
Identyfikacja odbywa się za pomocą pary (user, password)
CREATE USER user_specification
[IDENTIFIED BY [PASSWORD] 'password']
UWAGA:
niestosowanie klauzuli IDENTIFIED BY umożliwia logowania bez hasła
IDENTIFIED BY PASSWORD umożliwia ominięcie podawania hasła otwartym
tekstem
mysql> create user wojmos;
mysql> select user, host, password from mysql.user;
+--------+-----------+-------------------------------------------+
| user
| host
| password
|
+--------+-----------+-------------------------------------------+
| root
| localhost | *E35C24480CDB73576312748BA0DCF2E4648A7E6C |
| wojmos | %
|
|
+--------+-----------+-------------------------------------------+
CREATE USER, DROP USER i SET PASSWORD
CREATE USER – tworzenie użytkowników
DROP USER – usuwanie użytkowników (z prawami!)
SET PASSWORD – ustawianie haseł
RENAME USER – zmiana nazwy
GRANT USAGE
CREATE USER user_specification
[IDENTIFIED BY [PASSWORD] 'password']
DROP USER user [, user] ...
RENAME USER old_user TO new_user
SET PASSWORD [FOR user] = {
'encrypted password‘ |
PASSWORD('some password') |
OLD_PASSWORD('some password')
}
GRANT USAGE ON *.* TO ‘wojmos'@'%.wojmos.com' IDENTIFIED BY 'newpass';
Hasła podawane w CREATE USER i SET PASSWORD
mysql> create user 'sample1';
mysql> create user 'sample2' identified by 'lipton';
mysql> create user 'sample3' identified by password
'*90E462C37378CED12064BB3388827D2BA3A9B689';
mysql> create user 'sample3' identified by password 'lipton';
ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number
mysql> create user 'sample4';
mysql> set password for sample4 = PASSWORD('lipton');
mysql> create user 'sample5';
mysql> set password for sample5 = OLD_PASSWORD('lipton');
mysql> select user, host, password from mysql.user;
+---------+-----------+-------------------------------------------+
| user
| host
| password
|
+---------+-----------+-------------------------------------------+
| root
| localhost | *E35C24480CDB73576312748BA0DCF2E4648A7E6C |
| sample2 | %
| *D0CD8B382FCD172A273DE23563C530DB2E12D059 |
| wojmos | %
|
|
| sample1 | %
|
|
| sample3 | %
| *90E462C37378CED12064BB3388827D2BA3A9B689 |
| sample4 | %
| *D0CD8B382FCD172A273DE23563C530DB2E12D059 |
| sample5 | %
| 58ac86b863e0cbbf
|
+---------+-----------+-------------------------------------------+
Przykład działania GRANT OPTION
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
GRANT ALL ON *.* TO wojmos with GRANT OPTION
root
GRANT ALL ON sample.* TO sample1 with GRANT OPTION
wojmos
GRANT ALL ON sample.* TO sample2
sample1
CREATE TABLE X(id int)
sample2
DROP USER SAMPLE1
CREATE TABLE Y(id int)
sample2
<===========?
Kontekst użytkownika
mySQL umożliwia dość szerokie i elastyczne zarządzanie prawami:
mysql> create user koza@localhost;
mysql> create user koza identified by 'lipton';
mysql> create user koza@'192.168.0.110';
mysql> create user koza@'10.10.0.';
mysqlmysql> select user, host, password from mysql.user where user like
'koza';
+------+---------------+-------------------------------------------+
| user | host
| password
|
+------+---------------+-------------------------------------------+
| koza | localhost
|
|
| koza | %
| *D0CD8B382FCD172A273DE23563C530DB2E12D059 |
| koza | 192.168.0.110 |
|
| koza | 10.10.0.
|
|
+------+---------------+-------------------------------------------+
Szyfrowanie tablic – bezpieczeństwo w mySQL
Rozważmy typową bazę, z dostępem via Internet / PHP:
Typowy (powszechne rozwiązanie) plik show.php:
USER(id int, login char(10), pass char(10), firstname char(20), lastname char(50))
http://show.php?q=‘select * from product where id=7’
Typowy atak:
http://show.php?q=‘select * from user’
Tworzenie własnych funkcji i procedur
Tworzenie własnych funkcji i procedur
Funkcje są definiowanymi przez użytkownika rozszerzeniami SQL.
MySQL pozwala na 3 mechanizmy:
Funkcje wkompilowywane w kod serwera
Funkcje w postaci kompilowanych bibliotek
Funkcje i procedury składowane (stored functions/procedures)
(Zwykły użytkownik zasadniczo używa jedynie 3 drogi)
Stored function (funkcja składowana) jest wywoływana tak, jak
zwykła funkcja SQL.
select concat('Ala', ' ma kota');
select count(*) from user;
select moja_funkcja();
Stored procedure (procedura składowana) jest wywoływana przez
call
call moja_procedura();
Własne funkcje
CREATE PROCEDURE/FUNCTION
DROP PROCEDURE/FUNCTION
SHOW PROCEDURE/FUNCTION STATUS
mysql> show function status;
+-----------+---------+----------+----------------+---------------------+---------------------+---------------+---------+
| Db
| Name
| Type
| Definer
| Modified
| Created
| Security_type | Comment |
+-----------+---------+----------+----------------+---------------------+---------------------+---------------+---------+
| poligon
| nazwa
| FUNCTION | root@localhost | 2009-12-10 21:36:12 | 2009-12-10 21:36:12 | DEFINER
|
|
| populacja | exp2lvl | FUNCTION | root@localhost | 2009-08-21 16:35:32 | 2009-08-21 16:35:32 | DEFINER
|
|
+-----------+---------+----------+----------------+---------------------+---------------------+---------------+---------+
2 rows in set (0.00 sec)
SHOW CREATE PROCEDURE/FUNCTION
Procedury i funkcje są obiektami konkretnej bazy (od 5.0.1+)!
Procedury i funkcje są obiektami serwera (do 5.0.0)!
Funkcje vs procedury
Funkcje i procedury składowane mają podobną budowę i znaczenie
Funkcje wymagają określenia RETURNS
Funkcje składowane i UDF dzielą ze sobą przestrzeń nazw
Parametry funkcji są zawsze IN (nie mogą zmienić wartości)
Funkcje są wywoływane jak normalne funkcje SQL
CREATE [DEFINER] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type
[special ...]
routine_body
func_parameter: name type
special: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO
SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER
| INVOKER } | COMMENT 'string'
routine_body: SQL | BEGIN...END
Funkcje vs procedury
Funkcje i procedury składowane mają podobną budowę i znaczenie
Procedury nie określają RETURNS – nie zwracają wartości
Procedury przekazują wartości tylko przez zmienne
Parametry procedur mogą być IN, OUT lub INOUT
Procedury wywoływane są tylko poprzez call
CREATE [DEFINER] PROCEDURE sp_name ([proc_parameter[,...]]) [special...]
routine_body
proc_parameter: [ IN | OUT | INOUT ] name type
special: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO
SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER
| INVOKER } | COMMENT 'string'
routine_body: SQL | BEGIN...END
Porównanie CREATE dla funkcji i procedur
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
type:
param_name type
Any valid MySQL data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
READS SQL DATA | MODIFIES SQL DATA }
INVOKER }
| COMMENT 'string'
| { CONTAINS SQL | NO SQL |
| SQL SECURITY { DEFINER |
DELIMITER i jego znaczenie
Procedury i funkcje składają się zwykle z wielu linii kodu. Oznacza
to problem z interpretacją znaku ; po stronie klienta.
PRZYPOMINAMY: znak terminalny „;” powoduje natychmiastowe
wysłanie poleceń do serwera (w celu interpretacji). Druga jego rola
to oddzielanie kolejnych poleceń.
CREATE FUNCTION nazwa () RETURNS VARCHAR(20)
BEGIN
RETURN 'Hello world';
END;
w tym miejscu kod
zostanie wysłany do
serwera
CREATE FUNCTION nazwa () RETURNS VARCHAR(20)
BEGIN
RETURN 'Hello world';
powyższy fragment NIE JEST jednak poprawny!
DELIMITER – zmiana znaku terminalnego
WNIOSEK: musimy chwilowo zmienić znaczenie znaku ; (aby
przestał wysyłać kod do serwera).
DELIMITER ;;
nowy symbol terminalny
CREATE FUNCTION nazwa () RETURNS VARCHAR(20)
BEGIN
RETURN ‘Hello world';
END;
tworzymy funkcję
;;
wysyłamy do serwera
DELIMITER ;
stary symbol terminalny
koniec polecenia
(sprzątamy po sobie!)
Prosta funkcja składowana
Stworzenie funkcji
mysql> DROP FUNCTION IF EXISTS Hello;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-------------------------------+
| Level | Code | Message
|
+-------+------+-------------------------------+
| Note | 1305 | FUNCTION Hello does not exist |
+-------+------+-------------------------------+
DELIMITER ;;
CREATE FUNCTION Hello() RETURNS VARCHAR(20)
BEGIN
RETURN 'Hello world';
END;
;;
DELIMITER ;
Prosta funkcja składowana
Stworzenie i wywołanie funkcji
DROP FUNCTION IF EXISTS Hello;
DELIMITER ;;
CREATE FUNCTION Hello() RETURNS VARCHAR(20)
BEGIN
RETURN 'Hello world';
END;
;;
DELIMITER ;
mysql> select hello();
+-------------+
| hello()
|
+-------------+
| Hello world |
+-------------+
1 row in set (0.00 sec)
Prosta procedura składowana
Stworzenie procedury
mysql> DROP PROCEDURE IF EXISTS Hello;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+--------------------------------+
| Level | Code | Message
|
+-------+------+--------------------------------+
| Note | 1305 | PROCEDURE Hello does not exist |
+-------+------+--------------------------------+
DELIMITER ;;
CREATE PROCEDURE Hello()
BEGIN
SELECT('Hello world');
END;
;;
DELIMITER ;
Prosta funkcja składowana
Stworzenie i wywołanie procedury
DROP PROCEDURE IF EXISTS Hello;
DELIMITER ;;
CREATE PROCEDURE Hello()
BEGIN
SELECT('Hello world');
END;
;;
DELIMITER ;
mysql> call hello();
+-------------+
| Hello world |
+-------------+
| Hello world |
+-------------+
1 row in set (0.00 sec)
Parametry przekazywane do obiektów składowanych
Funkcje – parametry zawsze są traktowane jako IN
Procedury – parametry mogą mieć typ IN, OUT lub INOUT
IN – parametr wejściowy, modyfikacja nie jest widoczna na zewnątrz (inicjalizuje
go obiekt wywołujący funkcję)
OUT – parametr wyjściowy, pełni rolę wartości zwracanej przez zmienną, ma
inicjalnie ustawianą wartość na NULL (inicjalizuje go funkcja)
INOUT - parametr wejściowo-wyjściowy, pełni rolę wartości zwracanej przez
zmienną, ma inicjalnie ustawianą wartość przez obiekt wywołujący funkcję
Dane do przykładu
mysql> select * from pracownik;
+--------+-----------+-----------+-------------+----------+
| Jan
| Kowalski | polski
| 73020103456 | Gdansk
|
| Jan
| Kowalski | niemiecki | 73020103456 | Gdansk
|
| Piotr | Nowak
| polski
| 43121102934 | Szczecin |
| Stefan | Kozlowski | polski
| 98101045777 | Warszawa |
| Piotr | Nowak
| angielski | 43121102934 | Szczecin |
| Jan
| Kurski
| polski
| 71101099348 | Gdansk
|
+--------+-----------+-----------+-------------+----------+
Parametry IN
Procedura z parametrem typu IN
CREATE PROCEDURE Sample(IN parametr char(30))
BEGIN
SELECT * FROM pracownik WHERE i LIKE parametr;
END;
Wywołanie
mysql> call Sample('Jan');
+------+----------+-----------+-------------+--------+
| i
| n
| j
| p
| miasto |
+------+----------+-----------+-------------+--------+
| Jan | Kowalski | polski
| 73020103456 | Gdansk |
| Jan | Kowalski | niemiecki | 73020103456 | Gdansk |
| Jan | Kurski
| polski
| 71101099348 | Gdansk |
+------+----------+-----------+-------------+--------+
3 rows in set (0.00 sec)
Parametry OUT
Procedura z parametrem typu OUT
CREATE PROCEDURE Sample(OUT parametr char(30))
BEGIN
SELECT count(*) FROM pracownik into parametr;
END;
Wywołanie
call sample(@zmienna); select @zmienna;
Query OK, 0 rows affected (0.00 sec)
+----------+
| @zmienna |
+----------+
| 6
|
+----------+
1 row in set (0.00 sec)
Parametry OUT
Parametr typu OUT ma inicjalną wartość NULL
CREATE PROCEDURE Sample(OUT parametr char(30))
BEGIN
SELECT parametr;
SELECT count(*) FROM pracownik into parametr;
END;
Wywołanie
mysql> call sample(@zmienna);
+----------+
| parametr |
+----------+
| NULL
|
+----------+
mysql> call sample('Jan');
ERROR 1414 (42000): OUT or INOUT argument 1 for routine wojmos.sample
is not a variable or NEW pseudo-variable in BEFORE trigger
Parametry OUT
Parametr typu OUT ma ZAWSZE inicjalną wartość NULL
CREATE PROCEDURE Sample(OUT parametr char(30))
BEGIN
SELECT parametr;
SELECT count(*) FROM pracownik into parametr;
END;
Wywołanie
mysql> select @a:=3; call sample(@a);
+-------+
| @a:=3 |
+-------+
|
3 |
+-------+
+----------+
| parametr |
+----------+
| NULL
|
+----------+
Parametry INOUT
Procedura z parametrem typu INOUT
CREATE PROCEDURE Sample(INOUT parametr char(30))
BEGIN
SELECT parametr;
SELECT count(*) FROM pracownik into parametr;
END;
Wywołanie (parametr inicjowany przez obiekt wołający!)
mysql> select @a:=3; call sample(@a);
+-------+
| @a:=3 |
+-------+
|
3 |
+-------+
+----------+
| parametr |
+----------+
| 3
|
+----------+
Parametry INOUT
Procedura z parametrem typu INOUT
CREATE PROCEDURE Sample(INOUT parametr char(30))
BEGIN
SELECT parametr;
SELECT count(*) FROM pracownik into parametr;
END;
Wywołanie (parametr inicjowany przez obiekt wołający!)
mysql> call sample(@a); select @a;
+----------+
| parametr |
+----------+
| 3
|
+----------+
+------+
| @a
|
+------+
| 6
|
+------+
Własne funkcje
SHOW PROCEDURE/FUNCTION STATUS
mysql> show function status;
+-----------+---------+----------+----------------+---------------------+---------------------+---------------+---------+
| Db
| Name
| Type
| Definer
| Modified
| Created
| Security_type | Comment |
+-----------+---------+----------+----------------+---------------------+---------------------+---------------+---------+
| poligon
| nazwa
| FUNCTION | root@localhost | 2009-12-10 21:36:12 | 2009-12-10 21:36:12 | DEFINER
|
|
| populacja | exp2lvl | FUNCTION | root@localhost | 2009-08-21 16:35:32 | 2009-08-21 16:35:32 | DEFINER
|
|
+-----------+---------+----------+----------------+---------------------+---------------------+---------------+---------+
2 rows in set (0.00 sec)
mysql> show function status;
+-----------+---------+----------+----------------+
| Db
| Name
| Type
| Definer
|
+-----------+---------+----------+----------------+
| poligon
| nazwa
| FUNCTION | root@localhost |
| populacja | exp2lvl | FUNCTION | root@localhost |
+-----------+---------+----------+----------------+
Budowa obiektów składowanych - zmienne
Zmienne w programach składowanych
zmienne sesyjne
zmienne lokalne (dla bloków BEGIN-END)
Zmienne lokalne:
deklarowane za pomocą DECLARE
muszą być deklarowane
istnieją tylko w kontekście swoich bloków
nie wymagają używania @
mogą być ustawiane za pomocą SET
mogą być ustawiane za pomocą SELECT i SELECT INTO
Zmienne w obiektach składowanych
Zliczamy byty o podanym imieniu w 2 tablicach
CREATE PROCEDURE Sample(IN parametr char(30))
BEGIN
DECLARE x1 CHAR(30) DEFAULT 0;
DECLARE x2 CHAR(30) DEFAULT 0;
SELECT count(*) into x1 FROM czlowiek where imie like parametr;
SELECT count(*) into x2 FROM pracownik where i like parametr;
select x1 as czlowiek, x2 as pracownik;
END;
mysql> call sample('jan');
+----------+-----------+
| czlowiek | pracownik |
+----------+-----------+
| 0
| 3
|
+----------+-----------+
1 row in set (0.00 sec)
INSTRUKCJE sterowania (nie mylić z FUNKCJAMI)
Funkcje sterowania są zwykłymi funkcjami SQL
IF(), CASE(), IFNULL(), NULLIF()
Instrukcje sterowania są przeznaczone tylko dla obiektów
składowanych. Oczywiście w obiektach składowanych MOŻNA
używać również funkcji sterowania
IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE
Instrukcja IF
Instrukcja IF
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
CREATE PROCEDURE Sample(IN parametr char(30))
BEGIN
IF parametr='Jan' THEN
SELECT ('Janek!') as wynik;
ELSE
SELECT ('NIE Janek?') as wynik;
END IF;
END;
mysql> call sample('Jan');
+--------+
| wynik |
+--------+
| Janek! |
+--------+
mysql> call sample('Janusz');
+------------+
| wynik
|
+------------+
| NIE Janek? |
+------------+
Instrukcja IF
IF z wykorzystaniem bloku BEGIN-END
DROP PROCEDURE IF EXISTS Sample;
DELIMITER ;;
CREATE PROCEDURE Sample(IN parametr char(30))
BEGIN
IF parametr='Jan' THEN
BEGIN
SELECT ('Janek!') as wynik;
SELECT ('Kope lat!') as wynik;
END;
ELSE
SELECT ('NIE Janek?') as wynik;
END IF;
END;
;;
DELIMITER ;
Instrukcja CASE
Instrukcja CASE
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
CREATE PROCEDURE Sample(IN parametr int)
BEGIN
CASE parametr
WHEN 1 THEN SELECT('JEDEN');
WHEN 2 THEN SELECT('DWA');
ELSE
BEGIN
SELECT ('MNOSTWO!!!');
END;
END CASE;
END;
mysql> call sample(1);
+------------+
| JEDEN
|
+------------+
| JEDEN
|
+------------+
mysql> call sample(2);
+------------+
| DWA
|
+------------+
| DWA
|
+------------+
mysql> call sample(5);
+------------+
| MNOSTWO!!! |
+------------+
| MNOSTWO!!! |
+------------+
Instrukcje LOOP i LEAVE
LOOP jest pętlą nieskończoną, z której wyjście zapewnia LEAVE
nie jest to rozwiązanie "eleganckie"
[begin_label:] LOOP
statement_list
END LOOP [end_label]
CREATE PROCEDURE Sample(IN parametr int)
BEGIN
select (parametr);
petla: LOOP
IF parametr>10 THEN
LEAVE petla;
END IF;
SET parametr = parametr + 1;
END LOOP;
select (parametr);
END;
mysql> call sample(3);
+------------+
| (parametr) |
+------------+
|
3 |
+------------+
1 row in set (0.00 sec)
+------------+
| (parametr) |
+------------+
|
11 |
+------------+
1 row in set (0.00 sec)
Instrukcje LOOP i LEAVE
REPEAT jest pętlą warunkową, która wykonuje się minimum 1 raz
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
CREATE PROCEDURE Sample(IN parametr int)
BEGIN
REPEAT
select (parametr);
SET parametr = parametr + 1;
UNTIL parametr>10
END REPEAT;
END;
mysql> call sample(8);
+------------+
| (parametr) |
+------------+
|
8 |
+------------+
1 row in set (0.00 sec)
+------------+
| (parametr) |
+------------+
|
9 |
+------------+
1 row in set (0.00 sec)
+------------+
| (parametr) |
+------------+
|
10 |
+------------+
1 row in set (0.00 sec)
Instrukcje LOOP i LEAVE
WHILE jest typową pętlą warunkową
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
CREATE PROCEDURE Sample(IN parametr int)
BEGIN
DECLARE n INT;
SET n = 0;
WHILE n<parametr DO
select (n);
SET n = n + 1;
END WHILE;
END;
mysql> call sample(4);
+------+
| (n) |
+------+
|
0 |
+------+
+------+
| (n) |
+------+
|
1 |
+------+
+------+
| (n) |
+------+
|
2 |
+------+
+------+
| (n) |
+------+
|
3 |
+------+
1 row in set (0.00 sec)
Wykorzystanie obiektów składowanych
CREATE PROCEDURE MakeCzlowiek()
BEGIN
DECLARE ile_sylab INT;
DECLARE nazwisko char(50) default '';
DECLARE imie1 char(50) default '';
DECLARE temp char(10) default '';
select 2+CAST(2*rand() as signed) into ile_sylab;
while (ile_sylab>0) do
select dana into temp from sylaba order by rand() limit 1;
set nazwisko := concat(nazwisko,temp);
set ile_sylab := ile_sylab - 1;
end while;
set nazwisko := concat(nazwisko,'cki');
set nazwisko :=
concat(ucase(substring(nazwisko,1,1)),substring(nazwisko,2))
;
select dana into imie1 from imie order by rand() limit 1;
SELECT nazwisko;
insert into czlowiek(imie, nazwisko) values (imie1, nazwisko);
END;
mysql> call MakeCzlowiek(); select * from czlowiek;
+-------------+
| Nazanacki
|
+-------------+
Wykorzystanie obiektów składowanych
DROP PROCEDURE IF EXISTS MakeLudzie;
DELIMITER ;;
CREATE PROCEDURE MakeLudzie(IN ilu int)
BEGIN
delete from czlowiek;
while (ilu>0) do
call MakeCzlowiek();
set ilu := ilu - 1;
end while;
select count(*) as ilu_mamy_ludzi from czlowiek;
END;
;;
DELIMITER ;
mysql> call MakeLudzie(100);
+----------------+
| ilu_mamy_ludzi |
+----------------+
|
100 |
+----------------+
1 row in set (0.06 sec)
TRIGGER – specyficzna stored procedure
TRIGGER – funkcja składowana wywoływana automatycznie, przez
serwer po zaistnieniu pewnego zdarzenia
TRIGGER (wyzwalacz) jest więc swego rodzaju event guardianem
obiekt standardowy SQL99
update licznik set imie_ile=select count(*) from imie
+----------------+
|
imie
|
+----------------+
|Jan
|
|Tadeusz
|
|Piotr
|
|Kacper
|
+----------------+
mysql> insert into imie values ('Czesio')
Klasyfikacja TRIGGERÓW
Triggery (wyzwalacze) możemy podzielić:
według kryterium czasu:
• triggery BEFORE
• triggery AFTER
• triggery INSTEAD OF (rzadko implementowane)
według kryterium rodzaju operacji (związek z type operacji, a nie poleceniem!)
• triggery ON INSERT (działa także w przypadku LOAD DATA)
• triggery ON DELETE
• triggery ON UPDATE
według kryterium obiektu strzeżonego
• triggery modyfikacji danych
• triggery modyfikacji struktury (trigger ALTER, DROP)
• triggery eventowe (trigger LOGIN)
Budowa TRIGGERA
Ogólna postać:
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW trigger_stmt
delimiter |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = 3;
END; |
delimiter ;
Prawa (MySql):
tworzenie triggerów wymaga praw TRIGGER dla danej tablicy (wcześniej: prawo
globalne SUPER)
obiekty NEW i OLD
Obiekty przechowujące wartość poprzednią i nową:
create table t1 (id int, auto_increment primary key, liczba int);
create table historia (z char(100), stamp timestamp)
delimiter |
CREATE TRIGGER moj1 AFTER UPDATE ON t1
FOR EACH ROW
BEGIN
INSERT INTO history(z) values (CONCAT(OLD.liczba,'->',NEW.liczba));
END;
|
delimiter ;
update t1 set liczba=2 where id=1;
insert into t1(liczba) values (222);
Obiekty OLD i NEW
MySQL ułatwia wywołania obiektów poprzez nadanie im nazw
delimiter |
CREATE TRIGGER pensja_trigger BEFORE UPDATE ON pracownicy_table
REFERENCING NEW ROW AS n, OLD ROW AS o
FOR EACH ROW
IF n.pensja <> o.pensja THEN
--wykonaj odpowiednie działania;
END IF;
delimiter ;
Bezpieczniejsze tworzenie triggerów
DROP TRIGGER IF EXISTS moj1;
delimiter |
CREATE TRIGGER moj1 AFTER UPDATE ON t1
FOR EACH ROW
BEGIN
INSERT INTO history (zapis) values (3);
END;
|
delimiter ;