SQL - argomenti avanzati - SisInf Lab

SQL – argomenti avanzati
1
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
Introduzione
• Asserzioni
• Viste
• Indici
• Catalogo di sistema
• SQL Embedded
• SQL Dynamic
• Stored Procedures
• Triggers
2
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
Asserzioni
• Le Asserzioni introdotte in SQL-2 rappresentano dei vincoli che non sono però
associati a nessun attributo o tabella in particolare, ma appartengono
direttamente allo schema.
• Mediante le asserzioni è possibile esprimere tutti i vincoli d’integrità definiti nella
definizione della tabella (ossia vincoli di tupla e di tabella). In più le asserzioni
permettono di definire vincoli che altrimenti non sarebbero definibili, come vincoli
su più tabelle o vincoli che richiedono che una tabella abbia una cardinalità
minima.
• Le asserzioni possiedono un nome, tramite il quale possono essere eliminate
esplicitamente dallo schema. La sintassi per la loro definizione è la seguente:
create assertion NomeAsserzione check(condizione)
• Ad ogni vincolo di integrità (check o assertion) è associata una politica di
controllo: i vincoli immediati sono verificati immediatamente dopo ogni modifica
della base di dati (es. primary key, unique, not null, foreign key), mentre i vincoli
differiti sono verificati solo al termine della transazione (serie di operazioni)
set constraint NomeVincolo immediate|deferred
3
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
ESEMPIO DI CHECK (1/2)
Vincolo a livello di tupla:
Esempio – […] può essere richiesto del “personale speciale”
classificato come baby sitter, clown, marionette e mimo. Ciascuna figura è
caratterizzata da codice fiscale, nome e cognome […]
Create table personale_speciale
( CF char(16) primary key,
nome varchar(20) not null,
.
.
tipo varchar(11) not null check( tipo IN (‘baby sitter’,’clown’,’marionette’,’mino’)),
.
…. )
4
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
ESEMPIO DI CHECK (2/2)
Vincolo a livello di tabella:
Esempio – […] si verifichi che ciascun dipartimento abbia almeno due professori
associati […]
Create table dipartimento
( Codice char(10) primary key,
nome varchar(20) not null,
indirizzo varchar(100) not null
.
…,
check( 2 < = (Select count(*)
From Impiegato I
Where I.dipartimento = codice
AND I.ruolo = ‘Professore associato’))
)
5
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
ESEMPIO DI ASSERZIONE
Vincolo a livello di schema:
PRODOTTO(CodProd, QtaDisp)
ORDINE(Cliente, CodProd, Data, Qta)
Esempio – […] si verifichi che la quantità di un ordine non superi la quantità
disponibile in magazzino per il prodotto ordinato[…]
Create schema Magazzino authorization SisInfLAB
Create table Prodotto ( … )
Create table Ordine ( … )
Exists è un operatore che
restituisce vero se la
selectSQL ha almeno una
tupla e falso altrimenti
Create assertion gestioneQta
check ( NOT EXISTS ( Select *
From Prodotto P
Where QtaDisp < ( Select sum(Qta)
From Ordine
Where CodProd=P.CodProd )))
6
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
IMPORTANZA E DEFINIZIONE DELLE VISTE
 Per offrire visioni diverse degli stessi dati
 Per rendere più semplici alcune interrogazioni
 Per rendere possibili alcune interrogazioni
