Slide Oracle Pl SQL

ORACLE

SQL e PLSQLElementi di Base

GLI ASPETTI FONDAMENTALI DEL DISCORSO SQL

Con SQL (Structured Query Language), si può conversare con ORACLE chiedendo le informazioni che si desidera selezionare (select), inserire (insert), aggiornare (update) o cancellare (delete). In effetti questi, sono i quattro verbi fondamentali che si utilizzano per fornire istruzioni a ORACLE.

Si tratta infatti di un prodotto molto potente di ORACLE che consente di raccogliere le istruzioni fornite dall’utente, controllarne la correttezza e quindi sottoporle a ORACLE

SQL*PLUS

Assicura Il colloquio tra l’utente e SQL

Successivamente, tali istruzioni si possono modificare o riformulare per cui si avrà una risposta elaborata da ORACLE sulla base degli ordini o delle direttive impartiti.

Si può pensare a SQL*PLUS come a un semplice collaboratore, un assistente che segue le istruzioni dell’utente e lo aiuta a eseguire il lavoro in maniera più veloce. Si interagisce con questo collaboratore semplicemente digitando con la tastiera.

LO SCOPO DI SQL * PLUS ?

Per SQL * PLUS non è importante se i comandi SQL vengono digitati in maiuscolo o in minuscolo

Alcune cose importanti sullo stile (1/5)

Select argoMENTO, sezione, PAGINA FROM gioRNAle;

produce lo stesso risultato se scritta :

select Argomento, Sezione, Pagina from Giorna1e;

L’utilizzo di lettere maiuscole o minuscole è importante soltanto quando in SQLPLUS o in ORACLE vengono verificati i termini di un’uguaglianza nel database.

Se si indica a ORACLE di trovare un riga in cui Sezione = ‘f’, e Sezione, è in realtà corrispondente a ‘F’, ORACLE non trova nulla (perché f e F non coincidono).


Pertanto, la lettera ‘F’, in questo uso, viene definita come elemento letterale, ovvero significa che nella Sezione deve essere verificata la presenza della lettera ‘F’ e non ricercata una colonna denominata F.

L’apice singolo su un lato della lettera indica che si tratta di una ricerca letterale e non di un nome di colonna.


I termini Select, from, Where, Order by, having e group by vengono sempre indicati in minuscolo e con un tipo di carattere diverso da quello del corpo del testo.

Anche i comandi SQLPLUS sono riportati nello stesso stile: column set save, tittle e così via


IN, BETWEEN, UPPER, SOUNDEX e altri operatori e funzioni di SQL vengono riportati in maiuscolo e con un tipo di carattere diverso.

I nomi di colonna sono riportati con l’iniziale maiuscola e il tipo di carattere normale: Argomento, EstOvest, Longitudine e così via.

Per i nomi di tabelle vengono utilizzati caratteri maiuscoli con il tipo di carattere normale:

Es. GIORNALE, CLIMA, LOCAZIONE e così via.


Selezionare dati da tabelle

Sia data la seguente tabella Questa istruzione consente di ottenere la tabella in questo modo:

select Argomento, Sezione, Pagina from GIORNALE

14 rows selected

Osserviamo da vicino. . .

• Cambia il formato delle intestazioni che risultano diverse da quelle richiamate con il comando SELECT.

• La colonna “Sezione” appare soltanto con la lettera “S” anche se si sono utilizzate lettere maiuscole o minuscole per digitare la riga di comando: SELECT Argomento, Sezione, Pagina from GIORNALE;

Tali cambiamenti sono il risultato delle elaborazioni svolte da SQLPLUS sulle modalità con cui le informazioni dovrebbero essere presentate.

(Selezionare dati da tabelle)

E’ possibile modificare tali parametri, ma finché non si assegnano modalità diverse, ecco come viene trasformato da SQLPLUS ciò che l’utente digita:

o tutte le intestazioni di colonna sono riportate in caratteri maiuscoli;

o l’ampiezza delle colonne è fissata dal valore definito in ORACLE;

o vengono eliminati gli spazi tra le parole, se l’intestazione di colonna rappresenta una funzione.


(Selezionare dati da tabelle)

Come vengono definiti i parametri per le colonne?

Proprietà di una tabella

Per scoprirlo è sufficiente chiederlo a ORACLE. Occorre chiedere a SQLPLUS di descrivere la tabella come illustrato qui di seguito

