Appunti di informatica

Parlando in modo estremamente semplice potremmo dire che le Stored Procedures (dette anche SQL-invoked routines o più semplicemente “procedure memorizzate”) sono dei programmi, scritti generalmente in linguaggio SQL (ma non solo), che vengono memorizzati nei database; ad essi viene associato un nome, cioè un identificativo univoco che verrà utilizzato dal database server per effettuarne la chiamata.

Per la creazione di Stored Procedures alcuni Database Managers hanno a disposizione dei veri e propri linguaggi procedurali come per esempio PL/pgSQL per PostgreSQL, PL/SQL per Oracle o Informix Stored Procedure Language (SPL) che è un’estensione di SQL appositamente dedicata a questo scopo.

In MySQL le Stored Procedures sono disponibili soltanto a partire dalla versione 5, questo aggiornamento ha permesso di colmare una lacuna che in un DBMS così diffuso non poteva essere ignorata.

Quando si lavora a progetti di un certo livello, può essere infatti molto importante avere gli strumenti necessari per eseguire alcune parti della logica di un’applicazione all’interno dello spazio stesso dei processi. Il linguaggio SQL è per natura dichiarativo, quindi le Stored Procedures rappresentano una sua estensione procedurale.

Per chiarire quanto appena esposto è necessario fare un piccolo passo indietro e introdurre una distinzione concettuale fondamentale per i linguaggi:

Un linguaggio viene definito dichiarativo o logico quando le istruzioni descrivono le relazione che intercorrono tra i dati; lo sviluppatore in questo caso descrive l’insieme delle relazioni che sussistono tra i dati e il risultato atteso.
Un linguaggio viene invece denominato procedurale quando per esso non si ragiona in termini di relazioni tra dati ma in termini di assegnazione di valori ad uno spazio di memoria; i linguaggi procedurali fanno infatti parte della più ampia famiglia dei linguaggi imperativi.

Il vantaggio relativo all’introduzione delle Stored Procedures è quindi evidente, esse consentono infatti di creare e riutilizzare applicazioni che sono sempre disponibili per il DBMS e le operazioni di manipolazione dei dati.

Le stored procedures (procedure memorizzate) sono presenti a partire dalla versione 5.0 di MySQL.

Una stored procedure è un insieme di istruzioni SQL che vengono memorizzate nel server con un nome che le identifica; tale nome consente in seguito di rieseguire l’insieme di istruzioni facendo semplicemente riferimento ad esso. Vediamo come creare una stored procedure:

 CREATE PROCEDURE nome ([parametro[,...]])
   [SQL SECURITY { DEFINER | INVOKER }] corpo
   //parametri:
   [ IN | OUT | INOUT ] nomeParametro tipo

Come abbiamo detto, ogni procedura è identificata da un nome. Inoltre la procedura è attribuita ad uno specifico database (a partire da MySQL 5.0.1), esattamente come una tabella. Di conseguenza la procedura viene assegnata ad un database al momento della creazione, ed i nomi referenziati al suo interno si riferiranno allo stesso database, a meno che non siano qualificati con un nome di database specifico. In fase di creazione, quindi, se indichiamo il nome della procedura senza specificare il database questa sarà assegnata al db attualmente in uso.

Ogni procedura può avere uno o più parametri, ciascuno dei quali è formato da un nome, un tipo di dato e l’indicazione se trattasi di parametro di input, di output o entrambi. Se manca l’indicazione, il parametro è considerato di input.

La clausola SQL SECURITY stabilisce se, al momento dell’esecuzione, la procedura utilizzerà i permessi dell’utente che la sta eseguendo o quelli dell’utente che l’ha creata (il default è DEFINER).

Vediamo adesso alcuni esempi concreti di stored procedure:

Esempio 1

 CREATE PROCEDURE nomeProc (IN param1 INT, OUT param2 INT)
   SELECT COUNT(*) INTO param2 FROM tabella
   WHERE campo1 = param1;

Questa istruzione crea una procedura chiamata ‘nomeProc’ nel database in uso; la procedura usa un parametro in input e uno in output, entrambi interi, ed effettua il conteggio delle righe in tabella in cui il valore di campo1 corrisponde al primo parametro; il risultato della query viene memorizzato nel secondo parametro attraverso la clausola INTO.

Ecco come eseguire la procedura e visualizzare il risultato:

 CALL nomeProc (5, @a);
   SELECT @a;

