Plsql 72422i

ORACLE PL/SQL DOCUMENTAZIONE DIDATTICA

Pl/sql è l’estensione 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 l’utente 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 ALL’INTERNO DI UN TERMINARE CON UN PUNTO E VIRGOLA (;)

BLOCCO

PL/SQL

DEVE

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 un’ulteriore sezione chiamata EXCEPTION per la gestione degli errori che va posta dopo il BEGIN e prima dell’END;.

DECLARE Statement; BEGIN Statement;

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

DESCRIZIONE Include solo le cifre da 0 a DEC,DECIMAL, 9, NUMBER (precisione, DOUBLE_PRECISION, un punto decimale e un scala ) FLOAT,INT,INTEGER segno meno, se NUMERIC,REAL,SMALLEST necessario. Interi con segno da –2 NATURAL, BINARY_INTEGER elevato alla 31 –1 e da 2 POSITIVE elevato alla 31 –1 CHARACTER, Stringhe a lunghezza CHAR (dimensione) fissa, massimo 32767 STRING Stringhe a lunghezza variabile, massimo 32767 VARCHAR2 (dimensione) VARCHAR byte, non può eccedere la lunghezza di 2000 caratteri Date, ore, minuti e DATE secondi Valori logici True o False BOOLEAN TIPI

RECORD TABLE

SOTTOTIPI

Tipi di record dall’utente Tabelle Pl/sql

definiti

DECLARE Nome CHAR(20); Cont NUMBER(2); Data Date; Descrizione Varchar2(30); ………………..etc; BEGIN ……………..; ……………..; END; Le variabili inoltre possono essere inizializzate con un valore attraverso := o la keyword default: Nome CHAR(20) := ‘Giorgio’; (l’apecetto ‘ indica la stringa Giorgio) Cont NUMBER(2) default 4; (l’apecetto non serve poiché il tipo di variabile è numerico)

Data Date; Descrizione Varchar2(30) default ( o :=) ‘mio nome’; (l’apecetto ‘ indica che è una 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 CONSTANT CHAR(20) default ‘Giorgio’; Cont CONSTANT NUMBER(2) := 5; Data Date; Descrizione Varchar2(30);

ASSEGNAZIONE DELLE VARIABILI

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

L’ASSEGNAZIONE DELLE VARIABILI PUO’ ESSERE FATTA ANCHE ATTRAVERSO DELLE ESPRESSIONI

Le espressioni usate per l’assegnazione sono: •

Le stringhe

•

Le variabili

•

Le Costanti

•

Le funzioni pl/sql

•

Operatori Algebrici L’ASSEGNAZIONE 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; ………………… ………. Begin Select

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 l’attributo %TYPE. Con tale attributo possiamo dire che la nostra variabile è dello stesso tipo della colonna dove si trova un nostro dato. Sintassi : Nome_variabile Colonna.Tavola%TYPE; Declare Identificativo nominativo.clienti%type (colonna.Tabella%type) Contatore Number(2) := 1; ………………… ………. Begin Select

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 l’assegnazione 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

Campo1 Campo2 Campo3 Campon

Campo2

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 mio_tipo_record contatore Number(2) := 1; ……………… Begin …………… End;

Campo3

Campon

Come accedere al campo del record? Si usa:

nome_record.Nome_campo

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 un’unica 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 l’attributo %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 dall’utente, 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 l’accesso alle righe di tipo vettoriale.

Campo1

Campo1 Campo2 Campo3 Campon

Campo2

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;

Campo3

Campon

CONTROLLI DI FLUSSO

•

Controllo condizionale tramite IF

•

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

Controllo condizionale tramite struttura IF 1)

If condizione then………….. end if;

2)

If condizione then………….. Else end if;

3)

If condizione then………….. Elsif condizione2 then………….. Elsif condizione3 then………….. ………………………………………. Else………………………………….. end if;

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

nominativo Identificativo clienti codice=7301;

If Identificativo = ‘Silvia’ then Contatore := contatore +1; End if; End;

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

nominativo Identificativo clienti codice=7301;

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

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 char(20); contatore number(2) := 1; Begin Loop Select nominativo Into Identificativo Fromclienti Where codice=7301; 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 l’esecuzione delle istruzioni End if; End Loop; End;

posizionato

dopo

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 char(20); contatore 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 i fondamentali: 1. Dobbiamo aprire il cursore

APERTURA

2. Leggere la riga attuale

LETTURA

3. Chudere il cursore

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 all’interno di un blocco BEGIN o EXCEPTION. Sintassi Declare Cursor nome_cursore IS Select From Where Begin OPEN Nome_cursore; End;

Esempio: Declare Identificativo char(20); contatore 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: FETCH. Questo comando serve a : •

Far scorrere il cursore all’interno dell’Active Set

•

Assegnare ogni riga dell’Active specificate nella clausola INTO

Set

alla

lista

delle

Sintassi Fetch nome_cursore into variabile1, variabile2,etc ….

variabili

Dal momento che la Fetch restituisce una riga alla volta, per scorrere tutto l’Active 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 all’interno di un blocco BEGIN o EXCEPTION e deve essere successivo all’OPEN. 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 all’interno 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 è l’ultima all’interno dell’Active Set

•

%ROWCOUNT, è l’unico 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 all’interno 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

•

Chiudere il cursore

Sintassi:

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; All’interno 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 dell’utente. La dichiarazione di una o più exceptions viene realizzata all’interno 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 dall’utente 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 l’esecuzione 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); All’interno della sezione exception vengono gestiti gli errori, nel caso in cui si verificasse un errore non gestito il programma terminerebbe rientrando nell’ambiente 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 dell’errore

•

SQLERRM restituisce una stringa contenente l’errore Oracle verificatosi

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