ORACLE PL/SQL DOCUMENTAZIONE DIDATTICA Pl/sql lestensione procedurale di SQL. Sql un linguaggio che dice al server di database cosa fare, il quale poi traduce i suoi comandi in procedure che vengono eseguite e non come farlo. Sql un linguaggio non procedurale Linuaggi come il C per esempio o il Pascal sono procedurali e lutente deve conoscere tutte le attvit per accedere ai dati per creare i programmi. PL/SQL

Il Pl/sql un linguaggio strutturato a blocchi. Un blocco Pl/sql inizia con la keyword Begin e termina con la keyword END. BEGIN; (qui si scrive lo statement o codice) END;

OGNI STATEMENT ALLINTERNO DI UN BLOCCO PL/SQL DEVE TERMINARE CON UN PUNTO E VIRGOLA (;) BEGIN STATEMENT; STATEMENT; STATEMENT; END; Un blocco Pl/sql ha una sezione di dichiarazioni variabili chiamata DECLARE che deve essere posta prima del BEGIN. DECLARE Statement; BEGIN Statement; END; Infine un blocco Pl/sql ha unulteriore sezione chiamata EXCEPTION per la gestione degli errori che va posta dopo il BEGIN e prima dellEND;.

DECLARE Statement; BEGIN Statement;

EXCEPTION Statement; END; Allinterno di ogni sezione del blocco PL/Sql possono essere presenti altri sottoblocchi con struttura analoga a quella descritta.

DECLARE Statement; BEGIN Statement;DECLARE Statement; BEGIN Statement; EXCEPTION Statement; END;

EXCEPTION Statement;DECLARE Statement; BEGIN Statement; EXCEPTION Statement; END;

END; I COMMENTI Allinterno di un blocco PL/sql possono essere presenti dei commenti. Se i commenti sono solo su una sola riga dovranno essere preceduti da -- ( due trattini) su pi righe da /* */ : -- Commento su una riga /* Commento su pi righe */ BLOCCO DI DICHIARAZIONI DI VARIABILI

DECLARE ..; - - dichiarazione di variabile BEGIN Statement;DECLARE Statement;

BEGIN Statement; EXCEPTION Statement; END;

EXCEPTION Statement;DECLARE Statement; BEGIN Statement; EXCEPTION Statement; END;

END;

TIPI DI VARIABILI E COSTANTI

Regole per la dichiarazione di Variabili e costanti: 1. blocco Pl/sql. 2. 3. 4. 5. 6. 7. Esempio: DECLARE Mia_variabile Contatore2 Variazione_Tot ..etc; BEGIN ..; END; Tipo (il tipo sta per tipo variabile); --punto e virgola finale Tipo; --punto e virgola finale Tipo; -- punto e virgola finale Le variabili e le costanti devono essere dichiarati nella sezione DECLARE del Possono essere scritte sia con lettere maiuscole che minuscole. Devono iniziare con un carattere Non possono contenere spazi. Possono essere lunghe massimo 30 caratteri. Non possono essere parole riservate. La dichiarazione termina con un punto e virgola (;)

SOTTOTIPI DEC,DECIMAL, DOUBLE_PRECISION, NUMBER (precisione, scala ) FLOAT,INT,INTEGER NUMERIC,REAL,SMALLES T BINARY_INTEGE NATURAL, R POSITIVE CHARACTER, CHAR (dimensione) STRING VARCHAR2 (dimensione) DATE BOOLEAN RECORD TABLE VARCHAR

TIPI

DESCRIZIONE Include solo le cifre da 0 a 9, un punto decimale e un segno meno, se necessario.

Interi con segno da 2 elevato alla 31 1 e da 2 elevato alla 31 1 Stringhe a lunghezza fissa, massimo 32767 Stringhe a lunghezza variabile, massimo 32767 byte, non pu eccedere la lunghezza di 2000 caratteri Date, ore, minuti e secondi Valori logici True o False Tipi di record definiti dallutente Tabelle Pl/sql

DECLARE Nome Cont Data Descrizione ..etc; BEGIN ..; ..; END; CHAR(20); NUMBER(2); Date; Varchar2(30);

Le variabili inoltre possono essere inizializzate con un valore attraverso := o la keyword default: Nome CHAR(20) := Giorgio; (lapecetto indica la stringa Giorgio) Cont NUMBER(2) default 4; (lapecetto non serve poich il tipo di variabile numerico) Data Date;

Descrizione che una

Varchar2(30) default ( o :=) mio nome; (lapecetto indica Stringa) LE COSTANTI

