La programmazione del database con Transact SQL

Nella prime lezioni del tutorial abbiamo definito il Transact SQL come una
collezione di estensioni capaci di aumentare le potenzialità dell'ANSI-SQL 92
(cioè il linguaggio SQL standard).

Oltre a questo Transact SQL aggiunge costrutti tipici della programmazione
come istruzioni per il controllo del flusso (if e while per esempio),
variabili, gestione degli errori, ecc... che ci mettono in grado di fare esercizio
di programmazione dei databases.

Possiamo così creare con il T-SQL veri e propri programmi (le stored procedure e i triggers) sfruttando da un lato la potenza nell'interrogazione di basi dati delle istruzioni SQL e dall'altro funzioni e costrutti del T-SQL per la creazione della logica del programma.

Dopo avere esamininato i tipi di dati passiamo a descrivere ‚ sintassi e funzioni del Transact SQL, nelle prossime lezioni invece cominceremo a parlare dei costrutti T-SQL e della progettazione di stored procedure e trigger.

Convenzioni e sintassi nel T-SQL

Ecco le convenzioni usate per i riferimenti alla sintassi T-SQL nelle prossime lezioni del tutorial.

Convenzione	Usata per
MAIUSCOLO	Le parole chiave del Transact-SQL.
Corsivo	Parametri forniti dall'utente
| (barra verticale)	Elementi della sintassi separati con parentesi quadre o graffe. E' possibile scegliere solo uno uno degli elementi della lista.
[ ] (quadre)	Elementi opzionali della sintassi. Non devono essere digitate le parentesi quadre.
{} (graffe)	Elementi richiesti della sintassi. Non devono essere digitate le parentesi quadre.
[,...n]	Gli elementi che precedono questa indicazione possono essere ripetuti n volte. Devono essere separati da virgole.
[ ...n]	Gli elementi che precedono questa indicazione possono essere ripetuti n volte. Devono essere separati da spazi.
grassetto	Indica il nome di un database, di un oggetto di un database, nome di una colonna, tipi di dati, utilità, ecc... deve essere digitato così come visualizzato.
<etichetta> ::=	Il nome di un blocco di sintassi

Qualificare gli oggetti del database

Transact SQL fa riferimento ai nomi degli oggetti del database attraverso la
seguente sintassi, composta tipicamente da quattro elementi:

 [
    server_name.[database_name].[owner_name].
    | database_name.[owner_name].
     owner_name.
    ]
]
object_name

1. servername è il nome del SQL Server (remoto o linked)
2. database_name è il nome del database all'interno del SQL Server
3. owner_name è il nome del proprietario dell'oggetto
4. object_name è il nome dell'oggetto a cui voglio fare riferimento

Quando facciamo un riferimento ad uno specifico oggetto di un database, abbiamo diversi modi per identificarlo in modo corretto. La stringa di identificazione completa è:

server.database.propietario.oggetto >> mioserver.pubs.dbo.authors

Il nome del server, del database e del proprietario sono comunque opzionali
e possono essere vicariati dal punto (.) per indicarne la posizione.

Sono perciò validi i seguenti riferimenti per la tabella authors nelle seguenti istruzioni SELECT:

q       select * from mioserver.pubs.dbo.authors

q       select * from mioserver..dbo.authors

q       select * from mioserver.pubs..authors

q       select * from pubs..authors

q       select * from mioserver...authors

q       select * from authors

Nota Bene:

