Programmabilita dei DBMS
Maurizio Fermeglia
[email protected]
www.caslab.units.it
Gli oggetti programmabili di Sql
È possibile racchiudere comandi SQL in oggetti
programmabili la cui definizione rimane nelle tabelle di
sistema
Facilitano il riutilizzo del piano di esecuzione
Incapsulano la logica applicativa di accesso ai dati
Nascondono al client la complessità della base dati
Nome dell’evento
19 December 2015 - slide
Gli oggetti programmabili di Sql
Oggetti di questo tipo sono


Viste
Stored procedure
 Un insieme di comandi SQL con parametri di input e output che
possono restituire resultset

Trigger
 Particolari stored procedure che vengono associate ad una operazione
su un’oggetto e invocate automaticamente

User defined function
 Consentono di raggruppare e riutilizzare codice SQL solitamente
ripetuto all’interno di stored procedure e trigger

Assemblies SQLCLR (solo in MS SQL server 2005-08)
Transact SQl o PL SQL
Nome dell’evento
19 December 2015 - slide
Il testo di un oggetto programmabile
EXEC sp_helptext [@objectname = ] ‘name’
USE library
EXEC Not
sp_helptext
'dbo.OverdueView'
Every Programming
Object Has Associated Text
GO
Nome dell’evento
19 December 2015 - slide
Introduzione alle viste
title
title_no title
1
2
3
author
synopsis
Last of the Mohicans
James Fenimore Cooper ~~~
The Village Watch-Tower Kate Douglas Wiggin
~~~
Poems
Wilfred Owen
~~~
USE library
GO
CREATE VIEW dbo.TitleView
AS
SELECT title, author
FROM title
GO
TitleView
title
author
Last of the Mohicans
James Fenimore Cooper
The Village Watch-Tower Kate Douglas Wiggin
Poems
Wilfred Owen
Nome dell’evento
User’s View
19 December 2015 - slide
Vantaggi delle viste
Focalizza I dati per gli utenti


Focus sui dati importanti o solo su quelli appropriati
Limita accesso a dati sensibili
Maschera la complessità del DB


Nasconde I dettagli dello schema logico
Semplifica le queries
Semplifica la gestione delle User Permissions
Organizza I dati per esportazione verso altre
applicazioni
Nome dell’evento
19 December 2015 - slide
Definizione di Viste
Example 1: Creating a View
USE library
GO
CREATE VIEW dbo.UnpaidFinesView (Member, TotalUnpaidFines)
AS
SELECT member_no, (sum(fine_assessed-fine_paid))
FROM loanhist
GROUP BY member_no
HAVING SUM(fine_assessed-fine_paid) > 0
GO
Example 2: Querying a View
SELECT *
FROM UnpaidFinesView
GO
Nome dell’evento
19 December 2015 - slide
Update di viste e ANSI ISO Standard
Una vista può essere modificata se (SQL 1.0)





DISTINCT non viene specificato
La clausola FROM deve specificare SOLO una tabella oppure una
vista. E l’utente deve avere i privilegi di modifica.
Ciascun elemento della SELECT DEVE contenere un campo (no
espressioni, campi calcolati,…)
La clausola WHERE non deve contenere SubQuery
La query NON deve contenere GROUP BY ed HAVING.
Affinché una vista sia modificabile, il DBMS deve essere in
grado di tracciare ciascuna colonna e ciascuna riga
UNIVOCAMENTE
fino alle tabelle origine
Nome dell’evento
19 December 2015 - slide
Restrizioni sull’uso delle Viste (T-SQL)
Può referenziare al massimo 1024 Columns
Non può contenere clausole COMPUTE o COMPUTE BY
Non può contenere clausole ORDER BY, a meno che non
siano usate con clausola TOP
Non può contenere INTO Keyword
Non può referenziare tabelle temporanee
Deve essere espressa come una singola Transact-SQL
Batch
Nome dell’evento
19 December 2015 - slide
Esempio: Viste da tabelle multiple
member
juvenile
member_no lastname
firstname
middleinitial
photograph
member_no adult_no
birth_date
11
12
13
14
Gary
Clair
Frank
Clair
~~~
~~~
~~~
~~~
~~~
~~~
~~~
~~~
12
13
1992-01-16 00:00:00.000
1984-01-18 00:00:00.000
Thomas
Thomas
Funk
Rudd
USE library
GO
CREATE VIEW dbo.birthdayview
(lastname, firstname, birthday)
AS
SELECT lastname, firstname
,CONVERT(char(8), birth_date, 2)
FROM member
INNER JOIN juvenile
ON member.member_no = juvenile.member_no
GO
Nome dell’evento
11
6
BirthdayView
lastname
firstname
Birth Date
Thomas
Funk
Gary
Frank
92.01.16
84.01.18
19 December 2015 - slide
Nome dell’evento
Database Schema
Application
Stored Procedure
19 December 2015 - slide
Nome dell’evento
Database Schema
Stored Procedures
Application
Stored Procedure
19 December 2015 - slide
Stored Procedure
Sono “Subroutine” che contengono tutto il codice necessario per
effettuare una operazione (codice T SQL)
Incapsula task ripetitivi
Accettano parametri di input
Producono uno o più output


