Corso SQL

Collegamenti utili gratuiti

Corso SQL

Da Wikipedia, l'enciclopedia libera.

SQL (Structured Query Language) è un linguaggio di interrogazione per database progettato per leggere, modificare e gestire dati memorizzati in un sistema basato sul modello relazionale, per creare e modificare schemi di database, per creare e gestire strumenti di controllo ed accesso ai dati.

SQL è un linguaggio per interrogare e gestire basi di dati mediante l'utilizzo di costrutti di programmazione denominati query. La prima versione fu sviluppata da IBM all'inizio degli anni settanta. Chiamata originariamente SEQUEL era progettata per manipolare dati memorizzati nel database relazionale ideato e brevettato da IBM. Nel 1986 l' ANSI lo standardizzò con la sigla SQL-86. Negli anni successivi sono state rilasciate altre versioni, riconosciute anche dall' ISO. La maggior parte dei sistemi per la gestione di database implementano questi standard ed aggiungono funzionalità aggiuntive proprietarie. Con SQL si leggono, modificano, cancellano dati e si esercitano funzioni gestionali ed amministrative sul sistema dei database. La maggior parte delle implementazioni dispongono di interfaccia alla riga di comando per l'esecuzione diretta di comandi, in alternativa alla sola interfaccia grafica GUI.

Originariamente progettato come linguaggio di tipo dichiarativo, si è successivamente evoluto con l'introduzione di costrutti procedurali, istruzioni per il controllo di flusso, tipi di dati definiti dall'utente e varie altre estensioni del linguaggio. A partire dalla definizione dello standard SQL:1999 molte di queste estensioni sono state formalmente adottate come parte integrante di SQL nella sezione SQL/PSM dello standard.

Alcune delle critiche più frequenti rivolte ad SQL riguardano la mancanza di portabilità del codice fra vendors diversi, il modo inappropriato con cui vengono trattati i dati mancanti (Null), e la semantica a volte inutilmente complicata.

L'SQL nasce nel 1974 ad opera di Donald Chamberlin, nei laboratori dell'IBM. Nasce come strumento per lavorare con database che seguano il modello relazionale. A quel tempo però si chiamava SEQUEL (la corretta pronuncia IPA è [ˈɛsˈkjuˈɛl], quella informale [ˈsiːkwəl]). Nel 1975 viene sviluppato un prototipo chiamato SEQUEL-XRM; con esso si eseguirono sperimentazioni che portarono, nel 1977, a una nuova versione del linguaggio, che inizialmente avrebbe dovuto chiamarsi SEQUEL/2 ma che poi divenne, per motivi legali, SQL. Su di esso si sviluppò il prototipo System R, che venne utilizzato da IBM per usi interni e per alcuni suoi clienti. Ma, dato il suo successo, anche altre società iniziarono subito a sviluppare prodotti basati su SQL. Nel 1981 IBM iniziò a vendere alcuni prodotti relazionali e nel 1983 rilasciò DB2, il suo DBMS relazionale diffuso ancor oggi. SQL divenne subito lo standard industriale per i software che utilizzano il modello relazionale.

L'ANSI lo adottò come standard fin dal 1986, senza apportare modifiche sostanziali alla versione inizialmente sviluppata da IBM. Nel 1987 la ISO fece lo stesso. Questa prima versione standard è denominata SQL/86. Negli anni successivi si realizzarono altre versioni, che furono SQL/89, SQL/92 e SQL/2003. Tale processo di standardizzazione mirava alla creazione di un linguaggio che funzionasse su tutti i DBMS (Data Base Management Systems) relazionali, ma questo obiettivo non fu raggiunto. Infatti, i vari produttori implementarono il linguaggio con numerose variazioni e, in pratica, adottarono gli standard ad un livello non superiore al minimo, definito dall'Ansi come Entry Level.

Fine articolo da Wikipedia su SQL

Corso e guida SQL

IL LINGUAGGIO SQL (Structured Query Language)

Caratteristiche generali

Il linguaggio SQL può essere considerato l’’esperanto’ dei DBMS; e' un linguaggio non procedurale che e' diventato uno standard tra i linguaggi per la gestione di database relazionali; oggi viene usato in tutti i prodotti DBMS come linguaggio di comandi per l'utente della base di dati (Oracle, Informix, Access).
Ricordiamo che all'interno del DBMS esistono specifici linguaggi che svolgono funzioni diverse:

DDL (data definition language) e DMCL (device media control language): e' un linguaggio per la realizzazione fisica della base di dati e viene utilizzato dal programmatore e dall'amministratore della base di dati
DML (data manipulation language): serve per effettuare variazioni (inserimenti, modifiche e cancellazioni) dei dati nella base di dati ed e' a disposizione dell'utente
DCL (data control language): consente al Database administrator di stabilire le autorizzazioni agli accessi e i permessi consentiti agli utenti relativamente a scrittura, modifica e cancellazione degli archivi
QL (query language): linguaggio di interrogazione, utilizzato dall'utente per ottenere informazioni dalla base di dati.

Nei moderni DBMS questi linguaggi sono unificati in uno specifico linguaggio denominato linguaggio per la gestione di basi di dati; tipico esempio e' il linguaggio SQL.
Le parole chiave che costituiscono i comandi del linguaggio SQL possono essere usate in modo interattivo, scrivendole direttamente sulla tastiera nel momento in cui si vogliono attivare. Tuttavia nelle piu' recenti versioni del linguaggio i comandi possono essere inviati al sistema attraverso un'interfaccia grafica, che utilizza menu', finestre e icone per guidare l'utente nella scelta del lavoro da richiedere (ad es. la finestra Qbe di Access).
Rispetto ai linguaggi di programmazione procedurali e tradizionali, il linguaggio SQL puo' sicuramente aumentare la produttivita' nel progetto e nello sviluppo delle applicazioni orientate alla gestione di archivi di dati grazie alla semplicita' nell'uso delle istruzioni, alla concisione dei comandi e alla visione tabellare dei dati,.
Il linguaggio SQL consente all'utente di:

definire la struttura delle tabelle del database (funzioni DDL)
modificare i dati contenuti nel database, con operazioni di inserimento, variazione e cancellazione (funzioni DML)
gestire il controllo degli accessi per gli utenti (funzioni DCL)
porre interrogazioni al data base (funzioni query language).

Inoltre il linguaggio fornisce gli opportuni comandi per definire in modo facile i tabulati di uscita dei risultati (report), per recuperare dati quando si verifica un'interruzione o un malfunzionamento del sistema, per definire le viste degli utenti, per garantire la sicurezza dei dati nei confronti degli accessi di piu' utenti, ecc. SQL è case-insensitive.
ATTENZIONE: negli appunti si farà principalmente riferimento all’utilizzo SQL-Access; SQL-ANSI prevede alcune differenze che verrano evidenziate

Identificatori e tipi di dati

