INDICE 1.01 - La programmazione con T-SQL 1.02 - Quello che SQL non può fare 1.03 - Introduzione 1.04 - L’architettura di SQL Server 7.0 1.05 - Gli oggetti di un database 1.06 - Cosa sono i metadati? 1.07 - La sicurezza del database 1.08 - Il mondo di SQL Server 1.09 - Gli strumenti di SQL Server 1.10 - Come ci colleghiamo ad SQL Server? 1.11 - Gestire i dati dal Query Analyzer 1.12 - Cominciamo! 1.13 - I tipi di dati in SQL Server 1.14 - La programmazione del database con Transact SQL 1.15 - Lavorare con le variabili 1.16 - Controllo del flusso nel T-SQL 1.17 - Le viste 1.18 - Le stored procedure 1.19 - 1.20 - Dal sito HTML.it Guida linguaggio T-SQL Ritorna all'indice 1.01 - La programmazione con T-SQL Se avete dimestichezza nella programmazione dei linguaggi procedurali di 3 generazione (3GL) come il BASIC o il COBOL dovete fare uno sforzo per dimenticare le vostre tecniche di programmazione tradizionali nell’approccio alla programmazione con T-SQL. Infatti sia il BASIC che il COBOL trattano i dati in maniera totalmente opposta a quella di T-SQL. Il programmatore che lavora con i 3GLs deve dire al programma cosa deve fare dei dati che sta processando in modo esatto e preciso, elemento dopo elemento. Questo stile di programmazione potremmo chiamarlo riga per riga. Al contrario i programmi scritti per i databases ragionano in modo differente perchè preferiscono operare su un insieme di dati (o risultati). Quando passo un’istruzione ad SQL Server lui ritorna esattamente il gruppo di dati che soddisfano quella richiesta. Non devo preoccuparmi di sapere cosa succede o cosa fa SQL Server per avere quei risultati. Per rendere meglio queste differenze proviamo a fare una analogia di questo tipo. Supponiamo di voler sistemare la nostra vecchia Moto Guzzi. Dobbiamo recarci da un fornitore ed acquistare un elenco di pezzi da sostituire. Potremmo avere due approcci: Con il carrello della spesa giro per il negozio e fila dopo fila passo tra gli scaffali cercando i pezzi che mi servono. Quando trovo il pezzo di ricambio lo metto nel carrello e continuo la ricerca fino alla fine del mio elenco. Quindi vado alla cassa e pago. Questo è un esempio di approccio riga per riga. Oppure consegno la lista mia della spesa ad un commesso del negozio e lui dopo alcuni minuti ritorna magicamente con tutti i pezzi che mi servono per sistemare la mia moto. Questo è un esempio di approccio per insieme di dati. Nel secondo caso il commesso impiega poco tempo per trovare i pezzi che mi servono, perché a differenza di me conosce la posizione dei pezzi negli scaffali. Per scrivere i programmi per i databases (cioè le procedure memorizaate) dobbiamo avere ben chiaro questo tipo di approccio. Ritorna all'indice 1.02 - Quello che SQL non può fare Facciamo una breve panoramica delle possibilità che ci offre T-SQL e quindi dei limiti di SQL: Usare il costrutto condizionale if … else per imporre delle condizioni alla esecuzione di comandi T-SQL o SQL If (select count(*) from authors) = 0 Print ‘Non ci sono autori nella tabella’ Else Print ‘Ci sono autori nella tabella’ produce un output simile Ci sono autori nella tabella Usare una istruzione SELECT per stampare il nome e la versione dell’SQL Server in uso select @@SERVERNAME, @@version produce un output simile LUCA Microsoft SQL Server 7.00 – 7.00.623 (Intel X86) Nov 27 1998 22:20:07 Copyright (c) 1988-1998 Microsoft Corporation Desktop Edition on Windows 4.10 (Build 1998: ) (1 row(s) affected) Usare una istruzione SELECT senza FROM, per creare dei contatori di record per due diverse tabelle select c_authors=(select count(*) from authors), c_titles=(select count(*) from titles) produce un output simile c_authors c_titles ———– ———– 23 18 1 row(s) affected) Usare una procedura particolare di T-SQL dal nome xp_cmdshell per eseguire qualsiasi comando da shell, come visualizzare i files contenuti nel disco fisso C exec master..xp_cmdshell 'dir c:' produce un output simile output ———————————————————————————————————————————————— NULL Il volume nell’unità C è LAPTOP Numero di serie del volume: 3D6F-10E0 Directory di C: NULL COMMAND COM 96.200 16/05/98 4.01 COMMAND.COM WINDOWS DIR 01/12/99 20.07 WINDOWS CONFIG SYS 282 14/03/01 20.04 CONFIG.SYS MSSQL7 DIR 15/09/00 10.26 MSSQL7 WINZIP LOG 74.732 16/05/01 20.17 winzip.log NETLOG TXT 12.766 25/05/99 16.55 NETLOG.TXT LOGO SYS 129.078 24/06/98 16.44 LOGO.SYS —————————————————————————- Queste sono solo alcune delle caratteristiche avanzate che il T-SQL possiede rispetto all’SQL Standard e che nel proseguire delle lezioni riuscire a padroneggiare. Ritorna all'indice 1.03 - Introduzione Prima di approfondire il Transact-SQL di Microsoft credo sia necessario fare un’overview su SQL Server. SQL Server 7.0 (la versione di SQL Server a cui fa riferimento questo corso) rappresenta una delle release più stabili che Microsoft abbia mai prodotto (oggi esiste in commercio la versione 2000 di SQL Server). I miglioramenti apportati alla versione 7.0 sono stati numerosi: Miglioramenti architetturali (a livello del codice): 1. Incremento della velocità di elaborazione Microsoft ha ridisegnato il motore di interrogazione di SQL. 2. Supporto alle interrogazioni complessi Funzioni di hash e fusione nelle operazioni di join, operazioni multi-indice 3. Triggers ricorsivi Sono stati introdotti trigger che chiamano sè stessi. 4. Supporto a UNICODE Supporta pienamente lo standard UNICODE Miglioramenti amministrativi: 1. Gestione della memoria La memoria viene allocata dinamicamente in base alle esigenze del sistema 2. Gestione del disco Il database aumenta e diminuisce le dimensioni a seconda delle dimensioni Miglioramenti per lo sviluppatore: 1. Nuove funzioni e potenzialità per T-SQL Introduzioni di nuove istruzioni come: ALTER PROCEDURE, ALTER TRIGGER, ALTER TABLE, ALTER VIEW 2. Nuovi strumenti Microsoft English Query, SQL Server Agent, OLAP Service, Index Tuning Wizard, ecc… 3. Nuovi tipi di dati I tipi dati char, varchar, binary, varbinary ora possono memorizzare fino a 8000 byte di informazioni Ovviamente questi sono solo alcuni dei miglioramenti apportati a SQL Server 7.0. Ritorna all'indice 1.04 - L’architettura di SQL Server 7.0 Se paragoniamo SQL Server ad un corpo umano il database potrebbe rappresentare la singola cellula. Il database è la struttura principale di SQL Server e fornisce l’ambiente per archiviare e controllare i dati. Come rappresentato in figura 1, SQL Server ha due grosse categorie di databases: 1. Databases di sistema 2. Databases utente All’ interno dei databases di sistema SQL Server memorizza tutte le informazioni e gli oggetti necessari al suo funzionamento. I databases di sistema che SQL Server crea al momento dell’installazione sono quattro: 1. MASTER contiene le informazioni sul server ad alto livello 2. TEMPDB contiene le tabelle e gli oggetti temporanei 3. MODEL contiene il modello per la creazione di un database tipo 4. MSDB informazioni per il funzionamento di SQL Server Agent (Jobs, Web Assistant, ecc..) Nei databases utente invece memorizziamo i dati utente e gli oggetti del database che vedremo successivamente. Il numero di databases di questo tipo che possiamo creare all’interno di SQL Server è invece 32734. Sempre al momento dell’installazione SQL Server ci installa due databases utente di default per fare le nostre prove: 1. pubs 2. northwind NOTA BENE: Per gli scopi del corso e sopratutto per la maggior parte degli esercizi farò riferimento al database pubs. Ritorna all'indice 1.05 - Gli oggetti di un database Figura 2 - Oggetti del database In figura 2 possiamo vedere gli oggetti che sono contenuti in un database: 1. Tabelle o Tables Memorizzano i dati che inseriamo nel nostro database, sono tra loro in relazione reciproca. Sono fatte da colonne e da righe. Una tabella può avere fino a 1024 colonne e 8092 bytes per riga. Il numero di tabelle per database può arrivare fino a due miliardi. 2. Tipi di dati o Data Type Definiscono i tipi di dati che possono essere inseriti nelle colonne, possono essere definiti dall’utente. 3. Obblighi o Constraint Servono a rafforzare l’integrità del database. 4. Default Assegna valori predefiniti ad una determinata colonna. 5. Regole o Rule Definiscono vincoli ai dati che vengono inseriti, servono a rafforzare l’integrità del database. 6. Indici Servono ad ottimizzare l’accesso ai dati contenuti nelle tabelle. 7. Viste o View Sono tabelle generate con colonne prese da una o più tabelle. 8. Procedure Memorizzate o Stored Procedures Sono set di istruzioni T-SQL, sono dei veri e propri programmi per i databases. 9. Attivatori o Trigger Sono procedure memorizzate che si attivano in modo autonomo in base allo scatenarsi di determinati eventi come INSERT, UPDATE, DELETE. L’insieme degli oggetti di database che costituiscono il database prende il nome di SCHEMA. La progettazione di tutti questi oggetti rappresenta il modello di dati. Tutti questi oggetti possono essere creati dall’utente del database in tre modi differenti: 1. Attraverso le procedure guidate di SQL Server 7.0 (wizard) 2. Con istruzioni SQL (CREATE TABLE, CREATE PROCCEDURE, ecc..) 3. Attraverso l’Enterprise Manager Console Ritorna all'indice 1.06 - Cosa sono i metadati? SQL Server tiene traccia all’interno di ogni database degli oggetti che vengono creati, modificati o eliminati. Le informazioni che descrivono gli oggetti del database sono chiamate metadati. I metadati sono organizzati in un dizionario dei dati che contiene istruzioni come CREATE o ALTER. Il dizionario dei dati è organizzato in tabelle di sistema (iniziano con il prefisso sys) che sono contenute all’interno di ogni databases utente che viene creato all’interno di SQL Server Figura 3 - Tabelle di un database Nella figura qui sopra possiamo vedere uno screenshot delle tabelle contenute in un database. Ritorna all'indice 1.07 - La sicurezza del database In un database multi-utente la sicurezza è importante. In SQL Server la sicurezza è implementata su due livelli: 1. a livello del server (logins) 2. a livello del database (users) Per questo motivo ogni volta che accediamo a SQL Server dobbiamo essere provvisti login valida. La login è composta da uno User ID e da una password. Per ogni login all’interno di SQL Server deve esserci un corrispondente utente all’interno di uno specifico database. L’utente deve avere i privilegi opportuni per poter compiere le varie operazioni all’interno del database. Server login ID Una server login valida è necessaria per accedere ad SQL Server, il login account è fatta di 3 componenti: SERVER LOGIN ID (luca) SERVER PASSWORD (lucky) DEFAULT DATABASE (dove accedere dopo la connessione, CorsoTSQL) Esiste una login molto particolare che viene creata per default da SQL Server al momento dell’installazione: il system administrator o sa, il sa è abilitato a creare database ed utenti a fare backup, ecc.. insomma ad amministrare il sistema. Una volta creato un database il sa creerà gli utenti che potranno accedere al database, assegnando i privilegi ed i ruoli opportuni. Ricordiamoci che il creatore del database ne diventa proprietario cioè: database owner (dbo). Ogni oggetto nel database ha un owner. Ritorna all'indice 1.08 - Il mondo di SQL Server Ritorna all'indice 1.09 - Gli strumenti di SQL Server SQL Server è un prodotto molto completo e ci fornisce una vasta gamma di strumenti (grafici e non) utili a monitorare, amministrare, interrogare, ecc.. SQL Server. Ecco un elenco dei tools che abbiamo a disposizione: 1. Amministrazione: - SQL Enterprise Manager 2. Interrogazione: - Utility da linea di comando isql - SQL Query Analyzer - Microsoft Query - SQL Server WWWeb Assistant - Microsoft Access 3. Monitoraggio: - SQL Performance Monitor - SQL Trace 4. Documentazione: - SQL Server Book Online - Help vari (ODBC,ecc..) 5. Configurazione: - Client configuration utility - Server Network utility 6. Altri: - SQL Server Service Manager Ritorna all'indice 1.10 - Come ci colleghiamo ad SQL Server? Ci sono vari modi di parlare con SQL Server dall’esterno: 1. Attraverso utility client come SQL Query Analyzer 2. Altri database Microsoft: ACCESS 3. Linguaggi di programmazione: Visual Basic 4. Da Internet attraverso le pagine ASP Attraverso uno dei metodi visti sopra possiamo collegarci ed inviare dei comandi T-SQL a SQL Server. Ritorna all'indice 1.11 - Gestire i dati dal Query Analyzer Dopo aver parlato delle origini del T-SQL ed aver visto in modo sommario l’architettura di SQL Server è venuto il momento di passare all’azione cominciando a lanciare le prime query in T-SQL. Per fare questo dobbiamo conoscere uno strumento molto importante: il Query Analyzer (d’ora in poi QA). Il QA è una applicazione interagisce con il motore di SQL Server e permette di fare varie cose: 1. progettare, creare, testare, ottimizzare ed eseguire query e scripts in T-SQL; 2. fare il debug di query e scripts usando il T-SQL Debugger integrato; 3. avere informazioni aggiuntive sulle query, come la stima dei piani di esecuzione e la rappresentazione grafica dei risultati; 4. avere informazioni sugli indici e sulle loro performance, sulla necessità o meno del loro utilizzo. QA è il compagno ideale per chi sviluppa con T-SQL, comunque potremmo usare anche tools simili di terze parti, pagando ovviamente! I passi per lavorare con Query Analyzer Per prima cosa lanciamo il QA (start>programmi>Microsoft SQL Server 7.0>Query Analyzer). La schermata che ci appare è del tutto simile a quella in figura 1, ora dobbiamo fornire i parametri di login(login name, password) utili per la connessione ad un SQL Server: Figura 1: Pannello connessione Una volta validate le informazioni di login, ci appare la schermata principale del QA con i suoi menu ed all’interno una FINESTRA DELLE QUERY o QUERY WINDOW (d’ora in poi QW) La finestra delle query è lo strumento principale con il quale noi andremo ad interagire, per questo è giusto analizzare in dettaglio le sue caratteristiche: Figura 2: Finestra delle query La finestra delle query può essere suddivisa in tre grosse aree o pannelli: 1. l’area delle query, dove digitiamo le interrogazioni e gli scritps T-SQL, all’interno di questo pannello possono esserci più istruzioni T-SQL, per eseguire una istruzione alla volta basta selezionarla con il mouse e premere F5. (indicata con ->query) 2. l’area dei risultati, quella in basso, dove possiamo visualizzare l’output delle query o scripts che lanciamo dall’area delle query. 3. l’area dei messaggi viene visualizzata quando lanciamo una query e si verificano degli errori, ci da tutte le informazioni per capire dove abbiamo sbagliato. Oltre a queste tre aree, possiamo visualizzare in modo opzionale altri pannelli all’interno dell’area dei messaggi. Come si vede in figura 1 non abbiamo solamente il pannello dei risultati, ma è disponibile anche il pannello del piano di esecuzione della query, se lo cliccate QA vi spiegherà passo dopo passo quello che fa la vostra query! Il pannello dell’Execution Plan sarà trattato alla fine del corso per capire come ottimizzare (in termini di perfomance ed esecuzione) le nostre query. Sempre nella figura vedete una freccia con la scritta database, attraverso questo pulldown menù potete scegliere il database dove far girare le vostre query. Attenzione non scegliete mai, almeno per ora i databases MASTER, MSDB, TEMPDB e MODEL (cioè i databases di SISTEMA) come ambiente per far girare le query di prova. Invece utilizzate il database PUBS (installato di default da SQL Server) per fare i vostri giochi e le vostre prove di T-SQL, cosa che faremo anche noi per la maggior parte del corso. Ritorna all'indice 1.12 - Cominciamo! Dopo tante parole proviamo la nostra prima query T-SQL. Apriamo il Query Analyzer, connettiamoci al nostro SQL Server, e digitiamo i seguenti comandi: USE PUBS /* Usa il database PUBS */ GO /* Delimitatore del BATCH */ SELECT * FROM authors SELECT * FROM titles Ora se tutto è OK, controlliamo l’area dei risultati della nostra finestra delle query dovremmo ottenere qualcosa di simile a questo: Figura 3: finestra delle query Entrambe le query sono state eseguite su SQL Server, nella finestra dei risultati prima avremo l’output della query SELECT * FROM authors e poi della seconda SELECT * FROM titles con in fondo il numero di righe ritornate dall’istruzione (23 row(s) affected) Supponiamo ora di voler provare solo una delle due SELECT, diciamo la prima. Selezioniamo con il mouse (o da tastiera) la query che ci interessa (come in figura 4) e poi premiamo il tasto F5 per eseguire la query. Ecco che magicamente abbiamo fatto girare solamente la prima query ignorando le altre istruzione T-SQL presenti nella nostra finestra. Salviamo le QUERY Grazie al QA, dopo aver progettato una query possiamo salvarla su disco. Per salvare l’esempio precedente basta premere i tasti CTRL+S, nella finestra di dialogo che appare dare un nome al file, esempio prova1 e dare un invio. Il file viene salvato con il sufisso *.sql. Trucchi e suggerimenti Se siete alle prime armi con il T-SQL, l’help in linea del Query Analyzer vi sarà di grande aiuto, per averlo a disposizione basta premere F1. Però forse non tutti sanno che possiamo avere qualcosa in più dall’help: provate ad evindenziare una istruzione T-SQL come la SELECT in figura 4, bene, fatto questo premete i tasti SHIFT+F1 ed ecco che magicamente compare l’help online già posizionato sulla clausola FROM della nostra SELECT. Provatelo anche selezionando altre istruzioni e comando T-SQL. La prossima puntata Nella prossima puntata, cominceremo l’analisi della sintassi e dei costrutti del T-SQL. Allegato ad ogni lezione probabilmente ci saranno degli esercizi da completare per fare pratica con T-SQL Appendice A Scorciatoie da tastiera per il Query Analyzer CTRL+Shift+Del Cancella il contenuto della finestra delle query corrente CTRL+O Apri una connessione ad SQL Server CTRL+F5 Controlla la sintassi della query. F5 Esegui una query ( oppure CTRL+E) CTRL+N Apri una nuova finestra per le query CTRL+I Attiva l’analisi delle performance sugli indici. CTRL+L Visualizza il piano di esecuzione stimato della query CTRL+K Visualizza il piano di esecuzione della query CTRL+D Visualizza I risultati in griglia (tipo foglio excel) CTRL+T Visualizza I risultati tipo testo CTRL+F Cerca all’interno del testo CTRL+H Cerca e sostituisci all’interno del testo CTRL+A Seleziona tutto il testo della finestra CTRL+R Visualizza / Rimuovi il pannello dei risultati CTRL+TAB Passa da una finestra delle query all’altra CTRL+ Shift + L Converti la selezione corrente in minuscolo CTRL+ Shift + U Converti la selezione corrente in maiuscolo Ritorna all'indice 1.13 - I tipi di dati in SQL Server Nelle lezioni precedenti abbiamo visto come le tabelle siano gli oggetti all’interno dei databases destinati a contenere i nostri dati. Quando creiamo una tabella dobbiamo definire in modo esatto il tipo di dati che ogni colonna può contenere. SQL Server ci permette di definire vari tipi di dati utili per immagazzinare: caratteri, numeri, bytes, date, immagini, ecc… oltre a questo possiamo definire tipi di dati personalizzati secondo le nostre esigenze. Ecco l’elenco e la descrizione dei tipi di dati disponibili con SQL Server 7.0: Dati binari: - binary[(n)]: ha una lunghezza fissa e può contenere fino ad 8000 bytes di dati binari - varbinary[(n)]: ha una lunghezza variabile e può contenere fino ad 8000 bytes di dati binari Dati carattere: - char[(n)]: ha una lunghezza fissa e può contenere fino ad 8000 caratteri ANSI (cioè 8000 bytes) - varchar[(n)]: ha una lunghezza variabile e può contenere fino ad 8000 caratteri ANSI (cioè 8000 bytes) - nchar[(n)]: ha una lunghezza fissa e può contenere fino a 4000 caratteri UNICODE (cioè 8000 bytes, ricordiamo che per i caratteri UNICODE servono 2 bytes per memorizzare un carattere) - nvarchar[(n)]: ha una lunghezza variabile e può contenere fino a 4000 caratteri UNICODE (cioè 8000 bytes, ricordiamo che per i caratteri UNICODE servono 2 bytes per memorizzare un carattere) Dati ora e data: - datetime: ammette valori compresi dal 1 gennaio 1753 al 31 dicembre 9999 (precisione al trecentesimo di secondo), occupa uno spazio di 8 byte - smalldatetime: meno preciso del precedente (precisione al minuto), ,occupa uno spazio di 4 byte Dati monetari: - money: Contiene valori monetari da -922337203685477.5808 a 922337203685477.5807 con una precisione al decimillesimo di unità monetaria, occupa 8 bytes di memoria - smallmoney: Contiene valori monetari da – 214748.3648 a 214748.3647 con una precisione al decimillesimo di unità monetaria, occupa 4 bytes di memoria. Dati numerici approssimati: - float[(n)]: Contiene numeri a virgola mobile positivi e negativi, compresi tra 2.23E-308 e 1.79E308 per i valori positivi e tra -2.23E-308 e -1.79E308 per i valori negativi, occupa 8 bytes di memoria ed ha una precisione di 15 cifre - real: Contiene numeri a virgola mobile positivi e negativi comprese tra 1.18E-38 e 3.40E38 per i valori positivi e tra -1.18E-38 e -3.40E38 per i valori negativi, occupa 4 bytes di memoria ed ha una precisione di 7 cifre Dati numerici esatti: - decimal[(p[, s])] - numeric[(p[, s])] decimal e numeric sono sinonimi per SQL Server 7, possono avere valori compresi tra 10^38 – 1 e – 10^38 -1. La memoria che occupano per essere immagazzinati varia a seconda della precisione che utilizziamo per rappresentarli, da un minimo di 2 bytes a un massimo di 17 bytes p – è la precisione, che rappresenta il numero massimo di cifre decimali che possono essere memorizzate (da entrambe le parti della virgola). Il massimo della precisione è 28 cifre. s – è la scala, che rappresenta il numero di massimo di cifre decimali dopo la virgola e deve essere minore od uguale alla precisione. - int: occupa 4 byte di memoria e memorizza i valori da -2147483648 a 2147483647 - smallint: occupa 2 byte di memoria e memorizza i valori da -32768 a 32,767 - tinyint: occupa 1 byte di memoria e memorizza i valori da 0 a 255 Dati speciali: - bit: tipicamente è usato per rappresentare i flag, vero/false o true/false o si/no, perché può accettare solo due valori 0 o 1. Occupa un bit ovviamente. Le colonne che hanno un tipo dati bit non possono avere valori nulli e non possono avere indici. - cursor: sono usati come varibili in stored proc oppure come parametri di OUTPUT sempre in stored proc, fanno riferimento ai cursori. Possono essere nulli e non possono essere usati con le istruzioni CREATE TABLE. - sysname: una varchar di 128 caratteri ed occupa 256 bytes, viene usato per assegnare i nomi ad ogggetti del database, come tabelle, procedure, triggere, indici, ecc… - timestamp: occupa 8 bytes ed è un contatore incrementale per colonna assegnato automaticamente da SQL Server 7. - UNIQUEIDENTIFIER (GUID): E’ un identificatore unico a livello globale di 16 byte di lunghezza chiamato anche GUID. E’ generato (molto lentamente) automaticamente da SQL Server. Dati text ed image: I dati di questo tipo, non vengono memorizzati nelle normali pagine dati di SQL Server, ma sono tratati in modo speciale su apposite pagine di memorizzazione. - text: un tipo dati a lunghezza variabile, che può memorizzare fino a 2147483647 caratteri. - ntext: come il precedente ma memorizza caratteri UNICODE, quindi fino alla metà del precedente, cioè 1073741823 caratteri. - image: può memorizzare fino a 2147483647 bytes di dati binari, è solitamente usato per le immagini. Sinonimi per i tipi di dati Per assicurare la compatibilità con lo standard SQL-92, SQL Server può usare i seguenti sinonimi per i corrispondenti tidi di dati quando usiamo istruzioni che fanno parte del data definition language (DDL), come CREATE TABLE, CREATE PROCEDURE o in DECLARE @nomevariable Vediamo un esempio: use tempdb go declare @sinonimo_carattere national character varying(10) /* è la stessa cosa che scrivere: */ declare @carattere nvarchar(10) La conversione dei data types in altri linguaggi Una delle cose che T-SQL non fa è mappare i tipi di dati con quelli di altri linguaggi di programmazione. Nelle due tabelle sottostanti abbiamo le corrispondenze tra i tipi di dati di SQL Server e quelli di Visual Basic (VB) Cosa è lo standard UNICODE ? Lo standard UNICODE a differenza dello standard ANSI mira a supportare tutti i caratteri dei linguaggi e alfabeti del mondo. UNICODE è ben supportato nei programma che girano nella galassia Windows: NT, W 95, 98 ME, 2000 ma non efficacemente nei sistemi UNIX ,IBM, e DOS. La memoria necessaria per memorizzare un carattere UNICODE è doppia (2 byte per carattere) rispetto a quella per i caratteri ANSI (1 byte per carattere) In Sql Server i tipi dati che supportano lo standard UNICODE sono: nchar, nvarchar, ntext. Il prefisso n(national) non è casuale ma è stato fissato dallo standard SQL-92. I caratteri codificabili con UNICODE sono oltre 65.000 con ANSI solo 256. Ritorna all'indice 1.14 - 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 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: select * from mioserver.pubs.dbo.authors select * from mioserver..dbo.authors select * from mioserver.pubs..authors select * from pubs..authors select * from mioserver…authors 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 8. Matematici Lavorano su numeri (I tipi di dati in SQL Server) e compiono le seguenti operazioni matematiche: 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. 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. 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: 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: Per le parti di data presenti come argomento delle funzioni facciamo riferimento alla tabella sottostante: 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). 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: xp (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. Ritorna all'indice 1.15 - Lavorare con le variabili I costrutti del Transact SQL Avrete già capito che il T-SQL va ben oltre le funzionalità dell’SQL standard. Difatti con il T-SQL possiamo fare esercizio di vera e propria programmazione come in C++ o Visual Basic. Programmare in T-SQL significa sostanzialmente progettare e creare stored procedure (che analizzeremo in dettaglio più avanti) che rappresentano i veri programmi creati con il T-SQL. Per questo motivo è fondamentale comprendere bene tutti gli elementi trattati in di questa lezione: le VARIABILI, il costrutto per il controllo del flusso IF ELSE e quello del loop WHILE. Lavorare con le variabili In precedenza abbiamo parlato dei datatype (tipi di dati). Ogni variabile utilizzata all’interno di una stored procedure (o di un batch) deve essere dichiarata assieme al suo datatype attraverso una istruzione DECLARE ed in seguito possiamo assegnarle un valore attraverso una istruzione SET o SELECT. Vediamo alcuni esempi su come possiamo dichiare variabili: Su linea singola: DECLARE @VARIABILE_TESTO AS VARCHAR(300) oppure su più linee (è il parser ad occuparsi della verifica delle istruzioni) DECLARE @VARIABILE_NUMERO AS INTEGER, @VARIABILE_TESTO AS CHAR(300), @ VARIABILE_TESTOUNICODE NVARCHAR(500) la sintassi con cui dichiariamo le variabili può essere riportata allo schema seguente: DECLARE {@variabile_locale [AS] tipo_dato} [,…;n] Come potete osservare il nome delle variabili deve iniziare con il simbolo “at” (@ ) e seguire le regole standard per gli identificatori (sono permessi caratteri alfanumerici e simboli come _ , @ , $ e # ), la parola chiave AS opzionale. Al contrario di altri linguaggi come il C++ od altri nel T-SQL non è possibile dichiarare costanti. In T-SQL le variabili debbono interdersi come variabili locali, cioè hanno significato solo all’interno del batch (gruppo o sequenza di istruzioni T-SQL) o della stored procedure nella quale vengono dichiarate , fuori da questo contesto perdono significato. E’ possibile passare variabili da una procedura ad un’altra. Convenzioni per i nomi delle variabili E’ utile trovare a livello aziendale o personale delle convezioni nell’assegnazione dei nomi alle variabili. Generalmente, dove è possibile, è bene far coincidere i nomi delle variabili con i nomi delle colonne delle tabelle. Altra regola utile è dare nomi combinati alle variabili, dove necessario. Ad esempio nel caso di un campo Descrizione nella tabella News, al quale vogliamo far riferimento con due variabili per avere la descrizione completa e quella breve (ad esempio i primi 50 caratteri) potremmo scrivere: DECLARE @DescrizioneBreve CHAR(50), @DescrizioneEstesa CHAR(255) Altrettanto comodo è scegliere delle convenzioni nello stile di scrittura delle variabili. Tutte in maiuscolo oppure minuscolo, usare il carattere _ come separatore oppure no ? Questi dilemmi possono essere risolti a seconda delle preferenze del programmatore, ecco una piccola panoramica delle possibilità: DECLARE @MiaVariabile Varchar(500) DECLARE @Mia_Variabile varchar(500) DECLARE @MIA_VARIABILE VARCHAR(500) Assegnare i valori alle variabili Ritorna all'indice 1.16 - Controllo del flusso nel T-SQL Come in tutti i linguaggi di programmazione che si rispettino anche il Transact SQL possiede istruzioni per il controllo del flusso logico del programma. Ne abbiamo di due tipi: IF ELSE e il loop WHILE, vorrei però aggiungere in questa lezione anche la funzione CASE molto utile nella programmazione T-SQL. Prima di analizzare in dettaglio questi costrutti diamo un occhio su come usare l’istruzione BEGIN END. L’istruzione BEGIN END L’istruzione BEGIN END, viene comunemente utilizzata per raggruppare più istruzioni SQL o T-SQL in un unico blocco. Dal punto di vista della logica di programmazione l’istruzione BEGIN END è utilissima per racchiudere porzioni di codice pronte per essere eseguite, ad esempio, all’interno di una IF ELSE. Vediamo un esempio: /* Blocco di istruzioni all’interno di BEGIN END */ BEGIN –- apro il blocco /* Dichiaro la variabile */ DECLARE @au_lname AS CHAR(20) /* Inizializzo la variabile */ SET @au_lname = ‘’ /* Recupero il valore e lo assegno con una istruzione SELECT */ SELECT @au_lname = au_lname FROM authors WHERE au_id = ‘aaa-aaa-aaa’ /* Stampo il valore che ho precedentemente recuperato */ PRINT @au_lname END –- chiudo il blocco L’uso deve essere fatto in coppia, ad ogni BEGIN deve corrispondere una propria END, altrimenti il parser ci segnala un errore. BEGIN END viene generalmente usata in questi casi: - Un loop WHILE che ha bisogno di includere interi blocchi di istruzioni. - Un elemento della funzione CASE che deve includere blocchi di istruzioni. - Una clausola IF o ELSE che deve includere blocchidi istruzioni. Il costrutto IF ELSE Chi possiede una minima esperienza con la programmazione avrà sicuramente incontrato l’istruzione IF ELSE essa è la struttura fondamentale per l’elaborazione condizionale. In parole povere quando il programma incontra una IF valuta la condizione specificata di seguito e se è vera prende una certa strada, altrimenti se è falsa ne prende un’altra. Possiamo pensare ad IF ELSE come un semaforo condizionale a due vie. Le condizioni che può valutare una IF ELSE in SQL Server possono essere due: TRUE o FALSE. Vediamo la sintassi dell’istruzione IF espressione_booleana { istruzione_SQL | blocco_istruzioni } [ ELSE { istruzione_SQL | blocco_istruzioni } ] Possiamo osservare che l’espressione ELSE è opzionale, solamente la IF è necessaria. Vediamo alcuni utilizzi /* 1° Esempio di IF ELSE */ SELECT au_id FROM dbo.authors WHERE au_lname = 'Luca' IF @@ROWCOUNT > 0 /* E' possibile solamente una riga di istruzione */ PRINT 'AUTORE TROVATO' ELSE /* E' possibile solamente una riga di istruzione */ PRINT 'AUTORE NON TROVATO' /* 2° Esempio di IF ELSE , in questo caso viene utilizzata anche il blocco BEGIN END per poter includere più righe di istruzioni SQL */ SELECT au_id FROM dbo.authors WHERE au_lname = 'Black' IF @@ROWCOUNT > 0 BEGIN /* E' possibile inserire più righe di istruzioni SQL */ PRINT 'AUTORE TROVATO' PRINT ‘———————————-‘ END ELSE BEGIN /* E' possibile inserire più righe di istruzioni SQL */ PRINT 'AUTORE NON TROVATO' PRINT '———————————-' END Gli esempi chiariscono a sufficienza i campi di utilizzo di questa istruzione. Il costrutto WHILE Setta una condizione per l’esecuzione ripetuta di una ( o di un blocco) di istruzioni SQL. Tipicamente le istruzioni SQL sono racchiuse all’interno di un blocco BEGIN END. Vediamone la sintassi WHILE espressione_booleana { istruzione_SQL | blocco_istruzioni } [ BREAK ] { istruzione_SQL | blocco_istruzioni } [ CONTINUE ] Le istruzioni SQL vengono eseguite ripetutamente finchè la condizione specificata è vera, a quel punto si esce dal loop. E’ possibile controllare l’esecuzione delle istruzioni all’interno del loop attraverso le parole chiave BREAK e CONTINUE. Break Causa l’uscita immediata dal loop, quando all’interno di un ciclo WHILE si incontra la parola chiave BREAK le istruzioni SQL fino alla parola chiave END vengono saltate. Continue Reinizializza il ciclo, ripartendo dalla condizione booleana, tutte le istruzioni SQL dopo la parola chiave CONTINUE vengono ignorate. Ecco un esempio di un tipico ciclo WHILE DECLARE @contatore AS INTEGER SET @contatore = 0 WHILE @contatore < 100 BEGIN IF @contatore = 50 BREAK SET @contatore = @contatore + 1 END PRINT @contatore CASE Valuta un elenco di condizioni e ritorna uno dei risultati tra quelli possibili. L’argomento ELSE è opzionale. La funzione CASE permette l’uso di elaborazioni condizionali all’interno di una query. Può essere usata all’interno di istruzioni select, inser, update e delete. E’ molto utile alla causa della programmazione T-SQL, perché semplifica molti passaggi laboriosi e ridondanti. Esistono due differenti sintassi per l’istruzione CASE, semplice e ricercata. Primo uso della funzione CASE (semplice) CASE espressione_input WHEN espressione_when THEN espressione_risultato [ …n ] [ ELSE espressione_risultato ] END Secondo uso della funzione CASE (ricercata): CASE WHEN espressione_booleana THEN espressione_risultato [ …n ] [ ELSE espressione_risultato ] END Usare l’espressione CASE semplice E’ possibile verificare (solo uguaglianza) un’espressione particolare e paragonarla con una serie di espressioni o valori differenti. Vediamone un esempio. USE pubs GO SELECT Categoria = CASE type WHEN 'popular_comp' THEN 'Computer' WHEN 'mod_cook' THEN 'Cucina moderna' WHEN 'business' THEN 'Business' WHEN 'psychology' THEN 'Psicologia' WHEN 'trad_cook' THEN 'Cucina tradizionale' ELSE 'Nessuna categoria' END, title AS 'Titolo' FROM titles WHERE price IS NOT NULL ORDER BY type, price Nell’esempio precedente abbiamo costruito un piccolissimo report abbinando la funzione CASE ad una istruzione SELECT, come potete osservare il valore del campo type viene confrontato con l’espressione di input all’interno di WHEN. Il confronto viene fatto sulla base dell’equaglianza dei valori di input. Usare l’espressione CASE ricercata A differenza della precedente forma in questo caso la funzione CASE presenta un’espressione booleana di confronto per ogni clausola WHEN specificata. Vediamone un esempio USE pubs GO SELECT 'Categoria di prezzo' = CASE WHEN price IS NULL THEN '*' WHEN price < 10 THEN '**' WHEN price >= 10 and price < 20 THEN '***' ELSE '****' END, title FROM titles ORDER BY price GO In questo caso abbiamo costruito un semplice rating in funzione delle diverse categorie di prezzo per i libri del database pubs. A differenza del caso precedente la funzione CASE ricercata nella clausola WHEN abbiamo un’espressione booleana che viene valutata. Nel caso il risultato sia true viene riportata l’espressione dopo la parola chiave THEN, altrimenti verrà riportata quella specificata nella parola chiave ELSE. Possiamo paragonare questa forma di espressione CASE alla istruzione SELECT usata in Visual Basic. E’ sempre consigliato specificare la clausola ELSE all’interno delle nostre funzioni CASE. Ritorna all'indice 1.17 - Le viste Cosa sono le views? Le viste sono delle QUERY memorizzate con un proprio nome che possono essere considerate simili a tabelle virtuali. Sono una via efficace per mostrare informazioni che arrivano da una più tabelle. Le analogie tra tabelle e viste sono parecchie, ma dobbiamo considerare le limitazioni ed i requisiti necessari per poter utlizzare le viste. Come si crea una view? Creare una view è una cosa piuttosto semplice, esiste una istruzione CREATE VIEW appositamente usata per questo scopo. Vediamone la sintassi ed anche un esempio: CREATE VIEW [ < nome_database > . ] [ < proprietario > . ] nome_vista [ ( colonna [ ,…n ] ) ] [ WITH < attributi_vista > [ ,…n ] ] AS istruzione_SELECT [ WITH CHECK OPTION ] Ora supponiamo di voler creare una vista che ci dia l’elenco di tutti i prodotti per ogni categoria del database Northwind. Per prima cosa costruiamo la query utile a recuperare i dati che ci servono SELECT Categories.CategoryName AS NomeCategoria, Products.ProductName AS NomeProdotto, Products.QuantityPerUnit AS Quantità FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID Fatto questo non ci resta che memorizzare la query all’interno di una vista, che chiameremo V_ProdottiPerCategoria. CREATE VIEW V_ProdottiPerCategoria AS SELECT Categories.CategoryName AS NomeCategoria, Products.ProductName AS NomeProdotto, Products.QuantityPerUnit AS Quantità FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID In questo caso ho specificato il nome delle singole colonne dei dati all’interno della SELECT, ma sarebbe stato possibile fare questo anche a livello dell’intestazione della vista, ad esempio CREATE VIEW V_ProdottiPerCategoria (NomeCategoria,NomeProdotto,Quantità) AS SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID NB: ricordate che ogni colonna dati della vista deve avere un nome ed il nome deve essere univoco Ora che abbiamo creato la vista possiamo accedere molto rapidamente alla lista dei prodotti per categoria ongni qualvolta lo desideriamo, basterà una semplice SELECT del tipo: SELECT * FROM V_ProdottiPerCategoria Per modificare una vista esiste un’altra istruzione analoga alla precedente dal nome ALTER VIEW, vediamo la sintassi: ALTER VIEW [ < nome_database > . ] [ < proprietario > . ] nome_vista [ ( colonna [ ,…n ] ) ] [ WITH < attributi_vista > [ ,…n ] ] AS istruzione_SELECT [ WITH CHECK OPTION ] Come si può intuire questa istruzione modifica la struttura di una vista precedentemente creata, supponiamo ad esempio di voler aggiungere una colonna dati alla vista che abbiamo precedentemente creato ALTER VIEW V_ProdottiPerCategoria (NomeCategoria,NomeProdotto,Quantità,InMagazzino) AS SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID Ecco fatto, basterà far girare nel QA il batch per la modifica della vista. I limiti delle views Non è possibile usare la clausola ORDER BY in una view Questa limitazione può sembrare assurda, ma difatti non è possible specificare un ordinamento tramite la clausola ORDER BY per il ROWSET ritornato da una view. Il motivo di tutto ciò e che la view come la tabella ritorna un set di dati che non possiede alcun ordinamento. (Standard ANSI) Per fare una prova lanciamo una ALTER sulla VIEW precedente provando ad inserire un ordinamento in base al nome della categoria ALTER VIEW V_ProdottiPerCategoria (NomeCategoria,NomeProdotto,Quantità,InMagazzino) AS SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID ORDER BY Categories.CategoryName Una volta lanciata la modifica, SQL Server ci ritorna un errore di questo tipo: Server: Msg 1033, Level 15, State 1, Procedure V_ProdottiPerCategoria, Line 10 The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified. Il messaggio ci dice che la clausola ORDER BY non può essere utilizzata in una vista senza che la clausola TOP sia specificata. Quindi è gioco facile trovare la scappatoia per creare viste ordinate, sarà sufficiente inserire la clausola TOP all’interno della query, dato che la clausola TOP supporta anche la percentuale di righe per prendere tutto il set di dati sarà necessario specificare TOP 100 PERCENT ALTER VIEW V_ProdottiPerCategoria (NomeCategoria,NomeProdotto,Quantità,InMagazzino) AS SELECT TOP 100 PERCENT –non un numero fisso ma la percentuale di righe 100% Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID ORDER BY Categories.CategoryName Non è possibile usare una SELECT INTO in una view Non è possibile usare una clausola COMPUTE o COMPUTE BY Non è possibile fare riferimento a tabelle temporanee o variabili tabella Nascondere la definizione di una vista Grazie all’opzione WITH ENCRYPTION disponibile con le istruzioni CREATE ed ALTER VIEW, possiamo criptare la definizione delle nostre viste (in buona sostanza la natura della query) da occhi indiscreti. Difatti normalmente quando creiamo una vista viene aggiunta una riga nella tabella di sistema syscomment la quale contiene il testo della vista, facciamo un esempio SELECT text FROM dbo.syscomments WHERE id = object_id('V_ProdottiPerCategoria') Con questa semplice SELECT possiamo recuperare la definizione della vista, per ovviare a questo inconveniente proviamo a modificare la vista con l’opzione WITH ENCRYPTION ALTER VIEW dbo.V_ProdottiPerCategoria (NomeCategoria,NomeProdotto,Quantità,InMagazzino) WITH ENCRYPTION AS SELECT TOP 100 PERCENT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock FROM dbo.Categories INNER JOIN dbo.Products ON Categories.CategoryID = Products.CategoryID ORDER BY Categories.CategoryName Ed ora se lanciamo la SELECT precedente per recuperare la definizione dell’oggetto V_ProdottiPerCategoria vedrete che la definizione è stata criptata e non più accessibile. Solo SQL Server è in grado di risolvere l’algoritmo di criptazione ed eseguire correttamente la query contenuta nella vista. Le viste come meccanismo di sicurezza SQL Server gestisce i permessi di accesso ai dati sia a livello di tabella che e livello di colonna. Ma a differenza di altri RDBMS SQL Server non gestisce i permessi di accesso a livello di riga. In questo caso ci vengono in aiuto le viste per implementare meccanismi di sicurezza orizzontale sui dati. Difatti attraverso le viste possiamo filtrare solo i dati (le righe della tabella) che ci interessano e concedere i relativi permessi solo agli utenti o i gruppi pertinenti, questi infine potranno lavorare con le viste come se fossero tabelle vere e proprie. Ma per poter concedere tali permessi sulle viste non dobbiamo necessariamente disporre degli opportuni permessi sugli oggetti sottostanti a cui fa riferimento la vista a patto che vengano soddisfatti i seguenti requisiti: 1. Il proprietario della view e quello degli oggetti sottostanti sono la stessa persona 2. Se la vista fa riferimento ad un oggetto in un altro database, la login che sta eseguendo la query sulla vista deve avere accesso a quel database. Se non dovesse essere soddisfatto primo requisito dobbiamo concedere all’utente i permessi a tutti gli oggetti cui la vista fa riferimento. Facciamo un esempio, supponiamo voler creare una vista per visualizzare gli autori e i libri da loro scritti CREATE VIEW dbo.V_AutoriPerTitoli AS SELECT T.title AS “Titolo”, A.au_lname + ' ' + A.au_fname AS “Autore libro”, A.state AS “Stato di nascita” FROM dbo.titles T INNER JOIN dbo.titleauthor TA ON TA.title_id = T.title_id INNER JOIN dbo.authors A ON A.au_id = TA.au_id In questo caso la vista dbo.V_AutoriPerTitoli fa riferimento a tre tabelle tutte di proprietà del possessore della vista, quindi per concedere l’uso di questa vista all’utente gino sarà sufficiente eseguire il comando: GRANT SELECT ON dbo.V_AutoriPerTitoli TO gino Osserviamo bene una cosa, non è necessario per accedere ai dati che l’utente gino abbia i permessi sugli oggetti a cui fa riferimento la vista dbo.V_AutoriPerTitoli (le tabelle dbo.titles, dbo.titleauthor, dbo.authors). Ora vediamo un altro caso, la vista dbo.V_AutoriPerTitoli2 fa riferimento a due oggetti che non sono di proprietà del possessore della vista (dbo). CREATE VIEW dbo.V_AutoriPerTitoli2 AS SELECT T.title AS “Titolo”, A.au_lname + ' ' + A.au_fname AS “Autore libro”, A.state AS “Stato di nascita” FROM dbo.titles T INNER JOIN pippo.titleauthor TA ON TA.title_id = T.title_id INNER JOIN pippo.authors A ON A.au_id = TA.au_id In questo caso non sarà sufficiente concedere all’utente gino i permessi sulla vista come nel caso precedente, ma dovremo concedere esplicitamente i permessi anche sugli oggetti a cui la vista fa riferimento: GRANT SELECT ON pippo.titleauthor TO gino GRANT SELECT ON pippo.authors TO gino Ritorna all'indice 1.18 - Le stored procedure Le query memorizzate 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) Le stored procedure 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: 1. Aumento nella velocità di esecuzione del codice SQL e quindi delle performance generali delle applicazioni. 2. 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 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 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 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 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: 1. Le variabili in Transact SQL sono locali ed il loro contesto è circostanziato alla sessione in cui vengono create. 2. 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: 1. Il nome deve essere pertinente con l’azione compiuta dalla procedura affinchè sia il esplicativo possibile 2. Il nome non deve essere eccessivamente lungo, se potete usate delle abbreviazioni, ins per inserisci, upd per update, del per delete, ecc… 3. 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 procedure di sistema molto utili Esistono alcune procedure di sistema che ci possiono 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 Ritorna all'indice 1.19 - Ritorna all'indice 1.20 -