Parametri di output
Resultset
Sono FONDAMENTALI per incapsulare la logica di


Accesso alle tabelle
Manipolazione dei dati
Permettono la creazione di un livello di astrazione del modello fisico
del database


Aiutano a mantenere un alto disaccoppiamento
Garantiscono la possibilità di intervenire sul database senza necessariamente
modificare le applicazioni che lo usano
 Introduzione di nuove funzionalità necessarie ad altre applicazioni (un db non è
“privato”!!!)
 Miglioramento performance
Nome dell’evento
19 December 2015 - slide
Stored Procedure: vantaggi
Permettono l’implementazione di arbitrari meccanismi di
sicurezza
Migliorano le performance (cached execution plans)
Condividono la logica dell’applicazione
Mascherano lo schema logico del DB
Forniscono meccanismi di sicurezza
Migliorano Performance
Riducono il Network Traffic
NON SONO TRANSAZIONALI “DI
PER SE”!!!!
Nome dell’evento
14
19 December 2015 - slide
User Defined Function
Sql Server espone una serie di funzioni di sistema
che facilitano la scrittura del codice T-SQL

Es. CONVERT(), GETDATE(), ecc.
L’utente può estendere questo set di funzioni
realizzandone delle proprie da utilizzare poi nelle
stored procedure o nella query ad hoc
Sono gruppi di comandi che possono ritornare
valori scalari o tabellari
Possono essere utilizzati nella Select List di una
query o nella clausola FROM
Nome dell’evento
19 December 2015 - slide
Cos’è una User-defined Function?
Scalar Functions
 Simile ad una built-in function
 Ritorna un singolo valore costruito con una serie di
statements
Multi-Statement Table-valued Functions
 Contenuto simile ad una stored procedure
 Referenziata come una Vista
In-line Table-valued Functions
 Simile ad una Vista con parametri
 Ritorna una tabella come risultato di uno statement SELECT
singolo
Nome dell’evento
19 December 2015 - slide
Creare una User-defined Function
USE northwind
GO
CREATE FUNCTION fn_NewRegion ( @myinput nvarchar(30) )
RETURNS nvarchar(30)
BEGIN
IF @myinput IS NULL
SET @myinput = 'Not Applicable'
RETURN @myinput
END
GO
Nome dell’evento
19 December 2015 - slide
UDF scalare
-- Creo la funzione
CREATE FUNCTION ExtractNamePart(@InName varchar(100), @part tinyint)
RETURNS varchar(30) AS
BEGIN
DECLARE @offset tinyint
SET @offset = charindex(' ', @InName)
RETURN CASE @part
WHEN 1 THEN substring(@InName, 1, @offset-1)
WHEN 2 THEN substring(@InName,@offset+1, len(@InName))
ELSE NULL
END
END
-- La utilizzo
Select dbo.myfunction(CustomerName) From Customers
Nome dell’evento
19 December 2015 - slide
UDF tabellari
-- UDF tabellare in-line. Es. per viste parametrizzate
CREATE FUNCTION SalesByStore(@storid varchar(30))
RETURNS TABLE
AS
RETURN(SELECT title, qty FROM sales s, titles t WHERE s.stor_id = @storid)
-- UDF tabellare multi-statement
CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
( ShipperID int, ShipperName nvarchar(80), OrderID int,
ShippedDate datetime, Freight money )
AS
BEGIN
INSERT @OrderShipperTab
SELECT
S.ShipperID,
S.CompanyName,
O.OrderID,
O.ShippedDate,
O.Freight
FROM Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia WHERE O.Freight > @FreightParm
RETURN
END
Nome dell’evento
19 December 2015 - slide
Introduzione ai Triggers
Paradigma della programmazione ad eventi
Un Trigger è un tipo speciale di Stored Procedure
Un Trigger è:
 Associato ad una tabella
 Invocato automaticamente
 Mai chiamato diretamente
