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
4 June 2017 - slide 2
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
4 June 2017 - slide 3
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
4 June 2017 - slide 4
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
4 June 2017 - slide 5
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
4 June 2017 - slide 6
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
4 June 2017 - slide 7
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
4 June 2017 - slide 8
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
4 June 2017 - slide 9
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
4 June 2017 - slide 10
Nome dell’evento
Database Schema
Application
Stored Procedure
4 June 2017 - slide 11
Nome dell’evento
Database Schema
Stored Procedures
Application
Stored Procedure
4 June 2017 - slide 12
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
4 June 2017 - slide 13
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
4 June 2017 - slide 14
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
4 June 2017 - slide 15
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
4 June 2017 - slide 16
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
4 June 2017 - slide 17
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
4 June 2017 - slide 18
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
4 June 2017 - slide 19
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
4 June 2017 - slide 20
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
4 June 2017 - slide 21
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
4 June 2017 - slide 22
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
4 June 2017 - slide 24
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
4 June 2017 - slide 25
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
4 June 2017 - slide 26
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
4 June 2017 - slide 27
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
4 June 2017 - slide 28
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
4 June 2017 - slide 29
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
4 June 2017 - slide 30
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
4 June 2017 - slide 31
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
4 June 2017 - slide 32
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
4 June 2017 - slide 33
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
4 June 2017 - slide 34
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
4 June 2017 - slide 35
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
4 June 2017 - slide 36
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
4 June 2017 - slide 37
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
4 June 2017 - slide 38
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
4 June 2017 - slide 39
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
4 June 2017 - slide 40
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
4 June 2017 - slide 42
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
4 June 2017 - slide 43
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
4 June 2017 - slide 44
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
4 June 2017 - slide 45
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
4 June 2017 - slide 46
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
4 June 2017 - slide 47
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
4 June 2017 - slide 48
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
4 June 2017 - slide 49
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
4 June 2017 - slide 50
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
4 June 2017 - slide 51
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
4 June 2017 - slide 52
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
4 June 2017 - slide 53
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
4 June 2017 - slide 55
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
4 June 2017 - slide 56
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
4 June 2017 - slide 57
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.
4 June 2017 - slide 58
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
4 June 2017 - slide 59