La costante un particolare identificatore, il quale assume un valore quando viene dichiarato che non cambier mai. Le costanti vengono dichiarate come le variabili, unica differenza quella della presenza della keyword CONSTANT posizionata dopo il nome e prima del tipo dato: Nome_costante CONSTANT tipo_dato := valore;

Declare Nome Cont Data Descrizione CONSTANT CHAR(20) default Giorgio; CONSTANT NUMBER(2) := 5; Date; Varchar2(30); ASSEGNAZIONE DELLE VARIABILI

Per assegnare un valore ad una variabile si usa loperatore di assegnazione := o default Lassegnazione di una variabile pu avvenire in qualsiasi parte del blocco pl/sql. Indispensabile lassegnazione delle variabili numeriche prima che esse vengano usate, in quanto altrimenti esse conterrebbero un valore nullo.

LASSEGNAZIONE DELLE VARIABILI PUO ESSERE FATTA ANCHE ATTRAVERSO DELLE ESPRESSIONI

Le espressioni usate per lassegnazione sono: Le stringhe Le variabili Le Costanti Le funzioni pl/sql

Operatori Algebrici LASSEGNAZIONE DELLE VARIABILI ATTRAVERSO IL COMANDO SELECT

Possiamo assegnare un valore ad una variabile usando il comando SELECT alla sola condizione che questo ritorni un unico valore, altrimenti si verifica un errore Declare Identificativo char(20); Contatore Number(2) := 1; . BeginSelect nominativo

Into From Where

identificativo clienti codice=7302;

. End; ATTRIBUTI PER LA DICHIARAZIONE DELLE VARIABILI

Quando si dichiara una variabile che deve assumere un valore prelevato da una tavola del database Oracle, pu essere utilizzato lattributo %TYPE. Con tale attributo possiamo dire che la nostra variabile dello stesso tipo della colonna dove si trova un nostro dato. Sintassi : Declare Identificativo nominativo.clienti%type (colonna.Tabella%type) Contatore Number(2) := 1; . Nome_variabile Colonna.Tavola%TYPE;

BeginSelect nominativo

Into From Where

identificativo clienti codice=7302;

. End;

CONVERSIONE TRA TIPI DI DATO

DATE

NUMBER

ROWID

VARCHAR2

BYNARY_INTEGER

CHAR

RAW

Il pl/sql pu effettuare automaticamente delle conversioni di dato, quando viene fatta lassegnazione di una variabile. TIPI DI DATO COMPOSTI IN PL/SQL

TIPO RECORD TIPO TABLE RECORD

Il record u tipo di dato composto formato da campi che hanno un proprio nome e possono avere tipi di dato scalari diversi. Campo1 Campo2 Campo3 Campon

Campo1 Campo2 Campo3 Campon

Varchar2 Number Char

Type Nome_record IS RECORD (Campo1 Varchar2, campo2 Number, campo3 Char,.); Esempio: Declare Type mio_tipo_record IS RECORD (codice Varchar2, nominativo Number, data_nas Char,.); mio_record contatore Begin End; Come accedere al campo del record? Si usa: nome_record.Nome_campo mio_tipo_record Number(2) := 1;

Quindi per assegnare un valore ad un campo del record: mio_record.codice := 7301; oppure

Select Into From Where

codice,nominativo,data_nas mio_record clienti codice=7301;

Da ricordare: Anche in questo caso la SELECT deve ritornare ununica riga.

Attributo per la dichiarazione dei campi di un record Quando un record viene dichiarato con la stessa struttura della riga di una tabella del database si pu utilizzare lattributo %ROWTYPE. Ossia: Nome_record Nome_tavola%ROWTYPE; Esempio: Mio_record Clienti.%ROWTYPE; TABLE La table un tipo di dato composto simile alle tavole del database. Fondamentalmente la table un array unidimensionalecomposto da record. La table contiene una colonna definita dallutente, di qualsiasi tipo purch scalare, che non ha un nome esplicito. La table non ha una grandezza definita, cresce in modo dinamico. Tutti gli elementi della tavola sono indicizzati da una colonna(chiave primaria) che permette laccesso alle righe di tipo vettoriale.

Campo1

Campo2

Campo3

Campon

Campo1 Campo2 Campo3 Campon

Varchar2 Number Char

