Modo di composizione nella programmazione T-SQL

Questi componenti consentono la gestione di varie operazioni che non possono essere completate in una singola istruzione. Gli utenti possono raggruppare più comandi insieme e utilizzare uno dei seguenti modi:

• Batch : un elenco di uno o più comandi inviati come unità di query dall’applicazione al server
• Stored procedure : una stored procedure è una raccolta di comandi T-SQL precompilati e predefiniti sul server.
• Trigger : un trigger è un tipo di procedura memorizzata che viene eseguita quando l’utente elabora un evento come INSERT,UPDATE, DELETE sulla tabella.
• Script : una serie di istruzioni T-SQL archiviate in un file e utilizzate per l’input tramite SSMS o sqlcmd
• Variabili : una variabile che può essere utilizzata dall’utente come input di un’istruzione T-SQL
• Control-of-flow : controlla il flusso dideefu per la creazione di costrutti condizionali in T-SQL
• Gestione degli errori: meccanismo di gestione degli errori utilizzato per controllare gli errori e fornire informazioni agli utenti quando si verificano errori.

Batch T-SQL

Il T-SQl è un gruppo di una o più istruzioni inviate come unità di query dall’applicazione al server. SQL Server compila il batch in una singola unità di esecuzione, denominata piano di esecuzione. Nel piano di esecuzione, SQL eseguirà le istruzioni una per una. Ogni istruzione termina con un “;”. Questa condizione è facoltativa, ma costituisce la base per terminare l’istruzione senza “;” è deprecato e potrebbe essere rimosso nella nuova versione di SQL Server. Quindi usa dauas; per terminare la dichiarazione è sempre consigliato.

Quando si verifica un errore di compilazione, ad esempio un errore di sintassi, il batch non verrà eseguito.

Un errore di runtime come una violazione di un vincolo o una logica algoritmica risulterà in una delle seguenti situazioni:

• La maggior parte degli errori di runtime interromperà il comando corrente e le seguenti istruzioni nel batch
• Un errore di runtime specificato come vincolo verrà utilizzato solo se l’istruzione esiste e le parti rimanenti dell’istruzione vengono ancora eseguite.

Le istruzioni SQL eseguite prima che si verifichi l’errore di runtime non sono interessate. L’unica eccezione è quando il batch è una transazione e l’errore si verifica nella transazione quando si verifica un rollback.

Ad esempio, supponiamo che ci siano 10 istruzioni nel batch e 6 istruzioni abbiano un errore, le istruzioni rimanenti nel batch non verranno eseguite. Se il batch viene compilato e il terzo comando non riesce, verranno eseguite le prime due istruzioni.

Alcune regole da seguire quando si utilizzano i batch:

• CREATE FUNCTION, CREATE DEFAULT, CREATE RULE, CREATE TRIGGER, CREATE PROCEDURE, CREATE VIEW o CREATE SCHEMA non possono essere concatenati con un’altra istruzione nel batch. L’istruzione CREATE avvia il batch e tutte le istruzioni rimanenti all’interno del batch vengono considerate parte della definizione dell’istruzione CREATE.
• non sono state apportate modifiche alla tabella e le nuove colonne hanno fatto riferimento a come erano
• Se la prima istruzione del batch è un’istruzione di esecuzione, il comando excute non è richiesto, è richiesto solo se l’istruzione di esecuzione non esiste nella prima istruzione del batch.

Esempio di lotto:

USE AdventureWorks2019; GO GREATE VIEW dbo.vProduct AS SELECT ProductNumber, Name FROM Production.Product; GO SELECT * FROM dbo.vProduct; GO

L’esempio 2 viene utilizzato per combinare più BATCH in una transazione:

BEGIN TRANSACTION GO USE AdventureWorks2019; GO CREATE TABLE Company( Id_Num int IDENTITY(1,1), Company_Name nvarchar(100)) GO INSERT Company (Company_Names) ('Company 1') INSERT Company (Company_Names) ('Company 2') INSERT Company (Company_Names) ('Company 3') GO SELECT Id_Num, Company_Name FROM dbo.Company ORDER BY Company_Name ASC; GO COMMIT; GO

Nel codice sopra, più batch vengono combinati in un’unica transazione. Le istruzioni BEGIN TRANSACTION e COMMIT definiscono l’apertura e la chiusura della transazione. CREATE TABLE, BEGIN TRANSACTION, SELECT, COMMIT e USE sono batch di istruzioni singole. Le istruzioni INSERT sono tutte impacchettate in un batch.

Variabile (variabile) in T-SQL