Con l’istruzione CALL effettuiamo la chiamata della procedura (immaginando che il database attualmente in uso sia lo stesso a cui la procedura è associata), passando il valore 5 come parametro di input e la variabile @a come parametro di output, nel quale verrà memorizzato il risultato. La SELECT successiva visualizza il valore di tale variabile dopo l’esecuzione.

Nell’esempio appena visto la stored procedure conteneva una semplice SELECT; è possibile invece creare procedure che contengono sintassi complesse comprendenti più istruzioni: in pratica, dei veri e propri script, con la possibilità di controllare il flusso attraverso vari costrutti (IF, CASE, LOOP, WHILE, REPEAT, LEAVE, ITERATE). Inoltre è possibile utilizzare i cursori per gestire i resultset.

Esempio 2

CREATE PROCEDURE nomeProc(IN importo_scelto DOUBLE, OUT totale DOUBLE, OUT numero INT)
SELECT SUM(importo), count(*) INTO totale, numero
FROM tabella
WHERE importo > importo_scelto

Questa istruzione crea una procedura chiamata ‘nomeProc’ nel database in uso; la procedura usa un parametro in input e due in output, due in doppia precisione ed uno intero, ed effettua il conteggio delle righe in tabella in cui importo è maggiore dell'importo_scelto (parametro in input) ed il totale degli importi; i risultati della query vengono memorizzati nel secondo e nel terzo parametro attraverso la clausola INTO.

Ecco come eseguire la procedura e visualizzare il risultato:

CALL nomeProc (@p0 , @p1 , 150);
SELECT @p0 AS totale , @p1 AS numero ;

inoltre, una volta salvata la procedura, è possibile:

cliccare su "ROUTINES",
scegliere la procedura precedentemente creata (nomeProc nel nostro caso),
cliccare su "ESEGUI" per visualizzare la finestra "ESEGUI LA ROUTINE ....." (nomeProc nel nostro caso),
inserire i valori per i parametri in input (importo_scelto nel nostro caso),
cliccare "ESEGUI" per ottenere il risultato desiderato.

Il risultato desiderato (1) sarà associato alla procedura (generata automaticamente) che lo ha prodotto (2):

Esempio 3

DELIMITER //
USE DBProva//
DROP PROCEDURE IF EXISTS MostraINominativiDellaClasse//
CREATE PROCEDURE MostraINominativiDellaClasse(IN VarClasse VARCHAR(255))
BEGIN
SELECT Nominativo FROM Studenti WHERE Classe=VarClasse;
END //
DELIMITER ;

Il parametro VarClasse (mode:IN) contiene la sigla della classe dalla quale si vuole estrarre l'elenco degli studenti. Ad esempio per estrarre tutti gli studenti della '6N' devo digitare il seguente comando:

CALL MostraINominativiDellaClasse('6N');

Esempio 4

DELIMITER $$
USE DBProva$$
DROP PROCEDURE IF EXISTS ContaGliStudentiDellaClasse//
CREATE PROCEDURE ContaGliStudentiDellaClasse(IN VarClasse VARCHAR(255), OUT NrStud INT)
BEGIN
SELECT Count(*) INTO NrStud FROM Studenti WHERE Classe=VarClasse;
END $$
DELIMITER ;

In VarClasse (mode:IN) viene impostata la classe mentre nella variabile NrStud (mode:OUT) viene restituito il numero degli studenti della classe indicata nel primo parametro. Per conoscere il numero di studenti presenti nella fulgida '6N' basta digitare il seguente comando:

CALL ContaGliStudentiDellaClasse('6N',@quanti);
SELECT @quanti AS Numero_Studenti;

Esempio 5

DELIMITER //
CREATE PROCEDURE proc_WHILE()
BEGIN
SELECT COUNT(*) FROM tabella;
SELECT id, nominativo, importo FROM tabella;
END //
DELIMITER ;

CALL proc_WHILE (
);

Esempio 6

La procedura seguente restituisce come risultato una serie di insiemi.

DELIMITER //
CREATE PROCEDURE get_data()

BEGIN

   SELECT Codice, Nome, Popolazione, Continente FROM Nazioni
   WHERE Continente = 'Oceania' AND Popolazione > 10000;

   SELECT Codice, Nome, Popolazione, Continente FROM Nazioni
   WHERE Continente = 'Europa' AND Popolazione > 10000;

  SELECT Codice, Nome, Popolazione, Continente FROM Nazioni
  WHERE Continente = 'America' AND Popolazione > 10000;