Gli identificatori dei nomi di tabelle e di attributi sono costituiti da sequenze di caratteri: devono iniziare con una lettera e possono contenere numeri e il carattere '_' come separatore (e' bene non utilizzare spazi, punti, il trattino '-' o altri caratteri particolari che potrebbero generare confusione quando si utilizza SQL o codici scritti in Visual Basic); la lunghezza massima di un identificatore valido e' 18 caratteri.
Per identificare il nome di un attributo della tabella si usa la sintassi:
NomeTabella.NomeAttributo
(cio' ha senso se pensiamo che in Access non e' obbligatorio dare nomi diversi a campi che contengono gli stessi dati in tabelle diverse, a differenza degli archivi tradizionali).
Il linguaggio SQL utilizza caratteri alfabetici, cifre decimali, operatori aritmetici (+ - / *), di confronto (= < >) e booleani (NOT, AND, OR).
Le costanti stringa vanno racchiuse tra apici (' o "); le costanti numeriche possono avere o non avere il segno + o -.
Nei numeri decimali la parte intera e' separata dalle cifre decimali attraverso il punto (notazione anglosassone).
Gli attributi che hanno un valore non disponibile o non definito assumono valore Null (Null non e' uguale a nessun altro valore: e' diverso dallo zero e dalla stringa vuota; negli ordinamenti Null compare all'inizio delle sequenze crescenti e alla fine di quelle decrescenti).
Quando si dichiara la struttura di una tabella (v. paragrafo successivo) occorre specificare il tipo dei dati scelto per gli attributi; i tipi di dati standard sono:

CHARACTER(n) o CHAR(n): stringa di lunghezza n (da 1 a 15.000); se viene omesso n si intende un solo carattere
DATE: data nella forma mm/gg/aa
TIME: ora nella forma hh:mm
INTEGER(p) o INT(p): numero intero con precisione p (da 1 a 45) (in ambiente Access non va!)
SMALLINT: numero intero con precisione 5 (da -32768 a 32767 v. Pascal)
INTEGER: numero intero con precisione 10 (da -2.147.483.648 a 2.147.483.647)
DECIMAL(p,s) o DEC(p,s): numero decimale con precisione p e cifre decimali s (p da 1 a 45 e s da 0 a p)
REAL: numero reale con mantissa di precisione 7
FLOAT (o DOUBLE PRECISION): numero reale con mantissa di precisione 15
FLOAT(p): numero reale con mantissa di precisione p (p da 1 a 45).

Per i dati numerici la precisione p indica il numero massimo di cifre che il numero puo' contenere, escluso il segno e il punto decimale; per i numeri decimali il valore s indica il numero di cifre che seguono il punto decimale; i dati numerici floating point (numeri approssimati) sono memorizzati in forma esponenziale e la precisione riguarda solo le cifre della mantissa (v. note in fondo).
In SQL-ANSI è possibile scrivere commenti facendoli precedere dal simbolo – (due trattini); in SQL-Access non vengono però accettati.

TABELLE UTILIZZATE NEGLI APPUNTI:
STUDENTE(COD_STUD, COGNOME, NOME, INDIRIZZO, CITTA, DATANASCITA)
VOTI o VOTO(NUMERO o progressivo, MATERIA, DATA, COD_STUD)
In associazione 1:N STUDENTE:VOTI
(in VOTI si potrebbe omettere la KP)

CONVENZIONE: negli appunti utilizzo il nome COD_STUD sia come chiave primaria (in STUDENTE) sia come chiave esterna (in VOTI); in realtà sarebbe opportuno utilizzare le seguenti convenzioni:

nella tabella STUDENTE (parte 1 dell’associazione), chiamare la chiave primaria MATRICOLA(o ID, se non voglio usare la matricola);
nella tabella VOTI (parte N dell’associazione) usare come chiave esterna un campo denominato IDSTUDENTE (cioè ID seguito dal nome della tabella a cui deve riferirsi la chiave esterna).

Definizione delle tabelle (DDL)

I comandi che seguono rappresentano la parte del linguaggio SQL definita come DDL (data definition language), cioe' il linguaggio che permette l'implementazione del modello logico dei dati sulle memorie di massa.
Tuttavia nelle moderne versioni dei prodotti DBMS (come Access) le operazioni di creazione di tabelle e indici, di modifica o cancellazione della struttura, vengono effettuate in modo interattivo attraverso interfacce grafiche che consentono all'utente di svolgere le operazioni senza conoscere la sintassi dei comandi.
I comandi sono:

per creare la struttura della tabella: CREATE TABLE seguito dal nome della tabella e dall'elenco degli attributi, specificandone nome e tipo di dato; ad esempio:

CREATE TABLE STUDENTE
(COD_STUD CHAR(3) NOT NULL,
COGNOME CHAR(10),
NOME CHAR(10),
INDIRIZZO CHAR(30),
CITTA CHAR(20),
DATA_NASCITA DATE);
La clausola NOT NULL e’ opzionale ma specificandola accanto ad un attributo, e in particolare modo per quello che fungera' da chiave primaria, si vuole indicare che nessuna riga di questa colonna potra’ essere vuota (In Access corrisponde alla proprieta' 'richiesto' 'Si').
Ricordiamo che lo stesso nome di attributo si puo' utilizzare in tabelle diverse perche' durante la gestione dei dati si distinguono gli attributi specificando il nome della tabella a cui appartengono: NomeTabella.NomeAttributo.
Ogni istruzione SQL termina con ;
Utilizzando Access, possiamo provare a realizzare la nostra tabella in SQL seguendo questa procedura:
1    Nella finestra del database, scegliere la scheda Query, quindi scegliere Nuovo.
2    Nella finestra di dialogo Nuova query, scegliere Visualizzazione Struttura, quindi scegliere OK.
3    Senza aggiungere tabelle o query, scegliere Chiudi nella finestra di dialogo Mostra tabella.
4    Scegliere SQL Specifico dal menu Query, quindi scegliere Definizione dati.
5    Immettere l'istruzione SQL per la query di definizione dati e salvare. Ciascuna query di definizione dati è costituita soltanto da un'istruzione di definizione dati.
6    Per eseguire la query, fare clic su Esegui (punto esclamativo !) sulla barra degli strumenti e viene creata la tabella oppure ciccare due volte sulla query.
N.B. le “query” create in questo modo non sono query di interrogazione: notate il diverso simbolo che appare di fianco al nome nella finestra database.
Per verificare che la struttura della tabella sia stata creata scegliere la linguetta tabella della finestra database e aprire in modalita’ struttura. Ma manca ancora la chiave primaria!

per creare una tabella con chiave primaria:
CREAZIONE TABELLA CON CHIAVE PRIMARIA (con una sola istruzione)

CREATE TABLE STUDENTI
(MATRICOLA COUNTER PRIMARY KEY,
COGNOME CHAR(20),
NOME CHAR(20), ………….);
se un campo viene definito PRIMARY KEY, risulta inutile (anche se è accettato) imporre la clausola NOT NULL (la chiave primaria non può mai essere nulla: integrità sull’entità)

CREAZIONE TABELLA CON CHIAVE PRIMARIA e CHIAVE ESTERNA garantendo l’INTEGRITA’ REFERENZIALE

CREATE TABLE VERIFICA
(PROGRESSIVO COUNTER PRIMARY KEY,
DATA DATE,
VOTO DOUBLE,
MATRICOLA INT,
CONSTRAINT VERIFICA
FOREIGN KEY(MATRICOLA) REFERENCES STUDENTI);
Si definisce il campo (MATRICOLA) con il tipo di dato (INT), si specifica la clusola CONSTRAINT sulla tabella (significa che c’è una regola di integrità), si dichiara la chiave esterna sul campo in esame (FOREIGN KEY (MATRICOLA)) specificando a quale tabella primaria deve essere correlata (STUDENTI).

un INDICE è un campo mantenuto sempre in ordine, che permette di accedere ed ottenere in modo più veloce ed efficiente i dati quando questi sono oggetto di frequenti ricerche ed ordinamenti (vantaggio: velocità di accesso ai dati); tuttavia la gestione di un indice è pesante in quanto richiede spazio su memoria di massa e deve essere sempre mantenuto ordinato (svantaggi).

Le chiavi primarie sono sempre indicizzate (senza duplicati); teoricamente è possibile creare un indice per ogni campo, ma ciò è molto pesante: quindi la scelta deve essere attentamente valutata. Il concetto di indice si trova SIA nella gestione dei FILE che dei DB. In Visual Basic non è previsto l’accesso indexed ai file; per poter gestire un indice è necessario creare un archivio ausiliario (contenente il solo campo mantenuto ordinato e un campo che contiene la posizione, che punta al record del file per mantenere la corrispondenza) a progettare gli appositi algoritmi di gestione. Nei DBMS (e Access) i campi sono delle tabelle ausiliarie che hanno una corrispondenza nella tabella originarioa e che vengono mantenute ordinate sul campo scelto come indice; è conveniente indicizzare solo i campi oggetto di frequenti ricerche e ordinamenti. Per creare un indice in SQL si usa l'istruzione
CREATE [UNIQUE] INDEX nomeindice ON nometabella (nomecampo)
dove: nomeindice e' il nome del campo da indicizzare; la parola riservata UNIQUE impedisce la presenza di valori duplicati.
Quando si definisce la chiave primaria, essa risulta automaticamente NOT NULL, INIQUE e indicizzata: è inutile imporre queste clausole!
L'istruzione completa per creare un indice e':
CREATE [UNIQUE ] INDEX campo ON tabella (campo ASC);
Per fare in modo che l'indice sia crescente o decrescente si usa la clausola ASC o DESC dopo il nome del campo indice; UNIQUE potra’ essere specificato se anche la chiave secondaria non ammette duplicati.
Ad esempio, creiamo un indice alfabetico (crescente) sul cognome:
CREATE INDEX COGNOME ON STUDENTE (COGNOME ASC);
Anche in questo caso, dopo aver creato l'istruzione SQL con il procedimento visto prima per la creazione della tabella, verifichiamo in Access che nella tabella sia stata inserito l'indice: nella struttura della tabella vediamo che il campo COGNOME risulta indicizzato con duplicati; per verificare meglio tali proprieta' possiamo analizzare la finestra indici: per aprirla, con la tabella aperta in modalita' struttura, dal menu' visualizza scegliamo indici

per modificare la struttura di una tabella (cioe’ aggiungere o eliminare colonne): ALTER TABLE seguito dal nome della tabella e da:

ADD nome del campo e tipo di dato, per aggiungere una colonna oppure
DROP nome del campo, per eliminarla.
Ad esempio, per aggiungere l’attributo telefono alla tabella STUDENTE:
ALTER TABLE STUDENTE
ADD TELEFONO CHAR(12);
e’ possibile aggiungere contemporaneamente anche piu’ campi, separandoli con la virgola; es.:
ALTER TABLE STUDENTE
ADD TELEFONO CHAR(12), CELLULARE CHAR(12), ONOMASTICO DATE;
se in una tabella non ho ancora definito la KP, posso aggiungerla scrivendo:
ALTER TABLE nometabella
ADD nomecampo tipo PRIMARI KEY;
Per rimuovere uno o piu’ campi:
ALTER TABLE STUDENTE
DROP CELLULARE, ONOMASTICO;
Non è possibile usare ADD e DROP nella stessa istruzione SQL.

Per eliminare una tabella o un indice si usa il comando DROP;

ad esempio, per eliminare la tabella STUDENTE:
DROP TABLE STUDENTE;
per eliminare l’indice chiave primaria dalla tabella STUDENTE:
DROP INDEX COD_STUD ON STUDENTE;
(N.B. non viene eliminato il campo ma solo l’indice!)
Se devo eliminare un campo che è chiave primaria/indice, prima occorre rimuovere l’indice e poi è possibile eliminare il campo.

Manipolazione dei dati (DML)

Come gia’ visto per le operazioni di definizione delle tabelle, anche le operazioni relative all’inserimento, modifica e cancellazione dei dati vengono gestite nei moderni DBMS attraverso opportune interfacce grafiche. Vediamo comunque brevemente quali sono i comandi SQL:

per inserire i dati di una nuova riga nella tabella STUDENTE:

INSERT INTO STUDENTE (COD_STUD, COGNOME, NOME, INDIRIZZO, CITTA, DATA_NASCITA, TELEFONO)
VALUES ('AB1', 'ROSSI', 'PAOLO', 'VIA XXXX', 'CREMONA', '22/02/80', '037231594');
cioe’ occorre elencare i campi da riempire e i valori corrispondenti ad ogni campo…e non e’ certo comodo!
E’ possibile rendere piu’ generale l’accodamento di una riga della tabella scrivendo un’istruzione parametrica, i cui valori sono cioe’ diversi di volta in volta a seconda dei dati da inserire:
INSERT INTO STUDENTE (COD_STUD, COGNOME, NOME, INDIRIZZO, CITTA, DATA_NASCITA, TELEFONO )
VALUES ([inserire il codice dello studente], [cognome:], [nome:], [indirizzo:], [città:], [data di nascita:], [telefono:]);
… e cosi’ e’ un po’ piu’ comoda!
ATTENZIONE: se un campo KP è di tipo COUNTER (non ANSI), quando inserisco i dati deve essere omesso; nell’esempio COD_STUD è COUNTER:
INSERT INTO STUDENTE (COGNOME, NOME, INDIRIZZO, CITTA,DATA_NASCITA, TELEFONO )
VALUES ([cognome:], [nome:], [indirizzo:], [città:], [data di nascita:], [telefono:]);

Per modificare un dato:

UPDATE STUDENTE SET INDIRIZZO = 'VIA ROMA, 6'
WHERE INDIRIZZO='VIA XXXX';
Stesso commento di prima!
Anche questa istruzione puo’ essere resa parametrica; ad esempio, si vuole modificare l’indirizzo di un certo studente, noto il codice:
UPDATE STUDENTE SET INDIRIZZO = [inserire il nuovo indirizzo]
WHERE COD_STUD=[inserire il codice dello studente];
E’ possibile aggiornare più campi di uno stesso record:
UPDATE STUDENTE SET INDIRIZZO = [inserire il nuovo indirizzo], NTEL=[nuovo numero], …
WHERE COD_STUD=[inserire il codice dello studente];

Per cancellare dalla tabella STUDENTE i dati dello studente con codice ‘AB1’:

DELETE *
FROM STUDENTE
WHERE COD_STUD='AB1';
Per cancellare tutti gli studenti di Cremona:
DELETE *
FROM STUDENTE
WHERE CITTA='CREMONA';
Naturalmente e’ possibile cancellare i record che corrispondono ad un criterio che puo’ variare di volta in volta (parametro); ad esempio, per cancellare gli studenti di una certa citta’:
DELETE *
FROM STUDENTE
WHERE CITTA=[INSERIRE LA CITTA'];
Il simbolo * puo’ essere omesso; in ogni caso indica che verranno eliminati tutti i dati relativi a una o piu’ righe specificate. L’istruzione DELETE cancella un intero record, non un singolo campo; potrei scrivere:
DELETE COGNOME
FROM STUDENTE
WHERE COD_STUD=[inserire codice]
Ed è equivalente a DELETE *!
(in Access tali istruzioni vengono dette “query di eliminazione”)
La clausola WHERE consente di operare su gruppi di record, anziche’ su una sola riga: basta indicare dopo WHERE una condizione (fissa o parametrica) che deve essere verificata dalle righe. Inoltre e’ possibile combinare due o piu’ condizioni usando gli operatori logici AND, OR, NOT (anche BETWEEN…AND).
Ricordiamo che le costanti vanno scritte tra apici e le variabili tra parentesi quadre (come in Access)!

DELETE e UPDATE possono operare anche su tutti i record di una tabella:
UPDATE VOTI
SET NUMERO = 10
Assegna 10 al campo NUMERO di tutti i record.
UPDATE VOTI
SET NUMERO = NUMERO+1
Incrementa di 1 tutti i voti (numero)
DELETE *
FROM STUDENTE
Elimina tutti i record dalla tabella STUDENTE

L’istruzione UPDATE consente anche di AGGIORNARE I DATI DI UNA TABELLA CON I DATI PRELEVATI DA UN’ALTRA tabella:
ad esempio, date le tabelle:
ARTICOLO (CODA, DESCRIZIONE, PREZZO, GIACENZA…)
RIGAFATTURA (NUMERO, QTA, DATA, CODA…)
Dove RIGAFATTURA memorizza le vendite per ogni articolo, aggiornare la giacenza di magazzino in base alla quantità venduta:
UPDATE ARTICOLO INNER JOIN RIGAFATTURA
ON ARTICOLO.CODA=RIGAFATTURA.CODA
SET GIACENZA = GIACENZA – QTA;
vengono aggiornate tutte le righe della tabella ARTICOLO, in base alle righe contenute nella tabella RIGAFATTURA congiunte con un equi-join ad ARTICOLO (per INNER JOIN v. oltre). In Access tale istruzione è detta “query di aggiornamento”

Se ho due tabelle aventi la stessa struttura, UPDATE consente anche DI ACCODARE I DATI DI UNA TABELLA ALL’ALTRA:
INSERT INTO TAB2
SELECT *
FROM TAB1
(WHERE condizione);
WHERE consente di accodare solo alcuni record, in base alla condizione specificata.
In Access tale istruzione è detta “query di accoramento”.

Gestione di accessi, viste, sicurezza, integrita’ dati e transazioni (DCL)

Il Database Administrator o colui che crea le tabelle puo’ stabilire anche i diritti di accesso per utenti specifici e per operazioni specifiche.

per concedere il permesso a determinati utenti di poter compiere determinate operazioni si usa GRANT seguito dal tipo di operazione ammessa e dal nome di uno o piu’ degli utenti a cui e’ concessa; ad esempio, per concedere agli utenti denominati USER1 e USER2 il permesso di modificare i dati della tabella studenti, si usa il comando:

GRANT UPDATE
ON STUDENTE
TO USER1, USER2;

la revoca dei permessi avviene con il comando REVOKE:

REVOKE UPDATE
ON STUDENTE
FROM USER1, USER2;
I permessi che possono essere concessi o revocati agli utenti vanno specificati dopo i comandi GRANT o REVOKE e sono i comandi gia’ visti:

ALTER: per aggiungere o togliere colonne da una tabella o modificare i tipi di dati
DELETE: per eliminare righe da una tabella
INDEX: per creare indici
INSERT: per inserire nuove righe
SELECT: per effettuare interrogazioni
UPDATE: per modificare i valori dei dati nella tabella
ALL: per concedere o revocare tutti i permessi visti sopra (es GRANT ALL ON STUDENTE TO USER1;)

le viste: in SQL e’ possibile decidere le modalita’ con le quali gli utenti possono vedere le tabelle del database, creando una finestra (vista) solo su alcuni dati contenuti in una o piu’ tabelle.

Una vista (view) quindi e’ una tabella piu’ piccola ottenuta dalle tabelle di partenza, a disposizione di uno o piu’ utenti che su di essa possono operare con le autorizzazioni viste prima.
Con le viste e’ possibile decidere che un utente debba avere una visine solo parziale dei dati registrati nel database: si elimina cosi’ il rischio di modifiche indesiderate, si proteggono i dati riservati a certi livelli aziendali e si evita che l’utente sia distratto da troppi dati rispetto alle elaborazioni che deve effettuare.
In ogni caso l’utente opera sulla vista come se fosse una vera e propria tabella, con tutti i comandi gia’ visti; le viste sono dinamiche: modifiche nei dati del database producono modifiche nei dati della vista e viceversa (solo le viste che contengono risultati di funzioni di aggregazione non possono essere modificate).
Per creare una vista:
CREATE VIEW nomevista
AS SELECT *
FROM nometabella
WHERE condizione;
per eliminare la vista:
DROP VIEW nomevista;

Integrita’ dei dati: quando si verificano situazioni di malfunzionamento o danneggiamento dei dati, si puo’ ricorrere ai seguenti comandi:
RECOVER TABLE: consente di recuperare una tabella da una copia di sicurezza
CHECK TABLE: controlla la corrispondenza dei dati di una tabella con i suoi indici
REPAIR TABLE: ricostruisce gli indici se il comando precedente non ha funzionato
le transazioni: sono una serie di operazioni eseguite su un database che devono essere eseguite in modo completo affinche’ l’elaborazione sia corretta; tali operazioni costituiscono un’unita’ logica di lavoro; ad esempio si considerino le transazioni bancarie come un prelievo allo sportello ATM: deve essere registrata l’operazione di prelievo e contemporaneamente deve essere aggiornato il saldo, controllando la disponibilita’; se una di tali operazioni non va a buon fine devono essere annullate anche le altre.

I comandi sono:

BEGIN TRANSACTION: da dichiarare prima della serie di istruzioni facenti parte dell’unita’ logica di lavoro: provoca la modifica prevista a tutte le righe del database, impedendo la modifica ad altri utenti
COMMIT: conferma la fine di una transazione corretta
ROLLBACK: ripristina il database allo stato in cui era prima del comando BEGIN TRANSACTION, nel caso che i risultati della transazione non corrispondano a quelli attesi.

Tutte le istruzioni viste sopra sono SQL-ANSI, e in Access non funzionano!.

Interrogazioni (QL)

L’aspetto piu’ importante del linguaggio SQL e’ costituito dalla possibilita’ di porre interrogazioni alla base di dati in modo semplice per ritrovare le informazioni che interessano. Queste prestazioni sono fornite dal comando SELECT, che attiva le interrogazioni sulle tabelle e le operazioni relazionali per ottenere nuove tabelle.
Ricordiamo che le interrogazioni consistono in proiezioni, selezioni e congiunzioni sui dati delle tabelle
La struttura generale del comando e’:
SELECT attibuto1, attributo2, …
FROM nometabella
WHERE condizione;
I comandi che utilizzano SELECT sono vere e proprie query di interrogazione; noterete che, dopo averle create, il simbolo che vi compare a fianco e’ quello che normalmente in Access indica una interrogazione (Access le definisce query di selezione).
E’ opportuno notare come nel linguaggio SQL con poche righe di codice e’ possibile estrarre da una base di dati tutte le informazioni che interessano. Utilizzando un linguaggio di programmazione tradizionale (es Cobol), sarebbe necessario scrivere un elevato numero di righe di codice per ottenere le stesse informazioni. Gli esempi che seguono lo dimostrano!

Ordinamenti

In una SELECT si puo’ inserire la clausola ORDER BY per ottenere i risultati di un’interrogazione in ordine crescente (ASC) o decrescente (DESC); si puo’ ottenere l’ordinamento secondo una o piu’ chiavi specificate dopo la clausola stessa; L’ordinamento crescente e’ di default e quindi si puo’ evitare di specificare ASC dopo l’istruzione.
La clausola ORDER BY in genere e’ l’ultima istruzione di un comando SQL.
Esempio: elenco alfabetico degli studenti con cognome, nome e data di nascita (in caso di omonimia sul cognome, si richiede l’ordinamento sul nome):
SELECT COGNOME, NOME, DATA_NASCITA
FROM STUDENTE
ORDER BY COGNOME, NOME;

proiezione

Per realizzare una proiezione, accanto alla parola SELECT si elencano i nomi degli attributi da visualizzare; se si vogliono elencare tutti gli attributi di una tabella si puo’ usare il simbolo asterisco *; dopo FROM si indicano i nomi delle tabelle su cui operare;
esempio:

elenco degli studenti con cognome e nome:

SELECT COGNOME, NOME
FROM STUDENTE;

elenco di tutti i dati degli studenti:

SELECT *
FROM STUDENTE;

Elenco delle materie che sono state verificate con uno o piu’ voti:

SELECT MATERIA
FROM VOTI;

Il commando SELECT possiede due predicati: ALL e DISTINCT.
Il predicato ALL indica la richiesta di ottenere come risultato dell’interrogazione tutte le righe che soddisfano le condizioni indicate nel comando; e’ di default, cioe’ se non viene specificato (come nel caso visto sopra) vengono visualizzate tutte le righe, anche quelle doppie; infatti l’istruzione vista sopra e’ equivalente a:
SELECT ALL MATERIA
FROM VOTI;
Il risultato e’ una tabella che contiene tutte le righe della tabella VOTI, quindi se una material e’ stata verificata piu’ volte, le righe si ripetono.
Il predicato DISTINCT riduce a una le righe uguali e ripetute nella tabella:
SELECT DISTINCT MATERIA
FROM VOTI;
Con SELECT è possible anche visualizzare un campo calcolato; data la tabella ARTICOLO(ID,NOME,PREZZOUNITARIO,GIACENZA) è possibile ottenre il valore per ogni articolo:
SELECT NOME, PREZZOUNITARIO * GIACENZA
FROM ARTICOLO
L’intestazione della colonna contenente il valore sara “Expr1000…”; per intestare correttamente la colonna si può utilizzare una clausola particolare: AS (v.oltre)

selezione

Per realizzare una selezione su una tabella, dopo WHERE si specifica una o piu’ condizioni che i campi delle righe devono soddisfare (si possono usare anche piu’ condizioni combinando gli operatori AND, OR, NOT).
Esempi:

Visualizzare i dati degli studenti di Cremona:

SELECT *
FROM STUDENTE
WHERE CITTA='CREMONA';

Visualizzare tutti i dati degli studenti nati tra due date da specificare (e’ una selezione parametrica):

SELECT *
FROM STUDENTE
WHERE DATA_NASCITA>=[inserire la prima data] AND DATA_NASCITA<=[inserire la seconda data];

Visualizzare gli studenti di Cremona nati dopo una certa data:

SELECT *
FROM STUDENTE
WHERE CITTA='CREMONA' AND DATA_NASCITA>=[inserire una data];
Inoltre e’ possibile combinare le operazioni tra loro; ad esempio:

Visualizzare nomi e cognomi degli alunni di Cremona (e’ una proiezione con selezione):

SELECT COGNOME, NOME
FROM STUDENTE
WHERE CITTA='CREMONA';
La tabella che si ottiene da un’interrogazione possiede delle intestazioni di colonna con il nome degli attributi; se si vuole modificare tale intestazione si scrive il nome del campo seguito dalla clausola AS e dalla nuova intestazione. AS consente di creare un ALIAS per un campo, un tabella, un calcolo…
Esempio: nella tabella STUDENTI abbiamo un campo COD_STUD; se con un’interrogazione vogliamo visualizzare cognome, nome e codice di tutti gli studenti, mettendo come intestazione di colonna “codice” invece di “cod_stud”, possiamo scrivere:
SELECT COGNOME, NOME, COD_STUD AS CODICE
FROM STUDENTE;
Questa clausola serve anche per ottenere i campi calcolati!
esempio: avendo a disposizione la data di nascita degli studenti, vogliamo ottenere l’eta’ (e’ un campo calcolato):
SELECT COGNOME, NOME, YEAR(NOW())-YEAR(DATA_NASCITA) AS ETA
FROM STUDENTE;
(YEAR è una funzione che estrae da un tipo data solo l’anno)
esempio:
SELECT NOME, PREZZOUNITARIO * GIACENZA AS VALORE
FROM ARTICOLO
esempio:
aumentare di un punto tutti i voti di una certa materia (e’ una proiezione su materia, data e voto modificato, con una selezione su materia):
SELECT MATERIA, DATA, NUMERO+1 AS NUOVO
FROM VOTI
WHERE MATERIA=[inserire il nome della materia];
Inoltre, come vedremo più avanti, AS viene utilizzata anche per creare un ALIS di una tabella (v. SELF JOIN).

Clausola TOP (SOLO PER Access):
SELECT TOP numero_intero: consente di visualizzare i primi record specificati con “intero”; ad esempio, visualizzare il voto più alto:
SELECT TOP 1 VERIFICA.VOTO, VERIFICA.DATA
FROM VERIFICA
ORDER BY VERIFICA.VOTO DESC;
Avendo ordinato VERIFICA in ordine decrescente su VOTO, il voto più alto è il primo; TOP 1 consente di visualizzarlo.
Visualizzare i primi tre voti voti più alti:
SELECT TOP 3 VERIFICA.VOTO, VERIFICA.DATA
FROM VERIFICA
ORDER BY VERIFICA.VOTO DESC;
SELECT TOP numero PERCENT: consente di visualizzare una percentuale dei dati; ad esempio, visualizzare il primo 25% dei voti:
SELECT TOP 25 PERCENT VERIFICA.VOTO, VERIFICA.DATA
FROM VERIFICA
ORDER BY VERIFICA.VOTO DESC;

congiunzione

Il comando SELECT puo’ operare su piu’ tabelle, indicandone i nomi (separati da virgola) dopo la parola FROM; scrivendo poi dopo la parola WHERE i nomi degli attributi che si corrispondono nelle due tabelle (chiave primaria e chiave esterna) legati dal segno =, si realizza l’operazione di congiunzione secondo l’attributo comune.
Esempio: per ottenere l’elenco di tutti i voti con il nome e cognome dell’alunno che li ha riportati:
SELECT MATERIA, NUMERO, DATA, COGNOME, NOME
FROM VOTI, STUDENTE
WHERE VOTI.COD_STUD=STUDENTE.COD_STUD;
Per esigenze di chiarezza o perche’ si e’ usato lo stesso nome per un attributo in tabelle diverse, e’ opportuno specificare il nome delle tabella e il nome dell’attributo separandoli con un punto. Cio’ non e’ obbligatorio quando si usano nomi diversi.
La congiunzione appena vista e’ un esempio di equi-join: vengono combinate solo le righe per le quali si possono trovare valori uguali negli attributi che si corrispondono; nella versione del linguaggio SQL per Access in ambiente Windows l’equi-join viene indicato con INNER JOIN; in fatti l’istruzione precedente e’ equivalente:
SELECT VOTI.MATERIA, VOTI.NUMERO, VOTI.DATA, STUDENTE.COGNOME, STUDENTE.NOME
FROM STUDENTE INNER JOIN VOTI ON STUDENTE.COD_STUD = VOTI.COD_STUD;
Si possono anche omettere i nomi delle tabelle, dove cio’ non genera confusione:
SELECT MATERIA, NUMERO, DATA, COGNOME, NOME
FROM STUDENTE INNER JOIN VOTI ON STUDENTE.COD_STUD = VOTI.COD_STUD;
Inoltre e’ possibile realizzare anche join esterni (left e right) e self join (v. oltre).

Se devo congiungere più tabelle, si procede in questo modo: avendo le tabelle
STUDENTE(ID, COGNOME, NOME, INDIRIZZO, CITTA, DATANASCITA)
VERIFICA(VOTO,DATA, IDSTUDENTE, IDMATERIA)
MATERIA(ID,NOMEM)
SELECT …
FROM STUDENTE INNER JOIN(VERIFICA INNER JOIN MATERIA ON VERIFICA.IDMATERIA=MATERIA.ID) ON STUDENTE.ID=VERIFICA.IDSTUDENTE
WHERE…
OPPURE:
SELECT …
FROM STUDENTE, VERIFICA, MATERIA
WHERE STUDENTE.ID=VERIFICA.IDSTUDENTE AND VERIFICA.IDMATERIA=MATERIA.ID)

Dopo aver visto la sintassi delle operazioni di selezione, proiezione e congiunzione, possiamo realizzare la combinazione delle diverse operazioni; ad esempio, si vuole visualizzare l’elenco degli alunni con cognome, nome, voto e data che sono stati verificati in una certa materia:
SELECT COGNOME, NOME, NUMERO, DATA
FROM VOTI, STUDENTE
WHERE VOTI.COD_STUD=STUDENTE.COD_STUD
AND MATERIA=[inserire la materia scelta];
oppure:
SELECT COGNOME, NOME, NUMERO, DATA
FROM STUDENTE INNER JOIN VOTI ON STUDENTE.COD_STUD=VOTI.COD_STUD
WHERE MATERIA=[inserire la materia scelta];

Le operazioni relazionali generano una nuova tabella “temporanea” da quelle di partenza; se si vuole conservare tale tabella risultante dall’operazione, occorre aggiungere al comando SELECT la clausola INTO seguita dal nome da assegnare alla nuova tabella: ad esempio dall’interrogazione precedente generiamo una nuova tabella APPOGGIO, che potra’ essere utilizzata in altre operazioni:
SELECT MATERIA, NUMERO, COGNOME, NOME INTO APPOGGIO
FROM STUDENTE, VOTI
WHERE STUDENTE.COD_STUD=VOTI.COD_STUD
AND MATERIA=[inserire la materia scelta];
Le colonne della nuova tabella presentano gli stessi nomi, formati e dimensioni degli attributi delle tabelle di origine. Questa tabella potrà essere utilizzata anche per altre interrogazioni:
SELECT COGNOME, NOME
FROM APPOGGIO
In alternativa è possibile costruire una query su un’altra query, facendo riferimento al nome con cui la prima query è stata salvata; l’esempio precedente poteva essere realizzato in questo modo:
realizzo la prima query e la salvo come QUERY1:
SELECT MATERIA, NUMERO, COGNOME, NOME
FROM STUDENTE, VOTI
WHERE STUDENTE.COD_STUD=VOTI.COD_STUD
AND MATERIA=[inserire la materia scelta];
realizzo la seconda query su QUERY1:
SELECT COGNOME, NOME
FROM QUERY1

Le funzioni di aggregazione

Sono funzioni predefinite che agiscono sui valori contenuti nelle righe delle tabelle e che vengono utilizzate nelle interrogazioni.

COUNT: conta il numero di righe di una tabella, cioe’ la cardinalita’; deve essere specificato un attributo o il simbolo *;

ATTENZIONE: specificando un attributo vengono contate solo le righe che non hanno valore Null; utilizzando l’asteriscono vengono contate tutte le righe (quindi se un campo non può avere valore NUL, è indifferente usare COUNT(*) O count(nomecampo));
ad esempio contiamo il numero degli studenti registrati:
SELECT COUNT(*)
FROM STUDENTE;
che equivale a:
SELECT COUNT(COD_STUD)
FROM STUDENTE;
Poichè COD_STUD è CHIAVE PRIMARIA e non può essere null; non sarebbe invece equivalente:
SELECT COUNT(CITTA)
FROM STUDENTE;
se non abbiamo imposto che il campo CITTA debba essere NOT NULL!
per contare il numero di voti registrati nella tabella VOTI, escludendo quelli Null:
SELECT COUNT(NUMERO)
FROM VOTO;
E’ possibile combinare la funzione COUNT con una o piu’ condizioni, ottenendo il numero di righe della tabella che soddisfano la condizione; esempio:
numero di insufficienze:
SELECT COUNT(*)
FROM VOTI
WHERE NUMERO<6;
Ancora piu’ complicato: numero degli studenti di Cremona che hanno conseguito almeno la sufficienza in una certa materia:
SELECT COUNT(*)
FROM STUDENTE, VOTI
WHERE STUDENTE.COD_STUD=VOTI.COD_STUD
AND MATERIA=[inserire la materia scelta]
AND NUMERO>=6
AND CITTA='CREMONA';
Il risultato del conteggio puo’ essere descritto con una adeguata intestazione usando la clausola AS:
SELECT COUNT(*) AS SUFFICIENZE
FROM STUDENTE, VOTI
WHERE STUDENTE.COD_STUD=VOTI.COD_STUD
AND MATERIA=[inserire la materia scelta]
AND NUMERO>=6;
Viene visualizzata una colonna con intestazione ‘SUFFICIENZE’ e il risultato del calcolo.

SUM: restituisce la somma di tutti i valori contenuti in una colonna specificata come argomento della funzione (l’attributo deve essere numerico!); esempio: si vuole la somma di tutti i voti (non ha molto senso!) intestando la colonna come “somma”:

SELECT SUM(NUMERO) AS SOMMA
FROM VOTI;
L’argomento puo’ essere anche un’espressione numerica; ad esempio, data una tabella contenente i dati sugli articoli in magazzino e avente come attributi prezzo unitario e quantita’ si puo’ calcolare il valore complessivo:
SELECT SUM(PREZZO*QTA) AS TOTALE
FROM ARTICOLI;

AVG: restituisce il valore medio dei dati numerici contenuti in una colonna (avg= average); ad esempio, si vuole la media dei voti di una certa materia:

SELECT AVG(NUMERO)
FROM VOTI
WHERE MATERIA=[inserire la materia scelta];

MIN e MAX: restituiscono il valore minimo e massimo di una colonna numerica o alfanumerica specificata; ad esempio, si vuole il voto minimo tra tutte le materie:

SELECT MIN(NUMERO)
FROM VOTI;
(Per visualizzare anche la materia corrispondente occorre una interrogazione nidificata; v. oltre)
Le funzioni possono essere anche usate nella stessa interrogazione; ad esempio: voto massimo, minimo e medio:
SELECT MAX(NUMERO), MIN(NUMERO), AVG(NUMERO)
FROM VOTI;
o meglio ancora:
SELECT MAX(NUMERO) AS MASSIMO, MIN(NUMERO) AS MINIMO, AVG(NUMERO) AS MEDIA FROM VOTI;

ATTENZIONE:

in Access una funzione NON può contenere al suo interno un’altra funzione! Non è possibile: SUM(COUNT(*))
SQL-ANSI consente la seguente istruzione:

SELECT COUNT(DISTINCT CITTA)
FROM STUDENTE
Per ottenere il numero delle diverse città da cui provengono gli studenti; ma in SQL-Access non è possibile: possiamo ricorrere ad una prima query (QUERYA) con cui ottenere le diverse città:
SELECT DISTINCT CITTA
FROM STUDENTE;
e sulla QUERYA eseguiamo il conteggio dei record:
SELECT COUNT(*) AS [NUMERO CITTA DI PROVENIENZA]
FROM QUERYA

Raggruppamenti

Con le funzioni di aggregazione viste prima (sum, avg, count, max, min) e’ possibile ottenere i risultati richiesti raggruppati per un insieme di righe aventi lo stesso valore nell’attributo specificato, anziche’ operare su tutta la tabella; e’ un problema di rottura di codice. Si utilizza l’opzione GROUP BY seguita dal nome del campo su cui operare il raggruppamento: questa opzione produce una riga di risultato per ogni raggruppamento.
Esempio: elenco dei voti medi di ogni studente con codice dello studente:
SELECT COD_STUD, AVG(NUMERO)
FROM VOTI
GROUP BY COD_STUD;
Per visualizzare un’opportuna intestazione di colonna per il voto medio:
SELECT COD_STUD, AVG(NUMERO) AS [VOTO MEDIO]
FROM VOTI
GROUP BY COD_STUD;
Numero di materie verificate per ogni studente, con risultato del conteggio, codice degli studenti e cognome:
SELECT COUNT(MATERIA) AS [NUMERO DI VERIFICHE], VOTI.COD_STUD, STUDENTE.COGNOME
FROM VOTI, STUDENTE
WHERE VOTI.COD_STUD=STUDENTE.COD_STUD
GROUP BY VOTI.COD_STUD, STUDENTE.COGNOME;
(N.B: e’ necessario specificare i nomi delle tabelle prima dei nomi dei campi, perche’ COD_STUD e’ comune a entrambe le tabelle; inoltre e’ necessario specificare come criterio di raggruppamento sia il codice che il cognome )

La clausola GROUP BY puo’ essere integrata con HAVING seguita da una o piu’ condizioni: in questo modo si opera una selezione sui valori oggetto del raggruppamento; esempio:
elenco dei voti medi sufficienti di ogni studente, con indicazione del cognome:
SELECT VOTI.COD_STUD, STUDENTE.COGNOME, AVG(NUMERO) AS [VOTO MEDIO]
FROM VOTI, STUDENTE
WHERE VOTI.COD_STUD=STUDENTE.COD_STUD
GROUP BY VOTI.COD_STUD, STUDENTE.COGNOME
HAVING AVG(NUMERO)>=6;
HAVING assomiglia a WHERE ma opera in modo diverso: con “where” si pongono condizioni sulle righe della tabella, con “having” il controllo avviene sui risultati della funzione di aggregazione

Condizioni di ricerca

Utilizzando le clausole WHERE e HAVING si indicano le condizioni di ricerca rispetto alle righe o ai raggruppamenti; nella scrittura delle condizioni abbiamo utilizzato i simboli >, <, =, <>, >=, <=. Si possono utilizzare piu’ condizioni di ricerca legandole con gli operatori AND, OR, NOT.
Nelle condizioni di ricerca si possono inoltre utilizzare parole riservate del linguaggio SQL che rendono piu’ efficienti le ricerche.

BETWEEN… AND…controlla se un valore e’ compreso in un intervallo, inclusi gli estRemi; esempio:

elenco degli studenti nati tra due date:
SELECT COGNOME, NOME
FROM STUDENTE
WHERE DATA_NASCITA BETWEEN[inserire la prima data] AND[inserire la seconda data];
e’ equivalente all’istruzione:
SELECT COGNOME, NOME
FROM STUDENTE
WHERE DATA_NASCITA>=[inserire la prima data] AND DATA_NASCITA<=[inserire la seconda data];

LIKE confronta il valore di un attributo di tipo carattere con un modello di stringa che puo’

contenere il carattere jolly * , per indicare una sequenza qualsiasi di caratteri
Esempio: elenco degli studenti che iniziano con la lettera R con cognome e nome:
SELECT COGNOME, NOME
FROM STUDENTE
WHERE COGNOME LIKE 'R*';
Esempio: elenco degli studenti il cui cognome inizia con una lettera da specificare:
SELECT COGNOME, NOME
FROM STUDENTE
WHERE COGNOME LIKE [inserire la prima lettera del cognome] & '*';
Between e like possono essere preceduti da NOT per indicare criteri di ricerca opposti

Interrogazioni nidificate

In SQL e’ possibile realizzare interrogazioni complesse ponendo un’interrogazione (subquery o di appoggio) all’i0nterno di un’altra, il cui risultato deve essere confrontato con la principale. Cio’ spiega il termine structured perche’ il linguaggio consente di costruire interrogazioni ben strutturate.
Ad esempio, le funzioni di ricerca del massimo o minimo restituiscono semplicemente il valore piu’ grande o piu’ piccolo di una tabella; ma in genere interessa conoscere anche l’oggetto a cui si riferiscono; si costruisce quindi una sottointerrogazione che cerca il massimo o minimo; tale subquery viene quindi utilizzata all’interno di un’altra interrogazione come valore di confronto (si pensi a come funziona l’algoritmo di ricerca del massimo o minimo), restituendo le righe che soddisfano la condizione.

visualizzare la materia con relativo voto minimo:

SELECT MATERIA, NUMERO
FROM VOTI
WHERE NUMERO=(SELECT MIN(NUMERO)
FROM VOTI);

visualizzare i voti superiori al voto medio, con data e nome della materia:

SELECT DATA, MATERIA, NUMERO
FROM VOTI
WHERE NUMERO>(SELECT AVG(NUMERO)
FROM VOTI);

visualizzare cognome e nome dello studente che ha conseguito il voto piu’ basso:

SELECT COGNOME, NOME
FROM STUDENTE, VOTI
WHERE STUDENTE.COD_STUD=VOTI.COD_STUD
AND NUMERO=(SELECT MIN(NUMERO)
FROM VOTI);
(naturalmente lo stesso risultato poteva essere ottenuto utilizzando una query su tabella di appoggio o una precedente query. V. esempi successivi)

Interrogazioni complesse con utilizzo di una tabella di appoggio o costruite su un’altra query

Alcune interrogazioni, per la loro complessita’, possono essere realizzate creando una tabella temporanea o una query su cui costruirei un’altra query; questa tabella/query verra’ poi utilizzata nell’interrogazione principale.

Ad esempio, la query precedente (cognome e nome dello studente con voto piu’ basso) poteva essere realizzata cosi’:
si realizza una tabella APPOGGIO contente il risultato di una subquery che, agendo sulla tabella VOTI, determina il voto minimo (nel un solo valore nel campo MINIMO):

SELECT MIN(NUMERO) AS MINIMO INTO APPOGGIO
FROM VOTI;

si realizza l’interrogazione principale utilizzando la tabella STUDENTE, VOTI e APPOGGIO: si congiunge la tabella STUDENTE con VOTI e si cerca la riga con il NUMERO uguale al voto minimo della tabella APPOGGIO, visualizzando relativo COGNOME e NOME dello studente:

SELECT COGNOME, NOME
FROM STUDENTE, VOTI, APPOGGIO
WHERE STUDENTE.COD_STUD=VOTI.COD_STUD
AND NUMERO=MINIMO;
OPPURE: creo QUERY1:
SELECT MIN(NUMERO) AS MINIMO
FROM VOTI;
e poi:
SELECT COGNOME, NOME
FROM STUDENTE, VOTI, QUERY1
WHERE STUDENTE.COD_STUD=VOTI.COD_STUD
AND NUMERO=MINIMO;

Consideriamo un altro esempio: visualizzare cognome e nome dello studente che presenta il voto medio piu’ alto:

Costruiamo una tabella APPOGGIO contenente il risultato della subquery che, per ogni studente, contiene il codice e calcola il rispettivo voto medio:

SELECT COD_STUD, AVG(NUMERO) AS MEDIA INTO APPOGGIO
FROM VOTI
GROUP BY COD_STUD;

Ricerchiamo il Massimo dei voti medi (MEDIA) con una interrogazione nidificata (si potrebbe usare anche un’altra tabella di appoggio), con cui visualizziamo anche cognome e nome dello studente:

SELECT COGNOME, NOME
FROM STUDENTE, APPOGGIO
WHERE STUDENTE.COD_STUD=APPOGGIO.COD_STUD
AND MEDIA=(SELECT MAX(MEDIA)
FROM APPOGGIO);

visualizzare il numero di materie verificate (contando solo le materie diverse tra loro): nella tabella VOTI il nome della materia si puo’ ripetere, quindi utilizzando COUNT su tale tabella potrebbero essere contate piu’ volte le stesse materie; occorre utilizzare una tabella appoggio in cui memorizzare i nomi delle materie scelte con la clausola DISTINCT:

SELECT DISTINCT MATERIA INTO APPOGGIO
FROM VOTI;
e successivamente si effettua il conteggio sulla tabella APPOGGIO:
SELECT COUNT(*) AS [N° MATERIE]
FROM APPOGGIO;

comunicare, con la stessa interrogazione, il numero di studenti e il numero di verifiche complessive effettuate: si tratta di contare i record di STUDENTI e i record di VOTI, comunicando contemporaneamente i due risultati del conteggio; ATTENZIONE, in questo caso non si possono congiungere le due tabelle in quanto si otterrebbe una tabella con tante righe quante sono quelle della tabella piu’ lunga: effettuando il conteggio del numero di studenti e dei voti sulla congiunzione, si ottiene lo stesso risultato per entrambe le richieste.

In questo caso occorre memorizzare il risultato di ogni conteggio in una tabella APPOGGIO distinta: per il numero degli studente:
SELECT COUNT(*) AS NSTUD INTO APPOGGIO1
FROM STUDENTE;
e per il numero di verifiche:
SELECT COUNT(*) AS NVERIFICHE INTO APPOGGIO2
FROM VOTI;
quindi si visualizza il contenuto di APPOGGIO1 e APPOGGIO2 con una sola interrogazione, senza congiungere le due tabelle:
SELECT NSTUD AS [N° STUDENTI], NVERIFICHE AS [N° VERIFICHE]
FROM APPOGGIO1, APPOGGIO2;

NOTE E APPROFONDIMENTI

tipi di dati

Non e’ possibile creare una tabella in modo parametrico
Dati numerici floating point, memorizzati in forma esponenziale: 3E+3 significa che ci sono tre zeri dopo il tre:3000; 3.5E-4 significa 0.00035; la parte numerica indicata (es3 o 3.5) e’ la mantissa
Tipi di dati: utilizzando SQL in Access e’ possibile definire anche i seguenti tipi di dati:

COUNTER (contatore): 4 byte, si incrementa automaticamente quando si inserisce un nuovo record; ad esempio, per creare la tabella FATTURA(NUMERO, DATA, IMPORTO, PAGATO, CODCLI):
CREATE TABLE FATTURA
(NUMERO COUNTER PRIMARI KEY,
DATA DATE,
IMPORTO CURRENCY,
PAGATO CHAR(2),
CODCLI CHAR(16));
Per inserire una fattura, in modo che il campo NUMERO si incrementi automaticamente, non si deve dichiarare tale campo tra quelli da scrivere:
INSERT INTO FATTURA ( DATA, IMPORTO, PAGATO, CODCLI )
VALUES ([data fattura:], [importo:], [pagato (SI/NO):], [codice cliente:]);
(se la KP e’ counter, la corrispondente KE deve essere integer; v. Access)
CURRENCY (valuta)

Durante l’inserimento dei dati non si puo’ usare il simbolo *, ma occorre elencare tutti i campi in cui vanno inseriti dei dati
Con la clausola AS, per indicare un’intestazione di colonna contenente spazi, si racchiude tra [ ]
Nelle condizioni, per i valori fissi, si usa:
le stringhe tra apici ‘ ‘
le date tra # # con l’anno a 4 cifre
i numeri, niente!

Funzioni di aggregazione:

una funzione di aggregazione non puo’ contenere al suo interno un’altra funzione di aggregazione; si dovra’ utilizzare una tabella appoggio o una select nidificata

Raggruppamenti: quando si utilizza la clausola GROUP BY, tutti gli attributi che compaiono nella lista dopo SELECT devono essere inclusi nella clausola ‘group by’, oppure devono essere argomenti della funzione di aggregazione; cio’ e’ dovuto al fatto che l’attributo oggetto del raggruppamento si ripete, soprattutto se prima e’ stata operata una congiunzione (v. pag.11)

Per stampare il listato SQL in Access del database

scegliere dal menu Strumenti, Analizza, Analizzatore; scegliere la scheda Query ( o tabella …), premere Seleziona Tutto e Opzioni; lasciare il segno di spunta solo a SQL, premere OK e confermare con Ok nella finestra principale. Vengono generati tanti report quanti sono gli oggetti da stampare; per migliorare la stampa e salvare il documento conviene importarlo in Word: premere il pulsante W e scegliere pubblica in Word: in questo modo e’ possibile formattare il testo per la stampa e salvarlo come documento.

Caratteri jolly:

in SQL-ANSI esistono altri caratteri jolly, oltre a *:
? sostituisce un singolo carattere; ad esempio, elenco dei codici degli studenti che iniziano con AB e una cifra qualunque:
SELECT COD_STUD
FROM STUDENTE
WHERE COD_STUD LIKE 'AB' & '?';
# sostituisce una singola cifra (0-9); quindi poteva essere utilizzato anche nell’esempio visto sopra:
SELECT COD_STUD
FROM STUDENTE
WHERE COD_STUD LIKE 'AB' & '#';
In SQL-Access, per ottenere ad esempio l’elenco degli studenti il cognome inizia per R:
SELECT *
FROM STUDENTE
WHERE COGNOME LIKE ‘R*’
Oppure:
SELECT *
FROM STUDENTE
WHERE COGNOME LIKE ‘R’&’*’
Per avere gli studenti il cui cognome inizia con una lettera scelta:
SELECT *
FROM STUDENTE
WHERE COGNOME LIKE [inserire iniziale]&’*’
Per ottenere la parte finale di una stringa
SELECT *
FROM tabella
WHERE ‘*’&[PARTE FINALE STRINGA]

Utilizzo di parentesi

Attenzione all’utilizzo corretto delle parentesi quando si realizzano interrogazioni con piu’ operatori logici; ad esempio, cognome e nome degli studenti che hanno conseguito un certo voto in inglesse o in informatica:
SELECT DISTINCT COGNOME, NOME
FROM STUDENTE, VOTI
WHERE STUDENTE.COD_STUD=VOTI.COD_STUD
AND NUMERO=[inserire voto da controllare]
AND (MATERIA='INGLESE' OR MATERIA='INFORMATICA');
senza la parentesi, le operazioni logiche sarebbero state svolte in sequenza (studenti che hanno preso un certo voto in inglese, oppure la cui materia e’ informatica indipendentemente dal voto conseguito) producendo risultati non corretti

embedded SQL

Finora si e’ considerato SQL utilizzato in modo interattivo; ma le istruzioni possono essere inserite anche all’interno dei programmi scritti con i principali linguaggi (funzionamento batch) . Tuttavia talvolta esistono difficolta’ a causa della non prefetta compatibilita’ tra il linguaggio batch e SQL. Ogni linguaggio prevede regole sintattiche particolari e comunque l’istruzione SQL in genere deve essere preceduta da EXEC SQL.
Ad esempio, in un programma COBOL:
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 VAR1 PIC ….
01 VAR2…….
02……
………..
EXEC SQL END DECLARES SECTION END-EXEC.
………
PROCEDURE DIVISION.
INIZIO.
EXEC SQL SELECT campo1, campo2, …….
FROM tabella1, tabella2….. forma generale dell’istruzione SQL
WHERE condizione
END-EXEC.

differenza tra due date o due orari

Data una tabella DURATA(DATAINIZIO, DATAFINE)
CREATE TABLE DURATA
(DATAINIZIO DATE,
DATAFINE DATE);

SELECT [DATAFINE]-[DATAINIZIO] AS TEMPO
FROM DURATA;

DATAINIZIO	DATAFINE
28/10/00	31/10/00
01/01/00	31/12/00
22/02/00	25/02/01

GIORNI

365

369

La differenza tra due date restituisce il tempo in GIORNI

Data la tabella ORARIO (ORAINIZIO,ORAFINE)
CREATE TABLE ORARIO
(ORAINIZIO TIME,
ORAFINE TIME);

SELECT ORAFINE-ORAINIZIO AS TEMPO
FROM ORARIO;

ORAINIZIO	ORAFINE
12.00.00	13.00.00
14.30.00	16.28.00

TEMPO

0.0416666667

0.0819444444

Produce il tempo in frazione di giorno-ora, in 24-esimi; la restante parte in 60-esismi per i minuti e 60-esimi per i secondi.
Quindi per convertire tutto in minuti si moltiplica per 24*60=1440 e si ottiene il tempo in minuti
SELECT (ORAFINE-ORAINIZIO)*24*60 AS [TEMPO IN MINUTI]
FROM ORARIO;
si ottiene:

TEMPO IN MINUTI

118

approfondimenti sulle query (operatori IN e ANY)

IN controlla se un valore appartiene ad un insieme specificato di valori; puo’ essere preceduto da NOT per indicare la condizione opposta;
ad esempio: si vuole l’elenco degli alunni, con cognome e nome, che hanno voti in informatica e inglese (senza righe doppie):
SELECT DISTINCT COGNOME, NOME
FROM STUDENTE, VOTI
WHERE STUDENTE.COD_STUD=VOTI.COD_STUD
AND MATERIA IN ('INFORMATICA', 'INGLESE');
elenco degli studenti (senza righe doppie) che non hanno voti inglese e informatica:
SELECT DISTINCT COGNOME, NOME
FROM STUDENTE, VOTI
WHERE STUDENTE.COD_STUD=VOTI.COD_STUD
AND MATERIA NOT IN ('INFORMATICA', 'INGLESE');

Elenco degli studenti con cognome e nome e rispettivo numero di verifiche, che presentano piu’ di un certo numero di verifiche:
SELECT COGNOME, NOME, COUNT(NUMERO) AS [N° VERIFICHE]
FROM STUDENTE, VOTI
WHERE STUDENTE.COD_STUD=VOTI.COD_STUD
GROUP BY COGNOME, NOME
HAVING COUNT(NUMERO)> [inserire numero minimo di verifiche];
Elenco degli studenti con cognome, nome e relativo voto massimo:
SELECT COGNOME, NOME, MAX(NUMERO) AS [VOTO MASSIMO]
FROM STUDENTE, VOTI
WHERE STUDENTE.COD_STUD=VOTI.COD_CODSTUD
GROUP BY COGNOME, NOME;
(per ogni gruppo di studenti viene cercato il voto massimo)
per poter visualizzare anche la rispettiva materia, relativa al voto massimo, devo ricorrere ad una tabella di appoggio:
SELECT COD_STUD, MAX(NUMERO) AS MASSIMO INTO APPOGGIO
FROM VOTI
GROUP BY COD_STUD;
Che restituisce il voto massimo di ogni studente e relativo codice;
SELECT COGNOME, NOME, MASSIMO, MATERIA
FROM STUDENTE, VOTI, APPOGGIO
WHERE STUDENTE.COD_STUD=VOTI.COD_STUD
AND VOTI.COD_STUD=APPOGGIO.COD_STUD
AND NUMERO=MASSIMO;
OPPURE: si utilizza una interrogazione nidificata che restituisce piu’ valori (uno per ogni studente); il voto di ogni studente viene confrontato con il rispettivo risultato della subquery; in questo caso occorre la clausola ANY, che permette il confronto con uno o piu’ valori di una subquery:
SELECT COGNOME, NOME, MATERIA
FROM STUDENTE, VOTI
WHERE STUDENTE.COD_STUD=VOTI.COD_STUD
AND NUMERO= ANY(SELECT MAX(NUMERO)
FROM VOTI
GROUP BY COD_STUD);
ANY indica che la subquery puo’ restituire zero, uno o un insieme di valori e che la condizione di ricerca e’ vera se il confronto e’ vero per almeno uno dei valori restituiti;

Numero di studenti che presentano voto medio sufficiente (si usa una tabella appoggio)
Primo modo:
SELECT COD_STUD, AVG(NUMERO) AS MEDIA
INTO APPOGGIO
FROM VOTI
GROUP BY COD_STUD
HAVING AVG(NUMERO) >= 6;
Genera la tabella appoggio, contenente solo i dati che interessano, su cui effettuare il conteggio;
SELECT COUNT(*) AS [N° STUDENTI]
FROM APPOGGIO;
Secondo modo:
SELECT COD_STUD, AVG(NUMERO) AS MEDIA
INTO APPOGGIO
FROM VOTI
GROUP BY COD_STUD;
Genera una tabella appoggio contenente codici e voti medi di tutti gli studenti;
SELECT COUNT(*) AS [N° STUDENTI]
FROM APPOGGIO
WHERE MEDIA >=6;
Conta solo gli studenti con media sufficiente.

TIPI DI JOIN

esistono diversi tipi di congiunzione; noi normalmente usiamo l’equi-join o join interno (inner); in realtà l’equi join è un caso particolare di inner join, dove viene posta l’uguaglianza tra KP e KE

join interno (inner join o equi-join)

Vengono combinate solo le righe per le quali si possono trovare valori uguali nell’ attributo comune.
Avendo a disposizione le seguenti tabelle, con i dati:
STUDENTE:

COD_STUD	COGNOME	NOME	INDIRIZZO	CITTA	DATA_NASCITA	TELEFONO
AB1	ROSSI	LUCA	VIA VERDI, 4	MILANO	23/05/80	02657481
AB2	VERDI	LAURA	VIALE PO, 3	CREMONA	22/02/80	037256410
AB3	BIANCHI	PAOLO	VIA ROMA, 2	CREMONA	12/05/81	037256410
AB4	BLU	ROSA	PIAZZA LODI, 6	CREMONA	15/12/81	037258946

VOTI:

NUMERO	MATERIA	DATA	COD_STUD
5	INFORMATICA	22/10/00	AB1
7	INGLESE	23/10/00	AB1
8	INGLESE	02/11/00	AB1
7	INFORMATICA	23/10/00	AB2
8	INGLESE	22/12/00	AB2
6	STORIA	24/10/00	AB3
4	INFORMATICA	22/12/00	AB3

Si vuole realizzare la seguente interrogazione: elenco con cognome e nome degli studenti e rispettivo numero di voti
con la seguente istruzione (equi-join)
SELECT COGNOME, NOME, COUNT(NUMERO) AS [N° VERIFICHE]
FROM STUDENTE INNER JOIN VOTI ON STUDENTE.COD_STUD=VOTI.COD_STUD
GROUP BY COGNOME, NOME;
equivalente a:
SELECT COGNOME, NOME, COUNT(NUMERO) AS [N° VERIFICHE]
FROM STUDENTE, VOTI
WHERE STUDENTE.COD_STUD=VOTI.COD_STUD
GROUP BY COGNOME, NOME;
si ottiene:

COGNOME	NOME	N° VERIFICHE
BIANCHI	PAOLO	2
ROSSI	LUCA	3
VERDI	LAURA	2

Ma lo studente AB4, BLU ROSA (presente nella tabella STUDENTE) non viene visualizzato perche’ non ha voti nella tabella VOTI.
Per visualizzare anche i dati di questo studente occorre effettuare un diverso tipo di join

join esterno (left e right)

left join

elenca tutte le righe della prima tabella (sinistra, left) congiungendo tra le righe della seconda tabella (destra) solo quelle per le quali si trovano valori corrispondenti per l’attributo comune.
Quindi, per l’interrogazione precedente, la query:
SELECT COGNOME, NOME, COUNT(NUMERO) AS [N° VERIFICHE]
FROM STUDENTE LEFT JOIN VOTI ON STUDENTE.COD_STUD=VOTI.COD_STUD
GROUP BY COGNOME, NOME;
Produce il risultato desiderato:

COGNOME	NOME	N° VERIFICHE
BIANCHI	PAOLO	2
BLU	ROSA	0
ROSSI	LUCA	3
VERDI	LAURA	2

Quindi la tabella STUDENTE, dichiarata prima dell’istruzione di congiunzione, viene considerata come la tabella di sinistra.

Ma l’istruzione:
SELECT COGNOME, NOME, COUNT(NUMERO) AS [N° VERIFICHE]
FROM VOTI LEFT JOIN STUDENTE ON STUDENTE.COD_STUD=VOTI.COD_STUD
GROUP BY COGNOME, NOME;
In cui si considera con tabella di sinistra VOTI, non produce il risultato atteso

COGNOME	NOME	N° VERIFICHE
BIANCHI	PAOLO	2
ROSSI	LUCA	3
VERDI	LAURA	2

Perche la tabella VOTI, dichiarata prima del join, viene considerate come tabella di sinistra
In alternativa si puo’ realizzare un join destro

right join

Restituisce comunque tutte le righe della seconda tabella (destra, right) e di queste congiunge con le righe della prima tabella (sinistra) solo quelle per le quali si possono trovare valori corrispondenti per l’attributo comune.
L’interrogazione precedente in SQL e’:
SELECT COGNOME, NOME, COUNT(NUMERO) AS [N° VERIFICHE]
FROM VOTI RIGHT JOIN STUDENTE ON STUDENTE.COD_STUD=VOTI.COD_STUD
GROUP BY COGNOME, NOME;
Con cui si ottiene il risultato desiderato:

COGNOME	NOME	N° VERIFICHE
BIANCHI	PAOLO	2
BLU	ROSA	0
ROSSI	LUCA	3
VERDI	LAURA	2

Perche’ la tabella STUDENTE, dichiarata dopo il join, viene considerata tabella di destra.

Invece l’istruzione:
SELECT COGNOME, NOME, COUNT(NUMERO) AS [N° VERIFICHE]
FROM STUDENTE RIGHT JOIN VOTI ON STUDENTE.COD_STUD=VOTI.COD_STUD
GROUP BY COGNOME, NOME;
Non produce il risultato corretto:

COGNOME	NOME	N° VERIFICHE
BIANCHI	PAOLO	2
ROSSI	LUCA	3
VERDI	LAURA	2

Perche’ viene la tabella VOTI viene considerata tabella di destra.
Quindi e’ importante tenere conto della posizione (destra o sinistra) delle tabelle!

self-join

È data la tabella PERSONALE; il campo DIRIGENTE contiene la MATRICOLA della persona che svolge il ruolo di dirigente rispetto al dipendente considerato. Le informazioni sui dirigenti sono contenute nella stessa tabella perché i dirigenti sono anche dipendenti. Il campo DIRIGENTE è vuoto nel record del dipendente che è dirigente, perché non può essere dirigente di se stesso.
Inoltre il campo non contiene il cognome del dirigente perché, se ci sono casi di omonimia, occorrerebbe anche il nome, con ridondanza dei dati.

create table PERSONALE
(MATRICOLA char(5) primary key,
COGNOME char(20),
NOME char(20),
STIP integer,
DIRIGENTE char(5));

corso sql

si vuole ottenere l’elenco con cognome e nome dei dipendenti (che non sono dirigenti), insieme al cognome (non la matricola!) del rispettivo dirigente.
Si devono usare gli ALIAS per il nome della tabella, cioè si indica con due nomi diversi la stessa tabella utilizzando la clausola AS nel FROM della SELECT:
SELECT TAB1.COGNOME AS [COGNOME DIPENDENTE], TAB1.NOME AS [NOME DIPENDENTE], TAB2.COGNOME AS [COGNOME DIRIGENTE]
FROM PERSONALE AS TAB1, PERSONALE AS TAB2
WHERE TAB1.DIRIGENTE=TAB2.MATRICOLA;
oppure con INNER JOIN:
SELECT TAB1.COGNOME AS [COGNOME DIPENDENTE], TAB1.NOME AS [NOME DIPENDENTE], TAB2.COGNOME AS [COGNOME DIRIGENTE]
FROM PERSONALE AS TAB1 INNER JOIN PERSONALE AS TAB2
ON TAB1.DIRIGENTE=TAB2.MATRICOLA;

corso sql
(l’ordine è irrilevante, perché dopo aver creato le copie delle tabella con gli alias, si realizza di fatto un equi-join)

OPPURE:
SELECT TAB1.COGNOME AS [COGNOME DIRIGENTE], TAB1.NOME AS [NOME DIRIGENTE], TAB2.COGNOME AS [COGNOME DIPENDENTE], TAB2.NOME AS [NOME DIPENDENTE]
FROM PERSONALE AS TAB1, PERSONALE AS TAB2
WHERE TAB1.MATRICOLA=TAB2.DIRIGENTE;

corso sql

E’ sufficiente rinominare anche solo una tabella:
SELECT TAB1.COGNOME AS [COGNOME DIRIGENTE], TAB1.NOME AS [NOME DIRIGENTE], PERSONALE.COGNOME AS [COGNOME DIPENDENTE], PERSONALE.NOME AS [NOME DIPENDENTE]
FROM PERSONALE AS TAB1 INNER JOIN PERSONALE
ON TAB1.MATRICOLA=PERSONALE.DIRIGENTE;

Altri esempi

Data la tabella CITTADINO,dove CONIUGE contiene il codice del cittadino (corrisponde alla KP):

create table CITTADINO
(CODICE char(5) primary key,
COGNOME char(20),
NOME char(20),
DATANASCITA date,
STATOCIVILE char(1),
CONIUGE char(5));

corso sql

si vuole ottenere l’elenco dei cittadini (coniugati), con cognome e nome e cognome e nome del coniuge:
SELECT TABA.COGNOME, TABA.NOME, TABB.COGNOME AS [COGNOME CONIUGE], TABB.NOME AS [NOME CONIUGE]
FROM CITTADINO AS TABA, CITTADINO AS TABB
WHERE TABA.CONIUGE=TABB.CODICE;

corso sql

OPPURE:
SELECT TABA.COGNOME, TABA.NOME, TABB.COGNOME AS [COGNOME CONIUGE], TABB.NOME AS [NOME CONIUGE]
FROM CITTADINO AS TABA, CITTADINO AS TABB
WHERE TABA.CODICE=TABB.CONIUGE;

corso sql

in questo modo però otteniamo delle righe uguali anche se con attributi in posizioni diverse (di fatto la riga 1 è uguale alla 3 e la 2 è uguale alla 4); per eliminare le righe uguali è sufficiente imporre la condizione:
SELECT TABA.COGNOME, TABA.NOME, TABB.COGNOME AS [COGNOME CONIUGE], TABB.NOME AS [NOME CONIUGE]
FROM CITTADINO AS TABA INNER JOIN CITTADINO AS TABB
ON TABA.CODICE=TABB.CONIUGE

WHERE TABA.COGNOME>TABB.COGNOME

Data la tabella PROVINCIA, dove CAPOREG contiene il codice della provincia capoluogo di regione (corrisponde a CODP ed è vuoto nel caso del comune che è anche capoluogo di provincia):

create table PROVINCIA
(CODP char(3) primary key,
NOMEP char(20),
CAPOREG char(3));

corso sql

Si vuole ottenere l’elenco delle province con nome e nome del rispettivo capoluogo di regione:
SELECT TAB1.NOMEP AS COMUNE, TAB2.NOMEP AS [CAPOLUOGO REGIONE]
FROM PROVINCIA AS TAB1, PROVINCIA AS TAB2
WHERE TAB1.CAPOREG=TAB2.CODP;

corso sql

OPPURE:
SELECT TAB1.NOMEP AS [CAPOLUOGO], TAB2.NOMEP AS COMUNE
FROM PROVINCIA AS TAB1, PROVINCIA AS TAB2
WHERE TAB1.CODP=TAB2.CAPOREG;

corso sql
per ottenere solo i capoluoghi di regione invece è sufficiente la seguente interrogazione:
SELECT NOMEP
FROM PROVINCIA
WHERE CAPOREG IS NULL

PRECISAZIONE SU TIPI DI DATI E CORRISPONDENZE ACCESS,SQL-ANSI

In MS-Access è possibile dichiarare numerosi tipi di dati che non hanno sempre corrispondenza con SQL standard proposta dall’ANSI; tuttavia, utilizzando SQL all’interno di Access (SQL-ACCESS) è consentito dichiarare alcuni tipi di dati non previsti dall’ANSI, come in tabella:

ACCESS	SQL-ANSI	SQL-ACCESS
Testo	CHAR(…)	CHAR(…)
Memo	Non disponibile	MEMO
Data	DATE oppure TIME	DATE/TIME
Valuta	Non disponibile	MONEY o CURRENCY
Contatore	Non disponibile	COUNTER
Si/no	BIT	BIT
Byte	Non disponibile	BYTE
Intero	SMALLINT	SMALLINT
Intero lungo	INT	INT
Precisione singola	Non disponibile	SINGOLA
Precisione doppia	DOUBLE	DOUBLE
Oggetto OLE	Non disponibile	IMAGE

Fonte: www.galileicrema.it

Fine articolo Corso SQL

Corso SQL

IL LINGUAGGIO SQL

DISPENSE DIDATTICHE

a cura della

Prof.ssa R. B. SCHIAVARELLI

GENERALITA’ SUL LINGUAGGIO SQL

Il linguaggio SQL (Structured Query Language) è un linguaggio divenuto lo standard per creare e manipolare basi di dati relazionali. Questo linguaggio assolve alle funzioni di:

DDL (Data Definition Language) che prevede le istruzioni per definire la struttura della base di dati. Serve quindi, a creare tabelle, vincoli, ecc.

DML (Data Manipulation Language) che prevede le istruzioni per manipolare i dati contenuti nelle tabelle. In particolare permette di inserire, modificare, cancellare record e di effettuare interrogazioni.

DCL (Data Control Language) che prevede istruzioni per controllare il modo in cui le operazioni vengono eseguite. Consentono di gestire il controllo degli accessi da parte di più utenti mediante permessi e autorizzazioni.

Il linguaggio SQL può essere usato in due diverse modalità:

Modalità STAND ALONE: in questo caso i comandi possono essere inviati direttamente al sistema operativo che li esegue dopo averli tradotti utilizzando l’interprete SQL.

Modalità ENBEDDED: in questo caso i comandi vengono inseriti all’interno di un linguaggio ospite ( ad esempio Visual Basic, java ). In questo caso il programma che ospita le istruzioni SQL subirà un primo processo di precompilazione, seguito dalla compilazione vera e propria.

IDENTIFICATORI E TIPI DI DATI

SQL non è un linguaggio Case-sensitive, per cui le istruzioni possono essere scritte utilizzando indifferentemente caratteri maiuscoli o minuscoli. Tuttavia, per una maggiore leggibilità, utilizzeremo le seguenti convenzioni:

parole chiave: tutte in maiuscolo
nomi di tabelle e campi: con iniziali maiuscole
nomi dei campi chiave primaria e chiave esterna: tutti in maiuscolo

Gli identificatori utilizzati per i nomi di tabelle e campi devono sottostare alle seguenti regole:

avere una lunghezza massima di 18 caratteri
iniziare con un carattere alfabetico
non contenere vocali accentate e caratteri speciali; l’unico carattere speciale consentito è l’underscore “_”

Per riferirsi al campo di una tabella, si utilizza la seguente sintassi: NomeTabella.NomeCampo

Le costanti di tipo stringa devono essere racchiuse tra apici (‘……’) o doppi apici (“…..”).

Nelle espressioni possono essere utilizzati i seguenti operatori:
- aritmetici: + - * /
- relazionali: = < > <= >= <> =
- logici: AND OR NOT

I principali tipi di dati sono i seguenti:

TIPO	DESCRIZIONE	Range di variabilità
CHAR	Singolo carattere	Tutti i caratteri del codice ASCII
CHAR(n)	Stringa di caratteri di lunghezza n	n varia da 1 a 15000
BIT	Singolo bit; corrisponde al tipo booleano	0 ≡ Falso, 1 ≡ Vero
INT	Numero intero	Dipende dall’implementazione, solitamente 4 Byte
SMALLINT	Numero intero inferiore a INT	Dipende dall’implementazione, solitamente 2 Byte
REAL	Numero reale	Dipende dall’implementazione, solitamente 7 bit di mantissa
FLOAT	Numero reale	Dipende dall’implementazione, solitamente 15 bit di mantissa
DOUBLE PRECISION	Numero reale	Bit di mantissa doppi rispetto a FLOAT
DATE	Data nel formato “AAAA/MM/GG”
TIME	Ora nel formato “hh:mm:ss:msms”

ISTRUZIONI DDL

Le istruzioni DDL servono definire la struttura della base di dati. Le principali sono:

Creazione di un database:

CREATE DATABASE NomeDatabase

Esempio: CREATE DATABASE DBAzienda

Creazione di una tabella

CREATE TABLE   NomeTabella
(NomeCampo1         Tipo                    [Vincolo],
……….............             ……           …………

NomeCampoN Tipo [Vincolo],

[VincoloTabella1],
…………………..

[VincoloTabellaM] );

Osserviamo che l’istruzione CREATE TABLE specifica

Il nome della tabella
I nomi dei campi
I tipi dei campi
Eventuali vincoli sui campi: questi possono essere:

Un valore di default che serve a specificare il valore da assegnare a quel campo nel caso in cui tale valore non sia stato assegnato.
Se il valore è obbligatoriamente richiesto (Not Null)

Eventuali vincoli di tabella: questi possono essere

Vincoli sui valori assunti da uno o più campi
Definizione della chiave primaria
Definizione di eventuali chiavi esterne con riferimento alle tabelle collegate;
Il comportamento da tenere nel caso di modifica dei valori delle chiavi con conseguente violazione dell’integrità referenziale.

Vediamo un esempio:
            CREATE TABLE Dipendenti
(ID                              INT                              NOT NULL,
Nominativo                 CHAR(50)                  NOT NULL,
DataNascita               DATE                         NOT NULL,
Sesso                         BIT                              NOT NULL,
DataAssunzione         DATE,
Livello                          INT                              DEFAULT 5,
Stipendio                     REAL,
ID_REPARTO                       INT,
CHECK (Stipendio>0),
CHECK (Livello IN (1,2,3,4,5)),
CHECK (DataAssunzione > DataNascita),
CHECK (Stipendio BETWEEN 1500 AND 2000),
UNIQUE (Nominativo, DataNascita),
PRIMARY KEY (ID),
FOREIGN KEY (ID_REPARTO) REFERENCES Reparti (ID) ON DELETE SET NULL );

La clausola CHECK è utilizzata per esprimere vincoli sui valori dei campi; oltre agli operatori di confronto si possono utilizzare i seguenti operatori:

IN: per specificare che il valore deve essere compreso in un insieme finito.
BETWEEN: per specificare che un valore deve essere compreso tra un minimo e un massimo
LIKE: per specificare che il valore del campo deve avere un certo formato; ad esempio:

CHECK (CodiceArticolo LIKE “COD%”).

La parola chiave UNIQUE serve a specificare che i valori di una sequenza di campi devono essere diversi per ogni record.
Come già detto, nella definizione di una tabella è possibile specificare la chiave primaria ed eventuali chiavi esterne. In questo caso occorre indicare la tabella a cui la chiave esterna fa riferimento e le politiche da adottare in caso di violazione dell’integrità referenziale. In particolare, può accadere che il valore della chiave primaria a cui una chiave esterna fa riferimento venga:

Modificato: ON UPDATE
Eliminato: ON DELETE

I comportamenti che possono essere adottati sono i seguenti:

CASCADE: i valori della chiave esterna vengono automaticamente aggiornati con il nuovo valore della chiave primaria;
SET NULL: i valori delle chiavi esterne vengono impostati a NULL;
SET DEFAULT: i valori delle chiavi esterne vengono impostati al valore di DEFAULT
NO ACTION: Non viene fatto niente; questa è l’opzione di default che viene adottata nel caso in cui non sia stato specificato nulla.

ISTRUZIONI DML

Questo gruppo di istruzioni consente di inserire, modificare, cancellare record da una tabella ed interrogare il database:

INSERIMENTO DI UN RECORD

INSERT INTO NomeTabella(NomeCampo1, ….NomeCampoN)
VALUES ( Valore1,….ValoreN );

Esempio:
INSERT INTO Calciatori (Nominativo, Livello, Gol)
VALUES (“Ronaldo”,1, 8);

MODIFICA DI UNO O PIU’ RECORD

UPDATE NomeTabella
SET Campo1= Espressione1,
Campo2= Espressione2,
………………………………….
CampoN = EspressioneN
[WHERE condizione];

Esempi:
UPDATE Calciatori
SET Gol = Gol +1 ;

UPDATE Calciatori
SET Gol = 10
WHERE Nominativo=’Ronaldo’;

CANCELLAZIONE DI UNO O PIU’ RECORD

DELETE FROM NomeTabella
[WHERE Condizione];

Esempi: DELETE FROM Calciatori;

DELETE FROM Calciatori
WHERE Gol < 3;
MODIFICA DELLA STRUTTURA DI UNA TABELLA

È possibile aggiungere o eliminare un campo:

ALTER TABLE NomeTabella
ADD NomeCampo1 Tipo
[BEFORE NomeColonna2];

ALTER TABLE NomeTabella
DROP COLUMN NomeCampo;

ELIMINAZIONE DI UNA TABELLA

DROP TABLE NomeTabella

in questo caso occorre specificare il comportamento da adottare nel caso vi siano tabelle collegate a quella che si vuole eliminare. Le possibili opzioni sono:

CASCADE: cancella in cascata tutte le tabelle collegate
SET NULL: imposta a NULL tutti i valori delle chiavi esterne collegate alla tabella da eliminare
RESTRICT: non cancella la tabella se essa è collegata ad altre tabelle

Esempio

DROP TABLE Dipendenti RESTRICT;

INTERROGAZIONE DEL DATABASE
Il comando che si utilizza per interrogare il database è il SELECT. Si tratta di un comando dalla sintassi molto complessa, quindi ci limiteremo a fornire solo degli esempi.
Questo comando consente tra l’altro, di effettuare le principali operazioni che già conosciamo:

PROIEZIONE

SELECT Campo1,…CampoN
FROM NomeTabella;

Esempio:
SELECT Nominativo, Livello
FROM Calciatori;

RESTRIZIONE

SELECT *
FROM NomeTabella
WHERE condizione;

Esempio:
SELECT *
FROM Calciatori
WHERE Gol > 10;

INNER JOIN

SELECT *
FROM NomeTabella1 INNER JOIN NomeTabella2 ON condizione;

Esempio:

SELECT *
FROM Calciatori INNER JOIN Squadre ON Calciatori.ID_SQUADRA= Squadre.ID;

Il comando SELECT consente di effettuare contemporaneamente una restrizione e/o una proiezione e/o un join. Vediamo alcuni esempi:

SELECT Nominativo, Livello
FROM Calciatori
WHERE Gol > 10;

SELECT Calciatori.Nominativo, Squadre.Nome
FROM Calciatori INNER JOIN Squadre ON Calciatori.ID_SQUADRA= Squadre.ID;

SELECT Calciatori.Nominativo, Calciatori.Gol, Squadre.Nome
FROM Calciatori INNER JOIN Squadre ON Calciatori.ID_SQUADRA= Squadre.ID
WHERE Calciatori.Gol > 10

Quando si effettua una interrogazione, L’ SQL permette di rinominare una tabella o un campo, in modo che nella tabella risultato le colonne siano intestate con altri nomi; per fare ciò sui utilizza la clausola AS:

SELECT Calciatori.Nominativo AS Calciatore, Calciatori.Gol AS GolFatti, Squadre.Nome AS Squadra
FROM Calciatori INNER JOIN Squadre ON Calciatori.ID_SQUADRA= Squadre.ID
WHERE Calciatori.Gol > 10

Per utilizzare l’OUTER JOIN destro o sinistro, il comando è lo stesso ma al posto di INNER JOIN si scrive RIGHT JOIN o LEFT JOIN.
Per realizzare il SELF JOIN su una stessa tabella si utilizza la ridenominazione delle tabelle:

SELECT *
FROM Persone AS Pers1 INNER JOIN Persone AS Pers2 ON Pers1.ID_PADRE= Pers2.ID;

Per realizzare il CROSS JOIN, si utilizza la seguente sintassi:

SELECT *
FROM tabella1, tabella2

È possibile effettuare contemporaneamente anche il CROSS JOIN tra più di due tabelle

SELECT *
FROM tabella1, tabella2, ….,tabellaN

Il comando SELECT normalmente non elimina eventuali righe uguali dalla tabella risultato; se si vuole invece avere la tabella risultato senza ripetizioni occorre utilizzare la clausola DISTINCT:

SELECT DISTINCT *
FROM …………..

Nell’ espressioni delle condizioni, semplici o composte, si possono utilizzare gli operatori logici AND, OR, NOT e gli operatori IN, BETWEEN, LIKE. Per quanto riguarda il valore NULL, occorre precisare che la sintassi corretta è la seguente:

WHERE Impiegati.DataAssunzione IS NULL

Oppure

WHERE Impiegati.DataAssunzione IS NOT NULL

Infine, per tradurre le operazioni di UNIONE, INTERSEZIONE, DIFFERENZA, SQL utilizza gli operatori UNION, INTERSECT, EXCEPT.
Ricordiamo che questi possono essere applicati a tabelle compatibili

Dirigenti UNION Impiegati
Dirigenti INTERSECTION Impiegati
Dirigenti EXCEPT Impiegati

ma anche a tabelle risultato di una query, purchè compatibili:

(SELECT Cognome, Nome FROM Registi) UNION (SELECT Cognome, Nome FROM Attori)

(SELECT Cognome, Nome FROM Registi) INTERSECT (SELECT Cognome, Nome FROM Attori)

(SELECT Cognome, Nome FROM Registi) EXCEPT (SELECT Cognome, Nome FROM Attori)

LE FUNZIONI DI AGGREGAZIONE

L’ SQL possiede alcune funzioni predefinite, utili nei casi in cui occorre effettuare conteggi, somme ecc. sulla tabella risultato di una query. Tali funzioni si applicano a un campo di una tabella. Le principali funzioni sono:

COUNT: conta il numero di elementi presenti nel campo specificato, diversi da NULL.

Se invece di specificare il campo si usa l’asterisco, vengono contati tutti i record della tabella;

MIN, MAX: restituiscono il valore minimo o massimo del campo specificato;

SUM: restituisce la somma degli elementi specificati;

AVG: restituisce la media aritmetica degli elementi del campo specificato.

Esempio:
Consideriamo la seguente tabella:
Dipendenti (ID, Nominativo, Livello, Stipendio)

Per calcolare il numero di dipendenti con stipendio maggiore di 2000:

SELECT COUNT (Stipendio)
FROM Dipendenti
WHERE Stipendio >2000;

Per calcolare lo stipendio medio di tutti i dipendenti

SELECT AVG (Stipendio)
FROM Dipendenti;

Per calcolare lo stipendio massimo di tutti i dipendenti del 5° livello

SELECT MAX (Stipendio)
FROM Dipendenti
WHERE Livello= 5;

Per calcolare il numero di record presenti nella tabella Dipendenti:

SELECT COUNT (*)
FROM Dipendenti;

ORDINAMENTI

In SQL è possibile ordinare le righe di una tabella risultato in base a certi criteri.
E’ possibile specificare uno o più campi, in base a cui ordinare e per ogni campo si può specificare il tipo di ordinamento crescente (ASC) o decrescente (DESC). Per fare ciò basta utilizzare la clausola ORDER BY:

Esempio:

Per avere tutti i dipendenti ordinati per nome in ordine crescente

SELECT *
FROM Dipendenti
ORDER BY Nominativo ASC;

Per avere tutti i dipendenti del 5° livello ordinati per stipendio decrescente

SELECT *
FROM Dipendenti
WHERE Livello= 5
ORDER BY Stipendio DESC;

Per avere tutti i dipendenti ordinati per livello decrescente e poi per nominativo crescente:

SELECT *
FROM Dipendenti
ORDER BY Livello DESC, Nominativo;

RAGGRUPPAMENTI

L’ SQL consente di raggruppare logicamente le righe della tabella risultato di una query, in base al valore assunto da uno o più campi.
In particolare tutte le righe aventi lo stesso valore (o gli stessi valori) di un determinato campo (campi) vengono fuse in un’unica riga.
Per effettuare un raggruppamento si usa la clausola GROUP BY:

Esempio:

La seguente query raggruppa tutti i dipendenti per livello e calcola la media dello stipendio per ciascun livello:

SELECT Livello, AVG (Stipendio)
FROM Dipendenti
GROUP BY Livello;

Livello	AVG(Stipendio)
1	1500
2	1400
3	1300
4	1200
5	1100

La seguente query raggruppa tutti i dipendenti per livello e calcola il numero di dipendenti per ciascun livello:

SELECT Livello, COUNT (Livello) AS NumDipendenti
FROM Dipendenti
GROUP BY Livello;

Livello	NumDipendenti
1	15
2	5
3	7
4	10
5	12

Si può aggiungere una condizione sul gruppo, utilizzando la clausola HAVING:

SELECT Livello, AVG(Stipendio) AS StipendioMedio
FROM Dipendenti
GROUP BY Livello
HAVING Livello >=4;

Livello	StipendioMedio
4	1200
5	1100

QUERY INTERMEDIE E QUERY ANNIDATE

In alcuni casi, la scrittura di una interrogazione può richiedere più passaggi: occorre, cioè, creare prima una o più tabelle intermedie e infine scrivere la query che produce il risultato finale.

Esempio1
Supponiamo di voler conoscere i nominativi dei dipendenti aventi stipendio maggiore dello stipendio medio di tutti i dipendenti. Procediamo nel seguente modo:

1) scriviamo una query intermedia che calcola lo stipendio medio e salva il risultato in una tabella StipMedio.

CREATE TABLE StipMedio
SELECT AVG (Stipendio)
FROM Dipendenti;

Osserviamo che la tabella StipMedio conterrà un singolo valore, cioè una sola riga ed un solo campo

AVG(Stipendio)

1300

2) scriviamo la query principale che seleziona i nominativi dei dipendenti aventi stipendio maggiore della media