Sintassi
Create view NomeVista [(Lista di Attributi)] as
selectSQL [with [cascaded | local] check option]
Vista: tabella virtuale il cui contenuto è definito a partire da altre tabelle (tabelle
base) o viste nello schema, ma non ricorsive. In pratica è una relazione non
costituita da tuple, ma da una definizione.
Gli attributi nella lista devono essere in corrispondenza 1 a 1con le colonne
prodotte dalla query, oppure la vista li eredita dalla query.
Una vista è una query con un nome eseguita dinamicamente ma, a differenza di
una query, con una vista sono possibili operazioni di modifica come per le tabelle
7
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
VISTE AGGIORNABILI
VISTA AGGIORNABILE : le modifiche si propagano dalla vista alla tabella “base”
(gli aggiornamenti devono continuare ad appartenere alla vista)
• SQL92 consente l’update solo per viste determinate a partire da tabelle singole
senza funzioni aggregate. Cioè quando ogni tupla della vista mappa una tupla
della relazione di partenza.
• L’opzione with check option è necessaria quando si preveda l’aggiornamento di
una vista, indicando che un update deve far sì che le tuple risultanti appartengano
ancora alla vista (non violino i predicati di selezione).
• Per viste ottenute da altre viste, local e cascaded specificano, rispettivamente,
se il controllo vada effettuato solo al livello della vista presente o debba
propagarsi. Il default è cascaded.
Sintassi della selectSQL affinchè la vista sia sicuramente aggiornabile:
1. SELECT senza DISTINCT e funzioni aggregate
2. FROM una sola tabella (senza join)
3. WHERE senza subquery
4. GROUP BY ed HAVING non sono presenti
8
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
SIGNIFICATO DI CHECK OPTION (1/2)
IMPIEGATO (Codice, nome, cognome, stipendio, qualifica)
dove qualifica є {dipendente, direttore, supervisore}
CREATE VIEW Supervisori
AS SELECT *
FROM Impiegato
WHERE tipo = ‘supervisore’
Vista senza clausola
with check option
Dopo la creazione della vista eseguiamo il seguente comando:
UPDATE Supervisori SET tipo = ‘direttore’
Se ora eseguiamo il seguente comando quale sarà il risultato??
SELECT * FROM Supervisori
9
La vista è vuota!!!!
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
SIGNIFICATO DI CHECK OPTION (2/2)
CREATE VIEW Supervisori
AS SELECT *
FROM Impiegato
WHERE tipo = ‘supervisore’
WITH CHECK OPTION
Vista con la clausola
with check option
Dopo la creazione della vista eseguiamo il seguente comando:
UPDATE Supervisori SET tipo = ‘direttore’
Il comando non viene eseguito questa volta poiché la vista deve essere aggiornabile
quindi la vista non deve perdere tuple
Il sistema in questo caso verifica che la clausola WHERE della vista è in contrasto
con l’aggiornamento del campo tipo richiesto nell’update ed impedisce l’aggiornamento.
Poiché per default la clausola check option è cascaded, il rispetto della clusola WHERE
viene verificato per tutti gli “oggetti” che fanno riferimento alla vista.
10
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
QUERY IMPOSSIBILI …
IMPIEGATO (codice, nome, cognome, dipartimento, ufficio, stipendio_mensile)
DIPARTIMENTO (nome, indirizzo, città, tel, facoltà)
Interrogazione 29 :
Trovare il numero medio di impiegati dei dipartimenti del ‘Politecnico di Bari’
Esempio di query non corretta:
Select avg(count(*))
From Impiegato
Where dipartimento IN (Select nome
From Dipartimento
Where facoltà=‘Politecnico di Bari’)
Group by dipartimento
Soluzione
corretta
Create view NumImpiegatiDip(dipart, num_imp)
As Select dipartimento,count(*)
From Impiegato
Where dipartimento IN (Select nome
From Dipartimento
Where facoltà=‘Politecnico di Bari’)
Group by dipartimento
Select avg(num_imp)
From NumImpiegatiDip
11
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
Cancellazione di viste ed asserzioni
DROP VIEW NomeVista [restrict|cascade]
• Restrict: è l’opzione di default e specifica che il comando viene
eseguito solo se la vista non è utilizzata nella definizione di altre
tabelle o viste.
• Cascade: specifica che eliminando una vista che compare nella
definizione di altre tabelle o viste, anche queste tabelle o viste
vengono rimosse.
DROP ASSERTION NomeAsserzione [restrict|cascade]
• Restrict: è l’opzione di default e specifica che il comando viene
eseguito solo se l’asserzione non è più “utilizzata”.
• Cascade: specifica che l’asserzione possa sempre essere
cancellata.
12
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
PROGETTAZIONE FISICA
La progettazione fisica produce in uscita lo schema fisico della base di dati,
costituito dalle effettive definizioni delle relazioni e soprattutto delle strutture
fisiche utilizzate.
L’attività di progettazione fisica può essere molto complessa, perché oltre
alle scelte relative alle strutture fisiche (organizzazione dei files e degli
indici), può essere necessario definire molti parametri, come dimensioni
iniziali dei file, possibilità di espansione e contiguità di allocazione.
La specifica dei parametri fisici di memorizzazione dei dati dipende dallo
specifico sistema di gestione (DBMS) scelto.
La progettazione fisica è ricondotta principalmente all’attività di
individuazione degli indici da definire su ciascuna relazione (a parte la
specifica degli schemi delle relazioni in DDL)
13
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
INDICE – SIGNIFICATO
L’indice è una struttura ausiliaria per l’accesso efficiente ai dati
Indice primario: è quello che viene normalmente definito di tipo unique sulla chiave primaria
(primary key) di ciascuna tabella. Si osservi che chiave ed indice primario sono concetti
differenti, il primo fa riferimento ad una proprietà astratta dello schema ed il secondo ad una
proprietà della implementazione fisica della base di dati.
Indici secondari: sono quelli che possono essere ti tipo unique e multiple; nel secondo
caso ad ogni valore di una chiave dell’indice possono corrispondere varie tuple.
Le operazioni più delicate in una base di dati relazionale sono quelle di selezione (che
corrisponde all’accesso ad uno o più record sulla base dei valori di uno o più attributi) e di join
(che richiede di combinare tuple di relazioni diverse sulla base dei valori di uno o più attributi di
ognuna di tali relazioni). Ciascuna delle due operazioni può essere eseguita in modo molto più
efficiente se sui campi interessati è definito un indice, che permette un accesso diretto.
Possono essere definiti ulteriori indici su altri campi su cui vengono effettuate operazioni di
selezione oppure su cui è richiesto un ordinamento in uscita (perché un indice ordina
logicamente i record di un file, rendendo nullo il costo di un ordinamento).
È buona norma, dopo l’aggiunta di un indice, verificare che le interrogazioni ne facciano uso (in
genere, esiste un comando show plan che descrive la strategia di accesso scelta dal DBMS).
14
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
INDICE – SINTASSI
Il comando per la creazione di un indice è messo a disposizione da ciascun DBMS ma
non fa parte dello standard SQL-2.
CREATE [unique] INDEX NomeIndice on NomeTabella ( Lista attributi)
L’ordine in cui compaiono gli attributi nella lista è significativo: le chiavi dell’indice
vengono ordinate prima in base ai valori del primo attributo della lista, poi a parità di
valore del primo attributo si usano i valori del secondo attributo, e così in sequenza
fino all’ultimo attributo.
L’uso della parola unique specifica che nella tabella non sono ammesse tuple che
abbiano lo stesso valore relativamente agli attributi dell’indice (in termini relazionali si
tratta di una “chiave candidata”).
Il comando DROP INDEX NomeIndice elimina l’indice specificato.
In generale non è semplice stabilire quali indici creare su una base di dati per
migliorare le prestazioni complessive delle applicazioni poiché se da un lato gli indici
accelerano le operazioni di ricerca, dall’altro occupano memoria e rallentano le
operazioni di aggiornamento.
15
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
CATALOGHI RELAZIONALI
Tutti i DBMS relazionali gestiscono il proprio dizionario dei dati (ovvero la descrizione
delle tabelle presenti nella base di dati) mediante una struttura relazionale, cioè
tramite tabelle.
La base di dati contiene quindi due tipi di tabelle: quelle che contengono i dati e quelle
che contengono i cosiddetti metadati (dati che descrivono i dati). Questo secondo
insieme di tabelle costituisce il catalogo della base di dati. In questo modo la base di
dati può utilizzare per la gestione interna dei metadati le stesse funzioni che vengono
usate per la gestione delle istanze.
Il dizionario dei dati è differente in ciascun DBMS.
Lo standard SQL-2 prevede per il dizionario dei dati una descrizione in 2 livelli. Un
primo livello è quello del definition_schema, costituito da un insieme di tabelle
che contengono la descrizione di tutte le strutture della base di dati. Il secondo
componente dello standard è l’ information_schema, un’insieme di viste definite
sul definition_schema che contiene viste come domains, domain_constraint,
tables, views e columns che descrivono la struttura della base di dati.
Esempio: in SQL Server la tabella syscolumns contiene campi come name, table_id,
column_id, type, cdefault, domain, scale, isnullable, etc.
16
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
Uso di SQL e nei linguaggi di programmazione
• Nella “vita reale” l’interazione con basi di dati utilizzando SQL non è
diretta, ma mediata attraverso l’uso di programmi applicativi. Oltre ai
tradizionali linguaggi di programmazione di alto livello (procedurali ed a
oggetti) esistono i cosiddetti linguaggi di quarta generazione (4GL),
strumenti di sviluppo molto sofisticati che permettono di sviluppare
complete applicazioni per la gestione di basi di dati.
• E’ necessario stabilire modalità di interazione tra SQL e tali linguaggi. I
problemi di interazione sono rappresentati dall’ Impedence mismatch
(“disaccoppiamento di impedenza”) fra base di dati e linguaggio di
programmazione poiché i linguaggi operano tipicamente su singole
variabili o oggetti mentre SQL opera su relazioni (insiemi di tuple)
• In alcuni approcci la soluzione ai problemi di interazione è rappresentata
dall’uso dei cursori
17
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
PRINCIPALI APPROCCI
• Call Level Interface (CLI)
SQL statico
• SQL embedded (SQL “integrato”)
SQL dinamico
• Stored Procedures
18
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
CLI – COME SI UTILIZZANO
E’ una Application Programming Interface (API) per accedere ai
databases, cioè un insieme di funzioni chiamabili direttamente dal
linguaggio di programmazione usato. Il database mette a disposizione
una libreria di funzioni, che seguono lo standard CLI. Generalmente
vengono usate per applicazioni basate su SQL dinamico.
API
Oracle
Prog
C
Oracle
API
Interbase
Interbase
19
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
•
•
•
EVOLUZIONE di CLI
Utilizza dei driver specifici per ciascun database
E’ uno strato software che si interpone tra l’applicazione e i driver specifici del
database. Consente quindi ai programmi applicativi di usare query standard
SQL, che accederanno al database, senza necessità di conoscere la particolare
interfaccia proprietaria.
Un modulo del linguaggio di programmazione fornisce una API (serie di funzioni
o classi se OO) che permette di interfacciare qualsiasi database tramite un
driver specifico per quel database (es. ODBC, JDBC)
Prog
JAVA
Driver
Oracle
JDBC
API
Oracle
Driver
Interbase
Interbase
20
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
Implementazioni delle CLI
ODBC (Open Database Connectivity): implementazione proprietaria (Microsoft) di
SQL/CLI (altra CLI parte di SQL:1999), per accesso a basi di dati relazionali in un
contesto eterogeneo e distribuito. Originariamente rilasciata nel 1992, consente di
accedere a database di numerosissimi costruttori, inclusi Microsoft (jet, Access,
SQLServer); Oracle, IBM, Informix e numerosi altri.
JDBC (Java Database Connectivity): è un API Java sviluppata da JavaSoft per
eseguire istruzioni SQL e consente ai programmi Java di interagire con qualunque
database. Poiché Java gira sulla maggior parte delle piattaforme, JDBC rende
possibile scrivere una singola applicazione di database che può girare
indifferentemente su piattaforme diverse e interagire con diversi database. JDBC è
simile a ODBC ma è progettato apposta per programmi Java mentre ODBC è
indipendente dal linguaggio di programmazione.
Nello scrivere programmi in Java e usando l’interfaccia JDBC si può usare un prodotto
che comprende un "bridge" program JDBC-ODBC per raggiungere database ODBCaccessible.
In generale le operazioni richieste sono: caricamento del driver, apertura della
connessione con la base di dati, richiesta di esecuzione di istruzioni SQL ed
elaborazione dei risultati delle istruzioni SQL
21
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
EMBEDDED SQL
•
•
Tecnica sviluppata sin dagli anni ’70
L’ SQL è “ospitato” in un linguaggio di programmazione (Java, Pascal,
Cobol, C, C++, …) che è chiamato linguaggio ospite in quanto questi
standard hanno definito l’interfaccia con SQL: il sorgente del vostro
programma contiene sia codice nel linguaggio di programmazione che
codice sql
• un preprocessore, dipendente sia dal linguaggio ospite che dalla
piattaforma del DBMS viene usato per analizzare il codice e tradurlo
sostituendo le istruzioni SQL con chiamate alle funzioni di una API del
DBMS prima della compilazione vera e propria
Ogni programma che usa Embedded SQL deve:
1. Definire quale database usare
2. Connettersi al database
3. Effettuare le operazioni sul database (usando cursori e statementSQL)
4. Disconnettersi dal database
22
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
ESEMPIO in C di SQL statico– prima….
#include<stdlib.h>
#include<stdio.h>
#include<sqlenv.h>
main()
{
exec sql begin declare section; /*dichiarazione variabili*/
char *NomeDip = “DEE";
int Id_Dip = 3;
exec sql end declare section; /*fine della dichiarazione variabili*/
}
23
exec sql connect to esempio@azienda_esempio;
if (sqlca.sqlcode != 0) {
/*sqlca (SQL Communication Area) è una struttura di dati predefinita che mantiene le
informazioni sulla comunicazione tra SQL e programma, sqlcode=0 no errore,
altrimenti codice dell’errore*/
printf(“Errore di connessione al DB\n"); }
else {
exec sql insert into Dipartimento
values(:NomeDip,:Id_Dip);
/* notare l’uso del : per utilizzare le variabili del programma ospite in SQL*/
exec sql disconnect all;
}
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
ESEMPIO in C di SQL statico – prima e
dopo …
main()
{
exec sql connect to universita
user pguser identified by pguser;
exec sql create table studente (matricola integer primary key,
nome varchar(20),
annodicorso integer);
exec sql disconnect;
}
/* These include files are added by the preprocessor */
#include
#include
#include
#include
<ecpgtype.h>
<ecpglib.h>
<ecpgerrno.h>
<sqlca.h>
main()
{
ECPGconnect(__LINE__, "universita" , "pguser" , "pguser" , NULL, 0);
ECPGdo(__LINE__, NULL, "create table studente ( matricola integer primary key ,
nome varchar ( 20 ) , annodicorso integer )", ECPGt_EOIT, ECPGt_EORT);
ECPGdisconnect(__LINE__, "CURRENT");
}
24
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
CURSORI
• Quando una query restituisce più tuple è necessario passarle al
programma ospite una per volta
• Si utilizza un cursore per accedere a tutte le tuple di una interrogazione,
accede a tutte le tuple di una interrogazione in modo globale (tutte
insieme o a blocchi – è il DBMS che sceglie la strategia efficiente); il
cursore passa poi una tupla per volta al programma
• Il cursore viene definito su una generica interrogazione mediante la
seguente sintassi
declare Cursor_Name [scroll ] cursor for
SelectSQL [for <read only| update [of attribute,{attribute}]>]
Dove, scroll indica che il cursore può muoversi liberamente sul risultato
della query, mentre for update indica che il cursore può essere utilizzato in
operazioni di aggiornamento permettendo di specificare eventualmente
gli attributi oggetto del comando.
Si osservi che per riga corrente si considera l’ultima riga letta.
25
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
UTILIZZO DEL CURSORE
•
Esegue la query: open Cursor_Name
•
Utilizzo dei risultati (una ennupla alla volta): fetch [position from]
Cursor_Name into Variables (accedere alla tupla corrente del risultato o a
quella indicata da position e porne i valori nelle variabili del programma ospite)
•
Position (utilizzabili se l’opzione scroll è stata imposta altrimenti è disponibile solo
next) :
next (con riferimento a current indica la prossima tupla);
prior (tupla precedente);
first (prima tupla);
last (ultima tupla);
absolute integer_value (tupla posizionata al valore ordinale espresso)
relative integer_value (tupla posizionata al valore ordinale espresso, rispetto
alla posizione corrente)
•
Accedere alla tupla corrente di un cursore per effettuare un update o un delete (si
usa solo nella clausola where e quando la query associata al cursore non esegue
un join tra diverse tabelle):
es. Delete from NomeTabella where current of Cursor_Name
•
Chiusura: close cursor Cursor_Name
26
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
ESEMPIO IN C CON UTILIZZO DI
CURSORI
……
printf(“nome della citta‘?”);
scanf(“%s”,&citta[0]);
/*creo il cursore*/
EXEC SQL DECLARE P CURSOR FOR SELECT NOME, REDDITO
FROM Impiegato
WHERE CITTA = :citta ;
EXEC SQL OPEN P ; /* apro il cursore -> eseguo la query*/
EXEC SQL FETCH P INTO :nome, :reddito ; /*prelevo i dati*/
while (sqlca.sqlcode == 0)
{
printf(“Qual è l’aumento per %s? ”, nome);
scanf(“%d”,&aumento);
EXEC SQL UPDATE Impiegato SET REDDITO = REDDITO + :aumento
WHERE CURRENT OF P; /*eseguo l’update su current*/
EXEC SQL FETCH P INTO :nome, :reddito; ; /*prelevo i dati per la tupla
immediatamente successiva*/
}
EXEC SQL CLOSE CURSOR
…..
27
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
DYNAMIC SQL
•
Non sempre le istruzioni SQL sono note quando si scrive il codice ospite (si
pensi, ad esempio, ad un’applicazione che interagisce con utenti). E’ stata
introdotta una tecnica completamente diversa, chiamata SQL dinamico, che
permette di generare istruzioni SQL a tempo di esecuzione (addirittura
ricevute dal programma attraverso parametri o da input). Ovviamente, la
tecnica porta ad un degrado delle prestazioni.
•
Le operazioni in SQL dinamico possono essere eseguite immediatamente
oppure prima “preparate” e poi eseguite (anche più volte)
•
La differenza è che, in SQL statico, i nomi delle relazioni e degli attributi
coinvolte nelle istruzioni SQL sono fissate a priori; l’unica parte delle
istruzioni che può rimanere non nota a tempo di compilazione è costituita
dagli specifici valori da ricercare o da aggiornare. Nell’SQL dinamico,
invece, le istruzioni SQL sono generate a tempo di esecuzione; non è quindi
necessario specificare relazioni e attributi coinvolti in una istruzione SQL
prima della sua esecuzione.
28
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
MODALITÀ DI ESECUZIONE
Le operazioni SQL possono essere:
•
•
Esempio:
Char *
Con esecuzione immediata (in assenza di parametri)
SQL_string=“delete from Impiegato
execute immediate SQLStatement
where CF=27”;
…
exec sql execute immediate :
SQL_string;
Con esecuzione differita e ripetibile (anche con parametri):
prepare CommandName from SQLStatement
execute CommandName [into TargetList][using ParameterList]
Esempio:
Prepare :trova_nome From “select nome
from Impiegato
where CF =?”
Execute :trova_nome into:nome_imp using :cf_imp
Rilascio del comando: Deallocate prepare :trova_nome
29
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
SQL Embedded VS CLI
SQL embedded
• pemette precompilazione (e quindi efficienza e portabilità del
sorgente)
• uso di SQL completo
• Molto usato in applicazioni legacy
CLI
• indipendente dal DBMS, uso di funzioni di interazione con i DBMS,
attraverso Application Programming Interfaces (API)
• permette di accedere a più basi di dati, anche eterogenee (senza
ricompilazione: portabilità dell’eseguibile): richiede un ulteriore
strato di comunicazione
 esistono drivers specifici per i vari DBMS
30
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
STORED PROCEDURES
A partire da SQL-2 è possibile definire procedure dette stored procedures per il fatto che
vengono memorizzate all’interno della base di dati come parti dello schema.
Le procedure permettono di associare un nome ad un’istruzione SQL, con la possibilità di
specificare dei parametri da utilizzare per lo scambio di informazioni con la procedura. I vantaggi
sono: minor quantità di dati trasferita tra client e server, una più facile manutenibiltà, favorisce il
riutilizzo della logica dell’applicazione da parte di utenti diversi e la possibilità di ottenere in diversi
casi un sensibile incremento delle prestazione. In tal modo però la logica di business risulta in
gran parte inglobata nella base di dati.
Una volta che la procedura è definita, essa è utilizzabile come se facesse parte dell’insieme dei
comandi SQL predefiniti. La procedura può essere invocata avendo cura di associare un valore ai
Parametri.
procedure Update_address(:cod_dip int, :Addr varchar(60))
Begin;
update Department set Address =:Addr where ID_dip =:cod_dip
End;
exec sql Update_address(:codice,:indirizzo);
Definizione
Chiamata
Molti sistemi, e.g Oracle PL/SQL, presentano estensioni ricche e più complesse (ad es. costrutti
if – else, cicli while che rendono SQL un linguaggio computazionalmente completo ossia con lo
stesso potere espressivo di un normale linguaggio di programmazione).
31
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
TRIGGERS – IL PARADIGMA ECA
Una base di dati si dice attiva quando dispone di un sottosistema integrato per
definire e gestire regole di produzione (regole attive) che seguono il paradigma
E (evento) – C (condizione) – A (azione). Allo stato attuale molte basi di dati
relazionali sia di tipo commerciale (Oracle, MS SQL server) che open source
(PostgreSQL, MySQL) possono essere considerate basi di dati attive poiché
mettono a disposizione semplici regole chiamate trigger.
Evento – è una primitiva per la manipolazione dei dati in SQL (DML):
insert, update o delete
Condizione – è un predicato booleano espresso in SQL (stessi operatori
utilizzati per definire una condizione semplice o complessa
della clausola WHERE di una selectSQL)
Azione – è in generale una sequenza di statement SQL (select, insert,
update e delete) arricchita dai costrutti forniti da linguaggi di
programmazione proprietari (PL/sql in Oracle, PL/pgsql in
PostgreSQL)
32
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
TRIGGERS – DEFINIZIONE
Una regola attiva è definita su una sola tabella chiamata target. La regola è
attivata a seguito di un evento su tale tabella, se la condizione è verificata
allora viene eseguita l’azione.
Le regole attive servono per
gestione interna (DBMS) – le regole attive possono gestire vincoli di integrità
predefiniti (per es. una politica di reazione di tipo cascade per una foreign key
può essere implementata con una regola attiva), calcolare attributi derivati,
gestire dati duplicati e le eccezioni (per es. sollevate dalla violazione dei vincoli
di integrità)
gestione esterna – le regole attive permettono di codificare complesse regole
aziendali (business rules) non rappresentabili in altro modo nello schema (per
es. mediante check o assertion). In questo caso non esistono schemi fissi per
la codifica delle regole e ciascun problema applicativo va affrontato
singolarmente.
33
E. Tinelli
Basi di Dati+Lab
Laurea Triennale in
Informatica
TRIGGERS – SINTASSI
Problema – I trigger non sono stati definiti in SQL-92, la sintassi ed la gestione variano a
seconda dello specifico DBMS utilizzato
CREATE TRIGGER NomeTrigger
modalità evento {, evento}
on TabellaTarget
[referencing referenza]
[granularità]
[when (condizione)]
StatementSQL
Dove evento, condizione e statementSQL rappresentano
rispettivamente Evento, Condizione e Azione
del paradigma ECA, mentre la clausola referencing
permette di rinominare le variabili predefinite NEW
(rappresenta la nuova tupla ) ed OLD (rappresenta la
vecchia tupla ) ad esempio è possibile scrivere:
referencing NEW AS nuoviDati
Per accedere ad un campo specifico di una tupla si usa
la dot notation
Modalità (di valutazione) può essere after: la valutazione avviene immediatamente dopo l’evento
(caso più frequente) oppure before: la valutazione del trigger precede logicamente l’evento a cui
si riferisce
Granularità può essere a livello di riga (clausola for each row nel comando create trigger) in
questo caso il trigger viene attivato, verificato ed eseguito per ogni tupla della tabella target,
oppure a livello di primitiva (è il livello di default clausola for each statement) in questo caso il
trigger viene attivato, verificato ed eseguito una sola volta per tutte le tuple della tabella target.
34
E. Tinelli