Introduzione ad SQL*Plus di Oracle8i

Indice

1. Introduzione
   • Accesso al DBMS via SQL*Plus
   • Uscire da SQL*Plus
2. Editing di comandi
   • Buffer di editing
   • Eseguire comandi memorizzati su file
   • Registrare una sessione di lavoro
   • Caratteri speciali
3. Gestione utenti
   • Creare ed eliminare utenti
   • Privilegi e ruoli di un utente
4. Gestione tavole
   • Creare ed eliminare tavole
   • Inserire e cancellare tuple
   • Commit e rollback
   • Interrogare tavole
   • Utilizzare il catalogo

1. Introduzione

Per la parte implementativa del corso si fa uso di un DBMS commerciale, in particolare si fa uso di Oracle8i Personal, una versione ridotta di Oracle8i che gira sotto Windows (95, 98, NT, 2000) e Unix (Linux). Per maggiori informazioni su questo sistema si può fare riferimento alla documentazione in linea diponibile sul sito web di Oracle http://www.oracle.com/.

Oracle8i Personal, come tutti i DBMS commerciali, implementa un sottoinsieme dello standard SQL-92. Lo strumento base per interagire con il DBMS di Oracle8i è SQL*Plus, un interprete di comandi SQL con un'interfaccia a linea di comando.

Accesso al DBMS via SQL*Plus

Sotto Windows, ci sono due modi per attivare SQL*Plus.

• Dal menù avvio. In tale caso viene aperta una finestra specifica SQL*Plus.
• Dal prompt di comandi MS-DOS attraverso il comando sqlplus. Questo comando esegue SQL*Plus direttamente su una finestra MS-DOS. Ciò è particolarmente utile se, ad esempio utilizzando Java, si mandano stringhe sulle stream di uscita o di errore, le quali possono essere lette su una finestra MS-DOS, mentre non vengono visualizzate su finestre grafiche quali quella di SQL*Plus eseguito da menù avvio.

Per connettersi alla base di dati si devono fornire a SQL*Plus i seguenti parametri:

• <nome-utente>: il nome dell'utente che accede alla base di dati. Ad esempio system, un utente predefinito con tutti i privilegi sulla base di dati.
• <password>: la password dell'utente. Ad esempio manager nel caso dell'utente system.
• <descrittore-di-connessione>: un descrittore della destinazione per una connessione via rete ad una base di dati remota. Questo è composto da una coppia <indirizzo>.<servizio>, dove <indirizzo> indica il server al quale connettersi, e <servizio> indica l'istanza sul server della base di dati identificata da un SID (ID di sistema Oracle). Questo parametro può essere omesso per connettersi alla base di dati di default installata sulla macchina locale, mentre deve necessariamente essere specificato per connettersi ad una base di dati su una macchina remota. Ad esempio, per connettersi dai client del laboratorio di via Tiburtina alla base di dati tiburdb sul server NT tibntnew identificato dall'indirizzo oratib, deve essere specificato oratib.tiburdb.

    sqlplus <nome-utente>/<password>@<descrittore-di-connessione>

    sqlplus stud00/parola00@oratib.tiburdb

Una volta connessi alla base di dati, dopo una serie di massaggi, appare il prompt di SQL*Plus:

    SQL>

Uscire da SQL*Plus

Per uscire da SQL*Plus, si deve digitare:

    quit;

    exit;

2. Editing di comandi

In SQL*Plus i comandi possono estendersi su più linee e devono terminare con il punto e virgola ";". Se il comando occupa più linee, ad ogni return viene dato un prompt speciale con il numero di linea corrente fino a che non si digita il punto e virgola o un return lasciando una riga vuota. Nel primo caso viene eseguito il comando; nel secondo caso il comando viene memorizzato su un buffer (si veda Buffer di editing).

Si tenga presente che SQL è case-insensitive. Quindi in SQL*Plus, si possono usare indifferentemente maiuscole o minuscole (questo vale anche per la password). Si noti però che i dati di tipo stringa fanno distinzione tra maiuscole e minuscole.

Buffer di editing

Se si termina un comando con una riga vuota, invece del punto e virgola, il comando viene memorizzato in un buffer. Questo comando può essere eseguito dal buffer digitando:

    run

Il comando nel buffer può essere editato prima di eseguirlo. Per fare ciò si pu&ogreve; utilizzare un editor di linea interno a SQL*Plus, oppure un editor esterno. I comandi principali dell'editor di linea sono i seguenti:

In alternativa, si può chiamare da SQL*Plus un editor esterno attraverso il comando edit. Se digitiamo:

    edit <nome-file.sql>

    define_editor = "emacs"

    save <nome-file.sql>

Eseguire comandi memorizzati su file

Anziché eseguire comandi SQL digitandoli su terminale, è spesso più conveniente scriverli in un file di testo e poi richiamarli da SQL*Plus.

Supponiamo di aver scritto alcuni comandi SQL in un file foo.sql nella directory corrente. Possiamo eseguire il file da SQL*Plus con il comando:

     @foo.sql

     start foo.sql

Se non viene specificata nessuna estensione per il file, cioè se scriviamo @foo, allora SQL*Plus assume che l'estensione sia .sql. È ovviamente possibile anche specificare il path completo del file, mettendolo tra singoli apici. Ad esempio, possiamo scrivere @'e:\esercitazioni\foo.sql'.