SELECT Dipendenti.Nominativo
FROM Dipendenti
WHERE Dipendenti.Stipendio > StipMedio;

È possibile arrivare allo stesso risultato anche scrivendo una sola query che “annida” dentro di sé la query parziale:

SELECT Dipendenti.Nominativo
FROM Dipendenti
WHERE Dipendenti.Stipendio > (SELECT AVG (Stipendio) FROM Dipendenti);

Esempio2
Supponiamo di avere le seguenti tabelle relative ai noleggi di film presso una videoteca:

Clienti (ID, Nominativo)
Film (ID, Titolo)
Noleggi (ID_CLIENTE, ID_FILM, Data)

E di voler conoscere il titolo del film che è stato noleggiato meno volte; effettuiamo I seguenti passaggi:

1) contiamo il numero di noleggi per ogni film e salviamo il risultato in una tabella NoleggiPerFilm

CREATE TABLE NoleggiPerFilm
SELECT Film.Titolo AS Titolo, COUNT(Film.Titolo) AS NumNoleggi
FROM Noleggi INNER JOIN Film ON Noleggi.ID_FILM=Film.ID
GROUP BY Film.Titolo;

NoleggiPerFilm

Titolo	NumNoleggi
Matrix	12
Blade runner	20
Shrek	9

2) Selezioniamo da NoleggiPerFilm Il valore minimo del campo NumNoleggi e salviamo il risultato in una tabella MinNoleggi