Si utilizza il comando DESCRIBE il quale funziona per qualsiasi tabella

• Nella prima colonna abbiamo i nomi delle colonne in tabella.

• Nella seconda colonna, Null?, viene indicata la regola seguita per la colonna citata a sinistra

• Nella terza colonna si fa riferimento alla tipologia del campo

Consente di sapere il numero delle righe di cui è costituita la tabella

La funzione FEEDBACK

Il comando per attivare tale funzione è:

set feedback 25

Per visualizzare una tabella che sia almeno di 25 righe

set feedback off

Per non visualizzare i messaggi di feedback

A meno che non si imposti un parametro diverso, la funzione di feedback è impostata a 6 righe.

Alcuni comandi

Set

Molte sono le modalità di esecuzione di un feedback

E’ un comando che indica a SQLPLUS le modalità di esecuzione di un’istruzione

Show Consente di visualizzare le istruzioni impostate. Infatti può essere utile verificare le impostazioni di feedback digitando:

show feedback

la risposta visualizzata da SQLPLUS è:

feedback ON for 25 or more rows

Select, From, Where e Order by

Sono le quattro parole chiave fondamentali di SQL che vengono utilizzate per selezionare le informazioni da una tabella di ORACLE

Select indica a ORACLE le colonne che si desidera visualizzare. From indica i nomi delle tabelle in cui si trovano tali colonne. Where indica a ORACLE quali qualificatori inserire nelle informazioni selezionate. Order by per fornire un criterio di ordinamento delle informazioni

Alcuni comandi di esempio select, Argomento, Sezione, Pagina from GIORNALE where Sezione = ‘F’ order by Pagina;

select, Argomento, Sezione, Pagina from GIORNALE where Sezione = ‘F’ order by Pagina , Argomento;

Restituisce la tabella qui illustrata

Restituisce la tabella qui illustrata

Le Clausole

I comandi select, from where e order by presentano modalità specifiche nella strutturazione delle parole che seguono. In termini relazionali, i gruppi di parole che comprendono queste parole chiave sono spesso definite CLAUSOLE .

LOGICA E VALORE (1/2)

• Anche la clausola con where, proprio come quella order by, può essere impostata da più parti, ma con un grado di raffinamento decisamente superiore.

• Il grado di utilizzo di where viene controllato mediante l’uso attento di istruzioni logiche impartite a ORACLE secondo le esigenze del momento.

• Le istruzioni vengono espresse utilizzando simboli matematici detti operatori logici.

LOGICA E VALORE (2/2)

select, Argomento, Sezione, Pagina from GIORNALE where Pagina = 6

Ecco un esempio di relazione tra logica e valore

Le istruzioni restituiscono la tabella

Verifiche di valori singoli (1/3)

Il segno di uguale viene definito come operatore logico, perché lo si utilizza effettuando una verifica logica, ovvero confrontando i valori posti alla sua sinistra e alla sua destra

Si può utilizzare un singolo operatore logico per verificare un singolo valore

Con i test logici si possono confrontare dei valori, sia con il parametro di uguaglianza, sia con parametri relativi.


Un semplice test, effettuato per tutte le sezioni uguali a B:

select, Argomento, Sezione, Pagina from GIORNALE where Sezione = ‘B’

Ecco il risultato


Il test seguente riguarda tutti i numeri di pagina maggiori di 4 :

select, Argomento, Sezione, Pagina from GIORNALE where Pagina > 4

L’operatore LIKE (1/2)

Si tratta di un operatore molto efficace con il quale è possibile effettuare una ricerca nelle righe di una colonna di un database per i valori che assomigliano a un dato modello descritto.

Vengono utilizzati due caratteri speciali per contrassegnare il tipo di verifica che si desidera effettuare:“%” il segno di percentuale, detto carattere jolly“_” un trattino di sottolineatura detto marcatore di posizione

L’operatore LIKE (2/2)

select, Argomento, Sezione, Pagina from GIORNALE where Argomento LIKE ‘N’;

Il segno (%) significa che qualsiasi carattere può essere accettato in quella posizione: un carattere, cento o nessuno. Se la prima lettera è “N”, viene visualizzata la riga trovata mediante l’operatore LIKE .

select, Argomento, Sezione, Pagina from GIORNALE where Argomento LIKE ‘_ _ i%’;

