Stored procedure

Dalla versione 5.0, MySQL ha eliminato, una delle più grandi lacune che lamentava, cioè la mancanza delle Stored Procedures. Grazie all’introduzione delle cosidette “procedure archiviate”, ora anche in MySQL si può creare – all’interno della stessa base dati – dei “programmi” con cui è possibile eseguire operazioni complesse sui dati con minor dispendio di risorse ed energie.

Ora dunque, il linguaggio SQL – nato dichiarativo – viene “esteso” in senso procedurale (cioè imperativo).

I vantaggi dell’utilizzo delle Procedure in MySQL sono molteplici:

contribuiscono ad una maggior efficenza in sede di sviluppo (grazie al principio del ri-utilizzo del codice);
migliorano l’astrazione (chi invoca la procedura può ignorarne i dettagli implementativi);
semplificano ed ottimizzano i rapporti tra il client (l’applicativo che utilizza i dati) ed il server (la base dati), riducendo, inoltre, lo scambio di dati in quanto il primo non dovrà inviare lunghe e complesse query ma, semplicemente, dovrà far riferimento al nome della procedura che desidera invocare;
aumentano il livello di sicurezza complessivo in quanto talune procedure sono eseguite direttamente all’interno del database (e non a livello dell’applicativo) ed è, pertanto, possibile studiare logiche di sicurezza più restrittive in merito all’accesso diretto ai dati.

La maggior parte dei sistemi offrono delle estensioni che permettono di scrivere procedure complesse (es., Oracle PL/SQL), con strutture di controllo, variabili, eccezioni, etc.

– si ottiene un linguaggio di programmazione completo.

• Due momenti:

– dichiarazione (DDL)
– invocazione (DML)

• Con architettura client-server sono normalmente:

– invocate dai client
– memorizzate ed eseguite presso i server.

In verità, l’utilizzo di questi strumenti ha delle controindicazioni tra cui – soprattutto – un considerevole aumento nel consumo di risorse (CPU e memoria) per il database server.

Procedure e Funzioni

Le Procedure di MySQL possono essere suddivise in due macro-gruppi:

Procedure: (Stored Procedures in senso stretto) ammettono dei parametri di input e/o di output ma non producono alcun valore di ritorno;
Funzioni: (anche dette Stored Functions) ammettono dei parametri di input e producono un valore di ritorno.

In questo articolo ci occuperemo delle prime.

Creare la prima procedure in MySQL

Per creare una procedura si usa il comando CREATE PROCEDURE seguito dal nome della procedura che vogliamo creare.

Per prima cosa effettuiamo l’accesso, tramite linea di comando, al client mysql e selezioniamo il database all’interno del quale desideriamo creare la nostra Procedure:

USE nomedatabase;

Si sa, infatti, che una procedura deve “appartenere” ad uno specifico database. Una volta selezionato il Database sul quale vogliamo lavorare, digitiamo:

** DELIMITER;

Con questa istruzione andiamo a cambiare provisoriamente il delimitatore da punto e virgola (;) a doppio asterisco (**). La scelta del doppio asterisco è arbitraria, si può scegliere quello che si ritiene più opportuno.

La sostituzione del delimitatore ordinario si rende necessaria nella scrittura della nostra Stored Procedure al fine di poter utilizzare, al suo interno, il punto e virgola senza che l’interprete MySQL lo interpreti – mentre digitiamo – come la fine di un istruzione.

CREATE PROCEDURE TuttiGliStudenti()
BEGIN
SELECT * FROM Studenti;
END **

Attraverso questo codice abbiamo creato, attraverso lo statement CREATE PROCEDURE, una procedura col nome TuttiGliStudenti. Le istruzioni della nostra procedura vengono inseriti tra le keyword BEGIN e END che delimitano, appunto il body della nostra procedura.

Si noti che la keyword END è seguita dal doppio asterisco (**) che indica il termine dell’istruzione.

Per finire, ricordiamoci di ripristinare il delimitatore di default (;):

DELIMITER ;

La clausola DEFINER nella creazione di una Stored Procedure in MySQL

Mediante l’opzione DEFINER (non obbligatoria) è possibile specificare l’utente creatore (e perciò “proprietario”) della Stored Procedure. In mancanza, il DBMS ne assegnerà la titolarità all’utente corrente (è come se avessimo dichiarato esplicitamente DEFINER = CURRENT_USER).

Esempio : prelievo dal magazzino

Specifica

L’utente indica un prelievo dando il codice del prodotto e la quantità da prelevare
• Se la quantità disponibile in magazzino non è sufficiente la procedura si arresta con una eccezione
• Viene eseguito il prelievo, modificando la quantità disponibile in magazzino
• Se la quantità disponibile in magazzino è inferiore alla quantità di riordino si predispone un nuovo ordine d’acquisto.
Interfaccia
procedure Prelievo
( Prod integer,
Quant integer )

Invocazione
Stato iniziale nella base di dati

CodProd

QtaDisp

QtaRiord

170

Realizzazione della procedura

1. Dichiarazione variabili
2. Lettura dello stato
3. Se la quantità disponibile è insufficiente: eccezione
4. Aggiornamento dello stato
5. Se la nuova quantità disponibile è inferiore alla quantità di riordino: emissione di un ordine.
Procedura
procedure Prelievo (Prod integer, Quant integer) is
begin
Q1, Q2 integer;
X exception;
select QtaDisp, QtaRiord into Q1, Q2
from Magazzino
where CodProd = Prod;
if Q1 < Quant then raise(X);
update Magazzino
set QtaDisp = QtaDisp – Quant
where CodProd = Prod;
if Q1 – Quant < Q2 then
insert into Riordino
values(Prod,sysdate,Q2)
end;

Esempio di invocazione

Prelievo(4,150)
Prod=4, Quant=150
select QtaDisp, QtaRiord into Q1, Q2
from Magazzino
where CodProd = Prod;

CodProd

QtaDisp

QtaRiord

170

Q1 = 170, Q2 = 50

Invocazione (continua)

if Q1 < Quant then raise(X) non scatta
update Magazzino
set QtaDisp = QtaDisp – Quant
where CodProd = Prod

CodProd

QtaDisp

QtaRiord

Q1 – Quant < Q2 è vero:
insert into Riordino
values(Prod, sysdate, Q2)

CodProd

Data

QtaRiord

10-10-97

Problemi del progetto di procedure.

Decomposizione modulare delle applicazioni
• Aumento di:
– efficienza
– controllo
– riuso
Aumenta la responsabilità dell’amministratore della base di dati (rispetto al programmatore applicativo)
Si sposta “conoscenza” dalle applicazioni allo schema della base di dati.