Cos'è una stored procedure
Quando inviamo una query al server SQL di fatto viene inviato un batch di comandi SQL.
Oltre a ciò si può fare molto di più ad esempio memorizzare i comandi SQL sul server stesso, come nel caso delle stored procedures, in modo che possano essere usati anche per futuri utilizzi in modo rapido ed efficace. Prima di parlare delle stored procedure è meglio fare chiarezza sul concetto di batch SQL.
I batch SQL
Un batch è sempre formato da uno o più comandi SQL che vengono inviati ed eseguiti sul server SQL. Questo implica una sincronizzazione tra client e server, ogni qualvolta viene inviato un batch di comandi a SQL server è richiesto il riconoscimento del client da parte del server , il parsing dei condi inviati, la loro esecuzione e il ritorno di un codice di stato che identifichi lo stato (positivo e negativo) dell'esecuzione del comando inviato.
Queste operazioni vengono eseguite ogni qualvolta si invia un batch SQL indipendentemente dal numero di comandi coinvolti, per questo motivo è meglio accodare molti più comandi SQL all'interno di un singolo batch per diminuire il carico di lavoro sul server.
Facciamo un esempio, se lanciamo 1000 istruzioni INSERT ognuna in batch singoli
il carico di lavoro sarà sempre maggiore che lanciare un unico batch con all'interno le 1000 istruzioni INSERT. Vediamo il codice
Use pubs
Go /* Inizio del batch SQL */
INSERT INTO authors ('xxxxxxxx','xxxxxxxxx')
Go /* Inizio del batch SQL
successivo*/
INSERT INTO authors ('xxxxxxxx','xxxxxxxxx')
Go /* Inizio del batch SQL
successivo*/
INSERT INTO authors ('xxxxxxxx','xxxxxxxxx')
Go /* Inizio del batch SQL
successivo*/
INSERT INTO authors ('xxxxxxxx','xxxxxxxxx')
-------------------------------------------------------
Go /* Fine del batch SQL */
E così via, oppure inviare tutti I comandi in un unico batch
Go /* Inizio del batch SQL */
INSERT INTO authors ('xxxxxxxx','xxxxxxxxx')
INSERT INTO authors ('xxxxxxxx','xxxxxxxxx')
INSERT INTO authors ('xxxxxxxx','xxxxxxxxx')
INSERT INTO authors ('xxxxxxxx','xxxxxxxxx')
INSERT INTO authors ('xxxxxxxx','xxxxxxxxx')
INSERT INTO authors ('xxxxxxxx','xxxxxxxxx')
INSERT INTO authors ('xxxxxxxx','xxxxxxxxx')
INSERT INTO authors ('xxxxxxxx','xxxxxxxxx')
-----------------------------------------------------------------------------
Go /* Fine del batch SQL */
L'utilizzo di batch per accodare più comandi SQL in una sola passata
veramente molto conveniente sia dal punto di vista delle perfomance, che
della velocità nel traffico di rete!
Usare i comandi e la logica Transact SQL all'interno dei batch facilita enormemente l'esecuzione di operazioni complesse diminuendo enormente la conversazione tra client e server! (maggiore è il dialogo tra client e server maggiore è la dispersione delle prestazioni)
Stored procedure in SQL
Le stored procedure rappresentano il "cuore" della programmazione Transact
SQL. Presenti fin dalle prime versioni di SQL Server sono gruppi di istruzioni
SQL compattati in un modulo e memorizzati nella cache per un successivo utilizzo.
Racchiudere il codice SQL all'interno di procedure memorizzate porta due grossi
vantaggi rispetto ai batch di codice SQL tradizionale:
- Aumento nella velocità di esecuzione del codice SQL e quindi delle performance generali delle applicazioni.
- Aumento della leggibilità e della portabilità del codice e quindi della
scalabilità delle applicazioni.
Le procedure possono essere create sia per uso permanente che temporaneo
ed inoltre possono essere avviate in modo automatico quando viene avviato
SQL Server.
La quantità di istruzione SQL che può accogliere una procedura è enorme: 128
MB invece il numero massimo di parametri che possiamo assegnare ad una procedura
2100.
Le procedure che scriviamo vengono salvate su una tabella di sistema del database sul quale stiamo lavorando dal nome syscomments.
Sql Server stesso possiede una serie di procedure dette di sistema che vengono generate al momento della sua installazione e sono necessarie ad eseguire una serie fondamentale di compiti che vanno dalla creazione dei databases alla loro manutenzione (utenti, permessi, repliche, backup, restore, ecc...).
Noi però ci occuperemo delle stored procedures create dagli utenti di SQL
Server.
Come creare le stored procedure
L'istruzione DDL per la creazione di stored procedure è CREATE PROCEDURE,
vediamone la sintassi completa:
CREATE PROC [ EDURE ] nome_procedura
[ ; numero]
[ { @parametro tipo_di_dati }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ] [ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION
} ]
[ FOR REPLICATION ]
AS istruzione_sql [ ...n ]
Come vedete creare una procedura non è affatto complicato.
E' sufficiente eseguire in un batch l'istruzione CREATE PROCEDURE dichiarando i parametri di input ed output necessari ed infine aggiungere le istruzioni Transact SQL costituenti il corpo vero e proprio della procedura. Vediamo un esempio, creiamo la procedura p_sel_autore nel database pubs, che ci servirà per recuperare un autore dalla tabella authors in funzioni del proprio ID di identificazione
Use pubs
Go /*inizio del batch SQL per la creazione della procedura*/
CREATE PROCEDURE dbo.p_sel_autore ( @au_id VARCHAR(11) = '' ) AS
SELECT
au_lname + ' ' + au_fname AS Nome
FROM
authors
WHERE
au_id = @au_id
Go /*fine del batch SQL per la creazione della procedura*/
Semplice no!?
Ricordatevi però che l'istruzione CREATE PROCEDURE deve essere la prima del
batch altrimenti la creazione della procedura fallirà!
Ad esempio il codice sottostante è errato perché prima dell'istruzione CREATE
PROCEDURE abbiamo una SELECT (Ricordate che il batch è il codice SQL compreso
tra la parola Go e la successiva).
Use pubs
Go /*inizio del batch SQL per la creazione della procedura*/
SELECT
au_lname + ' ' + au_fname AS Nome
FROM
authors
/* La presenza di questa SELECT fa fallire la creazione dell'oggetto*/
CREATE PROCEDURE dbo.p_sel_autore ( @au_id VARCHAR(11) = '' ) AS
SELECT
au_lname + ' ' + au_fname AS Nome
FROM
authors
WHERE
au_id = @au_id
Go /*fine del batch SQL per la creazione della procedura*/
Nella dichiarazione dei parametri di input di una procedura possiamo assegnare per questi dei valori di default, questo ci è molto utile nella costruzione di procedure efficaci perché i valori che ci arriveranno dai parametri saranno sempre coerenti e consistenti con la logica della routine.
Se un parametro non possiede un valore di default al momento dell'esecuzione vi verrà chiesto di passare un valore specifico per quel parametro altrimenti SQL Server segnalera un errore. Vedremo più avanti come devono essere passati i paramentri ad una stored procedure.
Le opzioni di CREATE PROCEDURE
Nella sintassi dell'istruzione CREATE PROCEDURE abbiamo visto che esistono alcune opzioni che possiamo specificare durante la creazione di una procedura RECOMPILE e ENCRYPTION.
La prima obbliga la ricompilazione della procedura ogni qualvolta viene eseguita, sostanzialmente la procedura non viene messa in cache e non viene creato un piano di esecuzione ad hoc richiamabile.
La seconda permette di criptare il contenuto della procedura cosicchè nessuno
all'infuori del proprietario del codice sorgente avrà accesso al suo contenuto.
Nidificare le stored procedure
Le procedure possono richiamare ed essere richiamate da altre procedure e così via fino ad un livello di nidificazione pari a 32. Questo limite è imposto
da SQL Server per impedire errori di overflow.
Al contrario una stored procedure può chiamare altre centinaia di stored procedure al suo interno.
Eseguire una procedura
Ci sono diversi modi per chiamare una procedura, per la precedente possiamo usare due differenti sintassi in funzione del modo con sui vengono passati i parametri (se esistono ovviamente), una implicita ed una esplicita. Per essere più chiari creiamo una nuova procedura più complessa della precedente con più parametri di input, ecco il codice
Use pubs
Go
CREATE PROCEDURE dbo.p_sel_autore2 ( @state VARCHAR(2) , @contract BIT
) AS
SELECT
au_lname + '
' + au_fname AS Nome
FROM
authors
WHERE
state = @state
AND
contract = @contract
RETURN(0)
Go
/*
Nella modalità implicita il nome del parametro di input non viene specificato ed passato correttamente in funzione del suo ordine di chiamata nella procedura
*/
EXEC dbo.p_sel_autore2 'CA', '1' -- Questa chiamata è corretta
-- Ma se invertiamo i parametri l'esecuzione è errata
EXEC dbo.p_sel_autore2 'CA', '1'
/*
Nella modalità esplicita invece il nome del parametro di input viene specificato e passato senza che l'ordine di chiamata nella procedura sia importante
*/
-- Questa chiamata è corretta
EXEC dbo.p_sel_autore2 @state = 'Ca', @contract = '1'
-- Ed ora invertiamo i parametri per vedere cosa succede
EXEC dbo.p_sel_autore2 @contract = '1', @state = 'CA'
-- Ma anche questa chiamata corretta, pur invertendo l'ordine di chiamata
-- questo perché abbiamo specificato i nomi dei parametri @contrat e @state in
-- in abbinamento ai valori appropriati
I più attenti avranno osservato che questa procedura possiede una istruzione
nuova rispetto alla precedente: RETURN. La parola chiave RETURN provoca l'uscita
incondizionata dalla procedura, in qualunque posizione essi si trovi nel codice
il parser quando la incontra esce e non esegue le istruzioni sottostanti.
Oltre ad uscire possiamo abbinare un codice di uscita (rappresentato da un
numero intero) che aggiunge maggiori informazioni alla nostra istruzione RETURN.
Di default il valore di RETURN è 0, se invece si verifica un errore il valore
sarà diverso da 0 ovviamente. Anche noi possiamo assegnare dei valori all'istruzione RETURN, ad esempio RETURN(-100) esce dalla procedura con codice di errore uguale a -100.
Come facciamo a catturare questo valore di uscita? Vediamo un esempio
DECLARE @ret INTEGER
EXEC @ret = dbo.p_sel_autore2
@contract = '1', @state='CA'
PRINT @ret
Stamperà 0, se invece nelle parentesi tonde mettiamo il valore RETURN(-100),
l'istruzione PRINT stamperà -100.
Alterare ed eliminare le stored procedure
Ci sono altre due istruzioni importanti per lavorare con le procedure ALTER
e DROP PROCEDURE.
La prima ci permette di modificare il contenuto di una procedura una volta
che è stata create, per esempio cambiamo il contenuto della procedura p_sel_autori, facciamo in modo di recuperare i primi 10 autori in ordine decrescente
Use pubs
Go /*inizio del batch SQL per la creazione della procedura*/
ALTER PROCEDURE dbo.p_sel_autore ( @au_id VARCHAR(11) = '' ) AS
SELECT TOP 10
au_lname + ' ' + au_fname AS Nome
FROM
authors
WHERE
au_id = @au_id
ORDER BY au_fname DESC
Go /*fine del batch SQL per la creazione della procedura*/
Semplice no! Se invece vogliamo eliminare la procedura sarà sufficiente usare
(con attenzione!) l'istruzione DROP PROCEDURE.
Use pubs
Go /*la procedura è eliminata*/
DROP PROCEDURE dbo.p_sel_autore
Ricordate bene che:
- Le variabili in Transact SQL sono locali
ed il loro contesto è circostanziato alla sessione in cui vengono create. - Per aumentare l'efficacia di esecuzione di una procedura nella sua
esecuzione è bene specificare il nome del proprietario dell'oggetto procedura
(tipicamente dbo) ed anche il database nel quale è contenuta
Regole di naming
Assegnare un nome ad una procedura è un compito in apparenza facile ma dobbiamo seguire alcune linee di condotta se non vogliamo perderci nel marasma più completo:
- Il nome deve essere pertinente con l'azione compiuta dalla procedura affinchè sia il esplicativo possibile
- Il nome non deve essere eccessivamente lungo, se potete usate delle abbreviazioni, ins per inserisci, upd per update, del per delete, ecc...
- Scegliete un prefisso per indicare l'oggetto precedura, Microsoft sconsiglia
di usare sp_ perché indica le procedure di sistema fornite da SQL Server, io uso sempre p_.
Ecco alcuni esempi: p_ins_autori, p_del_autori, p_sel_top_news, p_sel_xml_autori, e così via!
Così facendo già dal nome della procedura possiamo intuire grosso modo il
compito che essa svolge!
Alcune stored procedure di sistema molto utili
Esistono alcune procedure di sistema che ci possono aiutare a gestire il
lavoro quotidiano con le stored procedure:
sp_help: permette di avere informazioni sulla procedura (uso, tipo di parametri, ecc...)
uso: EXEC sp_help nome_della_procedura
sp_helptext: permette di vedere il testo di una stored procedure
uso: EXEC sp_helptext nome_della_procedura
sp_depends: per scoprire le dipendenze da altri oggetti
uso: EXEC sp_depends nome_della_procedura
sp_rename: per rinominare una procedura
uso: EXEC sp_rename vecchio_nome_della_procedura, nuovo_nome_della_procedura