TYPE Tipo_tavola IS TABLE OF Tavola.colonna%TYPE INDEX BY BYNARY_INTEGER; Nome_tavola Tipo_Tavola; Esempio: Declare TYPE Tipo_clienti IS TABLE OF Clienti.nominativo%TYPE INDEX BY BYNARY_INTEGER; Mia_Tavola Tipo_clienti; . Begin . End; CONTROLLI DI FLUSSO

Controllo condizionale tramite IF Controllo Iterativo tramite ciclo semplice (Loop, While Loop, For Loop)

Controllo condizionale tramite struttura IF 1) If end if; If Else condizione then..

2) end if; 3)

condizione

then..

If condizione then.. Elsif condizione2 then.. Elsif condizione3 then.. .

Else.. end if; Esempio 1: Declare Identificativo contatore Begin Select Into From Where char(20); number(2) := 1;

nominativo Identificativo clienti codice=7301;

If Identificativo = Silvia then Contatore := contatore +1; End if; End; Esempio 2: Declare Identificativo contatore Begin Select Into From Where char(20); number(2) := 1;

nominativo Identificativo clienti codice=7301;

If Identificativo = Silvia then Contatore := contatore +1; else Contatore := contatore -1; End if;

End; Esempio 3: Declare Identificativo contatore Begin Select Into From Where char(20); number(2) := 1;

nominativo Identificativo clienti codice=7301;

If Identificativo = Silvia then Contatore := contatore + 1; Elsif Identificativo = Salvatore then Contatore := contatore + 2; Elsif Identificativo = Salvatorino then Contatore := contatore + 3; End if; End; Controlli Iterativo Esistono 3 modalit fondamentali per ottenere un controllo di flusso iterativo: LOOP di base WHILE LOOP FOR LOOP LOOP DI BASE Lo scopo fondamentale di un Loop quello di eseguire pi volte una o un gruppo di istruzioni. La struttura di un Loop :

LOOP . END LOOP; Il ciclo non ha mai termine. Esiste per la possibilit di uscire usando il comando EXIT.

Esempio Loop di Base. - -Questo ciclo esegue le istruzioni almeno una volta Declare Identificativo contatore Begin Loop Select From Where nominativo Into Identificativo clienti codice=7301; char(20); number(2) := 1;

If Identificativo = Silvia then Contatore := contatore + 1; Elsif Identificativo = Salvatore then Contatore := contatore + 2; Elsif Identificativo = Salvatorino then Contatore := contatore + 3; Else Exit; -- Il comando exit va posizionato dopo lesecuzione delle istruzioni End if; End Loop; End; I CURSORI Sinora abbiamo visto che le istruzioni che eseguiamo come risultato restituiscono solo una riga. Per gestire pi righe allora?

Se una query chiede di gestire pi righe, bisogna dichiarare un cursore, cio un nome per la query che consente di elaborare le righe restituite dalla query una per una separatamente. I cursori possono essere: Espliciti Impliciti Interni CURSORE ESPLICITO Dichiarazione Il cursore fondamentalmente una variabile, cio un dato e come tale va definito. Come tutte le variabili, la sua dichiarazione avviene nella sezione DECLARE. Declare .. .. Cursor Nome_cursore IS Select . From Begin End; Esempio: Declare Identificativo contatore

char(20); number(2) := 1;

Cursor query_clienti IS Select nominativo,codice,data_nas From clienti Where data_nas = 23-03-1973; Begin End;

USARE I CURSORI Per usare i cursori dobbiamo seguire tre passi fondamentali: 1. Dobbiamo aprire il cursore 2. Leggere la riga attuale 3. Chudere il cursore APERTURA LETTURA CHIUSURA

APERTURA La prima operazione da eseguire per usare un cursore quella di eseguire la query a lui associata. Per eseguire la query si utilizza il comando: OPEN Nome_cursore; Il comando OPEN deve essere richiamato allinterno di un blocco BEGIN o EXCEPTION. Sintassi Declare Cursor nome_cursore IS Select From Where Begin OPEN Nome_cursore; End; Esempio: Declare Identificativo contatore char(20); number(2) := 1;

Cursor query_clienti IS

Select nominativo,codice,data_nas From clienti Where data_nas = 23-03-1973; Begin Open query_clienti; .. End; LETTURA Il cursore ci permette di leggere pi righe da una tabella, questo insieme di righe prende il nome di ACTIVE SET. Per utilizzare i dati della query si utilizza il comando: Questo comando serve a : Far scorrere il cursore allinterno dellActive Set FETCH.

Assegnare ogni riga dellActive Set alla lista delle variabili specificate nella clausola INTO Sintassi Fetch nome_cursore into variabile1, variabile2,etc .