END //
DELIMITER ;

Stored procedure

Esempio 7

DELIMITER //
CREATE PROCEDURE elencoUtentiScelti(IN nomeScelto VARCHAR(20) CHARSET utf8, IN cognomeScelto VARCHAR(20) CHARSET utf8)
BEGIN
    SELECT COUNT(*) as numero_record
    FROM tabella;
    SELECT id, nome, cognome, importo
    FROM tabella
    where nome like concat(nomeScelto,'%') AND cognome like concat(cognomeScelto,'%')
    order by nome asc;
END //
DELIMITER ;

SET @p0 = 'a';
SET @p1 = 'r';
CALL elencoUtentiScelti (@p0 , @p1);

Esecuzione di una stored procedure in PHP

Esempio 8

La creazione di una procedura in MySQL è stata semplificata a partire dalla versione 5.0. Il seguente è un esempio di semplice script.

Linea 1 - cancella la procedura se è già esistente nel database.
Linea 2 - imposta il delimitatore, questo permetterà alla procedura di avere più istruzioni SQL.
Linea 3 - contiene le parole chiave per la creazione della procedura "CREATE PROCEDURE" seguito dal nome della procedura. In questo caso, questa la procedura richiederà due parametri: TEAM e YEAR entrambi varchar.
Linea 5 - L'istruzione SQL si trova tra le parole chiave "BEGIN" e "END". nota i riferimenti ai parametri di input nell'istruzione SQL.
Linea 7 - la parola chiave "DELIMITER" chiude la zona in cui viene utilizzato il delimitatore $$. Si possono avere anche variabili in output (OUT) per le query che restituiscono singole righe ma noi non ne abbiamo bisogno per questo esempio perché restituisce più righe.

1. DROP PROCEDURE IF EXISTS get_players
2. DELIMITER $$
3. CREATE PROCEDURE get_players(IN strTeam VARCHAR(100), IN strYear VARCHAR(100))
4. Begin
5.   select nameLast, namefirst
      from Appearances, Master
      where teamID = strTeam and yearID = strYear and Appearances.playerID = Master.playerID
6. END $$
7. DELIMITER ;

Uno dei modi più semplici per lavorare con le stored procedure è attraverso una GUI, vediamo ora come procedere:

Copiare il codice della procedura nell’area di lavoro SQL di PhpMyAdmin e salvare. Se tutto va bene la procedura verrà salvato nel database.
È possibile verificare la presenza della procedura con PhpMyAdmin>Routine. L'istruzione SQL in questa procedura diventa “get_players” basate sulla squadra e l'anno.

Codice PHP

Richiamare la procedura da PHP è semplice. Il codice necessario per chiamare la procedura e la visualizzazione dei risultati in html è quella che segue.

Esempio 9

DELIMITER //
CREATE PROCEDURE `elencoScelti`(IN `nomeScelto` VARCHAR(20) CHARSET utf8, IN `cognomeScelto` VARCHAR(20) CHARSET utf8)
BEGIN
         SELECT id, nome, cognome, importo
         FROM tabella
         where nome like concat(nomeScelto,'%') and cognome like concat(cognomeScelto,'%')
         order by nome asc;
END //
DELIMITER ;

SET @p0 = 'a';
SET @p1 = 'r';
CALL elencoScelti (@p0 , @p1);

Codice PHP

<?php
$nomeScelto = $_POST['nomeScelto'];
$cognomeScelto = $_POST['cognomeScelto'];
$connessione = mysql_connect("localhost","root","") or die('Connessione impossbile: ' . mysql_error());
mysql_select_db("prova", $connessione);
/*
$sql = "select *
from tabella
where nome like “ . $nomeScelto ."% and cognome like " . $cognomeScelto . "%
order by nome asc;";
$risultato = mysql_query($sql);
*/
$risultato = mysql_query("call elencoScelti(‘$nomeScelto’, ‘$cognomeScelto’)") or die(mysql_error());
while($riga = mysql_fetch_array($risultato)):
echo $riga['id'] . " " . $riga['cognome'] . " " . $riga['nome'] . "<br />";
endwhile;
@mysql_close($connessione);
?>