Linguaggio SQL

Basi di Dati
prof. G.Tombolini
Gruppo utenti 1
Vista 1
Gruppo utenti 2
Vista 2
Database concettuale
Definizione
e corrispondenza
scritte nel linguaggio
di definizione dei dati
Gruppo utenti n
Vista n
Database
fisico
Implementato
su supporti
fisici
Definizione
e corrispondenza
scritte nel linguaggio
di definizione dei dati
del sotto schema
ELEMENTI BASE :SQL
1 1
DML
(Data Manipulation
Language)
2 2
DDL
(Data Definition Language)
SELECT
CREATE
INSERT
ALTER
UPDATE
DROP
DELETE
RENAME
TRUNCATE
3 3
4 4
TCL
(Transaction Control Language)
DCL
(Data Control Language)
COMMIT
GRANT
ROLLBACK
REVOKE
SAVEPOINT
5 5
6 6
Istruzioni select di base
Scrittura di istruzioni sql
SELECT
identifica le colonne
FROM identifica la tabella
7 7
Le istruzioni SQL possono essere scritte
indifferentemente in maiuscolo o in minuscolo.
Le istruzione SQL possono essere scritte su una o più
righe
Non è possibile abbreviare o dividere su più righe le
parole chiavi
Le clausole vengono in generale scritte su righe
diverse
Per facilitare le lettura del codice, è possibile utilizzare
8 8
tabulazioni e indentazione
Selezione di tutte le colonne
Impostazioni predefinite delle
intestazioni di colonne
SQL> SELECT *
2 FROM IMPIEGATI;
Dipartimento
------------------10
20
nome
località
--------- ---------ricerca
NY
sviluppo
Dallas
Allineamento predefinito
- Sinistra: dati di tipo carattere e data
- Destra: dati di tipo numerico
Visualizzazione predefinita: caratteri maiuscoli
9 9
Espressioni aritmetiche
Operatore di concatenazione
Creare espressioni su dati di tipo NUMBER e
DATE utilizzando operatori aritmetici
OPERATORE
DESCRIZIONE
+
Addizione
-
Sottrazione
*
Moltiplicazione
/
Divisione
10 10
11 11
Collega
tramite concatenazione colonne o
stringhe di caratteri ad altre colonne
E’ rappresentato da due barre verticali o
pipe ||
Crea una nuova colonna che rappresenta
un’espressione di caratteri
12 12
Tipi di dati disponibili
Stringhe di caratteri
Le
stringhe di caratteri sono caratteri,
numeri o date contenuti nella lista dopo
l’istruzione SELECT
I valori di tipo carattere e data devono
essere racchiusi tra apici (‘ ‘)
Ciascuna stringa d caratteri viene
visualizzata una volta per ciascuna riga
restituita.
Tipi di dati
NUMBER(p,s)
Descrizione
VARCHAR2(s)
Dati di tipo carattere con lunghezza variabile
massima s.
DATE
Data e ora comprese tra il 1° gennaio 4712
a.C. e il 31 dicembre 9999 d.C.
CHAR(s)
Dati di tipo carattere con lunghezza fissa s
Valore numerico con un numero massimo di
cifre p e un numero di decimali s inseriti a
destra della virgola decimale
13 13
14 14
Limitazione delle righe selezionate
LIMITAZIONE
Limitare le righe selezionate utilizzando la
clausola WHERE
SQL> SELECT [DISTINCT]{*| colonna [alias], …}
FROM
tabella
[WHERE
condizione/i ];
E
ORDINAMENTO
15 15
La clausola WHERE segue la clausola FROM
16 16
Stringhe di caratteri e date
Operatori di confronto
Le
stringhe di caratteri e le date vengono
racchiuse tra apici (‘’).
I valori di tipo carattere non possono
essere scritti indifferentemente in
maiuscolo o minuscolo, mentre per le date
è rilevante il formato.
Il formato data predefinito è DD-MON-YY
Operatore
Significato
=
Uguale a
>
Maggiore di
>=
Maggiore o uguale a
<
<=
Minore di
Minore o uguale a
<>
Diverso da
17 17
Altri operatori di confronto
Operatore
Significato
BETWEEN
…AND…
Tra due valori
IN(Lista)
Corrispondenza a un valore
qualsiasi della lista
LIKE
Corrispondenza a un carattere
campione
IS NULL
E’ un valore nullo
18 18
Utilizzo dell’operatore BETWEEN
Utilizzare l’operatore BETWEEN per
visualizzare le righe in base a un intervallo di
valori
SQL> SELECT nome, salario
FROM impiegati
WHERE salario BETWEEN 1000 AND 1500;
NOME
----------MARTIN
TURNER
ADAMS
19 19
SALARIO
----------1250
1500
1100
20
Utilizzo dell’operatore IN
Utilizzo dell’operatore LIKE
Utilizzare l’operatore IN per verificare se un
valore è contenuto in una determinata lista
SQL> SELECT matricola, nome, cod_superiore
FROM impiegato
WHERE cod_superiore IN (7902,7566,7788);
MATRICOLA
NOME
7902
FORD
7369
7788
SALARIO
COD_SUPERIORE
3000
7566
SMITH
800
7902
SCOTT
1100
7788
Utilizzare
l’operatore LIKE per ricercare
valori di stringa validi utilizzando i caratteri
jolly
Le condizioni di ricerca possono contenere
sia caratteri che numeri
- % corrisponde a zero o a più caratteri
- _ corrisponde a un singolo carattere
21 21
Caratteri jolly
Utilizzo dell’ operatore IS NULL
%
SQL> SELECT nome
FROM impiegato
WHERE nome LIKE ‘M%’;
22 22
L’operatore IS NULL consente di verificare
se vi sono valori nulli
NOME
--------------MARTIN
MILLER
SQL> SELECT nome, mansione
FROM impiegato
WHERE mansione IS NULL;
_
SQL> SELECT nome
FROM impiegato
WHERE nome LIKE ‘M___’;
NOME
NOME
KING
--------------MARK
MANSIONE
MATT
23 23
24 24
Operatori logici
Utilizzo dell’operatore AND
AND richiede che entrambe le condizioni
restituiscano TRUE
Operatore
Significato
AND
Restituisce TRUE se entrambe le
condizioni sono TRUE
OR
Restituisce TRUE se una delle due
condizioni è TRUE
NOT
Restituisce TRUE se la condizione
che segue è FALSE
SQL> SELECT matricola, nome, mansione, salario
FROM impiegato
WHERE salario >= 1100
AND mansione = ‘MANAGER’;
MATRICOLA
NOME
7876
7934
MANSIONE
SALARIO
ADAMS
MANAGER
MILLER
MANAGER
1100
1300
25 25
26 26
Tavola di verità AND
Utilizzo dell’operatore OR
La tabella seguente riporta i risultati ottenuti
combinando due espressioni con AND
AND
TRUE
FALSE
NULL
TRUE
TRUE
FALSE
NULL
FALSE
FALSE
FALSE
FALSE
NULL
NULL
FALSE
OR richiede che una delle due condizioni
restituisca TRUE
SQL> SELECT matricola, nome, mansione, salario
FROM impiegato
WHERE salario >= 1100
OR mansione = ‘MANAGER’;
MATRICOLA
NULL
27 27
NOME
MANSIONE
SALARIO
7839
KING
PRESIDENT
5000
7698
7666
JONES
CLARK
MANAGER
MANAGER
950
1250
28 28
Tavola di verità OR
Utilizzo dell’operatore NOT
La tabella seguente riporta i risultati ottenuti
combinando due espressioni con OR
OR
TRUE
FALSE
NULL
TRUE
TRUE
TRUE
TRUE
FALSE
TRUE
FALSE
NULL
NULL
TRUE
NULL
NULL
SQL> SELECT nome, mansione
FROM impiegato
WHERE mansione NOT IN
(‘MANAGER’,’ANALYST’);
NOME
KING
MARTIN
TURNER
MANSIONE
PRESIDENT
SALESMAN
SALESMAN
29 29
Regole di precedenza
Tavola di verità NOT
Ordine di esaminazione Operatore
La Tabella seguente riporta i risultati ottenuti
applicando l’operatore NOT a una condizione
NOT
TRUE
TRUE
FALSE
FALSE
TRUE
30 30
NULL
1
Tutti gli operatori di confronto
2
NOT
3
AND
4
OR
NULL
31 31
E’ possibile utilizzare le parentesi per
specificare l’ordine di precedenza degli
operatori
32 32
Clausola ORDER BY
Ordinare
Utilizza clausola ORDER BY
la righe con la clausola ORDER
BY:
- ASC: ordine crescente(impostazione
predefinita)
- DESC: ordine decrescente
Nell’istruzione SELECT la clausola ORDER
BY viene inserita per ultima
SQL> SELECT nome, mansione, cod_dipartimento,
data_assunzione
FROM impiegato
ORDER BY data_assunzione;
NOME MANSIONE
SMITH MANAGER
ALLEN ANALYST
COD_DIPARTIMENTO
20
30
DATA_ASSUNZIONE
17-DEC-80
20-FEB-81
33 33
Ordinamento in ordine decrescente
34 34
Ordinamento in ordine crescente
SQL> SELECT nome, mansione, cod_dipartimento,
data_assunzione
FROM impiegato
ORDER BY data_assunzione ASC;
SQL> SELECT nome, mansione, cod_dipartimento,
data_assunzione
FROM impiegato
ORDER BY data_assunzione DESC;
NOME
NOME
MANSIONE
SMITH MANAGER
ALLEN ANALYST
MILLER MANAGER
COD_DIPARTIMENTO
MANSIONE
COD_DIPARTIMENTO
DATA_ASSUNZIONE
DATA_ASSUNZIONE
20
30
20
17-DEC-83
20-FEB-82
28-SEP-81
35 35
SMITH MANAGER
ALLEN ANALYST
MILLER MANAGER
20
30
20
17-DEC-81
20-FEB-82
28-SEP-83
36 36
Ordinamento in base ad alias di
colonna
SQL> SELECT matricola, nome, salario*12 sal_annuo
FROM impiegato
ORDER BY data_assunzione sal_annuo;
MATRICOLA
7369
7654
876
NOME
SMITH
JAMES
ADAMS
Funzioni che agiscono
su una sola riga
SAL_ANNUO
9600
11400
13200
37 37
Funzioni che agiscono su una sola riga
38 38
Funzioni di stringa
Le funzioni che agiscono su una sola riga sono utilizzate per manipolare i
dati. Accettano uno o più argomenti e restituiscono un valore per ogni riga
restituita dalla query. È possibile scegliere tra i seguenti argomenti:
– Costante definita dall’utente;
– Valore di variabile;
– Nome di colonna;
– Espressione.
Caratteristiche delle funzioni che agiscono su una sola riga:
– Agiscono su ciascuna riga restituita dalla query;
– Restituiscono un risultato per riga;
– Possono restituire un valore di un tipo diverso da quello a cui è stato fatto
riferimento;
– Accettano uno o più argomenti;
– Possono essere utilizzate nelle clausole SELECT, WHERE e ORDER BY e
possono essere nidificate.
Nome_funzione (colonna | espressione, [arg1, arg2, …])
39 39
Funzioni
di stringa
Funzioni di conversione
dei caratteri in maiuscolo
e minuscolo
LOWER
UPPER
INITCAP
Funzioni di
manipolazione
delle stringhe
CONCAT
SUBSTR
LENGTH
INSTR
LPAD
TRIM
40 40
Funzioni di stringa
Esempi di funzioni di stringa
Funzione
Descrizione
LOWER(colonna|espressione)
Converte le stringhe con caratteri misti o maiuscoli in caratteri minuscoli
UPPER(colonna|espressione)
Converte le stringhe con caratteri misti o minuscoli in caratteri maiuscoli
INITCAP(colonna|espressione)
Converte la prima lettera di ogni parola in maiuscolo e in minuscolo le restanti
lettere della parola
CONCAT(colonna1|espressione1,
colonna2|espressione2)
Concatena il primo valore di tipo carattere al secondo; equivale all’operatore di
concatenazione ( || )
SUBSTR(colonna|espressione, m
[,n])
Restituisce i caratteri specificati dal valore di tipo carattere m per una
lunghezza di n caratteri. Se m è negativo, il conteggio inizia dalla fine del valore
di tipo carattere. Se n è assente, vengono restituiti tutti i caratteri fino alla fine
della stringa
LENGTH(colonna|espressione)
Restituisce il numero di caratteri nella stringa
INSTR(colonna|espressione, m)
Restituisce la posizione numerica di un determinato carattere
LPAD(colonna|espressione, n,
‘string’)
Riempie una stringa fino a una lunghezza totale di n caratteri aggiungendo a
sinistra una determinata serie di caratteri
TRIM(iniziale|finale|entrambi,
carattere_da_eliminare FROM
stringa_da_cui_eliminare)
Consente di eliminare i caratteri iniziali o finali (o entrambi) di una stringa. Se
carattere_da_eliminare o stringa_da_cui_eliminare sono stringhe di caratteri, è
necessario racchiuderle tra apici ( ‘ ‘ ). Questa funzione è disponibile a partire
dalla versione Oracle8i
Funzione
Risultato
LOWER(‘SQL Course’)
sql course
UPPER(‘SQL Course’)
SQL COURSE
INITCAP(‘SQL Course’)
Sql Course
CONCAT(‘Good’ , ‘String’)
GoodString
SUBSTR(‘String’, 1, 3)
Str
LENGTH(‘String’)
6
INSTR(‘String’, ‘r’)
3
LPAD(sal, 10, ‘*’)
******5000
TRIM(‘S’ FROM ‘SSMITH’)
MITH
41 41
42 42
Utilizzo delle date
Funzioni numeriche
Funzione
Descrizione
ROUND(colonna|espressione, n )
ROUND(45.926,2)
45.93
Arrotonda la colonna, l’espressione o il
valore a n decimali oppure, se n viene
omesso, senza decimali. Se n è
negativo, vengono arrotondati i numeri a
sinistra della virgola
TRUNC(colonna|espressione, n )
TRUNC (45.926,2)
45.92
Tronca la colonna , l’espressione o il
valore a n decimali oppure, se n viene
omesso, senza decimali. Se n è
negativo, i numeri a sinistra della virgola
vengono troncati a zero, < 0
MOD(m, n)
MOD(1600, 300)
Restituisce il resto della divisione di m
per n. < 0
100
43 43
Oracle memorizza le date in formato numerico
interno:secolo, anno, mese, giorno, ore, minuti,
secondi.
Il formato data predefinito è DD-MM-YY.
La funzione SYSDATE restituisce data e ora
correnti.
DUAL è una tabella fittizia utilizzata per
visualizzare SYSDATE è di proprietà dell’
utente SYS ed è accessibile a tutti gli
utenti.Contiene una colonna DUMMY e una
riga con il valore X .
44 44
Funzioni di data
Operazioni aritmetiche con le date
Funzione
Descrizione
Sommare o sottrarre un numero da una data in modo da
ottenere un valore di tipo data .
Eseguire la sottrazione tra due date per trovare il numero di
giorni trascorsi tra di esse.
Sommare ore a una data dividendo il numero di ore per 24.
MONTHS_BETWEEN(data1, data2)
MONTHS_BETWEEN(’01-SET-95’,’11-GEN-94’)
19.6774194
Trova il numero di mesi trascorsi tra data1 e
data2. Il risultato può essere un valore positivo
o negativo.
ADD_MONTHS(data, n)
ADD_MONTHS(’11-GEN-94’,6)
Aggiunge un numero n di mesi alla data. Il
valore n deve essere un numero intero e può
essere negativo.
Operazione
Risultato
Descrizione
Data + numero
Data
Somma un numero di giorni a una
data. < 0
Data – numero
Data
Sottrae un numero di giorni da una
data. < 0
Data – data
Numero di giorni Sottrae una data da un’altra data.
<0
Data + numero/24
Data
Somma un numero di ore a una
data.
<0
’11-LUG-94’
NEXT_DAY(data,’car’)
NEXT_DAY(’01-SET-95’, ‘VENERDI’)
95’
LAST_DAY(data)
LAST_DAY(’01-SET-95’)
’08-SET-
’30-SET-95’
ROUND(data [, fmt])
ROUND(’25-LUG-95’,’MONTH’)
RUND(’25-LUG-95’,’YEAR’)
TRUNC(data [, fmt])
TRUNC(’25-LUG-95’,’MONTH’)
TRUNC(’25-LUG-95’,’YEAR’)
’01-AGO-95’
’01-GEN-96’ O
’01-LUG-95’
’01-GEN-95’
45 45
Trova la data del giorno della settimana
specificato (‘car’) successivo alla data .
Individua la data dell’ultimo giorno del mese
relativo alla data specificata.
Restituisce la data arrotondata all’unità
specificata nel modello del formato fmt. Se tale
modello non viene specificato, la data viene
troncata al giorno più vicino.
Restituisce la data con la parte del giorno
troncata all’unità specificata dal modello del
formato fmt. Se tale modello non viene
specificato, la data viene troncata al giorno più
vicino.
46 46
Conversione esplicita di tipi di dati
Conversione implicita di tipi di dati
Funzione
Descrizione
TO_CHAR(numero|data,[f
mt],[nlsparams])
Converte un valore numerico o di data nella stringa di caratteri VARCHAR2 con
il modello di formato fmt.
Conversione numerica
Il parametro nlsparams specifica i seguenti caratteri, restituiti da elementi in
formato numerico:
carattere decimale
Separatore di gruppo
Simbolo di valuta locale
Simbolo di valuta internazionale
Se nlsparams o qualsiasi altro parametro viene omesso, la funzione utilizza i
valori di parametro predefiniti per la sessione.
Conversione di data
Il parametro nlsparams specifica la lingua in cui vengono restituiti i nomi del
mese e del giorno e le abbreviazioni. Se tale parametro viene omesso, la
funzione utilizza la lingua predefinita per la sessione .
TO_NUMBER(car,
[fmt],[nlsparams])
Converte una stringa di caratteri contenente cifre in un numero nel formato
specificato dal modello facoltativo fmt. In questa funzione lo scopo del
parametro nlsparams è uguale a quello della funzione TO_CHAR per la
conversione numerica.
TO_DATE(car,
[fmt],[nlsparams])
Converte una stringa di caratteri che rappresenta una data in un valore di data
specificato in base a fmt. Se fmt è omesso, il formato è DD-MON-YY. In questa
funzione il ruolo del parametro nlsparams è uguale a quello della funzione
TO_CHAR per la conversione di data.
48 48
Per le assegnazioni, Oracle Server può
eseguire automaticamente le seguenti
conversioni:
Da
VARCHAR2 or CHAR
A
NUMBER
VARCHAR2 or CHAR
DATE
NUMBER
DATE
VARCHAR2
VARCHAR2
47 47
Funzione TO_CHAR con date
Elementi del modello del formato data
TO_CHAR (data, ‘fmt’ )
La funzione TO_CHAR consente di convertire una data dal formato
predefinito in un altro formato specificato dall’utente.
Il modello del formato deve essere racchiuso tra apici e distingue tra
maiuscole e minuscole.
Il modello del formato può contenere qualsiasi elemento del formato
data valido.
Assicurarsi di separare il valore di data dal modello del formato con una
virgola.
I nomi dei giorni e dei mesi dell’output vengono automaticamente
riempiti con spazi vuoti.
Per eliminare gli spazi aggiunti o gli zeri iniziali, utilizzare l’elemento fm
di riempimento.
È possibile ridimensionare la larghezza con cui il campo carattere
risultante viene visualizzato tramite il comando COLUMN di SQL*Plus.
La colonna risultante ha una larghezza predefinita di 80 caratteri.
Elemento
Descrizione
YYYY
Anno completo in cifre
YEAR
Anno iscritto per esteso
MM
Mese espresso con due cifre
MONTH
Nome completo del mese
DY
Abbreviazione di tre lettere per il
giorno della settimana
DAY
Nome completo del giorno
49 49
Formato data RR
Funzione TO_CHAR con numeri
Il formato data RR è simile all’elemento YY, ma consente di specificare
secoli diversi.utilizzando l’elemento del formato data RR al posto di YY, il
secolo del valore restituito varia in base alle due cifre dell’anno specificato
e alle due cifre dell’anno corrente.
Se le due cifre dell’anno corrente sono:
0 – 49
Se le 2 cifre
dell’anno
specificato
sono:
50 50
50 – 99
TO_CHAR (numero, ‘fmt’ )
Utilizzare questi formati con la funzione TO_CHAR per visualizzare
valori numerici come stringhe di caratteri:
Elemento
0 – 49
La data restituita è nel secolo
corrente
La data restituita è nel secolo
successivo a quello corrente
50 - 99
La data restituita è nel secolo
precedente a quello corrente
La data restituita è nel secolo
corrente
Descrizione
9
Rappresenta un numero
0
Forza la visualizzazione di uno zero
$
Inserisce il simbolo di dollaro mobile
Anno corrente
Data specificata
Formato RR
Formato YY
€
Utilizza il simbolo della valuta locale mobile
1994
27-OTT-95
1995
1995
.
Visualizza il separatore decimale
1994
27-OTT-17
2017
1917
2002
27-OTT-17
2017
2017
,
Visualizza il separatore di migliaia
51 51
52 52
Funzione NVL
Funzioni TO_NUMBER e TO_DATE
Converte un valore nullo in un valore reale.
È possibile utilizzare i tipi di dati data, carattere e numero.
I tipi di dati interessati dalla conversione devono corrispondere.
Convertire una stringa di caratteri in un formato
numerico tramite la funzione TO_NUMBER.
TO_NUMBER (car , [‘fmt’ ])
Convertire una stringa di caratteri in un formato data
tramite la funzione TO_DATE.
TO_DATE (car , [‘fmt’ ])
Tipi di dati
Esempi di conversione
NUMBER
NVL (colonna_numero, 9)
DATE
NVL (colonna_data, ’01-GEN-95’)
CHAR o
VARCHAR2
NVL (colonna_carattere, ‘Unavailable’)
Utilizzo della funzione NVL:
SQL> SELECT nome, salario, commissione, (salario*12)+NVL(commissione, 0)
FROM impiegati ;
53 53
54 54
Funzioni nidificate
Funzione DECODE
La funzione DECODE decodifica un’espressione in modo analogo alla logica di
IF – THEN – ELSE utilizzata in vari linguaggi. L’espressione viene decodificata dopo
essere stata confrontata con ciascun valore di ricerca. Se l’espressione corrisponde
a
Ricerca viene restituito il risultato. Se il valore predefinito viene omesso, nelle
posizioni in cui il valore ricercato non corrisponde ad alcun risultato viene restituito
un valore nullo.
DECODE(col/espressione, ricerca1, risultato1 [, ricerca2, risultato2, ….,] [,predefinito])
Le funzioni che agiscono su una sola riga possono essere
nidificate fino a un numero illimitato di livelli.
Le funzioni nidificate sono eseguite dal livello più interno al
livello più esterno.
F3 (F2 (F1
(col, arg1), arg2 ),
arg3 )
Utilizzo della funzione DECODE:
Fase 1 = Risultato 1
SQL> SELECT mansione, salario,
2
DECODE(mansione, ‘ANALYST’, SALARIO*1.1,
3
‘CLERK’,
SALARIO*1.15,
4
‘MANAGER’, SALARIO*1.20,
5
SALARIO)
SALARIO_INCREMENTATO
6 FROM impiegati ;
Fase 2 = Risultato 2
Fase 3 = Risultato 3
55 55
Esempio funzioni nidificate:
SQL> SELECT nome, NVL (TO_CHAR (superiore), ‘No Manager’)
2 FROM impiegati
3 WHERE superiore IS NULL ;
56 56
Definizione di join
L’operazione di join consente di eseguire query su dati
contenuti in più tabelle.
SELECT tabella1.colonna, tabella2.colonna
FROM tabella1, tabella2
WHERE tabella1.colonna1 = tabella2.colonna2
Visualizzazione di dati
contenuti in più tabelle
La condizione di join deve essere inserita nella clausola
WHERE.
Se lo stesso nome di colonna appare in più tabelle,
anteporre il nome della tabella a quello della colonna.
57 57
Prodotto cartesiano
Un
58 58
Tipi di join
prodotto si definisce cartesiano se:
I due tipi principali di join sono:
Equijoins
Non-equijoins
– Una condizione di join viene omessa
– Una condizione di join non è valida
– Tutte le righe della prima tabella vengono
unite tramite join a tutte le righe della
seconda tabella
evitare di ottenere un prodotto
cartesiano, inserire sempre una
condizione di join valida nella clausola
WHERE
Altri metodi di join sono:
Outer joins
Self joins
Per
59 59
60 60
Recupero di record tramite
equijoin
Identificazione di nomi di colonna
ambigui
Anteporre
SELECT matricola,nome, impiegati.cod_dipartimento,
localita
FROM impiegati, dipartimenti
WHERE impiegati.cod_dipartimento =
dipartimenti.cod_dipartimento
La clausola SELECT specifica i nomi di colonna da
recuperare
La clausola FROM specifica le due tabelle da cui ricavare
i dati
La clausola WHERE specifica il metodo di join delle due
tabelle
il nome della tabella ai nomi di
colonna presenti in più tabelle
Per ottenere prestazioni ottimali,
aggiungere il nome della tabella sotto
forma di prefisso
Assegnare alias alle colonne che hanno lo
stesso nome ma che risiedono in tabelle
diverse
61 61
Recupero di record tramite nonequijoin
Utilizzo degli alias di tabella
62 62
Utilizzare gli alias di tabella per semplificare
le query
SELECT i.nome, i.salario, l.livello
FROM impiegati i, liv_salario
WHERE i.salario
BETWEEN l.sal_min AND l.sal_max;
SELECT i.matricola,i.nome, i.cod_dipartimento,
d.localita
FROM impiegati i, dipartimenti d
WHERE i.cod_dipartimento = d.cod_dipartimento
63 63
64 64
Outer join
Self join
L’outer
join si utilizza per visualizzare le
righe che di solito non soddisfano la
condizione di join
L’operatore di outer join è il segno ‘+’
Talvolta è necessario unire tramite join una
tabella a se stessa
SELECT a.colonna, b.colonna
FROM tabella a, tabella b
WHERE a.colonna1 = b.colonna2
SELECT tabella1.colonna, tabella2.colonna
FROM tabella1, tabella2
WHERE tabella1.colonna1 = tabella2.colonna2(+)
65 65
66 66
Tipi di funzioni di gruppo
Aggregazione di dati tramite
funzioni di gruppo
67 67
AVG([DISTINCT] n )
Valore medio di n, i valori nulli
vengono ignorati
COUNT({*|[DISTINCT] espr})
Numero di righe, dove espr
restituisce un valore diverso da nullo
MAX([DISTINCT] espr)
Valore massimo di espr; i valori nulli
vengono ignorati
MIN([DISTINCT] espr)
Valore minimo di espr; i valori nulli
vengono ignorati
STDDEV([DISTINCT] n)
Deviazione standard di n, i valori
nulli vengono ignorati
SUM([DISTINCT] n)
Somma di n, i valori nulli vengono
ignorati
VARIANCE([DISTINCT] n)
Varianza di n, i valori nulli vengono
ignorati
68 68
Creazione di gruppi di dati:
clausola GROUP BY
Utilizzo della clausola GROUP
BY su più colonne
Per dividere le righe di una tabella in gruppi più
piccoli, utilizzare la clausola GROUP BY
SQL>SELECT cod_dipartimento, mansione, sum(salario)
FROM impiegati
GROUP BY cod_dipartimento, mansione;
COD_DIPARTIMENTO MANSIONE SUM(SALARIO)
10 IMPIEGATO
1300
10 DIRIGENTE
2450
10 PRESIDENTE
5000
20 ANALISTA
6000
20 IMPIEGATO
1900
SELECT colonna,funzione_gruppo(colonna)
FROM tabella
[ WHERE condizione ]
[ GROUP BY espressione_group_by ]
[ ORDER BY colonna ]
9 rows selected
69 69
Query non valide con le funzioni
di gruppo
SQL>SELECT cod_dipartimento, COUNT(nome)
FROM impiegati;
E
R
R
A
T
A
Le colonne o le espressioni nella lista dopo l’istruzione SELECT che non sono funzioni aggregate devono
essere inserite nella clausola GROUP BY.
SQL>SELECT cod_dipartimento, AVG(salario)
FROM impiegati
WHERE AVG(salario) > 2000
GROUP BY cod_dipartimento;
E
R
R
A
T
A
70 70
Esclusione dei risultati di
raggruppamento: clausola HAVING
La clausola HAVING consente di limitare i gruppi
Le righe vengono raggruppate.
Viene applicata la funzione di gruppo.
Vengono visualizzati i gruppi che soddisfano le condizioni della clausola
HAVING
SELECT colonna,funzione_gruppo
FROM tabella
[WHERE condizione]
[GROUP BY espressione_group_by]
[HAVING condizione_gruppo]
[ORDER BY colonna];
•Non è possibile utilizzare la clausola WHERE per limitare i gruppi
71 71
72 72
Utilizzo della clausola HAVING
SQL>SELECT cod_dipartimento, MAX(salario)
FROM impiegati
GROUP BY cod_dipartimento
HAVING MAX(salario) > 2000;
Subquery
COD_DIPARTIMENTO MAX(SALARIO)
10
5000
20
3000
73 73
Subquery
74 74
Utilizzo di una subquery
SELECT lista_select
FROM tabella
WHERE espr operatore
(SELECT lista_select
FROM tabella);
SELECT nome
FROM impiegati
WHERE salario >
(SELECT salario
FROM impiegati
WHERE matricola = 7566);
La subquery (query interna) viene eseguita prima della query principale.
Il risultato della subquery viene utilizzato dalla query principale(query esterna)
NOME
--------------BIANCHI
ROSSI
VERDI
E’ possibile inserire subquery nelle clausole SQL elencate di seguito:
Clausola WHERE;
Clausola HAVING;
Clausola FROM.
75 75
76 76
Istruzioni per l’ utilizzo delle
subquery
Racchiudere le subquery tra parentesi.
Inserire le subquery a destra dell’operatore di
confronto.
Non aggiungere una clausola ORDER BY alla
subquery.
Utilizzare operatori che agiscono su una sola
riga con subquery che restituiscono una sola
riga.
Utilizzare operatori che agiscono su più righe
con subquery che restituiscono più righe.
77 77
Subquery che restituiscono una
sola riga
Restituiscono una sola riga.
Utilizzano operatori di confronto che agiscono su una
sola riga
Operatore
Significato
=
Uguale a
>
Maggiore di
>=
Maggiore o uguale a
<
Minore di
<=
Minore o uguale a
<>
Diverso da
78 78
Subquery che restituiscono più
righe
Restituiscono più righe.
Utilizzano operatori di confronto che agiscono su più righe.
Operatore
Significato
IN
Uguale a qualsiasi membro nella lista
ANY
Confronta un valore con tutti i valori
restituiti dalla subquery
ALL
Confronta un valore con tutti i valori
restituiti dalla subquery
Subquery che restituiscono più
colonne
79 79
80 80
Subquery che restituiscono più
colonne
Le subquery che restituiscono più colonne consentono di combinare
condizioni WHERE duplicate in un’unica clausola WHERE.
Utilizzo di subquery che
restituiscono più colonne
Visualizzazione degli identificativi dell’ordine, del prodotto e della quantità di
articoli contenuti nella tabella ARTICOLI che corrispondono all’identificativo del
prodotto e alla quantità di un articolo presente nell’ordine 605
SELECT colonna, colonna, …
FROM tabella
WHERE (colonna, colonna, …) IN
(SELECT colonna, colonna, …
FROM tabella
WHERE condizione);
SELECT id_ordine, id_prodotto, qta
FROM articoli
WHERE (id_prodotto, qta) IN
(SELECT id_prodotto, qta
FROM articoli
WHERE id_ordine = 605)
AND id_ordine <> 605;
81 81
82 82
Istruzione INSERT
Aggiungere
nuove righe alla tabella
tramite l’istruzione INSERT.
Manipolazione di Dati
INSERT INTO tabella [(colonna[, colonna…])]
VALUES
(valore [, valore…]) ;
Linguaggio DML
(Data Manipulation Language)
Se
83 83
si utilizza la sintassi descritta, nella
tabella viene inserita solo una riga alla
volta.
84 84
Istruzione UPDATE
Istruzione DELETE
Modificare le righe esistenti tramite l’istruzione UPDATE.
Se necessario, è possibile aggiornare più righe alla
volta.
E’
possibile eliminare righe esistenti da
una tabella tramite l’istruzione DELETE.
DELETE [FROM] tabella
[WHERE
condizione] ;
UPDATE tabella
SET
colonna = valore [,colonna = valore, …]
[WHERE condizione] ;
85 85
86 86
Elaborazione di transazioni
implicite
Controllo delle transazioni
Istruzione
Descrizione
COMMIT
Termina la transazione corrente
rendendo permanenti tutte le modifiche
apportate.
Contrassegna un savepoint all’interno
SAVEPOINT NOME
della transazione corrente.
ROLLBACK [TO SAVEPOINT
nome]
ROLLBACK termina la transazione
corrente annullando tutte le modifiche
apportate; ROLLBACK TO SAVEPOINT
ritorna al savepoint specificato della
transazione corrente, eliminando il
savepoint e le successive modifiche. Se
non si specifica quest’ultima clausola,
l’istruzione ROLLBACK annulla le
modifiche apportate nell’intera
transazione.
87 87
Ha luogo un commit automatico quando:
- viene eseguita un’istruzione DDL;
- viene eseguita un’istruzione DCL;
- SQL*Plus viene terminato secondo la normale
procedura,
senza eseguire in modo esplicito l’istruzione COMMIT o
ROLLBACK.
Ha luogo un rollback automatico quando SQL*Plus viene
terminato in modo anomalo o il sistema si blocca.
88 88
Stato dei dati prima dell’esecuzione
di COMMIT o ROLLBACK
Stato dei dati dopo l’esecuzione di
COMMIT
Le operazioni di manipolazione dei dati hanno effetto
principalmente sul buffer del database; di conseguenza, è
possibile ripristinare lo stato precedente dei dati.
L’utente corrente può controllare i risultati delle operazioni di
manipolazione dei dati eseguendo query sulle tabelle.
Gli altri utenti non hanno accesso ai risultati delle operazioni di
manipolazione dei dati gestite dall’utente corrente. Oracle
Server garantisce che vi sia uniformità di lettura in modo che
tutti gli utenti visualizzino lo stato dei dati al momento dell’ultimo
commit.
Le righe interessate dall’operazione sono bloccate e gli altri
utenti non possono modificare i dati in esse contenuti.
Le modifiche apportate ai dati diventano
permanenti nel database.
Non è possibile ripristinare lo stato precedente
dei dati.
Tutti gli utenti possono visualizzare lo stato
della transazione.
Le righe interessate dall’operazione vengono
sbloccate e possono essere modificate di
nuovo da altri utenti.
Tutti i savepoint vengono cancellati.
89 89
90 90
Stato dei dati dopo l’esecuzione di
ROLLBACK
Utilizzare l’istruzione ROLLBACK per annullare tutte le
modifiche non ancora permanenti.
Le modifiche apportate vengono annullate.
Viene ripristinato lo stato precedente dei dati.
Le righe interessate dall’operazione vengono sbloccate.
SQL> DELETE FROM
14 righe eliminate.
SQL> ROLLBACK;
Rollback completato.
Rollback delle modifiche in
corrispondenza di un contrassegno
Utilizzare l’istruzione ROLLBACK TO SAVEPOINT per
eseguire il rollback delle modifiche apportate fino a quel
contrassegno.
Inserire un contrassegno nella transazione corrente
tramite l’istruzione SAVEPOINT.
SQL> UPDATE …
SQL>SAVEPOINT update_done;
Savepoint creato.
SQL>INSERT …
SQL>ROLLBACK TO update_done;
Rollback completato
impiegati;
91 91
92 92
Lock
I lock di Oracle:
Impediscono il verificarsi di interazioni conflittuali tra transazioni
simultanee;
Non richiedono l’intervento dell’utente;
Utilizzano automaticamente il livello più basso di limitazione;
Sono validi solo per la durata della transazione:
Sono disponibili in due modalità:
- lock esclusivo impedisce la condivisione di una risorsa. La prima
transazione che applica un lock esclusivo a una risorsa è la sola in
grado di modificarla finchè il lock non viene rilasciato;
- lock di condivisione consente la condivisione di una risorsa.
E’ possibile condividere dati tra più utenti che vi accedono in lettura
applicando lock condivisi che impediscano la modifica simultanea
dei dati (operazione per cui è necessario un lock esclusivo). La
stessa
risorsa accetta blocchi condivisi da più transazioni.
Creazione e gestione di
tabelle
93 93
94 94
Convenzioni per la denominazione
Oggetti di database
Oggetto
Descrizione
Tabella
Unità di memorizzazione di base,
composta da righe e colonne.
Vista
Dal punto di vista logico rappresenta set
secondari di dati da una o più tabelle.
Sequenza
Genera valori di chiave primaria.
Indice
Migliora le prestazioni di alcune query.
Sinonimo
Assegna nomi alternativi agli oggetti.
95 95
Per assegnare nomi alle colonne e alle tabelle dei database è necessario rispettare
le
seguenti convenzioni standard per la denominazione degli oggetti di database di
Oracle:
Il primo carattere dei nomi di tabelle e colonne deve essere una lettera e la
lunghezza dei nomi deve essere compresa tra 1 e 30 caratteri;
I nomi possono essere composti solo dai caratteri A – Z, a – z,
0 – 9, _ (sottolineatura), $ e # (caratteri legali, di cui si sconsiglia tuttavia
l’utilizzo);
Non è possibile assegnare un nome uguale a due oggetti di proprietà dello
stesso utente di Oracle Server;
I nomi non possono essere parole chiavi di Oracle Server;
Utilizzare nomi descrittivi per le tabelle e gli altri oggetti di database;
Assegnare lo stesso nome a un oggetto che compare in più tabelle. La colonna
contenente il numero di dipartimento, ad esempio, è denominata
COD_DIPARTIMENTO sia nella tabella IMPIEGATI sia nella tabella
DIPARTIMENTI.
96 96
Istruzione CREATE TABLE
Riferimenti a tabelle di altri utenti
È necessario disporre di:
- un privilegio CREATE TABLE
- un’area di memorizzazione
CREATE [GLOBAL TEMPORARY] TABLE [schema.]tabella
(colonna tipodati [DEFAULT espr] [, …]) ;
È necessario specificare :
- il nome della tabella
- il nome, il tipo di dati e le dimensioni della
colonna.
97 97
98 98
Tabelle nei database Oracle
Creazione di tabelle
Creare una tabella.
SQL> CREATE TABLE dipartimenti
2
(cod_dipartimento
NUMBER(2),
3
nome_dipartimento VARCHAR2(14),
4
localita
VARCHAR2(13));
Confermare la creazione della tabella.
SQL> DESC[RIBE] dipartimenti
Name
-----------------------------COD_DIPARTIMENTO
NOME_DIPARTIMENTO
LOCALITA
Null? Type
------- ----------NUMBER(2)
VARCHAR2(14)
VARCHAR2(13)
Uno schema è un insieme di oggetti. Gli
oggetti dello schema costituiscono le strutture
logiche che fanno direttamente riferimento ai
dati contenuti in un database e comprendono
tabelle, viste, sinonimi, sequenze, stored
procedure, indici, cluster e collegamenti a
database.
Le tabelle di proprietà di altri utenti non fanno
parte dello schema del proprietario.
È necessario far precedere il nome della
tabella dal nome del proprietario.
99 99
Tabelle dell’utente
- insieme di tabelle create e gestite dall’utente
- contengono informazioni relative all’utente
Dizionario dati
- insieme di tabelle create e gestite da Oracle Server
- contengono informazioni relative al database
- le tabelle del dizionario dati sono di proprietà dell’utente SYS
- le viste del dizionario dati si suddividono in quattro categorie, ognuna delle
quali dispone di un prefisso distinto che ne riflette la funzione
Prefisso
Descrizione
USER_
Queste viste contengono informazioni sull’oggetto di proprietà dell’utente.
ALL_
Queste viste contengono informazioni su tutte le tabelle (Object Table e
tabelle relazionali) accessibili all’utente.
DBA_
Queste viste sono limitate e sono accessibili solo agli utenti a cui è stato
assegnato il ruolo di amministratore del database.
V$_
Queste viste contengono informazioni sulle viste delle prestazioni
dinamiche, sulle prestazioni del database server e sui lock.
100100
Tipi di dati
Creazione di una tabella tramite una subquery
Tipi di dati
Descrizione
VARCHAR2
(dimensione)
Dati di tipo carattere a lunghezza variabile (è necessario specificare una
dimensione massima: la dimensione minima e predefinita è 1, la dimensione
massima è 4000)
Dati di tipo carattere a lunghezza fissa con la lunghezza in byte pari a dimensione
(è necessario specificare una dimensione massima: la dimensione minima e
predefinita è 1, la dimensione massima è 2000)
CHAR
(dimensione)
NUMBER(p,s
)
Numero con precisione p e scala s (la precisione corrisponde al numero totale di
cifre decimali e la scala corrisponde al numero di cifre a destra del separatore
decimale. Il valore della precisione può essere compreso tra 1 e 38, mentre
quello della scala tra -84 e 127)
DATE
LONG
Valori di data e ora compresi tra il 1° gennaio 4 712 a.C. e il 31 dicembre 9999
d.C.
Dati di tipo carattere a lunghezza variabile fino a un massimo di 2 GB
CLOB
Dati di tipo carattere a byte singolo fino a un massimo di 4 GB
RAW
(dimensione)
Dati di tipo RAW con la lunghezza pari a dimensione (è necessario specificare
una dimensione massima: la dimensione massima è 2000)
LONG RAW
Dati di tipo RAW a lunghezza variabile fino a un massimo di 2 GB
BLOB
Dati binari fino a un massimo di 4 GB
BFILE
Dati binari memorizzati in un file esterno fino a un massimo di 4 GB
CREATE TABLE table
[(colonna, colonna …)]
AS subquery ;
102102
Istruzione ALTER TABLE
ALTER TABLE tabella
DROP UNUSED COLUMNS;
Eliminazione di una tabella
ALTER TABLE tabella
DROP COLUMN colonna ;
ALTER TABLE tabella
SET UNUSED (colonna);
OR
ALTER TABLE tabella
SET UNUSED COLUMN colonna;
Il numero di colonne specificato deve corrispondere al numero di
colonne della subquery.
Definire le colonne assegnandovi un nome e valori predefiniti.
La tabella verrà creata con i nomi di colonna specificati e le righe
recuperate tramite l’istruzione SELECT verranno inserite nella tabella.
La definizione di colonna può contenere solo il nome di colonna e il
valore predefinito.
Se i valori della colonna vengono specificati, il numero di colonne deve
corrispondere a quello indicato nella lista dopo l’istruzione SELECT
della subquery.
Se i valori della colonna non vengono specificati, i nomi di colonna
della tabella saranno uguali ai nomi di colonna indicati nella subquery.
101101
Utilizzare l’istruzione ALTER TABLE per:
Aggiungere una nuova colonna utilizzando la clausola ADD, la nuova colonna
diventa l’ultima colonna;
Modificare una colonna esistente o definire un valore predefinito utilizzando la
clausola MODIFY, la modifica del valore predefinito ha effetto solo sulle
aggiunte effettuate dopo la modifica;
Eliminare le colonne non più necessarie dalla tabella utilizzando la clausola
DROP COLUMN, è irrilevante che le colonne contengano dati o meno, è
possibile eliminare solo una colonna alla volta, dopo la modifica la tabella deve
contenere almeno una colonna, non è possibile recuperare le colonne eliminate;
Contrassegnare una o più colonne che non vengono utilizzate utilizzando la
clausola SET UNUSED;
Eliminare le colonne contrassegnate come UNUSED utilizzando la clausola
DROP UNUSED.
ALTER TABLE table
ADD/MODIFY (colonna tipodati [DEFAULT
espr]
[, colonna tipodati]…) ;
Creare una tabella e inserire nuove righe tramite la combinazione
dell’istruzione CREATE TABLE e l’opzione AS subquery.
103103
Tutti i dati e la struttura della tabella vengono
eliminati.
Tutte le transazioni in sospeso vengono rese
permanenti.
Tutti gli indici vengono eliminati.
Le viste e i sinonimi continuano a essere
memorizzati, ma non sono più validi.
Solo l’autore della tabella o gli utenti che dispongono
del privilegio DROP ANY TABLE possono eliminare
una tabella.
TABLE eseguire
tabella ; il rollback di questa
NonDROP
è possibile
104104
istruzione.
Troncamento di una tabella
Modifica del nome di un oggetto
L’istruzione TRUNCATE TABLE consente di:
- eliminare tutte le righe da una tabella;
- liberare lo spazio di memorizzazione utilizzato dalla
tabella.
Se si utilizza TRUNCATE, non è possibile effettuare il
rollback dell’eliminazione delle righe.
In alternativa, è possibile eliminare le righe tramite
l’istruzione DELETE, ma in tal caso non libera spazio di
memorizzazione.
Per troncare una tabella è necessario essere proprietari
o disporre dei privilegi di sistema.
L’istruzione
RENAME consente di
modificare il nome di tabelle, viste,
sequenze o sinonimi.
TRUNCATE TABLE tabella
;
RENAME nome_precedente TO
nome_nuovo
È
necessario essere proprietari
dell’oggetto.
105105
106106
Aggiunta di commenti ad una tabella
Per aggiungere commenti a una tabella o a una
colonna, utilizzare l’istruzione COMMENT.
COMMENT ON TABLE tabella | COLUMN
tabella.colonna IS ‘ testo ’ ;
Per eliminare un commento dal database, sostituire il
teso con una stringa vuota ( ‘ ’ ).
È possibile visualizzare i commenti tramite le viste del
dizionario dati:
• ALL_COL_COMMENTS
• USER_ COL_COMMENTS
• ALL_TAB_ COMMENTS
• USER_TAB_COMMENT
Inclusione di vincoli
107107
108108
Definizione dei vincoli
Istruzioni per l’utilizzo dei vincoli
I vincoli applicano le regole a livello di tabella.
I vincoli impediscono la cancellazione di una tabella
in caso di dipendenze.
In Oracle è possibile utilizzare i seguenti tipi di
vincoli:
Vincolo
Descrizione
NOT NULL
Indica che la colonna non può contenere valori
nulli
Indica una colonna o una combinazione di
UNIQUE
colonne i cui valori devono essere univoci per tutte
le regole della tabella
PRIMARY KEY
Identifica in modo univoco ogni riga della tabella
FOREIGN KEY
Definisce e applica una relazione di chiave
esterna tra la colonna e una colonna della tabella
di riferimento
Indica
una condizione che deve risultare vera
CHECK
Denominare un vincolo oppure Oracle Server genererà
un nome utilizzando il formato SYS_Cn.
Creare un vincolo:
- contemporaneamente alla creazione della tabella;
- dopo che la tabella è stata creata.
Definire un vincolo a livello di colonna o di tabella
Visualizzare un vincolo nel dizionario dati consultando
la tabella USER_CONSTRAINTS.
Visualizzare le colonne associate ai nomi dei vincoli
nella vista USER_CONS_COLUMNS.
109109
Definizione di vincoli
Parole chiave del vincolo
FOREIGN KEY
CREATE TABLE [schema.]tabella
(colonna tipodati [DEFAULT espr]
[vincolo_tabella] [, …] );
I vincoli in genere sono creati contemporaneamente alla tabella. È comunque possibile
aggiungerli alla tabella dopo che è stata creata e anche disabilitarli temporaneamente.
I vincoli possono essere definiti a uno dei due livelli:
Livello di vincolo
Descrizione
Colonna
Fa riferimento ad una singola colonna e viene definito all’interno di una specifica per
la colonna di proprietà; può definire qualsiasi tipo di integrità.
Tabella
Fa riferimento a una o più colonne e viene definita separatamente dalle definizioni
delle colonne nella tabella; può definire qualsiasi vincolo ad eccezione di NOT NULL.
Livello del vincolo di colonna
Livello del vincolo di tabella
column [CONSTRAINT nome_vincolo] tipo_vincolo,
colonna, …
[CONSTRAINT nome_vincolo] tipo_vincolo
(colonna, …),
110110
111111
La chiave esterna è definita nella tabella figlia, mentre la tabella
contenente la colonna a cui viene fatto riferimento è la tabella
padre. La chiave esterna viene definita mediante una
combinazione
delle seguenti parole chiavi:
FOREIGN KEY: definisce la colonna nella tabella figlia a livello
del vincolo di tabella.
REFERENCE: identifica la tabella e la colonna nella tabella
padre.
ON DELETE CASCADE:consente di effettuare cancellazioni
nella tabella padre e la cancellazione di righe dipendenti nella
tabella figlia, senza questa opzione la riga nella tabella padre
non può essere cancellata se nella tabella figlia viene fatto
112112
riferimento a essa.
Istruzioni con i vincoli
Vincolo CHECK
Definisce una condizione che ogni riga deve soddisfare.
Espressioni non consentite:
- riferimenti a pseudocolonne CURRVAL, NEXTVAL, LEVEL e
ROWNUM;
- chiamate a funzioni SYSDATE, UID, USER e USERENV;
- query che fanno riferimento ad altri valori in altre righe.
Una colonna singola può avere più vincoli di CHECK che fanno
riferimento alla colonna nella sua definizione. Il numero di vincoli
CHECK che è possibile definire in una colonna è illimitato.
È possibile definire vincoli CHECK a livello di colonna o a livello di
tabella.
… , cod_dipartimento NUMBER(2),
CONSTRAINT imp_cod_dipartimento_ck
CHECK (COD_DIPARTIMENTO BETWEEN 10 AND 99) , 113
113
…
È possibile aggiungere, cancellare,abilitare o disabilitare un
vincolo, ma non è possibile modificarne la struttura.
È possibile aggiungere un vincolo NOT NULL a una colonna
esistente mediante la clausola MODIFY dell’istruzione ALTER
TABLE, è possibile definire una colonna NOT NULL solo se la
tabella non contiene righe, poiché non è possibile specificare
dati per righe esistenti contemporaneamente all’aggiunta della
colonna.
AGGIUNTA DI UN VINCOLO:
CANCELLARE UN VINCOLO:
ALTER TABLE tabella
ADD [CONSTRAINT vincolo] tipo (colonna)
;
ALTER TABLE tabella
DROP CONSTRAINT nome_vincolo ;
DISABILITARE UN VINCOLO:
ABILITARE UN VINCOLO:
ALTER TABLE tabella
DISABLE CONSTRAINT nome_vincolo ;
ALTER TABLE tabella
ENABLE CONSTRAINT nome_vincolo
;
114114
Vincoli con clausola CASCADE
CONSTRAINTS
La clausola CASCADE CONSTRAINTS è utilizzata
con la clausola DROP COLUMN.
Consente di cancellare tutti i vincoli di integrità
referenziale che fanno riferimento alle chiavi primarie
e univoche definite nelle colonne cancellate.
La clausola CASCADE CONSTRAINTS cancella
anche tutti i vincoli multicolonna definiti nelle colonne
cancellate.
Creazione di viste
ALTER TABLE tabella
DROP
nome_colonna CASCADE
CONSTRAINTS ;
115115
116116
Viste semplici e viste complesse
Finalità delle viste
Limitare l’accesso ai dati in quanto
consentono di visualizzare solo determinate
colonne della tabella.
Consentono agli utenti di eseguire query
semplici per recuperare i risultati di query più
complesse, ad esempio di eseguire query su
informazioni contenute in più tabelle anche
senza saper scrivere un’istruzione JOIN.
Garantiscono l’indipendenza dei dati per
utenti e programmi applicativi ad hoc. Una
sola vista può essere utilizzata per
recuperare dati da più tabelle.
Consentono a gruppi di utenti l’accesso ai
117117
dati secondo determinati criteri.
Le viste possono essere classificate in due tipi: semplici e complesse. La
differenza
fondamentale riguarda le operazioni DML (inserimento, aggiornamento ed
eliminazione).
Con vista semplice si intende una vista che:
- estrae i dati da una sola tabella;
- non contiene funzioni o gruppi di dati;
- può eseguire DML tramite la vista.
Con vista complessa si intende una vista che:
- estrae i dati da più tabelle;
- contiene funzioni o gruppi di dati;
- non sempre consente DML tramite la vista.
Caratteristica
Viste semplici
Numero di tabelle
Una
Viste complesse
Una o più
Contiene funzioni
No
Sì
Contiene gruppi di dati
No
Sì
DML tramite la vista
Sì
Non sempre
118118
Creazione di una vista
Incorporare una subquery nell’istruzione CREATE VIEW.
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW vista
[(alias [, alias] …)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT vincolo ]]
[WITH READ ONLY] ;
La subquery che definisce una vista non può contenere una sintassi
SELECT complessa che include join, gruppi e subquery.
La subquery che definisce una vista non può contenere una clausola
ORDER BY. La clausola ORDER BY viene specificata quando si
recuperano dati dalla vista.
Se non si specifica un nome di vincolo per una vista creata con CHECK
OPTION, il sistema assegna un nome predefinito nel formato SYS_Cn.
Mediante la clausola WITH CHECK OPTION è possibile garantire che
un’operazione DML sulla vista rientri nel dominio della stessa.
È possibile utilizzare l’opzione OR REPLACE per modificare la
definizione della vista senza eliminarla e ricrearla o concedere di nuovo i
privilegi sugli oggetti.
119119
Modifica e rimozione di una vista
Si può modificare una vista mediante la clausola CREATE OR
REPLACE VIEW
Quando vengono assegnati gli alias di colonna nella clausola
CREATE VIEW bisogna tenere a mente che gli alias sono
elencati nella stessa sequenza delle colonne nella subquery.
Utilizzare la clausola DROP VIEW per rimuovere una vista.
L’istruzione rimuove la definizione della vista dal database. La
rimozione di viste non ha alcun effetto sulle tabelle sulle quali è
basata la vista. Le viste o le applicazioni basate su viste rimosse
non saranno più valide. Solo l’autore o un utente con privilegio
DROP ANY VIEW può rimuovere una vista.
SINTASSI:
DROP VIEW vista;
120120
Viste inline
Analisi “Top – N”
vista inline si intende una subquery con
un alias (nome di correlazione) che può
essere utilizzata in un’istruzione SQL.
L’utilizzo di una vista inline è simile a quello
di una subquery specifica nella clausola
FROM della query principale.
Una vista inline non è un oggetto di schema.
Le query “Top – N” sono utili nelle situazioni in
cui è necessario visualizzare solo gli n record
superiori o inferiori da una tabella in base ad
una condizione. Il set di dati restituito può
essere utilizzato per ulteriori analisi. I set dei
valori massimi e minimi sono considerati query
“Top – N”.
Con
121121
122122
Controllo dell’accesso degli
utenti
Le funzioni di protezione del database di
Oracle Server consentono di:
Controllare l’accesso al database;
Concedere l’accesso a oggetti specifici del
database;
Confermare i privilegi concessi e quelli
ricevuti con il dizionario dati Oracle;
Creare sinonimi per gli oggetti del
database.
Controllo dell’accesso degli
utenti
123123
124124
Privilegi
Privilegi di sistema
Protezione del database:
Privilegi di sistema: consentono l’accesso al
database.
I privilegi di sistema disponibili per utenti e ruoli sono più di
80. Vengono normalmente concessi dall’amministratore del
database.
Privilegi tipici dell’amministratore di sistema:
– Protezione di sistema
– Protezione dei dati
Privilegi sugli oggetti: consentono di manipolare
il contenuto degli oggetti del database
Schema: insieme di oggetti, come tabelle, viste
e sequenze.
Privilegi di sistema
Operazioni autorizzate
CREATE USER
Creazione utenti Oracle
DROP USER
Cancellazione utenti
DROP ANY TABLE
Cancellazione di tabelle da qualunque schema
BACKUP ANY
TABLE
Backup di tabelle in qualunque schema tramite la utility di
esportazione
125125
Privilegi di sistema degli utenti
Una volta creato un utente, l’amministratore
può concedergli privilegi di sistema specifici
GRANT privilegio [,privilegio…] TO utente [, utente]
Privilegi tipici dell’amministratore di sistema:
Privilegi di sistema
Operazioni autorizzate
CREATE SESSION
Connessione al database
CREATE TABLE
Creazione di tabelle nel proprio schema
CREATE SEQUENCE
Creazione di sequenze nel proprio schema
CREATE VIEW
Creazione di viste nel proprio schema
CREATE PROCEDURE
Creazione di stored procedure,stored function o
package nel proprio schema
126126
Definizione di ruolo e concessione di privilegi
Un ruolo è un gruppo specifico di privilegi correlati
che possono essere concessi agli utenti.
Un utente può avere accesso a diversi ruoli e più
utenti possono essere assegnati allo stesso ruolo.
Una volta creato il ruolo il DBA può utilizzare
l’istruzione GRANT per assegnargli utenti e
privilegi.
SQL> CREATE ROLE superiore;
Role created.
SQL> GRANT create table, create view TO superiore;
Grant succeded.
127127
SQL> GRANT superiore to BLAKE, CLARK;
Grant succeded.
128128
Privilegi sugli oggetti
Conferma dei privilegi concessi
Tabella del dizionario dati
Descrizione
ROLE_SYS_PRIVS
Privilegi di sistema concessi ai ruoli
ROLE_TAB_PRIVS
Privilegi sulle tabelle concesse ai ruoli
USER_ROLE_PRIVS
Ruoli accessibili da parte dell’utente
USER_TAB_PRIVS_MADE
Privilegi concessi sugli oggetti dell’utente
USER_TAB_PRIVS_RECD
Privilegi sugli oggetti concessi all’utente
USER_COL_PRIVS_MADE
Privilegi concessi sulle colonne degli
oggetti dell’utente
USER_COL_PRIVS_RECD
Privilegi sugli oggetti concessi all’utente
relativamente a colonne specifiche
I privilegi sugli oggetti variano da oggetto a
oggetto.
Il proprietario di un oggetto dispone di tutti i
privilegi sull’oggetto.
Un proprietario può concedere privilegi
specifici sull’oggetto.
GRANT privilegio_oggetto [(colonne)]
ON
oggetto
TO
{utente|ruolo|PUBLIC}
[WITH GRANT OPTION];
129129
130130
Creazione di utenti
Modalità di revoca dei privilegi
sugli oggetti
Per revocare i privilegi concessi agli altri utenti
utilizzare l’istruzione REVOKE.
Verranno revocati anche i privilegi concessi ad
altri mediante WITH GRANT OPTION.
REVOKE { privilegio [, privilegio …] | ALL}
ON
oggetto
FROM { utente [, utente …] | ruolo |PUBLIC}
[CASCADE CONSTRAINTS] ;
L’amministratore del database crea gli
utenti tramite l’istruzione CREATE USER.
CREATE USER utente
IDENTIFIED BY password ;
SQL> CREATE USER scott
2 IDENTIFIED BY tiger ;
User created.
131131
132132
Cambio della password
L’amministratore del database crea un
account per ogni singolo utente e ne
inizializza la password.
Gli utenti a loro volta possono cambiare la
password con l’istruzione ALTER USER.
SQL> ALTER USER scott
2 IDENTIFIED BY lion ;
User altered.
133133