SQL*Plus 1

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

rem Nome: statopre.sqlrem Tipo: file di creazione di reportrem Descrizione: Report sullo stato dei prestiti per ciascun utentettitle 'Elenco dei prestiti per ciascun utente con segnalazione dei

prestiti scaduti'btitle 'Biblioteca di casa mia'column COGNOME heading 'UTENTE' format a10 word_wrappedcolumn NOME heading '' format a1 truncatedcolumn DATA_PRESTITO heading 'DATA'column AUTORE format a15 truncatedcolumn TITOLO format a20 word_wrappedcolumn GIORNI heading 'durata|del|prestito' format 999

break on COGNOME skip 2compute count of NOME on COGNOMEset linesize 80set 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 62 Marianna Ucria ********** - count 1 Rossi L 25-GEN-01 Vasco Pratolini Metello 46 ********** - count 1 Verdi G 12-OTT-00 Vasco Pratolini Le ragazze di 151 Sanfrediano G 12-OTT-00 Susanna Tamaro Tobia e l'angelo 151 M 12-NOV-00 Susanna Tamaro Per voce sola 120 ********** - count 3

Biblioteca di casa mia

Selezionate 6 righe.

SQL*Plus 4

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 5

Titoli

btitle <stringa> imposta il titolo inferiore per ogni pagina

del report

ttitle imposta il titolo superiore per ogni pagina

del report

SQL*Plus 6

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 7

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 8

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 9

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 10

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 11

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 12

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 13

Sistema operativo

host <comando>

$ <comando> trasferiscono un comando al sistema

operativo host.

SQL*Plus 14

Visualizzazione settaggi column [<colonna/e>] ttitle btitle compute show linesize show headsep show …

SQL*Plus 15

Disattivazione settaggi ttitle off btitle off clear column clear break clear computes

SQL*Plus 16

Formattazione di colonne a20 9999990 999,999.99 B999 9990 0999 $9999

9999MI 9999PR 9.999EEEE S9999 L9999 RM 999V99

column MATRICOLA heading 'MATR.'column COGNOME format a10column NOME format a10column TITOLO format a35 word_wrapped heading

'ESAME'column VOTO format 99.99 clear breaksclear computes break on MATRICOLA skip 1compute avg of VOTO on MATRICOLA  select MATRICOLA, COGNOME, NOME, TITOLO, VOTO, LODEfrom STUDENTE, CORSO, ESAME where MATR_STUD=MATRICOLA and COD_MATERIA=CODorder by MATRICOLA, COGNOME, NOME;

MATR. COGNOME NOME ESAME VOTO L

--------- ---------- ---------- ----------------------------------- ------ -

1000 ROSSI PAOLO Programmazione I 25.00

ROSSI PAOLO Architetture degli Elaboratori I 21.00

ROSSI PAOLO Matematica Discreta: Algebra 28.00

********* ------

avg 24.67

 

1001 BIANCHI STEFANO Analisi Matematica I: Calcolo 27.00

Differenziale

 

BIANCHI STEFANO Architetture degli Elaboratori I 30.00

BIANCHI STEFANO Matematica Discreta: Algebra 27.00

********* ------

avg 28.00

 

1022 BIANCHI LUIGI Matematica Discreta: Algebra 19.00

BIANCHI LUIGI Analisi Matematica I: Calcolo 18.00

Differenziale

 

********* ------

avg 18.50

 

 Selezionate 8 righe.

SQL*Plus 19

ttitle e btitle (2)left allinea a sinistraright allinea a destracenter centracolumn <n> salta alla posizione n a

partire dal margine sinistro

skip <n> stampa n linee vuotetab <n> salta avanti (indietro se

n<0) di n posizioni

SQL*Plus 20

Variabili di sistema

sql.lno numero di linea correntesql.pno numero di pagina

correntesql.release numero di versione di

Oraclesql.sqlcode codice di errore correntesql.user nome utente

column MATRICOLA heading 'MATR.' format 99999column COGNOME format a10column NOME format a10column TITOLO format a35 word_wrapped heading 'ESAME'column VOTO format 99.99clear breaksclear computes set linesize 80set pagesize 30set 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 1compute avg label MEDIA of VOTO on MATRICOLA report select MATRICOLA, COGNOME, NOME, TITOLO, VOTO, LODEfrom STUDENTE, CORSO, ESAME where MATR_STUD=MATRICOLA and COD_MATERIA=CODorder 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 27.00 Differenziale  Architetture degli Elaboratori I 30.00 Matematica Discreta: Algebra 27.00****** ------MEDIA 28.00  1022 BIANCHI LUIGI Matematica Discreta: Algebra 19.00 Analisi Matematica I: Calcolo 18.00 Differenziale ****** ------MEDIA 18.50  ********** ********** ------MEDIA 24.38 CECILIA Pag 1

SQL*Plus 23

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 24

Variabili definite dall’utente

accept <variabile>

[prompt <stringa>]

accept accetta l’input dalla tastiera e lo assegna alla variabile

prompt visualizza il messaggio

column MATRICOLA heading 'MATR.' format 99999column TITOLO format a35 word_wrapped heading 'ESAME'column VOTO format 99.99clear breaksclear computesset linesize 80set pagesize 15set sqlcase upper prompt Certificato esami sostenuti da uno studente accept xCOGNOME prompt ‘Inserire Cognome: ’ characcept xNOME prompt ‘Inserire Nome: ’ char ttitle left 'Esami sostenuti e media di ' xCOGNOME ‘ ‘ xNOME skip 2;btitle center 'Pag ' format 999 sql.pno ; break on NOME on COGNOME on MATRICOLA skip 1compute avg label MEDIA of VOTO on MATRICOLA  select MATRICOLA, TITOLO, VOTO, LODEfrom STUDENTE , CORSO, ESAME where NOME=’&xNOME’ and COGNOME=’&xCOGNOME’ and MATR_STUD=MATRICOLA and COD_MATERIA=CODorder by MATRICOLA, COGNOME, NOME;