Per trovare tutti gli argomenti che iniziano con la lettera “N”

LIKE e i caratteri speciali (1/2)

select, Argomento, Sezione, Pagina from GIORNALE where Argomento LIKE ‘%O%O%’;

Si possono anche utilizzare segni di percentuale multipli. Per trovare le parole in cui siano presenti due ‘o’ minuscole in qualsiasi posizione, vengono utilizzati tre segni di percentuale, come in questo esempio :

Il risultato è il seguente

LIKE e i caratteri speciali (2/2)

Si confronti con la stessa query, in cui però l’elemento da ricercare è rappresentato da due “i”

select, Argomento, Sezione, Pagina from GIORNALE where Argomento LIKE ‘ %i%i%’;

Il risultato è il seguente

Questa funzionalità di ricerca può avere un ruolo importante nel rendere un’applicazione più facile da utilizzare, semplificando le ricerche per nome, prodotto, indirizzo e altri elementi che potrebbero essere ricordati solo parzialmente.

NULL e NOT NULL (1/2)

select Città, DataCampione, Precipitazione from COMFORT where Precipitazione IS NULL

Si osservi la seguente istruzione:

IS NULL fondamentalmente indica le posizioni in cui il dato è mancante. Poiché il dato è sconosciuto, il valore nella colonna non è indicato come 0, ma la posizione rimane vuota.

Utilizzando NOT , si possono anche trovare record per cui i dati invece esistono, impostando la query seguente:

select Città, DataCampione, Precipitazione from COMFORT where Precipitazione IS NOT NULL

NULL e NOT NULL (2/2)

Ecco la tabella risultante

Verifiche semplici con un elenco di valori (1/2)

Se si possono utilizzare operatori logici per verificare un singolo valore, esistono altri operatori logici che si possono utilizzare per verificare diversi valori, come un elenco?

select, Argomento, Sezione, Pagina from GIORNALE where Sezione IN (‘A’, ‘B’, ‘F’);

Verifiche semplici con un elenco di valori (2/2)

select, Argomento, Sezione, Pagina from GIORNALE where Sezione = ‘F’ OR Pagina > 7;

select, Argomento, Sezione, Pagina from GIORNALE where Sezione = 7AND Pagina between 7 AND 10;

Infine si possono selezionare le righe nella Sezione F tra la pagina 7 e la pagina 10 impostando la seguente query:

Test logici su un elenco di valori

Esempio con numeri

Esempio con lettere o caratteri

Logica della combinazione

Gli operatori AND e OR si utilizzano seguendo il significato comune dei termini corrispondenti (“e” e “o”). Possono essere combinati in un numero virtualmente infinito di modi ma con molta attenzione

select, Argomento, Sezione, Pagina from GIORNALE where Sezione = ‘A’OR Sezione = ‘B’AND pagina > 2

Ecco alcuni esempi

select, Argomento, Sezione, Pagina from GIORNALE where Sezione = ‘A’AND pagina > 2OR Sezione = ‘B’

Le sottoquery

select Sezione from GIORNALE where Argomento = ‘Salute’

select Argomento from GIORNALE

where Sezione = ‘F’);

select ARGOMENTO from GIORNALE where Sezione = (select Sezione from

GIORNALE where Argomento = ‘Salute’);

Le due query possono essere combinate insieme

Valori singoli in una sottoquery In effetti, il comando SELECT tra parentesi

(definito sottoquery) ha per risultato un valore singolo, F. Nella query principale il valore F è trattato come se fosse un letterale ‘F’. Pertanto il segno di "uguale" rappresenta una verifica per un valore singolo e non funziona con elenchi di valori , Nel caso in cui la query ha più di una riga come risultato allora si ha il seguente messaggio:

select * from GIORNALE where Sezione = (select Sezione from

GIORNALE where Pagina = 1);

ERROR : ora – 1427: single-row subquery returns more than one row

Elenco di valori da una sottoquery

In una sottoquery possono essere utilizzati operatori logici a valori multipli. Se una sottoquery ha come risultato una o più righe, il valore nella colonna per ciascuna riga viene visualizzato in un elenco.

Ad esempio per conoscere le città e i paesi in cui il tempo è nuvoloso. Si potrebbe avere una tabella di informazioni complete sul tempo per le città e una tabella di localizzazione per città e paesi corrispondenti:

Un’istruzione del tipo:

select Citta , Paese from LOCAZIONE where Citta IN (‘select Citta from CLIMA where Condizione = ‘NUVOLOSO’);

Operatori a valori multipli (1/2)

Il loro compito principale è quello di creare una sottoquery che produca un elenco che possa essere verificato logicamente.

QUALI GLI ASPETTI DA CONSIDERARE ?

• La sottoquery deve avere una sola colonna, oppure deve confrontare le colonne selezionate con le colonne multiple poste tra parentesi nella query principale

• La sottoquery deve essere racchiusa tra parentesi

Operatori a valori multipli (2/2)

• Le sottoquery che hanno come risultato soltanto una riga possono essere utilizzate sia con operatori a valori singoli, sia con operatori a valori multipli.

• Le sottoquery che hanno come risultato più di una riga possono essere utilizzate soltanto con operatori a valori multipli

• L’operatore BETWEEN non funziona se utilizzato come in questa istruzione

select * from METEO where Temperatura BETWEEN 16 AND (select Temperatura from METEO where Citta = ‘PARIGI’);

Combinare più tabelle

Premesso che le tabelle abbiano un campo comune, basta semplicemente utilizzare una query con WHERE per indicare a ORACLE ciò che le due tabelle hanno in comune.

select CLIMA .Citta , Condizione, Temperatura, Latitudine,

NordSud, Longitudine, EstOvest from CLIMA , LOCAZIONE where CLIMA.Citta =LOCAZIONE.Citta;

L’istruzione qui illustrata consente di sapere il CLIMA presente nelle varie Città, prelevando tali informazioni dalle tabelle CLIMA e LOCAZIONE


(Combinare più tabelle)

La tabella risultante è la seguente:

Le righe presenti in questa tabella combinata sono quelle che riportano i valori delle città presenti in entrambe le tabelle originarie

E’ come dire “seleziona le righe nella tabella CLIMA e nella tabella LOCAZIONE dove le città sono uguali”. Per cui se una città è riportata soltanto in una tabella, manca il termine di uguaglianza nell’altra tabella.


(Combinare più tabelle)

Se nella prima riga fosse stato scritto semplicemente select Citta , Condizione , Temperatura,

Latitudine

Mediante il comando SELECT vengono selezionate le colonne delle due tabelle che si desidera vedere visualizzate insieme;

Non sarebbe stato possibile per ORACLE determinare la città a cui si faceva riferimento

Pertanto le due possibilità di sintassi corretta della query con select potrebbero essere “CLIMA.Citta” oppure “LOCAZIONE.Citta”

Creazione di una vista

La proiezione o tabella risultante può ricevere un nome ed essere utilizzata proprio come una vera tabella. Questo procedimento viene definito come creazione di una vista

create view INVASIONE AS select CLIMA.Citta ,Condizione , Temperatura,

Latitudine. NordSud, Longitudine, EstOvest for CLIMA LOCAZIONE where CLIMA.Citta = LOCAZIONE. Citta

View created.

Creazione della nuova versione di una vista

Con il comando CREATE OR REPLACE VIEW è possibile creare una nuova versione della vista senza perdere quella precedente.

create OR replace view INVASIONE as select METEO ,Citta, Condizione, Temperatura,

Latitudine, NordSud, Longitudine, EstOvest from METEO LOCAZIONE where METEO.Citta =LOCAZIONE.Citta AND Paese = ‘Grecia’ View created

Ecco un esempio.

Espansione della visualizzazionedi una vista

Una caratteristica importante delle viste è quella di poter occultare o anche modificare i dati. Questo consente di creare report molto complessi impostando, a priori, una serie di viste semplici, elementi o gruppi magari delimitati in modo da visualizzare soltanto determinate parti dell’intera tabella.

Qualsiasi qualificazione inserita in una query può diventare parte di una vista

Le viste non sono istantanee dei dati fissati in un determinato punto nel passato, ma sono elementi dinamici che riflettono costantemente i dati delle tabelle di origine.

INSERT, UPDATE, DELETE

INSERT consente di inserire una riga di informazioni direttamente in una tabella

UPDATE consente di aggiornare le informazioni presenti in una o più tabelle

DELETE consente di cancellare le informazioni presenti in una o più tabelle

Il comando INSERT

Data la seguente tabella

Per aggiungere una nuova riga a questa tabella, occorre la seguente istruzione