Sono disponibili due tabelle fittizie
 INSERTED
 Contiene le informazioni inserite o i nuovi valori delle modificate
 DELETED
 Contiene le informazioni cancellate o quelle precedenti ad una
modifica
Viene eseguito in una transazione con l’operazione che lo ha generato
 È possibile annullarla chiamando ROLLBACK TRANSACTION
Nome dell’evento
19 December 2015 - slide
Trigger
È un particolare “tipo” di stored procedure agganciato ad
una operazione fatta su un oggetto

Es. DELETE sulla tabella Customer
CREATE TRIGGER trDeleteCustomer
ON Customer
FOR DELETE
AS
IF EXISTS(Select COUNT(*) From Orders
Where Orders.CustomerID=Deleted.CustomerID)
ROLLBACK TRANSACTION
GO
Viene eseguita automaticamente da Sql Server al
verificarsi della operazione sulla tabella
Possono essere utilizzati per forzare l’integrità referenziale

Nome dell’evento
Attenzione alle implicazioni sulle performance
19 December 2015 - slide
Alcune raccomandazioni
Verifica il testo dell’oggetto con EXEC sp_helptext
Usa Viste per catturare e riutilizzare Queries
Usa Stored Procedures per incapsulare procedure complesse
Usa User-defined Functions to incapsulare espressioni
Nome dell’evento
19 December 2015 - slide
Stored Procedures
Stored Procedure
CREATE PROCEDURE è il comando per creare il nuovo
oggetto
È possibile dichiarare parametri di input e di output
Da una SP è possibile chiamare un’altra SP e utilizzarne i
valori in output
CREATE PROCEDURE spMyProc
@inputpar datatype,
@outputpar datatype OUTPUT
AS
SELECT * FROM MyTable
WHERE IdColumn=@inputpar
SELECT @outputpar = @@ROWCOUNT
GO
Nome dell’evento
19 December 2015 - slide
Stored Procedure
Parametri di input e di output
Validazione dei parametri passati
“Componentizzazione” delle Stored Procedure