Nota: per realizzare una base di dati è utile preparare i seguenti file:

• Un file per creare tavole e viste.
• Un file per rimuovere tutte le tavole e le viste.
• Un file per popolare le tavole, cioè per inserire tuple nelle tavole.

Registrare una sessione di lavoro

Per avere una copia su file di una sessione di lavoro, si può usare il comando spool di SQL*Plus. In particolare, digitando:

    spool <nome-file>

    spool off

Caratteri speciali

I caratteri speciali non possono essere utilizzati se non in stringhe. In queste, per ottenere l'apostrofo (poiché una stringa é delimitata da singoli apici) si usano due apostrofi. Ad esempio:

    'citta'' del sole'

    set escape <carattere-di-escape>

    set escape ^

    show escape

    set escape off

3. Gestione utenti

Creare ed eliminare utenti

    create user <nome-utente> identified by <password>;

dove <nome-utente> è il nome del nuovo utente e <password> è la password assegnatagli.

L'utente, una volta connessosi alla base di dati, potrà cambiare la password attraverso il comando:

    alter user <nome-utente> identified by <nuova-password>;

    drop user <nome-utente> cascade;

Privilegi e ruoli di un utente

Una volta creato l'utente si devono associare ad esso gli opportuni permessi attraverso il comando SQL grant. Si ricorda che il comando grant permette di dare diritti di inserimento, cancellazione, aggiornamento, e interrogazione di una tavola a determinati utenti. Inoltre grant può essere usato per associare ruoli prestabiliti. In particolare, in Oracle8i sono predefiniti una serie di ruoli, tra cui:

• CONNECT, con i privilegi per la connessione ad una base di dati e per effettuare interrogazioni sulle tavole di sistema e sulle tavole visibili all'utente;
• RESOURCE, con, i privilegi per la creazione e cancellazione di nuove tavole.
• DBA, con tutti i privilegi per la gestione di utenti e tavole (anche altrui);

    grant CONNECT to <nome-utente>;
    grant RESOURCE to <nome-utente>;

Per revocare privilegi ad un utente si usa il comando SQL revoke. Questo si utilizza pure per revocare ruoli. Ad esempio:

    revoke RESOURCE from <nome-utente>;

4. Gestione tavole

Creare ed eliminare tavole

Per creare una tavola si usa il comando SQL standard create table, avente la forma seguente:

    create table <nome-tavola> (
        <lista di attributi e tipi ad essi associati>
    );

Un esempio di creazione è il seguente:

    create table Test (
        i int,
        s char(10)
    );

Questo comando crea una tavola chiamata TEST con due attributi: il primo, chiamato i, è un intero; il secondo, chiamato s, è una stringa lunga al più 10 caratteri.

Per eliminare una tavola dalla base di dati, si usa il comando SQL standard drop table, avente la forma seguente:

    drop table <nome-tavola>;

In generale, dopo aver creato tavole di prova si suggerisce di eliminarle con drop table per mantenere pulita la base di dati. Ad esempio, eseguire:

    drop table Test;

Inserire e cancellare tuple

Dopo aver creato una tavola possiamo inserirvi tuple utilizzando il comando insert. La maniera più semplice è inserire direttamente i valori:

    insert into <nome-tavola>
        values (<lista ordinata dei valori da inserrire negli attributi>);

Per esempio, possiamo inserire la tupla (10, 'foobar') nella tavola TEST come segue:

    insert into Test values (10, 'foobar');

Commit e rollback

Le modifiche che si effettuano sulla base di dati non sono permanenti fino a che non si dà il comando:

    commit;

    rollback;

Si noti che i comandi di aggiornamento tra due commit formano una transazione, cioè un'azione sulla base di dati da considerarsi come una unità.

È possibile far generare i comandi di commit in modo automatico da SQL*Plus dopo ogni istruzione DDL di SQL, digitando:

    set autocommit on

    set autocommit off

    show autocommit

Interrogare tavole

Possiamo vedere quali sono i valori memorizzati in una tavola attraverso una semplice query:

    select * from <nome-tavola>;

Per esempio, dopo aver creato la tavola TEST ed aver inserito la tupla (10,'foobar'), il comando:

    select * from Test;

             I S
    ---------- ----------
            10 foobar

Utilizzare il catalogo

Il DBMS mantiene tutte le informazioni sulla base di dati (metadati) in delle tavole di sistema che formano il cosiddetto catalogo. Le tavole del catalogo sono interrogabili attraverso SQL esattamente come le tavole create dagli utenti. L'elenco completo delle tavole lo si trova nella documentazione online di Oracle8i. Qui ci occupiamo solo della tavola ALL_TABLES, che contiene le infomazioni su tutte le tavole della base di dati. In particolare, se non ci si ricorda quali sono le tavole dell'utente <nome-utente> si può utilizzare la seguente query:

    select TABLE_NAME
    from ALL_TABLES
    where OWNER = '<nome-utente>'

Dato il nome di una tavola è possibile ottenere i suoi attributi (nome e tipo) con il comando:

    describe <nome-tavola>

Per esempio, per sapere gli attributi memorizzati dal sistema in ALL_TABLES si può usare:

    describe ALL_TABLES