Sommario
DML - Data Manipulation Language
DDL - Data Definition Language
Il linguaggio SQL (Structured Query Language, linguaggio strutturato per le interrogazioni) e' l'insieme dei comandi che possono essere usati per accedere ai database relazionali, come ad esempio Oracle, il database piu' diffuso su macchine Unix. L'ANSI (American National Standard Institute) ha adottato l'SQL come il linguaggio standard per i database relazionali e, anche per questo motivo, e' diffuso in tutte le industrie, le universita' e le aziende di ogni parte del mondo.
I maggiori benefici risultanti dall'utilizzo del linguaggio SQL sono:
· L'SQL
e' un linguaggio ad alto livello: l'utente specifica cosa vuole ottenere,
il sistema si preoccupa di come ottenerlo.
· L'SQL puo'
elaborare piu' elementi insieme anziche' uno alla volta.
· Puo' essere
usato da tutti i livelli di utente, a partire dall'amministratore di sistema
per arrivare all'utente finale; e' potente ma i suoi comandi principali
possono essere imparati in poche ore.
· Il linguaggio
permette di eseguire diverse istruzioni come l'inserimento, la modifica,
la cancellazione e la selezione dei dati, la creazione, la modifica e
l'eliminazione di oggetti quali tabelle, viste, indici, utenti e cosi'
via. Una prerogativa importante dell'SQL consiste nel comprendere tutti
i comandi nello stesso linguaggio, anziche' ricorrere a piu' linguaggi
diversi.
· L'SQL e'
un linguaggio estremamente portabile: una applicazione scritta per un
DB basato su un suo "dialetto" SQL puo' essere portata senza nessuna o,
al piu', poche modifiche, in un altro database basato sull'SQL.
Il linguaggio SQL opera su una serie di oggetti che fanno parte del database. Tra questi i principali sono tabelle, indici, viste, ...
Le tabelle Oracle sono i principali oggetti per la gestione dei dati. Ciascuna tabella e' formata da una o piu' colonne. Naturalmente il tipo di dato presente nelle colonne e' un tipo di dato supportato da Oracle.
I comandi SQL possono essere classificati in 3 grosse categorie:
A seconda del database che si utilizza sono presenti diversi tipi di dato, anche se le caratteristiche di tali tipi sono molto simili tra loro; di seguito viene presentato un elenco dei tipi di dato ORACLE.
|
Tipo di dato |
Descrizione |
|
CHAR(n) |
(fino alla vers. 6.x): Stringa costituita al massimo da n caratteri. (vers. 7 e succ.): stringa costituita esattamente da n caratteri; se la lunghezza effettiva della stringa e' m < n, Oracle aggiunge in coda al record (n - m)caratteri blank in modo da rendere la lunghezza comunque di n caratteri. |
|
VARCHAR2(n) |
Stringa costituita al massimo da n caratteri. Non puo' comunque eccedere la lunghezza di 2000 caratteri. |
|
VARCHAR(n) |
Nella versione 7 e' analogo al VARCHAR2; si prevede che, nelle future versioni di Oracle, diventi un tipo di dato a se stante. |
|
LONG |
Puo' contenere stringhe lunghe fino a 2GB. E' utile ma ha diverse limitazioni( una tabella puo' contenere solo un campo LONG, non si possono fare ricerche su sottostringhe al suo interno, ecc...) |
|
NUMBER |
Dati di tipo numerico. Accetta numeri interi o decimali purche' il numero complessivo di cifre sia al massimo 38 |
|
NUMBER(p) |
Accetta solo numeri interi con un numero massimo di p cifre |
|
NUMBER(p,s) |
p = precision, num. max. cifre s = scale, num. max di cifre decimali. Se s e' negativo, il dato viene arrotondato a s cifre a sinistra del punto decimale: ad es. valore = 1234567.89 NUMBER(7,-2) ==> 1234600 |
|
DECIMAL(p,s) |
E' sinonimo di NUMBER(p,s) |
|
INTEGER |
Sinonimo di NUMBER(38). |
|
SMALLINT |
Sinonimo di NUMBER(38). |
|
DATE |
Usato per contenere informazioni su data e tempo, piu' precisamente su: · secolo E' possibile selezionare un campo DATE con numerosissimi formati. |
|
ROWID |
Tipo di dato particolare che e' restituito dalla pseudo-colonna ROWID che rappresenta l' indirizzo fisico del record. E' normalmente visualizzato in esadecimale. |
|
RAW(n) |
Stringa binaria della lunghezza specificata (max 2000 byte). puo' essere utilizzato per memorizzare su tabelle programmi applicativi in altri linguaggi immagini grafiche. |
|
LONG RAW |
Stringa binaria come RAW, con caratteristiche simili al tipo LONG. |
I comandi DML permettono di manipolare i dati nel DB; i comandi piu' importanti sono:
|
SELECT |
Seleziona dati da una o piu' tabelle |
|
DELETE |
Elimina i dati da una tabella secondo alcune condizioni. |
|
INSERT |
Inserisce nuovi dati in una tabella |
|
UPDATE |
Modifica i dati di una o piu' righe di una tabella |
E' importante sottolineare che questi comandi non hanno un effetto definitivo sui dati; per confermare o annullare del tutto una delle suddette operazioni, occorre utilizzare rispettivamente i comandi COMMIT e ROLLBACK, facenti parte della categoria di Controllo della Transazione.
Select
E' uno dei comandi piu' importanti del linguaggio SQL, usato per selezionare dati da una o piu' tabelle.
Sintassi:
SELECT [ALL|DISTINCT] { * | lista_colonne }
FROM lista_tabelle
[ WHERE condizione ]
[ CONNECT BY condizione [ START WITH condizione ] ]
[ GROUP BY lista_colonne [ HAVING condizione ] ]
[ {UNION|INTERSECT|MINUS} select ... ]
[ ORDER BY {lista_colonne|posizione} [ASC|DESC] ]
[ FOR UPDATE OF lista_colonne [NOWAIT] ]
Parametri:
*: tutte le colonne dell'oggetto.
ALL: restituisce tutte le righe selezionate.
DISTINCT: agisce su tutte le colonne della select, eliminando le combinazioni duplicate.
lista_colonne: una lista separata da virgole di colonne da selezionare.
lista_tabelle: una lista di tabelle o viste.
condizione: una lista booleana di condizioni usata per discriminare la ricerca delle righe.
Esempio:
SELECT c.firm_name, e.surname, e.name
FROM companies c, employees e
WHERE c.company_code = e.company_code
AND e.surname like 'SM%';
In questo esempio vengono selezionati la societa' e il nome degli impiegati con cognome che inizia per SM ...
Aggiunge righe a una tabella.
Sintassi:
INSERT INTO tabella [lista_colonne] {VALUES lista_valori|subquery}
Parametri:
tabella: nome della tabella in cui inserire le righe.
lista_colonne: lista delle colonne che devono essere coinvolte nell'inserimento.
lista_valori: lista di valori da inserire, nello stesso ordine delle colonne specificate oppure, se le colonne non sono specificate, nello stesso ordine di creazione delle colonne stesse.
subquery: il risultato della selezione sara' inserito nella tabella (vedi Esempio 3)
Esempi:
1.INSERT INTO singers
VALUES (1,'Springsteen','Bruce',2,3,'234,Madison Avenue,
Atlantic City, NJ');
2.INSERT INTO singers(employee_code, surname)
VALUES (1,'Springsteen');
3.INSERT INTO singers(surname, name)
SELECT first_name, second_name
FROM rockers WHERE first_name LIKE 'S%';
Elimina righe da una tabella o da una vista (solo se e' costruita su una sola tabella).
Sintassi:
DELETE [FROM] tabella [WHERE condizione]
Parametri:
tabella: il nome della tabella da cui eliminare le righe.
condizione: una lista booleana contenente i valori per cui eliminare le righe; se non viene specificata nessuna condizione, la DELETE eliminera' tutte le righe della tabella.
Esempio:
1.DELETE FROM cities
WHERE country_name = 'SPAIN';
2.DELETE FROM singers;
Modifica il contenuto di una tabella o di una vista (solo se e' costruita su una sola tabella).
Sintassi:
UPDATE tabella SET col = expr [,col = expr] [WHERE condizione]
Parametri:
tabella: il nome della tabella da modificare.
col: il nome della colonna.
expr: il valore da assegnare.
condizione: l'eventuale elenco delle condizioni per cui applicare la modifica.
Esempi:
1.UPDATE records
SET support_type = 'CD'
WHERE to_char(date,'YYYY') > '1990';
2.UPDATE cities
SET dummy=0
WHERE dummy IS NULL;
I comandi facenti parte del DDL permettono di creare, modificare ed eliminare oggetti e modificare la struttura fisica del DB. I comandi piu' usati del DDL sono:
|
ALTER INDEX |
Modifica alcuni parametri dell' indice |
|
ALTER TABLE |
Modifica la struttura di una tabella |
|
ALTER VIEW |
Modifica una vista |
|
CREATE INDEX |
Crea un indice |
|
CREATE TABLE |
Crea una tabella |
|
CREATE VIEW |
Crea una vista |
|
DROP INDEX |
Elimina un indice |
|
DROP TABLE |
Elimina una tabella |
|
DROP VIEW |
Elimina una vista |
|
RENAME |
Rinomina un oggetto |
|
TRUNCATE |
Cancella irrimediabilmente tutte le righe di una tabella |
Questi comandi, vista la loro potenza e pericolosita', sono spesso resi eseguibili a pochi utenti, tra cui il Data Base Administrator (DBA), che si occupa di installare il software del database, di creare e attivare il DB, di farne periodicamente il backup, di migliorare le performance del DB, di creare e gestire gli utenti che vi accedono e, in generale, di supervisionare l'andamento della base dati.
Modifica alcuni parametri dell' indice.
Sintassi:
ALTER INDEX indice STORAGE clausola
Parametri:
indice: il nome dell' indice da creare.
Esempio:
ALTER INDEX i_utenti STORAGE(NEXT 200K);
Modifica la struttura di una tabella; usando Alter Table e' possibile:
· Aggiungere e modificare una colonna.
· Aggiungere un constraint.
· Modificare i parametri di dimensionamento della tabella.
· Abilitare o disabilitare i constraint.
Sintassi:
ALTER TABLE tabella
[ADD {colonna tipo_dato[, ... ] | constraint definizione[, ... ]}]
[MODIFY colonna tipo_dato[, ... ] ]
[DROP CONSTRAINT constraint]
[PCTFREE intero] [PCTUSED intero] [STORAGE clausola]
Parametri:
tabella: il nome della tabella che deve essere modificata.
colonna: il nome della colonna che deve essere modificata o aggiunta.
costraint: il nome del costraint che deve essere aggiunta o tolto.
Esempi:
1.ALTER TABLE cantanti add(categoria decimal(2));
2.ALTER TABLE cantanti modify(cognome varchar2(20));
La clausola Storage permette di impostare le caratteristiche relative al dimensionamento di oggetti nel database come tabelle, indici, segmenti di rollback, snapshot e tablespace.
La sintassi corretta per la clausola di storage e':
STORAGE( INITIAL intero <K/M>
NEXT intero <K/M>
MINEXTENTS intero
MAXEXTENTS intero
PCTINCREASE intero )
INITIAL: specifica la dimensione in byte del primo extent dell'oggetto; il valore minimo e' di 2 data block, il default e' di 5 data block, mentre il valore massimo dipende dal sistema operativo.
NEXT: indica la dimensione in byte del successivo extent che deve essere allocato; il valore minimo e' di 1 data block, quello di default e' di 5, mentre il valore massimo dipende dal sistema operativo.
PCTINCREASE: specifica la percentuale di crescita di un extent rispetto al precedente; il valore di default e' di 50%; se si specifica 0 significa che tutti gli extent successivi al primo avranno la stessa dimensione. Non e' ammesso specificare un valore di PCTINCREASE per un segmento di rollback, il cui valore fisso e' 0.
MINEXTENTS: indica il totale di extent allocati quando il segmento e' creato; questo parametro permette di allocare molto spazio quando si crea un oggetto, anche se lo spazio disponibile non e' contiguo; il valore minimo e' 1.
MAXEXTENTS: indica il numero massimo di extent allocabili alla crescita dell'oggetto.
Ricompila una vista esistente.
Sintassi:
ALTER VIEW vista COMPILE
Parametri:
vista: il nome della vista da modificare.
Crea un nuovo indice su specifiche colonne di una tabella.
Sintassi:
CREATE [UNIQUE] INDEX indice
ON tabella (colonna1[,colonna2 ... ])
[TABLESPACE SYSTEM|tablespace]
[STORAGE clausola] [PCTFREE 10|n]
Parametri:
indice: il nome dell' indice da creare.
tabella: il nome della tabella da indicizzare
colonna: il nome della colonna che entra a far parte dell' indice.
tablespace: il nome del tablespace in cui si crea l' indice.
Esempio:
CREATE INDEX i_utenti ON utenti(codice)
TABLESPACE IDX STORAGE(INITIAL 1M NEXT 100K);
Crea una nuova tabella, specificando la definizione delle colonne, il tablespace su cui crearla, le caratteristiche dello storage ed, eventualmente, permette di riempire tale tabella con il risultato di una interrogazione da altra tabella.
Sintassi:
CREATE TABLE tabella
(colonna1 tipo_dato DEFAULT expr column_constraint[, ... ])
[table_constraint]
[PCTFREE 10|n] [PCTUSED 40|n] [STORAGE clausola]
[TABLESPACE SYSTEM|tablespace]
[AS subquery]
Parametri:
tabella: il nome della tabella da creare.
colonna: il nome della colonna.
tablespace: il nome del tablespace in cui si crea la tabella.
subquery: una selezione opzionale che permette di riempire la tabella nel momento della creazione.
Esempio:
CREATE TABLE utenti
(codice decimal(5), cognome varchar2(18), nome varchar2(18));
Crea una "fotografia dinamica" di una particolare selezione da una o piu' tabelle; quando il contenuto della tabella cambia, la vista cambia.
Sintassi:
CREATE [OR REPLACE] VIEW vista AS subquery
[WITH CHECK OPTION]
Parametri:
OR REPLACE: sostituisce la vista se questa gia' esiste.
nome_vista: il nome della vista da creare.
subquery: la selezione che definisce la vista
WITH CHECK OPTION: specifica che inserimenti e modifiche effettuati nella vista devono risultare nelle righe che la vista puo' selezionare.
Esempio:
CREATE VIEW caratt_hw AS
select m.nome_macchina, md.ds_modello, cpu.ds_cpu
from macchine m, modelli md, cpu
where m.cd_modello = md.cd_modello
and m.cd_cpu = cpu.cd_cpu;
Elimina un indice da una tabella.
Sintassi:
DROP INDEX indice
Parametri:
indice: il nome dell' indice da eliminare.
Esempio:
DROP INDEX i_utenti
Elimina una tabella coi relativi dati da un database. Non si puo' ripristinare la situazione precedente, come in tutti i comandi DDL).
Sintassi:
DROP TABLE tabella
Parametri:
tabella: il nome della tabella da eliminare.
Elimina una vista senza pero' influenzare i dati a lei legati.
Sintassi:
DROP VIEW vista
Parametri:
vista: il nome della vista da eliminare
Rinomina una tabella, una vista, una sequence o un sinonimo privato.
Sintassi:
RENAME old TO new
Parametri:
old: il nome corrente dell' oggetto
new: il nuovo nome dell' oggetto
Cancella irrimediabilmente tutte le righe di una tabella.
Sintassi:
TRUNCATE TABLE tabella
Parametri:
tabella: il nome della tabella da ripulire.
I comandi di controllo della sicurezza permettono di gestire gli accessi al DB e sono:
|
GRANT |
Fornisce un privilegio a un utente o a un gruppo di utenti |
|
REVOKE |
Toglie un privilegio a uno o piu' utenti |
I comandi di controllo della transazione permettono di gestire le modifiche operate dai comandi di Data Manipulation e sono:
|
COMMIT |
Rende permanenti le modifiche operate dall'inizio della transazione corrente |
|
ROLLBACK |
Annulla le modifiche operate dall'inizio dell'operazione o dall'ultimo savepoint e riporta i dati alle condizioni iniziali |
|
SAVEPOINT |
Stabilisce un punto oltre il quale e' possibile effettuare un rollback |
|
LOCK TABLE |
Impedisce l'accesso ad una tabella agli altri utenti |
|
SET TRANSACTION |
Stabilisce determinate proprieta' per la transazione corrente |
Assegna ruoli o privilegi di sistema a utenti.
Sintassi:
GRANT {system_priv|role} TO {user|role|PUBLIC} [WITH ADMIN OPTION]
Parametri:
system_priv: il privilegio di sistema da assegnare. Tra i piu' importanti privilegi si ricorda CREATE ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE
role: il ruolo da assegnare; un ruolo e' un insieme di privilegi. Quelli gia' definiti dal sistema sono CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, IMP_FULL_DATABASE.
user: l'utente a cui e' assegnato il privilegio
role: il ruolo a cui e' assegnato il privilegio
PUBLIC: se indicato, significa che tutti gli utenti che hanno visibilita' sul tablespace in esame riceveranno il privilegio indicato.
WITH ADMIN OPTION: permette all'utente specificato di poter assegnare a sua volta privilegi e ruoli ad altri utenti.
Esempi:
1.GRANT CONNECT TO pippo;
2.GRANT DBA to system;
Toglie privilegi a utenti e ruoli. Un utente puo' usare questo comando se e' in possesso della clausola WITH ADMIN OPTION.
Sintassi:
REVOKE {system_priv|role} FROM {user|role|PUBLIC}
Parametri:
system_priv: il privilegio da togliere.
role: il ruolo da togliere.
user: l'utente a cui togliere il privilegio.
role: il ruolo a cui togliere il privilegio.
PUBLIC: se indicato, significa che tutti gli utenti perderanno il privilegio.
Esempio:
REVOKE DBA FROM sys;
Rende permanenti le modifiche effettuate sul DB nella transazione corrente.
Sintassi:
COMMIT
Parametri:
Nessun parametro.
Esempio:
commit;
Usato per eliminare le modifiche effettuate sul DB nella sessione corrente o dall'ultimo savepoint. Viene cosi' ripristinata la condizione precedente.
Sintassi:
ROLLBACK [TO SAVEPOINT savepoint]
Parametri:
savepoint: il nome del savepoint a partire dal quale si vogliono eliminare le modifiche effettuate.
Esempi:
1.ROLLBACK;
2.ROLLBACK TO SAVEPOINT save_me;
Imposta un punto oltre il quale e' possibile effettuare un rollback.
Sintassi:
SAVEPOINT savepoint
Parametri:
savepoint: il nome assegnato al savepoint, per renderlo identificabile al momento del rollback
Esempio:
savepoint save_work;
Inibisce l'accesso alla tabella ad altri utenti.
Sintassi:
LOCK TABLE tabella IN lockmode MODE [NOWAIT]
Parametri:
tabella: il nome della tabella.
lockmode: puo' essere uno dei seguenti
ROW SHARE: permette l'accesso a piu' entita' sulla tabella, ma inibisce gli utenti dal fare a loro volta un lock per averne l'accesso esclusivo.
ROW EXCLUSIVE: come il ROW SHARE, ma impedisce anche i lock in modo SHARE; e' automaticamente ottenuto quando si inserisce, modifica o eliminano dei dati.
SHARE UPDATE: sinonimo di ROW SHARE.
SHARE: permette le interrogazioni contemporanee ma non le modifiche sulla tabella.
EXCLUSIVE: permette solo le query sulla tabella e inibisce qualunque altra attivita' sulla tabella.
SHARE ROW EXCLUSIVE: permette le interrogazioni ma proibisce agli utenti le modifiche e il lock in modo SHARE.
NOWAIT: se specificato, Oracle restituisce immediatamente il controllo all'utente nel caso in cui la tabella abbia un lock di un altro utente; se omesso, Oracle aspetta finche' la tabella non e' di nuovo disponibile e, poi, restituisce il controllo all'utente.
Esempio:
LOCK TABLE macchine IN EXCLUSIVE MODE NOWAIT;
Impedisce operazioni che non siano una query agli altri utenti sulla tabella MACCHINE e controlla che la tabella non sia gia' sotto lock.
Usato per assegnare alla transazione corrente alcune delle seguenti caratteristiche:
READ ONLY
READ WRITE
USE ROLLBACK SEGMENT <segment_name>
Sintassi:
SET TRANSACTION
{READ ONLY | READ WRITE | USE ROLLBACK SEGMENT segment_name}
Parametri:
READ ONLY: stabilisce che la transazione corrente sara' in sola lettura, cioe' non tocchera' i dati del DB
READ WRITE: indica che la transazione sara' anche in scrittura.
USE ROLLBACK SEGMENT segment_name: assegna la transazione corrente ad uno specifico segmento di rollback: e' molto utile per assegnare una transazione molto pesante a segmenti particolarmente grandi.
Esempio:
SET TRANSACTION USE ROLLBACK SEGMENT USER;
Forza la transazione ad usare il segmento di rollback USER; ovviamente il segmento di rollback deve esistere.
A seconda del tipo di data base con cui si lavora, sono presenti molti altri comandi che permettono di eseguire le piu' svariate operazioni, come il controllo della sessione (ALTER SESSION su Oracle) o del sistema (ALTER SYSTEM); per un elenco piu' completo dei comandi si rimanda alla documentazione fornita con il software.
Operatore Funzione
* moltiplicazione
/ divisione
+ addizione
- sottrazione
Operatore Funzione
NOT capovolge il risultato logico dell' espressione
AND combina due o piu' espressioni logiche:
TRUE se sono tutte TRUE; altrimenti FALSE
OR combina due o piu' espressioni logiche:
TRUE se qualcuna e' TRUE; altrimenti FALSE
Operatore Funzione
= uguale a
!= , <> diverso da
> maggiore
>= maggiore o uguale
< minore
<= minore o uguale
IN uguale ad almeno un valore di un set o di una subquery
NOT IN diverso da ogni valore di un set o di una subquery
BETWEEN x AND y maggiore o uguale a x e minore o uguale a y
EXISTS TRUE se la subquery restituisce almeno una riga
LIKE trova una corrispondenza tra stringhe; '%' corrisponde a qualsiasi
sequenza di caratteri, '_' corrisponde a qualsiasi carattere singolo
IS NULL valore nullo
Operatore Funzione
UNION restituisce tutte le righe della prima query + tutte le righe della seconda, evitando le righe duplicate
UNION ALL restituisce tutte le righe della prima query + tutte le righe della seconda, comprese le righe duplicate
INTERSECT restituisce solo le righe di intersezione tra due query, evitando le righe duplicate
MINUS restituisce tutte le righe della prima query che non sono anche nella seconda, evitando le righe duplicate
Operatore Funzione
* indica tutte le colonne di una tabella o di una vista
(+) indica una colonna in outer join
PRIOR indica una relazione padre-figlio tra nodi di una query strutturata ad albero
DISTINCT elimina le righe duplicate dal risultato di una query
ALL mantiene le righe duplicate nel risultato di una query (e' il default)
Funzione Valore restituito
ABS(n) valore assoluto di n
ROUND(n[,m]) n arrotondato a m cifre decimali; m=0 di default; m puo' essere negativo
TRUNC(n[,m]) n troncato a m cifre decimali; m=0 di default; m puo' essere negativo
SIGN(n) 1 se n e' positivo; 0 se n e' 0; -1 se n e' negativo
CEIL(n) il piu' piccolo intero maggiore o uguale a n
FLOOR(n) il piu' grande intero minore o uguale a n
MOD(n,m) il resto della divisione di n per m
POWER(n,m) n elevato alla m
SQRT(n) radice quadrata di n
Funzione Valore restituito
SUBSTR(char,m[,n]) una sottostringa di char, che inizia al carattere m, lunga n byte (se n manca, lunga fino alla fine della stringa char)
LENGTH(char) lunghezza della stringa char in byte
CHR(n) carattere con valore ASCII n
ASCII(char) valore ASCII del primo carattere della stringa char
UPPER(char) stringa char con tutte le lettere maiuscole
LOWER(char) stringa char con tutte le lettere minuscole
INITCAP(char) stringa char con l' iniziale di ogni parola maiuscola
REPLACE(char,string1[,string2])
char con ogni occorrenza di string1 sostituita da string2 (se manca string2, string1 viene cancellata)
TRANSLATE(char,from,to) char con ogni carattere presente in from sostituito col corrispondente carattere di to
RPAD(char1,n[,char2]) char1, riempito a destra di char2 fino alla lunghezza n (il default di char2 e' un blank)
LPAD(char1,n[,char2]) char1, riempito a sinistra di char2 fino alla lunghezza n (il default di char2 e' un blank)
RTRIM(char[,set]) char, con i caratteri finali cancellati dopo l' ultimo carattere non in set (il default di set e' un blank)
LTRIM(char[,set]) char, con i caratteri iniziali cancellati prima del primo carattere non in set (il default di set e' un blank)
Funzione Valore restituito
AVG(n) valore medio di n, ignorando i valori nulli
COUNT(*) numero di righe estratte dalla query
COUNT([DISTINCT]expr) numero di righe estratte dalla query
MAX(expr) valore massimo di expr
MIN(expr) valore minimo di expr
SUM(n) somma dei valori di n
Funzione Valore restituito
SYSDATE data e ora corrente di sistema
ADD_MONTHS(d,n) data d + n mesi
LAST_DAY(d) data dell' ultimo giorno del mese contenente d
MONTHS_BETWEEN(d1,d2)
numero di mesi compresi tra d1 e d2
NEXT_DAY(d,char) data del primo giorno della settimana identificato da char
uguale o maggiore di d
ROUND(d[,fmt]) data d arrotondata come specificato dal formato fmt
TRUNC(d[,fmt]) data d troncata come specificato dal formato fmt
Funzione Valore restituito
TO_CHAR(expr[,fmt]) expr, convertito da NUMBER o DATE a CHAR nel formato specificato da fmt
TO_DATE(char[,fmt]) char, convertito da CHAR nel formato fmt a DATE
TO_NUMBER(char[,fmt]) char, che deve essere una stringa contenente un numero nel formato opzionale fmt, convertito in NUMBER
Funzione Valore restituito
DECODE(expr,search1,return1, [search1,return1,]...[default])
se expr eguaglia qualche search, restituisce return, altrimenti restituisce default
DUMP(expr) expr nel formato interno di Oracle
GREATEST(expr1[,expr2]...)
expr col valore maggiore
LEAST(expr1[,expr2]...) expr col valore minore
NVL(expr1,expr2) expr2, se expr1 e' nullo; altrimenti expr1
UID numero identificativo dell' utente corrente
USER nome dell' utente corrente
USERENV('option') informazione sulla sessione corrente.
Options: SESSIONID, TERMINAL, ecc
VSIZE(expr) numero di byte occupati dalla rappresentazione interna di expr
Nome colonna Valore restituito
sequence.CURRVAL valore corrente della sequence valido per la sessione corrente se e' stato referenziato prima NEXTVAL
sequence.NEXTVAL valore incrementale della sequence nella sessione corrente
[table.]LEVEL 1 per il nodo radice, 2 per un figlio di radice, ecc; usato nelle query strutturate ad albero
[table.]ROWID identificativo di una riga di una tabella del database (contiene il block_id, il row_id e il file_id)
ROWNUM posizione di ogni riga estratta da una query
Nome tabella Valore restituito
DUAL pseudo-tabella presente in ogni database Oracle, accessibile a tutti gli utenti, che contiene 1 colonna di 1 carattere e 1 riga; permette di eseguire ‘false' SELECT allo scopo di eseguire calcoli, visualizzare la data, incrementare una sequence di 1 unita'
create table anagrafica(
cod_anagrafica decimal(5) NOT NULL,
cognome char(18),
nome char(18),
indirizzo char(30),
localita char(18),
prefisso_tel char(5),
numero_tel char(12),
prefisso_fax char(5),
numero_fax char(12));
La tabella ‘anagrafica' ha la caratteristica di contenere un campo, cod_anagrafica, che non puo' contenere valori nulli: se si tenta di inserire un record con codice nullo, come in un esempio successivo, verra' generato un errore e il record, ovviamente, non sara' inserito nella base dati.
insert into anagrafica
values(5, ‘ROSSI','MARIO','C.so Orbassano 120',
'Torino','011','3452456',null, null);
insert into anagrafica(cod_anagrafica, cognome, nome,
indirizzo, localita)
values(6,'VERDI','GIUSEPPE','Via Rossini 21','Torino');
il seguente statement genera un errore:
insert into anagrafica(cognome, nome, localita)
values(‘PAOLINO','PAPERINO','Paperopoli');
perche' si tenta di inserire un record con cod_anagrafica nullo, mentre il campo e' stato dichiarato NOT NULL.
Selezione dati da una tabella
Lo statement di select e' tra i piu' importanti del linguaggio SQL, in quanto permette di selezionare i dati presenti in una o piu' tabelle.
select cognome, nome, citta'
from anagrafica
where cognome like ‘RO%';
seleziona il cognome, nome e la citta' delle persone il cui cognome inizia per RO.
select * from anagrafica;
seleziona tutte le righe della tabella.
update anagrafica
set indirizzo = ‘Via Roma 32',
numero_tel = ‘322344'
where localita = ‘Torino';
update anagrafica
set cognome = null;
Quest'ultimo statement modifica tutti i record della tabella; occorre prestare molta attenzione quando non si pongono condizioni in uno statement, perche' le conseguenze potrebbero essere molto dolorose.
delete from anagrafica
where localita like ‘Tor%';
elimina dalla tabella tutte le persone della tabella anagrafica che risiedono in una localita che comincia con la stringa ‘Tor' (Torino, Tortona, Toronto, ecc...). Se, per ipotesi, non viene rilevato nessun record, la tabella resta invariata.
delete from anagrafica;
elimina tutti i record dalla tabella anagrafica !
Per ottenere un buon report dal nostro database ci servono due tipi di comandi:
· i comandi SQL che permettono di estrarre i dati (in particolare l' istruzione SELECT)
· i comandi SQL*Plus che permettono di migliorare l' output, agendo sul risultato della query
COLUMN
Permette di cambiare la visualizzazione di una colonna in un report.
Sintassi:
COLUMN {nome_colonna|alias} opzioni
Opzioni:
FORMAT per formattare la colonna:
An alfanumerico di ampiezza n
9 posizione numerica
0 forza gli zeri non significativi
$ dollaro
. punto decimale
, virgola di separazione tra le migliaia
HEADING per cambiare l' intestazione (stringa tra apici singoli)
JUSTIFY per allineare le colonne (LEFT/CENTER/RIGHT)
TRUNC tronca i valori troppo grandi rispetto al formato della colonna
WRAP continua a scrivere un valore troppo grande rispetto al formato della colonna nella riga successiva
Esempi:
COLUMN emp_name FORMAT A20 HEADING 'Cognome e Nome'
COLUMN salary FORMAT $09,999.99 HEADING 'Stipendio'
Permette di inserire un titolo su tutte le pagine del report, come intestazione (Top Title) o come pie' di pagina (Bottom Title).
Sintassi:
TTITLE opzioni 'testo' variabile
BTITLE opzioni 'testo' variabile
Opzioni:
COL n inizia alla colonna n
SKIP n salta n righe (1 di default)
LEFT,CENTER,RIGHT centratura rispetto alla dimensione della riga
BOLD grassetto
FORMAT formato della variabile che segue
Variabili:
SQL.PNO numero della pagina
SQL.USER utente con cui sono collegato a Oracle
Esempio:
TTITLE LEFT 'FIATSAVA' RIGHT 'Pag. ' FORMAT 999 SQL.PNO -
SKIP COL 10 'Elenco dipartimenti:'
Separa i gruppi di valori uguali di una colonna sopprimendo la stampa dei valori che si ripetono.
I valori nella colonna di break devono essere ordinati.
Sintassi:
BREAK ON colonna1 opzioni colonna2 opzioni ...
Opzioni:
PAGE effettua un salto pagina al variare del valore nella colonna di break
SKIP n lascia n righe bianche (1 di default) al variare del valore nella colonna di break
Esempio:
BREAK ON dept_id PAGE
Esegue dei calcoli alla fine di ogni sezione di break, per questo e' sempre associato a un corrispondente comando di BREAK ON.
Sintassi:
COMPUTE funzione OF colonna ON colonna
Funzioni:
Sono le funzioni di gruppo: AVG, COUNT, SUM, MAX e MIN
Esempio:
COMPUTE COUNT OF emp_id ON dept_id
COMPUTE SUM OF salary ON dept_id
Imposta un vasto numero di parametri di output (sia su terminale che su stampante).
Sintassi:
SET parametro {ON|OFF|valore}
Parametri:
PAGESIZE n numero di righe per pagina (default = 14)
LINESIZE n numero di caratteri per riga di output (default = 80)
SPACE n numero di spazi bianchi tra le colonne di output (default = 1, massimo 10)
FEEDBACK on/off
visualizza alla fine di ogni query il messaggio indicante il numero di record selezionati
FEEDBACK n
mostra il messaggio solo se i record selezionati sono n o piu' (default = 6)
HEADING on/off
stampa le intestazioni delle colonne (default on)
PAUSE 'stringa'
mostra il messaggio e attende un Return per mostrare una nuova pagina
LONG n dimensione massima per la visualizzazione di un capo LONG (default = 80)
ECHO on/off visualizza il comando da eseguire quando lo legge da un file (default = off)
TERMOUT on/off
visualizza l' output generato dall' esecuzione di un file di comandi (default = on)
VERIFY on/off
visualizza le linee di verifica prima e dopo la sostituzione di una variabile (default = on)
Esempio:
SET PAGESIZE 74
SET FEEDBACK OFF
Definisce una variabile usata in un' istruzione SQL. La variabile rimane definita per tutta la sessione SQL*Plus o finche' non viene emesso un comando Undefine su di essa.
Sintassi:
DEFINE variabile = valore
Variabili:
Una variabile puo' essere richiamata in due modi nell' istruzione SQL:
&variabile se la variabile non e' definita, e' richiesta all' utente la sua valorizzazione, e' usata questa valorizzazione e poi e' annullata
&&variabile se la variabile non e' definita, e' richiesta all' utente la sua valorizzazione, e' usata questa valorizzazione e poi rimane attiva per le istruzioni successive
Esempio:
DEFINE mansione = VENDITORE
Annulla un comando dato in precedenza.
Sintassi:
CLEAR comando
Esempio:
CLEAR BREAK
Apre un file di stampa (.LIS o .LST) in cui scrive tutto cio' che compare sul video fino al comando Spool off.
Sintassi:
SPOOL {nome_file|OFF}
Esempio:
SPOOL lista1
SPOOL OFF
Salva l' ultima istruzione SQL in un file di comandi (.SQL).
Sintassi:
SAVE nome_file
Esempio:
SAVE prova1
Richiama un file di comandi nel buffer SQL.
Sintassi:
GET nome_file
Esempio:
GET prova2
Richiama un file di comandi e lo esegue.
Sintassi:
START nome_file
Esempio:
START prova3
Il PL/SQL e' un' implementazione procedurale (Procedural Language) del linguaggio SQL per lo sviluppo di applicazioni che usano l' RDBMS Oracle.
Oltre alle potenzialita' del linguaggio SQL, il PL/SQL offre le capacita' di un linguaggio procedurale: l' uso di variabili e di costanti, i cicli, le label e un gestore di errori.
PL/SQL invia all' RDBMS un unico blocco comprensivo di diverse istruzioni SQL, riducendo cosi' il traffico di rete verso l' RDBMS. Permette inoltre di compilare dei blocchi di istruzioni, di memorizzarli sul database e di utilizzarli senza piu' bisogno di compilazione, migliorando le prestazioni.
PL/SQL lavora all' interno dei tool Oracle come ad esempio SQL*Forms e SQL*ReportWriter.
In PL/SQL sono scritti i Database Trigger e le Procedure.
Le principali caratteristiche di PL/SQL sono:
· struttura
a blocchi
· gestione
di variabili e costanti
· controllo del flusso
· gestione dei cursori
· gestione
degli errori
Il blocco PL/SQL e' costituito di tre parti:
· parte dichiarativa (inizia con DECLARE)
· parte esecutiva (inizia con BEGIN)
· parte di gestione degli errori e delle eccezioni (inizia con EXCEPTION)
L' intero blocco finisce con END.
L' unica parte obbligatoria del blocco e' quella esecutiva.
Il blocco principale (enclosing-block) puo' contenere al suo interno altri blocchi (sub-block).
Gestione di variabili e costanti
All' interno del blocco PL/SQL possiamo utilizzare le costanti e le variabili definite nella parte dichiarativa del blocco stesso. Una variabile e' globale se definita nell' enclosing-block, e' locale se definita nel sub-block.
L' assegnazione degli attributi di una variabile puo' avvenire con l' operatore %TIPE che assegna alla variabile il tipo e la grandezza di una colonna del database.
L' assegnazione di un valore a una variabile avviene con:
· l' operatore di assegnazione ':='
· la 'SELECT valore INTO variabile'
Sintassi:
nome_variabile tipo_dato
nome_costante CONSTANT tipo_dato := valore;
Note:
tipo_dato : NUMBER, CHAR, DATE o BOOLEAN
Esempio:
incr CONSTANT NUMBER(3,2) := 1.1;
premio NUMBER(10);
codice emp.cod_emp%TYPE;
Esistono due tipi di controllo del flusso:
· controllo condizionale
IF condizione1 THEN operazione 1;
ELSIF condizione2 THEN operazione 2;
ELSE operazione 3;
END IF;
· controllo iterativo
1) LOOP operazione1;
IF condizione THEN operazione2 EXIT;
END IF;
END LOOP;
2) FOR indice IN inizio..fine LOOP
operazione;
END LOOP;
3) WHILE condizione LOOP
operazione;
END LOOP;
Il comando GOTO permette di modificare il flusso di esecuzione dei un blocco PL/SQL mandandolo alla label specificata.
Esistono due tipi di cursori:
· cursore esplicito da definire nella parte dichiarativa del blocco
· cursore implicito autodefinito da PL/SQL quando usiamo un' istruzione SQL
La gestione degli errori avviene nell' ultima parte del blocco PL/SQL, all' interno della struttura EXCEPTION. L' exception scatta quando si verifica un errore che interrompe l' esecuzione del programma.
Esistono due tipi di exception:
· exception predefinite
DUP_VAL_ON_INDEX valore duplicato nella chiave
INVALID_CURSOR cursore non esistente
NO_DATA_FOUND la select non trova righe
TOO_MANY_ROWS la select trova troppe righe
OTHERS tutti gli errori non definiti nell' EXCEPTION
· exception definite dall' utente
sono dichiarate nella parte dichiarativa del blocco
sono richiamate dal comando RAISE nella parte esecutiva
Esistono due funzioni che possono aiutarci nella gestione degli errori:
· la funzione SQLCODE che fornisce il codice d' errore Oracle
· la funzione SQLERRM che fornisce il messaggio d' errore Oracle
Sintassi:
WHEN nome_exception THEN operazione;
Esempio:
WHEN NO_DATA_FOUND THEN close cursor c1;
WHEN OTHERS THEN null;
Testo: Corso linguaggio SQL
Data: 30 Dicembre 1997
Versione: 1.2.1
Autori: Meo Bogliolo, Fabio
Maggiora