SP che ne richiamano altre
Utilizzo di tabelle temporanee e variabili di tipo
TABLE
Valore di ritorno
Combinare più result set di ritorno
Cinque tipi (System, Local, Temporary, Remote,
and Extended)
Return Status Value Indica Successo o Fallimento
Nome dell’evento
19 December 2015 - slide
Elaborazione di una Stored Procedure
Creazione
Esecuzione
(prima volta
o recompile)
Parsing
Ottimizzazione
Compilazione
Nome dell’evento
Info in sysobjects
e syscomments
Piano di esecuzione
in cache
19 December 2015 - slide
Esecuzioni successive
Se il piano di esecuzione esiste in cache viene utilizzato
Piano di esecuzione
Execution Plan
Retrieved
Contesto di esecuzione
SELECT *
FROM
dbo.member
WHERE
member_no = ?
Connection 1
8082
Connection 2
24
Connection 3
1003
Quando nessuno lo richiama più
Unused plan is aged out
viene scaricato per liberare spazio
Nome dell’evento
19 December 2015 - slide
Stored Procedure: vantaggi
Permettono l’implementazione di arbitrari meccanismi di
sicurezza
Migliorano le performance (cached execution plans)
Condividono la logica dell’applicazione
Mascherano lo schema logico del DB
Forniscono meccanismi di sicurezza
Migliorano Performance
Riducono il Network Traffic
NON SONO TRANSAZIONALI “DI
PER SE”!!!!
Nome dell’evento
28
19 December 2015 - slide
Creazione di una Stored Procedures
Creata nel DSB corrente usando CREATE PROCEDURE
Statement
USE Northwind
GO
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE
RequiredDate
Can Nest
to 32 Levels< GETDATE() AND ShippedDate IS Null
GO
Use sp_help to Display Information
Nome dell’evento
19 December 2015 - slide
Linee guida per la creazione di Stored
Procedures
dbo User deve essere Owner di tutte Stored
Procedures
Una Stored Procedure per un Task
Crea, Test, e Troubleshoot
Evitare sp_ Prefix in Stored Procedure Names
Usare la stessa stringa di connessione per tutte le
Stored Procedures
Minimizzare l’uso di Stored Procedures temporanee
Mai cancellare le entry nella Syscomments
Nome dell’evento
19 December 2015 - slide
Execuzione di Stored Procedures
Esecuzione di Stored Procedure
EXEC OverdueOrders
Esecuzione di Stored Procedure dentro un INSERT
Statement
INSERT INTO Customers
EXEC EmployeeCustomer
Nome dell’evento
19 December 2015 - slide
Modifica e cancellazione di Stored
Procedures
Modifica Stored Procedures
USE Northwind
GO
Dropping
stored procedures
ALTER PROC
dbo.OverdueOrders
 Execute the sp_depends stored procedure to determine
AS
whether objects depend
on the stored
procedure
SELECT CONVERT(char(8),
RequiredDate,
1) RequiredDate,
CONVERT(char(8), OrderDate, 1) OrderDate,
OrderID, CustomerID, EmployeeID
FROM Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
ORDER BY RequiredDate
GO
Nome dell’evento
19 December 2015 - slide
Utilizzo di Input Parameters
Validare tutti I Parametri prima di eseguire
Prevedere Appropriati valori di default e verificare il
Null
CREATE PROCEDURE dbo.[Year to Year Sales]
@BeginningDate DateTime, @EndingDate DateTime
AS
IF @BeginningDate IS NULL OR @EndingDate IS NULL
BEGIN
RAISERROR('NULL values are not allowed', 14, 1)
RETURN
END
SELECT O.ShippedDate,
O.OrderID,
OS.Subtotal,
DATENAME(yy,ShippedDate) AS Year
FROM ORDERS O INNER JOIN [Order Subtotals] OS
ON O.OrderID = OS.OrderID
WHERE O.ShippedDate BETWEEN @BeginningDate AND @EndingDate
GO
Nome dell’evento
19 December 2015 - slide
Esecuzione di Stored Procedures con
Input Parameters
Passaggio valori per Parameter Name
EXEC AddCustomer
@CustomerID = 'ALFKI',
@ContactName = 'Maria Anders',
@CompanyName = 'Alfreds Futterkiste',
@ContactTitle = 'Sales Representative',
@Address = 'Obere Str. 57',
@City = 'Berlin',
@PostalCode = '12209',
@Country = 'Germany',
@Phone = '030-0074321'
Passaggio valori per Posizione
EXEC AddCustomer 'ALFKI2', 'Alfreds
Futterkiste', 'Maria Anders', 'Sales
Representative', 'Obere Str. 57', 'Berlin',
NULL, '12209', 'Germany', '030-0074321'
Nome dell’evento
19 December 2015 - slide
Ritorno di valori con Output Parameters
Creazione Stored
Procedure
Esecuzione Stored
Procedure
Risultati Stored
Procedure
Nome dell’evento
CREATE PROCEDURE dbo.MathTutor
@m1 smallint,
@m2 smallint,
@result smallint OUTPUT
AS
SET @result = @m1* @m2
GO
DECLARE @answer smallint
EXECUTE MathTutor 5,6, @answer OUTPUT
SELECT 'The result is: ', @answer
The result is:
30
19 December 2015 - slide
Ricompliazione esplicita di Stored
Procedures
Recompilare Quando



