1 / 45

SQL*Plus

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.sql rem Tipo: file di creazione di report

mahina
Télécharger la présentation

SQL*Plus

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 SQL*Plus

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

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

  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 SQL*Plus

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

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

  19. ttitle e btitle (2) leftallinea a sinistra rightallinea a destra center centra column <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

  20. Variabili di sistema sql.lnonumero di linea corrente sql.pnonumero di pagina corrente sql.releasenumero di versione di Oracle sql.sqlcodecodice di errore corrente sql.usernome utente SQL*Plus

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

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

  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 SQL*Plus

  25. 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 skip 2; 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;

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

  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 SQL*Plus

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

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

  31. 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 skip 2; btitle center 'Pag. ' format 999 sql.pno - right xOGGI ; 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') OGGI from STUDENTE, CORSO, ESAME where MATR_STUD='&xMATR' and MATRICOLA=MATR_STUD and COD_MATERIA=COD order by MATRICOLA, COGNOME, NOME;

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

  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>) SQL*Plus

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

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

  38. contenuto del file slave.sql 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; SQL*Plus

  39. Prima modifica select ‘select ‘||’’’’||table_name||’’’’||’,count(*) from ‘||table_name||’;’ from user_tables; 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; slave.sql CORSO 5 DIPARTIMENTO 3 DOCENTE 5 DOCENTE_CORSO 5 ESAME 6 STUDENTE 2 table.lst SQL*Plus

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

  41. 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 5 DIPARTIMENTO 3 DOCENTE 5 DOCENTE_CORSO 5 ESAME 6 STUDENTE 2

  42. 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 spool c:\cecilia\basidi~1\slave2.sql prompt $cls prompt spool c:\cecilia\basidi~1\table2.lst prompt prompt Inizio report tabelle prompt 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 master2.sql

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

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

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

More Related