Dal momento che la Fetch restituisce una riga alla volta, per scorrere tutto lActive Set, tale istruzione deve essere racchiusa in un ciclo; avremo perci: Declare Identificativo clienti.nominativo%type; codice clienti.codice%type; contatore Number(2) := 7000; CURSOR query_clienti IS Select nominativo, codice From clienti; BEGIN Open query_clienti;

Loop Fetch query_clienti into nominativo,codice; If codice < 8000 contatore then Contatore := contatore + 1; end if; End Loop; END; CHIUSURA Una volta terminate tutte le operazioni con il cursore, questo deve essere chiuso. Per chiudere il cursore si utilizza il comando: CLOSE nome_cursore; Il comando Close deve essere richiamato allinterno di un blocco BEGIN o EXCEPTION e deve essere successivo allOPEN. Per poter riutilizzare il cursore si dovr riaprirlo. Esempio: Declare Identificativo clienti.nominativo%type; codice clienti.codice%type; contatore Number(2) := 7000; CURSOR query_clienti IS Select nominativo, codice From clienti; BEGIN Open query_clienti; Loop Fetch query_clienti into nominativo,codice; If codice < 8000 contatore then Contatore := contatore + 1; end if; End Loop; Close query_clienti; END;

MODIFICA DI UNA TAVOLA TRAMITE UN CURSORE

Il pl/sql permette, quando si estrae una riga da una tabella con il comando Fetch, di modificare o cancellare i dati allinterno del database. Per modificare una riga restituita da una Fetch si usa la clausola WHERE CURRENT OF I comandi usati sono : DELETE e UPDATE Inoltre il cursore utilizzato deve avere nella sua dichiarazione la clausola FOR UPDATE.

Quando si opera con For Update si acquista un lock esclusivo sulle righe estratte. Esempio: Declare Identificativo clienti.nominativo%type; codice clienti.codice%type; contatore Number(2) := 7000; CURSOR query_clienti IS Select nominativo, codice From clienti FOR UPDATE; BEGIN Open query_clienti; Loop Fetch query_clienti into nominativo,codice; If codice < 8000 contatore then Contatore := contatore + 1; DELETE Clienti WHERE CURRENT OF query_clienti; Else UPDATE Clienti WHERE CURRENT OF query_clienti; end if; End Loop; Close query_clienti; END;

INFORMAZIONI SUL CURSORE Oracle associa delle informazioni al cursore, le quali per mettono di monitorare i processi a lui connessi. Tali informazioni sono gli ATTRIBUTI. %NOTFOUND, assume un valore vero, quando la Fetch arriva alla fine del cursore

%FOUND, assume un valore vero, quando la Fetch trova una riga che non lultima allinterno dellActive Set %ROWCOUNT, lunico attributo che assume come valore un intero, questo rappresenta il numero delle righe esaminate fino alla sua chiamata. %ISOPEN, assume un valore vero, quando la il cursore a lui associato aperto.

Gli attributi assumono dei valori booleani; di conseguenza possono essere usati allinterno dei controlli di flusso. Esempio 1: Declare Identificativo clienti.nominativo%type; codice clienti.codice%type; contatore Number(2) := 7000; CURSOR query_clienti IS Select nominativo, codice From clienti FOR UPDATE; BEGIN Open query_clienti; Loop Fetch query_clienti into nominativo,codice; If codice < 8000 contatore then Contatore := contatore + 1; DELETE Clienti WHERE CURRENT OF query_clienti; Else UPDATE Clienti WHERE CURRENT OF query_clienti; end if; EXIT WHEN query_clienti%NOTFOUND; End Loop;

Close query_clienti; END; Esempio 2: Declare Identificativo clienti.nominativo%type; codice clienti.codice%type; contatore Number(2) := 7000; CURSOR query_clienti IS Select nominativo, codice From clienti FOR UPDATE; BEGIN Open query_clienti; Loop Fetch query_clienti into nominativo,codice; If codice < 8000 contatore then Contatore := contatore + 1; DELETE Clienti WHERE CURRENT OF query_clienti; Else UPDATE Clienti WHERE CURRENT OF query_clienti; end if; EXIT WHEN query_clienti%ROWCOUNT > 1000; End Loop; Close query_clienti; END; CURSORE IMPLICITO Ogni volta che non facciamo uso di query multi riga, quindi usiamo una semplice Select con una condizione che restituisce una sola riga, usiamo un Cursore Implicito. Anche il cursore implicito ha degli attributi, che sono gli stessi di quello esplicito. Unica differenza quella che gli attributi invece di essere preceduti dal nome del cursore, essi vengono preceduti dalla parola SQL. SQL%NOTFOUND