In SQL Server, le variabili possono essere dichiarate e utilizzate come parametri o scrivere query dinamiche, come parametro passato dall’applicazione. SQL Server fornisce istruzioni per dichiarare e assegnare valori alle variabili.

DICHIARARE

La variabile viene inizializzata tramite l’ istruzione DECLARE nel corpo del batch. A queste variabili vengono assegnati valori utilizzando le istruzioni SELECT o SET. Le variabili vengono inizializzate con il valore NULL se non viene fornito alcun valore dall’utente al momento della dichiarazione.

Sintassi:

DECLARE {(@local_variable [AS] data_type) } [= value]}

Per esempio:

USE AdventureWorks2019; GO DECLARE @find varchar(30) = 'Man%'; SELECT p.LastName, p.FirstName, ph.PhoneNumber FROM Person.Person AS p JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID WHERE LastName LIKE @find;

IMPOSTARE

L’istruzione SET assegna un valore a una variabile locale inizializzata con DECLARE per determinare il valore della variabile

Sintassi:

SET {@local_variable = {expression}} {@local_variable {+= | -= | *= | /= | %= | &= | ^= | |=}}

Spiegazione dell’operatore:

• += aggiungi e assegna uguale
• -= sottrarre e assegnare uguali
• *= moltiplica e assegna uguale
• /= divide e assegna uguale
• %= dividere per resto e assegnare uguale a
• &= AND bit per bit e assegnare in seguito
• ^= XOR bit per bit e assegnare in seguito
• |= OR bit a bit e assegnare in seguito.

Per esempio:

DECLARE @myvar char(20); SET @myvar = 'Hello world';

SELEZIONARE

L’istruzione SELECT specifica la variabile locale specificata tramite l’istruzione DECLARE per recuperare l’espressione

Sintassi:

SELECT {@local_variable {= | += | -= | *= | /= | %= | &= | ^= | != } expresssion}

Per esempio:

USE AdventureWorks2019 GO DECLARE @var1 nvarchar(30); SELECT @var1 = 'company hello'; SELECT @var1 = Name FROM Sales.Store WHERE BusinessEntityID = 10; SELECT @var1 AS 'Company name';

Le istruzioni SET e SELECT sembrano simili, ma presentano una leggera differenza:

• È possibile assegnare solo una variabile alla volta utilizzando SET, tuttavia, SELECT può assegnare valori a più variabili contemporaneamente.
• SET può assegnare solo un’assegnazione di tipo di dati di base dalla query, si verificherà un errore quando i dati sono un insieme di righe e colonne. Tuttavia, SELECT può assegnare più valori di ritorno a una variabile.

Sinonimi

Sinonimi è un oggetto di database che serve ai seguenti scopi:

• Suggerisce un nome diverso per diversi oggetti di database, chiamati anche oggetto di base, che possono esistere su un server remoto o locale.
• Rappresenta un livello di astrazione che protegge l’applicazione client dalle modifiche apportate alla posizione e al nome dell’oggetto di base.

Ad esempio, considera la tabella Department in AdventureWorks2019 che si trova su un server denominato server1. Per fare riferimento a una tabella dal server 2 denominata server2, il client dovrà utilizzare quattro nomi di elementi:
server1.AdventureWorks2019.HumanResources.Department

Se la posizione della tabella viene modificata, ad esempio in un altro server, l’applicazione client non deve essere influenzata da questa modifica. Per risolvere entrambi questi problemi, gli utenti possono creare un sinonimo DeptEmpTable owr Server2 per la tabella Department su Server1
Da lì, il client deve utilizzare solo una DeptEmpTable denominata per fare riferimento alla tabella Department .

Allo stesso modo, se la posizione della tabella Department cambia, l’utente deve modificare il sinonimo DeptEmpTable per puntare alla nuova posizione della tabella Department .
E nota, senza l’istruzione ALTER Synonym , è necessario eliminare il sinonimo e reinizializzarlo con il vecchio nome ma puntando alla nuova posizione del Dipartimento .

Elenco di oggetti di database per i quali l’utente può creare sinonimi:

• Procedure archiviate estese
• Funzione SQL con valori di tabella
• Procedure memorizzate SQL
• Tabella (definita dall’utente)
• Procedura di filtro di replica
• Funzioni scalari SQL
• Funzione SQL con valori tabellari in linea
• Visualizzazione

Sinonimi e schemi

Si supponga che un utente desideri creare un sinonimo e disponga di uno schema predefinito che non è di sua proprietà. In questo caso, potrebbero essere in grado di identificare il nome del sinonimo con lo scema che effettivamente possiedono. Ad esempio, l’utente possiede lo schema Resources ma Material è lo schema predefinito, se l’utente desidera creare un sinonimo, deve prefisso sinonimo con lo schema Resources .