Stored procedure ritornano valori variabili
SI aggiunge un nuovo indice ad una tabella
Il valore del parmatero è atipico
Recompilare utilizzando



Nome dell’evento
CREATE PROCEDURE [WITH RECOMPILE]
EXECUTE [WITH RECOMPILE]
sp_recompile
19 December 2015 - slide
Esecuzione di Extended Stored
Procedures
Sono Programmate usando Open Data Services API
Possono contenere C e C++ Features
Possono contenere Funzioni Multiple
Possono essere chaimate da un Client o da SQL
Server
Possono essere aggiunte al master Database
solamente
EXEC master..xp_cmdshell 'dir c:\'
Nome dell’evento
19 December 2015 - slide
Gestione Messagi di Errore
Statement RETURN esce dalla query o procedura
incondizinataemnte
sp_addmessage Crea Custom Error Messages
@@error Contiene Error Number per l’ultimo stament
eseguito
RAISERROR Statement


Nome dell’evento
Ritorna messaggio di errore user-defined o di sistema
Setta un system flag per registrare l’errore
19 December 2015 - slide
Considerazioni di Performance
Windows 2003 System Monitor


Object: SQL Server: Cache Manager
Object: SQL Statistics
SQL Profiler


Nome dell’evento
monitor eventi
test caiscun statement in una stored procedure
19 December 2015 - slide
Racommandazioni
Verificare Input Parameters
Progetta ciascuna Stored Procedure per un singolo Task
Validare I dati prima di cominciare la transazione
Usare la stessa stringa di connessione per tutte le Stored Procedures
Usare WITH ENCRYPTION per nascondere il Testo delle SP
Nome dell’evento
19 December 2015 - slide
User-defined Functions
Cos’è una User-defined Function?
Scalar Functions
 Simile ad una built-in function
 Ritorna un singolo valore costruito con una serie di
statements
Multi-Statement Table-valued Functions
 Contenuto simile ad una stored procedure
 Referenziata come una Vista
In-line Table-valued Functions
 Simile ad una Vista con parametri
 Ritorna una tabella come risultato di uno statement SELECT
singolo
Nome dell’evento
19 December 2015 - slide
Creazione di una User-defined Function

Creazione di Function
USE Northwind
CREATE FUNCTION fn_NewRegion
(@myinput nvarchar(30))
RETURNS nvarchar(30)
BEGIN
IF @myinput IS NULL
SET @myinput = 'Not Applicable'
RETURN @myinput
END

Nome dell’evento
Restrizioni sulle funzioni
19 December 2015 - slide
Creare una Function con Schema
Binding
User-defined Functions e Views sono Schema Bound
Oggetti non sono referenziati con un Two-Part Name
Function e Oggetti sono tutti nello stesso Database
Bisogna avere Permessi di referenziazione sugli oggetti
richiesti
Nome dell’evento
19 December 2015 - slide
Permissions per User-defined Functions
Serve CREATE FUNCTION Permission
Serve EXECUTE Permission
Serve REFERENCE Permission sulle Tables, Views, e
Functions citate
Si deve essere owner della Function per usare in CREATE
o ALTER TABLE Statement
Nome dell’evento
19 December 2015 - slide
Modifica User-defined Functions
Modifica Functions
ALTER FUNCTION dbo.fn_NewRegion
<New function content>


Mantiene permissions assegnate
Rimpiazza l’wsistente con al nuova funzione
Cancellazione di Functions
DROP FUNCTION dbo.fn_NewRegion
Nome dell’evento
19 December 2015 - slide
Scalar User-defined Function
Clausola RETURNS specifica il Data Type
Function definita dentro a BEGIN e END Block
Return Type è qualsiasi Data Type eccetto text, ntext,
image, cursor, o timestamp
Nome dell’evento
19 December 2015 - slide
Scalar User-defined Function

Creazione della Function
USE Northwind
CREATE FUNCTION fn_DateFormat
(@indate datetime, @separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@indate))
+ @separator
+ CONVERT(Nvarchar(20), datepart(dd, @indate))
+ @separator
+ CONVERT(Nvarchar(20), datepart(yy, @indate))
END