insert into COMFORTvalues ('WALPOLE', TO_DATE('21‑MAR‑1993','DD‑MON‑YYYY'), 13.8, 6.ó, o);


(Il comando INSERT)

• La parola values deve precedere l'elenco di dati da inserire.

• Una stringa di caratteri deve essere racchiusa fra apici singoli.

• I numeri possono stare da soli. • I campi devono essere separati da virgole e

devono avere lo stesso ordine delle colonne nella descrizione della tabella.

Per inserire una data in un formato diverso occorre utilizzare la funzione TO_DATE con una maschera di formattazione,

INSERT con SELECT

insert into COMFORT(DataCampione, Precipitazione, Citta, Mezzogiorno, Mezzanotte)select TO_DATE('22‑DEC‑1993','DD‑MON‑YYYY'), Precipitazione,'WALPOLE', Mezzogiorno, Mezzanottefrom COMFORTwhere Citta = 'KEENE' and DataCampione = '22‑DEC‑93',

È anche possibile inserire informazioni che sono state selezionate da una tabella

L’istruzione che segue inserisce delle colonne insieme a dei valori

UPDATE (1/2)

UPDATE richiede di impostare valori particolari per ogni colonna che si desidera modificare e di specificare su quale riga o su quali righe si desidera operare utilizzando una clausola where costruita attentamente

update COMFORT set Precipitazione = .5 Mezzanotte = 22.9

where Citta = ‘walpole’ AND DataCampione = TO_DATE (‘22-DEC-

1993’, ‘ DO-MON-YYYY’);

UPDATE (2/2)

Si possono anche svolgere calcoli, funzioni stringa e ogni altra legittima funzione nell'impostazione di un valore per update

update COMFORT set Mezzanotte=Mezzanotte+1, Mezzogiorno=Mezzogiorno+1where Citta = 'WALPOLE';4 rows updated.

Nell'esempio seguente tutte le temperature di Walpole sono aumentate di un grado:

UPDATE + SELECT (1/2)

È possibile impostare i valori in un comando update incorporandovi un’istruzione select proprio nel mezzo. Si noti che questa select ha la propria clausola where, che preleva la temperatura di MANCHESTER dalla tabella CLIMA, e update ha la propria clausola where che influisce solo sulla città WALPOLE in un certo giorno:

update COMFORT set Mezzanotte =(select Temperatura from CLIMA where Citta = 'MANCHESTER') where Citta = 'WALPOLE'AND DataCampione = TO DATE('22‑DEC‑1993','DD‑MON‑YYYY');1 row updated.

UPDATE + SELECT (2/2)

Ouando si utilizza una sottoquery con update, occorre assicurarsi che non restituisca più di un record per ognuno dei record aggiornati, altrimenti l'aggiornamento fallisce

Le colonne devono essere racchiuse fra parentesi e separate da virgole

update COMFORT set (Mezzogiorno, Mezzanotte) =(sel ect Umidita, Temperaturafrom CLIMAwhere Citta = 'MANCHESTER')where Citta = 'WALPOLE'AND DataCampione = TO_DATE('22‑DEC‑1993','DD‑MON‑YYYY');

DELETE

La rimozione di una o più righe da una tabella richiede il comando DELETE. La clausola WHERE è essenziale per eliminare solo le righe desiderate.

delete from COMFORT where Citta = 'WALPOLE';

DELETE senza una clausola WHERE svuota completamente la tabella

PL / SQL

PL/SQL è un linguaggio strutturato di query (SQL, Structured Query Language) che a sua volta ha come sottoinsieme il linguaggio procedurale (PL , Procedural Language) di ORACLE.

Il linguaggio PL/SQL può essere utilizzato per codificare le proprie regole aziendali mediante la creazione di procedure memorizzate a package, per attivare eventi di database quando necessario o per aggiungere una logica di programmazione all’esecuzione dei comandi SQL

Il codice PL/SQL è raggruppato in strutture dette blocchi. Se si crea una procedura memorizzata o un package, si assegna un nome al blocco di codice PL/SQL

PL / SQL

Un blocco di codice PL/SQL contiene tre sezioni

La Sezione delle dichiarazioni inizia con la parola chiave declare e termina quando inizia la Sezione dei comandi eseguibili, indicata dalla parola chiave begin .

DichiarazioniComandi Eseguibili Gestione delle eccezioni

Fa seguito la Sezione della gestione delle eccezioni, il cui inizio viene indicato dalla parola chiave exception