Assegna le autorizzazioni sui sinonimi

Solo i membri dei ruoli db_owner o db_ddladmin o il proprietario del sinonimo possono concedere autorizzazioni a un sinonimo. Un utente può negare, concedere o revocare tutte o tutte le autorizzazioni su un sinonimo.

Elenco delle autorizzazioni:

• ELIMINA
• INSERIRE
• ASSUMERE LA PROPRIETÀ
• VEDI DEFINIZIONE
• CONTROLLO
• ESEGUIRE
• SELEZIONARE
• AGGIORNARE

Lavorare con i sinonimi

Gli utenti lavorano con i sinonimi in SEL SERVer 2019 o T-SQL in SSMS. Per creare un sinonimo in SSMS utilizzando Esplora oggetti, procedi come segue:

Per creare un sinonimo utilizzando T-SQL, utilizzare la sintassi:

CREATE SYNONYM [schema_name_1.] synonym_name FOR <object> <object> ::= { server_name.[database_name].[schema_name_2].|database_name.[schema_name_2].|schema_name_2.] object_name}

Per esempio:

USE AdventureWorks2019; GO CREATE SYNONYM MyAddressType FOR AdventureWorks2019.Person.AddressType; GO

Dichiarazioni di flusso del programma

I diversi tipi di istruzioni di flusso del programma supportati da T-SQL sono i seguenti:

Linguaggio T-SQL Control-of-Flow

Il controllo del flusso mostra il flusso di istruzioni SQL, blocchi di comandi, funzioni definite dall’utente e stored procedure.

Per impostazione predefinita, le istruzioni T-SQL vengono eseguite in sequenza. Il flusso del programma consente di eseguire istruzioni in situazioni specifiche e interconnesse e viene generato tra codice utilizzando costrutti simili ai linguaggi di programmazione.

Elenco delle parole chiave del linguaggio di controllo del flusso :

• RESTITUZIONE
• GETTARE
• PROVA A PRENDERE
• ASPETTARE
• MENTRE
• INIZIO… FINE
• ROMPERE
• TIẾP TỤC
• Etichetta GOTO
• SE ALTRO

INIZIO… FINE

Sintassi:

BEGIN { sql_statement | statement_block } END

Per esempio

USE AdventureWorks2019; GO BEGIN TRANSACTION; GO IF @@TRANCOUNT = 0 BEGIN SELECT FristName, MiddleName FROM Person.Person WHERE LastName = 'Andy'; ROLLBACK TRANSACTION; PRINT N'Rolling back the transaction two times would cause an error.'; END; ROLLBACK TRANSACTION; PRINT N'Rolled back the transcaction'; GO

SE ALTRO

TRansact-SQL si basa sulla parola chiave IF e la condizione viene eseguita solo se è soddisfatta e restituisce true. La parola chiave ELSE è facoltativa nell’istruzione T-SQl che viene eseguita quando la condizione IF non è soddisfatta e restituisce FALSE.

Sintassi:

IF Boolean_expression {sql_statement | statement_block} [ELSE sql_statement | statement_block } ]

Per esempio:

USE AdventureWorks2019; GO DECLARE @ListPrice money; SET @ListPrice = (SELECT MAX(p.ListPrice) FROM Production.Product AS p JOIN Production.ProductSubcategory AS s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.[Name] = 'Mountain Bikes'); PRINT @ListPrice IF @ListPrice < 3000 PRINT 'All the products in this category can be purchased for an amount less than 3000' ELSE PRINT 'The prices for some products in this category exceed 3000'

MENTRE

L’istruzione WHILE definisce la condizione per la ripetizione dell’esecuzione del blocco. L’istruzione viene eseguita ripetutamente finché la condizione specificata non restituisce true. L’istruzione di esecuzione nel ciclo WHILE può essere controllata con la parola chiave BREAK o CONTINUE

Sintassi:

WHILE Boolean_expression {sql_statement | statement_block | BREAK | CONTINUE}

Per esempio:

DECLARE @flag int SET @flag = 10 WHILE (@flag <= 95) BEGIN IF @flag % 2 = 0 PRINT @flag SET @flag = @flag+ 1 CONTINUE; END; GO

CASO QUANDO

Sintassi:

 SELECT column_list, CASE column_name WHEN value1 then display1 WHEN value2 then display2 ... ELSE display END [AS alias] FROM relative_table [WHERE condition];

Per esempio

 SELECT studentid, studentname, GENDER = CASE gender WHEN 1 then 'Male' WHEN 0 then 'Female' ELSE 'Unknow' END FROM Student;