SQL> @a:certificato.sql 'Certificato esami sostenuti da uno studente'Inserire Cognome: BIANCHIInserire Nome: STEFANOvecchio 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

SQL*Plus 27

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 28

new_value

column <colonna> new_value <variabile>

assegna il valore della colonna selezionato dalla query, alla variabile indicata

column NOME new_value xNOMEcolumn COGNOME new_value xCOGNOMEclear breaksclear computes set verify offset echo offset linesize 80set 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 skip 2;btitle center 'Pag ' format 999 sql.pno ; break on NOME on COGNOME on MATRICOLA skip 1compute avg label MEDIA of VOTO on MATRICOLA  select MATRICOLA, NOME, COGNOME, TITOLO, VOTO, LODEfrom STUDENTE, CORSO, ESAME where MATR_STUD='&xMATR' and MATRICOLA=MATR_STUD and COD_MATERIA=CODorder 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 30.00 Matematica Discreta: Algebra 27.00****** ------MEDIA 28.00   Pag 1

rem certificato.sql column MATRICOLA heading 'MATR.' format 99999column TITOLO format a35 word_wrapped heading 'ESAME'column VOTO format 99.99colum OGGI new_value xOGGI noprint format a1 trunccolumn COGNOME format a10column NOME format a10column NOME new_value xNOMEcolumn COGNOME new_value xCOGNOMEclear breaksclear computesset verify offset echo offset linesize 80set 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 skip 2;btitle center 'Pag. ' format 999 sql.pno -

right xOGGI ; break on NOME on COGNOME on MATRICOLA skip 1compute avg label MEDIA of VOTO on MATRICOLA  select MATRICOLA, NOME, COGNOME, TITOLO, VOTO, LODE, to_char(sysdate, 'dd fmMonth yyyy') OGGIfrom STUDENTE, CORSO, ESAME where MATR_STUD='&xMATR' and MATRICOLA=MATR_STUD and COD_MATERIA=CODorder 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 30.00 Matematica Discreta: Algebra 27.00****** ------MEDIA 28.00   Pag. 1 19 Marzo 2001

SQL*Plus 33

definizione e rimozione di variabili

undefine <variabile>

define <variabile> = <valore> definizione tramite assegnazione

SQL*Plus 34

decode

decode(<valore>, <if1>, <then1>, <if2>,

<then2>, <if3>, <then3>,

…,

<else>)

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.

SQL*Plus 36

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

DOCENTE_CORSO

ESAME

STUDENTE

 

SQL> select count (*) from CORSO;

una query per ogni tabella

SQL*Plus 37

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

Soluzione con file di avvio

SQL*Plus 38

select count(*) from CORSO;

select count(*) from DIPARTIMENTO;

select count(*) from DOCENTE;

select count(*) from DOCENTE_CORSO;

select count(*) from ESAME;

select count(*) from STUDENTE;

contenuto del file slave.sql

SQL*Plus 39

select ‘select ‘||’’’’||table_name||’’’’||’,count(*) from ‘||table_name||’;’ from user_tables;

Prima modifica

select 'CORSO',count(*) from CORSO; select 'DIPARTIMENTO',count(*) from DIPARTIMENTO; select 'DOCENTE',count(*) from DOCENTE; select 'DOCENTE_CORSO',count(*) from DOCENTE_CORSO; select 'ESAME',count(*) from ESAME; select 'STUDENTE',count(*) from STUDENTE;

CORSO 5

DIPARTIMENTO 3

DOCENTE 5

DOCENTE_CORSO 5

ESAME 6

STUDENTE 2

slave.sql

table.lst

rem master.sql - crea ed esegue il file slave.sql che crea una vistaset pagesize 30000set linesize 300set timing off set time offset feedback offset heading offset echo offttitle offbtitle offspool 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.sqlselect * from DIMENSIONE_TABELLE;

Seconda modifica: creazione di una vista

CORSO 5

DIPARTIMENTO 3

DOCENTE 5

DOCENTE_CORSO 5

ESAME 6

STUDENTE 2

slave.sqlcreate 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 /

Secondo esempiorem master2.sql - crea ed esegue slave.sqlset pagesize 300set linesize 200start c:\cecilia\basidi~1\offcolumn acapo newline

spool c:\cecilia\basidi~1\slave2.sql

prompt $clsprompt spool c:\cecilia\basidi~1\table2.lstprompt prompt Inizio report tabelleprompt 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 completatiprompt spool offprompt $print c:\cecilia\basidi~1\table2.lst

spool offstart c:\cecilia\basidi~1\slave2.sql

master2.sql

describe &Table;select 'Questa tabella contiene '||count(*)||' righe.' from &Table;prompt promptprompt

sizing.sql

$clsspool c:\cecilia\basidi~1\table2.lstprompt Inizio report tabelleprompt

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 completatispool off$print c:\cecilia\basidi~1\table2.lst

slave2.sql

Inizio report tabelle

Working on 'CORSO'

Nome Null? Tipo ------------------------------- -------- ---- COD NOT NULL NUMBER(4) SIGLA VARCHAR2(8) TITOLO VARCHAR2(50) TIPOL CHAR(2) TIPOD CHAR(2)

Questa tabella contiene 5 righe.

Working on 'DIPARTIMENTO'

Nome Null? Tipo ------------------------------- -------- ---- CODDIP NOT NULL NUMBER(2) DENOMINAZIONE VARCHAR2(40)

Questa tabella contiene 3 righe. ...Report tabelle completati

table2.sql