CREATE TABLE MinNoleggi
SELECT MIN(NumNoleggi) FROM NoleggiPerFilm;

MinNoleggi

MIN(NumNoleggi)

3) Selezioniamo da NoleggiPerFilm il titolo del film con meno noleggi

SELECT Titolo
FROM NoleggiPerFilm
WHERE NoleggiPerFilm.NumNoleggi = MinNoleggi;

Titolo

Shrek

Oppure, scriviamo un’unica query:

SELECT Titolo
FROM (SELECT Film.Titolo AS Titolo, COUNT(Film.Titolo) AS NumNoleggi
FROM Noleggi INNER JOIN Film ON Noleggi.ID_FILM=Film.ID
GROUP BY Film.Titolo) AS NoleggiPerFilm
WHERE NoleggiPerFilm.NumNoleggi = (SELECT MIN(NumNoleggi) FROM NoleggiPerFilm);

QUERY PARAMETRICHE

Per generalizzare una query, è possibile utilizzare uno o più parametri: essi possono essere visti come “dati di input” della query. Ad esempio, supponiamo di voler conoscere I nominativi di tutti I dipendenti di un certo livello x e con stipendio maggiore di un valore y, scriveremo:

PARAMETERS LivX: intero, StipY: reale
SELECT Nominativo
FROM Dipendenti
WHERE Livello=[LivX] AND Stipendio > [StipY];
LE VISTE

In SQL è possibile definire un altro tipo di tabella chiamata VISTA.
A differenza di una normale tabella, una vista non è fisicamente memorizzata nel database ma può essere definita solo logicamente.
Per creare una VISTA si usa la seguente sintassi:

CREATE VIEW NomeVista AS Query

Una vista quindi non è altro che il risultato di una query a cui viene assegnato un nome.
Le viste vengono utilizzate per fornire una specie di “interfaccia controllata” tra il database e l’utente nel caso in cui si vogliono rendere visibili a determinati utenti solo determinati dati.

Esempio:Supponiamo di avere la seguente tabella

Prodotti (ID, Descrizione, Prezzo, Quantita)

Supponiamo che la precedente tabella faccia parte di un database di un’azienda.
In tale azienda ci sono diversi impiegati che svolgono determinate mansioni: il dirigente, l’impiegato d’ufficio, il magazziniere ecc..
Ognuno di essi nell’ambito del suo lavoro ha accesso al database.
Ciascuno però è interessato solo ad una parte di tutti i dati memorizzati nel database: quindi può essere utile definire diverse viste sul database destinate a ciascuna categoria.
Ad esempio una vista utile al magazziniere è quella che gli consente di visualizzare i prodotti che devono essere ordinati poiché sono terminati:

CREATE VIEW DaOrdinare AS
SELECT * FROM Prodotti WHERE Quantita = 0;

ISTRUZIONI DCL

Le istruzioni DCL permettono di impostare le politiche di sicurezza riguardo all’accesso ai dati da parte degli utenti del database.
Per concedere ad un utente o gruppo di utenti i diritti di accesso ad una tabella (o ad una vista), si utilizza il comando GRANT, la cui sintassi è:

GRANT ElencoPermessi ON NomeTabella TO (ElencoUtenti);

L’elenco dei permessi contiene l’insieme delle azioni che sono consentite sulla tabella e sui campi della tabella: SELECT, INSERT, UPDATE, DELETE.

Esempio:
Il seguente comando consente agli utenti Rossi e Bianchi di poter interrogare la tabella Dipendenti e di poter modificare il campo Stipendio

GRANT SELECT, UPDATE (Stipendio) ON Dipendenti TO (Rossi, Bianchi);

Analogamente si possono assegnare i permessi su una vista: in questo caso l’unico permesso che abbia senso è l’interrogazione:

GRANT SELECT ON DaOrdinare TO Neri;
fonte: itcsanteramo.altervista.org

Fine articolo Corso SQL

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

DML - Data Manipulation Language, linguaggio di manipolazione dei dati.
DDL - Data Definition Language, linguaggio di definizione dei dati.
DCL - Data Control Language, linguaggio di controllo dei dati.

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.

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)

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

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.

DDL - Data Definition Language