SQL%FOUND SQL%ROWCOUNT SQL%ISOPEN

Esempio : Declare Identificativo char(20); contatore number(2) := 7000; Begin For Indice IN 7000 ..7500 LOOP Select nominativo Into identificativo From Clienti Where codice=indice; IF SQL%NOTFOUND Then Insert Into Tavola_messaggi(messaggio) Values (La tavola non ha pi elementi); END IF; END LOOP; END; CURSORE INTERNO E RECORD IMPLICITO Una particolarit dei record e dei cursori quella che alcune volte si possono usare senza essere dichiarati. Se un record ha la stessa struttura di un cursore, con una sola operazione si evita di: Dichiarare il record Aprire il cursore Fare la Fetch del cursore

Sintassi:

Chiudere il cursore FOR nome_record IN nome_cursore LOOP;

Esempio : Declare contatore number(2) := 7000;

CURSOR query_clienti IS Select nominativo, codice From clienti; Begin For record IN query_clienti LOOP If record.codice < 8000 then Contatore := contatore + 1; DELETE Clienti WHERE CURRENT OF query_clienti; Else UPDATE Clienti SET Codice = codice + 100 WHERE CURRENT OF query_clienti; end if; EXIT WHEN query_clienti%ROWCOUNT > 1000; End Loop; END; Con il Cursore Interno si evita anche di dichiarare il Cursore Declare Contatore NUMBER(4) := 1; Begin For record IN (Select nominativo, codice From Clienti) Loop

If record.codice < 8000 Then Contatore := contatore + 1; End if; End Loop; END; PL/SQL E LA GESTIONE DEGLI ERRORI Un blocco Pl/sql al suo interno prevede una sezione chiamata EXCEPTION la quale provvede alla gestione degli eventuali errori. Declare Begin EXCEPTION Gestione errori END; Allinterno della sezione EXCEPTION viene usata la parola chiave WHEN per identificare il tipo di errore che si verificato e conseguentemente gestirlo. WHEN tipo_eccezione THEN..; Nel caso in cui ci sono pi eccezioni: WHEN tipo_eccezione THEN..; WHEN tipo_eccezione THEN..; Se due o pi eccezioni eseguono la stessa operazione: WHEN tipo_eccezione1 Or tipo_eccezione2 Or THEN..; DEFINIZIONE DI EXCEPTION PERSONALI

Pl/sql permette la definizione di Exception personali da parte dellutente. La dichiarazione di una o pi exceptions viene realizzata allinterno della sezione DECLARE come se fosse una variabile. Il tipo di definizione EXCEPTION. Declare Mia_exception EXCEPTION;

Begin . If condizione_errore = vera then RAISE mia_exception; . EXCEPTION WHEN mia_exception THEN .. END;

Le exception definite dallutente non scattano automaticamente; perci si usa il comando: RAISE nome_exception; Una volta che si entra dentro la sezione Exception di un blocco pl/sql non si pu tornare nella sezione Begin. Ci avviene per qualsiasi tipo di exception. Per eseguire eventuali altre istruzioni conviene usare dei controlli di flusso che racchiudano i sotto blocchi con le exception.LOOP DECLARE . BEGIN EXCEPTION END LOOP;

Con le exception si possono controllare direttamente gli errori Oracle.Perci se si dovesse verificare qualsiasi errore, questo pu essere gestito e non bloccare lesecuzione del blocco pl/sql attualmente in esecuzione. Per assegnare un errore Oracle ad una Exception si usa: Nome_exception EXCEPTION; PRAGMA EXCEPTION_INIT(Nome_exception,codice errore Oracle); Allinterno della sezione exception vengono gestiti gli errori, nel caso in cui si verificasse un errore non gestito il programma terminerebbe rientrando nellambiente chiamante. Per ovviare a questo si pu inserire la gestione tramite un Exception : OTHERS, la quale provvede a gestire tutti quegli errori non definiti precedentemente.

WHEN OTHERS THEN . SQLCODE E SQLERRM

Sono due funzioni utili per trattare gli errori insieme alla Exception Others. SQLCODE restituisce il codice Oracle dellerrore SQLERRM restituisce una stringa contenente lerrore Oracle verificatosi

Queste due funzioni devono essere assegnate a delle variabile per poter essere utilizzate.