SQL*Plus
Interfaccia utente interattiva al DMS
Oracle
formattare l’output di una query (ovvero
creare dei report)
creare file contenenti query ed altre
istruzioni per la gestione della base di dati
SQL*Plus
1
rem Nome: statopre.sql
rem Tipo: file di creazione di report
rem Descrizione: Report sullo stato dei prestiti per ciascun utente
ttitle 'Elenco dei prestiti per ciascun utente con segnalazione dei
prestiti scaduti'
btitle 'Biblioteca di casa mia'
column COGNOME heading 'UTENTE' format a10 word_wrapped
column NOME heading '' format a1 truncated
column DATA_PRESTITO heading 'DATA'
column AUTORE format a15 truncated
column TITOLO format a20 word_wrapped
column GIORNI heading 'durata|del|prestito' format 999
break on COGNOME skip 2
compute count of NOME on COGNOME
set linesize 80
set pagesize 30
spool a:\statopre.txt
select COGNOME, NOME, DATA_PRESTITO, AUTORE, TITOLO, (round(sysdate)
-DATA_PRESTITO) as GIORNI from PRESTITI
order by COGNOME,NOME;
spool off
Lun Mar 12
pagina
1
Elenco dei prestiti per ciascun utente con segnalazione dei prestiti scaduti
durata
del
UTENTE
DATA
AUTORE
TITOLO
prestito
---------- - --------- --------------- -------------------- -------Brogi
L 10-GEN-01 Susanna Tamaro Va'dove ti porta il
61
cuore
********** count
1
Paci
G 09-GEN-01 Dacia Maraini
La lunga vita di
Marianna Ucria
62
********** count
1
Rossi
L 25-GEN-01 Vasco Pratolini Metello
********** count
1
Verdi
46
G 12-OTT-00 Vasco Pratolini Le ragazze di
Sanfrediano
151
G 12-OTT-00 Susanna Tamaro
M 12-NOV-00 Susanna Tamaro
151
120
Tobia e l'angelo
Per voce sola
Commenti
remark
-
contrassegnano una singola ed intera linea di commento
(non possono essere inseriti all’interno di un comando
SQL)
/* */
contrassegnano l’inizio e la fine di un commento che può
estendersi su più righe e può essere inserito all’interno di
un comando SQL
SQL*Plus
4
Titoli
btitle <stringa>
imposta il titolo inferiore per ogni pagina del
report
ttitle
imposta il titolo superiore per ogni pagina del
report
SQL*Plus
5
Colonne
column <colonna>
[heading <stringa>]
[format <formato>]
[truncated | word_wrapped]
fornisce istruzioni sulla formattazione di una colonna,
definendo un eventuale titolo da sostituire al nome della
colonna, il formato, e come operare se il valore è più
lungo dell’ampiezza della colonna specificato nel formato
o preso dalla definizione della tabella
SQL*Plus
6
break on
break on <colonna>[skip <n>]
indica la colonna secondo cui devono essere
raggruppate le varie righe ed il numero n di
spazi da inserire tra le varie sezioni, dove
interrompere per subtotali o altre funzioni di
gruppo. Per ogni comando break on deve
esistere un comando order by correlato
SQL*Plus
7
compute
compute [sum | max | min | avg
| count | number | std |
variance] of <colonna> on
<colonna_break_on>
applica la funzione di gruppo alla colonna
relativamente alle righe della sezione indicate
nella istruzione break on. Per ogni comando
compute deve esistere un comando break on
correlato
SQL*Plus
8
Formato pagina
set newpage <n>
imposta il numero di righe vuote tra le pagine. Con il
valore n=0 inserisce un carattere di inizio pagina
prima della data su ogni pagina
set pagesize <n>
imposta il numero totale delle righe per pagina,
compresi i titoli e le righe vuote
set pause [‘stringa’ | on | off]
interrompe la visualizzazione ad ogni schermata
SQL*Plus
9
Formato linee
set headsep <char>
indica quale carattere usare come separatore di
righe. Per default è |
set linesize <n>
imposta il numero massimo di caratteri per riga
SQL*Plus
10
Gestione file
spool [<nomefile> | off]
reindirizza su un file il risultato di una query
start <nomefile>
esegue le istruzioni salvate su un file
SQL*Plus
11
Editor
define _editor = <nome>
indica l’editor specificato dall’utente
edit
invoca l’editor specificato dall’utente
save <nomefile> [replace]
salva le istruzioni SQL (ma non SQL Plus) impostate
fino al quel momento nel file indicato. Se il file esiste
già, si deve indicare l’opzione replace
SQL*Plus
12
Sistema operativo
host <comando>
$ <comando>
trasferiscono un comando al sistema operativo
host.
SQL*Plus
13
Visualizzazione settaggi
column [<colonna/e>]
ttitle
btitle
compute
show linesize
show headsep
show …
SQL*Plus
14
Disattivazione settaggi
ttitle off
btitle off
clear column
clear break
clear computes
SQL*Plus
15
Formattazione di colonne
a20
9999990
999,999.99
B999
9990
0999
$9999
9999MI
9999PR
9.999EEEE
S9999
L9999
RM
999V99
SQL*Plus
16
column MATRICOLA heading 'MATR.'
column COGNOME format a10
column NOME format a10
column TITOLO format a35 word_wrapped heading
'ESAME'
column VOTO format 99.99
clear breaks
clear computes
break on MATRICOLA skip 1
compute avg of VOTO on MATRICOLA
select MATRICOLA, COGNOME, NOME, TITOLO, VOTO, LODE
from STUDENTE, CORSO, ESAME
where MATR_STUD=MATRICOLA and COD_MATERIA=COD
order by MATRICOLA, COGNOME, NOME;
MATR.
COGNOME
--------- ---------1000 ROSSI
ROSSI
ROSSI
*********
avg
NOME
---------PAOLO
PAOLO
PAOLO
ESAME
VOTO L
----------------------------------- ------ Programmazione I
25.00
Architetture degli Elaboratori I
21.00
Matematica Discreta: Algebra
28.00
-----24.67
1001 BIANCHI
STEFANO
Analisi Matematica I: Calcolo
Differenziale
BIANCHI
BIANCHI
STEFANO
STEFANO
Architetture degli Elaboratori I
Matematica Discreta: Algebra
LUIGI
LUIGI
Matematica Discreta: Algebra
Analisi Matematica I: Calcolo
Differenziale
*********
avg
1022 BIANCHI
BIANCHI
*********
avg
Selezionate 8 righe.
27.00
30.00
27.00
-----28.00
19.00
18.00
-----18.50
ttitle e btitle (2)
left
right
center
column
allinea a sinistra
allinea a destra
centra
<n> salta alla posizione n a
partire dal margine sinistro
skip <n>
stampa n linee vuote
tab <n>
salta avanti (indietro se
n<0) di n posizioni
SQL*Plus
19
Variabili di sistema
sql.lno
sql.pno
sql.release
sql.sqlcode
sql.user
numero di linea corrente
numero di pagina corrente
numero di versione di
Oracle
codice di errore corrente
nome utente
SQL*Plus
20
column MATRICOLA heading 'MATR.' format 99999
column COGNOME format a10
column NOME format a10
column TITOLO format a35 word_wrapped heading 'ESAME'
column VOTO format 99.99
clear breaks
clear computes
set linesize 80
set pagesize 30
set feedback off
ttitle left 'Esami e medie' right ‘UNIVERSITA’’ DI FIRENZE’ skip 2;
btitle center 'Pag ' format 999 sql.pno –
left sql.user;
break on report on NOME on COGNOME on MATRICOLA skip 1
compute avg label MEDIA of VOTO on MATRICOLA report
select MATRICOLA, COGNOME, NOME, TITOLO, VOTO, LODE
from STUDENTE, CORSO, ESAME
where MATR_STUD=MATRICOLA and COD_MATERIA=COD
order by MATRICOLA, COGNOME, NOME;
Esami e medie
UNIVERSITA' DI FIRENZE
MATR. COGNOME
NOME
ESAME
VOTO L
------ ---------- ---------- ----------------------------------- ------ 1000 ROSSI
PAOLO
Programmazione I
25.00
Architetture degli Elaboratori I
21.00
Matematica Discreta: Algebra
28.00
******
-----MEDIA
24.67
1001 BIANCHI
STEFANO
Analisi Matematica I: Calcolo
Differenziale
Architetture degli Elaboratori I
Matematica Discreta: Algebra
******
MEDIA
1022 BIANCHI
LUIGI
Matematica Discreta: Algebra
Analisi Matematica I: Calcolo
Differenziale
******
MEDIA
27.00
30.00
27.00
-----28.00
19.00
18.00
-----18.50
********** **********
-----24.38
MEDIA
CECILIA
Pag
1
Variabili di sistema (2)
SQL> define
DEFINE _SQLPLUS_RELEASE = "800060000" (CHAR)
DEFINE _EDITOR
= "Notepad" (CHAR)
DEFINE _O_VERSION
= "Oracle8 Personal
Edition Release 8.0.6.0.0 - Production
PL/SQL Release 8.0.6.0.0 - Production" (CHAR)
DEFINE _O_RELEASE
= "800060000" (CHAR)
SQL*Plus
23
Variabili definite dall’utente
accept <variabile>
[prompt <stringa>]
accept
prompt
accetta l’input dalla tastiera e lo
assegna alla variabile
visualizza il messaggio
SQL*Plus
24
column MATRICOLA heading 'MATR.' format 99999
column TITOLO format a35 word_wrapped heading 'ESAME'
column VOTO format 99.99
clear breaks
clear computes
set linesize 80
set pagesize 15
set sqlcase upper
prompt Certificato esami sostenuti da uno studente
accept xCOGNOME prompt ‘Inserire Cognome: ’ char
accept xNOME prompt ‘Inserire Nome: ’ char
ttitle left 'Esami sostenuti e media di ' xCOGNOME ‘ ‘ xNOME
btitle center 'Pag ' format 999 sql.pno ;
break on NOME on COGNOME on MATRICOLA skip 1
compute avg label MEDIA of VOTO on MATRICOLA
select MATRICOLA, TITOLO, VOTO, LODE
from STUDENTE , CORSO, ESAME
where NOME=’&xNOME’ and COGNOME=’&xCOGNOME’
and MATR_STUD=MATRICOLA and COD_MATERIA=COD
order by MATRICOLA, COGNOME, NOME;
skip 2;
SQL> @a:certificato.sql
'Certificato esami sostenuti da uno studente'
Inserire Cognome: BIANCHI
Inserire Nome: STEFANO
vecchio
3: where S.NOME='&xNOME' and S.COGNOME='&xCOGNOME'
nuovo
3: where S.NOME='STEFANO' and S.COGNOME='BIANCHI'
Esami sostenuti e media di BIANCHI STEFANO
MATR. ESAME
VOTO L
------ ----------------------------------- ------ 1001 Analisi Matematica I: Calcolo
27.00
Differenziale
Architetture degli Elaboratori I
30.00
Matematica Discreta: Algebra
27.00
******
-----MEDIA
28.00
Pag
1
set …
set sqlcase upper
indica a SQLPlus di convertire in maiuscolo
prima di eseguire la query, il contenuto della
variabile
set verify [on | off]
abilita/disabilita la visualizzazione dei valori delle
variabili
set echo [on | off]
abilita/disabilita la visualizzazione delle istruzioni
eseguite
SQL*Plus
27
new_value
column <colonna> new_value
<variabile>
assegna il valore della colonna selezionato dalla
query, alla variabile indicata
SQL*Plus
28
column NOME new_value xNOME
column COGNOME new_value xCOGNOME
clear breaks
clear computes
set
set
set
set
verify off
echo off
linesize 80
pagesize 15
prompt 'Certificato esami sostenuti da uno studente'
accept xMATR prompt 'Inserire il numero di matricola: '
ttitle left 'Esami sostenuti e media di ' xCOGNOME ' ' xNOME
btitle center 'Pag ' format 999 sql.pno ;
skip 2;
break on NOME on COGNOME on MATRICOLA skip 1
compute avg label MEDIA of VOTO on MATRICOLA
select MATRICOLA, NOME, COGNOME, TITOLO, VOTO, LODE
from STUDENTE, CORSO, ESAME
where MATR_STUD='&xMATR' and MATRICOLA=MATR_STUD and COD_MATERIA=COD
order by MATRICOLA, COGNOME, NOME;
SQL> @a:certificato.sql
'Certificato esami sostenuti da uno studente'
Inserire il numero di matricola: 1001
Esami sostenuti e media di BIANCHI STEFANO
MATR. NOME
COGNOME
ESAME
VOTO L
------ ---------- ---------- ----------------------------------- ------ 1001 STEFANO
BIANCHI
Analisi Matematica I: Calcolo
27.00
Differenziale
Architetture degli Elaboratori I
Matematica Discreta: Algebra
******
MEDIA
Pag
1
30.00
27.00
-----28.00
rem certificato.sql
column MATRICOLA heading 'MATR.' format 99999
column TITOLO format a35 word_wrapped heading 'ESAME'
column VOTO format 99.99
colum OGGI new_value xOGGI noprint format a1 trunc
column COGNOME format a10
column NOME format a10
column NOME new_value xNOME
column COGNOME new_value xCOGNOME
clear breaks
clear computes
set verify off
set echo off
set linesize 80
set pagesize 30
prompt 'Certificato esami sostenuti da uno studente'
accept xMATR prompt 'Inserire il numero di matricola: '
ttitle left 'Esami sostenuti e media di ' xCOGNOME ' ' xNOME
btitle center 'Pag. ' format 999 sql.pno right xOGGI ;
skip 2;
break on NOME on COGNOME on MATRICOLA skip 1
compute avg label MEDIA of VOTO on MATRICOLA
select MATRICOLA, NOME, COGNOME, TITOLO, VOTO, LODE, to_char(sysdate, 'dd fmMonth yyyy
from STUDENTE, CORSO, ESAME
where MATR_STUD='&xMATR' and MATRICOLA=MATR_STUD and COD_MATERIA=COD
order by MATRICOLA, COGNOME, NOME;
SQL> @c:\cecilia\basidi~1\certificato;
'Certificato esami sostenuti da uno studente'
Inserire il numero di matricola: 1001
Esami sostenuti e media di BIANCHI STEFANO
MATR. NOME
COGNOME
ESAME
VOTO L
------ ---------- ---------- ----------------------------------- ------ 1001 STEFANO
BIANCHI
Analisi Matematica I: Calcolo
27.00
Differenziale
Architetture degli Elaboratori I
Matematica Discreta: Algebra
******
MEDIA
Pag.
1
30.00
27.00
-----28.00
19 Marzo 2001
definizione e rimozione di
variabili
undefine <variabile>
define <variabile> = <valore>
definizione tramite assegnazione
SQL*Plus
33
decode
decode(<valore>, <if1>, <then1>,
<if2>, <then2>,
<if3>, <then3>,
…,
<else>)
SQL*Plus
34
select COGNOME, NOME, SIGLA,
decode(trunc((VOTO-18)/4),0,VOTO,null) BASSO,
decode(trunc((VOTO-18)/4),1,VOTO,null) MEDIO,
decode(trunc((VOTO-18)/4),2,VOTO,null) ALTO,
decode(trunc((VOTO-18)/4),3,VOTO,null) OTTIMO
from STUDENTE, ESAME, CORSO
where MATRICOLA=MATR_STUD and COD_MATERIA=COD;
COGNOME NOME
SIGLA
BASSO
MEDIO
ALTO
OTTIMO
-------- -------- -------- --------- --------- --------- --------ROSSI
PAOLO
ProgI
25
ROSSI
PAOLO
ArchI
21
BIANCHI STEFANO ArchI
30
ROSSI
PAOLO
MDA
28
BIANCHI STEFANO MDA
27
BIANCHI STEFANO AMCD
27
Selezionate 6 righe.
Generazione di codice per una
query
Problema: selezionare le tabelle dell’utente e la loro dimensione:
SQL> select table_name from user_tables;
TABLE_NAME
-----------------------------CORSO
DIPARTIMENTO
DOCENTE
una query per
DOCENTE_CORSO
ogni tabella
ESAME
STUDENTE
SQL> select count (*) from CORSO;
SQL*Plus
36
Soluzione con file di avvio
rem master.sql – crea ed esegue il file slave.sql
set feedback off
set heading off
spool a:slave.sql
select ‘select count(*) from ‘||table_name||’;’ from
user_tables;
spool a:table.lst
start a:slave.sql
spool off
set feedback on
set heading on
SQL*Plus
37
contenuto del file slave.sql
select
select
select
select
select
select
count(*)
count(*)
count(*)
count(*)
count(*)
count(*)
from
from
from
from
from
from
CORSO;
DIPARTIMENTO;
DOCENTE;
DOCENTE_CORSO;
ESAME;
STUDENTE;
SQL*Plus
38
Prima modifica
select ‘select ‘||’’’’||table_name||’’’’||’,count(*)
‘||table_name||’;’ from user_tables;
slave.sql
select
select
select
select
select
select
from
'CORSO',count(*) from CORSO;
'DIPARTIMENTO',count(*) from DIPARTIMENTO;
'DOCENTE',count(*) from DOCENTE;
'DOCENTE_CORSO',count(*) from DOCENTE_CORSO;
'ESAME',count(*) from ESAME;
'STUDENTE',count(*) from STUDENTE;
CORSO
5
DIPARTIMENTO
DOCENTE
5
DOCENTE_CORSO
ESAME
6
STUDENTE
2
3
5
table.lst
SQL*Plus
39
Seconda modifica: creazione di una vista
rem master.sql - crea ed esegue il file slave.sql che crea una vista
set pagesize 30000
set linesize 300
set timing off
set time off
set feedback off
set heading off
set echo off
ttitle off
btitle off
spool a:slave.sql
select decode(rownum,1,'create or replace view DIMENSIONE_TABELLE as
','union ')||
'select
'||''''||table_name||''''||'
TABELLA'||',
count(*)
NUMERO_RIGHE from '||table_name from user_tables;
prompt /
spool off
@a:slave.sql
select * from DIMENSIONE_TABELLE;
slave.sql
create or replace view DIMENSIONE_TABELLE as select 'CORSO' TABELLA,
count(*) NUMERO_RIGHE from CORSO
union select 'DIPARTIMENTO' TABELLA, count(*) NUMERO_RIGHE from
DIPARTIMENTO
union select 'DOCENTE' TABELLA, count(*) NUMERO_RIGHE from DOCENTE
union select 'DOCENTE_CORSO' TABELLA, count(*) NUMERO_RIGHE from
DOCENTE_CORSO
union select 'ESAME' TABELLA, count(*) NUMERO_RIGHE from ESAME
union select 'STUDENTE' TABELLA, count(*) NUMERO_RIGHE from STUDENTE
/
CORSO
DIPARTIMENTO
DOCENTE
DOCENTE_CORSO
ESAME
STUDENTE
5
3
5
5
6
2
Secondo esempio
rem master2.sql - crea ed esegue slave.sql
set pagesize 300
set linesize 200
start c:\cecilia\basidi~1\off
column acapo newline
master2.sql
spool c:\cecilia\basidi~1\slave2.sql
prompt
prompt
prompt
prompt
$cls
spool c:\cecilia\basidi~1\table2.lst
prompt Inizio report tabelle
prompt
select 'define Table = '||''''||Table_name||'''' acapo,
'prompt Working on '||''''||Table_name||'''' acapo,
'prompt
' acapo,
'start c:\cecilia\basidi~1\sizing.sql' acapo
from user_tables;
prompt prompt Report tabelle completati
prompt spool off
prompt $print c:\cecilia\basidi~1\table2.lst
spool off
start c:\cecilia\basidi~1\slave2.sql
describe &Table;
select 'Questa tabella contiene '||count(*)||' righe.' from &Table;
prompt
prompt
prompt
sizing.sql
$cls
spool c:\cecilia\basidi~1\table2.lst
prompt Inizio report tabelle
prompt
define Table = 'CORSO'
prompt Working on 'CORSO'
prompt
start c:\cecilia\basidi~1\sizing.sql
define Table = 'DIPARTIMENTO'
prompt Working on 'DIPARTIMENTO'
prompt
start c:\cecilia\basidi~1\sizing.sql
define Table = 'DOCENTE'
prompt Working on 'DOCENTE'
prompt
start c:\cecilia\basidi~1\sizing.sql
...
define Table = 'UTENTE'
prompt Working on 'UTENTE'
prompt
start c:\cecilia\basidi~1\sizing.sql
prompt Report tabelle completati
spool off
$print c:\cecilia\basidi~1\table2.lst
slave2.sql
Inizio report tabelle
Working on 'CORSO'
Nome
Null?
------------------------------- -------COD
NOT NULL
SIGLA
TITOLO
TIPOL
TIPOD
Tipo
---NUMBER(4)
VARCHAR2(8)
VARCHAR2(50)
CHAR(2)
CHAR(2)
table2.sql
Questa tabella contiene 5 righe.
Working on 'DIPARTIMENTO'
Nome
Null?
------------------------------- -------CODDIP
NOT NULL
DENOMINAZIONE
Questa tabella contiene 3 righe.
...
Report tabelle completati
Tipo
---NUMBER(2)
VARCHAR2(40)
