Corso Linguaggio SQL

 


Sommario

Linguaggio SQL

Tipi di dati ORACLE

DML - Data Manipulation Language

DDL - Data Definition Language

DCL - Data Control Language

Comandi di Amministrazione

Sintassi SQL

Esempi di statement SQL

SQL*Plus

PL/SQL


Linguaggio SQL

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:

Tipi di dati ORACLE

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
· anno
· mese
· giorno
· ora
· minuto
· secondo

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.


DML - Data Manipulation Language

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 ...

Insert

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%';

 

Delete

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;

 

Update

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;

 

 

DDL - Data Definition Language

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.

 

Alter index

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);

 

Alter table

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));

 

Storage

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.

 

Alter view

Ricompila una vista esistente.

 

Sintassi:

ALTER VIEW vista COMPILE

 

Parametri:

vista: il nome della vista da modificare.

 

Create index

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);

 

Create table

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));

 

Create view

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;

 

Drop index

Elimina un indice da una tabella.

 

Sintassi:

DROP INDEX indice

Parametri:

indice: il nome dell’ indice da eliminare.

Esempio:

DROP INDEX i_utenti

 

Drop table

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.

 

Drop view

Elimina una vista senza pero’ influenzare i dati a lei legati.

Sintassi:

DROP VIEW vista

Parametri:

vista: il nome della vista da eliminare

 

Rename

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

 

Truncate

Cancella irrimediabilmente tutte le righe di una tabella.

Sintassi:

TRUNCATE TABLE tabella

Parametri:

tabella: il nome della tabella da ripulire.

DCL - Data Control Language

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

Grant

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;

 

Revoke

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;

 

Commit

Rende permanenti le modifiche effettuate sul DB nella transazione corrente.

Sintassi:

COMMIT

Parametri:

Nessun parametro.

Esempio:

commit;

 

Rollback

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;

 

Savepoint

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;

 

Lock table

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.

Comandi di Amministrazione

Set transaction

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.

Sintassi SQL

Operatori

Operatori aritmetici

 

Operatore Funzione

* moltiplicazione

/ divisione

+ addizione

- sottrazione

 

Operatori logici

 

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

 

Operatori di confronto

 

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

 

Operatori insiemistici

 

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

 

Altri operatori

 

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)

 

Funzioni

 

Funzioni numeriche

 

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

 

Funzioni di stringa

 

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)

Funzioni di gruppo

 

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

 

Funzioni sulle date

 

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

 

Funzioni di conversione

 

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

 

Altre funzioni

 

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

 

Pseudo-colonne

 

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

 

Pseudo-tabelle

 

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’

 

 

Alcuni semplici esempi di statement SQL

Creazione di una tabella

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.

 

Inserimento dati in tabella

 

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.

 

Aggiornamento dati in 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.

 

Eliminazione dati in tabella

 

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 !

SQL*Plus

Introduzione

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

 

Comandi

 

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’

 

TTITLE e BTITLE

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:’

 

BREAK ON

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

 

COMPUTE

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

 

SET

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

 

DEFINE

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

 

CLEAR

Annulla un comando dato in precedenza.

Sintassi:

CLEAR comando

Esempio:

CLEAR BREAK

 

SPOOL

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

 

SAVE

Salva l’ ultima istruzione SQL in un file di comandi (.SQL).

Sintassi:

SAVE nome_file

Esempio:

SAVE prova1

 

GET

Richiama un file di comandi nel buffer SQL.

Sintassi:

GET nome_file

Esempio:

GET prova2

 

START

Richiama un file di comandi e lo esegue.

Sintassi:

START nome_file

Esempio:

START prova3

PL/SQL

Introduzione

 

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.

 

Caratteristiche

Le principali caratteristiche di PL/SQL sono:

· struttura a blocchi
· gestione di variabili e costanti
·
controllo del flusso

·
gestione dei cursori
· gestione degli errori

Struttura a blocchi

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;

 

Controllo del flusso

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.

 

Gestione dei cursori

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

 

Gestione degli errori

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