PL / SQL

Ecco la struttura tipica di un blocco PL/SQL

declare<Sezione dichiarazioni>begin<comandi eseguibili>exception<gestione delle eccezioni>end;

I CICLI

I LOOP possono essere utilizzati per elaborare più record all'interno di un singolo blocco PL/SQL. Il linguaggio PL/SQL supporta tre tipi di cicli.

CICLO SEMPLICE

declare pi constant NUMBER(9,7):= 3.1415926 radius INTEGER(5); Area NUMBER(14,2);

begin radius := 3; Loop area := pi*power(radius,2);

insert into AREA values (radius, area);

radi us : = radi us+1; exit when area >100;

End Loop; end;

CICLO SEMPLICE A CURSORE (1/2)

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.

CICLO SEMPLICE A CURSORE (2/2)

Per determinare lo stato del cursore vengono verificati i relativi attributi

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

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

CICLO FOR (1/2)

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

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

CICLO FOR

In questo esempio 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;


(Il Ciclo FOR )

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

CICLO FOR A CURSORE (1/3)

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

Di seguito è riportato un ciclo FOR a cursore che effettua una query sulla tabella RADIUS_VALS e inserisce record nella tabella AREA:


Il ciclo FOR a cursore illustrato effettua una query sulla tabella RADIUS_VALS e inserisce record nella tabella AREA:

declare _ pi constant NUMBER(9,7) := 3.1415926, 1 . _ area NUMBER(14,2), l _ 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, . /


Il ciclo FOR a cursore illustrato effettua una query sulla tabella RADIUS_VALS e inserisce record nella tabella AREA:

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

CICLO WHILE (1/2)

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 in cui inizia il ciclo.

Esaminiamo, nella diapositiva successiva, un ciclo WHILE che consenta l'elaborazione di più valori di Radius

CICLO WHILE (2/2)

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

I TRIGGER

Sono privilegi di sistema che vengono creati sulle tabellecon il comando CREATE TRIGGER

Esistono 14 tipi di trigger.

Un tipo di trigger è definito dal tipo di transazione e dal livello al quale il trigger viene eseguito

I TRIGGER

Trigger a livello di riga Trigger a livello di istruzione Trigger BEFORE e AFTER Trigger INSTEAD OF

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

create [or replace] trigger [utente.]trigger {before I after I instead of} {delete |insert |update [of colonna [, colonna] ] } [or { delete

| insert | update [of colonna [, colonna] ] } ]

on [utente.]{TABLE I VIEW}[ [referenc~ng { old [as] vecch~o

| new [as] nnuovo} ]for each {row I statement} [when (condizione)] ] blocco pl/sql


(I Trigger )

La sintassi

Nella progettazione di un trigger è possibile una grande flessibilità grazie al significato che assumono


(I Trigger )

La sintassi

• Before

• After• Dele

te• Inser

t• Upd

ate• New

LE PAROLE CHIAVE

LE CLAUSOLE• For

each • When

COMBINAZIONI DI TRIGGER

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

LE PROCEDURE Per creare un oggetto procedurale è necessario disporre del privilegio di sistema CREATEPROCEDURE

Per l'esecuzione, l’oggetto procedurale fa riferimento ai privilegi di tabella del suo proprietario, non privilegi dell'utente che esegue l'oggetto stesso.

Per eseguire una procedura, l’ utente non deve obbligatoriamente disporre del privilegio di accesso alle tabelle cui accede la procedura stessa.

Per consentire ad altri utenti di eseguire un oggetto procedurale, occorre utilizzare il comando GRANT per il privilegio EXECUTE

La sintassi utilizzata per eseguire una procedura dipende dall'ambiente da cui la procedura viene richiamata. Dall'interno di SQLPLUS una procedura può essere eseguita utilizzando il comando EXECUTE, seguito dal nome della procedura stessa.

Qualsiasi argomento da passare alla procedura deve essere racchiuso tra parentesi e seguire il nome della procedura

execute NUOVO LAVORATORE('ADAH TALBOT');

ES.

LE PROCEDURE

PRIVILEGI DI TABELLA NECESSARI

Gli oggetti procedurali possono fare riferimento a tabelle. Perché gli oggetti vengano eseguiti correttamente, il proprietario della procedura, del package o della funzione da eseguire deve disporre di privilegi sulle tabelle utilizzate.