Per semplicità farò un elenco delle funzioni e degli operatori principali,
per completare la lettura vi consiglio i Books Online di SQL Server (è l'ottimo help integrato in SQL Server accessibile premendo F1 dal Query Analyzer).

Gli operatori

Sono dei simboli che specificano una azione o compiono operazioni su una o
più espressioni. Si dividono in diverse categorie:

1. Operatori di matematici
2. Operatori di assegnamento
3. Operatori Bitwise
4. Operatori di comparazioni
5. Operatori logici
6. Operatori di concatenazione delle stringhe
7. Operatori unari

Matematici

Lavorano su numeri (I tipi di dati in SQL Server) e compiono le seguenti operazioni matematiche:

Operatore	Azione
+ (Aggiunge)	Addizione
- (Sottrae)	Sottrazione
* (Moltiplica)	Moltiplicazione
/ (Divide)	Divisione
% (Modulo)	Ritorna l'intero di una divisione

Assegnamento

T-SQL ha un fondamentale operatore di assegnamento l'uguale =

Alcuni esempi di utilizzo, sia nelle variabili locali che all'interno di una
SELECT:

USE Northwind
GO
DECLARE @temp INT
SET @temp = 1
GO
SELECT PrimaColonna = 'xyz',
SecondaColonna = ProductID
FROM Products
GO

Comparazione

Fanno comparazioni tra espressioni (numeriche e testuali). Non lavorano sui
tipi di dati text, ntext, image.

Operatore	Azione
= (Uguale)	Uguale a
> (Maggiore di)	Maggiore di
< (Minore id)	Minore di
>= (Maggiore uguale a)	Maggiore uguale a
<= (Minore uguale a)	Minore uguale a
<> (diverso da)	Diverso da
!= (diverso da)	Non SQL-92 standard
!< (non minore di)	Non SQL-92 standard
!> (non maggiore di)	Non SQL-92 standard

I risultati di una operazione di comparazione in T-SQL danni tre possibili
risultati: TRUE, FALSE, e UNKNOWN (NULL). In seguito vedremo come meglio gestire la logica a tre vie del T-SQL nelle interrogazioni ai database. Se invece l'operatore ritorna dei valori TRUE o FALSe riccadiamo nella classica logica booleana.

Logici

Saggiano la verità o falsità di alcune condizioni, ritornano un tipo dato booleano.

Operatore	Azione
ALL	TRUE se tutte le condizioni sono TRUE.
AND	TRUE se tutte le espressioni booleane sono TRUE.
ANY	TRUE se almeno una delle condizioni è TRUE.
BETWEEN	TRUE se l'operando è all'interno del range.
EXISTS	TRUE se una subquery contiene una riga qualsiasi.
IN	TRUE se l'operando è uguale ad uno della lista di espressioni.
LIKE	TRUE se l'operando trova il valore cercato.
NOT	Inverte il valore di ogni operando booleano.
OR	TRUE se ogni espressione booleana è TRUE.
SOME	TRUE se alcuni set di comparazione sono TRUE.

Concatenazione

Nel T-SQL per concatenare le stringhe si utilizza l'operatore +

Proviamo a vedere come concatenare il nome ed il cognome di un autore del database pubs:

use pubs
SELECT au_lname + ' - ' ‚ + au_fname from authors

Semplice ! All'interno delle nostre SELECT possiamo così creare stringhe
anche complesse e produrre set di risultati piuttosto elaborati.

Le funzioni del Transact SQL

Come la maggior parte dei linguaggi di programmazione il Transact SQL è fornito con una serie di funzioni (dette built-in) in grado di aiutare lo sviluppatore nella manipolazione dei dati. Possiamo suddividere le funzioni SQL Server 7 in quattro grosse categorie in base ai dati sulla quale operano:

1. Funzioni per le stringhe
2. Funzioni matematiche
3. Funzioni per le date
4. Funzioni di sistema

Funzioni per le stringhe

Possiamo suddividerle in quattro gruppi:

Funzioni per il parsing ed il calcolo della lunghezza di una stringa
datalength (char_expr)	Ritorna un intero che indica la lunghezza di una stringa.
substring (expression,start, length)	Ritorna una parte di una stringa.
right (char_exp, int_expr)	Recupera n caratteri (indicati da un numero intero) dalla parte destra di una stringa
left (char_exp, int_expr)	Recupera n caratteri (indicati da un numero intero) dalla parte sinistra di una stringa
Funzioni per la manipolazione di una stringa
upper (char_expr)	Converte tutti i caratteri della stringa in maiscolo
lower (char_expr)	Converte tutti i caratteri della stringa in minuscolo.
space (int_expr)	Crea una stringa di spazi, la sua lunghezza è indicata dal numero intero nell'argomento .
replicate (char_expr, int_expr)	Ripete una stringa un certo numero di volte, indicato dal numero intero nell'argomento.
stuff (char_exp1, start, length, char_expr2)	Elinima una determinata porzione di stringa sostituendolo con un'altra, partendo da uno specifico punto di partenza.
reverse (char_expr)	Inverte una stringa.
ltrim (char_expr) e rtrim (char_expr)	Ritorna una stringa dopo averla ripulita degli spazi più a sinistra.
Funzioni per la conversione di una stringa
ascii (char_expr)	Ritorna il codice ASCII per il carattere più a sinistra della stringa.
char (int_expr)	Converte un intero rappresentante il codice ASCII in una stringa.
Funzioni per la ricerca all'interno di una stringa
charindex ( expression1 , expression2 [ , start_location ] )	Ritorna la posizione di partenza di una stringa all'interno di un'altra stringa, come numero intero.
Patindex ( '%pattern%' , expression )	Ritorna la posizione di partenza di una pattern all'interno di un'altra stringa, come numero intero.

Ecco alcuni esempi per provare le funzioni stringa:

use pubs
go
/* Esempio: datalength() */
select datalength('SQL Server 7.0') as [Lunghezza della stringa]
select distinct type, datalength (type), datalength (rtrim(type)) from titles
/* Esempio: substring() */
select substring('SQL Server 7.0',0,11) as [Parte di una stringa]
/* Esempio: right() */
select right('SQL Server 7.0',11) as [Parte destra di una stringa]
/* Esempio: left() */
select left('SQL Server 7.0',11) as [Parte sinistra di una stringa]
/* Esempio: upper() e lower() */
select lower(au_fname) as [Nome],
upper(au_lname) as [Cognome]
from authors
/* Esempio: space() */
select au_fname + space(2) + au_lname as [Nome e Cognome]
from authors
/* Esempio: replicate() */
select replicate('****', 2) + space(2) + au_fname + space(2) + replicate('****',
2)
as [Nome dell'autore]
from authors
/* Esempio: stuff() */
SELECT STUFF('Paperino e Nonnapapera', 1, 8, 'Pippo')
/* Esempio: reverse() */
SELECT reverse('Paperino e Nonnapapera') as [Stringa rovesciata]
/* Esempio: ltrim() e rtrim() */
SELECT rtrim(ltrim(' Paperino e Nonnapapera ‚ ')) as [Stringa ripulita]
/* Esempio: ascii() */
SELECT ascii('a') as [Codice ASCII]
/* Esempio: char() */
SELECT char(97) as [Carattere da Codice ASCII]
/* Esempio: charindex() */
SELECT charindex('tutti','Ciao a tutti',0) as [Posizione stringa]
/* Esempio: charindex() */
SELECT patindex('%a %','Ciao a tutti') as [Stringa ripulita]

Funzioni per le date
Servono a lavorare e soprattutto a manipolare le date:

getdate ()	Data corrente.
datename(datepart, date_expr)	Ritorna una specifica parte di una data come nome, il valore è una stringa.
datepart (datepart, date_expr)	Ritorna una specifica parte di una data come numero, il valore è un intero.
datediff (datepart, date_expr1, date_expr2)	Ritorna l'intervallo tra due date, la misurazione fa riferimento alla parte di data specificata.
dateadd (datepart, number, date_expr)	Ritorna una nuova data, creata aggiungendo un valore intero ad un data di input, l'aggiunta fa riferimento alla parte di data specificata

Per le parti di data presenti come argomento delle funzioni facciamo riferimento
alla tabella sottostante:

Parte di data	Abbrevizione
Year	yy, yyyy
quarter	qq, q
Month	mm, m
dayofyear	dy, y
Day	dd, d
Week	wk, ww
Hour	hh
minute	mi, n
second	ss, s
millisecond	Ms

Ecco alcuni esempi:

use pubs
go
/* Esempio: getdate() */
select getdate()
/* Esempio: datediff() */
select title,
datediff(yy, pubdate, getdate()) as [Anni dalla data di pubblicazione]
from titles
/* Esempio: datetime() */
select title, datename (mm, pubdate) from titles
select title, datename (dd, pubdate) from titles
/* Esempio: dateadd() */
select dateadd (dd, 2, getdate()) as [Data tra due giorni]

Funzioni matematiche

Sono funzioni scalari utili per fare trasformazioni matematiche su input vari
(di tipo numerico).

abs (numeric_expr)	Data corrente.
ceiling (numeric_expr)	Ritorna l'intero più grande od uguale all'espressione numerica di input
rand ‚ (int_expr)	Ritorna un numero float casuale tra 0 e 1

Esempi di utilizzo:

use pubs
go
/* Esempio: abs() */
select abs(-123) as [Valore assoluto]
/* Esempio: ceiling() */
select ceiling(-123.23) as [Valore assoluto]
/* Esempio: rand() */
Select round(rand() * 10,0) as [Numero casuale]

Ci sono anche altre funzioni matematiche ricordiamo tra le altre: exp (float_expr), pi (), ‚ round (numeric_expr, int_expr), sprt (float_expr) e le funzioni trigonometriche.

Funzioni di sistema

Verranno esaminate più avanti, riguardano operazioni e ritornano informazioni
e valori su oggetti di SQL Server, settaggi ed altro ancora.