I comandi facenti parte del DDL permettono di creare, modificare ed eliminare oggetti e modificare la struttura fisica del DB.
Ogni volta che viene mandato in esecuzione uno di questi comandi non è più possibile ripristinare la situazione precedente.
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:
ALTER TABLE cantanti add(categoria decimal(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.

Sintassi:
STORAGE( INITIAL intero <K/M>
NEXT intero <K/M>
MINEXTENTS intero
MAXEXTENTS intero
PCTINCREASE intero )

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

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.

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.

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.

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.

COMMIT : Rende permanenti le modifiche effettuate sul DB nella transazione corrente.

Sintassi:
COMMIT

Parametri : Nessun parametro.

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.

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

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.

Sintassi SQL :

Operatori

Operatori aritmetici

*       moltiplicazione
/          divisione
+          addizione
-       sottrazione

Operatori logici
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
=                                             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 insiemistica
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
*                      indica tutte le colonne di una tabella o di una vista
(+)                    indica una colonna in outer join (cfr. ‘Ripasso delle 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
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
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) l                      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, a destra, cancellati dopo l' ultimo carattere non in set (il default di set e’ un blank)
LTRIM(char[,set])                               char, con i caratteri iniziali, a sinistra, cancellati prima del primo carattere non in set (il default di set e’ un blank)

Funzioni di gruppo
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
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
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
DECODE(expr,search1,return1, [search2,return2,]...[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
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
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’

SQL*Plus
Lavorando con l’applicativo Client di Oracle SQL*Plus per estrarre i dati dal database si hanno a disposizione dei comandi che permettono di ottenere un buon report di output (ma soprattutto leggibile con minor difficoltà !!!) agendo sul risultato della query.
Questi comandi sono:
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)
SQLPROMPT stringa    per visualizzare un prompt diverso da quello di default (SQL>). Può essere utile quando si hanno più sessioni aperte di Sql*Plus su istanze o utenti diversi
SERVEROUTPUT on/off          per visualizzare in uscita da una stored procedure i messaggi eventualmente inseriti mediante le istruzioni DBMS_OUTPUT.PUT_LINE(stringa)
(cfr. linguaggio PL/SQL pag.24).
Il default è off

Vi sono molti altri parametri il cui valore più essere modificato da quello standard di default proprio di Oracle Sql*Plus. Quelli da me sopra riportati sono i più comuni nonché utili
Tutti questi parametri sono comunque visibili e settabili anche direttamente da Sql*Plus selezionando il menù ‘Opzioni’ à ‘Ambiente’ ; scegliendo l’opzione interessata e cambiando il radio button da ‘default’ a ‘corrente’ viene attivata in scrittura la casella dove è possibile assegnare il valore che si desidera avere.

Esempio:

SET PAGESIZE 74
SET FEEDBACK OFF
SET SQLPROMPT LIGEST> (il prompt diventa LIGEST>)

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

Linguaggio PL/SQL

Note storiche:
Prima del 1991 il solo modo per scrivere delle procedure con SQL era utilizzando il PRO*C. Gli statement SQL venivano inseriti nel codice C che veniva precompilato per convertire gli statement SQL con delle chiamate a librerie
In 1991 PL/SQL 1.0 è stato rilasciato con la release 6.0 di Oracle ma le sue capacità erano molto limitate.
PL/SQL versione 2.0 è stato rilasciato con la release 7.0 di Oracle . Adesso il linguaggio era in grado di supportare stored packages, procedures, functions, tabelle PL/SQL e tipi di record definiti nella programmazione.
PL/SQL versione 2.1 è stato rilasciato con la release 7.1 di Oracle . Il linguaggio è in grado di supportare l’uso di stored functions all’interno di statement SQL oltre che di consentire la gestione di statement SQL dinamici attraverso l’utilizzo del package DBMS_SQL. E’ anche diventato possible eseguire statement di tipo DDL (CREATE TABLE, CREATE VIEW, ALTER TABLE…) dall’interno di programmi scritti in PL/SQL.
PL/SQL versione 2.2 è stato rilasciato con la release 7.2 di Oracle . E’ stata resa possibile la schedulazione di job sul database mediante l’utilizzo del package DBMS_JOB.
PL/SQL versione 2.3 è stato rilasciato con la release 7.3 di Oracle . In questa versione vengono ulteriormente esaltate le capacità delle tabelle e viene aggiunta la possibilità di gestire file I/O.
PL/SQL versione 8.0 è stato rilasciato con la release 8.0 di Oracle . Questa versione supporta tutte le novità di Oracle 8, incluso i Large Objects (LOB), la gestione object oriented, le tabelle annidate e le novità a riguardo della gestione delle code.

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 ed 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 quindi notevolmenle 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 %TYPE 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.

Esempi
(1)
DECLARE

CURSOR c1 IS
SELECT numord FROM ordini [ WHERE];

var_A tab1.A%TYPE;

CURSOR c2 IS
SELECT prezzo
FROM riga_ordini
WHERE numord = var_A

var_B tab1.B%TYPE;

BEGIN
OPEN c1;
LOOP
FETCH c1 INTO c1rec;
EXIT WHEN c1%NOTFOUND;
Pippo := C1rec.A;
pap := c1rec.B;
END LOOP;
CLOSE c1;
END;

(2)
FOR c1_rec IN c1 LOOP

END LOOP;

(3)
<<outer>>
FOR x IN .. LOOP
<<B>>
FOR j IN .. LOOP
EXIT outer WHEN
END LOOP B;
END LOOP outer;

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

CURSORE ESPLICITO

Sintassi dichiarativa
1) CURSOR c1 IS
SELECT col1, col2, …
FROM tab
WHERE condizione;

I valori selezionati da questo cursore possono essere memorizzati in variabili dichiarate con dei propri formati :
sel1 NUMBER(2);
sel2 VARCHAR2(5);

o dei formati ereditati dalla tabella :
sel1 tab.col1%TYPE;
sel2 tab.col2%TYPE

oppure caricati tutti i una variabile di tipo record
c1rec IS c1%ROWTYPE;

da cui possono poi essere estratte per l’utilizzo usando la sintassi
c1rec.col1 cioè il valore della colonna col1 memorizzata nel tipo record c1rec
c1rec.col2 cioè il valore della colonna col2 memorizzata nel tipo record c1rec

Volendo selezionare tutte le colonne di una tabella è possible dichiarare il cursore in questo modo:

2) CURSOR c1 RETURN tablename%ROWTYPE;

E’ fortemente consigliato utilizzare sempre dei cursori espliciti per gestire qualsiasi istruzione SQL, anche quella più elementare. Il motivo è legato al fatto che quando una operazione di selezione sui dati viene eseguita senza dichiarare un cursore esplicito questa lavora con i cursori impliciti propri della basedati Oracle. Un cursore implicito però ha una gestione che non è controllabile dal programmatore e questo potrebbe far incorrere in problemi legati al numero massimo di cursori aperti contemporaneamente, variabile questa dichiarata in fase di creazione dell’istanza di DB. Quando questo valore viene raggiunto qualsiasi operazione in esecuzione cade in errore Oracle.

Ciclo semplice a cursore :
Gli attributi di un cursore, come l’esistenza o meno di righe rimaste da trasmettere con fetch, possono essere utilizzati come criteri di uscita da un ciclo. Nell’esempio seguente, un cursore viene eseguito fino a quando la query non restituisce più altre righe. Per determinare lo stato del cursore vengono verificati i relativi attributi. I cursori sono dotati di quattro attributi, che possono essere utilizzati per la programmazione.

%FOUND Il cursore può trasmettere un record.
%NOTFOUND Il cursore non può trasmettere altri record.
%ISOPEN Il cursore è stato aperto.

%ROWCOUNT Numero di righe trasmesse dal cursore sino a questo momento.

Gli attributi del cursore %FOUND, %NOTFOUND e %ISOPEN sono booleani e vengono impostati su VERO o FALSO. Poiché detti attributi sono booleani, è possibile valutare le loro impostazioni senza farli esplicitamente coincidere con valori di VERO o FALSO.
Ad esempio, il comando di seguito riportato causa un output quando rad_cursor%NOTFOUND è VERO:
exit when rad_cursor%NOTFOUND;
Nella stringa seguente viene utilizzato un ciclo semplice per elaborare più righe di un cursore.
declare
pi constant NUMBER(9,7) := 3.1415926;
area NUMBER(14,2);
cursor rad_cursor is
select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
begin
open rad_cursor;
loop
fetch rad_cursor into rad_val;
exit when rad_cursor%NOTFOUND;
area := pi*power(rad_val.radius,2);
insert into AREA values (rad_val.radius, area);
end loop;
close rad_cursor;
end;
.
/

La sezione di ciclo del blocco PL/SQL esegue la stessa elaborazione del ciclo semplice descritto nel precedente paragrafo, con un’eccezione. Invece di basare il criterio di uscita sul valore di Area, viene controllato l’attributo del cursore %NOTFOUND.
Se nel cursore non vengono trovate altre righe, l’attributo %NOTFOUND diventerà VERO e ciò causerà l’uscita dal ciclo. Di seguito è riportata la versione commentata del ciclo stesso.
loop
/* Nel ciclo, preleva un record. */
fetch rad_cursor into rad_val;
/* Se il tentativo di prelevamento non trova */
/* record nel cursore, esce dal ciclo. */
exit when rad_cursor%NOTFOUND;
/* Se il tentativo restituisce un record, */
/* elabora il valore del raggio e inserisce */
/* un record nella tabella AREA. */
area := pi*power(rad_val.radius,2);
insert into AREA values (rad_val.radius, area);
/* Segnala la fine del ciclo. */
end loop;

Quando il precedente blocco PL/SQL viene eseguito, tutti i record della tabella RADIUS_VALS vengono elaborati dal ciclo. Finora la tabella RADIUS_VALS conteneva solo un record: un valore di Radius pari a 3. Prima di eseguire il blocco PL/SQL per questa sezione, si aggiungano due nuovi valori di Radius alla tabella
RADIUS_VALS: 4 e 10.

Di seguito viene illustrata l’aggiunta dei nuovi record alla tabella RADIUS_VALS.

insert into RADIUS_VALS values (4);
insert into RADIUS_VALS values (10);
commit;
select *
from RADIUS_VALS
order by Radius;
RADIUS
----------
3
4
10
Una volta aggiunti i nuovi record alla tabella RADIUS_VALS, si esegua il blocco PL/SQL descritto in precedenza nel presente paragrafo. Di seguito è riportato l’output.

select *
from AREA
order by Radius;

RADIUS AREA
---------- ----------
3             28.27
4             50.27
10          314.16

La query della tabella AREA mostra come ogni record della tabella RADIUS_VALS sia stato trasmesso dal cursore ed elaborato. Una volta esauriti i record da elaborare, si è usciti dal ciclo e il blocco PL/SQL è stato completato.

Ciclo FOR :
Nei cicli semplici il corpo dei comandi viene eseguito finché non viene soddisfatta una condizione di uscita. In un ciclo FOR, il ciclo viene eseguito per un numero di volte specificato. Di seguito è riportato un esempio di ciclo FOR.
L’inizio del ciclo FOR viene indicato dalla parola chiave for, seguita dai criteri utilizzati per determinare quando uscire. Dato che il numero di esecuzioni del ciclo viene impostato all’inizio del ciclo stesso, non è necessario un comando exit all’interno del ciclo.
Nell’esempio seguente le aree dei cerchi vengono calcolate basandosi su valori di Radius da 1 a 7 compresi.

declare
pi constant NUMBER(9,7) := 3.1415926;
radius INTEGER(5);
area NUMBER(14,2);
begin
for radius in 1..7 loop
area := pi*power(radius,2);
insert into AREA values (radius, area);
end loop;
end;
.
/

I passaggi che l’elaborazione del ciclo comporta sono visualizzati nel seguente esempio commentato.
/* Specifica i criteri per il numero di esecuzioni */
/* del ciclo. */
for radius in 1..7 loop
/* Calcola l™area utilizzando il valore corrente */
/* di Radius. */
area := pi*power(radius,2);
/* Inserisce i valori di area e raggio nella tabella */
/* AREA. */
insert into AREA values (radius, area);
/* Segnala la fine del ciclo. */
end loop;

Si noti che nel ciclo FOR non esiste una riga:
radius := radius+1;
Dato che le specifiche del loop determinano:
for radius in 1..7 loop
i valori di Radius sono già specificati. Per ciascun valore verranno eseguiti tutti i comandi all’interno del ciclo (tali comandi possono comprendere altra logica condizionale, come condizioni if).
Una volta che il ciclo ha completato l’elaborazione di un valore di Radius, vengono verificati i limiti nella clausola for e si passa, a seconda dei casi, a elaborare il successivo valore di Radius o a completare l’esecuzione del ciclo.
Di seguito è riportato un esempio di output ricavato dall’esecuzione del ciclo FOR.

select *
from AREA
order by Radius;
RADIUS          AREA
----------           ----------
1                      3.14
2                      12.57
3                      28.27
4                      50.27
5                      78.54
6                      113.1
7                      153.94
7 rows selected.

Cicli FOR a cursore :
Nei cicli FOR le istruzioni sono eseguite per un numero di volte specificato. In un ciclo FOR a cursore, i risultati di una query vengono utilizzati per determinare dinamicamente il numero di esecuzioni del ciclo. In questi cicli l’apertura, la trasmissione e la chiusura dei cursori sono eseguite implicitamente. Non è pertanto necessario utilizzare comandi espliciti per tali azioni.
Di seguito è riportato un ciclo FOR a cursore che effettua una query sulla tabella RADIUS_VALS e inserisce record nella tabella AREA:

declare
pi constant NUMBER(9,7) := 3.1415926;
area NUMBER(14,2);
cursor rad_cursor is
select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
begin
for rad_val in rad_cursor
loop
area := pi*power(rad_val.radius,2);
insert into AREA values (rad_val.radius, area);
end loop;
end;
.
/

In un ciclo FOR a cursore non compare un comando open o fetch. Il comando:
for rad_val in rad_cursor
apre implicitamente il cursore “rad_cursor” e trasmette un valore nella variabile “rad_val”.
Quando nel cursore non ci sono più record, l’elaborazione esce dal ciclo e il cursore viene chiuso. In un ciclo FOR a cursore non è necessario il comando close.
Di seguito è riportata la parte relativa al ciclo del blocco PL/SQL, con commenti che descrivono il flusso dei controlli. Il ciclo viene controllato in base all’esistenza di un record trasmissibile nel cursore “rad_cursor”. Non è necessario verificare l’attributo %NOTFOUND del cursore; provvede automaticamente il ciclo FOR a cursore.

/* Se un record può essere prelevato dal cursore, */
/* lo memorizza nella variabile rad_val. Se */
/* non ci sono righe da prelevare, salta il ciclo. */
for rad_val in rad_cursor
/* Iniziano i comandi di ciclo. */
loop
/* Calcola l™area in base al valore del raggio */
/* e inserisce un record nella tabella AREA. */
area := pi*power(rad_val.radius,2);
insert into AREA values (rad_val.radius, area);
/* Segnala la fine dei comandi di ciclo. */
end loop;

Di seguito è riportato un esempio di output. In questo caso la tabella RADIUS_VALS dispone di tre record, con valori di Radius pari a 3, 4 e 10.

select *
from RADIUS_VALS
order by Radius;

RADIUS
----------
3
4
10

L’esecuzione del blocco PL/SQL con il ciclo FOR a cursore genera i seguenti record nella tabella AREA:
select *
from AREA
order by Radius;

RADIUS          AREA
----------           ----------
3                      28.27
4                      50.27
10                    314.16

Ciclo WHILE :
In un ciclo WHILE i comandi sono ripetuti finché non viene soddisfatta una condizione di uscita. Invece di specificare la condizione di uscita per mezzo di un comando exit all’interno del ciclo, la si specifica nel comando while con cui inizia il ciclo.
Nell’esempio seguente viene creato un ciclo WHILE che consenta l’elaborazione di più valori di Radius. Se il valore corrente della variabile Radius soddisfa la condizione while nella specificazione del ciclo, i comandi del ciclo stesso vengono elaborati. Quando un valore di Radius non soddisfa la condizione while nella specificazione del ciclo, l’esecuzione di quest’ultimo termina.

declare
pi constant NUMBER(9,7) := 3.1415926;
radius INTEGER(5);
area NUMBER(14,2);
begin
radius := 3;
while radius<=7
loop
area := pi*power(radius,2);
insert into AREA values (radius, area);
radius := radius+1;
end loop;
end;
.
/

Il ciclo WHILE è simile nella struttura al ciclo semplice, in quanto termina basandosi sul valore di una variabile. Di seguito sono riportati i passaggi implicati nel ciclo e i relativi commenti.

/* Imposta un valore iniziale per la variabile Radius. */
radius := 3;
/* Stabilisce i criteri per l™uscita dal ciclo */
/* Se la condizione è verificata, esegue i comandi */
/* all™interno del ciclo, altrimenti lo termina. */
while radius<=7
/* Inizio dei comandi da eseguire. */
loop
/* Calcola l™area in base al valore corrente di */
/* Radius e inserisce un record nella tabella AREA */
area := pi*power(radius,2);
insert into AREA values (radius, area);
/* Imposta un nuovo valore per la variabile Radius. Questo */
/* nuovo valore viene confrontato con i criteri di uscita */
/* e si ha una nuova esecuzione oppure l™uscita dal ciclo */
radius := radius+1;
/* Segnala la fine dei comandi del ciclo. */
end loop;

Al momento dell’esecuzione il blocco PL/SQL dell’esempio precedente inserisce record nella tabella AREA. Di seguito è riportato l’output del blocco PL/SQL.

select *
from AREA
order by Radius;
RADIUS          AREA
----------           ----------
3                      28.27
4                      50.27
5                      78.54
6                      113.1
7                      153.94
A causa del valore assegnato alla variabile “Radius” prima del ciclo, questo è forzato a essere eseguito almeno una volta. È necessario verificare che l’assegnazione della variabile soddisfi le condizioni utilizzate per limitare le esecuzioni del ciclo.

Istruzioni goto

Un’istruzione goto forza il flusso dei comandi a essere immediatamente deviato verso un’altra serie di comandi. Per utilizzare l’istruzione goto è necessario creare in precedenza delle etichette per serie di comandi. Un’istruzione goto non può trasferire il controllo a un blocco annidato all’interno del blocco corrente, all’interno di un ciclo FOR o all’interno di una condizione if.

Per creare un’etichetta occorre racchiuderne il nome tra apici angolari, come nell’esempio seguente.

<<area_del_cerchio>>
radius := 3;
while radius<=7
loop
area := pi*power(radius,2);
insert into AREA values (radius, area);
radius := radius+1;
end loop;

Nell’esempio precedente l’etichetta <<area_del_cerchio>> denomina la serie di comandi che la seguono.
Se all’interno della sezione dei comandi eseguibili compare:

goto area_del_cerchio;

inizia l’esecuzione dei comandi all’interno dell’etichetta <<area_del_cerchio>>.
Se possibile, è meglio tentare di utilizzare cicli o condizioni if al posto delle istruzioni goto. I cicli e le condizioni if, per loro natura, documentano le regole logiche applicate al flusso del controllo tra i comandi. Un comando goto, d’altro canto, non richiede alcuna documentazione circa il motivo per il passaggio a un’altra serie di comandi. Dato che i comandi goto non si documentano da sé, è più semplice la manutenzione di codice PL/SQL scritto con condizioni if e cicli.

Attributi dei cursori:
%FOUND
TIPO Attributo di cursore
PRODOTTI PL/SQL
VEDERE ANCHE %ISOPEN, %NOTFOUND, %ROWCOUNT, CURSOR, SQL CURSOR
SINTASSI
cursore%FOUND
oppure:
SQL%FOUND
DESCRIZIONE %FOUND rappresenta un flag di successo per select, insert, update e delete. cursore è il nome di un cursore esplicito dichiarato in un blocco PL/SQL, oppure il cursore implicito di nome SQL. %FOUND può essere associato al nome di un cursore come suffisso.
I due assieme rappresentano un flag di successo relativo all’esecuzione delle istruzioni select, insert, update e delete all’interno di blocchi PL/SQL.
PL/SQL impegna temporaneamente una parte di memoria come blocco degli appunti per l’esecuzione di istruzioni SQL e per memorizzare certi tipi di informazione (o attributi) relativi allo stato di questa esecuzione. Se l’istruzione SQL è select, quest’area contiene una riga di dati.
%FOUND rappresenta uno di questi attributi. Viene utilizzato nella logica PL/SQL come parte di un testo IF/THEN e viene sempre valutato al valore TRUE, FALSEo NULL. %NOTFOUND è il suo opposto logico. Risulta FALSE quando %FOUND è pari a TRUE, TRUE quando %FOUND è FALSE e NULL quando %FOUND è NULL.
Di seguito sono riportati gli stati di %FOUND sotto diverse condizioni:

ATTIVITÀ DEL CURSORE CURSORE ESPLICITO CURSORE IMPLICITO (SQL)
ATTIVITÀ DEL CURSORE               CURSORE ESPLICITO     CURSORE IMPLICITO (SQL)
Cursore esplicito non ancora aperto                       (ERROR)                                 n/a
Cursore aperto ma non ancora eseguito                                NULL                                      n/a
tramite FETCH
FETCH ha restituito una riga                                   TRUE                                      TRUE
FETCH non ha restituito alcuna riga                       FALSE                                    FALSE
Prima di qualsiasi istruzione SQL eseguita              n/a                                          NULL
in un cursore implicito
insert ha inserito una riga                                       n/a                                          TRUE
insert ha fallito nell’inserire una riga                        n/a                                          FALSE
update ha avuto effetto su almeno una riga           n/a                                          TRUE
update non ha avuto effetto su alcuna riga            n/a                                          FALSE
delete ha eliminato almeno una riga                       n/a                                          TRUE
delete non ha eliminato alcuna riga                        n/a                                          FALSE

Effettuare il controllo su %FOUND per conoscere la condizione di un cursore
esplicito prima che venga aperto provoca un errore di tipo EXCEPTION (codice d’errore
ORA-01001, INVALID CURSOR).

Si può notare come molte delle condizioni precedenti siano “n/a”, cioè “non applicabili”.
Questo perché solo il cursore implicito viene utilizzato per qualsiasi ope-razione
di insert, update o delete e qualsiasi controllo del suo valore deve essere
effettuato utilizzando SQL%FOUND dopo la sua esecuzione e prima che qualsiasi al-tra
istruzione SQL venga eseguita nel cursore di SQL.

%ISOPEN
TIPO Attributo di cursore
PRODOTTI PL/SQL
VEDERE ANCHE SQL CURSOR, Capitolo 22
SINTASSI cursore%ISOPEN
DESCRIZIONE cursore deve essere il nome di un cursore dichiarato esplicitamente
o il cursore implicito di nome SQL. Viene valutato pari a TRUE se il cursore specifi-cato
risulta aperto, FALSE se non lo è. SQL%ISOPEN viene sempre valutato pari a
FALSE, perché il cursore SQL viene aperto e chiuso automaticamente quando viene
eseguita un’istruzione SQL non esplicitamente dichiarata (vedere CURSORE
SQL). %ISOPEN viene utilizzato nella logica PL/SQL; non può far parte di un’istru-zione
SQL.
ESEMPIO ...
if NOT (REGISTRO%ISOPEN)
then open REGISTRO;
end if;
...
%NOTFOUND
Vedere %FOUND.

%ROWCOUNT
TIPO Attributo di cursore
PRODOTTI PL/SQL
VEDERE ANCHE CLOSE, DECLARE, DELETE, FETCH, INSERT, OPEN, SELECT, UPDATE
SINTASSI : cursore%ROWCOUNT
DESCRIZIONE cursore rappresenta o il nome di un cursore dichiarato in modo esplicito, oppure il cursore implicito di nome SQL. cursore%ROWCOUNT contiene il numero totale cumulativo di righe restituite tramite FETCH dall’insieme attivo nel cursore attuale. Può essere utilizzato per elaborare intenzionalmente solo un numero prefissato di righe, ma più comunemente viene utilizzato come un gestore di eccezioni per le operazioni select predisposte per restituire solo una riga (ad esempio
select...into). In questi casi, se non viene restituita alcuna riga, %ROWCOUNT viene
impostato a 0 (questo controllo può essere effettuato anche tramite %NOTFOUND;
nel caso in cui venga restituita più di una riga, non importa quante, il suo valore viene
impostato a 2).
%ROWCOUNT è utilizzato nella logica PL/SQL; non può entrare a far parte di
un’istruzione SQL. Se viene utilizzato SQL%ROWCOUNT, esso può fare riferimento
solo al più recente cursore implicito aperto. Se nessun cursore implicito è stato
aperto, SQL%ROWCOUNT restituisce NULL.

%ROWTYPE
TIPO Attributo di variabile
PRODOTTI PL/SQL
VEDERE ANCHE FETCH
SINTASSI {[utente.]tabella ¦ cursore}%ROWTYPE
DESCRIZIONE %ROWTYPE dichiara una variabile di un record in modo che abbia
la stessa struttura di un’intera riga di una tabella (o vista), oppure di una riga recu-perata
tramite il cursore specificato. Viene utilizzato come parte della dichiarazione
di una variabile e fa in modo che la variabile contenga i campi appropriati e i tipi di
dati per gestire tutte le colonne riportate. Se viene specificato [utente.]tabella, la ta-bella
(o vista) specificata deve esistere nel database.

Ad esempio, si riconsideri la tabella LAVORATORE:
Colonna Tipodato
------------- -----------------
Nome VARCHAR2(25) not null
Eta NUMBER
Alloggio VARCHAR2(15)

Per creare una variabile che contenga i campi corrispondenti, si utilizzi declare, il
nome di una variabile e %ROWTYPE con il nome della tabella e quindi si selezioni
una riga nel record (si noti il simbolo di *, che preleva tutte le colonne. Viene richiesto
per la forma che utilizza il nome di una tabella come prefisso a %ROWTYPE).

DECLARE
LAVORATORE_RECORD LAVORATORE%rowtype;
BEGIN
select * into LAVORATORE_RECORD from LAVORATORE
where Nome = ‚MARIO ROSSI™;
if LAVORATORE_RECORD.Eta > 65
then ...
end if;
END;

Poiché LAVORATORE_RECORD ha la stessa struttura della tabella LAVORATORE,
è possibile far riferimento al campo Eta come LAVORATORE_RECORD.Eta, utilizzando una notazione simile a quella tabella.colonna propria delle istruzioni SQL.

Select...into e fetch...into rappresentano i soli metodi per caricare un intero record
in una variabile. I campi singoli all’interno del record possono essere caricati utilizzando la notazione :=, come viene mostrato di seguito:
LAVORATORE_RECORD.Eta := 44;
Se come prefisso per %ROWTYPE si utilizza un cursore, esso può contenere un’istruzione select con tante colonne quante sono necessarie. Tuttavia, se una colonna prelevata da un cursore specifico è un’espressione, anziché un semplice nome di colonna, a questa espressione deve essere dato un alias nell’istruzione select prima che si possa farvi riferimento tramite questo metodo.

%TYPE
TIPO Attributo di variabile
PRODOTTI PL/SQL
VEDERE ANCHE %ROWTYPE
SINTASSI {[utente.]tabella.colonna ¦ variabile}%TYPE
DESCRIZIONE %TYPE viene utilizzato per dichiarare una nuova variabile dello stesso tipo di quella dichiarata precedentemente, oppure come colonna particolare di una tabella già esistente nel database a cui si è collegati.
ESEMPIO In questo esempio viene assegnato a una nuova variabile, Impiegato, lo stesso tipo della colonna Nome della tabella LAVORATORE. Poiché Impiegato ora esiste, può essere utilizzata per dichiarare un’altra nuova variabile, Nuovo_Lavoratore.

Impiegato LAVORATORE.Nome%TYPE;
Nuovo_Lavoratore Impiegato%TYPE;

& o && (e commerciale o e commerciale doppia)
TIPO Prefissi di variabili, oppure operatore di ricerca del testo
PRODOTTO SQL*PLUS, ConText
VEDERE ANCHE :, ACCEPT, DEFINE, START, CONTAINS, OPERATORI DI RICERCA TESTUALE
SINTASSI Per SQL*PLUS:
&intero
&variabile
&&variabile
Per ConText:
select colonna
from tabella
where CONTAINS(testo_colonna, ‚termine & termine™) > 0;
DESCRIZIONE & e && possono essere utilizzati in diversi modi (&& si applica solamente
alla seconda definizione descritta di seguito).

Prefisso per parametri in un file di avvio di SQL*PLUS. &1, &2 e così via vengono sostituiti da parole. Vedere START.
Prefisso per una variabile di sostituzione in un comando SQL all’interno di SQL*PLUS. SQL*PLUS richiede l’inserimento di un valore se trova una variabile & o && non definita. && definisce la variabile e quindi ne preserva il valore; & non definisce o preserva il valore, ma semplicemente esegue una sostituzione di ciò che viene inserito. Vedere ACCEPT e DEFINE.

• & può inoltre essere utilizzato per segnalare una condizione AND per ricerche di
testi che coinvolgono più termini nell’ambito di ConText. Se anche solo uno dei
termini ricercati non viene trovato, il testo non viene restituito dall’operazione di
ricerca.

PIPPO(A IN, B OUT, C IN OUT)

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

EXCEPTION	DESCRIZIONE
CURSOR_ALREADY_OPEN	Il cursore è già stato aperto
DUP_VAL_IN_INDEX	valore duplicato nella chiave
INVALID_CURSOR	cursore non esistente.
INVALID_NUMBER	La stringa che si sta elaborando non restituisce un formato valido.
LOGON_DENIED
NO_DATA_FOUND	la select non trova righe
NOT_LOGGED_ON
PROGRAM_ERROR	Oracle incontra un errore interno; non si può fare nulla per risolverlo. E’ solo possibile provare a rieseguire il programma e …pregare.
ROWTYPE_MISMATCH	Non è possible usare uno stesso cursore per lavorare su occorrenze di tipo diverso (o solitamente su tabelle diverse)
STORAGE_ERROR	Problemi con la memoria sul server dove risiede l’istanza del DB. Pregare con la speranza di non dover far ripartire l’istanza perchè rappresenta sempre un certo rischio
TIMEOUT_ON_RESOURCE	Questo errore può verificarsi quando il server è troppo occupato per dare delle risposte in tempi brevi.
TOO_MANY_ROWS	la select trova troppe righe
VALUE_ERROR	Solitamente si verifica quando la dimensione della colonna è troppo grande oppure quando si presentano errori aritmetici.
ZERO_DIVIDE	Si verifica quando in una operazione si tenta di effettuare una divisione con zero
OTHERS	E’ la più importante di tutte le EXCEPTION poiché consente di gestire qualsiasi delle precedenti . Quando si verifica un errore la cui gestione non è stata specificatamente presa in considerazione questo cade nella gestione prevista per OTHERS

Di seguito è riportato nel dettaglio l’elenco dei principali flag di eccezione attivati dal sistema.
• CURSOR_ALREADY_OPEN viene attivato quando un’istruzione OPEN cerca di aprire un cursore che è già aperto. SQLCODE viene impostato a -6511 (il codice di errore è ORA-06511).
• DUP_VAL_ON_INDEX viene attivato quando insert o update provocherebbero un doppione nei valori di un indice. SQLCODE viene impostato a -1 (il codice di errore è ORA-00001).
• INVALID_CURSOR viene attivato quando si cerca di aprire un cursore che non sia stato precedentemente dichiarato, quando si cerca di chiuderne uno che è già stato chiuso o quando si cerca di effettuare un FETCH da un cursore chiuso, e così via. SQLCODE viene impostato su -1001 (il codice di errore è ORA-01001).
• INVALID_NUMBER viene attivato quando si verifica un errore di conversione da una stringa di caratteri a un numero, quando la stringa di caratteri non contiene un numero corretto. SQLCODE viene impostato a -1722 (il codice di errore è ORA-01722).
• LOGIN_DENIED viene attivato quando ORACLE rifiuta il nome dell’utente o la sua password all’atto dell’ingresso nel sistema. SQLCODE viene impostato a –1017 (il codice di errore è ORA-01017).
• NO_DATA_FOUND viene attivato quando un’istruzione di selezione non trova alcuna riga che soddisfi i requisiti richiesti. (Non è la stessa eccezione generata da FETCH quando non restituisce alcuna riga. NO_DATA_FOUND è un flag specifico dell’istruzione select). SQLCODE viene impostato su +100 (il codice di errore è ORA-01403). I codici di errore sono differenti in quanto +100 è il codice di errore
standard ANSI che indica che i dati non sono stati trovati. Si noti che questo codice di errore è positivo; questo in generale significa che l’errore è recuperabile.
• NOT_LOGGED_ON viene attivato quando si cerca di effettuare qualsiasi tipo di operazione su un database senza essersi precedentemente collegati. SQLCODE viene impostato a -1012 (il codice di errore è ORA-01012).
• PROGRAM_ERROR viene attivato quando PL/SQL riscontra dei problemi di esecuzione nel codice. SQLCODE viene impostato a -6501 (il codice di errore è ORA-06501).
• STORAGE_ERROR viene attivato quando PL/SQL necessita di più memoria di quella effettivamente disponibile, o quando individua un problema di corruzione dei dati. SQLCODE viene impostato su -6500 (il codice di errore è ORA-06500).
• TIMEOUT_ON_RESOURCE viene attivato quando una risorsa richiesta da ORACLE non è disponibile quando questo tenta di accedervi. Questa situazione indica generalmente un’istanza che possiede una terminazione non normale. SQLCODE viene impostato a -51 (il codice di errore è ORA-00051).
• TOO_MANY_ROWS viene attivato quando un’istruzione select (o una sotto-query) che dovrebbe restituire una sola riga ne restituisce più d’una. SQLCODE vale -1427 (il codice di errore è ORA-01427).
• TRANSACTION_BACKED_OUT viene attivato quando viene effettuato un rollback della parte remota di una transazione. SQLCODE vale -61 (il codice di errore è ORA-00061).

exception definite dall’utente

sono dichiarate nella parte dichiarativa del blocco
sono richiamate dal comando RAISE nella parte esecutiva

Come devono essere gestiti questi errori ?
Una procedura deve avere il blocco delle EXCEPTION che è sempre l’ultimo blocco della procedura stessa e si presenta con questa sintassi:

Sintassi:
EXCEPTION
WHEN nome_exception THEN operazione

Esempio:
1) EXCEPTION
WHEN NO_DATA_FOUND THEN close cursor c1;
WHEN OTHERS THEN null;

