MySQL, il piu' diffuso database Open Source, dalla versione 5.0 fornisce il supporto per le Stored Routines. Le Stored Routine sono programmi costituiti da codice SQL, variabili, costrutti logici, ... che vengono memorizzati sulla base dati ed eseguiti a richiesta. Con una Stored Routine e' possibile manterere sul database la logica di trattamento dei dati rendendo univoche e piu' efficenti le procedure che lo utilizzano.
Nel seguito sono riportate alcune informazioni di interesse organizzate in paragrafi specifici: Definizione Funzioni Trigger GRANT Catalogo JDBC Replication Oracle PL/SQL
Creare ed eseguire una Stored Routine e' piu' facile a farsi che a dirsi!
DELIMITER // ; CREATE PROCEDURE hailora () BEGIN select now(); END // DELIMITER ; //
mysql> call hailora; +---------------------+ | now() | +---------------------+ | 2008-04-01 17:17:17 | +---------------------+ 1 row in set (0.00 sec)
Naturalmente e' possibile realizzare routine anche complesse... La sintassi utilizzata da MySQL rispetta quella definita dallo standard ANSI SQL2003. Nel seguito sono riportati maggiori dettagli utilizzando un esempio solo un poco piu' complesso.
Le Stored Routine (dette anche Stored Procedure) consentono di memorizzare programmi nella base dati. Le stored procedure sono particolamente efficenti nel trattare grandi quantita' di dati e permettono di isolare logiche di trattamento delle informazioni. Ecco un esempio che presenta i principali costruitti sintattici:
use test;
DROP PROCEDURE IF EXISTS test1;
DELIMITER '/';
CREATE PROCEDURE test1
(IN tot INTEGER)
LANGUAGE SQL
DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT 'Test Procedure'
BEGIN
DECLARE i INTEGER DEFAULT 0;
WHILE i < tot
DO
INSERT INTO DUMMY (X) VALUES ('X');
SET i = i + 1;
END WHILE;
COMMIT ;
END/
DELIMITER ';'/
Per richiamare la procedura il comando e' CALL test1(3).
Chiaramente la procedura richiamata inserira' tre record nella tabella DUMMY...
ma gli aspetti interessanti sono altri:
La gestione delle eccezioni puo' essere effettuata in modo semplice definendo un handler:
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET Error_found=1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'Errore: termine procedura';
END;
Analogamente alle Stored Routine possono essere definite le Function che hanno la caratteristica di restituire un valore e possono essere richiamate dagli statement SQL. La sintassi e' analoga a quella delle Stored Routine ma e' presente qualche limitazione in piu'... ad esempio non e' possibile richiamare statement che esplicitamente o implicitamente effettuino un COMMIT o un ROLLBACK.
I Trigger permettono di associare ad un evento (eg. INSERT) un'azione da svolgere. Le azioni possono essere di validazione dei dati oppure di modifica. La sintassi e':
CREATE TRIGGER nome [BEFORE|AFTER] [INSERT|UPDATE|DELETE]
ON tabella FOR EACH ROW statement
Un trigger BEFORE viene eseguito prima dello statement che lo ha attivato
e, se fallisce, lo statement non viene eseguito.
Un trigger AFTER viene eseguito dopo lo statement che lo ha attivato
per ogni riga che e' stata coinvolta.
Lo statement ha la stessa sintassi che abbiamo visto per le stored procedure
(eg. BEGIN ... END).
In realta' su un trigger non tutti i comandi SQL sono disponibili
e vi sono alcune limitazioni.
Non sono infatti concessi comandi che alterano il flusso della transazione
(eg. COMMIT).
All'interno di un trigger e' possibile riferirsi ai valori presenti
nella tabella prima o dopo l'operazione SQL indicandoli come
OLD.column_name e NEW.column_name rispettivamente.
I valori OLD sono in sola lettura mentre i valori NEW possono
essere modificati.
Sono necessari alcuni specifici GRANT per operare con le
Stored Routines: [ CREATE | ALTER | EXECUTE ] ROUTINE
(dall'ovvio significato).
Se la variabile di sistema automatic_sp_privileges
e' impostata ad ON (default) vengono assegnati automaticamente
i grant di ALTER ROUTINE ed EXECUTE all'utente che crea la
Stored Routine.
Come sempre in MySQL i GRANT possono essere assegnati sulla singola procedura,
a livello di database o globali.
I diritti di accesso agli oggetti
quando una Stored Routine viene eseguita,
dipendono dal security_type.
Il security_type puo' essere INVOKER o DEFINER.
Tipicamente viene utilizzato il default (DEFINER) il che significa
che la Stored Routine viene eseguita con i diritti che ha l'utente
che l'ha creata.
In questo modo
il diritto di eseguire la Stored Routine e' sufficiente per
ereditare,
durante l'esecuzione della routine,
i diritti sugli oggetti referenziati.
Naturalmente il DEFINER deve esistere ed avere privilegi sufficenti!
Questo e' importante soprattutto nel caso in cui la Stored Routine
venga migrata tra server MySQL differenti tra cui gli utenti possono
non coincidere.
Per controllare le caratteristiche ed il codice delle Stored Routines si usano i comandi:
SHOW PROCEDURE STATUS LIKE 'procedure_name'\G; SHOW CREATE PROCEDURE database.procedure_name\G;Oppure e' possibile utilizzare direttamente la tabella del catalogo mysql.proc.
Le Stored Routine possono essere richiamate anche da programmi java con l'interfaccia JDBC. In questo caso oltre al GRANT EXECUTE puo' essere necessaria la seguente abilitazione:
GRANT select ON mysql.proc to user@'host';Infatti il driver JDBC verifica il tipo dei parametri interrogando la tabella mysql.proc. Tale comportamento puo' essere modificato utilizzando il parametro noAccessToProcedureBodies (riconosciuto dalla versione 5.0.3 di MySQL Connector/J):
String connectionURL = "jdbc:mysql://localhost:3306/mydatabase?user=myuser&password=mypassword&noAccessToProcedureBodies=true"L'utilizzo di una versione recente di driver JDBC e' fortemente consigliata; infatti le versioni piu' recenti consentono un diagnostica nettamente migliore di eventuali problemi.
Un'importante considerazione e' relativa all'utilizzo del binary-log
(necessario per la replication e per la modalita' di recovery point-in-time).
Vengono infatti imposti una sintassi specifica
per indicare le modifiche svolte sui dati
e limiti alle tipologie di Stored Procedures utilizzabili,
per rendere replicabili le azioni svolte dalle stored procedures
sui server su cui viene replicato il DB.
Infatti sul binary log viene riportata la stored procedure richiamata
ed il risultato di questa deve essere replicabile per poter
riapplicare il log.
Per tale ragione e' importante dichiarare se il comportamento
della stored procedure e' deterministico (ovvero si ripete sempre
uguale a fronte degli stessi dati) e se vengono modificati dati.
Poiche' il thread di replicazione gira in stato privilegiato
sugli slave e' richiesto il privilegio SUPER oppure l'impostazione
del parametro --log-bin-trust-routine-creators.
Le Stored Routine di MySQL sono simili funzionalmente alle Stored Procedure realizzate in PL/SQL di Oracle. Poiche' l'RDBMS Oracle e' molto diffuso si e' ritenuto opportuno riportare qualche riferimento. Nel documento MySQL 4 Oracle DBA sono riportati maggiori dettagli sul confronto tra i due RDBMS. Nel seguito ci limitiamo alla parte relativa alle Stored Procedures.
Le differenze sintattiche tra il PL/SQL di Oracle e MySQL sono notevoli:
Anche con MySQL e' possibile definire Trigger. Non tutti i tipi di trigger supportati da Oracle sono disponibili con MySQL anche se quelli presenti hanno un comportamento simile. Molto differente e' la gestione del rollback, che e' possibile in Oracle ma non puo' essere gestita con MySQL.
La quantita' di funzioni e packages disponibili nel PL/SQL non ha eguali in MySQL. Sono disponibili in MySQL Forge una serie di nuove funzioni... ma la differenza e' di almeno due ordini di grandezza.
Parole, parole, parole, ... forse e' piu' utile un esempio sulla stessa procedura (il benchmark TPC-B) realizzata con una Stored Procedure in PL/SQL di Oracle e con una Stored Routine di MySQL!
Testo: MySQL Stored Routines
Data: 14 Febbraio 2008
Versione: 1.0.2 - 14 Febbraio 2012
Autore: mail@meo.bogliolo.name