LE FUNZIONI

Le funzioni possono restituire un valore al chiamante (le procedure non sono in grado di restituire valori). Tale valore viene restituito utilizzando la parola chiave RETURN all'interno della funzione

PACKAGE (1/2)

I package sono gruppi di procedure, funzioni, variabili e istruzioni SQL riuniti in un'unica unità. Per eseguire una procedura all'interno di un package è necessario riportare prima il nome del package e quindi il nome della procedura.

execute REGISTRO_PACKAGE.NUOVO_EAVORATORE('ADAH TAEBOT'),

I package consentono a più procedure di utilizzare le stesse variabili e gli stessi cursori.

PACKAGE (2/2)

I package sono gruppi di procedure, funzioni, variabili e istruzioni SQL riuniti in un'unica unità. Per eseguire una procedura all'interno di un package è necessario riportare prima il nome del package e quindi il nome della procedura.

execute REGISTRO_PACKAGE.NUOVO_EAVORATORE('ADAH TAEBOT'),

I package consentono a più procedure di utilizzare le stesse variabili e gli stessi cursori.

Sintassi di CREATE PROCEDURE create [or replace] procedure [utente.]

procedura [(argomen to [ IN|OUT|IN OUT] t ipodat i [(argomento [IN|OUT|IN OUT] tipodati] . . . )]

{IS|AS} {blocco | programma esterno};Sintassi di CREATE FUNCTION

create [or replace] function [utente.]funzione[(orgomento [IN|OUT|IN OUT] tipodoti[,orgomento [ I N | OUT | I N OUT] tipodoti] )]RETURN tipodoti{ISIAS} {blocco I corpo esterno};

Con tale comando vengono creati sia il titolo, sia il corpo della procedura. La parola chiave RETURN specifica il tipo di dati del valore restituito dalla funzione

Riferimenti a tabelle remote L'accesso a tabelle remote è consentito dalle

istruzioni SQL nelle procedure. È possibile effettuare una query su una tabella remota mediante un link di database nella procedura, dove la procedura inserisce un record nella tabella LAVORATORE del database definito di link REMOTE_CONNECT.

create or replace procedure NUOVO_LAVORATORE (Nome_Persona IN varchar2)

AS BEGIN

insert into LAVORATORE@REMOTE CONNECT

(Nome, Eta, Alloggio) values

(Nome_Persona, null, null),END,/

SHOW ERROR

Visualizza tutti gli errori associati con l'oggetto procedurale creato per ultimo.

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.

Visualizza il numero di riga e di colonna per ciascun errore, insieme al testo del messaggio di errore.

DBMS - OUTPUT (1/2)

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.

DBMS - OUTPUT (2/2)

PUT e PUT_LINE vengono utilizzate per generare le informazioni di debugging che si desidera visualizzare

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

FUNZIONI PERSONALIZZATE

La creazione di funzioni pesonalizzate potenzia notevolmente il linguaggio SQL in quanto consente di “adeguarlo” alle proprie esigenze .

L’unico limite delle funzioni personalizzate è che esse non possono essere utilizzate in delimitatori CHECK o DEFAULT

Le funzioni richiamate devono essere autonome (perché create con il comando CREATE FUNCTION) o dichiarate nelle specifiche di package

CREAZIONE DI UN PACKAGEPer la creazione di package, la specifica del package e il corpo del package stesso vengono creati separatamentePertanto abbiamo due comandi :

CREATE PACKAGE per la specifica del package

CREATE PACKAGE BODYper il corpo

Il corpo di un package contiene i blocchi e le specificazioni PL/SQL per tutti gli oggetti pubblici elencati nelle specifiche del package

Una specifica di package consiste nell'elenco delle funzioni, procedure, variabili costanti, cursori ed eccezioni che saranno disponibili all'utente del package.

Compilazione di procedure package e funzioni

ORACLE compila gli oggetti procedurali al momento della loro creazione

Tuttavia è possibile ricompilare esplicitamente le procedure, le funzioni e i package. Per ricompilare una procedura si utilizza il comando ALTER PROCEDURE,

alter procedure NUOVO_LAVORATORE compile;

L’unica opzione valida per tale comando è COMPILE

Questa prima parte delCorso

SQL PL/SQLal momento termina qui.

Slide Oracle Pl SQL

Documents

Transcript of Slide Oracle Pl SQL