Richiamo della Function
SELECT dbo.fn_DateFormat(GETDATE(), ':')
Nome dell’evento
19 December 2015 - slide
Multi-Statement Table-valued Function
BEGIN e END racchiudono Multiple Statements
Clausola RETURNS Specifica il table Data Type
Clausola RETURNS da il nome e definisce la tabella
Nome dell’evento
19 December 2015 - slide
Multi-Statement Table-valued Function
Creazione della Function
USE Northwind
GO
CREATE FUNCTION fn_Employees (@length nvarchar(9))
RETURNS @fn_Employees table
(EmployeeID int PRIMARY KEY NOT NULL,
[Employee Name] nvarchar(61) NOT NULL)
AS
BEGIN
IF @length = 'ShortName'
INSERT @fn_Employees SELECT EmployeeID, LastName
FROM Employees
ELSE IF @length = 'LongName'
INSERT @fn_Employees SELECT EmployeeID,
(FirstName + ' ' + LastName) FROM Employees
RETURN
END
Richiamo della Function
SELECT * FROM dbo.fn_Employees('LongName')
Or
SELECT * FROM dbo.fn_Employees('ShortName')
Nome dell’evento
19 December 2015 - slide
In-Line Table-valued Function
Contenuto della Function è un SELECT Statement
Non usare BEGIN e END
RETURN specifica il Data Type
Format è definito dal Result Set
Nome dell’evento
19 December 2015 - slide
In-Line Table-valued Function

Creazione della Function
USE Northwind
GO
CREATE FUNCTION fn_CustomerNamesInRegion
( @RegionParameter nvarchar(30) )
RETURNS table
AS
RETURN (
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = @RegionParameter
)

Richiamo della Function usando un parametro
SELECT * FROM fn_CustomerNamesInRegion(N'WA')
Nome dell’evento
19 December 2015 - slide
Racommandazioni
Usa Funzioni scalari complesse solo per piccoli Result Sets
Usa Multi-Statement Functions Invece che
Stored Procedures che ritornano tabelle
Usa In-Line Functions per creare Viste parametriche
Usa In-Line Functions per filtrare viste indicizzate
Nome dell’evento
19 December 2015 - slide
SQLCLR
54
Estendibilità di un RDBMS
T-SQL è ottimo per la manipolazione dei dati (di set di
dati!)…
…ma non è adatto per gestire



Logiche complesse
Calcoli matematici performanti
Accesso a risorse esterne a SQL Server
E’ necessario quindi poter creare oggetti che vivono
all’interno del database, sono trasparenti a T-SQL ma non
sono scritti in T-SQL
Nome dell’evento
55
19 December 2015 - slide
Estendibilità di un RDBMS
Il .NET Framework è ideale!


Tutta la potenza di un linguaggio evoluto ed OO
Integrabile in SQL Server in modo sicuro (per SQL Server 
 Essendo “managed” l’accesso alle risorse è governato dal framework
che a sua volta in questo caso è governato da SQL Server
 Garanzia di stabilità del database server
Nome dell’evento
56
19 December 2015 - slide
Estendibilità di un RDBMS
Cosa è possibile creare?
Le “solite cose” 



Stored procedure
Trigger
User Defined Functions
Ma inoltre! :


Nome dell’evento
57
User Defined Aggregates
User Defined Data Types
19 December 2015 - slide
Quando usare SQLCLR
Es: Perché non creare un tipo di dato “Customer” e creare
una tabella con una sola colonna di questo tipo?
Ricordiamo la prima forma normale!: i dati devono essere
“atomici”…complessi eventualmente a atomici!

Es: numero complesso
Come potremmo poi fare delle operazioni su di esso?

Nome dell’evento
58
Sarebbe inoltre molto complesso definire i constraint, effettuare
join, ecc. ecc.
19 December 2015 - slide
Estendibilità di un RDBMS
Passaggi necessari per creare una oggetto managed





Nome dell’evento
59
Abilitare il supporto al CLR
Creare l’assembly contenente la funzionalità da importare
Importare l’assembly in SQL Server
Creare un oggetto T-SQL che farà da wrapper alla funzione .NET
Finito 
19 December 2015 - slide