74 PDO4 stored procedure

Pubblicato il di

Le stored procedure sono procedure, sotto forma di script SQL, memorizzate in modo permanente sul server, come fossero funzioni SQL richiamabili dalla nostra pagina php. Il nostro obbiettivo non è quello di imparare a crearle, quanto quello di come richiamarle .

Sul nostro MySQL Workbench clicchiamo tasto destro su stored procedure e selezioniamo create new stored procedure. Scegliamo un nome :

CREATE PROCEDURE `domande_per_argomento` ()

tra il BEGIN e END possiamo inserire uno o più comandi SQL, separandoli col punto e virgola. Si possono richiamare le stored procedure con dei paramentri (es chiamo tramite parametro in ingresso soglia di tipo intero)

CREATE PROCEDURE `domande_per_argomento` (in soglia int)

possono essere anche in uscita (out) con la chiocciola quando si richiama o entrambi.

Voglio sapere quante domande ci sono per ciascun argomento (comando SQL visto in precedenza), indicando però un valore di soglia, ovvero voglio gli argomenti che hanno più domande di una data soglia

BEGIN
select argomento, count(*) as ‘numero_domande’
from argomenti join domande on idargomento=fkargomento
group by argomento
having count(*) > soglia
order by argomento;
END

La clausola having mi permette di ottenere le righe che hanno il conteggio domande maggiore di soglia. Salviamo la stored procedure e testiamola in modalità interattiva col comando call:

call domande_per_argomento(1);

Non bisogna confondere le stored procedure con le viste, quest’ultime non permettono il passaggio di parametri (in soglia int). Inoltre e ancora più importante con le viste posso usare solo la select, mentre le procedure store permettono molte più funzioni (sono veri e propri script). Inoltre la stored procedure garantisce un risparmio di banda essendo inviata la prima volta sul server col comando call. In ultimo se un domani dovessi modificare la procedura, lo farei solo sul server e non su tutte le pagine php anche di più progetti.

L’ottimizzazione di query del server analizzerà una volta sola il comando e non tutte le volte che una pagina php lo dovesse inviare.

Vediamolo in php:

$soglia=1;
$comandoSQL = “call domande_per_argomento($soglia)”;

Il valore soglia l’ho impostato fisso, ma immaginatelo arrivato per esempio da una form.

Vediamo una stored pocedure in uscita per calcolare il punteggio medio

CREATE DEFINER=`faghy`@`localhost` PROCEDURE `punteggio_medio`(out media double)
BEGIN
select avg(punti) into media
from risposte;
END

select avg prende il valore calcolato e lo deposita dentro il parametro media. Testiamo in modalità interattiva:

call punteggio_medio(@ris);

ricordare la chiocciola per il parametro in uscita.

Chiediamo il valore del parametro ris con una select:

select @ris;

in php prima dobbiamo chiudere il precedente comando SQL con il comando closeCursor, altrimenti otterremmo un errore

 $risultato->closeCursor();

usiamo un prepared statment inizializzandolo col parametro di out (@ris):

$comando_prepared = $conn_pdo->prepare(“call punteggio_medio(@ris)”);
$comando_prepared->execute();

chiudendo il precedente comando:

$comando_prepared->closeCursor();

Ora comandiamo ancora la select finale e la sua stampa

$risultato = $conn_pdo->query(“select @ris”);
$riga = $risultato->fetch();
echo “Punteggio medio per domanda: ” . $riga[‘@ris’];

Codice completo di stored_procedure.php

<html>
<head>
<title>Esempio con le stored procedure</title>
</head>
<body>

<?php
include($_SERVER[‘DOCUMENT_ROOT’].”/fagtest/my_include/setup_con_DB.php”)

//recupero credenziali da file ESTERNO alla cartella pubblica del sito

try
{
//CONNESSIONE
$conn_pdo = new PDO(“mysql:host=localhost;dbname={$accessData[‘dbname’]}”,
$accessData[‘username’],$accessData[‘password’]);
}

catch (PDOException $e)
{
echo $e->getMessage() . “<br/>”;
echo “Connessione al server fallita. Impossibile procedere. Contattare …”;
die();
}

//ATTIVAZIONE ECCEZIONI PER METODO QUERY
$conn_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$soglia=1;
$comandoSQL = “call domande_per_argomento($soglia)”;

try
{
$risultato = $conn_pdo->query($comandoSQL);
}

catch (PDOException $e)
{
echo $e->getMessage() . “<br/>”;
echo “Nessun risultato ottenuto  …”;

die();
}

 //fetch mode di default: PDO::FETCH_BOTH (associativo e per n. colonna)
while ( $riga = $risultato->fetch() )
{
//usando i nomi delle colonne restituite
echo $riga[‘argomento’].”: “.$riga[‘numero_domande’].$nl;
}
echo “——————————-“.$nl;

$risultato->closeCursor();

$comando_prepared = $conn_pdo->prepare(“call punteggio_medio(@ris)”);
$comando_prepared->execute();

 $comando_prepared->closeCursor();

$risultato = $conn_pdo->query(“select @ris”);
$riga = $risultato->fetch();
echo “Punteggio medio per domanda: ” . $riga[‘@ris’];

//CHIUDIAMO LA CONNESSIONE E LIBERIAMO LE RISORSE OCCUPATE …
$conn=null;
?>
</body>
</html>