2)         EXCEPTION
WHEN NO_DATA_FOUND THEN
  r-value := 0;
WHEN ZERO_DIVIDE THEN
               r-value := 9999;
WHEN OTHER THEN
Null;

Nella maggior parte delle procedure viene definito il blocco delle EXCEPTION così da porre evidenza su chi o cosa abbia generato l’errore in modo da effettuare eventualmente dei ROLLBACK per mantenere la congruità dei dati.

Esempio :
EXCEPTION
WHEN OTHER THEN
ROLLBACK ;

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

L’utilizzo di queste funzioni insieme al package DBMS_OUTPUT.PUT_LINE consente di puntualizzare con messaggi a video la situazione della procedura nel momento in cui si è verificato l’errore.
Nel caso in cui la procedura venga eseguita via SQL*Plus l’uscita del messaggio è vincolata al settaggio ad ON della variabile di ambiente SERVEROUTPUT.

Esempio:
declare
pi constant NUMBER(9,7) := 3.1415926;
radius INTEGER(5);
area NUMBER(14,2);
some_variable NUMBER(14,2);
begin
radius := 3;
loop
una_variabile := 1/(radius-4);
area := pi*power(radius,2);
insert into AREA values (radius, area);
radius := radius+1;
exit when area >100;
end loop;
exception
when ZERO_DIVIDE
then insert into AREA values (0,0);
end;
.
/

Ecco la sezione per la gestione delle eccezioni del blocco PL/SQL:
WHEN ZERO_DIVIDE
then insert into AREAS values (0,0);
           WHEN OTHERS THEN
              errore := 'errore OTHERS in INSERT su AREA: raggio = '||TO_CHAR(radius);
              DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
            DBMS_OUTPUT.PUT_LINE(errore);
       END;

Nel caso in cui si verifichi un qualsiasi errore durante l’inserimento sulla tabella AREA questo insieme di istruzioni, inserite all’interno di una procedura più in generale, determinano l’interruzione della procedura con l’uscita del messaggio personalizzato dall’utente nella variabile ‘errore’.

Quando si verifica un errore, il blocco PL/SQL ricerca l’eccezione definita nella sezione per la gestione delle eccezioni. In questo caso il blocco trova l’eccezione ZERO_DIVIDE, una delle eccezioni definite dal sistema disponibili nel linguaggio PL/SQL. Oltre alle eccezioni definite dal sistema e a quelle eccezioni definibili dall’utente è possibile utilizzare la clausola when others per indirizzare tutte le eccezioni non definite all’interno della sezione per la gestione delle eccezioni. Viene eseguito il comando all’interno della sezione per la gestione delle eccezioni relativo all’eccezione corrispondente e una riga viene inserita nella tabella tab1. Ecco l’output del blocco PL/SQL:

select *
from tab1;

RADIUS          AREA
----------           --------
3 28.27            0 0

L’output mostra che il primo valore di “Radius” (3) è stato elaborato e che l’errore è stato incontrato durante la seconda esecuzione del ciclo.

NOTA Una volta incontrata un’eccezione non è possibile tornare al normale flusso dell’elaborazione dei comandi all’interno della sezione dei comandi eseguibili. Non è possibile utilizzare un comando goto per passare dalla sezione per la gestione delle eccezioni a quella dei comandi eseguibili del blocco PL/SQL. Per mantenere il controllo all’interno della sezione dei comandi eseguibili occorre utilizzare condizioni if per verificare la possibilità di eccezioni prima che queste vengano incontrate dal programma.

Ricerca degli errori nelle procedure

Il comando SQLPLUS show errors visualizza tutti gli errori associati con l’oggetto procedurale creato per ultimo. Tale comando verifica nella vista del dizionario di dati USER_ERRORS gli errori associati con il più recente tentativo di compilazione per l’oggetto procedurale in esame. Il comando show errors visualizza il numero di riga e di colonna per ciascun errore, insieme al testo del messaggio di errore.
Per visualizzare errori associati con oggetti procedurali creati in precedenza, è possibile utilizzare direttamente una query su USER_ERRORS, come nell’esempio seguente. Le query su USER_ERRORS non sono comuni, in quanto implicano la presenza di errori in due o più procedure. Nell’esempio seguente è descritta una query su USER_ERRORS relativa ai messaggi d’errore incontrati durante la creazione della funzione CONTROLLO_SALDO.

select Line, /*Numero di riga dell™errore./*
Position, /*Numero di colonna dell™errore.*/
Text /*Testo del messaggio di errore.*/
from USER_ERRORS
where Name = ‚CONTROLLO_SALDO™
and Type = ‚FUNCTION™
order by Sequence;

I valori validi per la colonna Type sono PROCEDURE, PACKAGE, FUNCTION e PACKAGE BODY.
Per il prelevamento di informazioni su errori relativi a oggetti procedurali è possibile
utilizzare anche due ulteriori viste del dizionario dati, ALL e DBA.

Utilizzo del package DBMS_OUTPUT
Oltre alle informazioni per il debugging messe a disposizione dal comando show errors, è possibile utilizzare il package DBMS_OUTPUT, creato quando l’opzione procedurale viene installata nel database.
Di seguito è riportato il comando da immettere, per utilizzare DBMS_OUTPUT, prima di eseguire l’oggetto procedurale di cui si desidera effettuare il debugging.

set serveroutput on

Il package DBMS_OUTPUT consente di utilizzare le tre funzioni di debugging elencate di seguito.

PUT Colloca più output sulla stessa riga.
PUT_LINE Colloca ciascuna uscita su una riga separata.
NEW_LINE Utilizzata con PUT; segnala la fine della riga di output corrente.

PUT e PUT_LINE vengono utilizzate per generare le informazioni di debugging che si desidera visualizzare. Se ad esempio si effettua il debugging di una procedura che comprende un ciclo è possibile tenere traccia delle modifiche in una variabile a ogni successiva esecuzione del ciclo in questione. Allo scopo è possibile utilizzare un comando simile a quello descritto nel precedente esempio. In questo esempio viene stampato il valore della colonna Importo preceduto dalla stringa di caratteri ‘Importo:’.
PUT_LINE(‚Importo: ‚||Importo);
PUT e PUT_LINE possono essere utilizzate anche al di fuori dei cicli, ma gli scopi sottesi a tali utilizzi possono essere raggiunti in modo migliore per mezzo del comando RETURN nelle funzioni .

Visualizzazione del codice sorgente di oggetti procedurali esistenti:

Il codice sorgente di procedure, funzioni, package e corpi di package esistenti può essere sottoposto a query dalle viste del dizionario dati di seguito elencate.

USER_SOURCE Per oggetti procedurali di proprietà dell’utente.
ALL_SOURCE Per oggetti procedurali di proprietà dell’utente o per i

quali all’utente è stato concesso l’accesso.

DBA_SOURCE Per tutti gli oggetti procedurali nel database.

Per selezionare informazioni dalla vista USER_SOURCE si utilizza una query simile a quella riportata nell’esempio seguente. In questo esempio è selezionata la colonna Text, ordinata per numero di riga. Il nome dell’oggetto (Name) e il suo tipo (Type) vengono utilizzati per definire gli oggetti il cui codice sorgente deve essere visualizzato. Nell’esempio riportato di seguito viene utilizzata la procedura
NUOVO_LAVORATORE descritta in precedenza.

select Text
from USER_SOURCE
where Name = ‚NUOVO_LAVORATORE™
and Type = ‚PROCEDURE™
order by Line;

TEXT
--------------------------------------------------
procedure NUOVO_LAVORATORE
(Nome_Persona IN varchar2)
AS
BEGIN
insert into LAVORATORE
(Nome, Eta, Alloggio)
values
(Nome_Persona, null, null);
END;

Come riportato nell’esempio precedente, la vista USER_SOURCE contiene un record per ciascuna riga della procedura NUOVO_LAVORATORE. La successione delle righe viene mantenuta dalla colonna Line. Per questo motivo la colonna Line dovrebbe essere utilizzata nella clausola order by, come nell’esempio.
Valori validi per la colonna Type sono PROCEDURE, FUNCTION, PACKAGE e PACKAGE BODY.

You can use this query to see PL/SQL lines before and after the error line. Add it to your list of favorites!

select    decode(to_char(us.line), '1', ue.text||'Pkg:'||us.name||chr(10)||chr(10)||
'   '||to_char(us.line,'99990')||' '||us.text, to_char(ue.line-7),ue.text||'   Pkg:'||us.name||'     ', to_char(ue.line-6),'',to_char(ue.line+6),'',to_char(ue.line) ,'-->'||to_char(us.line,'99990')
||' '||us.text,'   '||to_char(us.line,'99990')||' '||us.text) outline
from user_source us, user_errors ue
where us.line between (ue.line-7) and (ue.line+6)
and us.name = ue.name
and us.type = ue.type
and ue.text not like 'PL/SQL: Statement ignored'
and ue.text not like 'PL/SQL: Declaration ignored'
order by ue.name, ue.line, ue.text, us.line;

Here is some sample output from the above query:

PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( @ % ;
The symbol ":= was inserted before "=" to continue.
Pkg:FOO

       20    LIMITATIONS:
21    ALGORITHM:
22    NOTES:
23 ******************************************************************************/
24 BEGIN
-->   25    tmpVar = 0;
26
27    EXCEPTION
28      WHEN NO_DATA_FOUND THEN
29        Null;
30

Compilazione di procedure, funzioni e package

ORACLE compila gli oggetti procedurali al momento della loro creazione, ma questi possono divenire non più validi se gli oggetti di database a cui fanno riferimento cambiano. Alla successiva esecuzione degli oggetti procedurali, questi verranno ricompilati dal database.
Per evitare questa compilazione durante l’esecuzione e il degrado delle prestazioni
che essa può causare, è possibile ricompilare esplicitamente le procedure, le
funzioni e i package. Per ricompilare una procedura si utilizza il comando alter procedure,
come nell’esempio seguente. La clausola compile è l’unica opzione valida per tale comando.
alter procedure NUOVO_LAVORATORE compile;
Per utilizzare questo comando è necessario possedere la procedura o disporre del privilegio di sistema ALTER ANY PROCEDURE.

Per ricompilare una funzione si utilizza il comando alter function con la clausola compile.
alter function NUOVO_LAVORATORE compile;

Per utilizzare questo comando è necessario possedere la funzione o disporre del privilegio di sistema ALTER ANY PROCEDURE.

Per ricompilare i package è possibile sia ricompilare la specificazione e il corpo dello stesso, sia ricompilare il solo corpo. L’impostazione predefinita prevede la ricompilazione della specificazione e del corpo del package. I comandi alter function o alter procedure non possono essere utilizzati per ricompilare funzioni e procedure memorizzate all’interno di un package.
Se il codice sorgente per le procedure o funzioni all’interno del corpo del package è cambiato, mentre la specificazione del package non è cambiata, è possibile ricompilare il solo corpo del package stesso. Nella maggior parte dei casi è più appropriato ricompilare sia la specificazione, sia il corpo del package.

Di seguito è riportata la sintassi per il comando alter package.
alter package [utente.] nome_package
compile [PACKAGE | BODY];

Per ricompilare un package si utilizza il comando alter package descritto in pre-cedenza
con la clausola compile, come nell’esempio seguente.
alter package REGISTRO_PACKAGE compile;
Per utilizzare questo comando è necessario possedere il package o disporre del privilegio di sistema ALTER ANY PROCEDURE.
Dato che nell’esempio precedente non sono stati specificati né ‘PACKAGE’, né ‘BODY’, è stata utilizzata l’impostazione predefinita ‘PACKAGE’, il cui risultato è la ricompilazione sia della specifica-zione,
sia del corpo del package.

Procedure e Funzioni

What is the difference between a PL/SQL function and procedure?
A function returns a result. This difference has to be reflected in the structure. Even here the difference is really minute.

Where a procedure was declared as:
PROCEDURE proc_name (parameterlist) IS

A function is declared as:
FUNCTION FUNC_NAME (ParameterList) RETURN TYPE IS

The difference is the reserved word RETURN and a variable TYPE such as NUMBER or VARCHAR or DATE.
Because the function returns a value, the function will also need TO declare a variable and a RETURN variable statement.
Esempio:
CREATE OR REPLACE
FUNCTION ODDITY (Num_In IN INTEGER) RETURN INTEGER
IS
Odd_out INTEGER; -- declare the return variable
BEGIN
    Odd_Out := 2 * Num_In - 1;
    RETURN Odd_Out;
EXCEPTION
    WHEN OTHERS THEN -- any error at all
        RETURN 0;
END;

EXCEPTION
WHEN OTHERS THEN -- any error at all
RETURN 0;
END;

This function apparently returns the nth odd number given n. The function may be used within you schema like any other Oracle function.
corso sql

TRIGGER :

I Triggers sono procedure prive di parametri che vengono eseguiti sia prima che dopo un inserimento , una cancellazione od una modifica di righe su di una tabella. Poiché vengono eseguiti al verificarsi di un certo evento (inserimento, cancellazione o modifica) e non per scelta questi non possono avere parametri.
Un trigger definisce un’azione che il database deve intraprendere
quando si verifica un determinato evento correlato al database stesso. I
trigger possono essere utilizzati per migliorare l’integrità differenziale dichiarativa,
per imporre complesse regole legate all’attività o per effettuare revisioni sulle modifiche
ai dati. Il codice all’interno di un trigger, detto corpo del trigger, è costituito
da blocchi PL/SQL .
L’esecuzione dei trigger è trasparente all’utente. I trigger vengono eseguiti dal
database quando specifici tipi di comandi di manipolazione dei dati vengono eseguiti
su specifiche tabelle. Tali comandi possono comprendere insert, update e delete.
Anche gli aggiornamenti di specifiche colonne possono essere utilizzati come trigger
di eventi.
Grazie alla loro flessibilità, i trigger possono aggiungere integrità referenziale,
ma non possono essere utilizzati in sostituzione di questa. Per imporre le regole legate
all’attività in un’applicazione è necessario in primo luogo affidarsi all’integrità
referenziale dichiarativa disponibile con ORACLE. Quindi si potranno utilizzare
trigger per imporre regole che non possono essere codificate attraverso l’integrità
differenziale.

L’esempio sotto riportato carica il PERSONAL_ID sulla tabella PEOPLE:

DECLARE X NUMBER;
BEGIN
    IF :new.PERSON_ID = 0 THEN
        SELECT PERSON_ID_SEQ.NEXTVAL INTO X FROM dual;
        :new.PERSON_ID := X;
    END IF;
END;

La notazione :NEW indica il valore che sarà inserito.
In una UPDATE è anche possible interrogare qual’era il valore che si aveva prima di effettuare la modifica e questo valore lo si trova nel campo identificato con :OLD. Other than these little differences a trigger obeys all the procedural rules.
L’esempio sottostante equivale al precedente con una notazione di scrittura semplificata.

BEGIN
    IF :new.PERSON_ID = 0 THEN
        SELECT PERSON_ID_SEQ.NEXTVAL INTO :new.PERSON_ID FROM DUAL;
    END IF;
END;

CREAZIONE O CANCELLAZIONE DI TRIGGER IN SQL*PLUS

Sintassi::
TRIGGER Trigger_Name
BEFORE | AFTER
INSERT | UPDATE | DELETE OF Column_Name ON Table_Name
FOR EACH ROW
DECLARE Variable variable_TYPE;
BEGIN
…
END;

Esempio:

TRIGGER PEOPLE_FORMAT_NAME
BEFORE
INSERT OR UPDATE OF LAST_NAME, FIRST_NAME, M_I, GENDER ON PEOPLE
FOR EACH ROW
DECLARE X VARCHAR2(25);
BEGIN
X := INITCAP(:new.LAST_NAME);
:new.LAST_NAME := X;
X := INITCAP(:new.FIRST_NAME);
:new.FIRST_NAME := X;
X := INITCAP(:new.M_I);
:new.M_I := X;
X := INITCAP(:new.GENDER);
:new.GENDER := X;
END;

corso sql

TIPI DI TRIGGER

Esistono 14 tipi di trigger. Un tipo di trigger è definito dal tipo di transazione e dal
livello al quale il trigger viene eseguito. Nei paragrafi seguenti sono riportate le descrizioni
di tali classificazioni, insieme alle corrispondenti restrizioni.

Trigger a livello di riga
I trigger a livello di riga vengono eseguiti una volta per ciascuna riga di una transazione.
I trigger a livello di riga costituiscono il tipo più comune: vengono spesso utilizzati
in applicazioni di revisione dei dati e si rivelano utili per mantenere sincronizzati i
dati distribuiti.
Per creare trigger a livello di riga occorre utilizzare la clausola FOR EACH ROW nel
comando create trigger. La sintassi dei trigger è riportata in precedenza

Trigger a livello di istruzione
I trigger a livello di istruzione vengono eseguiti una sola vola per ciascuna transazione.
I trigger a livello di istruzione non vengono pertanto utilizzati per attività correlate
ai dati. Tali trigger vengono normalmente utilizzati per imporre misure aggiuntive di
sicurezza sui tipi di transazione che possono essere eseguiti su una tabella.
I trigger a livello di istruzione sono il tipo predefinito creato dal comando create
trigger.

Trigger BEFORE e AFTER
I trigger, in quanto intervengono a causa di eventi precisi, possono essere impostati
in modo da intervenire immediatamente prima o dopo tali eventi. Dato che gli
eventi in grado di eseguire trigger sono transazioni di database, i trigger possono essere
eseguiti immediatamente prima o dopo l’utilizzo dei comandi insert, update e
delete.
All’interno del trigger è possibile fare riferimento ai vecchi e nuovi valori coinvolti
nella transazione. L’accesso richiesto per i vecchi e i nuovi dati può determinare
quale tipo di trigger sia necessario. “Vecchi” si riferisce ai dati nello stato
precedente alla transazione. I comandi update e delete fanno normalmente riferimento
ai vecchi valori. I valori “nuovi” sono i valori dei dati creati dalla transazione
(come le colonne in un record sottoposto a insert).
Per impostare un valore di colonna in una riga inserita mediante un trigger, è necessario
utilizzare un trigger BEFORE INSERT per accedere ai valori “nuovi”.
L’utilizzo di un trigger AFTER INSERT non consentirebbe di impostare il valore
inserito, in quanto la riga sarebbe già stata inserita nella tabella.
I trigger AFTER a livello di riga vengono utilizzati frequentemente in applicazioni
di revisione, in quanto non vengono attivati sino a che la riga non viene modificata.
L’avvenuta modifica della riga implica che quest’ultima abbia superato con
successo le restrizioni di integrità referenziale definite per quella tabella.

Trigger INSTEAD OF
In ORACLE8 è possibile utilizzare trigger INSTEAD OF per specificare che cosa
fare invece di eseguire le azioni che hanno attivato il trigger. Ad esempio, è possibile
utilizzare un trigger INSTEAD OF per reindirizzare gli insert in una tabella verso
una tabella differente o per aggiornare con update più tabelle che siano parte di una
vista. Ad esempio, se una vista comporta l’unione di due tabelle, la possibilità di utilizzare
il comando update su record della vista è limitata. Utilizzando un trigger INSTEAD
OF è possibile specificare a ORACLE come effettuare update, delete o insert di
record sulle tabelle sottostanti alla vista quando un utente tenta di modificare i valori
attraverso la vista stessa. Il codice del trigger INSTEAD OF viene eseguito al
posto del comando insert, update o delete immesso.

Tipi di trigger validi
Combinando i differenti tipi di azione di trigger si ottengono 14 configurazioni possibili.

BEFORE INSERT riga
BEFORE INSERT istruzione
AFTER INSERT riga
AFTER INSERT istruzione
BEFORE UPDATE riga
BEFORE UPDATE istruzione
AFTER UPDATE riga
AFTER UPDATE istruzione
BEFORE DELETE riga
BEFORE DELETE istruzione
AFTER DELETE riga
AFTER DELETE istruzione
INSTEAD OF riga
INSTEAD OF istruzione

I trigger UPDATE possono dipendere dalle colonne aggiornate
Nella progettazione di un trigger è possibile una grande flessibilità. Le parole
chiave before e after indicano se il trigger dovrà essere eseguito prima o dopo la
transazione che attiva il trigger stesso. Con la clausola instead of il codice del trigger
verrà eseguito in luogo dell’evento che ha causato l’esecuzione del trigger stesso. Le
parole chiave delete, insert e update (l’ultima delle quali può comprendere un elenco
di colonne) indicano il tipo di manipolazione dei dati che costituirà un evento in
grado di attivare il trigger. Per fare riferimento ai vecchi e nuovi valori delle colon-ne
è possibile utilizzare i valori predefiniti (“old” e “new”) o servirsi della clausola
referencing per specificare altri nomi.
Con la clausola for each row si ha un trigger a livello di riga, altrimenti si ha un
trigger a livello di istruzione. La clausola when viene utilizzata per applicare ulterio-ri
restrizioni al momento dell’esecuzione del trigger. Le restrizioni imposte per mez-zo
della clausola when possono comprendere verifiche dei vecchi e nuovi valori dei
dati.
Si supponga, ad esempio, di voler monitorare qualsiasi variazione di un importo
superiore al 10 per cento. Il trigger a livello di riga BEFORE UPDATE di seguito
descritto verrà eseguito solo se il nuovo valore della colonna Importo sarà maggiore,
rispetto al vecchio valore, in misura superiore al 10 per cento.
L’esempio seguente descrive anche l’utilizzo della parola chiave new, che fa riferimento al nuovo
valore della colonna, e di old, che fa riferimento al vecchio valore della colonna.

create trigger registro_bef_upd_row
before update on REGISTRO
for each row
when (new.Importo/old.Importo>1.1)
begin
insert into REGISTRO_AUDIT
values (:old.DataAzione, :old.Azione, :old.Articolo,
:old.Quantita, :old.TipoQuantita, :old.Tasso,
:old.Importo, :old.Persona);
end;

La suddivisione di questo comando create trigger nei suoi componenti rende più
semplice la comprensione. In primo luogo viene assegnato un nome al trigger.
create trigger registro_bef_upd_row
Il nome del trigger contiene il nome della tabella su cui agisce e il tipo del trigger
stesso.

before update on REGISTRO
Questo trigger si applica alla tabella REGISTRO. Il trigger verrà eseguito prima
di aggiornare (before update) le transazioni trasmesse al database.
for each row
A causa dell’utilizzo della clausola for each row il trigger verrà applicato a ciascuna
riga della transazione. Se tale clausola non viene utilizzata, il trigger verrà eseguito
solo al livello delle istruzioni.
when (new.Importo/old.Importo>1.1)
La clausola when aggiunge ulteriori criteri alla condizione di trigger. L’evento di
trigger deve non solo essere un aggiornamento (update) della tabella REGISTRO,
ma anche rispecchiare un aumento superiore al 10 per cento del valore della colonna
Importo.
begin
insert into REGISTRO_AUDIT
values (:old.DataAzione, :old.Azione, :old.Articolo,
:old.Quantita, :old.TipoQuantita, :old.Tasso,
:old.Importo, :old.Persona);
end;
Il codice PL/SQL dell’esempio precedente è il corpo del trigger. I comandi in
esso contenuti verranno eseguiti a ogni aggiornamento (update) della tabella REGISTRO
che soddisfi la condizione when. Perché ciò accada, deve esistere la tabella
REGISTRO_AUDIT e il proprietario del trigger deve disporre di privilegi (diretti
e non derivanti da un ruolo) rispetto a tale tabella. In questo particolare esempio si
effettua l’insert dei vecchi valori dal record REGISTRO nella tabella REGISTRO_
AUDIT prima che il record REGISTRO venga aggiornata.

NOTA Per il riferimento le parole chiave new e old nel blocco PL/SQL
sono precedute dal segno di due punti (:).

Questo esempio è un tipico trigger di revisione. L’attività di revisione è comple-tamente
trasparente all’utente che esegue l’update della tabella REGISTRO.
In questo esempio la transazione è avvenuta all’interno della tabella REGISTRO
e un’altra transazione è stata eseguita all’interno di REGISTRO_AUDIT a
causa del trigger. Se si desidera che la transazione in REGISTRO_AUDIT avvenga
senza inserire un record in REGISTRO, è necessario utilizzare un trigger IN-STEAD
OF. Il trigger INSTEAD OF verrà anch’esso creato sulla tabella REGI-STRO,
ma il suo codice verrà eseguito senza che venga effettuata implicitamente
anche la transazione che causa la stessa esecuzione del trigger. Se si crea un trigger
INSTEAD OF per gli insert nella tabella REGISTRO, tale trigger sarà in grado di
inserire (insert) record in REGISTRO_AUDIT, senza inserire alcun record nella tabella
REGISTRO.

COMBINAZIONI DI TRIGGER DI TIPO DIFFERENTE
I trigger per comandi multipli di insert, update e delete su una tabella possono essere
combinati in un singolo trigger, posto che siano tutti dello stesso livello (di riga o di
istruzione). Nell’esempio seguente è descritto un trigger che viene eseguito ogni qualvolta
avviene un insert o un update.
In tale esempio, due punti di particolare importanza sono indicati in grassetto: la
parte update del trigger interviene solo quando la colonna Importo viene aggiornata
e all’interno del blocco PL/SQL viene utilizzata una clausola if per determinare quale
dei due comandi ha eseguito il trigger.

create trigger registro_bef_upd_ins_row
before insert or update of Importo on REGISTRO
for each row
begin
IF INSERTING THEN
insert into REGISTRO_AUDIT
values (:new.DataAzione, :new.Azione, :new.Articolo,
:new.Quantita, :new.TipoQuantita, :new.Tasso,
:new.Importo, :new.Persona);
ELSE -- se non è un inserimento, si aggiorna Importo
insert into REGISTRO_AUDIT
values (:old.DataAzione, :old.Azione, :old.Articolo,
:old.Quantita, :old.TipoQuantita, :old.Tasso,
:old.Importo, :old.Persona);
end if;
end;

Si può scomporre ancora il trigger nelle sue componenti. Inizialmente il trigger
riceve un nome e viene identificato come trigger before insert e before update (del valore
Importo), da eseguirsi per ogni riga (for each row).
create trigger registro_bef_upd_ins_row
before insert or update of Importo on REGISTRO
for each row
Segue quindi il corpo del trigger. Nella prima parte, riportata di seguito, avviene
il controllodel tipo di transazione per mezzo di una clausola if. I tipi di transazione
validi sono INSERTING, DELETING e UPDATING. In questo caso il trigger verifica se il
record deve essere inserito nella tabella REGISTRO. In caso affermativo viene ese-guita
la prima parte del corpo del trigger. La parte INSERTING del corpo del trigger
inserisce i nuovi valori del record nella tabella REGISTRO_AUDIT.
begin
IF INSERTING THEN
insert into REGISTRO_AUDIT
values (:new.DataAzione, :new.Azione, :new.Articolo,
:new.Quantita, :new.TipoQuantita, :new.Tasso,
:new.Importo, :new.Persona);
Possono quindi essere verificati altri tipi di transazioni. In questo esempio, dal
momento in cui il trigger è stato eseguito, la transazione deve essere un insert o un
update della colonna Importo. Dato che la clausola if nella prima metà del corpo del
trigger verifica gli insert e il trigger viene eseguito solo per insert e update, l’unica
condizione in grado di eseguire la seconda metà del corpo del trigger è data da
update di Importo. Per questo motivo non saranno necessarie ulteriori clausole if per
determinare il tipo di transazione. Questa parte del corpo del trigger è identica a
quella precedente. Prima di essere aggiornati, i vecchi valori nella riga sono scritti
nella tabella REGISTRO_AUDIT.
ELSE -- se non è un inserimento, si aggiorna Importo
insert into REGISTRO_AUDIT
values (:old.DataAzione, :old.Azione, :old.Articolo,
:old.Quantita, :old.TipoQuantita, :old.Tasso,
:old.Importo, :old.Persona);
end;
La combinazione di trigger di tipo differente può agevolare la coordinazione
dello sviluppo di trigger tra più sviluppatori, in quanto aiuta a consolidare tutti gli
eventi di database che dipendono da una singola tabella.
Impostazione di valori inseriti
I trigger possono essere utilizzati per impostare valori di colonna durante insert e
update. Ad esempio, la tabella REGISTRO può essere stata parzialmente denormalizzata
per comprendere dati derivati come UPPER(Persona). La memorizzazione di
questi dati in maiuscolo in una colonna della tabella (ad esempio MaiuscPersona)
consente la visualizzazione dei dati agli utenti nel loro formato naturale, pur utilizzando
la colonna maiuscola durante le query.
I dati MaiuscPersona, essendo derivati, possono non essere sincronizzati con la
colonna Persona. Ciò significa che possono verificarsi intervalli di tempo, immediatamente
dopo le transazioni, durante i quali MaiuscPersona non è uguale a
UPPER(Persona). Si consideri un inserimento nella tabella REGISTRO; a meno che
l’applicazione utilizzata non fornisca un valore per MaiuscPersona durante insert, il
valore di quella colonna sarà NULL.
Per evitare questo problema di sincronia, è possibile utilizzare un trigger di database.
Si creano un trigger BEFORE INSERT e un trigger BEFORE UPDATE per
la tabella. Questi agiranno a livello di riga. Come nell’esempio seguente, i trigger
imposteranno un nuovo valore per MaiuscPersona a ogni modifica di Persona.
create trigger registro_bef_upd_ins_row
before insert or update of Persona on REGISTRO
for each row
begin
:new.MaiuscPersona := UPPER(:new.Persona);
end;
In questo esempio il corpo del trigger determina il valore per MaiuscPersona
utilizzando la funzione UPPER della colonna Persona. Tale trigger verrà eseguito
ogni volta che una riga viene inserita nella tabella REGISTRO e ogni volta che la
colonna Persona viene aggiornata. Le due colonne verranno così mantenute in sincronia.

Conservazione di dati duplicati
Il metodo di impostazione di valori per mezzo di trigger descritto nel paragrafo precedente
può essere combinato con i metodi di accesso ai dati remoti
L’utilizzo di trigger per la duplicazione dei dati è ridondante in database
dotati dell’opzione distribuita: a tale scopo vengono utilizzati gli snapshot.
Se però non si dispone dell’opzione distribuita, è possibile utilizzare i
trigger per conservare copie remote (sullo stesso host) delle tabelle. Come avviene
per gli snapshot, è possibile replicate tutte o parte delle righe di una tabella.
Si può ad esempio desiderare di creare una copia del proprio registro di revisione
dell’applicazione. In questo modo ci si cautela contro l’eliminazione, da parte di
un’unica applicazione, di tutti i record del registro di revisione creati da più applica-zioni.
La duplicazione dei registri di revisione viene utilizzata di frequente nel moni-toraggio
di sicurezza.
Si consideri la tabella REGISTRO_AUDIT utilizzata nei precedenti esempi del
presente capitolo. È possibile creare una seconda tabella, REGISTRO_AUDIT_
DUP, possibilmente in un database remoto. Per questo esempio si assuma che un
link di database denominato AUDIT_LINK possa essere utilizzato per connettere
l’utente al database in cui risiede REGISTRO_AUDIT_DUP (per ulteriori infor-mazioni
sui link di database si rimanda al Capitolo 21).
Per automatizzare la popolazione della tabella REGISTRO_AUDIT_DUP, è
possibile applicare alla tabella REGISTRO_AUDIT il trigger descritto di seguito.

create trigger registro_after_ins_row
before insert on REGISTRO_AUDIT
for each row
begin
insert into REGISTRO_AUDIT_DUP@AUDIT_LINK
values (:new.DataAzione, :new.Azione, :new.Articolo,
:new.Quantita, :new.TipoQuantita, :new.Tasso,
:new.Importo, :new.Persona);
end;

Come si deduce dalla sua intestazione, questo trigger viene eseguito per ciascuna
riga che viene inserita nella tabella REGISTRO_AUDIT. Esso inserisce un singolo
record nella tabella REGISTRO_AUDIT_DUP, nel database definito dal link
AUDIT_LINK. Se si utilizza ORACLE con l’opzione distribuita, AUDIT_LINK
può puntare a un database collocato su un server remoto, anche se in questo caso è
consigliato l’utilizzo di uno snapshot .

PERSONALIZZAZIONE DELLE CONDIZIONI D’ERRORE
All’interno di un singolo trigger è possibile stabilire differenti condizioni d’errore.
Per ciascuna delle condizioni d’errore definite è possibile selezionare un messaggio
visualizzato quando l’errore stesso interviene. I numeri degli errori e i messaggi vi-sualizzati
all’utente vengono impostati mediante la procedura RAISE_APPLICATION_ERROR.
Tale procedura può essere richiamata dall’interno di qualsiasi trigger.
Nell’esempio seguente è riportato un trigger a livello di istruzione BEFORE
DELETE sulla tabella REGISTRO. Quando un utente tenta il delete di un record
della tabella REGISTRO, questo trigger viene eseguito e verifica due condizioni di
sistema: che il giorno della settimana non sia sabato o domenica e che il nome uten-
te ORACLE dell’account che effettua l’eliminazione inizi con le lettere ‘FIN’. I
componenti del trigger sono descritti dopo l’esempio.

create trigger registro_bef_del
before delete on REGISTRO
declare
weekend_error EXCEPTION;
not_finance_user EXCEPTION;
begin
IF TO_CHAR(SysDate,™DY™) = ‚SAT™ or
TO_CHAR(SysDate,™DY™) = ‚SUN™ THEN
RAISE weekend_error;
END IF;
IF SUBSTR(User,1,3) <> ‚FIN™ THEN
RAISE not_finance_user;
EXCEPTION
WHEN weekend_error THEN
RAISE_APPLICATION_ERROR (-20001,
‚Eliminazioni non lecite nei fine settimana™);
WHEN not_finance_user THEN
RAISE_APPLICATION_ERROR (-20002,
‚Eliminazioni permesse solo a utenti finanziari™);
end;

L’intestazione definisce un trigger livello di istruzione BEFORE DELETE.
create trigger registro_bef_del
before delete on REGISTRO
In questo trigger non compaiono clausole when e il corpo del trigger verrà ese-guito
per tutti i delete.
La successiva parte del trigger dichiara i nomi delle due eccezioni che sono defi-nite
all’interno del trigger stesso.
declare
weekend_error EXCEPTION;
not_finance_user EXCEPTION;
La prima parte del corpo del trigger contiene una clausola if che utilizza la funzione
TO_CHAR sulla pseudocolonna SysDate. Se il giorno corrente è sabato o domenica,
verrà eseguita la condizione di errore WEEKEND_ERROR. Tale condizione di errore, detta eccezione, deve essere definita all’interno del corpo del trigger.
begin
IF TO_CHAR(SysDate,™DY™) = ‚SAT™ or
TO_CHAR(SysDate,™DY™) = ‚SUN™ THEN
RAISE weekend_error;
END IF;
Una seconda clausola if verifica che le prime tre lettere della pseudocolonna
User siano ‘FIN’. Se il nome utente non inizia con ‘FIN’, verrà eseguita l’eccezione
NOT_FINANCE_USER.
In questo esempio viene utilizzato l’operatore <>, equivalente a != (che significa “non è uguale”).
IF SUBSTR(User,1,3) <> ‚FIN™ THEN
RAISE not_finance_user;
La parte finale del corpo del trigger specifica come gestire le eccezioni. Tale parte
inizia con la EXCEPTION, seguita da una clausola when per ciascuna delle ecce-zioni.
Ciascuna eccezione nel trigger richiama la procedura RAISE_APPLI-CATION_ERROR.
La procedura RAISE_APPLICATION_ERROR riceve due parametri di input:
il numero dell’errore (che deve essere compreso tra -20001 e -20999) e il messaggio
d’errore da visualizzare. In questo esempio sono definiti due differenti messaggi
d’errore, uno per ciascuna delle eccezioni definite.
EXCEPTION
WHEN weekend_error THEN
RAISE_APPLICATION_ERROR (-20001,‚Eliminazioni non lecite nei fine settimana™);
WHEN not_finance_user THEN
RAISE_APPLICATION_ERROR (-20002,‚Eliminazioni permesse solo a utenti finanziari™);
end;
L’utilizzo della procedura RAISE_APPLICATION_ERROR consente grande
flessibilità nella gestione delle condizioni di errore che possono essere incontrate all’interno
del trigger.

ASSEGNAZIONE DEI NOMI AI TRIGGER
Il nome di un trigger deve indicare chiaramente la tabella a cui esso si applica, i comandi
DML che attivano il trigger, il suo stato before/after e se il trigger è a livello
di riga o di istruzione. Dato che il nome di un trigger non può superare i 30 caratteri,
è necessario utilizzare una serie standard di abbreviazioni. In generale il nome
del trigger deve comprendere una parte quanto più possibile ampia del nome della
tabella. Così, per creare un trigger a livello di riga BEFORE UPDATE sulla tabella
FOGLIO_BILANCIO, il nome assegnato al trigger non dovrebbe essere BEFORE_UPDATE_LIVELLO_RIGA_FOG_BIL. Un nome migliore potrebbe essere
FOGLIO_BILANCIO _BU_RIGA.

ATTIVAZIONE E DISATTIVAZIONE DEI TRIGGER
A differenza dalle restrizioni di integrità dichiarativa (come NOT NULL e PRIMARY
KEY), i trigger non hanno effetto su tutte le righe di una tabella, ma trigger influiscono
solo sulle transazioni del tipo specificato e solo mentre il trigger è abilitato.
Qualsiasi transazione creata prima della creazione di un trigger non verrà influenza-ta
dal trigger stesso.
Nel momento in cui viene creato, un trigger è per default attivato. Esistono però
situazioni in cui può essere necessario disattivare un trigger. Le due ragioni più comuni
riguardano il caricamento dei dati. Durante grandi caricamenti di dati possono
essere disattivati i trigger che verrebbero eseguiti durante il caricamento stesso; ciò
può migliorare enormemente le prestazioni. Una volta completato il caricamento
dei dati, sarà necessario eseguire manualmente il trattamento dei dati che sarebbe
stato effettuato dal trigger se questo fosse stato attivo.
La seconda ragione per la disattivazione di un trigger, correlata al caricamento
dei dati, interviene quando un caricamento di dati non viene completato con successo
e deve essere effettuato una seconda volta. In tal caso è probabile che il carica-mento
dei dati abbia avuto successo parziale e che il trigger sia stato eseguito per
una parte dei record interessati. Durante un successivo caricamento dei dati, verrebbero
inseriti gli stessi record. Così, è possibile che lo stesso trigger venga eseguito
due volte per la stessa transazione (quando tale transazione interviene durante ambedue
i caricamenti).
A seconda della natura della transazione e dei trigger ciò può risultare errato. Se
il trigger era attivato durante il caricamento non completato con successo, sarà necessario
disattivarlo prima di avviare un secondo processo di caricamento dei dati.
Una volta che i dati sono stati caricati, sarà necessario eseguire manualmente il trattamento
dei dati che sarebbe stato effettuato dal trigger se questo fosse stato attivo
durante il caricamento dei dati stessi.
Per attivare un trigger si utilizza il comando alter trigger con la parola chiave enable.
Per utilizzare tale comando è necessario possedere la tabella o disporre del pri-vilegio
di sistema ALTER ANY TRIGGER.
Nell’esempio seguente è riportato un modello di comando alter trigger.
alter trigger registro_bef_upd_row enable;
Un secondo metodo per l’attivazione dei trigger utilizza il comando alter table
con la clausola enable all triggers. Con questo comando non è possibile attivare trigger
specifici (per tale scopo è necessario utilizzare il comando alter trigger).

Nell’esempio seguente è descritto l’utilizzo del comando alter table:
alter table REGISTRO enable all triggers;
Per utilizzare il comando alter table è necessario possedere la tabella o disporre
del privilegio di sistema ALTER ANY TABLE.
I trigger possono essere disattivati utilizzando gli stessi comandi di base (e con
necessità di disporre degli stessi privilegi) con clausole modificate. Per il comando
alter trigger si utilizza la clausola disable.
alter trigger registro_bef_upd_row disable;
Per il comando alter table si utilizza utilizzare la clausola disable all triggers, come
nell’esempio seguente:
alter table REGISTRO disable all triggers;
A partire da ORACLE7.3 è possibile compilare i trigger. Per compilare manualmente
trigger esistenti che siano divenuti non validi si utilizza il comando
alter trigger compile.
I trigger, che a partire da ORACLE7.3 hanno delle dipendenze, possono
divenire non validi se l’oggetto da cui dipendono viene modificato. Il comando alter
trigger debug, disponibile anch’esso a partire da ORACLE7.3, consente la generazione
di informazioni PL/SQL durante la ricompilazione dei trigger.

EXPLAIN PLAN

Purpose

To determine the execution plan Oracle7 follows to execute a specified SQL statement. This command inserts a row describing each step of the execution plan into a specified table. If you are using cost-based optimization, this command also determines the cost of executing the statement.

Prerequisites

To issue an EXPLAIN PLAN statement, you must have the privileges necessary to insert rows into an existing output table that you specify to hold the execution plan. For information on these privileges, see the INSERT command on page 4 - 361.

You must also have the privileges necessary to execute the SQL statement for which you are determining the execution plan. If the SQL statement accesses a view, you must have privileges to access any tables and views on which the view is based. If the view is based on another view that is based on a table, you must have privileges to access both the other view and its underlying table.

To examine the execution plan produced by an EXPLAIN PLAN statement, you must have the privileges necessary to query the output table. For more information on these privileges, see the SELECT command on page 4 - 406.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the output table's creation label or you must satisfy one of the following criteria:

If the output table's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges.

If the output table's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

>>--- EXPLAIN PLAN |----------------------------------|----------------------à
| |
--à SET STATEMENT_ID = ‘Text’ --à

>-|--------------------------------------------------|--- FOR STATEMENT ------à
| |
à INTO --|-----------|-- TABLE --|------------|---
-- schema. - -- @dblink --

Keywords and Parameters

SET specifies the value of the STATEMENT_ID column for the rows of the execution plan in the output table. If you omit this clause, the STATEMENT_ID value defaults to null.
INTO specifies the schema, name, and database containing the output table. This table must exist before you use the EXPLAIN PLAN command. If you omit schema, Oracle7 assumes the table is in your own schema.
The dblink can be a complete or partial name of a database link to a remote Oracle7 database where the output table is located. For information on referring to database links, see the section, "Referring to Objects in Remote Objects," on page 2 - 13. You can only specify a remote output table if you are using Oracle7 with the distributed option. If you omit dblink, Oracle7 assumes the table is on your local database.
If you omit the INTO clause altogether, Oracle7 assumes an output table named PLAN_TABLE in your own schema on your local database.
FOR specifies a SELECT, INSERT, UPDATE, or DELETE statement for which the execution plan is generated.

Usage Notes

The definition of a sample output table PLAN_TABLE is available in SQL script on your distribution media. Your output table must have the same column names and datatypes as this table. The common name of this script is UTLXPLAN.SQL , although the exact name and location may vary depending on your operating system.

The value you specify in the SET clause appears in the STATEMENT_ID column in the rows of the execution plan. You can then use this value to identify these rows among others in the output table. Be sure to specify a STATEMENT_ID value if your output table contains rows from many execution plans.

Since the EXPLAIN PLAN command is a Data Manipulation Language command, rather than a Data Definition Language command, Oracle7 does not implicitly commit the changes made by an EXPLAIN PLAN statement. If you want to keep the rows generated by an EXPLAIN PLAN statement in the output table, you must commit the transaction containing the statement.

You should not use the EXPLAIN PLAN command to determine the execution plans of SQL statements that access data dictionary views or dynamic performance tables.

You can also issue the EXPLAIN PLAN command as part of the SQL trace facility. For information on how to use the SQL trace facility and how to interpret execution plans, see Appendix A "Performance Diagnositic Tools" of Oracle7 Server Tuning.

Example

This EXPLAIN PLAN statement determines the execution plan and cost for an UPDATE statement and inserts rows describing the execution plan into the specified OUTPUT table with the STATEMENT_ID value of 'Raise in Chicago':

EXPLAIN PLAN
      SET STATEMENT_ID = 'Raise in Chicago'
      INTO output (=PLAN_TABLE)
      FOR UPDATE emp
            SET sal = sal * 1.10
            WHERE deptno = (SELECT deptno
                             FROM dept
                             WHERE loc = 'CHICAGO')

This SELECT statement queries the OUTPUT table and returns the execution plan and the cost:

SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options,
object_name, position
FROM output
START WITH id = 0 AND statement_id = 'Raise in Chicago'
CONNECT BY PRIOR id = parent_id AND

statement_id = 'Raise in Chicago'

The query returns this execution plan:

OPERATION         OPTIONS     OBJECT_NAME       POSITION
UPDATE            STATEMENT                     1
FILTER                                         0
TABLE ACCESS      FULL        EMP               1
TABLE ACCESS      FULL        DEPT              2

The value in the POSITION column of the first row shows that the statement has a cost of 1.

STATIC SQL AND DYNAMIC SQL

Many of today's front-end Oracle database-application programs include only static SQL statements. In this scenario, a developer designs an application by planning the possible transactions and then embedding the corresponding SQL statements in the application. (See Footnote at the end of this article.) Consequently, the application has a limited number of well-defined database-access methods that are effectively compiled as part of the application.
Static SQL can be good or bad, depending on what you require from an application. For example, static SQL is perfect when you want to design an application that precisely controls what users can and cannot do: Because you embed all possible SQL statements in the application, users cannot stray from the application's intended uses. But when you want to design an application that is flexible enough to handle unforeseen transactions, SQL statements, or variations of SQL statements, static SQL--because of its very nature--cannot help you. Thus the need for dynamic SQL.
With dynamic SQL, database applications can build SQL statements at runtime, which means you can create applications that are flexible to users' changing needs. You can think of a tool such as SQL*Plus, for example, as an easy-to-use interface to dynamic SQL that users can work with to issue any type of SQL statement.

INTRODUCTION TO DYNAMIC SQL

The first thing to understand about using dynamic SQL is that when a SQL statement is dynamic, a program must build a valid SQL statement by performing a series of steps to define the statement; execute it; and in the case of queries, define columns and process the rows in the return set. The exact type and number of steps to perform for a dynamic SQL statement varies, depending on the type of SQL statement as well as on how the program needs to define and execute the statement. The different possibilities outline four formal methods of dynamic SQL. Table 1 briefly introduces some of the properties of each method. The last column in Table 1 is a quick reference list of the progression of DBMS_SQL calls that a PL/SQL program must make to implement each of these types of dynamic SQL.
If you're not familiar with dynamic SQL and would like more information about it, take a quick look at the chapter on dynamic SQL in the Programmer's Guide to the Oracle Precompilers. Rather than duplicate the documentation you'll find there, this article focuses on using the DBMS_SQL database package to perform dynamic SQL from within PL/SQL programs.

THE ROLE OF DBMS_SQL

The DBMS_SQL package contains many procedures and functions that provide PL/SQL with a procedural interface to perform the various steps of defining and executing dynamic SQL and PL/SQL statements in stored procedures, functions, and packages. The guide"A Quick Reference to the DBMS_SQL API" is a concise, incomplete quick-reference to the most commonly used procedures and functions of the DBMS_SQL application programming interface (API).
For a more thorough description of the constructs listed in the "Quick Reference to the DBMS_SQL API," see your Oracle7 Server Documentation Addendum or the script that Oracle7 executes to create the DBMS_SQL package (for example, $ORACLE_HOME/rdbms/admin/dbmssql.sql, on all UNIX systems).

EXAMPLES OF SIMPLE AND COMPLEX PROCEDURES

Now let's study some examples that illustrate how to use the DBMS_SQL package to establish dynamic SQL in PL/SQL programs.
Listing 1 shows two simple procedures that an application can use to create and clean up temporary tables on behalf of individual application user sessions. Take note of several interesting points in this listing:

The procedures show how to establish dynamic SQL Method 1 in a stored procedure, using DBMS_SQL.
The procedures show how DBMS_SQL allows a PL/SQL program to execute data-definition-language (DDL) SQL statements such as CREATE TABLE and DROP TABLE (see the sidebar "A Word About PL/SQL and DDL SQL" at the end of this article).
The procedures use the UNIQUE_SESSION_ID function of another utility package, DBMS_SESSION, to identify and use unique session IDs for every user that calls them.

LISTING 1: Using the DBMS_SQL package to establish dynamic SQL Method 1 inside stored procedures for DDL SQL statements

CREATE PROCEDURE create_temp_dept

   (tname IN OUT VARCHAR2)

AS

   cur INTEGER;    -- holds cursor ID

   ret INTEGER;    -- holds call return value

   str VARCHAR2(250);    -- holds command

BEGIN

-- generate temporary DEPT table name using hard-coded name

-- and return value from DBMS_SESSION.UNIQUE_SESSION_ID

tname := 'dept_t'||dbms_session.unique_session_id;

-- generate CREATE TABLE command from

-- hard-coded text and tname variable

str := 'CREATE TABLE '||tname

   ||' (deptno INTEGER,'

   ||' dname VARCHAR2(14),'

   ||' loc VARCHAR2(13)) '

   ||'TABLESPACE temp '

   ||'STORAGE ('

   ||'INITIAL 10K NEXT 10K MAXEXTENTS 2)';

--

-- Dynamic SQL Method 1 for DDL statement

--

   cur := dbms_sql.open_cursor;

   dbms_sql.parse(cur,str,dbms_sql.v7);

   ret := dbms_sql.execute(cur);

   dbms_sql.close_cursor(cur);

END;

CREATE PROCEDURE drop_temp_dept

   (tname IN OUT VARCHAR2)

AS

   cur INTEGER;   -- holds cursor ID

   ret INTEGER;   -- holds call return value

   str VARCHAR2(250);   -- holds command

BEGIN

-- generate temporary DEPT table name using hard-coded name

-- and return value from DBMS_SESSION.UNIQUE_SESSION_ID

--

   tname := 'dept_t'||dbms_session.unique_session_id;

--

-- generate DROP TABLE command from

-- hard-coded text and tname variable

--

   str := 'DROP TABLE '||tname;

--

-- Dynamic SQL Method 1 for DDL statement

--

   cur := dbms_sql.open_cursor;

   dbms_sql.parse(cur,str,dbms_sql.v7);

   ret := dbms_sql.execute(cur);

   dbms_sql.close_cursor(cur);

END;

As you can see from the examples in Listing 1, dynamic SQL Method 1 with DBMS_SQL requires only a few steps and is very simple to perform. The following anonymous PL/SQL block is a simple demonstration of how SQL*Plus or SQL*DBA can call the CREATE_TEMP_DEPT and DROP_TEMP_DEPT stored procedures to create and drop a temporary DEPT table on behalf of a database session. Before executing this block, remember to set your serveroutput to On.

DECLARE

x VARCHAR2(50);

BEGIN

create_temp_dept(x);

dbms_output.put_line(x||' table created');

drop_temp_dept(x);

dbms_output.put_line(x||' table dropped');

END;

Now let's take a look at a slightly more complicated example that shows how to perform dynamic SQL for a query in a stored function. The DEPT_LIST_BUILDER function in Listing 2 shows how to create a function that builds an output buffer containing a delimited list of department numbers and names for a client application.

LISTING 2: The DEPT_LIST_BUILDER function shows how to use the DBMS_SQL package to establish dynamic SQL Method 3 inside a stored function for a query.

CREATE FUNCTION dept_list_builder

   (loc_col IN CHAR DEFAULT 'Y')

   RETURN LONG

AS

   stmt VARCHAR2(250);   -- holds command

   select_list VARCHAR2(50);    -- holds select list

   deptid INTEGER;   -- holds deptno list data

   deptnm VARCHAR2(14);   -- holds dname list data

   deptlc VARCHAR2(13);   -- holds loc list data

   cur INTEGER;   -- holds cursor ID

   ret INTEGER;   -- holds call return value

   output LONG(32760);   -- holds delimited list data

BEGIN

   -- Build SELECT list based on loc_col flag.

   -- The query's SELECT list always contains

   -- the columns DEPTNO and DNAME.

   select_list := 'deptno,dname';

   IF UPPER(loc_col) = 'Y' THEN

      select_list := select_list||',loc';

   END IF;

   -- Build SELECT command.  Procedure owner

   -- must have SELECT privilege for SCOTT.DEPT.

   stmt := 'SELECT '||select_list

           ||' FROM scott.dept ORDER BY deptno';

--

   -- Dynamic SQL Method 3

--

   -- Open and parse query

   cur := dbms_sql.open_cursor;

   dbms_sql.parse(cur,stmt,dbms_sql.v7);

   -- Define colums in query

   dbms_sql.define_column(cur,1,deptid);

   dbms_sql.define_column(cur,2,deptnm,14);

   IF UPPER(loc_col) = 'Y' THEN

      dbms_sql.define_column(cur,3,deptlc,13);

   END IF;

   -- Execute query

   ret := dbms_sql.execute(cur);

   -- Fetch rows, delimit, and place

   -- into output buffer for list box.

   -- ',' delimiter indicates end of field.

   -- ';' delimiter indicates end of record.

   LOOP

      IF dbms_sql.fetch_rows(cur) > 0 THEN

 dbms_sql.column_value(cur,1,deptid);                                          dbms_sql.column_value(cur,2,deptnm);

       output := output||deptid||','||deptnm;

       IF UPPER(loc_col) = 'Y' THEN

        dbms_sql.column_value(cur,3,deptlc);

        output := output||','||deptlc;

       END IF;

       output := output||';';

      ELSE

       EXIT;

      END IF;

   END LOOP;

   dbms_sql.close_cursor(cur);

   RETURN output;

  END dept_list_builder;

Notice that the DEPT_LIST_BUILDER function illustrates the additional steps necessary to perform dynamic SQL for a query: defining a query's SELECT list, fetching individual rows after executing the query, and then accessing individual column values in the query's return set. The following anonymous PL/SQL block demonstrates how to call and view the return value of the DEPT_LIST_BUILDER function from SQL*Plus or SQL*DBA. Before executing this block, remember to set your
serveroutput to On.

DECLARE

   outputbuffer VARCHAR2(2000);

BEGIN

   outputbuffer := dept_list_builder('Y');

   -- Try the above call with 'N' also to

   -- see the dynamic SQL in action.

   dbms_output.put_line(outputbuffer);

END;

CONCLUSIONS

Implementing dynamic SQL in PL/SQL programs is fairly straightforward once you understand how to define and execute various types of dynamic SQL statements using the procedures and functions in the DBMS_SQL package. Although this article is not a complete reference to dynamic SQL or to the DBMS_SQL package, the information here should increase your awareness of both topics so that you can design better Oracle7 database applications.
Steve Bobrowski is the president of Animated Learning, a software company that develops multimedia courseware to teach people about client/server technology, and is the author of Mastering Oracle7 & Client/Server Computing (Sybex, 1994). You can reach him via phone at 408.688.7735 or via the Internet at stevebob@netcom.com. Thanks to Oracle consultant Gail Turk, who provided ideas for this article.

FOOTNOTE:

1. When designing an efficient client/server application, you typically code many of an application's SQL statements into stored database procedures and then have the application call the procedures to perform database work. However, since a compiled application has static procedure calls and the SQL statements in the database stored procedures are also compiled, the overall assertion that things are static remains true.

A WORD ABOUT PL/SQL AND DDL SQL

The examples in Listing 1 show you how to sidestep a limitation of Oracle7 Release 7.1's PL/SQL--its lack of support for data-definition language (DDL) SQL statements. Have you ever wondered why PL/SQL does not directly support DDL SQL statements? To answer this question, think about what Oracle7 does when you create a PL/SQL program.
When compiling a PL/SQL program, Oracle7 does more than just check statement syntax--it also checks database object dependencies and security authorizations to make sure that the PL/SQL program is valid. Furthermore, for database-stored PL/SQL programs such as procedures and triggers, Oracle7 automatically keeps track of object-dependency chains so that the server can automatically invalidate and revalidate objects that depend on one another when necessary. This built-in server feature eliminates the inconvenience of having to manually keep track of object dependencies and manually check or recompile objects when something as simple as a table modification occurs. When complex application schemes have many interdependent objects, manually managing object dependencies can be quite a significant task.
With Oracle7's automatic object-dependency mechanism in mind, consider what would happen if PL/SQL directly supported DDL SQL. With such functionality, a PL/SQL program could, among other things, create database-objectlike tables. But this scenario derives a paradox--it's not possible for Oracle7 to validate a PL/SQL program that depends on a database object that does not exist. This example demonstrates that to gain something, you often you have to give something up. In the case of PL/SQL, Oracle7's programmers specifically chose to prohibit DDL SQL functionality and instead support automatic object-dependency checking and validation.
Oracle7 Release 7.1's DBMS_SQL package provides a convenient way to work around PL/SQL's DDL limitation without undermining Oracle7's object-dependency mechanism. Because DDL SQL statements inside a PL/SQL program must be dynamic and built at runtime, Oracle7 can validate the program successfully. Understand, however, that when a PL/SQL program uses the DBMS_SQL package to include DDL SQL, the program must take the responsibility for errors that might result from object dependencies and security authorizations that Oracle7 cannot check at compile time.

fonte: sistemi.labinf.it

Fine articolo Corso SQL

Corso SQL

Database SQL
che cos'è una transazione? una sequenza di operazioni sql da trattare come atomica
considerando che il campo "indirizzoemail" è un campo testuale, quale affermazione è corretta in merito alla seguente query sql? select * from clienti where indirizzoemail > 'm_rossi@hotmail.com' la query restituisce i record con indirizzi alfabeticamente maggiori di quello specificato
il comando di sql "delete" viene utilizzato per: eliminare righe esistenti da una tabella
il comando di sql "grant" è utilizzato per: accordare agli utenti autorizzazioni su varie porzioni del database
il comando di sql "update" viene utilizzato per: cambiare dei valori esistenti in una tabella
il comando sql "insert into (nome_tabella) values (lista_valori)" pe rmette di inserire nella tabella: righe (i cui valori devono essere specificati come parametro)
il comando sql "revoke" è utilizzato per: annullare autorizzazioni accordate in precedenza
il comando sql "select": e' il comando di proiezione dell'sql
il seguente comando sql: delete from impiegati where idimpiegato ='m1010' attua la cancellazione di un sottoinsieme di righe della tabella
il terzo campo del recordset restituito dalla seguente query sql: select nomearticolo, prezzo * 0.9, ' = scontato' from articoli order by 2 e' una colonna di valori tutti uguali a, apici esclusi, ' = scontato'
la funzione sql di aggregazione "sum" implementa: la sommatoria di tutti i dati relativi al campo oggetto della funzione
la query "select nome_clienti , indirizzo_clienti from clienti where cod_cli = 10" restituisce: il nome e l’indirizzo dei clienti del nostro db che hanno un codice cliente pari a 10.
la query sql seguente: select * from impiegati estrae tutte le righe della tabella impiegati
la seguente query sql select count(distinct stipendio) from impiegato restituisce: il numero dei diversi valori dell'attributo stipendio fra tutte le righe della tabella impiegato
le funzioni "max" e "min" di sql: restituiscono il massimo ed il minimo valore posseduti dall'attributo specificato
nel linguaggio sql, di seguito al comando "select" è obbligatorio inserire: attributi
nel linguaggio sql, esiste una differenza tra la clausola "where" e la clausola "having"? si usa having per esprimere condizioni su valori aggregati, where per condizioni su semplici valori
nel linguaggio sql, gli alias sono: nomi che possono essere usati in alternativa ai nomi di campo
nel linguaggio sql, il carattere jolly "%": è utilizzato con la causola like per ricercare un insieme di caratteri qualunque
nel linguaggio sql, il comando "not select": e' invalido
nel linguaggio sql, il comando "rollback": è utilizzato per annullare tutti gli aggiornamenti di una transazione
nel linguaggio sql, il nome di una tabella in una causola "from" può essere seguito da un alias? sì
nel linguaggio sql, la causola "distinct" è utilizzata per: evitare duplicati sia nella visualizzazione sia nel conteggio dei valori
nel linguaggio sql, la causola "from" rappresenta: l'insieme delle tabelle a cui si vuole accedere
nel linguaggio sql, la causola "group by" è di solito utilizzata: per raggruppare dati all'interno di un campo
nel linguaggio sql, la causola "group by": occorre nelle interrogazioni
nel linguaggio sql, la causola "having" è utilizzata: per limitare l'output a determinati gruppi
nel linguaggio sql, la causola "set" del comando "update": indica la colonna da modificare, insieme con un'espressione che fornisce il nuovo valore
nel linguaggio sql, la clausola "distinct" nel comando "select" ha la funzione di: eliminare i record duplicati nel risultato del comando
nel linguaggio sql, la condizione "inner join" permette: di estrarre solo dati che soddisfino la condizione di join da due o più tabelle
nel linguaggio sql, la funzione "avg(attributo)" restituisce: il valore medio tra quelli dell'attributo
nel linguaggio sql, la funzione "count(distinct)" restituisce: il numero dei diversi valori degli attributi
nel linguaggio sql, la funzione "sum(attributo)" restituisce: la somma dei valori posseduti dall'attributo
nel linguaggio sql, le tre parole chiave "update", " set" e "where": permettono di aggiornare uno o più attributi delle righe di una tabella che soddisfano la condizione
nel linguaggio sql, l'operatore "between" è utilizzato: per ricercare valori all'interno di un intervallo
nel linguaggio sql, l'operatore "intersect": produce tutte le righe comuni ai risultati di due query
nel linguaggio sql, l'operatore "minus" posto tra due query: restituisce le righe che si trovano nei risultati della prima query ma non in quelli della seconda
nel linguaggio sql, per aggiungere una colonna in una tabella si utilizza: il comando alter table con a causola add
nel linguaggio sql, per congiungere delle tabelle in una interrogazione è necessario: elencare le tabelle nel blocco from e specificare eventuali condizioni nel blocco where
nel linguaggio sql, per filtrare dati in una tabella è necessario utilizzare: la condizione di ricerca where
nel linguaggio sql, per implementare un ordinamento decrescente è necessario utilizzare la sintassi: order by con la causola desc
nel linguaggio sql, per produrre delle aggregazioni a dei sottoinsiemi di righe occorre utilizzare: la causola "group by"
nel linguaggio sql, quando è obbligatorio utilizzare la clausola "group by"? quando devo applicare funzioni di aggregazione a sottoinsiemi di righe
nel linguaggio sql, quando utilizziamo la causola "is null" all'interno di una "where" ricerchiamo: tutti i campi con valore null
nel linguaggio sql, tramite la seguente sintassi: .... attributo is not null si permette l'estrazione di tutti i valori che non abbiano un valore null
nel linguaggio sql, utilizzando come opzione del costrutto "foreign key" la voce "cascade on update": il nuovo valore viene riportato su tutte le relative righe della tabella master legata
nel linguaggio sql, utilizzando come opzione del costrutto "foreign key" la voce "no action on update": non viene propagata alcuna operazione di aggiornamento
per creare una nuova tabella in un database è necessario utilizzare il comando sql: create table
per eliminare una tabella con tutti i suoi dati da un database è disponibile il comando sql: drop table
per inserire dati in una tabella si utilizza il comando sql: insert into e la clausola values specificando i valori per ciascuna delle colonne
per modificare le caratteristiche di una colonna è necessario utilizzare il comando sql: alter table con a causola modify
quale affermazione in merito alla query sql seguente è da considerarsi corretta? select i.cognome, s.importost from impiegati as i join stipendi as s on i.idimpiegati=s.idimpiegati estrae gli stipendi e i cognomi di ogni impiegato
quale delle affermazioni è corretta circa la sintassi sql seguente? comando insert into (nome_tabella) <selectsql> permette di inserire nella tabella le righe risultanti dalla selezione
quale delle affermazioni è corretta circa la sintassi sql seguente? delete from impiegati elimina tutte le righe dalla tabella impiegati
quale risultato si ottiene eseguendo la seguente query sql? select count(ordini), nome_clienti from clienti group by nome_clienti il numero totale dei record nel campo ordini per ogni cliente
quale risultato si ottiene eseguendo la seguente query sql? select nome_impiegati from impiegati order by cognome_impiegati il nome di tutti gli impiegati in ordine alfabetico per cognome
quale tra le tre proposte è una sintassi sql alternativa alla seguente clausola" where"? where s_cid between 7000 and 8000 where s_cid >= 7000 and s_cid <= 8000
quando una query è inserita all'interno di un'altra è detta: query annidata
una query di selezione scritta in sql può includere una clausola "where" che metta in relazione il valore di un campo con il risultato di un’altra query? si, solo se i campi confrontati contengono lo stesso tipo di dati
una volta eseguito un raggruppamento con la clausola sql "group by" è possibile selezionare solo alcuni gruppi usando la clausola: having

fonte: www.mininterno.net

Corso SQL

Collegamenti utili gratuiti

Disclaimer : gli obiettivi di questo sito sono il progresso delle scienze e delle arti utili in quanto pensiamo che siano molto importanti per il nostro paese i benefici sociali e culturali della libera diffusione di informazioni utili. Tutte le informazioni e le immagini contenute in questo sito vengono qui utilizzate esclusivamente a scopi didattici, conoscitivi e divulgativi. Le informazioni di medicina e salute contenute nel sito sono di natura generale ed a scopo puramente divulgativo e per questo motivo non possono sostituire in alcun caso il consiglio di un medico (ovvero un soggetto abilitato legalmente alla professione). In questo sito abbiamo fatto ogni sforzo per garantire l'accuratezza dei tools, calcolatori e delle informazioni, non possiamo dare una garanzia o essere ritenuti responsabili per eventuali errori che sono stati fatti, i testi contenuti nel sito sono di proprietà dei rispettivi autori. Se trovate un errore su questo sito o se trovate un testo o tool che possa violare le leggi vigenti in materia di diritti di autore, comunicatecelo via e-mail e noi provvederemo tempestivamente a rimuoverlo.

Corso SQL

Corso SQL

Corso e guida SQL

WHERE TABA.COGNOME>TABB.COGNOME

Corso SQL

Corso SQL

Istruzioni goto

EXCEPTION

DESCRIZIONE

Il cursore è già stato aperto

Compilazione di procedure, funzioni e package

EXPLAIN PLAN

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

STATIC SQL AND DYNAMIC SQL

INTRODUCTION TO DYNAMIC SQL

THE ROLE OF DBMS_SQL

EXAMPLES OF SIMPLE AND COMPLEX PROCEDURES

CONCLUSIONS

FOOTNOTE:

A WORD ABOUT PL/SQL AND DDL SQL

Corso SQL

Corso SQL

Corso SQL